Once you’ve created your pivot table and got to grips with the data that you want to include, you might want to format it.  Listed below are the steps needed to format your table:-

To format the style of the pivot table…

Click on any cell within the pivot table and you’ll see the PivotTable Tools option above the ribbon, with 2 tabs called Options and Design directly beneath.

Click on the Design tab and you’ll see it is divided into 3 groups:-

1. Layout Group – this allows you to add subtotals, grand totals and change its basic layout.

2. PivotTable Style Options Group – lets you refine the style of the table.

3. PivotTable Styles Group – contains the gallery of styles that you can apply to the pivot table.

Click on the more button in the PivotTable Styles Group (bottom right corner) and this will open the gallery for you to choose which style you prefer…

You can now format the layout of the table by adding banded rows or columns, you can switch off subtotals for the rows or columns, just click on the different options within the Design tab and find your own preferred format for the table.

To format the values within the pivot table…

If you want to change the format of the actual data, e.g. you want the totals to have € symbol, firstly click on the column heading in the pivot table that contains “Sum of”, then click on the Options tab in the ribbon…

In the Active Field section, click on Field Settings, which will open up the Value Field Settings dialogue box…

Here you can change the action of the field e.g. at the moment the column is summing to provide a total, you can change this to count or any of the other functions listed.

In order to change the actual format of the cells, click on the Number Format button and the Format Cells dialogue box appears…

You can now just choose the number format that you want to use e.g. currency €…

Click OK to close each dialogue box and you’ll see that the pivot table has now been formatted to your specifications…

And that’s it – again it’s just a matter of having a play around with the different formats until you find one that suits your purpose.

I hope you’ve found this series of tutorials helpful, and if you have any questions, just leave them in the comment section below 🙂