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?
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,720
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,720
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,127,882
Messages
5,627,453
Members
416,248
Latest member
inese_green

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