MS Excel pivot table field settings

Pivot table is a command in MS Excel which allows thorough analyses of a given database. It can help you group the information in a table by dynamic criteria. As an example, supposing that you have recorded all the sales you had over the past two years, you can group them to see how much you sold per year, or how many items did you sell in total, or you can make a top of your clients with split on the items bought by each of them.

I started this article with the intention of teaching you how to obtain a pivot table from an Excel database, but browsing the net, I found this site that does it very well, so if you don’t know how to do it, take a look there, then come back here for the tip I want to share with you.

I’ll show you how to switch between the normal view of the pivot table and other views, such as percentages. It saves you a lot of work, as in the common way, you would probably have to save your table as values, then create another table in which to put appropriate formulas, in order to obtain your percentages.

This way goes much faster and error free, as it is automatic:

1. Once you got your pivot table, click somewhere in the Total column
2. Look at the Pivot Table floating menu, and click on the button with an “i” in a blue circle (called Field Settings)
3. On the popup menu that opens, click on Options. You will see the menu elongating downwards
4. In the Show Data As field, choose % of column (by default, it should have been set to normal).
5. Click OK and look at your pivot table. Now the totals are displayed as percentages of the total.

Additional tip: if you press F11 while your cursor is somewhere inside the pivot table, you’ll get a very nice, dynamic and customizable pivot chart report.

If you are interested in more resources, you can find some great ones here.