Fixed Rows And Columns Headings In Pivot Table

Friday, May 03, 2013 0 Comments A+ a-

Problem: You want to show some data in excel pivot table in which you want to fix row headings and column headings of your pivot table.


Let’s have a real world scenario here to understand the pivot table easily.


You need to show the value of Sales Opportunities in a pivot table with Months as column headings and the Sales Managers names as rows.


Where there is data in the underlying database (spreadsheet is linked to a SQL server database) then there is no problem. However if there is no data for a given month then of course by default the Pivot Table doesn't supply that month as a column.


Is there anyway to force a Pivot Table to show column headings in a non-dynamic form. So if there is no data for May the column is still there. This will make readability for my boss a lot easier.


On a similar subject is there also anyway I could fix the rows so that all Sales Managers show up even if there is no data.



Locate your data in excel and make a pivot table with it. Select any column heading or row heading of your pivot table and right click on it. Select Field Settings from right click menu after that goto layout & Print and select Show items with no data. That’s it!!



For you help I am also attaching the snap shots. Hope this tutorial will work for you!!