Excel Dynamic Dashboards:

PivotTables, PivotCharts, and Slicers

[email protected]
 Excel Dynamic Dashboards:
PivotTables, PivotCharts, and Slicers
1.0-hour Zoom Session / 1.5-hour Classroom
Build Dashboard 1 ............................................................................................................... 3
Create PivotChart ............................................................................................................ 3
PivotChart Filtered Title .................................................................................................. 5
Add Dynamic Shapes....................................................................................................... 6
Dashboard 1 - Completed ............................................................................................... 7
Build Dashboard 2 ............................................................................................................... 8
Create Pivot Tables ......................................................................................................... 8
Create Pivot Charts ......................................................................................................... 8
Insert Slicers .................................................................................................................. 10
Dashboard 2 - Completed ............................................................................................. 11
What are Slicers? (Excel Help File) .................................................................................... 12
Build Dashboard 3 ............................................................................................................. 14
Format as a Table .......................................................................................................... 14
Create Summary by Employee...................................................................................... 14
Create Summary Sparklines .......................................................................................... 15
Create a Pie Chart ......................................................................................................... 16
Create Summary by Fiscal Year ..................................................................................... 16
Conditional Formatting for the top/bottom value ....................................................... 16
Conditional Formatting with Icons ............................................................................... 17
Create Charts ................................................................................................................ 18
Format Charts to not Resize with Cells ......................................................................... 19
Dashboard 3 - Completed ............................................................................................. 20

Pandora Rose Cowart

Education/Training Specialist
UF Health IT Training

E206 Professional Park (352) 273-5051

PO Box 100002 [email protected]
Gainesville, FL 32610-0002 http://training.health.ufl.edu

Class Evaluation: https://ufl.qualtrics.com/jfe/form/SV_1Ojjkl6lRsKV3XT

Updated: 07/22/2022
Build Dashboard 1
Create PivotChart
When you create a PivotChart, Excel will create a
PivotTable to support the chart. I recommend
building the PivotTable before creating the chart.
1) Open File: DynDash-Sales.xlsx
2) From the Insert tab, choose PivotTable
- Click OK to make a new PivotTable based
on the current dataset on a new
worksheet.
3) Build the table
- Row: Items
- Column: Size
- Value: Sum Price

4) On the PivotTable Analyze tab, choose PivotChart.

- If needed, click OK to accept the Clustered Column chart

5) Move the chart to a new sheet

- Option is the last button on the Design tab and can be found on the right-click menu
- If you don't see the Design and Format tabs in the ribbon, click on the chart again

3
6) Add the field Color to the PivotChart's Legend area

7) Return to PivotTable, move Color from Columns into Rows

4
PivotChart Filtered Title
1) Add the field Quarter to the PivotChart's
Filters area

2) Filter the chart to only show 1st Quarter

3) We can see the filtered value above the

table but not on the chart.

4) Click anywhere in the chart. Look for the big plus sign in the
upper right-hand corner. Click on it and choose Chart Title.

5) Click on the newly created Chart Title. Do not type inside the
title, instead once it is selected click in the formula bar.

6) Press the equal sign on the keyboard, use your

mouse to move back to the PivotTable, click in Cell B1
and press Enter

7) Change the Filter to 2nd Quarter

RESULT

5
Add Dynamic Shapes
1) Move Chart back to Sheet 1
- Option is the last button on the Design tab and can be found on the right-click menu
- If you don't see the Design and Format tabs in the ribbon, click on the chart again

2) Resize the chart to fill the space

3) From the Insert tab, choose Shapes

4) Click on a shape, and drag across the window

5) Copy and paste the shape twice so you have three

shapes, or make two new shapes from the Insert tab

6) Color them Red, White, and Blue

7) Click on the Blue shape and in the Formula bar put =B17
- Or where your Color Red Total is in the worksheet
- Do not click in the cell this time, it needs to be the
address, not the PivotTable

8) Repeat for the Red and White shapes

9) You can format the Font and Alignment from the Home tab, but if you want the number to be
formatted you have to change the cell.

10) Arrange and rotate

11) Change the Quarter filter and see the numbers change

6
Dashboard 1 - Completed

7
Build Dashboard 2
Create Pivot Tables
1) If needed, open the DynDash-Sales.xlsx file

2) From sheet Sales data, Insert a Pivot table on a new sheet

3) Create the PivotTable as shown here 

- Row: Items
- Column: Color
- Value: Count of Items

4) Then use the PivotTable Tool Tabs to modify the look of the table.

