Pivot Table - Missing Year (Row Label?)

washburncincy

New Member
Joined
May 29, 2014
Messages
6
I have a workbook that contains a pivot table. I use this for each of the accounts I handle for my job tracking historical data for the account. I have used this numerous times over the past year+ without incident. Today, though, I was looking at my pivot table and noticed that one of the years (2019) is missing. I've checked my table that is being used as the data source and everything seems to be fine. Here's a shot of what's happening on the pivot table (ignore the 4th column, it's calculated from the 2nd and 3rd):

1610394047338.png


And here's the corresponding data from the source table:

1610394117533.png


As you can see, the corresponding values for 2019 are pulling to the pivot table... just not the year. Now, here's an oddity that truly baffles me. In my attempts to "reset" the source data, I copied the value in B37 (2017) and pasted it to B39. Refreshed the pivot table and it aggregated it under the 2017 year row. I thought... oh, it's fixed. But if I replace the 2017 with '2019', it reverts to the problem scenario. This includes if I take the copied '2017', delete the '7' and replace with a '9'. I just don't get it. Thoughts?
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,791
Have you accidently renamed 2019 in the pivot to " "?

Try typing 2019 over the blank (or space) in the pivot
 

washburncincy

New Member
Joined
May 29, 2014
Messages
6
Have you accidently renamed 2019 in the pivot to " "?

Try typing 2019 over the blank (or space) in the pivot
Just tried it. It will allow it, and... typing it in one section automatically updates the field in each other section. However, if I then refresh the data, it stays out of order (remaining below 2020). I think the problem has to be somewhere in either the source table or the way it is pulling the data over. As I mentioned previously, if I change the year on the source table from 2019 to 2017, it aggregates that info on the pivot table under 2017 successfully.

Thought I'd test something out. I deleted '2017' from its row in the source table. When I did that, I now have two rows without a year in the pivot - 2017, represented by "(blank)" and 2019, represented by "". Weird.

Also, I've tried changing the formatting of the '2019' cell in the source table. I've tried text, number & general and refreshed the pivot after each change. Same results. I'm stumped.
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,791
If it is out of order still, just click the cell with 2019, hover the mouse to the top border and hold left click to drag it into place. When you refresh it will stay there
 

Watch MrExcel Video

Forum statistics

Threads
1,130,170
Messages
5,640,575
Members
417,151
Latest member
ChickenTenderer

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