Analyzing Data using

PivotTables in Microsoft
Excel 2019

Practical Guide to Get Quick Insights from Data

Demonstrated based on Human Resources and Investment Data

Aemero Andualem
Published by Super Consult PLC
 Published by Super Consult PLC, October 2021
Addis Ababa, Ethiopia.
Telephone: +251-973-054350 (WhatsApp and Telegram)
Gmail: aemeroandualem@gmail.com
Telegram: https://t.me/ExcelApplications

Copyright © All rights reserved; no part of this publication

may be reproduced, stored in a retrieval system, or
transmitted in any form or by any means, electronic,
mechanical, photocopying, recording, or otherwise without
the prior written permission of the author/the publisher.

First edition, October 2021

Donated to______________________________________

Donated by______________________________________

Date___________________________________________
Acknowledgements
ስለ ሁሉም ነገር እግዚአብሔር ይመስገን፡፡ እመ አምላክ ይህን ስራ ለዚህ
ስላበቃሽልኝ ከልቤ አመሰግንሻለሁ፡፡ እግዚአብሔር ፍቅር፤ ትህትና፤ሰላም፤
መተሳሰብና ልቦና ይስጠን። ይህን መጽሐፍ ስትገዙ እኔን እያበረታታችሁኝ
ስለሆነ ሁላችሁንም አመሰግናለሁ፡፡ Heartfelt thanks to my wife,
Elbetel Wosenyeleh, for her encouragement and support. I
would like to thank Dr. Andargachew Baylie, Dr. Getahun
Mekonnen and Dr. Temesgen Dagne for their professional
edits on the manuscript. Heartfelt thanks to Dr. Yoseph
Wouablem, General Manager of Super Consult PLC, for
the decision he and his team made to sponsor the
publication of this book by Super Consult PLC. Besides, I
kindly thank Dr. Temesgen Dagne and Bruk Tsegaye for
their support to cover part of the publication cost and
administrative expenses related to the book. Last but not
least, I would like to thank my families and dear friends for
their encouragement, due concerns and supports.

i
Contents
ACKNOWLEDGEMENTS ........................................................ I
CONTENTS........................................................................... II
PREFACE ............................................................................ IV
CHAPTER 1 : BASICS OF MICROSOFT EXCEL ......... 1
1.1 THE EXCEL USER INTERFACE ........................................ 1
1.2 WORKSHEET AND WORKBOOK...................................... 4
1.3 WORKSHEET FORMATTING ........................................... 8
1.4 AUTOFILL FEATURE IN EXCEL .................................... 12
1.5 CREATING A WORKSHEET ........................................... 15
CHAPTER 2 : INTRODUCTION TO PIVOTTABLES . 26
2.1 PIVOT TABLE CONSIDERATIONS .................................. 27
2.2 CREATE EXCEL TABLE................................................ 33
2.3 CREATE PIVOT TABLES................................................ 37
2.4 LAYING OUT FIELDS IN A PIVOT TABLE FIELD LIST ..... 48
2.5 REFRESHING PIVOT TABLE .......................................... 65
PROJECT-1......................................................................... 70
CHAPTER 3 : NESTING PIVOTTABLE FIELDS ........ 71
3.1 ADDING NESTED FIELDS .............................................. 71
3.2 SHOW OR HIDE LEVELS IN A NESTED FIELD................. 78
PROJECT-2......................................................................... 84
CHAPTER 4 : FORMATTING PIVOTTABLES ........... 85
4.1 PIVOTTABLE TOOLS.................................................... 85
4.2 PIVOTTABLE STYLES .................................................. 87
4.3 PIVOTTABLE STYLE OPTIONS ..................................... 87
4.4 LAYOUT ...................................................................... 89
PROJECT-3....................................................................... 106
CHAPTER 5 : GROUPING IN PIVOTTABLES .......... 107
5.1 GROUPING BY SELECTED ITEMS ................................ 107
5.2 GROUPING BY TIME PERIODS .................................... 112

ii
 5.3 GROUPING BY NUMBERS ........................................... 119
5.4 MULTIPLE GROUPS FROM THE SAME DATA SOURCE . 122
PROJECT-4....................................................................... 123
CHAPTER 6 : CALCULATIONS IN PIVOTTABLES 124
6.1 SUMMARIZE VALUES BY .......................................... 124
6.2 SHOW VALUES AS..................................................... 133
6.3 CREATING CALCULATED FIELDS ............................... 159
6.4 REFERENCING CELLS IN A PIVOT TABLE ................... 163
PROJECT-5....................................................................... 166
CHAPTER 7 : FILTERING AND SORTING DATA ... 169
7.1 REPORT FILTERS........................................................ 169
7.2 SLICERS AND TIMELINES ........................................... 183
7.3 GROUP FILTERS ......................................................... 203
7.4 SORT OPTIONS ........................................................... 220
PROJECT-6....................................................................... 226
CHAPTER 8 : PIVOTCHARTS & DASHBOARDS .... 228
8.1 CREATING PIVOT CHARTS ......................................... 228
8.2 CUSTOMIZING PIVOT CHARTS ................................... 231
8.3 CREATE SIMPLE EXCEL DASHBOARD ........................ 248
PROJECT-7....................................................................... 261
REFERENCES .................................................................. 263

iii
Preface
The ability to analyze and visualize data is a powerful skill
to monitor performance, make better decisions and take
proactive actions. Data is everywhere and organizations are
required to collect, summarize, analyze and visualize any
data of interest and generate periodic statistical reports for
sound decision making. To meet this requirement, the data
analysis and reporting systems of organizations need to be
enhanced through the use of available technology. To this
end, PivotTable feature in Microsoft Excel has been
identified as one of the effective tools to summarize,
analyze and visualize data. It is an interactive tool to create
a quick, easy-to-read summary information from any clean
data. With a pivot table, you can summarize, analyze and
visualize data; get quick insights; and track performance.
Despite the fact that pivot table is the most sophisticated
component in Excel, it is underutilized. Therefore, the goal
of this book is to demonstrate how pivot tables are effective
and efficient to generate interactive statistical reports to
enhance data-driven decision-making in any organization
(government, private or non-government).

This book is written with Microsoft Excel 2019 running

under the Windows operating system, but it can also be
used with Microsoft Excel versions from 2007-2016. It has
demonstrative examples, exercises and projects based on
sample human resources and investment data.

iv
v
 Chapter 1 : Basics of Microsoft Excel

Focus points
• The Excel user interface.
• Worksheet and workbook.
• Worksheet formatting.
• Autofill feature in Excel.
• Creating a worksheet.

Microsoft Excel is an electronic spreadsheet program that

can be used to create, organize, summarize, analyze and
visualize data. It presents data arranged in rows and
columns that can be manipulated using functions, formulas,
commands and other features. Before exploring pivot tables,
it is good to have a working skill of how to use Microsoft
Excel. Therefore, this chapter lays the foundation on basics
of Microsoft Excel.

1.1 The Excel user interface

An Excel user interface (UI) is the means by which users
communicate with computer programs. Some of the UI
components in Excel are the Ribbon, the Quick Access
Toolbar, Right-click menus, the mini-tool bar, Dialog
boxes, and Keyboard shortcuts.

1
1.1.1 The Ribbon
At the very top of the Excel window, you find a collection
of tabs, buttons, and icons, which are collectively known as
the Ribbon. The Ribbon is the primary UI component in
Excel which provides the user with a single place to
conveniently find every commonly used commands and
dialog boxes to perform a wide variety of tasks such as
inserting formulas, pivot tables, pivot charts, etc.

Figure 1-1: The Ribbon in Excel

1.1.2 Tabs and groups

The Ribbon contains a number of tabs including File,
Home, Insert, Page Layout, Formulas, Data, Review, View,
etc. On each tab, there are groups that contain related tools.
On the Home tab, for example, you find the Clipboard
group, the Font group, the Alignment group, and others.
Within the groups, you find command buttons that activate
their respective features. At the bottom of many of the
groups is a small box icon (a dialog box launcher) that
opens a dialog box related to that group. For instance, the
figure below shows the Dialog box launchers in the
Clipboard and Font groups.

2
 Figure 1-2: Dialog box launchers

1.1.3 ScreenTips
Hovering over is an act of moving a mouse cursor over a
clickable object but not actually clicking on it. Hovering
over a tool on the Ribbon displays a ScreenTip that explains
the command it executes. A screentip is a brief description
which appears when a cursor is positioned over an icon,
image, hyperlink, or other elements in a graphical user
interface. For instance, if you position your cursor over a
pivot table button (click Insert tab>Tables
group>PivotTable button), you see a screentip which says
“easily arrange and summarize complex data in a
PivotTable.”

1.1.4 Navigating in a worksheet

Each time you tap the arrow keys on your keyboard, you
move the cell pointer up, down, right or left. You can move
the pointer into a cell in your worksheet by pressing those
arrow keys. You can also use keyboard keys to navigate in
a worksheet. For instance, when you press the HOME key,
the cell pointer moves immediately to the beginning of the
row that you are on and pressing CTRL + HOME takes
you to the top-left corner of your worksheet (cell A1) from

3
anywhere your cursor was located. You can also move a
cell pointer with the mouse by pointing the mouse at the
desired cell and clicking the left mouse button. If you
cannot see the cell you want to visit, use a scroll bar on the
right side or bottom-right corner of your Excel window.

1.2 Worksheet and workbook

Excel works with files called workbook. You can open or
create as many workbooks as you want. Each workbook
contains one or more spreadsheets called worksheets. A
worksheet is a collection of cells where you store and
analyze data. Each worksheet consists of cells organized by
rows and columns. Rows are labeled with numbers
(1,2,3,…) and columns are labeled with letters (A, B,
C,….). The intersection of any column and any row is
called a cell. Each cell has its own unique address. A cell’s
address consists of that cell's column letter followed by its
row number. For example, a cell at the intersection of
column C and row 15 would be identified as cell C15.

1.2.1 Name box and Formula bar

Name box and Formula bar both appear in the bar between
the Ribbon and the column labels. This bar is split into two
sections. The smaller one on the left is called the Name box,
which shows the address of the currently selected cell
(active cell). For instance, if you click in the cell where
column E intersects with row 5, you see E5 in the name box
(see Figure 1-3). The larger part of this bar, to the right, is

4
called the Formula bar. It displays the contents of the active
cell and it is also where you can edit cell contents and
formulas. When there is a formula in a selected cell, the
formula appears in the Formula bar while the value of the
formula appears in the cell.

Figure 1-3: The Name Box and Formula Bar

1.2.2 Sheet tabs

A sheet tab is used to display the worksheet that a user is
currently working on (which is also called an active sheet).
For instance, Sheet1 is an active in the Figure 1-4. By
default, Excel provides three sheets (worksheets) in a
workbook with default names of Sheet1, Sheet2, and
Sheet3.

Figure 1-4: Three Sheet tabs in Excel

5
You can add a new sheet by clicking the plus (+) sign at
the end of sheet3 (see Figure 1-4).

To change a sheet’s name:

• Double-click a sheet tab and type the new name
• -OR- Right-click a sheet tab, click Rename and
change its name.

1.2.3 Data entry in a worksheet

You can enter a number, text, date, formula, and so on into
an Excel cell. That is, an Excel cell can contain:
• Numbers: It includes digits between 0 and 9,
currency sign (ETB or $), percent sign (%),
parenthesis or (), comma (,), dots (.), minus (−) or
plus (+) signs.
• Text: If data value contains any character such as a
space or a question mark or any letter, it is classified
as text. Excel does not perform any mathematical
operations on text data.
• Formulas: A formula is an expression that
calculates the value of a cell based on existing
constant values or other formulas. When inputs of a
formula change, the value of a formula changes.

6
 A formula entered into a cell can consist of operators,
values, functions, cell references and parenthesis.
a. Arithmetic operators include addition (+),
subtraction (−), multiplication (∗), division
(/) and exponentiation (^).
b. Logical comparison operators including less
than (<), greater than (>), less than or equal
(≤), greater than or equal (≥), equal (=) and
not equal (<>).
c. Excel functions include SUM, AVERAGE
and so on.
d. Cell references include named cells (e.g., A2)
and ranges (e.g., A1:C4) that refer to cells in a
worksheet.

To get “^" press 𝑆ℎ𝑖𝑓𝑡 𝑘𝑒𝑦 + 6 and to get " ∗ "press

𝑆ℎ𝑖𝑓𝑡 𝑘𝑒𝑦 + 8 .

1.2.4 Cell references

Most formulas reference one or more cells by using the cell
address. The default is relative cell reference. When you
copy a formula that contains a relative cell reference, for
instance A4, both the row and the column of the cells
referenced in the formula change relative to the cell you
moved to. However, when you copy a formula that contains
an absolute cell reference, for instance $A$4, which is
created by including the dollar currency sign, the formula
still refers to cell A4 wherever you move it.

7
1.3 Worksheet formatting
Excel allows you to control the appearance of fonts and
other attributes of a workbook to look professional. It
provides two types of cell formatting: stylistic and numeric
formatting. Stylistic formatting refers to the cosmetic
formatting (colors, shading, fonts, borders, and so on) that
you apply to make your workbook looks good. The Home
tab>Font group and Home tab>Styles group contain
commands to format a worksheet. Numeric formatting
controls how a value appears in a cell. It is important to
know that Excel treats information differently depending
on the type and format of data it represents. Some types and
formats of data include numbers, dates, currency,
percentages, text and so on. Usually, Excel determines a
data type based on the format you enter it. For example,
type 9/14/2021 into cell B2 of a worksheet. Press ENTER,
and see how your data appears. Excel treated it like a date
because you entered it using a date format.

To change the format of a cell:

• Click Home tab>Number group.
• Click the button with the currency sign on it ($). This
applies the Accounting Number Format.

8
 Figure 1-5: Accounting number format button ($)

• Look at the contents of cell B2 (9/14/2021) now. It

should show $44,453.00.
• Excel now treats the number as a currency.

You can change it back to a date format. Click on cell B2

($44,453.00), and do the following:
• Click the tiny arrow in the lower-right corner of the
Number group as shown below.

Figure 1-6: The dialog box launcher

This opens a Format Cells dialog box as shown below.

9
Figure 1-7: Format Cells dialog box

• In the Number tab, click Date in the Category list.

• In the Type box on the right, you see a list of choices
for how you want a date to appear. Choose the second
option, which should show the day of the week,
month, date, and year.
• Click OK. Now you should see Tuesday, September
14, 2021.

10
To perform other formatting options:
• Type 0.555 in cell D7. Press ENTER, and select cell
D7 again.
• In the Home tab>Number group, click the Percent
Style button (%) in the Number group. Cell D7 now
shows 56% (it is rounded up).

Figure 1-8: Percent Style button

• In the Home tab>Number group, you see two buttons

to the right of the cell formatting buttons to increase
or decrease decimal.
• Click the Increase Decimal button once. Cell D7
should now show 55.5%. Click it again, and you see
55.50%.

Figure 1-9: Increase Decimal button

• Now click the Decrease Decimal button (on the right

side of the Increase Decimal button) to return to
55.5%.

11
Excel applies some numeric formatting automatically based
on the entry of data. For example, if you precede a value
with a currency symbol (such as a dollar sign, $ or
Ethiopian Birr, ETB), Excel applies currency formatting. If
you add a percent symbol (%), Excel applies percent
formatting.

You might have noticed that there is another button in the

Number group for Comma Style. You can use this button
to place a comma in the number, as you usually see in
numbers greater than 999. To see how it works,
type 20998 in cell A1, and press ENTER. Then select the
cell and click the Comma Style button and currency format
($) and see how the number's appearance changes to $
20,998.00. Usually, it is much easier to read large numbers
when they contain commas.

Removing a format
If you want cell A3 to display 0.1273 instead of 12.7%,
you can clear all formatting from the cell. To do so:
▪ Return to cell A3.
▪ Click Home tab>Editing group> Clear arrow
>Clear Formats.

1.4 Autofill feature in Excel

If you need to fill a column or row with values that increase
incrementally, you can use an Excel feature called Autofill.
This feature in Excel helps to enter a series of numbers,

12
dates, or other values. The following examples show how
Autofill works in Excel.

Months of the year

When you work with Excel, you may often need to label
certain columns or rows with the months of the year.
Typing each month manually would be time-consuming.
Fortunately, Excel's Autofill can automatically fill a row or
column with the months of the year for you. For example,
select cell A1, and type Jan (or January) into this cell. When
you select cell A1, you notice that it is outlined in green and
there is a very small box in the lower-right corner of this
outline. This box is called the Fill Handle, a feature that
allows to extend and fill a series of numbers, formulas,
dates or even text to a desired number of cells.

Figure 1-10: Fill handle

Place your mouse over the fill handle in cell A1 and your
mouse turns into a small black cross (or plus sign) and drag
across to cell A12. In cell A12, release the mouse. You
should now see the list of all months in a year. Autofill
feature in Excel can do many wonderful things by filling in

13
relative information. Relative data is information that
corresponds to the column or row where it is located. The
month names that you just created are relative to each row.

You can also use Autofill to fill the days of the week. For
instance, type Sunday in cell D3. Drag the fill handle with
your mouse from cell D3 down to cell D9. The cells should
fill with the days of the week.

Numbers
Autofill works a little different with series of numbers.
Example, move to cell J3, and type the number 1. Place the
pointer in cell J3, and drag the fill handle from cell J3 down
to cell J7. Notice that the cells filled with copies of the
number 1.

To use Autofill in a series of numbers:

• Type the first two numbers in the series in two
adjacent cells. The numbers in this series must
increase upward (decrease downward) by a fixed
interval. You could use 1 and 2, or 2 and 4, or 1.1 and
1.2, or -1.5 and -2.0, or any other pair of numbers to
represent the first two numbers in your series.
• Select both of these cells.
• Drag the fill handle associated with this pair of cells
until you get the series of numbers you want.

14
1.5 Creating a worksheet
Creating a worksheet is the first step in data storage,
analysis and visualization in Excel. For instance, to create
a worksheet on budget sales and expenses for a gift shop;
Open a New Worksheet. Suppose you want to record sales
value in ETBs of different products of a gift shop for the
month of January, February and March. Hence, to make
new worksheet of financial records of the gift shop, type the
following:
▪ Sales volume per month (ETB) from cells B2:D2.
▪ Products in cell A3.
▪ Jan, Feb and Mar in cells B3, C3 and D3 respectively.
Alternatively, select cell B3 and type Jan. Use Autofill
to fill Feb in cell C3 and Mar in cell D3.
▪ T-Shirts in cell A4 and Lotion in cell A5.
▪ 150 in cell B4, 350 in cell C4, and 450 in cell D4.
▪ 300 in cell B5, 500 in cell C5, and 600 in cell D5.
▪ Total in cell A6.

To create a title for a worksheet:

▪ Click cell A1 to select it and type the title: Gift
Shop Financial Record.
▪ Highlight cells A1, B1, C1 and D1 by clicking in
cell A1, holding down the left mouse button, and
dragging to cell D1.
▪ Click Home tab>Alignment group>Merge &
Center button.

15
 Figure 1-11: The Merge & Center button

The title should now appear centered over the four columns.
You can make the title bold and give it a font size of 14 with
the buttons in the Font group on the Home tab.

1.5.1 Using formulas

Formulas perform calculations in Excel. For instance, to
add up the sales volume for each month, you can use a
formula.

To add numbers in Excel:

• Click cell B6 if you want Excel sum up the numbers in
the column above this cell.
• Click Home tab>Editing group>AutoSum.
• Excel proposes a formula.
The formula is:= SUM(B4: B5) where B4:B5 is range of
Excel data from cell B4 to B5.
• Press the ENTER key, and you shall see 450 appear in
cell B6. This is the total sales volume of T-shirts and
Lotion for the month of January.

16
 Figure 1-12: The AutoSum button

An example for AutoSum is shown in Table 1-1.

Table 1-1: AutoSum formula example

• Now change the entry in cell B4 to 200, and press

ENTER. You see the sum in cell B6 automatically
updated to 500.

Use Autofill to copy the sum formula:

• Click cell B6 to select it.
• Find the fill handle in the lower-right corner.
• Click and drag the handle across to cells C6 and D6.
Excel automatically copies the formula in each respective
cell adjusting to give the sum of the records in respective

17
columns. Now row six contains the total sales volume of
the months of January, February and March.

Table 1-2: Sum of sales for three months

1.5.2 Use the Format Painter

The Format Painter lets you copy all the formatting from
one object and apply it to another one. Thus, you can use
the Format Painter on the Home tab to quickly apply the
same formatting such as color, font styles and size, comma
style, percent style, currency, and so on to multiple numbers
or texts.

To use a Format Painter:

• Select the text or number that has the formatting that
you want to copy.
• Click Home tab >Clipboard group>Format Painter
button.
• Use the brush button to paint over the selection of
number or text to apply formatting you copied. This
works only once.
• To apply the formatting in multiple places, double click
Format Painter and apply it in the desired cells.
18
• To stop formatting, press ESC.

Figure 1-13: The Format Painter button

1.5.3 Width of columns and height of rows

If a number has many digits to fit in the width of a column,
Excel indicates that there is a record with many digits with
a series of pound or hash signs as shown in the figure below.

Figure 1-14: Column width error signs

As a result, when a column is not wide enough to show the

information in that cell, the solution is to make your column
wider. To do so,
• Click and drag. For instance, to widen the column
between column D and E, place your cursor between
the letters D and E that are at the top of columns D and

19
 E. When your cursor looks like the cross arrow, hold
down the left mouse button, and drag to the right until
the records become visible.
• -OR-Double click between column D and the E. The
column expands to contain the data.

To change the height of a row:

• Click the border and drag to the desired height or;
• Double click any of the two borders of the row.

To increase the height of a row, you can also choose to

format it with Wrap Text. This can be useful when a phrase
does not fit in a cell and you do not want to merge it with
adjacent cells. To wrap a text, click the cell(s) that you want
to format and click Home tab>Alignment group>Wrap
Text button and you can adjust the row height to make all
wrapped text visible.

Enter a line break

To start a new line of text at a cell:
• Double click the cell in which you want to enter a line
break.
• In the cell, click the location where you want to break
the line, and press ALT+ENTER.

20
1.5.4 Borders and Gridlines
Borders
In Excel, lines that appear on your computer screen will not
appear in a printed worksheet unless you specify them to
appear. It is possible to change how the border lines
between cells should appear and how thick or bold they
should be.

To add a border in the sheet:

• Highlight cells A1 to D8.
• Click Home tab>Font group.
• Click the drop-down arrow next to the Borders button.
• From the list of choices, select ‘All Borders’.

Figure 1-15: Borders menu in the Font group

21
Gridlines
Gridlines are horizontal and vertical lines that differentiate
between cells in a worksheet. You can remove Gridlines by
clicking View tab >Show group>uncheck the Gridlines
box.

1.5.5 Adding rows and columns

