Solved

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

Posted on 2014-03-18
2
803 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

Turn your laptop into a mobile console!

The CV211 Laptop USB Console Adapter provides a direct Laptop-to-Computer connection for fast and easy remote desktop access with no software to install.

Question has a verified solution.

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

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 …
Today companies are subjected to more-and-more data, and it won't stop any time soon.  But there are obvious opportunities for reducing data, particularly data duplicated among companies.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

632 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