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
Solved

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

Posted on 2014-03-18
2
766 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 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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

Suggested Solutions

Title # Comments Views Activity
Access 2013 qry from a SharePoint 2013 list 4 37
outlook 2016 orgnize ideas 2 91
trailing spaces all columns 4 96
Outlook 2010: How to search sub-folders 3 16
Photo Albums in PowerPoint Photo Albums are a very useful tool in PowerPoint and allow you quickly add a large number of images. The images can be formatted in a variety of ways so that you are able to create a professional looking presentation v…
With the internet and the ease of information transference, many professional jobs can be done anywhere today.  Why should it make a difference whether an x-ray is read in India or the United States as long as the radiologist is qualified?   Outso…
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.

789 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