To insert a blank row:
• Select a row by clicking the number to the left of that
row.
• Right-click anywhere in the row, and click Insert
• -OR-Click Home tab>Cells arrow> Insert Sheet Rows.

To insert a column:
• Select a column by clicking its letter label at the top.
• Right-click anywhere in the column, and click Insert
or;
• Click Home tab>Cells arrow> Insert Sheet Columns.

If you want to insert five rows or five columns at once, select

five rows or five columns in your worksheet and insert rows
or columns. You can do this with any number of rows or
columns. When you insert a row, it appears above the
selected row and when you insert a column it appears to the
left of the selected column.

22
1.5.6 Entering a formula
A formula in Excel begins with an equal sign (=). To
compute the sum of the values in A1 and A2 in cell A3 in
Excel:
• Type: = A1 + A2 in cell A3.
• Press ENTER.
• You get the result in cell A3 (based on the values in A1
and A2).

To enter a Sum formula = 𝐒𝐔𝐌(𝐀𝟐: 𝐀𝟒):

• Type = SUM(
• Highlight the cell range A2: A4.
• Close the parenthesis to complete the formula and you
get: = SUM(A2: A4).
• Press ENTER.
• You get the result in the cell and the formula is
displayed in the formula bar.

To add a series of values from cells A1 to A10:

• Use the formula = SUM(A1: A10) in cell A11 or a cell
(outside the range A1: A10).
• Press ENTER.

The colon between cells A1 and A10 means “to” and read
A1:A10 as A1 to A10.

23
Editing formulas
To edit a formula, select the formula cell that you want to
edit and click in the Formula bar. This enables you to edit
the cell contents in the Formula bar as you like.

1.5.7 Some functions in Excel

Excel functions are predefined formulas available to be
used. Some of the basic Excel functions such as average,
count, max, min and sum are briefly explained below.

AVERAGE function: Average function calculates the

average (arithmetic mean) of data.

𝐒𝐲𝐧𝐭𝐚𝐱 𝐀𝐕𝐄𝐑𝐀𝐆𝐄(𝐧𝐮𝐦𝐛𝐞𝐫𝟏, [𝐧𝐮𝐦𝐛𝐞𝐫𝟐], … )

Where: Number1 is the first number, cell reference, or
range for which you want the average.
Number2, .... Additional numbers, cell references or
ranges for which you want the average.

COUNT function: Count function counts the number of

cells in a range that contain numerical value. Count
function does not count empty (blank) cells.

𝐒𝐲𝐧𝐭𝐚𝐱 𝐂𝐎𝐔𝐍𝐓(𝐯𝐚𝐥𝐮𝐞𝟏, [𝐯𝐚𝐥𝐮𝐞𝟐], … )

Where: Value1, Value2…arguments that can contain or
refer to a variety of different types of data, but only
numbers are counted.

24
Max function: Max function returns the largest value in a
set of values.

𝐒𝐲𝐧𝐭𝐚𝐱 𝐌𝐀𝐗(𝐧𝐮𝐦𝐛𝐞𝐫𝟏, [𝐧𝐮𝐦𝐛𝐞𝐫𝟐], … )

Where: Number1 is the first number, cell reference, or
range for which you want the maximum.
Number2, .... Additional numbers, cell references or
ranges for which you want the maximum.

MIN function: Min function returns the smallest number

in a set of values.

𝐒𝐲𝐧𝐭𝐚𝐱 𝐌𝐈𝐍(𝐧𝐮𝐦𝐛𝐞𝐫𝟏, [𝐧𝐮𝐦𝐛𝐞𝐫𝟐], … )

Where: Number1 is the first number, cell reference, or
range for which you want the minimum.
Number2, .... Additional numbers, cell references or
ranges for which you want the minimum.

SUM function: SUM function calculates the sum of values

in cells.

𝐒𝐲𝐧𝐭𝐚𝐱 𝐒𝐔𝐌(𝐧𝐮𝐦𝐛𝐞𝐫𝟏, [𝐧𝐮𝐦𝐛𝐞𝐫𝟐], … )

Where: Number1 is the first number, cell reference, or
range for which you want the sum.
Number2, .... Additional numbers, cell references or
ranges for which you want to add.

25
 Chapter 2 : Introduction to PivotTables

Focus points
• Pivot table considerations.
• Create Excel Table.
• Create pivot tables.
• Laying out fields in a pivot table field list.
• Refreshing pivot tables.

If you are working with any data and want to be a good data
analyst, PivotTable is one of the most powerful tools to
summarize, analyze and visualize data to see comparisons,
patterns and trends in your data. A pivot table is a dynamic
statistical report generated from any clean data. It
transforms rows and columns of data into a meaningful
presentation and visualization. With pivot tables, you can
create frequency tables and cross-tabulations of different
data dimensions. The pivot table gets its name from the fact
that you can rearrange row and column headings around
your data, creating different views of the same information.
Rearranging a pivot table by moving rows and columns is
called pivoting: turning the same information to view it at
different angles. Moreover, if you make changes in the
source data, a refresh option updates the pivot table
instantly with the latest data. This chapter introduces
PivotTables and mainly focuses on creating a pivot table,
laying out fields and refreshing pivot tables.

26
2.1 Pivot table considerations
Before creating a pivot table, it is good to understand the
source data and set a goal why you create it. You should
also make sure the source data is organized correctly. You
need to enter the data properly and clean your data before
proceeding. Clean data makes creating a pivot table easier.

To create a pivot table, you need a long list of raw data

values to summarize. To work well with a pivot table, a data
list needs to meet the following criteria.
• The data should be in tabular format and each column
should have a unique name (header).
• The source data should have no blank rows and
columns.
• The data records in the data source should be properly
formatted (text, number, currency, date, and so on).
That is, a text data should be in a text format, a number
data should be in a number format, a date should be in
a date format, and so on.
• Typographical errors and spaces should be avoided.
• Use the same alphabet (unique value) when you use
Amharic language. For instance, Amharic words like
ፕሮፌሽናል ሳይንስ (ፕሳ)≠ፕሮፌሽናል ሣይንስ (ፕሣ)፣ ፀሀፊ≠ጸሐፊ
are treated differently in Excel and should be uniquely
entered in the data source.
• At least one column should have numerical values to
calculate statistics such as sum, count, average,
maximum, percentage, and so on. For categorical (non-
27
 numerical) columns, the only statistics that you can
calculate are counts of the number.

2.1.1 Remove blank rows

Blank rows are rows with incomplete or missing data. A
blank row in the wrong place can make Excel
misunderstand data ranges and cause errors. You can
remove blank rows manually or using the Excel’s Find &
Select command.

Remove blank rows manually

When there are a small number of rows/columns, you can
quickly find and remove them with a selection. You can
select multiple rows by pressing CTRL and clicking on the
row numbers. Right-click anywhere on the rows and select
DELETE command. The empty rows will disappear. The
rows below the deleted ones will be moved up to replace
the deleted space. The row numbers also update to be in
sequence.

Use Excel’s Find & Select command

You can use the Find & Select command to quickly select
all blank rows and remove them in one click. To do so,
• Select the range of rows and columns on a worksheet
that may contain blank cells.
• Go to Home tab > Editing Group> Find & Select
down arrow> Go To Special.

28
• In the Go to Special dialog, select the radio button
for Blanks and click on OK.

Figure 2-1: The Go To Special dialog box

All blank rows (row 7 and 12 in our case, see Figure 2-2)
in the selected range are highlighted. Right-click
anywhere on these rows and select DELETE command
from the menu. Excel displays four options in a tiny dialog
to decide how to handle the empty spaces after deleting
the blank rows.

29
 Figure 2-2: The Delete Command options

In our example, select the Entire row as the whole row is

blank.

Note that the steps used to remove blank rows can also be
applied to remove blank columns.

2.1.2 Remove spaces

Spaces in a data would create a problem when you create a
pivot table. Two cells containing the same text with and
without spaces are considered different values in pivot
table. So, it may take much time trying to figure out why an
obviously correct procedure cannot match two seemingly
identical entries. Spaces can be leading and trailing spaces.
Leading spaces are a blank at the beginning of a line and
trailing spaces are a blank at the end of a line (any white
space at the end of a line).

30
Figure 2-3: Leading, trailing and extra in between spaces

If your data set contains spaces, the Excel TRIM

function is one way of removing the leading, trailing and
multiple in-between spaces at once.

TRIM function: Removes all spaces from text except for

single spaces between words.

𝐒𝐲𝐧𝐭𝐚𝐱 𝐓𝐑𝐈𝐌(𝐭𝐞𝐱𝐭)
Where: Text is the text from which you want spaces
removed. For instance, the TRIM formula: = 𝐓𝐑𝐈𝐌(𝐀𝟏)
deletes spaces from a cell value in A1. The TRIM formula
successfully eliminated all spaces before and after the text
as well as consecutive spaces in the middle of a string.

Figure 2-4: Example for TRIM function

31
Using the TRIM function, you can remove the leading
space in cell A1 and you get the result at cell B1(which is
displayed in cell C1) with proper spacing (see Figure 2-4).

2.1.3 Data sources

To follow along with the book, download the data sources
from the Telegram channel. For instance, the human
resources data has a worksheet named HRMDATA and it
contains 1470 records (rows) as partially shown below.

Table 2-1: Part of the HRMDATA

Before creating a PivotTable, you should have headings in

the first row of your data.

In Excel table columns and rows are referred to as fields

and records respectively. For example, as shown in the
table above, the headers of each column labeled
as EmployeeID, Age Department and so on are fields and
the actual data for each field are records. For instance, the
records for the first employee are EMP00001, 41, Sales,
College, etc.

32
Each row shows the list of employees and the column
headers are EmployeeID (categorical data), Age (numerical
data), Department (categorical), Education Level
(categorical), Education Field (categorical), Gender
(categorical), Position (categorical), Marital Status
(categorical), Monthly Income (currency), Employment
Date (date) and Years of Experience (numerical).

The sample investment data has a worksheet named

InvestmentData which contains a list of 3060 rows
(records) and the column headers are Type of Business
(categorical), Gender (categorical), Registration Date
(date), Nationality (categorical), Zone (categorical), Sector
(categorical), Origin (categorical), Investment (currency),
Employment (numerical), and Status (categorical).

2.2 Create Excel Table

The recommended step before creating a pivot table is to
format your worksheet as an official Excel table which are
great features to analyze data. Formatting a data as a table
makes data analysis with pivot table a lot easier as it makes
it easy to sort, filter and format data. An Excel table usually
has headers to identify what each column contains. By
default, Excel assumes your data in Excel table has headers
and if not, you should insert headers before creating a pivot
table.

33
To format Excel worksheet as a table:
• Open the source data (for instance, the
InvestmentData). Make sure that the range does not
contain any completely blank rows or columns.
• Click a cell within the range.
• Choose Insert tab >Tables group>Table, or press
CTRL+T -OR- Click Home tab>Styles
group>Format as Table (you can use various options
in the Styles group to change the style of your Excel
Table). Excel responds with its Create Table dialog
box. Excel tries to guess the range and also whether
the table has a header row. If Excel guesses the range
incorrectly, you should make corrections.
• Check “My table has headers” box (see Figure 2-5).

Figure 2-5: Create Table dialog box

• Click OK.
The “Where is the data for your table?” box is filled in
with the range you are working with. The range in the
Figure 2-5 is $A$1:$K$3061.

34
The source data is automatically formatted as a table and
Excel placed AutoFilter drop-down arrows next to each
field header. That is, each column header contains a Filter
button that, when clicked, displays a drop-down list with
sorting and filtering options. Moreover, activating a cell in
the table gives you access to the Table Tools contextual tab
on the Ribbon which are relevant to working with a table.

If you encounter problems with tables, you can convert the

table back to a normal range by clicking Table Tools
tab>Design tab>Tools group>Convert to Range.

-OR-Right-click the table, then in the menu, click

Table>Convert to Range. The table style formatting
remains intact, but the range no longer functions as an Excel
table.

2.2.1 Rename an Excel table’s name

Each time you create an Excel table, Excel assigns a default
name as Table1, Table2, Table3, and so on. In a workbook
that contains many tables, you can rename each table to
make it easier to use it.

To rename a table:
• Click on the table and go to Table Tools tab>Design
tab>Properties group>Table Name.

35
 Figure 2-6: Renaming a table name

• Highlight the table name and enter a new name such

as InvestmentData.
• Press ENTER.

Note that you cannot have table names that are separated
by space. It should be one word or words separated by
underscores (_).

2.2.2 Adding new rows or columns in a table

To add a new column to the end of a table, just activate a
cell in the column to the right of the table and start entering
the data. Excel automatically extends the table horizontally.
When you insert a new column, the header row displays a
generic description, such as Column 1, Column 2, and so
on. You can change these names to more descriptive labels.
Similarly, if you enter data in the row below a table, Excel
extends the table vertically to include the new row.
However, if you enter data below the Total row, the table
will not be extended.

To add rows or columns inside an Excel table: Right-

click and choose insert from the menu.
36
Note that when you add or remove rows and columns, the
table range name adjusts automatically. If you scroll the
worksheet down so that the header row disappears, the
table headers replace the column letters in the worksheet
header.

You can change the styles of your table using different

options in the Table Styles group under the Table Tools Tab
(see Figure 2-7). Please experiment with different table
styles.

Figure 2-7: The Table Styles under the Design Tab

2.3 Create pivot tables

The HRMDATA (2012-2016) and InvestmentData (2016-
2018) contain fictional data. To make these data more
meaningful, you can summarize, analyze and visualize it
using Excel pivot table which gives a much better choice
and flexibility to reorganize the data as you want. Creating
a pivot table is an interactive process and it is good to
experiment with various layouts until you find one that fits
with your goals.

37
To create a pivot table:
• Select the range of cells (including column titles) that
you want to use for the pivot table.
• Alternatively, select a single cell in the range and Excel
expands the range automatically; if Excel misidentifies
the range, you can fix it in the next step.
• Click Insert tab>Tables group>PivotTable.

Figure 2-8: The insert PivotTable option

The Create PivotTable dialog box (based on the

InvestmentData) opens (see Figure 2-9). Excel automatically
chooses “Select a table or range”, with a table name or cell
range that you selected. As shown in the Figure 2-9, the table
name: ‘InvestmentData’ is chosen in the Table/Range box.

38
 Figure 2-9: Create PivotTable dialog box

Note that the Create PivotTable dialog box above is based

on an Excel Table.

Note that if you are creating a pivot table from an Excel

table, you can select a cell in the table and choose Table
Tools tab>Design tab>Tools group>Summarize with
PivotTable.

If you want to create a pivot table from the HRMDATA,

the Create PivotTable dialog box appears as shown in the
Figure 2-10, where the source data is a range (not an official
Excel Table).

39
 Figure 2-10: Create PivotTable dialog box

Note that the Create PivotTable dialog box above is based

on Excel range (A1:M1471).

The create PivotTable dialog box in the above figure asks

the following two questions:
• Where is the source data?
• Where is destination/location of the pivot table?

The source data is stored in ranges A1:M1471 (cell A1 to

cell M1471). The ‘SourceData! in the Table/Range box
denotes the name of the worksheet where the data range is
located.

In the Create PivotTable dialog box, choose where you

want the PivotTable report to be placed.
40
• Select “New Worksheet” to create a new worksheet for
the pivot table or choose “Existing Worksheet” to
insert the pivot table on a worksheet that is already in
your workbook. In the Location box, specify the cell
reference (the target cells) where the pivot table will be
located in the existing worksheet. Excel overwrites any
values in the target cells when it creates the pivot table.

In general, it is safe to place a pivot table on its own new

worksheet. If you restructure the pivot table in an existing
worksheet, it can grow to overwrite the other values on the
sheet.

• Click OK. Excel inserts the new pivot table (see Figure
2-11). The pivot table appears as an empty placeholder
until you define the rows, columns, and values to be
used to summarize the source data.

Figure 2-11: The PivotTable task pane

41
If you choose to create a new worksheet, Excel gives the
sheet a generic name (Sheet1, Sheet2, and so on) and places
it to the left of the worksheet that contains the source data.
You can rename, move and copy sheet names as you want.

2.3.1 PivotTable Fields list

The PivotTable Fields list is considered as a task pane
where you interact with the fields from the source data. It is
typically where you begin building a pivot table. It also
allows you access to certain field settings. As you start
adding fields to various areas of the pivot table, the pivot
table updates accordingly.

The PivotTable Fields list appears when you select a cell in

a pivot table and if it does not appear, do one of the
following:
• Click inside the pivot table and choose PivotTable Tools
>Analyze tab>Show group>Field List-OR-Right-click
the pivot table and choose the “Show Field List”.

Note that in Excel 2007-2010, Analyze tab was called

Options tab.

42
 Figure 2-12: PivotTable Fields list

Figure 2-12 contains Excel Table headers such as Gender,

Type of Business, Registration Date, and so on and four
areas (boxes) (Filters, Columns, Rows and Values).

Move, size and close

The upside-down menu in the right most corner of the
PivotTable Fields list gives you access to the task pane
options to move, size, and close the task pane (see Figure
2-13). If you want to move the task pane, select the upside-
down menu and choose the move option. Immediately, your
cursor changes to a four-sided white arrow. When that

43
occurs, you simply click and drag the task pane in a given
direction.

Figure 2-13: Move, size and close options

Tools option
The tools option in the pivot table fields list looks like a
gear and has an upside-down menu next to it (see Figure
2-14). If you click this menu, more options to adjust the
field list settings appear.

Figure 2-14: Tools options

44
The first five options allow you to change the appearance
of the field list. These options are:
• Fields Section and Areas Section Stacked (Default
option) (see Figure 2-12).
• Fields Section and Areas Section Side-by-Side (see
Figure 2-15) which is preferred when you have a lot of
fields.
• Fields Section Only.
• Areas Section Only (2 by 2).
• Areas Section Only (1 by 4).

Figure 2-15: PivotTable Fields list

The default view is designed for a small number of fields.

The other views are optimized for adding, removing, or
45
rearranging many fields. You can experiment with other
layout options.

Search option
In the PivotTable Fields list, a search dialog box is located
below the tools option. This feature is useful if you are
working with a large table with many fields. As you type
the field name, Excel filters the list to only show partial or
full matches. For example, as shown below, “gen” is typed
in the search box and Excel filtered to display the Gender
field.

Figure 2-16: Search dialog box

Below the search option is a listing of all the field names

from the source data. As shown in the Figure 2-14, the
fields are listed in the order they appear in the source data
and the Sort in Data Source Order is checked in the tools
option. If you prefer them to be listed in alphabetical order,
you can use the tools option and select the Sort A to Z
option as shown in the Figure 2-17. This does not change
the order in the source data; it just changes how they appear
in the pivot table fields list.

46
 Figure 2-17: Sort A to Z option for fields

The PivotTable Fields List has the following four areas

(boxes) (see Figure 2-12).
• Filters. These fields limit the data displayed in the pivot
table. Adding a field to the pivot table Filter area allows
to focus on a smaller portion of the larger dataset. Fields
in the filter area will be shown on the top left-hand
corner of the pivot table. You can use multiple fields as
filters to narrow on a specific item.
• Columns. In the pivot table, any field you move to the
Columns area is displayed across the top from left to

47
 right. Adding a field, or fields, to the Columns area adds
columns of data to a pivot table.
• Rows. Any field placed in the Rows area is displayed
down the column from top to bottom. Depending on the
format of a pivot table, adding more fields to the Rows
area increases the number of records in the report.
• Values. The Values box is typically used for numerical
fields. Fields in the values area will be shown into the
middle part of the pivot table. This area is designed to
perform calculations such as counts, sums, averages,
and other statistics. A pivot table lacks functionality if it
does not contain a numeric field (at least counts) within
the Values area.

By default, if Excel detects any field as text-based, it

automatically adds to the rows area and a numeric field is
added to the Values area. If you want a field to be in the
Filters or Columns area, you have to drag and drop that field
into the appropriate area. When a field is added in one of
the four areas, a check mark appears next to that value in
the field list.

2.4 Laying out fields in a pivot table field list

Laying out a pivot table is adding, rearranging, or removing
fields in the four areas or boxes in a pivot table field list.
To add fields into the boxes:
• Drag columns (fields), from the “Choose fields to add
to report” list into any of the four boxes.

48
• You can also select the checkbox next to a field; Excel
places it in a box depending on the field’s data type (if
Excel guesses wrong, drag the field to the correct box).
Excel updates the pivot table dynamically as you add,
rearrange, or remove fields in the four boxes or areas.
• You can also right-click the fields in the PivotTable
Fields list and add them to the desired boxes (see Figure
2-18).

Figure 2-18: Right-click options to add fields

Choosing a field to appear as a Row or Column label

Choosing whether a field appears as a row or column label
is a matter of formatting and readability (either way, the

49
same data can be displayed). Fields with long category
names or many distinct values typically work better as row
labels (as column labels, they stretch columns). You might
be able to fix this issue by placing a field in the filters area
that could shorten the width and generate an attractive
report.

Example 2.1: Prepare a report on the number of investment

projects by zone.

To create a pivot table:

• Click Insert tab>Tables group>PivotTable (place the
pivot table in a ‘New Worksheet’).
• Add the Zone field to Rows box. To add Zone field to
Rows box, do one of the following:
a. Drag Zone field to the Rows box.
b. Select the checkbox next to the Zone field.
c. Right-click on Zone field and select Add to
Rows Labels.
Excel fills in the list of zones from top to bottom in
alphabetical order one zone per row.

• Drag Zone field to the Values box. Excel fills the pivot
table with the count of Zone in each zone. Then, you get
a pivot table (see Table 2-2) which shows the number of
investment projects registered by zone.

50
 Table 2-2: Number of investment projects by zone

As per Table 2-2, during three years (2016-2018), for

instance, the number of investment projects registered in
East Zone are 492. Pivot tables also calculate grand totals
(total number of investment projects in this case) which is
3060 as shown in Table 2-2 (cell B9).

There is a checkmark in the Zone field which confirms that

Zone is in one of the boxes in the PivotTable Fields List

You can use the Sort option to arrange the row or column
fields in the pivot table. By sorting, you can highlight the
highest or lowest values. To sort a pivot table column;
right-click on a value cell, and choose Sort from the menu.
Then, click Sort Smallest to Largest or Sort Largest to
Smallest. -OR-Click in the pivot table and choose Data tab>
Sort & Filter group>Sort and select either A to Z or Z to A.

51
Exercise 2.1: Based on Table 2-2, which zone has the
highest number of investment projects and which one has
the lowest one? Use the Sort option to do this exercise.

2.4.1 Get a list of unique values

You can use pivot tables to get a list of the unique values in
any field of your data. Simply drag the field from which
you want unique values into the Rows area of a blank pivot
table and the resulting pivot table will contain a list of
unique values from your data for that field. For instance,
based on the figure below, there are six unique education
fields in the HRMDATA.

Figure 2-19: Unique list of Education Fields

2.4.2 Rearranging (pivoting) a pivot table

In the PivotTable Fields list, you can move fields at any
time to rearrange the pivot table.

52
To move a field from one area (box) to another, do any
of the following:
• Drag the field from one box to another.
• Click the field in a box and choose a “Move to”
command from the menu. For instance, to move a Zone
field from Rows box to Column box, click the “Move
to Column Labels” option (see Figure 2-20).

Figure 2-20: Move to and other options

53
To remove a field from a pivot table:
Do any of the following:
• Drag the field from any box out of the PivotTable
Fields task pane.
• Click the field in the box and choose Remove Field
from the menu.
• Clear the checkbox next to the field name in the
PivotTable Fields list.
• Right-click over the field in the PivotTable report and
click “Remove [the field name].

Note that the checkbox in the fields list acts like a toggle
(on and off). Click it once to add the check mark and the
field appears in your report and click it again to remove the
field.

2.4.3 Rename a PivotTable

When you create a PivotTable, Excel assigns a default
name such as PivotTable1, PivotTable2, and so on.
However, you can change the name of the pivot table to
make it more meaningful.

To rename a PivotTable:
• Click the PivotTable and go to PivotTable
Tools>Analyze tab> PivotTable group>PivotTable
Name text box.
• For Excel 2007-2010, PivotTable Tools>Options tab>
PivotTable group>PivotTable Name.

54
 • Type the new name.
• Press ENTER.

You can use CTRL+Drag to copy the worksheets which

contain pivot tables in your worksheet and edit it to create
a pivot table with different layout instead of creating pivot
tables from scratch now and then.

Example 2.2: Prepare a report on the amount of investment

by type of business. To do so:
• Create a new pivot table (use the CTRL+Drag to copy
the worksheet which contains the pivot table in
Example 2.1 and change the layout).
• In the PivotTable Fields task pane, drag Type of
Business field to the Rows box and Investment field to
the Values box. Excel fills the pivot table with the Sum
of Investment for each type of business.

Table 2-3: Amount of investment by type of business

The pivot table in Table 2-3 shows the amount of

investment by type of business. For instance, during three

55
 years, the amount of investment (ETB) made by Sole
Proprietors is the highest whereas the amount of investment
made by a public enterprise is the lowest.

Pivot table titles

You can create a title for your pivot table and format it as
you want. You can merge the title, add borders, adjust the
width of columns and height of rows in your pivot table.
Exercise 2.2: Generate pivot tables for each of the
following.
• The number of investment projects registered by
sector, type of business, status, and origin.
• The amount of investment by zone, sector, status and
origin.

If you change the count function to sum function in the

Values box, right-click a value in the column you want to
change (in the pivot table), then choose Summarize Values
By >Sum.

Example 2.3: Based on the HRMDATA, generate a pivot

table on the number of employees by gender with the
following settings.
Rows: Gender.
Values: Gender (Summarized by Count).
Based on these setting, you get the following pivot table that
summarizes the number of employees by gender (see Table
2-4).

56
 Table 2-4: Number of employees by gender

Table 2-4 shows that there were 588 female and 882 male
employees. The grand total, 1470, shows the total number
of employees. You can rename the default ‘Count of
Gender’ column title (at cell B2) to ‘Number of employees.’

2.4.4 Rename any label in a pivot table

You can rename any label in a pivot table simply by
selecting the cell and typing over it. You can change item
names in a field, row headings, column headings, filter
labels, totals or grand total labels. This does not change the
source data; it just changes how the item is labelled. The
exceptions are you cannot rename it to something that
already exists in your source data and you cannot type over
a value. If you try to rename a label to an existing name in
the source data, you may get a warning message saying
“PivotTable field name already exists.” You can resolve
this by adding a space character or underscores to the end
of the name.
Exercise 2.3: Create pivot tables to summarize the number
of employees by (a) department and (b) education level.

57
Example 2.4: Prepare a pivot table that summarizes the
number of employees by education level and gender with
the following settings.
Rows: Education Level.
Columns: Gender.
Values: Gender (Summarized by Count). The pivot table
(see Table 2-5) shows that the number of employees by
education level and gender. For instance, there were 235
female and 337 male employees who hold a bachelor
degree.

Table 2-5: Employees by education level and gender

2.4.5 Showing the details of a value’s source data

If you want to show the details of the data or if you find a
trend, unexpected relationship, or an outlier data in a pivot
table, you can “drill down” to see exactly how the value
was calculated. To do so, double-click any value cell in a
pivot table and Excel creates a new worksheet containing
copies of only the records that were used to calculate that
cell’s value.

58
Example 2.5: Based on the pivot table in Table 2-5, show
the details of the data for female employees who hold a
doctorate degree.

To show the details of the data:

Double-click the cell containing the number of female
employees with a doctorate degree. Excel adds a worksheet
containing copies of the 22 records (in a formatted table)
whose values are counted to generate female employees
who hold a doctorate degree-OR-Right-click the cell
containing the value and choose “Show Details” option.

After you finish examining the data, you can delete the
worksheet that contains the copied records (right-click the
worksheet tab at the bottom of the window and choose
DELETE). The original source data are not touched when
you delete the copy. If you find an error in the copied
records, you should go to the original source data to fix it.

If you want to see the data behind a subtotal (grand total),

double click that subtotal (grand total) and Excel provides
you a full set of data.

Turn off Show Details options

If the ability to show the detailed data behind a pivot table
result does not interest you, you can turn this feature off.

59
To turn off Show Details options:
• Select your pivot table and click PivotTable
Tools>Analyze tab>PivotTable group >Options.
• In the Options menu, go to the Data tab and uncheck
the Enable show details box to disable this feature.

Figure 2-21: The PivotTable option dialog box

2.4.6 PivotTable Options

You can change the most common pivot table settings by
using the Ribbon or the PivotTable Fields task pane, but

60
you can find many others in the PivotTable Options dialog
box (see Figure 2-22). To open PivotTable Options, right-
click a cell in the pivot table and choose PivotTable
Options.
-OR-Choose PivotTable Tools>Analyze tab >PivotTable
group>Options button.

Figure 2-22: The PivotTable Options dialog box

Note that settings changed in the PivotTable Options dialog

box apply to only the active pivot table.

61
 2.4.7 Moving a pivot table
You can move a pivot table to a new worksheet or an
existing one. To do so, click the pivot table and choose
PivotTable Tools>Analyze tab>Actions group >Move
PivotTable. The Move PivotTable dialog box is displayed.
Under Choose where you want the PivotTable to be placed,
do one of the following:
• To place the PivotTable in a new worksheet, click New
Worksheet.
• To place the PivotTable in an existing worksheet, select
Existing Worksheet, and type the first cell in the range
of cells where you want to locate the PivotTable.

2.4.8 Deleting a pivot table

Excel will not let you insert or delete individual cells, rows,
or columns in a pivot table. However, you can delete a pivot
table.

To delete a pivot table:

• Select the entire pivot table. To select the entire pivot
table, drag to select all the pivot table’s cells (including
headers)
• -OR-Select a cell in the pivot table and choose
PivotTable Tools>Analyze tab>Actions>Select
arrow>Entire PivotTable.
• Press DELETE command.

62
2.4.9 Move PivotTable labels
After you build a pivot table, and add fields, you can
rearrange the items in one or more of the fields.

Change order of PivotTable labels

When you add a field to the Row or Column label area of
the pivot table, its labels are usually sorted alphabetically
(A to Z or Z to A). If you want the labels in a non-
alphabetical order, you can manually move them as
follows.

1. Drag labels to new position

To move a pivot table label to a different position in the list,
you can drag it. To do so,
• Click on the label that you want to move.
• Point to the border of the selected cell, and when the
pointer changes to a four-headed arrow, drag the cell to
its new position. An insertion bar indicates where the
label will be dropped. The existing labels shift down,
and the moved label takes its new position.

2. Use menu commands to move label

To move a pivot table label to a different position in the list,
you can use commands in the right-click menu. To do so,
• Right-click on the row or column label that you want
to move.
• Click the Move command.

63
• Click one of the Move subcommands, such as Move
[item name] Up (see Figure 2-23).

Figure 2-23: Options to reorder fields in a pivot table

3. Type over another label

To move a pivot table label to a different position in the list,
you can type its name over another label.
• Click on the cell where you want a different label to
appear.
• Type the name of the label that you want to move.
• Press ENTER.

If you type a name that is not an existing label, you will

rename the label that you typed over, instead of moving it.

64
2.5 Refreshing pivot table
2.5.1 Change in a data source
If you add rows to the bottoms of a range of source data,
you can redefine the pivot table’s source data to include
those rows so that it is ready to be refreshed.

To change a pivot table’s source data:

• Select a cell in the pivot table.
• Choose PivotTable Tools>Analyze tab>Data
group>Change Data Source. The Change PivotTable
Data Source dialog box appears as shown in the Figure
2-24.
• Change the new data source in the ‘Table/Range’ box.
• Click OK.

Figure 2-24: The Change PivotTable Data Source dialog

The same fix applies if you add columns to the right edge
of the source data.

65
2.5.2 Refreshing a pivot table
If you make changes in the source data, pivot table you
generated from the source does not update automatically,
you should refresh it. A refresh option makes Excel scan
the source data and recalculates the pivot table to update
itself with the latest data. If a pivot table is created from
Excel Table Excel, it automatically accounts for any new
rows or columns that you add to the source data when you
refresh the pivot table.

To refresh a pivot table:

Do any of the following:
• Right-click the pivot table and choose ‘Refresh.’
• Select a cell in the pivot table and press ALT +F5.
• Select a cell in the pivot table and choose PivotTable
Tools>Analyze tab>Data group>Refresh arrow
(Refresh or Refresh All).
▪ Refresh: Refreshes any pivot table connected to
the source data of the active pivot table.
▪ Refresh All: Refreshes all data connections for all
pivot tables in the active workbook.

Auto refresh a pivot table

If you want to have the latest data in your pivot tables, you
can set the workbook to refresh all pivot tables connected
to particular data source. This is especially useful with if
you have a report that is connected to a constantly changing

66
data source and your report always needs to reflect these
changes.

To autorefresh a pivot table when you open a

workbook:
• Select a cell in the pivot table and choose
PivotTable Tools>Analyze tab>PivotTable
group>Options>Data> select “Refresh data when
opening the file.”

Figure 2-25: Refresh Data when opening the file option

After starting a refresh, you can review its status or cancel

it at any time by choosing Refresh Status or Cancel Refresh
from the Refresh menu.

67
2.5.1 Autoformatting on Refresh
If a pivot table becomes misformatted when you refresh it,
select the “Autofit column widths on update” and “Preserve
cell formatting on update” checkboxes on the ‘Layout &
Format’ tab in the PivotTable Options dialog box
(PivotTable Tools>Analyze tab>PivotTable
group>Options>Layout & Format).

Figure 2-26: Autoformatting options on refresh

2.5.2 Deferring layout updates

By default, Excel regenerates a pivot table every time you
drag a field to or from a box in the PivotTable Fields task
pane. If pivot table calculations involve a large data source
or many nested fields, then refresh can be slow. You can
defer updating the pivot table while you make changes in
the PivotTable Fields window. To disable autorefresh,
68
select ‘Defer Layout Update’ checkbox at the bottom of the
PivotTable Fields task pane. When this setting is turned on,
Excel does not refresh the pivot table as you changed it. To
see the effects of your changes, refresh the pivot table
manually by clicking the Update button (next to the Defer
Layout Update checkbox), or clear the ‘Defer Layout
Update’ checkbox to go back to automatic refresh.

Figure 2-27: Defer Layout Update option

2.5.3 Delete your source data

After creating your pivot table, you can delete the source
data if you want to reduce the workbook file size. You can
delete your source data by deleting the sheet it is contained
on. To delete a source data, right-click on the sheet tab and
select DELETE from the menu. Your pivot table contains
a cache of the data so it will continue to work as normal. If
you want to see your data again, you can double click on
the grand total value of your pivot table and the data will
appear in a new sheet.

69
Project-1
1. Convert the data sources into Excel Table format.
2. Create a pivot table that summarizes the number
of employment opportunities created by
investment projects by types of business, zone,
sector, status, and origin. Interpret the pivot table.
3. Prepare a report that shows the number of
employees by education field and position.
Interpret the pivot table.
4. Prepare a report that shows the number of
employees by department and gender. Interpret
the pivot table.
5. Add a new hypothetical data of five employees
(five rows on HRMDATA) and Refresh (Refresh
All) your pivot tables in #3 and #4 above.

70
 Chapter 3 : Nesting PivotTable Fields

Focus points
• Adding nested fields.
• Showing or hiding levels in a nested field.

If you have more than one field in a PivotTable Fields area,

the PivotTable layout depends on the order you place
the fields in that area. This is called the Nesting order.
Nesting fields is a logical procedure of placing fields inside
another field. It works best for closely related fields such as
education level and education field (each education level
has an education field); zone and woreda (woredas are
structured under zones) and so on. This chapter introduces
about adding nested fields and the techniques how to show
and hide levels.

3.1 Adding nested fields

If you know how your data is structured, you can place the
fields in the required order. Each time that you add a new
field, Excel subdivides, or nests, the current fields.

To add additional (nested) fields to a pivot table:

• Select a cell in the pivot table. Excel shows the
PivotTable Fields task pane.
• In the PivotTable Fields task pane, drag fields from
the “Choose fields to add to report” list to the Rows

71
 or Columns boxes underneath. The order of fields
within a box determines their nesting order in the
pivot table.
Example 3.1: Generate a pivot table that shows the number
of employees by gender and marital status. Consider a pivot
table with the settings below. Please delete the new data you
have added in the source data in Project-2 (#5).

Rows: Gender, Marital Status (Marital Status is nested

under Gender).
Values: Gender (Summarized by Count).

Figure 3-1: The Nesting order

As shown in the figure above, there are two fields in the

rows area: Gender and Marital Status in that order. This

72
order of the fields is called nesting order (Gender first and
Marital Status next). Each row in this pivot table shows the
number of employees by gender and marital status count.
That is, Excel summarizes the data first by gender then by
marital status within a given gender.

You can drag a field to a different area (box) or reorder the

fields within a box by dragging a field up or down in a box
or by clicking the arrow to the right of the field label.

Table 3-1 shows the pivot table with nested field and for
instance, the number of married female employees are 272.

Table 3-1: Employees by gender and marital status

Note that excessive nesting can make a pivot table

uninformative.
Example 3.2: Generate a report that shows the number of
employees by department and education level. Consider a
pivot table with the settings below.

73
Rows: Department, Education Level (Education Level is
nested under Department).
Values: Education Level (Summarized by Count).

The pivot table below shows the number of employees by

department and education level. For example, the number of
employees with doctorate degree in the Research and
Development department are 30.

Table 3-2: Employees by department and education level

To reorder fields in a pivot table fields task pane boxes:

• Select a cell in the pivot table to show the PivotTable
Fields task pane.
• In the PivotTable Fields task pane, drag fields up or
down within a box, or click a field in a box and choose
Move Up, Move Down, Move to Beginning, Move to
End, and so on from the menu. For instance, you can

74
 click on a Gender field in the Columns box and you
get the options as shown in Figure 3-2.

Figure 3-2: Options to reorder fields

Exercise 3.1: Generate a report that shows the number of

employees by gender and position (nest position field under
gender field). Which position has the highest number of
female employees and lowest number of male employees?

Exercise 3.2: Generate a report that shows the number of

employees by gender and education field (nest education
field under gender). What is the number of male employees

75
who studied Marketing? Female employees who studied
Life Sciences?

Example 3.3: Generate a report that shows the number of

investment projects registered by status and zone. Consider
a pivot table with the settings below.

Rows: Status, Zone (Zone is nested under project status).

Values: Type of Business (Summarized by Count) to count
the number of investment projects registered.

Table 3-3 shows the pivot table with the number of

investment projects registered by their status and zone. For
instance, the number of investment projects under
implementation in East zone were 40 (cell B4) and the total
number of operational investment projects were 340 (cell
B10).

Table 3-3: Number of projects by status and zone

76
Example 3.4: Generate a report that shows the number of
investment projects by status and sector. Consider a pivot
table with the settings below.
Rows: Status, Sector (Sector is nested under Status of the
investment project).
Values: Type of Business (Summarized by Count).

The pivot table in Table 3-4 shows the number of

investment projects by their status and sector. For instance,
the number of investment projects under implementation in
financial services sector were 4.

Table 3-4: Number of projects by status and sector

Exercise 3.3: Generate a report which shows the number of

investment projects registered by sector and zone (nest zone
under sector).

77
Exercise 3.4: Generate a report which shows the number of
investment projects registered by origin and type of
business (nest type of business under origin).

3.2 Show or hide levels in a nested field

You can show (expand) or hide (collapse) individual levels
in nested rows or columns. In a pivot table that nests
education field within education level, for example, you can
show only the education fields in a specific education level
and hide the rest or show the education level and hide the
education fields (see Table 3-5).

Example 3.5: Consider a pivot table with the settings below

and practice to expand and collapse nested fields.
Rows: Education Level, Education Field (Education Field
is nested under Education Level).
Values: Gender (Summarized by Count).

As per Table 3-5, the Education Fields under the Bachelor

degree Education Level are expanded but other Education
Fields are collapsed (hidden). You can click on the plus (+)
sign to expand (show) the education fields under each
education level. For example, 16 and 59 employees hold
bachelor degree in Human Resources and Marketing fields
of studies respectively.

78
 Table 3-5: Employees by education level & field

Show or hide specific levels

To show or hide specific levels, do any of the following:
• Click the plus (+) or minus (-) icon next to the level
name in the pivot table (click again to toggle visibility).
• Double-click the cell containing the level name or
double-click again to toggle visibility.
• Right-click the cell containing the level name and
choose Expand or Collapse from the Expand/Collapse
submenu.

Show or hide all levels

To show or hide all levels, do any of the following:
• In the target field, right-click a cell containing a level
name and choose Expand Entire Field or Collapse
Entire Field from the Expand/Collapse submenu.

79
• In the target field, select a cell containing a level name
and choose PivotTable Tools>Analyze tab> Active
Field group >Expand Field (+) or Collapse Field (-)
(see Figure 3-3).

Figure 3-3: Expand and Collapse Field buttons

If you try expand an innermost nested level (say by double

clicking), Excel opens the Show Detail dialog box listing
all the fields not currently showing (see
Figure 3-4). If you select a field and click OK, Excel adds
another nested field to the pivot table.

Figure 3-4: The Show Detail dialog box

80
Show or hide (+/-) buttons
The show or hide (+/-) buttons allow you to expand or
collapse items within the PivotTable. In nested fields, if you
want to show or hide (+/-) buttons, click inside the pivot
table and choose Analyze tab>Show group>Click the +/-
Buttons (see Figure 3-5).

Figure 3-5: Show or hide buttons

Note that if you hide the show or hide (+/-) buttons, you can
still use the Expand Field and Collapse Field buttons
(Figure 3-3) to expand or collapse nested fields.

Example 3.6: Create a summary of employees by

education level, marital status and gender. Create a pivot
table with the following settings.
Rows: Education Level, Gender.
Columns: Marital Status.
Values: Gender (Summarized by Count).

81
To create a pivot table based on the settings above:
• Drag Education Level to the Rows box.
• Drag Gender to the Rows box (place it below
Education Level).
• Drag Marital Status to the Columns box.
• Then drag Gender to the Values box (Summarized by
Count).
Based on the table below, there were 77 married female
employees with Master degree, 125 divorced employees
with bachelor degree and so on. A pivot table also
calculates subtotals (for nested fields) and grand totals. The
grand total is in the bottom right corner (at cell E19), which
is 1470 in this case.

Table 3-6: Number of employees by education level,

marital status and gender

82
Exercise 3.5: Based on Table 3-6, nest marital status under
education level and move the gender field to the Columns
box. Keep the second instance of gender field in the Values
box as it was. Based on your pivot table, how many of the
married female employees hold a doctorate degree?
Example 3.7: Generate a report that shows the
employments opportunities of investment projects by zone
and sector. Consider a pivot table with the settings below.
Rows: Zone, Sector (Sector is nested under Zone).
Values: Employment (Summarized by Sum).

Based on the table below, in the East zone, the

manufacturing sector created the highest number of
employments which is 27541 and the number of
employments created in the West zone is 146172 which is
the highest compared to other zones.

Table 3-7: Number of employments by zone and sector

83
Exercise 3.6: Generate a report that shows the amount of
investment made by projects by zone and sector (nest sector
under zone).
Exercise 3.7: Which employee (by employee ID) gets the
highest monthly income in each department? (Nest
EmployeeID under department).

Project-2
Using a logical nesting order, prepare and interpret a pivot
table that shows:
1. The number of investment projects and amount of
investment by type of business and sector.
2. The employments created by sector and zone.
3. The number of employees by education level,
education field and gender.
4. The number of employees by department, position
and marital status.

84
 Chapter 4 : Formatting PivotTables

Focus points
• PivotTable Tools.
• PivotTable Styles.
• PivotTable Style Options.
• Layout.

After creating a PivotTable and adding fields that you want

to analyze; you may want to enhance the report layout and
format to make the data easier to present. To change the
layout of a PivotTable, you can change the PivotTable style
and the way that fields, columns, rows, subtotals, empty
cells, and lines are displayed. To change the format of the
PivotTable, you can apply a predefined styles and layouts.
This chapter introduces how to format pivot tables using
various styles, layouts and options in the PivotTable Tools.

4.1 PivotTable Tools

When you select a cell in a pivot table, the Ribbon generates
two new tabs (contextual tabs) under the PivotTable Tools
heading: Analyze and Design. The PivotTable Tools are
contextual that appear only when a cell within the pivot
table is selected. When these tabs appear, they will be
placed to the right of any existing tabs and shaded with a
different color from the rest. Contextual tabs are special
types of tabs that appear only when a particular object is

85
selected such as a chart, a shape, pivot table, pivot chart
and so on.

Figure 4-1: The Analyze and Design tabs

The Analyze tab has advanced features like custom

calculations, field settings, options, filtering, pivot charts,
and so on. The groups in the Analyze tab are PivotTable,
Action Field, Group, Filter, Data, Actions, Calculations,
Tools and Show. The Design tab contains most of the
layout and style options. It contains options that can change
the appearance of an active pivot table. The groups in the
Design tab are PivotTable Styles, PivotTable Style Options
and Layout.

By selecting the pivot table and clicking the Field Headers

option on the Analyze tab, you can on and off the default
field headers in a pivot table (Analyze tab>Show
group>Field Headers). You can experiment with it.

86
 Figure 4-2: Field Headers option

4.2 PivotTable Styles

The PivotTable Styles is located in the Design tab. If you
click the bottom down arrow with the line above it located
in the lower right of the PivotTable Styles section, all the
available options are displayed. Click a style to change the
colors and shading of the pivot table. The colors come from
the workbook theme that you are using. To use different
colors, choose Page Layout tab>Themes group>Themes.
To see different pivot table styles, please experiment with
different styles and themes.

Figure 4-3: PivotTable Styles

4.3 PivotTable Style Options

The PivotTable Style Options group is located to the left of
the PivotTable Styles group and it contain options for Row

87
Headers, Column Headers, Banded Rows and Banded
Columns as shown below.

Figure 4-4: PivotTable Style Options

• Rows Headers: Display special formatting for the first

row of the pivot table.
• Column Headers: Display special formatting for the
first column of the pivot table.
• Banded Rows: Display banded rows, in which even
rows are formatted differently from odd rows. This
banding can make pivot tables easier to read.
• Banded Columns: Display banded columns, in which
even columns are formatted differently from odd
columns. This banding can also make pivot tables easier
to read.

You can place check marks in various combinations of the

four options to see different pivot table styles. For instance,
if you click the Banded rows option, you can see how the
styles to the right immediately change. Experiment with
different combinations of checkboxes in order to find the
one that is right for your purpose. If you do not want
shading to alternate from one row or column to the next,

88
clear the Banded Rows or Banded Columns checkboxes. If
you do not want to apply the style formatting to headers,
clear the Row Headers or Column Headers checkboxes.

4.4 Layout
In the Layout group of Design tab, choose a preset option
that controls spacing and subtotals in a pivot table. It
contains options for Subtotals, Grand Totals, Report Layout
and adding Blank Rows between grouped items.

Figure 4-5: Layout options

4.4.1 Subtotals
Subtotals drop-down button show or hide subtotals at the
end of each level. This setting applies if fields are nested.
Otherwise, the levels’ “Subtotals” are actually “grand
totals”. When your pivot table has more than one
dimension, you can add or remove subtotals to make results
easier to understand. By default, Excel automatically
inserts subtotals for every field you place in the rows and
columns area, except for the last field. This means that if
you insert three fields in the rows area, the first two will
have automatic subtotals.

89
Let us look at the various subtotaling options to see how
you can change this behavior. First, make sure you select a
cell in your pivot table so that the PivotTable Tools tabs
appear. Select the Design tab. On the far left of this tab,
click Subtotals button to see the options as shown below.

Figure 4-6: Subtotals options

The Subtotals drop-down button contains the ‘Do Not

Show Subtotals’, ‘Show all Subtotals at Bottom of Group’
and ‘Show all Subtotals at Top of Group’ as demonstrated
below.

1. Do Not Show Subtotals

If this option is selected, all subtotals in the current report
will be removed.

Example 4.1: Consider Table 3-6. If you want to know how

many of the employees hold a bachelor degree, you can

90
easily answer this by looking at the subtotal at cell E4 which
is 572. Now if you do not want to see this subtotal, click
Design tab>Layout group>Subtotals arrow>Do Not Show
Subtotals and you get a pivot table as shown below.

Table 4-1: Number of employees by education level,

gender and marital status

Exercise 4.1: Consider Table 3-7 and remove the subtotals

from each zone and apply a Banded Columns style to the
report.

2. Show all Subtotals at Bottom of Group

This option will make the subtotal for each section appear
at the bottom of each group which results in extra rows in
your pivot table.

91
Example 4.2: Consider Table 3-7 again and show all the
subtotals at the bottom. If you select “Show All Subtotals at
Bottom of Group”, your report should update and look like
the one in the table below where Excel has inserted a label
for subtotal for each zone category and placed the subtotals
at the bottom of each section.

Table 4-2: Number of employments by zone and sector

Table 4-2 shows that the subtotal (which is the number of

employments created) for the East zone (cell B11) is 48020
and the subtotal for the North zone (cell B12) is 78402 and
so on.

Exercise 4.2: Consider Table 3-6 again and show all the
subtotals at the bottom of education level.

92
3. Show all Subtotals at Top of Group
If you prefer to have the subtotals at the top, select the
“Show all Subtotals at Top of Group” option.
Adding subtotals above the group results in the extra
information but without the extra rows (with a compact
layout).

Selectively Removing Subtotals

If you have multiple fields in the rows section and you
wanted a subtotal to display for one field but not the others,
it is possible to selectively remove subtotals.

Example 4.3: Generate a report on the number of

employees by education level, gender and position.
Consider the following scenario.
Rows: Education Level, Gender, and Position in order.
Columns: Gender.
Values: Gender (Summarized by Count).

Now the pivot table report below has two subtotal levels:
one for education level and one for the gender. For instance,
the subtotal for Bachelor education level is 572 and the
subtotal for females and males under the Bachelor
education level is 235 and 337 respectively (= 235 +
337 = 572). The layout is quite confusing. Remember that
Excel automatically adds subtotals to all fields except for
the last one (Position field in this case).

93
 Table 4-3: Number of employees by education level,
gender and position

Let us assume that you want to keep the subtotal for each
Education Level field, but not for Gender field. To do this,
click one of the labels in the pivot table that is associated
with the field for which you want to remove subtotals. In
this case, select the Gender (Female), found in cell A4 (see
Figure 4-7). Right-click this cell, and from the menu, click
Subtotal “Gender”.

94
 Figure 4-7: Options to remove subtotal for Gender field

The check mark next to the Gender signifies that a subtotal

was active for this field. If you click on it, the check mark
disappears and the subtotal for the Gender field in the pivot
table will also be removed. This options acts like a toggle
(on and off) and if you need to reactivate the subtotal for
Gender, repeat the steps above.

4.4.2 Grand Totals

The next option on the Design tab is Grand Totals. Grand
Totals show or hide totals at the end of each row or column.
If you click the Grand Totals option on the tab, you notice
four options. The options in the Grand Totals are ‘Off for
Rows and Columns’, ‘On for Rows and Columns’, ‘On for
Rows Only’ and ‘On for Columns Only’.

95
 Figure 4-8: Grand Totals options

1. Off for Rows and Columns

When this option is selected, all grand totals are removed.
The table below shows a pivot table where grand total is off
for rows and columns.

Table 4-4: Grand Totals off for rows and columns

2. On for Rows Only

When this option is selected, only the grand total for the
rows is displayed. The table below shows a pivot table

96
where grand total is on for rows only. Note that the count
of Gender in the table below refers to the number of
employees in each education level.

Table 4-5: Grand Totals on for rows only

3. On for Columns Only

When this option is selected, only the grand total for the
columns is displayed. Table 4-6 shows a pivot table where
grand total is on for columns only.

Table 4-6: Grand Totals on for Columns only

4. On for Rows and Columns

When this option is selected, the rows and the columns will
have grand totals.

97
 Table 4-7: Grand Totals on for rows and columns

4.4.3 Report Layout

To change the layout of your pivot table, select Report
Layout button under the Layout section in the Design tab
and. When you select it, it displays a listing of options
available (see Figure 4-9).

Figure 4-9: Report Layout options

98
1. Show in Compact Form
By default, Excel builds all pivot tables in compact form:
all row labels are merged into a single column and this
means that the values are stacked on top of each other with
the next level below indented. After you prepare a pivot
table, choose the ‘Show in Compact Form’ and you get the
report which looks like as shown below.

Table 4-8: Report layout in compact form

2. Show in Outline Form

In outline form, each row label gets its own column (which
occupies much more space than the compact layout). After
you prepare a pivot table, choose the ‘Show in Outline
Form’ and you get the report which looks like as shown
below.

99
 Table 4-9: Report in outline form

3. Show in Tabular Form

Tabular form is like outline form but shows subtotals (extra
rows) at the bottom of each level or group. After you
prepare a pivot table, choose the ‘Show in Tabular Form’
and you get a report which looks like the one below.

Table 4-10: Report layout in tabular form

4. Repeat All Item Labels

In a tabular layout, you can also repeat labels to show
values of nested fields in all row and column labels. By
default, a pivot table shows the field label and blank cells

100
underneath for all other sub-fields included in the field
heading. Creating a Tabular Form layout with Repeat All
Item Labels is a great way to create another set of more
aggregated “Source Data” that you can copy and paste as
values and use elsewhere. After you prepare a pivot table,
choose the ‘Repeat All Item Labels’ and you get a report
which looks like as shown below.

Table 4-11: A report layout with repeat all item labels

If you change your mind, you can select Design tab>Layout

group>Report Layout > Do Not Repeat Item Labels and
they disappear.

Note that it is not recommended to repeat item labels unless

necessary.

101
4.4.4 Blank Rows
Blank rows option show or hide blank lines between levels
or groups. This option applies only if the Rows box
contains more than one field. Basically, you can choose to
insert blank lines after each item or remove the blank lines
after each item. By default, no blank lines have been
inserted after each item. To insert a blank line after each
item, go to the Design tab of the Ribbon and click on
the Blank Rows button in the Layout section then
select Insert Blank Line after Each Item. A blank line is
now inserted between each item. To switch it back,
click Design tab>Layout group>Blank Rows > Remove
Blank Line after Each Item.

Figure 4-10: The blank rows option

Table 4-12 shows the inserted blank lines after each item.
You just see one blank line (row 6) in the table below,
others are hidden as all Education Levels (except Bachelor)
are collapsed.

102
 Table 4-12: Report with blank lines inserted after items

4.4.5 Merge and center cells with labels

You can merge cells for row and column items in order to
center the items horizontally and vertically, or to unmerge
cells in order to left-justify items in the outer row and
column fields at the top of the item group.

To merge and center cells with labels:

• Click anywhere in the PivotTable. This displays
the PivotTable Tools tab on the Ribbon.
• Click Analyze tab>PivotTable group>Options.
• In the PivotTable Options dialog box, click the
Layout & Format tab, and under Layout, select or
clear the ‘Merge and center cells with labels’ check
box. If it is checked, it allows you to Merge and center
cells with labels.
• Click OK.

103
You notice the cell contents are now centered both
horizontally and vertically within the cell.

Figure 4-11: Layout & Format tab

4.4.6 Showing zeros in empty cells

If the “value” of a pivot table cell is zero, Excel shows as
an empty cell instead of zero. To show zeros in a pivot
table, right-click a cell in the pivot table and choose
PivotTable Options. -OR- Choose PivotTable
Tools>Analyze tab >PivotTable group>Options. On the
Layout & Format tab, clear the “For empty cells show”
checkbox (see Figure 4-11). If there are blank cells in a
pivot table and if you want the blank cell to contain a value

104
of zero, check the ‘For empty cells show’ checkbox and
type 0 in the blank box.

4.4.7 Show items with no data

By default, pivot table do not display items with a count of
zero. You can change this so that you see all items even
when there is no data.

To show items with no data:

• Right-click an item in the pivot table field, and click
Field Settings.
• In the Field Settings dialog box, click the Layout &
Print tab.
• Check the ‘Show items with no data’ check box.
• Click OK.

105
 Figure 4-12: Field Settings options

Note that Field Settings are settings that you can apply to
an individual field on your pivot table which are accessible
for row and column label fields.

Project-3
Based on Project-3, practice with:
1. All Subtotals options.
2. All Grand Totals options.
3. All Report Layout options.
4. Insert Blank Line after Each Item.
5. Remove Blank Line after Each Item.

106
 Chapter 5 : Grouping in PivotTables

Focus points
• Grouping by selected items.
• Grouping by time periods.
• Grouping by numbers.
• Multiple groups from the same data source.

One of the useful features of a pivot table is the ability to

combine related items into groups. You can group items
that appear as Row or Column labels. Grouping data in a
PivotTable can help you show a subset of data to
summarize and analyze. It is important especially for
creating frequency tables which depicts ranges of data with
its number of occurrences (frequencies). This chapter
introduces grouping by selected items, grouping by time
periods, grouping by numbers and creating multiple groups
from the same data source.

5.1 Grouping by selected items

Grouping in pivot table shows items (text data) or values in
logical groups for ease of performing data analysis.

To create a group:
• Select items in the pivot table that you want to group.
• Choose PivotTable Tools>Analyze tab>Group
section>Group Selection.

107
• -OR- Right-click a selected cell and choose Group from
the menu.

Note that to select adjacent cells, click the first cell press
the SHIFT key and click the last cell. To select nonadjacent
cells, hold CTRL and click each cell.

Example 5.1: Generate a report on the amount of

investment and number of employments created by sector.
Consider a pivot table with the settings below and group the
selected items.
Rows: Sector.
Values: Amount of Investment (Summarized by Sum) and
number of Employment (Summarized by Sum).

Based on the above pivot table setting, you get the result
below.

Table 5-1: Amount of investment and number of

employments by sector

108
Note that the report layout of Table 5-1 is in outline form
and the Column Labels (Columns B and C) are renamed as
Amount of investment and Number of employments
respectively.

Let us assume you want to compare the performances of

service sectors against non-service sectors. The goal here is
to create two groups of sectors: Service and Non-service
sectors. One solution is to add a new column Category
(Service and Non-service sectors) to the source data and
enter the category for each sector. In this case, it is easier to
create groups directly in the pivot table.

For the sake of demonstration, the following category is

assumed. Service sectors: Electricity and Water Supply,
Transport and Communication, Social Services and
Financial Services. Non-service sectors: Agriculture,
Construction, Manufacturing and Wholesale and Retail
Trade.

To group service sectors:

• In the pivot table, press CTRL key and click on
Electricity and Water Supply, Transport and
Communication, Social Services and Financial
Services.
• Right-click the selected items. Choose Group. A new
group (Group1) is created and the first column is
renamed as Sector2. As a result, the selected range will

109
 be combined into a single group. Change Group1 to
Service Sectors.

To group non-service sectors:

• Press CTRL key and click Agriculture, Construction,
Manufacturing and Wholesale and Retail Trade.
• Right-click the selected items. Choose Group. A new
group (Group2) is created. Change Group2 to Non-
Service Sectors.

By default, the grouped name for a set of items will be

Group1, Group2, Group3, etc. But you can change these to
more meaningful names. Excel also creates a new virtual
field named Sector2 in the Rows box. This is the newly
created grouped virtual field and you can use it just like any
other field in your data. You can move it to the Filters,
Columns areas or remove it completely from the pivot
table.

Note that removing virtual fields from the pivot table will
not ungroup the field. To ungroup data in a pivot table,
right-click the grouped name and select Ungroup from the
menu.

110
 Figure 5-1: PivotTable with virtual name of Sector2

You can rename the Sector2 field in Figure 5-1 as follows:

• Click on Sector2 in the pivot table fields.
• Select Fields Settings
• In Custom Name box change the name from Sector2 to
‘Classification of Sectors.’

As mentioned, the source data is fictional and do not pay

too much attention to the numbers rather try to customize
and apply the skills you acquired to your actual data on
hand.

111
Table 5-2: Amount of investment and number of
employments by categories of sectors

You can move the new field (Classification of Sectors) to

Columns or Filter areas. If you remove the Sectors field
from the Rows area in the pivot tables, you get a pivot table
as shown below.

Table 5-3: Classification of Sectors

Pivot tables allow easy comparison between two groups.

For instance, based on Table 5-3, the number of
employments created by non-service sectors and service
sectors were 399662 and 33176 respectively.

5.2 Grouping by time periods

When a field contains time periods, you can create groups
that summarize data by months, quarters, years, and so on.

112
For instance, you can use the Employment Date (in
HRMDATA) and Registration Date (InvestmentData)
fields to summarize employment date of employees or
registration of investment projects by month, quarter, etc.

Example 5.2: Generate a report on the number of

employees grouped by employment date. Consider a pivot
table with the settings below.
Rows: Employment date.
Values: Gender (Summarized by Count).

Table 5-4: Number of employees by employment date

Grouping dates works a little differently than grouping

items in a field. When you add a date field into either the
Rows or Columns area, Excel will assume you probably
want to view the data by Month, Quarter or Year and
will automatically group the dates like this.

113
If you have added the Employment Date into the rows area,
you can see it has been grouped by year, quarter and month
as shown in the Figure 5-2.

Figure 5-2: The Years, Quarters and Employment Date

If you want the view by date, you need to right-click on the

date values in the pivot table and choose Ungroup from the
menu and you get a pivot table as partially shown below.

Table 5-5: Employment date by gender (ungrouped)

In the pivot table (see Table 5-5), right-click a cell in the

employment date (rows) and select Group from the menu.
The Grouping dialog box opens (see Figure 5-3). In the By

114
list, select Months and Years. Verify that the Starting and
Ending dates are correct (based on the source data) and
click OK. The employment date items in the pivot table are
grouped by years and months. Excel also creates a new
virtual field name ‘Years’ in the Rows box, which you can
pivot on (drag Years to the Columns box, for example).

To show months and years in a pivot table:

• Select a cell in the date column
• Go to PivotTable Tools>Analyze tab>Group
section>Group Selection.
• In the Grouping dialog box, select Months and Years.

Figure 5-3: Grouping by months and year dialog box

You get the result as shown below.

115
Table 5-6: Employment date by gender (grouped by months
and years)

Based on the table above, the number of employees hired

during January 2012 were 4.

To remove the grouping:

• Right-click a cell in the employment date (rows) and
choose Ungroup.

Note that if you select only Months (and not Years) in the
By list, then months in different years are combined. The
Jan item for example would show the combined quantities
for 2012 to 2016. Try this by yourself.

116
Exercise 5.1: Generate a report on the number of
investment projects by registration date. Consider a pivot
table with the settings below and group by months and
years. In which month of each year, the highest number of
investment projects were registered?
Rows: Registration date.
Values: Type of Business (Summarized by Count).

5.2.1 Grouping by weeks

You can also group date by week (or any fixed number of
days).

Example 5.3: Consider a pivot table with the settings below

and group by weeks.
Rows: Registration date.
Values: Type of Business (Summarized by Count).

Right-click the first column in the pivot table you created

and click Group. In the Grouping dialog box (see Figure
5-4), select only Days in the By list and type 7 in the
“Number of days” box. You can clear the “Starting at”
check box and adjust the start date to fall on the first day
(typically, Sunday or Monday) of the first week of interest.
If you like, adjust the end date too. Click OK. Each row in
the resulting pivot table shows the start and end dates of
each week.

117
 Figure 5-4: The Grouping dialog box

Table 5-7: Number of investment projects by registration

date (weeks)

118
Based on the table above, the largest number of investment
projects, 181, were registered during week the 8/26/2016 to
9/1/2016.

Exercise 5.2: Consider a pivot table with the settings below

and group by weeks. During which week the largest number
of employees were hired?
Rows: Employment date.
Values: Gender (Summarized by Count).

5.3 Grouping by numbers

You can group by numbers to create a frequency table or
distribution, where each entry in the pivot table contains the
frequency (count) of the occurrences of values within a
particular group or interval.

Example 5.4: Consider a pivot table with the settings below

and create a frequency distribution for age ranges. A
frequency distribution, in this case, is table that shows age
ranges and the number of employees within each age range.
Rows: Age.
Values: Gender (Summarized by Count).

The pivot table shows the age and the corresponding

number of employees. If you want to determine how many
employees were in each 10-point age range (18-27, 28-37,
and so on), in the pivot table, right-click a cell in the age
row and select Group from the menu (see Figure 5-5).

119
 Figure 5-5: Right-click options in a Pivot table of
employees by age

The Grouping dialog box opens (see Figure 5-6). In the By

box, type the size of the interval (increment) for each group
(10 in this case). Based on the source data, verify that the
starting and ending points are correct and click OK.

120
 Figure 5-6: The Grouping dialog box with age interval

The age items in the pivot table (see Figure 5-7) are grouped
in uniform intervals (bins). The groups start at 18 and end
at 60, in increments of 10. A frequency distribution and a
column chart are shown in the Figure 5-7. To remove the
grouping, right-click a cell in the row label and choose
Ungroup.

Figure 5-7: A pivot table by age range and a pivot chart

The ranges 18-27, 28-37, 39-47 and so on are called classes

or categories and the pivot table in the Figure 5-7 has five
classes and the highest number of employees, 638, are
within in the age range of 28-37 years. By default, pivot
tables do not display items with a count of zero. To make
sure that your frequency distribution has no gaps between

121
intervals select a cell in the interval column and choose
PivotTable Tools>Analyze tab>Active Field group>Field
Settings> Layout and Print >Select “Show Items with no
data.”

Note that if you get the “Cannot group that selection” error
message when trying to group pivot tables, it is most likely
because your data is invalid in some way. This was most
likely a data entry error. For instance, Pivot tables will not
allow you to group dates if there are any invalid dates
within the data source. So, if you get this error message,
inspect your source data.

5.4 Multiple groups from the same data source

If you create multiple pivot tables from the same data
source, you may have noticed that grouping a field in one
pivot table affects the other pivot tables. Specifically, all the
other pivot tables automatically use the same grouping.
Grouping affects other pivot tables and to resolve this issue,
it is recommended to use multiple range names to the
source data. For example, name your source range Table1
and give the same range a second name: Table2. The easiest
way to name a range is to use the Name box, to the left of
the Formula bar. Select the range, type a name in the Name
box, and press ENTER. Then, with the range still selected,
type a different name and press ENTER. Excel displays
only the first name, but you can verify that both names exist
by choosing Formulas tab>Define Names group>Name

122
Manager. When you create the first pivot table, specify
Table1 as the Table/Range. When you create the second
pivot table, specify Table2 as the Table/Range. Each pivot
table treat them as different files, and you can create groups
in one pivot table, independent of the other pivot table. You
can also use this method with existing pivot tables. Make
sure that you give the data source a different name. Then
select the pivot table and choose PivotTable
Tools>Analyze tab>Data group>Change Data Source. In
the Change PivotTable Data Source dialog box, type the
new name that you gave to the range. This will cause Excel
to create a new pivot cache for the pivot table.

Note that pivot cache is something that automatically gets

generated when you create a pivot table. It is an object that
holds replica of the data source. While you cannot see it, it
is a part of the workbook and is connected to the pivot table.

Project-4
Prepare and interpret a pivot table report that shows:
1. Ranges of investment by number of investment
projects with an increment ETB 5,000,000.
2. Ranges of employment by number of investment
projects with an interval of 1000.
3. Monthly income range (intervals) by the number
of employees with an interval of ETB 1,000.

123
 Chapter 6 : Calculations in PivotTables

Focus points
• Summarize Values By variety of statistics.
• Show Values As compared to other values.
• Creating calculated fields.
• Referencing cells in a pivot table.

When you add a field to the Values box in the PivotTable

Fields task pane, Excel (in most cases) sums all the values
in that field. However, you can calculate other common
statistics such as count, average, maximum (max),
minimum (min), variance, standard deviation, and so forth
on a field. You can also perform multiple calculations (on
the same field or different fields) in the same pivot table
and create custom formulas. Moreover, if you want to
compare one data value to another, you can use the Show
Values As in pivot table. This chapter demonstrates how to
‘Summarize Values By’ variety of statistics, ‘Show Values
As’ compared to other values and create custom formulas.
Besides, the referencing cells in a pivot table using the
GetPivotData function is briefly demonstrated.

6.1 Summarize Values By

Pivot table data is most frequently summarized using a sum
function. However, you can display your data using a
number of different summary techniques specified in the

124
Value Field Settings dialog box. To display Value Field
Settings, right-click any value in the pivot table and choose
Value Field Settings from the menu. The Value Field
Settings has two tabs: ‘Summarize Values By’ and ‘Show
Values As’ as shown below.

Figure 6-1: The Value Field Settings (Count option)

Note that Value Field Settings are accessible for only value
fields.

You can use the ‘Summarize Values By’ tab to select

different summary functions such as Sum, Count, Average,
Max, Min, Product, Count Numbers, Standard Deviation
and Variance. In the ‘Summarize value field by’ box (see
the above figure), choose the type of calculation that you
want to use to summarize data from the selected field.

125
Example 6.1: Prepare a report that shows maximum
monthly income by education level and department. To
choose a preset calculation, consider the following settings.
Rows: Education Level.
Columns: Department.
Values: Monthly Income (Summarized by Max).

To prepare a pivot table with the above settings:

• Select a cell in the pivot table. Excel shows the
PivotTable Fields task pane.
• Click the target field (“Sum of Monthly Income”, for
example) in the Values box and choose Value Field
Settings from the menu.

Figure 6-2: Value Field Settings selected

126
The Value Field Settings dialog box opens and you can
choose the type of calculation that you want to use to
summarize data from the selected field. For this example,
choose “Max” from the list in the “Summarize value field
by” tab (see Figure 6-3).

Figure 6-3: The Summarize value field by (Max)

-OR-Right-clicking a value in the monthly income field of

your pivot table provides a menu for the ‘Summarize Values
By’ (see Figure 6-4). A menu appears showing the various
options. The Max option has a check mark next to it,
denoting that the field is currently using that option.

127
 Figure 6-4: Summarize Values By menus

To change a field name, do the following:

• Right-click any value within the target field.
• Select Value Field Settings as shown Figure 6-1.
• Enter the new name in the Custom Name input box.
• Click OK to apply the change-OR-Click Analyze
tab>Active Field group>change or customize the
name of the field in the Active Field: box.

Note that the custom name changes only in the pivot table,
it remains intact in the source data.

128
Format the field’s value
To format the field’s values:
• Click the Number Format (see Figure 6-3).
• Choose or define a new number format, and click OK.
-OR-Right-Click on any number in that field and
select Number Format from the menu (see Figure
6-5). You can change the number of decimal places,
add a currency symbol, and so on to all fields
simultaneously.

Figure 6-5: The Number Format from right-clicking

The Format Cell dialog box (only with Number tab) opens
as shown below.

129
 Figure 6-6: Format cells option

• Click OK to close the Value Field Settings dialog box.

Excel refreshes the pivot table with the new
calculations.

Do not select Format Cells option (see Figure 6-5)

because that format only the selected cell. The ‘Number
Format’ applies to all the fields in the pivot table and it
stays with your pivot table as it expands.

130
Accordingly, the table below shows the summary report for
the maximum monthly income by education level and
department.

Table 6-1: Maximum monthly income by education level

and department

Therefore, for instance, the maximum monthly income for

master degree holders in the Sales department is ETB
19,833.00.

Exercise 6.1: Prepare a report that shows the minimum

monthly income by education level and department.
Exercise 6.2: Prepare a report that shows the maximum
investment by sector and zone. What is the maximum
investment by Electricity and Water Supply sector in East
zone?

6.1.1 Calculation of multiple statistics

When you add multiple fields to the Values box, each field
is calculated and shown in a separate column in the pivot
table. You can summarize one field by average and the

131
other by using the Max or Min statistics. Besides, you can
do multiple calculations on the same field. For instance, to
count and calculate the percentage (%) of Grand Total for
the Gender field, drag Gender field into the Values box
twice and configure the two Gender fields separately.

Example 6.2: Calculate the average monthly income and

average years of experience by gender. Consider a pivot
table with the settings below.
Rows: Gender.
Values: Monthly Income and Years of Experience
(Summarized by Average).

To create a pivot table based on the above settings:

• Drag Gender field into the Rows box.
• Drag Monthly Income and Years of Experience fields
into the Values box.
• Using the Value Field Setting, choose Average for
monthly income and Average years of experience one
by one.

Table 6-2: Average income and average years of

experience by gender

132
Based on the above table, the average monthly income for
female and male employees was ETB 6,686.57 and ETB
6,380.51 respectively.

6.2 Show Values As

If you want to compare one data value to another, you can
use the Show Values As in a pivot table. You have many
options to choose from including % of Grand Total, % of
Column Total, % of Row Total, % Of, % of Parent Total,
% of Parent Row Total, % of Parent Column Total,
Difference From, % Difference From, Running Total In, %
Running Total In, Rank Smallest to Largest and Rank
Largest to Smallest.

You can access Show Values As feature in one of the

following ways.
• Right-clicking a value in your pivot table provides a
menu for the Show Values As with a list of sub-menus
(see Figure 6-7).
• In the sub-menu you can select from different
calculation options (see Figure 6-7).

Note that by default, the ‘No Calculation’ option has a

check mark next to it, denoting that it is active.

133
 Figure 6-7: The Show Values As calculation options

You can also access the ‘Show Values As’ through the
Value Field Settings menu. To do so,
• Click anywhere inside the values area in a pivot table.
• Click the Analyze tab> Active Field group >Field
Settings.
• In the ‘Value Field Setting’ dialog box, click the ‘Show
Values As’ button and you see the different calculation
options
• -OR-Right-click anywhere on the values area in the
pivot table.
• Select Value Field Settings.
• Click on the Show Values As tab.

134
 Figure 6-8: The Show Values As in the Value Field
Settings

The Show Values As presents values in different ways with

several new calculation options which are demonstrated
below.

6.2.1 % of Grand Total

You can use the ‘% of Grand Total’ calculation to compare
each value to the overall total. Each percentage represents
the percentage of that value to the total. For instance, what
is the percentage of female employees in an organization as
compared to the total employees?

Example 6.3: Calculate the number and percentage of

employees by gender. Consider a pivot table with the
settings below.

135
Rows: Gender.
Values: Gender (Summarized by Count) and Gender
(Summarized by % of Grand Total).

To create a pivot table:

• Drag Gender field into the Rows box.
• Drag Gender field into the Values box twice.

Each time you add the field to the Values area, it gets a
sequential number added to the end and you can change it
later. When you added the first instance of Gender to the
Value section, Excel labeled it Count of Gender. For the
second instance, it labeled it as Count of Gender2. You can
rename these labels to Number of employees and % of
Grand Total as shown below. Then, right-click a value in
the gender field and choose the Show Values As> the % of
Grand Total.

Table 6-3: Number of employees and % of Grand Total

by gender

The % of grand total show the percentage for each gender,

compared to the Grand Total of employees. Thus, based on
Table 6-3, there were 588 female employees which is 40%

136
of the total employees. The rest, 882 (60%) were males. The
percentage of male and female employees adds up 100%.

Note that you can change the field heading, e.g., to ‘number
of employees’ or to ‘% of grand total’ just to make the data
easier to understand.

Example 6.4: Generate a report on the number and

percentage of employees with monthly income ranges.
Consider a pivot table with the settings below.
Rows: Monthly income (group to show income ranges).
Values: Monthly income (Summarized by Count) and
Monthly income (Summarized by % of Grand Total).

This pivot table uses three instances of a single field:

Monthly income. When you initially added the Monthly
income field to the Rows section, the pivot table showed a
row for each unique monthly income.

To group the values:

• Right-click one of the amounts and choose Group
from the menu.
• In the Grouping dialog box, set up bins of 1000
increments.

The second instance of the Monthly income field (in the

Values section) is summarized by Count (right-click a value
and choose Summarize Data By>Count.

137
Note that the count here refers to the number of employees
falling within each income range.

Another instance of Monthly income is added to the Values

section and it is set up to display the percentage (right-click
a value in column C and choose Show Values As > % of
Grand Total. This percentage shows the proportion of
employees falling within each income range which is
calculated as follows:
= 𝑛𝑢𝑚𝑏𝑒𝑟 𝑜𝑓 𝑒𝑚𝑝𝑙𝑜𝑦𝑒𝑒𝑠 𝑖𝑛 𝑒𝑎𝑐ℎ 𝑖𝑛𝑐𝑜𝑚𝑒 𝑟𝑎𝑛𝑔𝑒/
𝑇𝑜𝑡𝑎𝑙 𝑛𝑢𝑚𝑏𝑒𝑟𝑜𝑓 𝑒𝑚𝑝𝑙𝑜𝑦𝑒𝑒𝑠).

Table 6-4: Ranges of monthly incomes (by number of

employees and % of Grand Total)

138
Based on the table above for example, 37 employees
(2.52%) fall within a monthly income range of ETB 1009
to 2008 and 50 employees (3.4%) fall within a monthly
income range of ETB 19009 to 20008.

Example 6.5: Generate a report on the number and

percentage of employees by education level and gender.
Consider a pivot table with the following settings.
Rows: Education Level, Gender (where Gender is nested
under education level).
Values: Gender (Summarized by Count) and Gender
(Summarized by % of Grand Total).

Table 6-5: Number of employees and % of grand total by

education level and gender

139
Based on the above table, there were 572 (39%) employees
with Bachelor degree, 170 (12%) employees who hold
Below College education level, 282 (19%) employees who
have a college education level and so on.

6.2.2 % of Column Total

The ‘% of Column Total’ displays all the values in each
column as a percentage of the total for that column. When
this is selected, each column total shows as 100% and all
the values in each column add up to 100% including the
Grand Total column.

Example 6.6: Prepare a pivot table that displays the % of

employees by gender and department (use the % of column
total where the column label is the Gender field).
Steps:
• Drag Department field to the Rows area.
• Drag Gender field to the Columns area.
• Drag Gender field to the Values area (Summarized by
Count).
• Right-click on the values area in the pivot table, and
click Show Values As>click % of Column Total.

140
 Table 6-6: Percentage of employees by gender and
department (% of column total)

The above table shows the percentage of employees within

the Gender and Grand Total columns. For example, 64.46%
(= 379/588) of the female employees and 65.99% (=
582/882) of the male employees were working in the
Research and Development department. Overall, 65.37% of
the employees were working in this department.

6.2.3 % of Row Total

The ‘% of Row Total’ displays the value in each row as a
percentage of the total for that row. When this is selected,
each row total shows as 100% and all the values in each
row add up to 100% including the Grand Total row.

Example 6.7: Based on Example 6.6, prepare a pivot table

that displays the % of employees by gender and department
(use the % of row total where the Row label is the
Department field). To do this, follow all the steps as in
Example 6.6 and click % of Row Total instead of the % of
Column Total.

141
Table 6-7 shows the percentage of employees for each
gender, across each department. For example, in the Human
Resource department, 31.75% (= 20/63) of the employees
were females and 68.25% (= 43/63) of the employees
were males.

Table 6-7: Percentage of employees by gender and

department (% of row total)

6.2.4 % Of
The ‘% Of’ calculation lets you compare all amounts to a
specific amount. It displays values as a percentage of the
value of the base item in the base field. Base Fields are any
fields in the source data whereas base items are actual
values of the fields. For instance, registration date is a field
and actual years (2016, 2017, and 2018) are items.

Example 6.8: Based on Example 6.6, prepare a pivot table

that shows the % Of Male employees. In this example,
employees in each department will be compared to the
Male employees of the same department, as a percentage.
Here the base field is Gender and the base item is Male.

142
Steps:
• Right-click one of the cells in the Values area.
• Click Show Values As>click % Of.
• From the base field list, choose Gender.
• From the base item list, choose Male.

Figure 6-9: Base field and base item for % Of calculation

• Click the OK.

Table 6-8: % Of employees compared to male employees

Note that the base field always shows as 100%, because it

is being compared to itself.

The pivot table shows each department’s employees by

gender as percent of the male employees total in each
department. For example, there were 20 female employees

143
in the Human Resources department and 43 male
employees in the same department and the % Of female to
male employees is 46.51% (= 20/43). Overall, female
employees are 66.67% (= 588/882) of the male
employees.

Example 6.9: Based on Example 6.6, prepare a pivot table

that shows the % Of Female employees. In this case,
employees in each department will be compared to the
Female employees of the same department, as a percentage.
Here the base field is Gender and the base item is Female.
Steps:
• Right-click one of the cells in the Values area.
• Click Show Values As>click % Of.
• From the base field list, choose Gender.
• From the base item list, choose Female.
• Click the OK.

Table 6-9: % Of employees compared to female

employees

The pivot table changes to show each gender as percent of

female employees in each department. For example, in the

144
Sales department, there were 189 female employees and
257 male employees. The % Of male employees as
compared to female employees in this department was
nearly 136% (= 257/189). The percentages in Table 6-9
shows that there were more male employees as compared
to female employees.

Example 6.10: Based on a new investment data source

(where some more investment projects are added in 2018),
create a pivot table based on the following settings and
compare the % Of investment projects registered in each
year as compared to the previous year. Select Base Field:
Years and Base Item: Previous.
Rows: Zone.
Column: Registration Years.
Values: Count of Zone (the number of projects registered).

Figure 6-10: Number of investment projects by zone and

year where base field and base item are selected.

145
 Table 6-10: Number of investment projects by zone and
year (% Of previous year)

Obviously, the % Of previous year for 2016 is 100%

because there is no previous year for 2016 in this case. As
indicated in the above table, there were 135, 228 and 315
investment projects registered in 2016, 2017 and 2018
respectively. If you take base item as the previous year, that
is, 2016 is the previous year for 2017 and 2017 is the
previous year for 2018. So, the % of investment projects
registered in East Zone were 168.89% in 2017 as compared
to 2016. This implies that from 2016 to 2017, the number
of investment projects increased by 68.89% (=
168.89% − 100%). The percentage of investment projects
in 2018 as compared to 2017 was 138.16% where the
change was 38.16%.

If you want to compare the percentage change of

investment projects registered in 2017 and 2018 against a
base year (base item) of 2016, change the base item to 2016
as shown in the Figure 6-11.

146
 Figure 6-11: Number of investment projects by zone and
year where base field and base item are selected.

You get the following result as shown below.

Table 6-11: Number of investment projects by zone and

year (% Of year 2016)

As indicated in the above table, in East zone, the percentage

of investment projects in 2017 and in 2018 as compared to
2016 (base-year) was 168.89% and 233.33% respectively.
You can also perform many other meaningful analyses
based on the above data.

147
6.2.5 % of Parent Total
The ‘% of Parent Total’ option allows you to select a base
field for the calculations and computes the % of each items
value against the total of the parent field (base field).
Steps:
• Right-click on the values area in a pivot table.
• Click Show Values As>% of Parent Total.
• From the base field list, choose Status (which is the
Parent).

Example 6.11: Create the pivot table based on the

following setting and calculate the % of Parent Total.
Rows: Sector nested under Status.
Values: Sector (Counted) and Sector (as % of Parent Total).

Table 6-12: The number of projects and % of Parent Total

Table 6-12 shows the percentage of the number of projects,

compared to the parent’s total for each Status and each

148
Sector. For instance, 1.54% (= 3/195) of the projects in
the agriculture sector were under implementation status.

6.2.6 % of Parent Row Total

If a parent field is in the Rows area, you can use the ‘% of
Parent Row Total’ option to show each item’s percentage
of its parent field's subtotal.

Example 6.12: Create the pivot table based on the

following setting and calculate the % of Parent Row Total.
Rows: Sector nested under Status.
Values: Sector (Counted) and Sector (as % of Parent Row
Total).

Steps:
• Right-click on the Values area.
• Click Show Values As>click % of Parent Row Total.

Table 6-13: Number of projects & % of Parent Row Total

149
Note that Parent Rows Total implies the parent field is a
rows label.

Table 6-13 shows the percentage for each sector’s number

of projects, compared to the parent row total. The first
parent is the status “Implementation” and therefore, to get
the % Parent Row Total to all the sectors under it, divide
the number of projects in each sector by the parent total
(row total). However, to get the % Parent Row Total for the
‘Implementation Total’ status, you should divide it to the
Grand Total (as its parent is the grand total). For instance,
68.21% (= 133/195) of the projects in the manufacturing
sector were under implementation status and overall, 5.01%
(= 195/3889) of the projects were under implementation.

6.2.7 % Of Parent Column Total

You can use the ‘% of Parent Column Total’ option to show
all values in each row as a percent of its parent column. Use
this custom calculation if the parent field is in the Column
area. The steps to calculate the % Of Parent Column Total
is the same with the % of Parent Row Total.

6.2.8 Difference From

The ‘Difference From’ calculation displays values as the
difference from the value of the base item in the base field.
This measures the change in value of an items based on a
base field (say Years). Select the Difference From option
to show all values as the difference between the current

150
item and previous item, next item or a fixed item’s value.
This custom calculation is used to subtract one pivot table
value from another, and show the result. For example, if
you want to measure the difference (variance) of a plan vs
actual, use the ‘Difference From’ calculation.

Example 6.13: Create a pivot table based on the settings

below and calculate the difference in the number of
registered projects from the previous year.
Base Field: Years and Base Item: Previous.
Rows: Zone.
Columns: Years.
Values: Count of Zone (number of projects).

Steps:
• Right-click one of the cells in the Values area.
• Click Show Values As>click Difference From.
• From the base field list, choose Years.
• From the base item list, choose Previous.

151
 Figure 6-12: Number of investment projects by zone and
year where base field and base item are selected.

You get the following result as shown below.

Table 6-14: Number of registered projects (Difference

From the previous year)

Within the Year field, each year's registered projects will

be subtracted from the previous year’s number of projects.

152
The pivot table above shows the differences in yearly
number of registered investment projects. For instance, in
East zone, the difference between the number of projects
between 2016 vs 2017 and 2017 vs 2018 were 93 and 87
respectively. In the North-East zone, the difference from
the previous year was −33 and −70 for years 2017 and
2018 respectively which shows a decline trend.

Example 6.14: Based on Example 6.13, if you change the

base Field to Zone and base Item to North-East (as a bench
mark), you get the result as shown below.

Table 6-15: Number of registered projects (Difference

From North-East zone)

As per Table 6-15, in years 2016 and 2017, most zones were
performing low as compared to the North-East zone
regarding the number of projects registered. Note that
North-East zone row is empty as it is benchmark (difference
from itself is zero).

153
6.2.9 % Difference From
The ‘% Difference From’ displays values as the percentage
difference from the value of the base item in the base field.
It shows all values as the percent difference between the
current item and previous item, next item or a fixed item’s
value.

Example 6.15: Based on Example 6.13, if you keep the base

field to Years and change the base item to 2016, calculate
the % Difference From in the number of registered projects.

Steps:
• Right-click one of the cells in the Values area.
• Click Show Values As>click % Difference From.
• From the base field list, choose Years.
• From the base item list, choose 2016.

Table 6-16: Number of registered projects (% Difference

From year 2016)

154
For instance, the % difference (% change) of the number of
projects registered in East zone from 2017 and 2018 (as
compared to 2016 was 68.89% (= 1 − 228/135) and
133.33% (= 1 − 315/135) respectively. When you
compare the % of projects registered in the North-East
zone, the % difference was −11.87% and −37.05% in
2017 and 2018 respectively as compared to 2016.

6.2.10 Running Total In

The ‘Running Total In’ displays the value for successive
items in the base field as a running total. For instance, it
calculates the cumulative number of projects registered
during 2016, 2017 and 2017.

Example 6.16: Create a pivot table based on the settings

below and calculate the Running Total In (cumulative)
number of registered projects during three years.
Rows: Years.
Values: Count of Zone (number of projects).

Steps:
• Right-click one of the cells in the Values area.
• Click Show Values As>click Running Total In.
• From the base field list, choose Years.

155
Figure 6-13: Base Field selected to calculate the Running
Total In

You get the cumulative number of projects as shown below.

Table 6-17: The cumulative number of projects

Based on Table 6-17, the cumulative number of the projects

in 2016, 2017 and 2018 were 965, 2265 and 3889. The
cumulative number in 2018 is the same as the grand total
number of registered projects.

Exercise 6.3: Based on Example 5.4, calculate the

cumulative number of employees for each age range and
plot the chart for it.

156
6.2.11 % Running Total in
The ‘% Running Total In’ calculates the value as a
percentage for successive items in the base field that are
displayed as a running total. Select the % Running Total In
option to show the running total for a given field as a
percent of the Grand Total.

Example 6.17: Based on Example 6.16, compute the %

Running Total In for the cumulative number of projects.

Steps:
• Right-click one of the cells in the Values area.
• Click Show Values As>click % Running Total In.
• From the base field list, choose Years.

Your final pivot table should look like as shown below.

Table 6-18: The % cumulative number of projects

Based on Table 6-18, the % cumulative number of the

projects (compared to the grand total) in 2016, 2017 and
2018 were 24.81%, 58.24% and 100% respectively. The %
cumulative in 2018 is the same as the % grand total against
itself.

157
Note that the Running Total In and the % Running Total In
calculations can be used to compute cumulative frequency
distributions in statistics.

6.2.12 Rank Smallest to Largest

It displays the rank of selected values in a specific field,
listing the smallest item in the field as 1, and each larger
value with a higher rank value.

Example 6.18: Compute the rank (smallest to largest) for

the number of projects shown in Figure 6-14.

Steps:
• Right-click one of the cells in the Values area.
• Click Show Values As>click Rank Smallest to Largest.
• From the base field list, choose Years.

Figure 6-14: To rank projects from smallest to largest

158
Your final pivot table should look like as shown in the table
below which shows the rank of number of projects from
smallest to largest.

Table 6-19: Rank of number of projects

6.2.13 Rank Largest to Smallest

It displays the rank of selected values in a specific field,
listing the largest item in the field as 1, and each smaller
value with a higher rank value.

Exercise 6.4: Based on Example 6.18, compute the rank

(largest to smallest) for the number of projects.

Note that if you want to revert back to normal sum or count

values, you can select the ‘No Calculation’ option in the
Show Values As options.

6.3 Creating calculated fields

A calculated field is a formula calculated using the fields
available in the pivot table. It is a newly created data field
and therefore, you are adding a virtual column to your
dataset.

159
To add a calculated field:
• Select a cell in the pivot table and choose PivotTable
Tools>Analyze tab>Calculations group>Fields, Items,
& Sets arrow>Calculated Field. The Insert Calculated
Field dialog box opens (see Figure 6-15).

Figure 6-15: Insert Calculated Field dialog box

Figure 6-15 lists all the fields in the source data.

• In the Name text box, type a descriptive name for the
new field.
• In the Formula text box, delete the 0 value and enter
the formula for this field. In the formula, you can use
mathematical operators.
• Click the Add button to add this new field.

The formula can use Excel’s built-in operators and

functions, or combine one or more of the fields in the Fields
160
List. To insert a field name in the formula quickly, double-
click the name in the list. If you manually type a field name
that contains spaces or special characters, enclose the name
in single quotes (‘Years of Experience’, for example).
• Click OK.
In the PivotTable Fields task pane, Excel adds the
calculated field to the fields list and the Values box so that
it appears in the pivot table. Excel sums the formula for
every row. Removing a custom field from the Values box
removes it from the pivot table, but it remains in the fields
list for later use.

Edit a calculated field formula

• Click the PivotTable.
• Choose Analyze tab>Calculations group> Fields,
Items, & Sets>Calculated Field.
• In the Name box, select the calculated field for which
you want to change the formula.
• In the Formula box, edit the formula.
• Click Modify.
To permanently delete a custom field, select it from the
Name drop-down list in the Insert Calculated Field dialog
box and click DELETE.

After you create the calculated field, Excel adds it to the

Values area of the pivot table (and it appears in the
PivotTable Fields task pane). You can treat it just like any
other field.

161
Example 6.20: Based on InvestmentData, calculate the
amount investment (in thousands) by sector. To create a
calculated field for the investment (in 000s) by sector:
• In the Name box type Investment (in 000).
• In the Fields area, click Investment, followed by the
Insert Field button.
• The Formula field now contains: = Investment.
• Your cursor is at the end of this field, so enter a division
sign. (/).
• Divide by 1000 to make the investment to make it in
thousand (000) notations.
• The formula is now: = Investment/1000.

Figure 6-16: Inserted Calculated Field dialog box

• Click OK. Excel inserts this new field into the pivot
table (see the table below).

162
 Table 6-20: Investment (in 000s) by sector

Note that you can use a calculated field as an input to

another calculated field.

To list all calculated fields in a worksheet, choose

PivotTable Tools>Analyze tab>Calculations group>Fields,
Items, &Sets>List Formulas.

Note that when you use calculated fields, make sure that
you turn off the “Subtotals” and “Grand Totals” in the
Design tab because when you create a calculated field, it is
also calculating for each sub-total and grand total.

6.4 Referencing cells in a pivot table

If you create a formula that refers to a cell within a pivot
table, Excel automatically converts the cell reference to a
GETPIVOTDATA function if it is turned on. The
GetPivotData function uses criteria to ensure that the

163
correct data is returned, even if the pivot table layout is
changed.

To turn on or off GETPIVOTDATA function:

• Choose PivotTable Tools>Analyze tab>PivotTable
group>Options arrow.
• Then click on Generate GetPivotData toggle (on or off)
-OR-Choose File tab>Options >Formulas> Use
GetPivotData functions for PivotTable references
checkbox.

Example 6.21: For instance, the figure below shows a

simple pivot table that displays the number and percentage
of employees by gender. Calculate the female to male ratio
outside the pivot table area.

Figure 6-17: The formulas in cell E3 reference cells in the

pivot table

Cell E3 contains formulas, and this column is not part of

the pivot table. These formulas calculate the Female-to-
Male ratio. The formula is created by pointing to the cells.
You may expect to see this formula in cell E3 to look like
B3/B4. In fact, the formula in cell E3 is:

164
When you use the pointing technique to create a formula
that references a cell in a pivot table, Excel replaces those
simple cell references with a much more complicated
GETPIVOTDATA function. If you type the cell
references manually (rather than pointing to them), Excel
does not use the GETPIVOTDATA function.

Using the GETPIVOTDATA function helps ensure that

the formula will continue to reference the intended cells if
the pivot table layout is changed. The figure below shows
the pivot table after education field is nested under the
gender field. As you can see, the formulas in cell E3 still
show the correct result even though the referenced cells are
in a different location. If you used simple cell references;
the formula would give incorrect results after changing the
layout of the pivot table. GETPIVOTDATA ensures that
the formulas still return the correct results even if you
rearrange the pivot table.

165
Figure 6-18: The GETPIVOTDATA function continue to
display the same result

Note that to use the GETPIVOTDATA function; the data

(input) that it uses in the formula must be visible in the pivot
table. If you modify the pivot table so that the value used by
GETPIVOTDATA is no longer visible, the formula returns
an error.

Project-5
Generate and interpret a pivot table on the following:
1. Average investment and average employments by
sector.
2. Maximum monthly income by education field and
gender.
3. The number and % of employees by position and
gender.

166
4. The number and percentage of employees by
income range (increment of ETB 2000) and
gender.
5. The number of employees by years of experience
range and gender.
6. The number and percentage of employees by
position, department and gender.
7. The number of employees hired in each
department by gender and employment year.
8. The number and % of employees by education
field and gender.
9. The distribution of investment projects by sector
and year (depict the amount of investment,
number of investment projects and number of
employments in each year).
10. The distribution of investment projects by zone
and year (depict the amount of investment,
number of investment projects and number of
employments in each year).
11. The distribution of investment projects by sector
and status (depict the amount of investment,
number of investment projects and number of
employments under each status).
12. The distribution of investment projects by sector,
zone and registration year (nest sector under each
year and show the amount of investment, number
of investment projects and number of
employments under each zone).

167
13. The distribution of investment projects by sector
and type of business (show the amount of
investment, number of investment projects and
number of employments under each type of
business).
14. The sectoral share of projects by sector and status
(depict in percent, the amount of investment,
number of investment projects and number of
employments under each status).
15. Practice with the ‘Summarize Values By’ and
‘Show Values As’ calculation options in a pivot
table.

168
 Chapter 7 : Filtering and Sorting Data

Focus points
• Report filters.
• Slicers and Timelines.
• Group filters.
• Sort options.

If a pivot table displays too much detail, you can filter it to

show only part of the source data. Excel offers several
filtering options such as Report filters, Slicers, Timelines,
and Group filters. Besides, you can sort your data from
smallest to largest or vice versa using the Sort options.
These filtering and sorting tools are demonstrated in this
chapter.

7.1 Report filters

After you summarized your data by creating an Excel pivot
table, you can focus on specific portions of the source data
using Report Filters. Report filter is a data filtering
technique by moving fields to the Reports area of a pivot
table field list.

Example 7.1: Generate a report on the number of

employees by gender and department filtered by Position
field. Consider a pivot table with the settings below.

169
Filters: Position.
Rows: Department.
Columns: Gender.
Values: Gender (Summarized by Count).

To create a summary for specific position(s), drag Position

field to the Filter box. The report filter field appears just
above the pivot table. To set the filter, click the drop-down
arrow in the field box and choose the Position(s) that you
want to display (see Figure 7-1). To find an item, scroll the
list or type the first few characters of its name in the search
box. To filter for several items at the same time, turn on the
“Select Multiple Items” checkbox and select the desired
items (for instance, the checkbox for Laboratory
Technician and Manufacturing Director is checked as
shown below.

Figure 7-1: Selecting fields from the report filter menu

170
 Table 7-1: Multiple items filtered for the position field

As per Table 7-1, there were 157 female and 247 male
employees in the Research and Development department
who were assigned in the Laboratory Technician and
Manufacturing Director positions.

Note that when a filter is applied, the field box button

changes to a filter (funnel) icon (see Figure 7-3) in the field
list in the PivotTable Fields task pane.

Note that any field that you use for report filtering cannot
also be used for grouping. If you filter by Position, for
example, you cannot also group by Position. This
restriction does not apply to slicers and group filters (to be
discussed later). For instance, if you move the position field
to the Rows area and try to group it, you get a warning
message which says “Cannot group that selection.”

Change the Report Filters Layout

By default, the Report Filters are shown in a single vertical
list at the top of a pivot table. To save space, you can change
the Report Filter layout. You can either limit the number of

171
fields in the vertical list, and create more vertical lists or
show the Report Filter across the row. In the PivotTable
Options, you can change the ‘Display Fields in the Report
Filter Area’ option, to find the best balance of height and
width for the report filter layout. For column arrangements,
use the ‘Down, Then Over’ option and for row
arrangements, use the ‘Over, Then Down’ option.

To control how report filter fields are arranged in rows and

columns, select a cell in the pivot table and choose
PivotTable Tools>Analyze tab>PivotTable
group>Options>Layout & Format. Experiment with the
‘Display fields in the report filter area’ (Down, Then Over
or Over, Then Down) and you can increase the number of
Report filter fields per column or row (see Figure 7-2).

172
 Figure 7-2: The pivot table options (Layout & Format)

To limit the number of fields in the vertical list:

Select Down, Then Over to first display fields in the report
filter area from the top to the bottom, as fields are added to
it, before taking up another column. To do so,
• Right-click a cell in the pivot table, and click
PivotTable Options.
• On the Layout & Format tab, the ‘Display Fields in the
Report Filter Area’ is set for ‘Down, Then Over’.

173
• In the ‘Report filter fields per column’ box, select the
number of filters to go in each column. The default
setting is zero, which means “No limit.”
• Click OK. The Report Filters change to show the
specified number of fields per column.

To show the Report Filters across the row:

Select Over, Then Down to first display fields in the report
filter area from left to right, as fields are added to it, before
taking up another row. To do so,
• Right-click a cell in the pivot table, and click
PivotTable Options.
• On the Layout & Format tab, click the drop-down
arrow beside ‘Display Fields in the Report Filter Area’.
• Click ‘Over, Then Down’.
• In the ‘Report filter fields per row’ box, select the
number of filters to go across each row. If the number
is set at zero, all the filters will be shown in one row.
• Click OK. The Report Filters change to a horizontal
layout, with the specified number of fields per row.

Clear a Report Filter

When you finished analyzing the filtered data in a pivot
table, you can clear the Report Filters, to see all the data
again.

174
To clear a Report Filter:
• In the pivot table, click on the drop-down arrow for
Report Filter.
• Click (All), to remove the filter criteria, and show all
the data.
• If other Report Filters have criteria applied, follow the
same steps to clear the criteria.
• When you are done, click OK.

7.1.1 Filter fields from the PivotTable Fields list

You can also filter items in a field from the field list in the
PivotTable Fields task pane. To do so, move your cursor
over the desired field and click on the small downward
arrow (filter or funnel) to the right of the field name (in the
Filter box) to open the filter menu (see Figure 7-3) and filter
as you like.

Figure 7-3: The Filter (funnel) icon to filter fields

175
Example 7.2: Generate a report on the number of
investment projects under operation and implementation by
sector with the amount of investment and number of
employments created. Consider a pivot table with the
following settings.
Filters: Status.
Rows: Sector.
Values: Type of Business (Summarized by Count),
Amount of Investment (Summarized by Sum), and
Employment (Summarized by Sum).

Figure 7-4: The Status field is moved to Filters box

176
Note that the filter (funnel) icon in the Status field shows
that it is in the Filters box (see Figure 7-4) as it is also
shown in the Figure 7-3.

To filter for ‘Implementation’ and ‘Operation’ project

statuses at the same time, turn on the ‘Select Multiple
Items’ checkbox and select the desired items (Figure 7-5).

Figure 7-5: Fields from the report filter drop-down list

The report on the number of investment projects under

operation and implementation by sector is presented in
Table 7-2 (where multiple items are filtered). Based on this,
in the agriculture sector for instance, there were 21 projects
(under operation and implementation) with investment
amount of ETB 169,890,824.00 which created job
opportunities for 7987 employees.

177
 Table 7-2: Investment profile by sector (report filter)

Example 7.3: Generate a report on the number of

employees by department and gender (filtered by education
level and education field). Consider a pivot table with the
following settings.
Filters: Education level, Education field (multiple filters).
Rows: Department.
Columns: Gender.
Values: Gender (Summarized by Count).

The pivot table with two filters is generated (see Table 7-3).

Table 7-3: A pivot table with multiple report filters

178
The pivot table in Table 7-3, uses a report filter for the
Education Field and Education Level fields and displays the
data for all the Education Levels and Education Fields.

How many employees hold a doctorate degree in human

resources field of study? To answer this question, choose
Doctor from the Education Level and Human Resources
from the Education Field drop-down filters.

Table 7-4: Number of employees by education level and

education field (with report filter)

Therefore, there were only two male employees in the

Human Resources department who hold a doctorate degree
in human resources field of study.

Exercise 7.1: Prepare a report on zonal distribution of

operational projects. Consider the following setting.
Filters: Status (filter by Operation).
Rows: Zone.
Values: Type of Business (Summarized by Count).

179
7.1.2 Replicating a pivot table for report filter items
In pivot table, you can create multiple reports with one
click. That is, if you have a pivot table with a field or fields
in the Filter area and you would like a pivot table for each
item in the field, this can actually be done automatically
using ‘Show Report Filter Pages’ which allows you to build
multiple reports from a single pivot table. You can set up a
pivot table and replicate it for every distinct value of a
report filter. It may be useful for generating separate pivot
tables or charts.

Example 7.4: Consider a pivot table with the settings

below.
Filters: Education Level.
Rows: Department.
Columns: Gender.
Values: Gender (Summarized by Count).
In this example, you have the education level field in the
filter area and pivot table is currently filtered on Bachelor
level, and you want a pivot table like this for each education
level.
Table 7-5: Number of employees by department, gender
and education level

180
Based on the above table, there were 235 female and 337
male employees with bachelor degree in three departments.

To replicate a pivot table for report filter items: Choose

PivotTable Tools>Analyze tab>PivotTable group>Options
arrow>Show Report Filter Pages button (see Figure 7-6).

Figure 7-6: Show Report Filter Pages button

The Show Report Filter Pages dialog box opens (see Figure
7-7).

Figure 7-7: Show Report Filter Pages dialog box

181
Select the desired field from the ‘Show Report Filter
Pages’ dialog box if you have multiple fields in the filter
area of your pivot table and press the OK button. Excel
creates a new sheet for each item in the field you
selected. Each sheet will be named after the item in your
field and will contain a copy of your pivot table filtered on
that item. In this case, you see new sheet tabs with the
names of each Education Level. It is a time saver when you
have a lot of items in your field.

Figure 7-8: New sheet tabs for each education level

These new reports are linked to the data source. If the data
changes, you just need to refresh each report (or refresh
all) to see the latest results.

Note that the Report Filters are not dependent, so items that
you select in one Report will not affect the items available
in any other Report Filters. You can use Slicers (to be
discussed later) to see related items from other fields, after
applying a filter.

182
7.2 Slicers and timelines
7.2.1 Slicers
In report filters, you can filter multiple items (education
level, education field, position, and so on) but these
multiple items are not visual. That is, you do not know
which position or education level is filtered. Slicers
(introduced in Excel 2010) offer about the same features as
report filters with an additional visual feature where the
items filtered are visible. They are visual buttons that allow
you to quickly and easily filter your data in an interactive
way. It displays all the possible values from a selected field
of your data and each value displays as an individual button
inside the slicer. Slicers offer fast one-click filtering. Each
slicer has its own floating window that you can format or
drag around the main Excel window.

Note that each slicer represents a particular field.

To create a slicer:
• Select a cell in the pivot table.
• Choose PivotTable Tools>Analyze tab >Filter
group>Insert Slicer.

The Insert Slicers dialog box opens (see Figure 7-9), listing
all the fields in the pivot table (except custom fields such
as calculated fields).

183
 Figure 7-9: Part of the Insert Slicers dialog box

• Place a checkmark next to the slicers you want. Fields

that have a small number of unique values (like Female
and Male in the case of Gender field) make the best
slicers because they fit well in a floating slicer window.
• Click OK. Excel adds a separate floating window for
each slicer.

No filtering is in effect in a newly created slicer window,

so every button is shaded. The slicer window lists all the
unique values in a field, each value appearing as a separate
button. The buttons of visible values are shaded.

You can move or resize the floating slicer windows as

desired. To move a slicer, point to a border (the pointer
turns into a four-way arrow) then drag. To resize a slicer,
point to a corner or the middle of an edge (the pointer turns
into a two-way arrow) and drag. You can use the slicer
window to apply filtering.

184
• To filter on a single value, click its button.
• To filter on multiple values, click toggle button at
the top of the slicer window and click the desired filter
buttons-OR-Hold down the CTRL key while you click
each button. To select a range of contiguous values,
click the first button and press the SHIFT key and click
the last button.

• To clear filtering, click the button at the top of the

slicer window.

Note that you can also right-click on any field (inside the
pivot table field list) you want to be a slicer and click on
‘Add as Slicer’ options.

Example 7.5: Consider a pivot table with the settings below

and create slicer for Position and Employment Date fields.
Rows: Department.
Columns: Gender.
Values: Gender (Summarized by Count). With this setting,
you get the pivot table as shown below.

Table 7-6: Number of employees by department and gender

185
• To insert a slicer on the pivot table in Table 7-6, select
the checkbox of Position and Employment Date fields
for filtering.
• Click OK. Excel adds a separate floating window for
each slicer as shown in the Figure 7-10.

Figure 7-10: Slicers for Position and Employment Date

To filter on two positions, press CTRL and click on the

buttons of Laboratory Technician and Manufacturing
Director for instance, and you get the report as shown in the
Figure 7-11. If you remember in Example 7.1, a report filter
on these two positions was used and the result was the
same. Therefore, there were 157 female and 247 male
employees in the Research and Development department

186
who were assigned in the Laboratory Technician and
Manufacturing Director positions.

Figure 7-11: A pivot table with slicer filters

Based on Example 7.5, how many employees (assigned in

the Laboratory Technician and Manufacturing Director
positions) were hired on March 7, 2012 (3/7/2012)? To
answer this, use the slicer settings as shown in the Figure
7-12 where Laboratory Technician and Manufacturing
Director buttons are selected from the Position slicer and
the date (3/7/2012) is selected from the Employment Date
slicer. Therefore, there were three (two females and one
male) employees who were hired on 3/7/2012 in the
Research and Development department.

187
As the employment date slicer has long lists, you need to
use the scroll bar to move up and down to select specific
dates.

Figure 7-12: A pivot table with multiple slicer filters

Example 7.6: Generate a report on the number of

investment projects under operation and implementation by
sector with the amount of investment and number of
employments created. Consider a pivot table with the
following settings.
Rows: Sector.
Values: Type of Business (Summarized by Count),
Amount of Investment (Summarized by Sum), and
Employment (Summarized by Sum). With this setting, you
get the pivot table as shown below. Add a slicer by Status
(Operation and Implementation).

188
 Table 7-7: Investment profile by sector

Now, let us create slicer for a Status field.

• To insert a slicer on the pivot table above, select the
checkbox of Status field for filtering.
• Click OK. Excel adds a separate floating window for
each slicer as shown in the Figure 7-13.

Figure 7-13: A slicer for Status field added

To prepare a report on the number of investment projects

under operation and implementation by sector (with
amount of investment and number of employments created)
press CTRL and click on the buttons of Operation and
Implementation. Then, you get the report as shown in the

189
Figure 7-14. If you remember in Example 7.2, a report filter
on Status field was used and the result was the same.

Figure 7-14: A pivot table with slicer filters applied

Therefore, for instance, in the agriculture sector, there were

21 projects (under operation and implementation) with
investment amount of ETB 169,890,824.00 which created
a job opportunity for 7987 employees.

Exercise 7.2: Generate a report on the number of

employees by department and gender. How many
employees hold a doctorate degree in human resources field
of study? Consider a pivot table with the following settings.
Rows: Department.
Columns: Gender.
Values: Gender (Summarized by Count).
Slicers: Education Field and Education Level.
The report is shown in the figure below.

190
 Figure 7-15: Number of employees with slicers

Your answer should be identical with the answer in

Example 7.3.

Therefore, there were only two male employees in the

Human Resources department who hold a doctorate degree
in human resources field of study.

Exercise 7.3: Prepare a report on zonal distribution of

operational projects. For instance, how many investment
projects are operational in the North zone? Consider a pivot
table with the following settings.
Rows: Zone.
Values: Type of Business (Summarized by Count).
Slicer: Status.
The report is shown as in the Figure 7-16. Your answer
should be identical with the answer for Exercise 7.1.

191
 Figure 7-16: Number of operational projects by zone

7.2.2 Link a slicer with multiple pivot tables

You can connect a slicer to one or more pivot tables to
control them with a single slicer.

Example 7.7: Prepare a report that shows the distribution

of operational investment projects by zone. Consider a
pivot table with the following settings.
Rows: Zone.
Values: Type of Business (Summarized by Count),
Amount of Investment (Summarized by Sum) and
Employment (Summarized by Sum). With this setting, you
get the pivot table as shown below.

Table 7-8: Summary of investment projects by zone

192
To connect a pivot table in Example 7.6 with the pivot table
in Example 7.7 with a Status field slicer:
• Select a cell in any of the pivot tables.
• From the Insert Slicer dialog box, select the Status field
to use as a filter in the slicer.
• Click OK.

At this point, you have a slicer in your worksheet which can

filter the pivot table in which you insert it.

To connect it to the second pivot table:

• Select the slicer and go to Slicer Tools>Option tab
>Slicer group >Report Connections-OR-Right-click
the slicer window and choose Report Connections
option from the menu.

Figure 7-17: The Report Connections option

• You get a new dialog box with the list of pivot tables
which are in your workbook. Check all the pivot tables
you need to connect and click OK. As shown in the
Figure 7-18, PivotTable3 (Example 7.6) and
PivotTable6 (Example 7.7) are connected.

193
Your PivotTable names may differ from the one shown in
the Figure 7-18.

Figure 7-18: Report Connections dialog box

Now you have connected one slicer with two pivot tables
and if you click on the status “Operation” in one of the
pivot tables, it has also an effect on the other pivot table
connected.

Figure 7-19: Investment profile by zone with slicer

Therefore, as per Figure 7-19, the number of operational

projects in the West zone were 99 with an amount of
investment of ETB 636,155,618.62 and the number of

194
employments created in this zone by operational projects
were 18863.

Moreover, if you add the Rows label to Sector field (by

removing the Zone field) and slice based on Status (Status:
Operation), the pivot table in the figure below shows the
number of operational projects in each sector. For instance,
in the Manufacturing sector, there were 35 operational
projects with an amount of investment of ETB
381,003,199.20.

Figure 7-20: Number of investment projects by sector with

slicer

You can also connect pivot charts with a slicer.

7.2.3 Formatting a slicer

The slicers can be formatted using the slicer styles in the
slicer tab from the Ribbon. You can format a single slicer
or a group of slicers at once.
To change the slicer’s style, choose Slicer Tools>Options
tab>Slicer Styles group gallery.

195
 Figure 7-21: Slicer Styles options

To change the style of more than one slicer at once:

• Select all slicers (CTRL+A). Clicking on one slicer
and pressing CTRL+A selects (activates) all slicers as
well.
• Select a style you want and it will be applied to all
slicers selected and all get updated automatically.

The slicer can also be formatted with New Slicer Style in

the Slicer Styles group with the 'New Format Style
dialogue box' by choosing the slicer element and by
clicking the 'Format' button.

To expand or compact the slicer window:

• Choose Slicer Tools>Options tab>Buttons group, and
change the Columns, Height, and Width values-OR-
Right-click the slicer window and choose Size and
Properties option from the menu.

196
 Figure 7-22: Slicer formatting options

Change 1 to 3 in the Columns box in the figure above and

you get a three-column slicer window with custom button
colors as shown below.

Figure 7-23: A formatted slicer window

To change the Slicer’s window title:

• Choose Slicer Tools>Options tab>Slicer group>Slicer
Caption box as shown below and change the title in the
Slicer Caption:

Figure 7-24: Slicer Caption option

197
 To hide the window title for a slicer:
Choose Slicer Tools>Options tab>Slicer group>Slicer
Settings> clear (uncheck) “Display header” (see Figure
7-25).

Figure 7-25: Slicer Settings options

To change the Caption (or apply other Slicer Setting

options) of more than one Slicers at once:
• Select all slicers.
• Right-click on selected slicers.
• Choose Slicer Setting.
• Type the name of the heading you want to all the slicers
under ‘Display header’ in the ‘Caption box.

To sort a slicer’s values:

• Right-click the slicer window.
• Choose Sort options from the menu.

198
To remove a slicer:
• Click it and press DELETE -OR- Right-click it and
choose the Remove [“Field Name”} option from the
menu.

7.2.4 Lock the workbook but not a slicer

If you want to send your pivot report to someone else and
you do not want them to touch the pivot table, but allow
them to use Slicers, do the following.
• Select one slicer, press CTRL+ A to select all of them.
• Right-click and choose Size and Properties.
• Under Properties, uncheck the Locked box and press
close.
Now you need to go the Review tab in the Ribbon and do
the following:
• Choose Protect Sheet in the Protect group. Unselect
the first option (Select locked cells).
• The ‘Select the locked cells’ should be checked so that
you can select the unlocked slicers.
• Select the ‘Use PivotTable and PivotChart’ and you
can put a password here to protect the sheet-OR- press
OK to leave without a password.

Now if you click in the PivotTable, nothing happens but the

slicers are clickable and you can use them to filter.

199
7.2.5 Timelines
Timelines work like slicers and they offer a visual way to
view and change a contiguous range of dates and filter pivot
tables and pivot charts. You can use a timeline to filter on
ranges of dates by days, months, quarters or years. The
dates appear in a horizontal line going from oldest to newest
as you go from left to right on the timeline.
To insert a timeline:
• Select a cell in the pivot table and choose PivotTable
Tools>Analyze tab>Filter group>Insert Timeline.
• Excel displays a dialog box that lists all date-based
fields.

Figure 7-26: Insert Timelines option

• Click OK. Timeline options appear (see Figure 7-27).

• Click the timeline controls to select time periods (days,
months, quarters, or years). You can change the time
periods as you want (see Figure 7-27).

200
 Figure 7-27: Timelines options

Figure 7-28 shows a pivot table created from

InvestmentData. This pivot table uses a timeline which is
set to allow date filtering by years. Click a button that
corresponds to the year you want to view, and the pivot
table is updated immediately. To select a range of years,
press SHIFT key while you click the first and last buttons
in the range. Other filtering options (selectable from the
drop-down in the upper-right corner) are Year, Month, and
Day (see Figure 7-28). By the way, you can use both slicers
and a timeline for a pivot table.

Figure 7-28: A pivot table with slicer and timeline

201
Note that a timeline has the same type of formatting options
as slicers.

To format a timeline:
• Select (click) it and choose Timeline Tools>Options tab
and experiment with various formatting options.

You can also connect Timelines with different pivot tables

as you do for slicers.

Example 7.8: Based on Example 7.7, how many

investment projects with operational status, were registered
in 2016? To answer this question, you have to use the Slicer
on Status field (select the Operation button) and the
Timeline on Registration Date where the year 2016 button
is selected as shown in the Figure 7-29. Therefore, the
number of investment projects registered in 2016 were 167.

Figure 7-29: Operational projects registered in 2016

202
Exercise 7.4: Based on Example 7.8, how many investment
projects with operational status were registered in 2017 and
2018 respectively? In quarter 4, 2016? In June 2017?

Exercise 7.5: Based on Example 7.3, how many Research

Scientists were hired in quarter 4 of 2014?

7.3 Group filters

Group filters in a pivot table can be used to perform the
following:
• To show or hide specific items (like report filters).
• To create complex conditions that subset data. For
example, you can show or hide dates that fall in a
specific time period, for example, names that begin or
end with a certain letter (for text data).
• To filter on multiple fields and configure them
independently.

Note that group filters are more powerful than report filters
and slicers.

Example 7.9: What is the average monthly income of

employees by education level and education field?
Consider a pivot table with the settings below.
Rows: Education Level, Education Field.
Values: Monthly income (Summarized by Average).

203
 Table 7-9: Average monthly income of employees by
education level and education field

To create a group filter:

• Click the drop-down arrow to the right of a rows or
columns cell. The filter list opens (see Figure 7-30).

204
 Figure 7-30: The group filter drop-down list

• If you are grouping on multiple fields, choose a field

from the “Select field:” drop-down list at top.
• Set the desired options in the filter list.
• Click OK.

To show or hide specific items, select or clear their

checkboxes. To find an item, scroll the list or type the first
few characters of its name in the search box. To show or
hide items that contain specific text, begin or end with a
certain letter, and so on, choose an option from the Label
Filters submenu. For example, to show only items that begin

205
with “B”, choose Label Filters>Begins With and type B in
the dialog box that opens (see Figure 7-31).

Figure 7-31: The Label Filters dialog box

Education Level(s) which begin with letter ‘B’ (Bachelor

and Below College) with their associated education fields
(nested under the education level) are listed below.

Table 7-10: Education Levels which begins with ‘B’

To show or hide calculated values based on numerical

criteria (less than, greater than, Top 10, Bottom 10, Top

206
25%, and so on, choose an option from the Value Filters
submenu (Figure 7-32). To show only average income
greater than ETB 6000, for example, choose Value Filters>
Greater Than and type 6000 in the dialog box that opens
(see Figure 7-32).

Figure 7-32: The Value Filters dialog box

Average monthly income of employees (by education

level) greater than ETB 6000 is listed below.

Table 7-11: Average income by education level

Therefore, the average monthly income of employees with

Bachelor, College, Doctor and Master degrees are ETB
207
6,517.26, 6,226.65, 8,277.65 and 6,832.40 respectively in
which all are greater than ETB 6,000.

Exercise 7.6: Based on Example 7.9, filter the average

monthly income of employees which is greater than ETB
6,000 by education field.

7.3.1 Date filters

In a pivot table, you can use date filters to show the results
for a specific date or date range. There are three types of
date filters:
1. Individual date checkboxes.
2. Selection by specific date range.
3. Dynamic date range selection.

1. Filter with date checkboxes

If a date field is in the Report Filter area, only the date check
boxes are available. If you want to filter for a date range,
move the field to the Rows or Columns areas.

To select specific dates:

• Click the drop-down arrow on date field.
• To show the check boxes, add a check mark to ‘Select
Multiple Items’.
• In the list of dates, add check marks to show dates, or
remove check marks to hide dates.
• Click OK.

208
 Figure 7-33: Options to select specific dates

When a filter is applied the field box button changes to

and a filter (funnel) icon appears in the field list in the
PivotTable Fields task pane.

Figure 7-34: Clear Filter from “Year” option

209
2. Filter for a specific date range
For a date field in the Rows or Columns areas of the pivot
table, you can select a specific date range for the filter.
• Click the drop-down arrow on the Rows or Columns
labels heading.
• Click Date Filters, then click, for instance, ‘Before’
from the submenu.
• In the Before dialog box, type a date or select them
from the calendars.
• Click OK.

3. Filter for a dynamic date range

A dynamic date range changes automatically, based on the
current date. For example, "Next Week" represents a
different range of dates, every day that you open the pivot
table file. If a date field is in the Rows or Columns areas,
follow these steps to show the current month's data, as a
dynamic date range.
• Click the drop-down arrow on the Rows or Columns
labels heading.
• Click Date Filters, then click ‘This Month’.

Clear the field filters

When you finish analyzing the filtered data, you can clear
the filters to see all the data again.
• In the pivot table, click the drop-down arrow in the
filtered field heading.

210
• To remove the filter criteria and show all the data, click
Clear Filter From Filtered Field. For instance, click
Clear Filter From [date field name for instance
“Years”]. If you have multiple filters, you can remove
each one separately.
• To remove Filters all at once (and show all data),
choose PivotTable Tools>Analyze tab>Actions
group>Clear arrow>Clear Filters.

7.3.2 Top 10 Filters in Pivot Table

You can use the Top 10 filter feature in a pivot table to see
the Top or Bottom Items or find items that make up a
specific Percent or items that total a set Sum. For example,
you can summarize your data by creating a PivotTable, and
use Value Filters to focus on the top 10, bottom 10 or a
specific portion of the total values in your data. For
instance, with a Top 10 Filter, you can quickly show the top
10 or bottom 10 employees based on their income, top 5 or
bottom 5 investors based on the amount of investment,
employments created, profits made and so on. Besides, in
an investment data, you can identify the best performing
projects (like top 10, top 5, top 5%) or the worst ones
(bottom 10, bottom 5, bottom 5%) and take appropriate
actions needed. The Top 10 Filter is flexible and allows you
to make meaningful analysis.

Example 7.10: Based on HRMDATA, create a pivot table

based on the following settings and generate a report on the

211
Top 10 Employees (use EmployeeID) based on their
monthly income. Sort the result from largest from smallest.

Rows: EmployeeID.
Values: Sum of monthly income.

To generate a report on the Top 10 Employees based on

their monthly income, follow the steps below:
• In the Pivot Table, click the drop-down arrow in the
EmployeeID field heading.
• In the menu, click Value Filters, then click Top 10.
• In the Top 10 Filter dialog box, leave the default
settings as it is (Top 10, Items, Sum of Monthly
Income).

Figure 7-35: Top 10 Filter dialog box

• Click OK, to close the Top 10 Filter dialog box, and

apply the Value Filters.

As per Table 7-12, the EmployeeID field has been filtered

to show only the Top 10 employees based on their monthly
income.

212
 Table 7-12: Top 10 employees based on their income

Based on the above table, the sum of monthly income of

top 10 employees is ETB 198,682.00. To sort the amounts
in descending order, right-click on one of the amounts
(income), and click Sort>Sort Largest to Smallest.

Modify a Top 10 filter

After you add a Top 10 Filter, you can change it, to show a
different result.
• In the Pivot Table, click the drop-down arrow in the
EmployeeID field heading.
• In the menu, click Value Filters, then click Top 10.
• In the Top 10 Filter dialog box, change the number of
Items to 5.
• Click OK.

The results change to show only the 5 employees

(EmployeeID) with the highest monthly income.

213
 Table 7-13: Top 5 employees based on their income

Based on the above table, the sum of monthly income of

top 5 employees is ETB 99,700.00

Filter a pivot table for Bottom 10 Items

Even though the filter is named ‘Top 10’, you can use it to
find the bottom amounts too. For example, to see only the
Bottom 5 income earners, follow the steps below.
• In the Pivot Table, click the drop-down arrow in the
EmployeeID field heading.
• In the menu, click Value Filters, then click Top 10.
• In the Top 10 Filter dialog box, select ‘Bottom’ from
the first drop-down, type 5 in the second drop-down,
select ‘Items’ in the third drop-down and set the last
drop-down to Sum of monthly Income.
• Click OK.

The result shows only the 5 employees (EmployeeID) with

the lowest monthly income (Table 7-14).

214
 Table 7-14: Bottom 5 employees based on their income

Based on the table above, the sum of monthly income of

bottom 5 employees is ETB 5,284.00.

Filter a pivot table for Top 10 Percent

In addition to filtering for the top or bottom items, you can
use a Value Filters to show a specific portion of the grand
total amount. In the table below, you can see that the grand
total monthly income for all employees (HRMDATA) is
9,559,309.00.

Table 7-15: Part of the pivot table based on EmployeeID

and Sum of Monthly Income

Ten percent of the grand total is ETB 955,930.90, and you

can use a Top 10 filter to find the top or bottom amount of

215
income combine to total at least that amount. To see only
the top number of employees who earn 10% of the total
monthly income, follow these steps.
• In the Pivot Table, click the drop-down arrow in the
EmployeeID field heading.
• In the menu, click Value Filters, then click Top 10.
• In the Top 10 Filter dialog box, change the settings to:
Top 10, Percent, Sum of monthly Income.

Figure 7-36: Top 10% Filter dialog box

The results show that only 50 (3.4%) employees

(EmployeeID) earn top 10% of total monthly income as
their combined monthly income (i.e., ETB 972,523.00) is
greater than or equal to 10% of the original grand total
monthly income (i.e., ETB 955,930.90).

Filter a pivot table for the Bottom Sum

Another way to use the Top 10 Value Filter is to find the
items that make up a specific sum. For example, from the
employees with the lowest monthly income, how many
employees (with their monthly income) would combine at
least ETB 1,000,000? To see only the bottom number of

216
employees who earn ETB 1,000,000 of the total monthly
income, follow these steps.
• In the Pivot Table, click the drop-down arrow in the
EmployeeID field heading.
• In the menu, click Value Filters, then click Top 10.
• In the Top 10 Filter dialog box, change the settings to:
Bottom, 1000000, Sum, Sum of Monthly Income.

Figure 7-37: Top 10 Filter dialog box (Sum option)

As you can see in the partial pivot table below, the bottom
412 employees earn only total ETB 999,788.00, so the 413th
lowest income earner (with monthly income of ETB 1,009)
is also included in the Value Filters results to achieve the
ETB 1,000,000 minimum. Note that the employee list starts
from Row 2 and the 413th list is located in Row 414.

The results show only 413 (28% = 413/1470)

employees are earning bottom sum of ETB 1,000,000.00,
because their combined monthly income is at least ETB
1,000,000 (actually it is ETB 1000,797.00). This implies
that 28% of the employees are earning about
10%(=9,559,309*10%) of the total monthly income

217
whereas 50 employees (3.4%) earn top ten percent of the
total monthly income as discussed above.

Table 7-16: The bottom 10 number of employees who earn

a sum of income of at least ETB 1,000,000

7.3.3 Allow multiple filters per field

As mentioned, Excel has two types of filters available for a
pivot table field. These are Label Filters (for text data) and
Value Filters (numerical data).

Example 7.11: Let us say you wanted to filter a pivot table

on all Education Field that contains “M” (using a Label
Filters) and having a total of employees greater than 100
(using a Value Filters), with the default settings, this is not
possible to have both filters at the same time.

To allow multiple filters per field:

▪ Right-click a cell in the pivot table.
▪ Click PivotTable Options.

218
 ▪ Click the Totals & Filters tab.
▪ Under Filters, add a check mark to ‘Allow multiple
filters per field’.
▪ Click OK.

Figure 7-38: Allow multiple filters per field checked

Now you can use both Label Filters and Value Filters at the
same time on one field and you get the result as shown
below.

Table 7-17: Label and Value filtering

219
Hide PivotTable items
Instead of searching through a long list of items in a drop-
down list, you can use a quick command to hide the selected
items using the steps below.
• Right-click on an item in the Row or Column labels.
• In the menu, click Filter, then click ‘Hide Selected
Items’.
• The item is immediately hidden in the pivot table.

You can use a similar technique to keep only selected items

(and hide most items). To do so,
• Select the pivot table items that you want to keep
visible.
• Right-click on one of the selected items.
• In the menu, click Filter, then click ‘Keep Only
Selected Items’.
• All except the selected items are immediately hidden
in the pivot table.

7.4 Sort options

You can use the sort option to arrange the row or column
fields in the pivot table. There are different ways to sort data
in a pivot table. The fastest way to sort alphabetically in
Excel is:
• Select an item to be sorted.
• Click Data tab>Sort & Filter group.
• -OR-Click Home tab>Editing group>Sort & Filter.

220
• Click either A to Z (lowest to highest) to sort ascending
or Z to A to sort descending (highest to lowest).
• -OR-Right-click on an item you want to sort, and click
Sort. Then, choose click Sort Smallest to Largest (A to
Z) or Sort Largest to Smallest (Z to A).
• -OR-Click the drop-down arrow to the right of a
rows or columns labels in a pivot table and use the Sort
options (Sort A to Z or Sort Z to A).

Note that if you want to sort sub-totals, click on one of the

subtotals and click either A to Z or Z to A in the Sort &
Filter group.

Note that when you sort date values, you get the options as
A to Z (Sort Oldest to Newest) or Z to A (Sort Newest to
Oldest).

Sorting a pivot table manually

You can sort items or row/column labels manually by
dragging.

To sort a pivot table manually:

• In the pivot table, right-click any row or column label
of the field that you want to sort and Choose More Sort
Options>Manual>OK.

221
 Figure 7-39: The Sort options

• Select the label cell of the row or column that you

want to move. If you want to move a group of adjacent
rows or columns, drag to select multiple label cells.
• Hover the pointer over the border of the selected
cell(s) until the pointer changes to a four-way arrow
(called a move pointer). You may have to move the
pointer slowly around the border until it changes to a
move pointer. Drag the selected rows or columns to
another location within the pivot table. As you drag, a
line appears to show where the selection will land
when dropped.
Exercise 7.7: Let us assume you like the department should
be listed in the following order: Sales, Human Resources
and Research and Development. The previous sort options
will not allow you to perform this type of sort. You should
do it manually, drag Sales to be the first in the list and you

222
get a new order as shown in Table 7-18. Before it is sorted,
the order was Human Resources, Research and
Development and Sales with 63, 961 and 446 number of
employees.

Table 7-18: Sorted manually in a new order

Note that the Count of Department in Table 7-18 refers to

the number of employees in each department.

Note that if you want to move a row label to ‘Beginning’, to

‘Up’, to ‘Down’ or to the ‘End’, right-click a row label and
choose Move and select one of the options you want.

7.4.1 Sort for Report filter fields

There is no quick Sort option available for Report filter
fields. If you right-click on any label in the Rows area, the
menu shows the Sort commands. The Sort commands is
also available on the Data tab. However, when you right-
click on a Report filter fields, the menu does not have a Sort
command.

223
To sort fields in the Report filter:
• Move (temporarily) the field to the Rows area.
• Sort the field items while it is in the Rows area.
• Move back the field to the Report filter area.

Example 7.12: Using the InvestmentData, prepare a report

based on the following setting.
Rows: Sectors.
Columns: Years.
Values: Investment (Summarized by Sum).
Based on the pivot table, perform the following:
a) Sort the row labels (Z to A).
b) Sort the values (A to Z).
c) Sort the values in each row from largest to smallest
in left to right direction.
With the setting above, you get the pivot table as shown
below.

Table 7-19: The sum of investment by sector and year

a) To sort the row labels from Z to A, right-click on

one of the row labels and choose Sort>Z to A.

224
 b) To sort the values from A to Z, right-click on one
of the values and choose Sort>A to Z.
c) The most common sort direction is from Top to
Bottom. If you want to sort the values in each row
from largest to smallest with a direction of left to
right direction:
• Right-click on one of the values.
• Choose Sort>More Sort Options.
• Sort options>Largest to Smallest; and Sort
direction>Left to Right>OK.

