Categorized | excel tips

How to Format a Pivot Table in Excel

In this post you will learn about how to format pivot tables in excel. Formatting a pivot table is often the step that people tend to miss out when presenting information using pivot tables. By formatting a pivot table you can make the data stand out, occupy less screen ‘real estate’ and make the user’s life simpler.

(You can download a copy of the format a pivot table in excel workbook with an example here.)

How to Format a Pivot Table?

Let say we have a data set like the one shown below:

We create our first pivot table using this data set and it comes out more or less like this:

Now this may have some use but the pivot table shown below, which has been properly formatted, may actually be better and more useful in most circumstances:

There are three steps to this transition:

Format the data fields so that they have the same format as the physical data point they represent

pivot-table-optionsWhat I mean is that, by default, the moment you create a pivot table each of the data fields will appear either in the number, date or text format. By formatting the pivot and assigning proper categories (currency, percentage, time etc.) to each data field, you can make the pivot not only look better but also aid the user in getting a grasp of the data much faster. Another important point to remember is that in order to preserve any formatting that you do on your pivot, you have to turn-off the ‘Auto format table’ feature by right clicking anywhere in the pivot table and choosing the ‘Table Options’ menu option. Un-check the ‘AutoFormat Table’ option. This prevents excel from auto formatting the table back to default when you play around with the data. Ensure that you do this step before you start formatting the pivot otherwise you may have to start all over again at a later point.

Ensure that you assign proper shading and border to the pivot table

By assigning proper border and shading, you give proper perspective to the reader. Check out the differences in the default and the properly formatted pivot tables shown above. The headers have been assigned a darker shade and the borders have been well defined. This helps the user separate the headers from the data and reduces the time it take him to get an idea of what data element can be found where.

Hide unnecessary rows and columns

The default pivot table that excel creates can have rows and columns that, on closer inspection, do not add value to the data being represented. In our case the row representing the header for the pivot fields (‘Sum of Sales’ and ‘Date’) adds no value. The reader can easily do without them. Also, empty rows that have no data can be safely removed.

So there you are. Actually we have barely scratched the surface when it comes to using pivot tables and what can be achieved. You may also want to read my earlier post on reading data from pivot table using vba to know how you can get data out of a pivot table using vba for some of your advanced dashboards.

What Do You Think ?

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>


Chinese (Simplified)DutchEnglishFilipinoFrenchGermanHebrewHindiItalianJapanesePortugueseRussianSpanish

treeemap software for excel