All You Need to Know About
Customize Pivot Table Tutorials
Learn how to customize Pivot Tables in Excel. You can create your own custom pivot tables with these top recommended modifications!
Here are the top things on what you can do with custom pivot tables in Excel:
Insert Blank Rows In a Pivot Table
Pivot Table reports are shown in a Compact Layout format as a default and if you have two or more Items in the Row Labels (e.g.Month & Customer), then the Pivot Table report can look very clunky…
There is a cool little trick that most Excel users do not know about that adds a blank row after each item, making the Pivot Table report look more appealing. Here is how you add these blank rows..
STEP 1: Click in the Pivot Table and go to Pivot Table Tools > Design > Blank Rows
STEP 2: You will need to click on the Blank Rows button and select Insert Blank Line After Each Item
NB: For this to work you will need at least two Pivot Table Items in the Rows Labels
You then get the following Pivot Table report:
STEP 3: To get rid off the blank rows you can press the CTRL+Z shortcut or from the Blank Rows button select Remove Blank Line After Each Item
Customize an Excel Slicer
Excel Slicers are very boring looking and there are only 14 different Slicer Styles to choose from, ranging from Light to Dark and with a few colors to choose from.
What if you wanted to create your own Slicer using your creative mind with super cool colors and fonts?
Well you can!
This is how you can achieve this with just a few simple steps:
STEP 1: You need to select the Excel Slicer and go to the Slicer Tools tab and choose Options;
STEP 2: Under the Slicer Styles drop down, Right Click on the highlighted Slicer and choose Duplicate;
STEP 3: Select a Slicer Element and click on the Format button;
There are several Slicer Elements which can become very confusing. I have tried to explained the different options with images in this Downloadable Workbook: Slicer Elements
STEP 4: Make the necessary formats and then press OK to go out of the Modify Slicer Style dialogue box;
STEP 5: To see the custom changes you need to select the newly created custom Slicer from the Slicer Styles drop down box.
How To Fix Pivot Table Empty Cells
I am sure that you have come across a Pivot Table which has empty cell values and thought “What the hell is happening here?”
This is because your data source has blank cells for certain items, which happens from time to time.
This can be fixed in your Pivot Table and you can enter a value or text in place of that horrible looking and lonely blank cell.
You need to click in your Pivot Table > PivotTable Tools > Options > Options > Layout & Format > Format > For empty cells show: enter a value or text in this box.
Watch the tutorial below to see how this is achieved…
STEP 1: Our pivot value for North is blank, let us change this!
STEP 2: Go to Pivot Table Tools > Options > Options
STEP 3: Set For empty cells show with your preferred value
All of your blank values are now replaced!