Pivot Table Shows Only Zeros

My pivot table displays zeros instead of numbers.  I have tried re-formatting 
the numbers to "General" and "Number" and "Text".  I have tried changing 
"Count" to "Sum".  I can see the correct numbers in the spreadsheet but not 
in the pivot table.  Please advise.

DOUG ECKERT
0
Utf
1/20/2010 4:07:01 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
967 Views

Similar Articles

[PageSpeed] 13

Changing the format of a cell doesn't change the value.

If your data is in A2:A99, then try this in two empty cells:

=counta(a2:a99)
and
=count(a2:a99)

=counta() will count the number of cells that have something in them.
=count() will count the number of cells that have a number in them.

If those formulas don't evaluate to the same thing (especially if =count()
returns a 0), then you have more to do to convert the values to real numbers.

Find an empty cell
edit|copy
Select the range to fix
Format as General
edit|paste special|values

This should convert those text numbers to number numbers.

Now back to your pivottable and refresh it.  

Do you see the non-zero sum?

DOUG wrote:
> 
> My pivot table displays zeros instead of numbers.  I have tried re-formatting
> the numbers to "General" and "Number" and "Text".  I have tried changing
> "Count" to "Sum".  I can see the correct numbers in the spreadsheet but not
> in the pivot table.  Please advise.
> 
> DOUG ECKERT

-- 

Dave Peterson
0
Dave
1/20/2010 5:07:09 PM
Dave Peterson:  Both COUNTA and COUNT returned the number one.  Pasting the 
value and/or format of the blank, general cell did not change the numbers in 
the base table or in the pivot table.  I did revert to the old trick of 
multiplying the numbers in the base table by 1, but that did not work either. 
 I am open to ideas.  I do have a sample of a very similar pivot table I 
completed a couple of months ago that is correct, but all of the underlying 
formatting appears to be the same.  (How frustrating)!

DOUG

"DOUG" wrote:

> My pivot table displays zeros instead of numbers.  I have tried re-formatting 
> the numbers to "General" and "Number" and "Text".  I have tried changing 
> "Count" to "Sum".  I can see the correct numbers in the spreadsheet but not 
> in the pivot table.  Please advise.
> 
> DOUG ECKERT
0
Utf
1/20/2010 7:37:01 PM
If both =counta() and =count() returned 1, then you only have something in a
single cell and it's a number.

Are you sure you pointed at the correct range?



DOUG wrote:
> 
> Dave Peterson:  Both COUNTA and COUNT returned the number one.  Pasting the
> value and/or format of the blank, general cell did not change the numbers in
> the base table or in the pivot table.  I did revert to the old trick of
> multiplying the numbers in the base table by 1, but that did not work either.
>  I am open to ideas.  I do have a sample of a very similar pivot table I
> completed a couple of months ago that is correct, but all of the underlying
> formatting appears to be the same.  (How frustrating)!
> 
> DOUG
> 
> "DOUG" wrote:
> 
> > My pivot table displays zeros instead of numbers.  I have tried re-formatting
> > the numbers to "General" and "Number" and "Text".  I have tried changing
> > "Count" to "Sum".  I can see the correct numbers in the spreadsheet but not
> > in the pivot table.  Please advise.
> >
> > DOUG ECKERT

-- 

Dave Peterson
0
Dave
1/20/2010 9:19:36 PM
Reply:

Similar Artilces:

Showing Specific Data from Master Sheet to Another Sheet
I am making a bill of materials at work and have a master list I want to access from other sheets. The master list has all parts named and categorized, and I want to be able to access sections (Hydraulics, Chassis, Electrical, etc.) of the master from other sheets. I have tried pivottables but I am having issues showing the data in it original form: Part Name Quantity Material Expense Category etc. (accross the row) I wish to be able to make calculations only off the selected data on each sheet but I am having immense problems getting there. If you can help. Parker Jo...

