Solved

Pivot changing values in column from Number to General(Text)???

Posted on 2014-03-18
2
789 Views
Last Modified: 2014-06-23
Hello all,

I have an .xlsm file with 15+ Pivot Tables.

I've created (successfully) external Excel file with "Raw Data".

There are 10+ columns with Numeric values in them.

I go to "Data" Tab -> "Get External Data" -> "From Other Sources" -> "From Microsoft Query".
There is a good connection for my "Raw Data" file.

Issue:

I’ve created 10 Pivot tables and everything is fine.
I’ve used 10 columns of data already, no issues.

Now I’m creating new Pivots in the same sheet and using same External “Raw Data” file.

I’m using data from column 11 and 12.

I’m doing Averages in my Pivots.

Getting error in my Pivot: “#DIV/0!”

I did double clicked the value (in the Pivot), new “Sheet1” popped and I look at column 11
It’s formatted as “General”!
And has little green triangle in the left upper corner “Number Stored as Text”????
It looks like it got changed somewhere between my source file and my Pivot???

BTW: my source has 90 000+ rows.

Thanks!
Michael
0
Comment
Question by:CABRLU63
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 19

Accepted Solution

by:
regmigrant earned 500 total points
ID: 39939158
'General' is not a text format - its just what it says, a default that shows numbers or text based on Excel analysing the content and deciding what's best so in this case its likely that the raw data itself has been stored as text during the query - can you post a sample of the data?

The first thing to do is go to the raw data and make sure those columns are formatted as numbers throughout - select the columns, right click,format cells, number. Then refresh and see if it is solved.

If not then you can force a conversion of numbers as text by putting (numeric) 1 in a cell, copy, select the entire column and paste special then choose multiply

Also confirm pivot table options, layout and format and 'preserve formatting' is checked.
0
 

Author Comment

by:CABRLU63
ID: 39940058
OK, so it looks like data in the "culprit" column, didnt start till row 40 000.
I've resorted my source table the way that "culprit" column has data on top 20 000 rows.
Now my Pivot works fine :-)
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
The viewer will learn how to edit text. This includes Font, Spacing, Resizing, Color, and other special text options.
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question