Figure 7-40: Sort By Value options

The report should sort the values in each row from largest
to lowest. For instance, the sorted value for Electricity and
Water Supply sector should be ETB 6,000,000 (2017), ETB
3,213,600 (2017) and ETB 0 (2018).

Note that the value for 2018 was empty and zero is entered
following the path: PivotTable Tools>Analyze tab

225
>PivotTable group>Options> check the ‘For empty cells
show’ checkbox and type zero in the blank box.

Project-6
1. Generate a report on the number and percentage of
employees by income range (increment of ETB
2,000) and gender. Filter the report by department.
a) How many employees (number and %) are
there in the Sales department?
b) Generate a ‘Show Report Filter Pages’ for
each department.
c) In which income range most employees
fall in each department?
2. Based on question #1 above, insert a slicer for the
Education Level field.
a) How many employees who hold a bachelor
degree fall in a monthly income range of
1009 to 3008 in the Sales department?
b) Repeat (a) for all education levels and all
income ranges.
c) Create a report connection for the reports
you generated in #1(b) above and repeat
(a) and (b) for other departments?
3. Generate a report on the distribution of investment
projects (number of investments, amount of
investment, number of employments) by sector,
zone and year. Filter by year and zone.

226
 a) What is the number of investments,
amount of investment, number of
employments for each zone in 2016, 2017
and 2018?
b) Generate a ‘Show Report Filter Pages’ for
each zone and each year.
4. Based on question #3 above, insert a slicer for
Status field.
a) Create a report connection for the reports
you generated in #3(b) above.
b) Slice by the Status of businesses and
determine the number of investments,
amount of investment and the number of
employments for each zone in 2016, 2017
and 2018? Use a single slicer.
5. Generate a report on the Top 5 Investors based on
Sector (Row label) and the amount investment.
Sort the result from largest from smallest.