Activities don't show up until we hit the refresh button
Hello all, We upgraded from 3 to 4 and it appears that the upgrade did not complete successfuly, but the users used the system. Everything appears to be working well except for a couple of strange things like when we create an activity the activity does not appear until we hit the Refresh button, going to history and coming back does not fix the issue, only the refresh button any quick fix on this one? thanks ...

Pivot Table Help #3
I have a lot of data that I am trying to analyze with a pivot table and am not sure how to go about it. Columns are (1) District (2) Store # (3) 2003 Score - these are #s or text ("incomplete") (4) 2004 Score - these are #s or text ("incomplete") For each district, I am trying to find out 3 things: (1) % of stores incomplete (2) Average score for 2003 & 2004 - I've got this one working properly (3) % change between 2003 & 2004 I can successfully analyze the data in a spreadsheet but there is too much to go through and thought a pivot table was the way to ...

Subtract colums in pivot table
I have a pivot table that has the following characteristics (Excel 2007): -rows (down the left) are values: "# Employees", "Total Pay" -Columns (across top) are Dates I want to calculate the difference between different date columns. Example: I have: Date 5/23/2010 5/16/2010 5/24/2009 Total # Emp 10 15 5 30 Pay 1000 15000 500 16500 I'd like: Date 5/23/2010 5/16/2010 5/24/2009 Total Vs. Last Wk % Change Vs. Last Yr % Change # Emp 10 15 5 30 -5 -33% 5 33% Pay 1000 5000 250 6250 -4000 -80% 750 15% Can anyone tell me how to cre...

Show All Groups in 2007
I've looked for this setting, but can't seem to find it -- or, maybe it's not available (which would totally surprise me) -- is there a way to always "Show All Groups" in Access 2007 as opposed to having to right click on the navigation pane and activate all the time? I prefer to see all my objects at once. Thanks for any help. Yes: the top of the Nav Pane has a drop-down. Choose: All Objects. To see them sorted like previous versions, right-click on the top of the Nav Pane, and choose: Category | Object Type -- Allen Browne - Microsoft MVP. Perth, W...

SBS 2008 Fax Sent Items Not showing all faxes sent
Hello, I need to allow sent faxes to be seen by all users. I have Windows XP and Windows 7 64bit client machines. Currently they can only see their sent faxes. In SBS 2003 they were able to see all sent faxes. Any suggestions? Could you create an Outlook rule based on the fax sent acknowledgement that copied the fax to a public folder? "RodSoh" <RodSoh@discussions.microsoft.com> wrote in message news:A4541A0E-18C2-4A83-A13D-8E7D045C29E8@microsoft.com... > Hello, > > I need to allow sent faxes to be seen by all users. I have Windows XP...

How to substitute for a non-existing column in a joined table
Hi, Is there a simpler way than a UNION to return a default value of a joined table for which a corresponding row does not exist? The following example (not a working one, of course) illustrates what I'm after. I'd like to return 'N/A' as c2name if there is no matching row in t2 SELECT t1.c1 (CASE t2.t1pk WHEN NULL THEN 'N/A' ELSE t2.name END) AS c2name, FROM t1 LEFT JOIN t2 ON t2.t1pk = t1.pk Thanks. On 2010-04-21 21:05, bob wrote: > Is there a simpler way than a UNION to return a default value of a joined > table for which a ...

How do I send newslette in email without showing the publisher too
When I email my newsletter as an attachment, when you open the attachment, it takes you to the publisher program page where I built it. How can I send a "finished" newsletter out? As a .pdf file. -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/KB/555375 "Lifefire" <Lifefire@discussions.microsoft.com> wrote in message news:F4E8B0FB-B033-4393-8F27-DFAAA6DD37A5@microsoft.com... > When I email my newsletter as an attachment, when you open the attachment, > it > takes you to the publisher program page w...

Pull Data From Multiple Tables ????
Hi I will have 4 tables name "TblCostomers","TblVendors","TblAccounts", TblExpenses" Now i have a for name "FrmDrVouchers" that has a table "TblDrVouchers" in source. This form has two TxtBox Control name "TxtAccountNo" and "TxtAccountName" If User enters a Account No., It pulls the Account Name from Any One of these Table. I can do this if I have only one table. But tell me how can i do it while I have 4 tables for One Field of a table Thank you.. -- Message posted via AccessMonster.com http://...

