Pivot Table in Excel only counts the rows

ajt8888

New Member
Joined
Aug 23, 2010
Messages
26
I have a weird workbook which is published nationally but will only count the number of rows or alternatively return zero values even though the Total column is formatted as numbers. Its driving me mad. The Pivot is on the Data Sheet

File
 
Last edited:

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,663
Office Version
  1. 365
Platform
  1. MacOS
it thinks its text, I notice the numbers are left justified
I copied the numbers into a different cell , and added 1 and they seem ok
but when i copied and paste back - then it works

So i think something is off with the formatting
 

Attachments

  • Screenshot 2021-02-24 at 09.57.21.png
    Screenshot 2021-02-24 at 09.57.21.png
    106.1 KB · Views: 2

ajt8888

New Member
Joined
Aug 23, 2010
Messages
26
I have a weird workbook which is published nationally but will only count the number of rows or alternatively return zero values even though the Total column is formatted as numbers. Its driving me mad. The Pivot is on the Data Sheet

File
Ok I have fixed it the column of numbers was formatted as numbers but was actually text, I found this article Fix text-formatted numbers by applying a number format and ran "Text to Columns"
 

ajt8888

New Member
Joined
Aug 23, 2010
Messages
26
it thinks its text, I notice the numbers are left justified
I copied the numbers into a different cell , and added 1 and they seem ok
but when i copied and paste back - then it works

So i think something is off with the formatting
Thanks for your help
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,663
Office Version
  1. 365
Platform
  1. MacOS
you are welcome
glad text to columns fixed it, I tried that first and did not work for some reason
anyway - all sorted now
 

Watch MrExcel Video

Forum statistics

Threads
1,129,537
Messages
5,636,893
Members
416,947
Latest member
asher_nk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top