227
 Chapter 8 : PivotCharts & Dashboards

Focus points
• Creating pivot charts.
• Customizing pivot charts.
• Creating simple Excel dashboards.

PivotCharts are created based on a pivot table. They are the

visual form of a pivot table report. PivotCharts are dynamic
like pivot tables. That is, as you drag fields in the
PivotTable Fields task pane from one area to another, Excel
automatically refreshes the pivot table and pivot chart. This
chapter focuses on creating pivot charts, customizing pivot
charts and creating simple Excel dashboards.

8.1 Creating pivot charts

Pivot charts visualize the true meaning of your data. Excel
makes charting data easy with a variety of chart types and
formatting options. When you create a chart, you need to
select a set of numbers and labels to explain what the
numbers mean. A chart consists of one or more data series
and each data series appears as Column, Pie, Line, etc.

8.1.1 Column chart

A column chart is a data visualization tool where each
category represented by a rectangle, with the height of the
rectangle being proportional to the values.

228
Example 8.1: Create a column chart to visualize the
number of employment opportunities of investment
projects by zone.

To create a pivot chart:

First create a pivot table with the following setting:
Rows: Zone.
Values: Employment (Summarized by Sum).

In the pivot table, the zones are labels with associated

number of employments.
• Select a cell in a pivot table.
• Choose PivotTable Tools>Analyze tab>Tools
group>PivotChart (see the figure below).

