?
Solved

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

Posted on 2014-03-18
2
Medium Priority
?
882 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
2 Comments
 
LVL 19

Accepted Solution

by:
regmigrant earned 2000 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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

This article describes how to use the Send to Mail Recipient command. The instructions apply generally to Office 2007 and later versions, but Microsoft® Word 2013 was used for the specific steps and figures.  What is Send to Mail Recipient? Send…
Gain an elementary understanding of Blockchain technology.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.

621 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