Pinterest Pixel
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…
clunky pivot table report
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..

YouTube player


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
insert blank line
You then get the following Pivot Table report:
insert blank rows pivot table
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.

YouTube player


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…

YouTube player


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!

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!