Figure 8-1: Insert pivot chart

• -OR-Select a cell in a pivot table.

• Choose Insert tab>Charts group>PivotChart
arrow>PivotChart.

229
 Figure 8-2: The Insert Chart dialog box

• Select a type of chart (e.g., Column chart in this case)

that you want.
• Click OK.

The pivot chart (Clustered Column chart) appears on the

worksheet which shows the number of employments
(vertical axis) by zone (horizontal axis). Each color-coded
bar is subdivided by zone. If you make any change in the
source data, the pivot table and the associated pivot chart
will also be changed when you refresh either the pivot table
or the pivot chart.

230
 Figure 8-3: A newly created column chart

8.1.2 Rename a PivotChart

You can change the default names such as Chart1, Chart2
and so on of a pivot chart to make it more meaningful.

To rename a PivotChart:
• Click the PivotChart and go to PivotChart
Tools>Analyze tab>PivotChart group>Chart Name፡
text box. Type the new name.

8.2 Customizing pivot charts

There several ways to customize a pivot chart.

8.2.1 Chart formatting

When you select a pivot chart, new tabs (contextual tabs)
appear at the Ribbon (under the PivotChart Tools heading):
Analyze, Design and Format. These tabs change the
formatting and layout of the chart and configure chart

231
elements such as chart titles, axes, axis titles, gridlines and
so on.