5) Customize if desired
- Remove Field Headers (Analyze tab)
- Change Count of Item to # of Items by Color

6) Copy the # of Items by Color PivotTable and paste several columns over
- Paste in Cell J3

7) From the column headings, remove the Color and add Size

8) Rename to # of Items by Size

Create Pivot Charts

1) Build a (clustered) column chart for each table.
- Click in the table
- Go to the Insert Tab
- Choose Recommended Chart
- Click OK

2) Customize the Charts

- Remove the Field buttons (Analyze tab)
- Move the Legend to the top of the charts (Add Chart Element on the Design tab)
- Change the color scheme of each chart to be different (Design tab)

8
 3) Resize both charts at once
- Use the Shift key to select both charts at the same time (select one, shift-click the other)
- Change the Height to 1.5", the Width to 3" (far right side of the Format tab)
- Align the charts to the top of each other (Format tab)

SAVE YOUR FILE!

4) Make the final PivotTable

- Copy one of the pivot tables and paste
into cell A11
- Clear the Pivot table
5) Set up the Pivot Table
- Row: Fiscal Years, Quarter
- Column: Item
- Values: Sum of Price

6) Modify the table

- Hide the +/- buttons (Analyze tab)

7) Create a Stacked Column chart

- Remove the Field buttons (Analyze tab)
- Move the Legend to the top of the charts (Add Chart Element on the Design tab)

9
Insert Slicers
We can add a filter to each of the tables, but a Slicer can filter all three at
once.

1) Click inside any PivotTable

2) Insert Slicer from the Analyze tab

3) Click the check box for County and City, click OK

4) Notice these values are not in our tables, but we can still use
the slicer to filter by the values we choose.

5) While the slicer is selected choose Report Connections from

the Options tab

6) Check all three tables on the list for this worksheet and click OK

7) Repeat for the other Slicer, so both are tied to all three tables

8) Click inside a table

9) From the Analyze tab choose Insert Timeline

- Choose Date from the list

SAVE YOUR FILE!

10
Dashboard 2 - Completed

11
What are Slicers? (Excel Help File)
Slicers are easy-to-use filtering components that contain a set of buttons that enable you to quickly filter
the data in a PivotTable report, without the need to open drop-down lists to find the items that you
want to filter.

When you use a regular PivotTable report filter to filter on multiple items, the filter indicates only that
multiple items are filtered, and you have to open a drop-down list to find the filtering details. However,
a slicer clearly labels the filter that is applied and provides details so that you can easily understand the
data that is displayed in the filtered PivotTable report.

A slicer typically displays the following elements:

1. A slicer header indicates the category of the items in the slicer.

2. A filtering button that is not selected indicates that the item is not
included in the filter.
3. A filtering button that is selected indicates that the item is included in the
filter.
4. A Clear Filter button removes the filter by selecting all items in the slicer.
5. A scroll bar enables scrolling when there are more items than are
currently visible in the slicer.
6. Border moving and resizing controls allow you to change the size and
location of the slicer.

Using Slicers

There are several ways to create slicers to filter your PivotTable

data. In an existing PivotTable, you can:

Because each slicer that you create is designed to filter on a

specific PivotTable field, it is likely that you will create more than
one slicer to filter a PivotTable report.

After you create a slicer, it appears on the worksheet alongside the

PivotTable, in a layered display if you have more than one slicer.
You can move a slicer to another location on the worksheet, and
resize it as needed.

To filter the PivotTable data, simply click one or more of the buttons in the slicer.

Formatting Slicers for a Consistent Look

To create professional looking reports or simply to match the format of a slicer to

the format of the associated PivotTable report, you can apply slicer styles for a
consistent look. By applying one of the various predefined styles that are
available for slicers, you can closely match the color theme that is applied to a This page is modified
PivotTable. from the Excel Help file

Page 12
Sharing slicers between PivotTables

When you have many different PivotTables in one report, such as a Business Intelligence (BI) report that
you are working with, it is likely that you will want to apply the same filter to some or all of those
PivotTables. You can share a slicer that you created in one PivotTable with other PivotTables. No need to
duplicate the filter for each PivotTable!

When you share a slicer, you are creating a connection to another PivotTable that contains the slicer that
you want to use. Any changes that you make to a shared slicer are immediately reflected in all PivotTables
that are connected to that slicer. For example, if you use a Country slicer in PivotTable1 to filter data for a
specific country, PivotTable2 that also uses that slicer will display data for the same country.

Create a slicer in an existing PivotTable

