Most of us customize our pivot tables as per requirement.
Whenever we create a new pivot table, some customization is always required. Some users add calculations, customize the layout, add a report filter, etc. There are lots of options in pivot tables to make best out of it.
Today I’m going to discuss ten simple, but valuable customizing tips or you can say options to give a better look to your newborn pivot table.
Table of Content10 Tips To Follow
- Number Format
- Change Layout
- Repeat Item Labels
- Banded Columns & Rows
- Change Column Heading
- Disable Autofit Column Width
- Error Handling
- Using Conditional Formatting
- Insert Slicer
- Insert Timeline
Define a Number Format
After creating a new pivot table, one thing which all the user does is adjusting “number format”. Excel by default set number format as “General” which is not so good to use or present in your reports.
It is better to change the number format as per your requirement (in numbers, currency, accounting, etc.) or at least decimals should streamline.
You can change it by using following formatting options.
Home Tab -> Numbers -> Number Format. or
Right Click -> Value Field Settings -> Number Format. or
Press Ctrl +!.
Well, formatted numbers are easy to understand & nice to present.
Change Layout to Tabular (Optional)
By default, Pivot table originates in a compact form. I always found tabular form easier to understand than the compact view.
In tabular form, all of your fields are in different columns rather than in the same column like compact form. Copying data in another sheet from the tabular form is easy.
Repeat Item Labels
Once you have changed your layout, you can also use “repeat item label” option. It’s a very useful option given by the Microsoft to make pivot more understandable.
It will fill the blank cells with the item label.
To apply “repeat label option”.
Pivot table tools -> Design -> Layout-> Report Layout -> Repeat All Item Labels. or
Click on Item Column -> Pivot table tools -> Analyze -> Active Field -> Field Setting -> Layout & Print -> Repeat All Item Labels.
Banded Columns & Rows
In the default view of the pivot table, there is no band on rows & columns. For quick understanding, banded rows & columns are very helpful.
It helps the viewer to read data in column and rows quickly without losing eye connection.
To apply banded rows & columns.
Pivot Table Tools -> Design -> PivotTable Style Options -> Banded Columns / Banded Rows.
Change Column Heading
Once you add your values in the pivot table, it will automatically sum up your values if they are numbers, else it counts them. Excel will change the column heading, according to your calculation type. For Example: “Sum of Amount” in the case of a sum, “Count of Amount” in case you use count.
So, it is better to change it according to actual rather than using the default name. In below example, I have removed the text “Sum of” from actual heading & only “Amount” is left.
Here’s one more thing is noticeable while removing extra text from heading, when you try to rename column heading same as one of your columns in the data dump, Excel will pop up an error.
As you already have the same column name in your data dump, excel will not allow you to put it again. If you want the same name in your heading, a simple solution is there.
Just put a space after your heading text so that Excel will not take it as the same name & and it will not even show in your name title or even at the time of printing.
Disable Autofit Column Width
After creating a pivot table if you change the (increase or decrease) width of pivot table you always wish that the width will stay same. But, By default in a pivot table whenever you update it or make some filtration, it will get back to its default width. 🙁
You can fix it by disabling autofit update.
Go To Pivot Table Tools -> Analyze -> PivotTable -> Options -> Format -> Disable autofit column width on update.
While working with a pivot table, it is evident to face some error at some time. We can’t practically ignore this. It is very important that how we handle those errors, but it also important that how we allow them to appear in between our working.
Showing a typical error sign is not so good. It should be something specific which tells that error has come. Fortunately, we have a customizing option to handle errors in the pivot table.
You can use a particular text or value in the place of error sign.
To Activate it. Go To
Pivot Table Tools -> Analyze -> PivotTable -> Options -> Format -> Activate “For Error Value Shows” & set a value to display on the place of error.
Using Conditional Formatting
Conditional formatting is one of the best tools to add power to your data. You can use it in a pivot table as well. A different color shading by values will make more sense of your data than presenting in a white shade.
Just click on any of the cells in the column in which you want to add conditional formatting.
Go To Home -> Styles -> Conditional Formatting -> New Rule
The best part of conditional formatting in a pivot table is its flexibility. It will adjust formatting with an update in the pivot table. You don’t have to do it again & again. 🙂
In Microsoft Excel 2010, Microsoft introduces a new filtering option named “Slicer”. It is very easy to use to filter items in a pivot table. You also connect a single slicer with all pivot tables in your worksheet.
To add slicer Go To Pivot Table Tools -> Analyze -> Filter -> Insert Slicer -> Add required
A timeline is an advance an option to filter dates. It will help you to filter data in various time trends (Days, Months, Quarters, Years). No need to get stuck with typical date filters, just play cool with a timeline.
To add Timeline Go To
PivotTable Tools -> Analyze -> Filter -> Insert timeline
By using above steps, you can easily make over your pivot table. It will not only look good, but you can also control your pivot table easily.
I hope you found this customization tips useful.
Do have any other tips which you are using for basic pivot table customization?
I would love to hear from you.
Go and hit comment box. See you there.
More About Pivot Table
- Add Rank in Pivot Table
- Add Calculation In Pivot Table
- Automatically Update Pivot Table
- Refresh All Pivot Tables At Once
- Group Dates In Pivot Table