To select a predefined chart style:

• Click the PivotChart Tools>Design tab>Chart Styles
group.
• Click one of the chart styles that you want to use.

Figure 8-4: The Chart Styles options

Chart Elements
The plus sign icon next to an active pivot chart provides
access to chart elements and the one with the brush sign
contains options for different chart styles (see Figure 8-5).

Figure 8-5: The chart elements option

Chart Elements add, remove or change chart elements such

as the title, legend, gridlines, and data labels. The ‘Add
232
Chart Element’ is also available in the Design tab under the
PivotCharts Tools (PivotCharts Tools >Design tab>Chart
Layouts group>Add Chart Element arrow).

Chart title
To add a chart title a chart:
• Click the chart element.
• Click on the check mark next to the ‘Chart Title’.
• Click the words ‘Total’ located in the chart itself. A
border will appear around that element of the chart.
When a border appears around something in a chart, you
can start typing a new chart title. For instance, type the
following title for the chart above: Number of
employments by zone. When you finish typing, press the
ENTER. The chart will look like the one shown below.

Figure 8-6: Chart with chart title

233
Data labels
Data labels show details about its individual data points.
Therefore, it would be good to include the number of
employments created in your chart.

To add data labels:

• Click the chart elements.
• Hover over the ‘Data Labels’ option and you should
see the number of employments associated with each
zone appearing in your chart.
• By default, Excel places the data labels on the outside
of the columns. When you hover over the ‘Data Labels’
option, you notice that a dark right-facing menu
appears.
• Click this menu to see more data-label specific choices
such as Center, Inside End, Inside Base, Outside End,
Data Callout, or More Options. Again, hovering over
each option provides you a preview of that choice.
• If you choose ‘Outside End’, your chart will look like
as shown below.