Leave Zero's in cell
Hi, when I put three zero's into a cell,.excel automatically changes it back to one zero. I want to keep the three zero's in the cell. Woiuld appreciate any advice please Regards Ivan Hi Ivan Either: Format the cell as Text (although then the numbers entered are text rather than genuine numerics) or format the cells to show leading zeroes (eg a custom format like 000) - note that this is simply for display purposes as the actual contents of the cell will be 0. Hope this helps1 Richard On 15 Mar, 10:47, "Ivan B" <nos...@nospam.net> wrote: > Hi, > when...

Renaming table in a dB
Is there a short way in which i can modify all references to a table after i rename it? Or would i have to open every query and form and manually change the table references?Thanksramesh Access doesn't provide a way to do this.There are commercial products that do, e.g.: http://www.speedferret.com/-- Allen Browne - Microsoft MVP. Perth, Western AustraliaTips for Access users - http://allenbrowne.com/tips.htmlReply to group, rather than allenbrowne at mvps dot org."Ramesh" <ramesh2020@gmaildotcom> wrote in messagenews:uGgN$EuZHHA.4000@TK2MSFTNGP02.phx.gbl...> Is the...

Balances don't show when reconciling
I just upgraded to Money 2004 and the totals dollar value of debits and credits cleared no longer shows. Instead, this line shows: "The difference between your statement and register is:" along with the difference. I want to know the dollar value of debits and credits I have cleared so far and compare that with the bank statement. Somebody at Microsoft thought this was an "improvement" compared to the way they presented the data in M03 and earlier. It's probably something that came out of the usability lab after they took their great grandmother in to see if ...

Dummy series and data table
Hi, I have a chart that presents 2005, 2006, 2007 summary data as a column chart and then 2007 by month as a line. To show the yearly data I have a yearly category, after which I have individual months where the yearly data is zero - sort of like a dummy series - because I only have one value for them. The 2007 detailed data has zero in the yearly column but all the individual values in the monthly columns. It worked fine until I was asked to add a data table to the chart. Now, since it has 2007 twice - once as the summarized for the year and the other as all these individual months - some us...

Tying tables to forms
I have four connected tables that work well as table input but when I put them in a form some of the fields will not let me make entries. Does this happen because I am using the Id fields and subsequent data from the wrong tables? -- Taylor It sounds like you have created a non-updatable form. One cautionary note first: Don't tie your forms directly to the tables. Use queries instead. The queries will act as a stop light for which data is written and when. If more than one person tries to make a change to the same record at the same time, you will run into problems. From wha...

Asset with ZERO cost?
is it possible to keep record of asset with zero (acquistion) cost? these are assets either donated by others or assets where acquistion cost can not be identified. The assets are 'physically' there and we would like keep in record. we tried cost=1 and LTD depn=1 but the will make total assets cost different from our book. any advice? HF: You should be able to save the asset at zero cost. Can you not? Frank Hamelly, MCP-GP NOVA Solutions LLC Melbourne, FL sorry, I can add asset at zero cost, but must the Depreciate to Date always equal Place In Service Date? thks &q...

Stop Buttons showing when opening up form
I have a button on my Main Start up page that when click makes these buttons visible, but when I open up my DB they automatically show on start up, is it possible they not be visible till I click ckbHelp.......Thanks for any Help.....Bob Private Sub ckbHelp_Click() If ckbHelp = True Then cmbHelpNewHorse.Visible = True cmbHelpActFinHorse.Visible = True End If If ckbHelp = False Then cmbHelpNewHorse.Visible = False cmbHelpActFinHorse.Visible = False End If End Sub Bob, In design view, set the property pf the *button* Visible=No Regards/Jacob "Bob" <xxx@xx.xx> wrote i...