1. Click anywhere in the PivotTable report for which you want to create
a slicer. This displays the PivotTable Tools, adding an Options and a
Design tab.
2. On the Options tab, in the Sort & Filter group, click Insert Slicer.
3. In the Insert Slicers dialog box, select the check box of the PivotTable
fields for which you want to create a slicer.
4. Click OK.
A slicer is displayed for every field that you selected.
5. In each slicer, click the items on which you want to filter.
To select more than one item, hold down CTRL, and then click the
items on which you want to filter.

Format a slicer
1. Click the slicer that you want to format. This displays the Slicer Tools, adding an Options tab.
2. On the Options tab, in the Slicer Styles group, click the style that you want. To see all available styles,
click the More button .

Disconnect or delete a slicer

If you no longer need a slicer, you can disconnect it from the PivotTable report, or you can delete it.

Disconnect a slicer
1. Click anywhere in the PivotTable report for which you want to disconnect a slicer. This displays the
PivotTable Tools, adding an Options and a Design tab.
2. On the Options tab, in the Sort & Filter group, click the Insert Slicer arrow, and then click Slicer
Connections.
3. In the Slicer Connections dialog box, clear the check box of any PivotTable fields for which you want to
disconnect a slicer.

Delete a slicer
Click the slicer, and then press DELETE; or Right-click the slicer, and then click Remove <Name of slicer>.

This page is modified

from the Excel Help file

Page 13
Build Dashboard 3

Format as a Table
1) Open File DynDash-Staff.xlsx

2) Click inside the dataset

3) From the Home tab choose Format as Table and choose a style and click OK

4) From the Table Design tab, turn on the Totals

5) Change each total to be a SUM

Create Summary by Employee

1) Copy the names of the employees

2) Right-click in an empty cell

- Suggest G2

3) Choose Paste Transpose

4) Link Ann to the corresponding Sum

- Click next to the title Ann
- Type the equal sign =
- And click, or type in, Ann's total
- Probably B26

5) Repeat for the other three employees

Page 14
Create Summary Sparklines
1) Click in the cell next to Ann's Total

2) From the Insert tab, choose Column from the Sparkline group

3) Select all of Ann's numbers and click OK

4) Repeat for the other three employees

5) Filter out 2020, numbers and charts change

6) Filter out 2018, numbers disappear as those rows hide

7) Insert five rows at the top of the sheet

8) Cut and paste your summary table above the data table

9) Try the filters again

10) Select the Sparklines (columns) and the cells next to them

11) From the Home tab, open the Merge and Center menu and choose Merge Across

Page 15
Create a Pie Chart
1) Select the names and numbers from your summary table

2) From the Create tab, choose a simple Pie chart

3) Format and resize as desired

Create Summary by Fiscal Year

1) Staying in the top five rows create a table to sum the Fiscal Years for each employee

2) Ignore the green triangles, Excel is concerned the formulas may be incorrect because they are
not following a pattern.
3) For a "Dynamic" interface, Use Find/Replace to fix formulas.
- Find SUM( replace with SUBTOTAL(9,

Conditional Formatting for the top/bottom value

1) Select all the sums in the new
summary

2) From the Home tab, choose

Conditional Formatting

3) Choose Top 10 values and change

the 10 to 1

4) Change the format to Green Fill

with Dark Green text

5) Add another conditional format for the Bottom 1 value, and format Light Red with Dark Red Text

Page 16
Conditional Formatting with Icons
1) In the cell next to Ann's FY 19-20 value, set an
equation to find the difference between the Fiscal
Year cells
- =Q2-P2

2) Fill the formula down

3) Select the four numbers and open the Conditional

Formatting menu

4) Choose an Icon Set

5) Open the Conditional formatting menu again and

choose Manage Rules

6) Choose the option Edit Rules

7) Check the box for Show Icon Only and change the Icon Style, if desired

Page 17
Create Charts
1) Create a column chart for the Summary by Fiscal Year

2) Create a stacked column chart for the original data table

Page 18
Format Charts to not Resize with Cells
1) Use the Shift key to select all three charts

2) From the Format tab, click the "more" button in the bottom right side of the Size group

3) This should open the Format Shape pane

4) If necessary, Turn to the Size and Properties tab

5) If necessary, open the Properties section by clicking the arrow in front

6) Choose the setting, Move but don't size with cells

7) Change the filters in the original data table to see your values and charts change.

Page 19
Dashboard 3 - Completed

Source: https://media.news.health.ufl.edu/misc/training/Handouts/zoom/Excel/Excel-DynDashboards.pdf

Page 20