234
 Figure 8-7: Data Labels added

Axis titles
Axis titles are identifiers of the data points on a horizontal
or vertical axis.

To add a title for two axes in a pivot chart:

• Click on your pivot chart to select it.
• Click the chart elements and hover over the ‘Axis
Titles’ option, and click the black arrow to reveal
more choices such as ‘Primary Horizontal’, ‘Primary
Vertical’, or ‘More Options’.
• Place a check mark next to the ‘Primary Horizontal’
and ‘Primary Vertical’ options.

By default, Excel placed ‘Axis Title’ placeholders across

the bottom and left edge of the chart. Since Excel has placed
a border around the vertical axis title, you can begin typing
a new description. For instance, you can type: Number of
employments. When you finish typing, press the ENTER
235
key and the change will take effect. Now, change the
horizontal axis title. Click the ‘Axis Title’ and a border
appears around it. Once the border appears, type: Zone.
When you finish typing, press ENTER. The chart looks like
as shown in the Figure 8-8.

Figure 8-8: A chart with chart title and axis titles

8.2.2 Bar chart

A bar chart is a graphical display for depicting qualitative
(categorical) data summarized in a table.

Example 8.2: Prepare a bar chart based on Example 8.1

To create a bar chart:

• Select a cell in a pivot table
• Choose PivotTable Tools>Analyze tab>Tools
group>PivotChart>Bar>Clustered Bar.
• Add chart titles, add axis titles, add data labels and
format chart (change its styles) as needed.