Sumif across a table
I am looking for a function that works using a =sumif function to add things that are not in a range that are next to each other as seen below the letters in () are the columns that the values are in... so I am looking for a sum in column A "X" of the total work out time if the appl column is "Y" total work Running (D) Walking (F) Elliptical (H) out time (C) Appl (D) Time (E) Appl (F) Time (G) Appl (H) Time (I) X Y 20 N 0 Y ...

Extra comma and zero-length string
I have a 'simple' concatenation of 3 fields with a comma before the 3rd field: "FirstName LastName, Academic" I would like for the finished string to read; "Jane Smith, PhD" howevever, I've discovered many records have zero-length strings in the Academic field and are causing the comma to appear when the Academic field is blank, e.g: "Tom Jones," This is what I have, which works for most, but not all the records that the Title field appears blank: Name: [tbl_Investigator]![FirstName] & " " & [tbl_Investigator]![LastName] &am...

Some contacts don't show on drop down box when addressing email
I was using Outlook Express, my computer motherboard died, got new computer with Windows 7, trying to learn Outlook 2003. The computer tech from my husband's office put Outlook 2003 on the new computer and somehow transferred the addresses that I had in Outlook Express. I do not know what method he used to get the addresses into Outlook 2003. Some things carried over just fine, some things didn't, i.e. a group list of my subdivision property owners, so I had to re-make that distribution list. When I want to email my daughter, her name or email address is not s...

Customized picklist fields are not showing up in the preview
hi, can you please help me out in the following situation: i have customized my crm 1.2 system in a huge manner. now all customized picklists do not show up in the preview of e.g. opportunity. opening such a related opportunity offers me the possibility to change the picklist field as wanted. note: all built-in picklist fields are showing up in the preview... tia -- Fritz Theiss Did you publish the customizations? And performed an IISReset? After those actions your changes should be visible. Hope this helps, -- Ronald Lemmen Avanade Netherlands "Fritz" wrote: > hi,...

Pivot table
Can I develop a formula that I can add to those which you pick from whe using the wizard ie sum, average, min, max etc Specifically, I want to add an IF statement to give me a 'flag' i which to summarize the data with elsewhere. The data behind the pivo changes (sales data) and I am trying to flag new customers that hav never worked with us before.....once they have traded with us then the dissappear as they are now an old customer To -- Message posted from http://www.ExcelForum.com Hi no you can't do this -- Regards Frank Kabel Frankfurt, Germany > Can I develop a for...

Aging report table for accounts receivable
Can somebody tell me what is the table for a/r aging..I want to make query/view in sql server 2005 Michael, You need to use RM20101 and RM10201 tables. You need to use date functions to get the aging for your view based on document date or due date field. If your aging is setup to be by Doc Date, Consider Doc Date and If it is due date, you should be taking it by Due Date. -- Thanks Janakiram M.P. MCP-GP http://janakirammp.blogspot.com "Michael@nyresume.com" wrote: > Can somebody tell me what is the table for a/r aging..I want to make > query/view in sql server 2005...

Signatures not showing up in email
I have signtures setup and selected in Outlook 2003 and format set to HTML yet they don't show up on the email when I select new. Any ideas on what I'm missing? Are you using Word as the editor? If yes, you must define a signature in Word and then your other signatures will be available via the right click context menu. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. After searching google.groups.com and finding no answer: KMW <anonymous@discussions.microsoft.com> asked: | I have signtures setup and selected in Outlook 2003 an...

Fractions showing up as decimals
I have a spreadsheet with columns of fractions, but when you select a cell the function bar shows a decimal instead and when you save as text file it saves as decimal instead of fraction. Could someone please tell me how to make it show as a fraction in the function bar? I have tried formatting the cell as fraction and it does not work that way Dani Dani Formatting a number as a fraction like 1/2 or 1/5 is strictly for appearance on the sheet. The underlying value is still .5 or .2 The formula bar shows that number and when you save as text file, that is the number that gets saved, n...

pivot tables #3
I am trying to change the order in how the tables display. I don't want an accending or decending alpha order as it is set up now. I would like to pick and choose how I want them listed. How do I change the order? ...