236
Figure 8-9 shows a bar chart for the number of
employments created by zone. Based on the chart, the
greatest number of employments is created in the West
Zone.

Figure 8-9: Bar chart for number of employments by zone

Changing a chart type

You can change a chart type after you created it. That is,
you can switch from a column chart to a bar chart or any
other type of chart.

To change the chart type:

• Click on a chart.
• Click the Design tab>Type group>Change Chart Type.
Select the chart you want.

237
 Figure 8-10: Change Chart Type button

8.2.3 Pie chart

Pie chart is a type of graph in which a circle is divided into
sectors (part of a circle) that each represents a proportion of
the whole.

Example 8.3: Change the bar chart in the Figure 8-9 into a
pie chart.
To change the bar chart to a pie chart:
• Click on your chart and click Design tab>Type
group>Change Chart Type.
• Choose the 3-D pie and the pie chart.
• Add chart titles and format the chart as you want.
• In the chart elements, choose Data Labels> More
Options.
• From the ‘Label Options’ select the ‘Category Name’,
‘Value’ and ‘Percent’ options.
• From the ‘Label Position’ select the ‘Best Fit’ option
and you get the pivot chart as shown below.

238
 Figure 8-11: Number of employments by zone

The pie chart in the figure above shows the same

information as the column and bar charts above.

If you check the Legend option in the chart elements, the

legend appears either to the Right, Top, Left or Bottom. The
figure above does not have a legend as it is removed
purposely.

8.2.4 Line chart

Line charts are used to depict data that change over time.
It is used to display trends over time.

Example 8.4: Prepare a line chart based the amount of

investment registered by Month.

To create a line chart:

• First create a pivot table based on the following
setting. Rows: Registration Date and Values:
Employment. Remove Quarters from the Rows

239
 box and keep Years and Registration Date in the
Rows box.
• Select a cell in the pivot table.
• Choose PivotTable Tools>Analyze tab>Tools
group>PivotChart>Line (the first option).
• You get a line chart as shown below.

Figure 8-12: The number of employments by registration

date (year, month)

Using the +/- signs in the Figure 8-12, you can collapse or
expand fields from the pivot chart.

As per Figure 8-12, there is a fluctuation in the number of

employments created by investment projects and during
October, November and December (2017), there was a
sharp decline in the number of employments.

240
8.2.5 Apply group filters in a pivot chart
You can also apply group filters directly on a pivot chart.
To do so, click one of the field buttons on the chart to show
a drop-down list of filtering options (see Figure 8-14).

Figure 8-13: You can apply group filters to a pivot chart

If you do not need the field buttons in your chart, you can
hide them by choosing PivotChart Tools>Analyze
tab>Show/Hide>Field Buttons arrow>Hide All (if you
click on the drop-down arrow, you get more options.

Figure 8-14: Field Buttons options

241
For instance, if you want to hide all fields of the line chart
in the Figure 8-12, choose the ‘Hide All’ option and your
line chart looks like as shown in the Figure 8-15.

Figure 8-15: A line chart (all field buttons are hidden)

8.2.6 Change colors

If you want to change the default colors of your pivot
charts, you can access the ‘Colors’ options by clicking
the PivotChart and choosing PivotChart Tools>Design
tab>Chart Styles group> Change Colors as shown below.

242
 Figure 8-16: Change Colors options

Experiment with a wide variety of color options and select

the one that you like.

8.2.7 Insert shape

You can insert shapes over your chart.

To insert a shape:
• Select your chart to activate it.
• Click PivotChart Tools>Format tab>Insert Shapes
group.
• Locate the down arrow with a line over it within the
Shapes icon -OR-You can inset shapes by clicking
Insert tab>Illustrations arrow>Shapes arrow. A menu
with different shapes will appear.
• Click any one of the shapes, and click anywhere on
your chart, and the shape will appear.

243
 Figure 8-17: Insert Shapes button

Add color to a shape

To add color to inside a shape:
• Select the shape to activate it.
• Click Drawing Tools>Format tab> Shape Styles
group> choose Shape Fill button and select a color. If
you do not need a color inside of your shape, choose
No Fill option.

To select a color for the outer edge of a shape:

• Select the shape to activate it.
• Click Drawing Tools>Format tab> Shape Styles
group> choose the Shape Outline button and choose a
color. If you do not need a color for the outer edge of
your shape, choose No Outline option.

8.2.8 Adding Text Box to your chart

You can add text to a chart.
To add a text box on a chart:
• Click the chart to which you want to add a text box.
• On the Format tab>click Insert Shapes>click Text Box
icon which is a square with the letter A in it and some
horizontal lines

244
 • In the chart, click where you want to start a corner
of the text box, and then drag until the text box is
the size that you want.

Figure 8-18: Text Box button

To type a text in the Text Box:

After you click the Text Box button, point your mouse at
the chart and click once where you want to type and start
typing. Click once elsewhere on your worksheet when you
finish typing.

To move your text box:

• Click once on the words. A small box will surround your
words.
• Place your mouse on this box, and your mouse will
change to a four-headed arrow. When this happens, hold
down your left mouse button and drag your mouse to
reposition the text.
• When it is in the correct location, simply release the
mouse button.

245
To change the color of a text in a text box:
• Triple-click your text. All the characters in the text
string should be highlighted.
• Click Format tab > WordArt Styles group >Text Fill
button to change the color of your words.

To change the color inside a text box:

• Select the text box to activate it.
• Click Drawing Tools>Format tab> Shape Styles
group> choose Shape Fill button and select a color.

To select a color for the outer edge of a text box:

• Select the text box to activate it.
• Click Drawing Tools>Format tab> Shape Styles group>
choose the Shape Outline button and choose a color. The
Shape Outline button places a colored border around
your text box.

Resize the shape of Text Box

To resize the shape of the text box:
• Click the text box and place your mouse over one of
the small squares in the corner of the box.
• The mouse changes into a double-headed arrow.
• Hold down the left mouse button and drag your mouse
to change the size and shape of your text box.

246
8.2.9 Insert the cell content into a shape
If you are creating a pivot chart with shapes, you might
need to update the shape text automatically depending on
the value or text in a cell.

To insert a cell content into your shape:

• Select the shape or text box.
• In the formula bar, type the equal ("=") symbol.
• Click the cell reference that contains the data or text
you want to insert into the selected shape or text box
and press ENTER.

Note the following:

• Pivot charts are linked directly to your data and if you
change a number or label in your worksheet, the change
will be automatically reflected in your chart.
• A pivot table and a pivot chart are joined in a two-way
link. If you make changes or filtering to one, the other
is also changed.
• When you activate a pivot chart, the PivotTables Fields
task pane changes to PivotChart task pane. In this task
pane, Legend (Series) replaces the Columns area, and
Axis (Categories) replaces Rows area.
• The field buttons in a pivot chart contain the same
controls as the pivot chart’s field headers. These
controls allow you to filter the data that is displayed in
the pivot table (and pivot chart). If you make changes

247
 to the chart using these buttons, those changes are also
reflected in the pivot table.
• If you have a pivot chart and you delete the underlying
pivot table, the pivot chart remains intact. However,
deleting a pivot table turns any of its associated pivot
charts into standard charts and you can no longer
update it.
• By default, pivot charts are embedded in the sheet that
contains the pivot table. To move the pivot chart to a
different worksheet (or to a Chart sheet), choose
PivotChart Tools>Analyze tab>Actions group>Move
Chart.
• Slicers and timelines also work with pivot charts.

8.3 Create simple Excel dashboard

Excel dashboard is a visual display of the most important
information needed to achieve objectives so it can be
monitored at a glance and gives a quick insight. Dashboards
are interactive tools which turn data into information by
creating different pivot tables, pivot charts, slicers,
timelines and other visual elements that give you a high-
level overview of that data. You can connect a slicer to all
the pivot tables (and its associated pivot charts) to control
entire dashboard with a single slicer. In this section, a
simple Excel dashboard is briefly demonstrated based on
the human resources data.

248
8.3.1 Excel dashboard considerations
Before creating a dashboard, you need to consider the
following points.
• Set goals: It is important to get clear on the why of
your dashboard first.
• Insert your data into Excel: To create a dashboard,
your data first needs to exist in worksheet.
• Clean your data: Before you create a dashboard,
clean the data and ensure it is well organized.
• Set up your workbook: To create a dashboard, you
may need three separate sheets (or tabs) within your
Excel workbook to contain the data source, the chart
for the data and the dashboard where your various
charts will appear.
• Creating pivot table and pivot charts: Create a few
different pivot tables and pivot charts, look at the
results, format them, and see which ones make the
most sense for visualizing your data. Besides, you can
connect pivot tables using slicers and timelines as
necessary.
• Designing and assembling a dashboard: You can
design a dashboard as you like. First create a pivot
table and create a pivot chart based on that pivot table.

Example 8.5: Prepare a simple Excel dashboard based on

sample HRMDATA for the following pivot table reports:
1. total number of employees;
2. the number of employees by gender;
249
 3. the number of employees by department;
4. the number of employees by education field;
5. the number of employees by education level;
6. the number of employees by position; and
7. income ranges by number of employees.
8. the number of employees by gender and
department.

The first step in creating an interactive dashboard is to

create pivot tables and corresponding pivot charts for the
data. Then, these charts will be assembled and formatted as
required. Shapes and other visual tools will also be added
to make the dashboard more informative and interactive.

Create a pivot table and pivot chart for the following

summary reports:

Total number of employees. To generate this pivot table

report: Drag the ‘Gender’ field to the ‘Values’ box
(Summarized by Count) and you get the total number of
employees as 1470. You can change the ‘Count of Gender’
label to ‘Total Number of employees’ in your pivot table.

The number of employees by gender. To get the number

of employees by gender:
• Drag the ‘Gender’ field to the ‘Rows’ box.
• Drag the ‘Gender’ field to the ‘Values’ box
(Summarized by Count).

250
As mentioned above, in the chart elements, choose Data
Labels> More Options. From the ‘Label Options’ select the
‘Category Name’, ‘Value’ and ‘Percent’ options and from
the ‘Label Position’ select the ‘Best Fit’ option and you get
the pivot chart (pie chart) as shown below.

Figure 8-19: Employees by Gender

The figure above shows that 40% of the employees are

females whereas the rest are males.

The number of employees by department. To generate

this report:
• Drag the ‘Department’ field to the ‘Rows’ box.
• Drag the ‘Department’ field to the ‘Values’ box
(Summarized by Count) and you get the pivot table.
Sort from smallest to largest.
• Add chart title and data labels.

251
The corresponding pivot chart is depicted in the Figure 8-20
which shows that the highest number of employees (961)
are working in the Research and Development department.
Note that as mentioned, you can select a predefined chart
style (PivotChart Tools>Design tab>Chart Styles group)
and choose the chart style that you want to use.

Figure 8-20: Number of employees by department

The number of employees by education field. To

generate this report:
• Drag the ‘Education Field’ field to the ‘Rows’ box.
• Drag the ‘Gender’ field to the ‘Values’ box
(Summarized by Count) and you get the pivot table.
Sort from largest to smallest.
• Add chart title and data labels. You can also format
the chart as you like.

252
The corresponding pivot chart is depicted in the Figure 8-21
where Life Sciences is the leading education field in the
human resources profile.

Figure 8-21: Number of employees by education field

The number of employees by education level. To

generate this report:
• Drag the ‘Education Level’ field to the ‘Rows’ box.
• Drag the ‘Gender’ field to the ‘Values’ box
(Summarized by Count) and you get the pivot table.
• Add chart title and data labels.

The corresponding pivot chart is shown in the Figure 8-22

where employees with Bachelor degree are the highest in
number.

253
 Figure 8-22: Number of employees by education level

The number of employees by position. To generate this

report:
• Drag the ‘Position’ field to the ‘Rows’ box.
• Drag the ‘Gender’ field to the ‘Values’ box
(Summarized by Count) and you get the pivot table.
• Add chart title and data labels.

The corresponding pivot chart is depicted in the Figure 8-23

where Sales Executive is the leading position in the human
resources profile.

Figure 8-23: Number of employees by position

254
Income ranges by number of employees report. To
generate this report:
• Drag the ‘Monthly Income’ field to the ‘Rows’ box.
• Drag the ‘Gender’ field to the ‘Values’ box
(Summarized by Count) and you get the pivot table.
• Group the income range with the increment of ETB
2,000.

The corresponding pivot chart is depicted in the Figure 8-24

which shows that 1036 (= 395 + 357 + 284) employees
are falling within the monthly income range of ETB 1009
to ETB 7008.

Figure 8-24: Income range by number of employees

The number of employees by gender and department.

To generate this report:
• Drag the ‘Department’ field to the ‘Rows’ box.

255
• Drag the ‘Gender’ field to the ‘Columns’ box.
• Drag the ‘Gender’ field to the ‘Values’ box
(Summarized by Count) and you get the pivot table.
• Add chart title and you can also format the chart as you
like.
• In the chart elements, choose Data Labels> More
Options.
• From the ‘Label Options’ select the ‘Series Name’,
‘Category Name’, and ‘Value’ options.

The corresponding pivot chart is depicted below. For

instance, there were 582 male and 379 female employees in
the Research and Development department.

Figure 8-25: Number of employees by gender and

department

256
8.3.2 Create simple dashboard
You can create a simple Excel dashboard in a single page
by assembling and formatting the above pivot charts with
other visual tools.

To create a dashboard:
1. Create a new sheet and rename it as simple
dashboard.
2. Insert a rectangle in the new sheet by choosing Insert
tab>Illustrations arrow>Shapes arrow>Rectangle.
For instance, you can insert rectangle in Excel ranges
A1:Q42. This rectangle will serve as a working space
to organize the pivot charts, slicer(s) and other visual
tools to create the dashboard.
3. Change the color of the rectangle as you want
(Drawing Tools>Format tab> Shape Styles group>
choose Shape Fill button and select a color). For
instance, Blue-Gray, Text 2 is chosen. You can hover
over each color option to read its description.
4. Remove the shape (rectangle) outline (Drawing
Tools>Format tab> Shape Styles group> choose
the Shape Outline button and choose No Outline).
5. Insert a Text Box and add a dashboard title. For
instance, add the title STAFF PROFILE inside the
Text Box. Center the title and change its font to
‘Times New Roman’ and font size to 18, Bold it,
resize the Text Box and remove the White Fill from
the Text Box (Drawing Tools>Format tab> Shape

257
 Styles group> choose Shape Fill button and select No
Fill).
6. Change the Shape Outline of the Text Box (of the
dashboard title) to Blue-Gray, Text 2 to match the
overall outline as you did in #3 above.
7. Change the color of text in the Text Box to White
(Home tab>Font group>Font Color>Select White
option).
8. Set aside a place for slicer(s) on the left side of the
shape (rectangle) in #2 above by inserting a smaller
rectangle and format it (change its color, resize it, and
so on).
9. Now copy (CTRL+C) and Paste (CTRL+V) all the
charts into the dashboard you are creating. Place the
charts where you want them to be. You can resize and
format the charts as you like.

Formatting the charts

• Format all charts by clicking Drawing
Tools>Format tab> Shape Styles group> choose Shape
Fill button and No Fill.
• Change the font of the texts, data labels or legends in
the pivot charts as you want.
• Change the color of the texts, data labels or legends in
the pivot charts to White as you did in #7 above.

258
Add slicer(s) in the dashboard
A slicer controls what you see in your dashboard. If you
click on a slicer, it only filters one pivot chart. You can link
a slicer with multiple pivot tables (which all also connect
all the corresponding pivot charts). You can add as many
slicers as you want and control all the pivot tables (pivot
charts) by a click of a single slicer. In this sample Excel
dashboard, a slicer for Gender field is added and connected
with all the pivot tables. If you want, you can change Slicer
Style to match with the overall design of the dashboard.

Insert the cell content to a text box

You can add a Text Box and type ‘TOTAL EMPLOYEES’
inside the text box.
• Select the text box.
• In the formula bar, type the equal (=) symbol.
• Click the cell reference (in this case
=Total_Employees!A4) that contains the data or text
you want to insert into the selected shape or text box
and press ENTER.

‘=Total_Employees!A4’ shows that the number for total

employees is located at a sheet named ‘Total_Employees’
at cell A4.

Group or ungroup pivot charts in Excel

You can group pivot charts to move or resize all of them at
the same time as though they were a single shape.

259
To group pivot charts:
• Press and hold CTRL while you click the shapes to
group and select more than one pivot chart in order to
enable the Group button.
• To group shapes, click Drawing Tools>Format
tab>Arrange group > Group arrow>Group.
Now you can see how the charts come together.

Figure 8-26: Group button

After you make your group, you can still work with a single
item in the group. Select the group, and click the item to
select it. Now you created your interactive dashboard. The
final sample Excel dashboard is shown in the Figure 8-27.
You should use the same Font and Font Size to the chart
titles and use your own artistic skill to format and develop
an interactive dashboard aligned with your objective.

260
 Figure 8-27: Sample Excel Dashboard

Note that if you change your source data, just refresh all (if
the pivot table is created from an Excel Table) then your
dashboard will be updated instantly.

Project-7
1. Create a pivot chart (bar chart) on the amount of
investment by zone.
2. Create a pivot chart (column chart) on the number
of employees by gender and employment year.
3. Create a pivot chart (pie chart) on the number and
% of employees by education level.
4. Create a pivot chart (line chart) on the number or
of employees by employment year.
261
5. Create a pivot chart (column chart) on average
monthly income of employees by age range
(increment of 10) and gender.
6. Create a pivot chart on the amount of investment
by Project Status.
7. Create a pivot chart on the number of
employments by Project Status.
8. Create a pivot chart on the amount of investment
by Zone.
9. Prepare a simple Excel dashboard based on
Investment Data for the following reports:
• total number of investment projects.
• total amount of investment.
• total number of employments.
• the number of investment projects by zone.
• the number of investment projects by
sector.
• the amount of investment by sector and the
number of employments created by sector.

262
References

(2019). Civil Servants Statistical Report. Bahir Dar,

Ethiopia: Amhara National Regional State Civil
Service Commission.
Alexander, M., & Kusleika, D. (2016). Excel 2016
Formulas. Indianapolis, IN, USA: John Wiley &
Sons, Inc.
Hill, T. (2016). Excel 2016 PivotTables for Windows. Las
Vegas, Nevada, USA: Questing Vole Press.
(2018). Investment Statistical Bulletin, No. 8. Bahir Dar,
Ethiopia: Amhara National Regional State
Investment Commission.
(2017). Statistical Report on Federal Civil Servants. Addis
Ababa, Ethiopia: FDRE Civil Service Commission.
Wambolt, C. (2016). www.ed2go.com. From
www.ed2go.com.
Winston, W. L. (2014). Microsoft Excel 2013 Data
Analysis and Business Modeling. California:
Microsoft Press.
www.microsoft.com. (2021, 08). From
www.microsoft.com: www.microsoft.com

263