Excel - Dashboards One

In this assignment we will create dashboards.

Instructions:

Read through the summary of key points. This is what you should know when you finish the assignment,

Watch the YouTube videos before starting the lesson.

Complete every step in the assignment, in the order given. Do not skip steps.

When you have done all of the steps, fill out the Check for Understanding (CFU) form.

A completed CFU for every lesson is due to be turned in during your next class. 

The CFU forms are not to be submitted by email.

Where there are differences between software versions, the instructions for Excel 2010 are in Black, for the Mac are in green and for Excel 2013 are in blue.

 

Summary of Key Points:

Use conditional cell formatting to visually represent data.

Create a dashboard using Conditional Formatting.

Create a dashboard using Pivot Table Slicers.

Create a checklist dashboard.

Use spark lines to show a trend 

Create a dashboard using Pivot Table Slicers.

 

Videos: 

Insert and Format Objects     Time = 4:55

https://www.youtube.com/watch?v=1kMGv_Kqnvo

 

Create a Trendline     Time = 0:34

https://www.youtube.com/watch?v=o34x1h-Byw4

 

Apply Conditional Formatting      Time = 1:00

https://www.youtube.com/watch?v=YSdQKNIniJU

 

Conditional Formatting

Dashboard Formatting

Introduction to Pivot Tables, Charts, and Dashboards for the MAC

Create a checkbox

Conditional Formatting Excel 2016

 

Extra Information

2010 Speedeometer Dashboard

2013 Speedometer Dashboard

2013 Gantt Chart

2013 Beer Mug DashBoard

2013 Thermometer Chart

 

What is a dashboard?

Dashboards show Key Performance Indicator (KPI) numbers as graphics. KPI measures are associated with a goal and an action plan. When the goal is not reached, it requires a response. Managers analyze the problem and take corrective action. Using color and shapes in a dashboard makes the numbers easier to spot and to comprehend.

 

The Quick Analysis Tool

This is a feature created for Excel 2013. If you are using an older version of the software, or a MAC, you can skip the first three steps and go right to conditional cell formatting.

1.       In a new Excel workbook, enter the numbers 1 to 12 in cells A1 to A12. Mouse over the column of cells to select it. To the lower right of cell A12 you should see the Quick Analysis icon. Click on the icon. 

Clicking on the icon gives you a wide variety of ways to quickly format the cells in colorful ways.  Your choices will be from five categories: Formatting, Charts, Totals Tables, and Sparklines. Each category has multiple options to choose from.

2.       Click on a few of the options to see how they affect the data. We will be working with some of these tools in this lesson.

3.       Use undo (Ctrl + Z) to return the cells to their original format.  Delete the contents of the cells.

 

Conditional Cell Formatting

A picture is worth a thousand words. Excel has some built in tools to create charts, but often conditional formatting is all you need. Let’s see how this works with some customer numbers that were obtained from observations at a San Jose outlet of Starbucks Coffee during the summer of 2011.

 

  1. Open a new worksheet and name it CustomerData Enter these labels, numbers, and formulas. The numbers shown are the number of customers served.

 

 

A

B

C

D

E

1

 

Monday

Tuesday

Wednesday

Average

2

Breakfast

275

282

261

=AVERAGE(B2:D2)

3

Lunch

215

246

222

= AVERAGE (B3:D3)

4

Dinner

205

162

210

= AVERAGE (B4:D4)

5

Total

=SUM(B2:B4)

=SUM(C2:C4)

=SUM(D2:D4)

 

6

 

 

 

 

 

 

Add Bullet Points to the four labels in column A as shown in the video

5.       https://www.youtube.com/watch?v=pUZI_B8TcOk&list=PLjQrfrgBLzJOoKa3OYwmNGiAu0OCA8Odu&index=13

Note that ALT+7 doesn’t work with all versions of Excel. You can also try ALT+8.

 

Add Data Bars:

  1. Select the cells in the range B2:B4.
  1. On the Home tab of the ribbon click on the Conditional Formatting icon [ALT  H  L]. Click on the “Data Bars” format. In Excel 2013 use [ALT  H  L  D].Select a Gradient Fill to apply to the range.
  1. Widen column B to see bigger bars.

 

Add Color Scales:

  1. Select the cells in the range C2:C4.
  1. Click on the Conditional Formatting icon [ALT  H  L]. Click on the “Color Scales” format. In Excel 2013 use [ALT  H  L  S]. Select a color scale to apply to the cells.

Add Icons:

  1. Select the cells in the range D2:D4.
  1. Click on the Conditional Formatting icon [ALT  H  L]. Click on the “Icon Set” format. In Excel 2013 use [ALT  H  L  I]. Select one of the ratings set of icons to apply to the cells.

Combining Formats:

You can apply more than one conditional format to cells.

  1. Select the cells in the range E2:E4.
  1. Click on the Conditional Formatting icon [ALT  H  L]. Click on the “Data Bars” format. In Excel 2013 use [ALT  H  L  D].  Select a solid fill color to apply to the cells.
  1. Click on the “Icon Set” format. In Excel 2013 use [ALT  H  L  I] Select one of the shapes to apply to the cells.

Clearing Conditional Formatting

Clear the conditional formatting from a range.

  1. Select the cells in the range B2:B4.
  1. Click on the Conditional Formatting icon [ALT  H  L]. Click on Clear Rules. In Excel 2013 use [ALT  H  L  C]. Select “Clear Rules from selected cells.”

Remove one of multiple formats from a range.

  1. Select the cells in the range E2:E4.
  1. Click on the Conditional Formatting icon [ALT  H  L]. Click on Manage Rules. In Excel 2013 use [ALT  H  L  M].
  1. Click on Data Bars and click on Delete Rule. Click OK.

Clear all conditional formatting from the worksheet.

  1. Click in any cell. Click on the Conditional Formatting icon [ALT  H  L]. Click on Clear Rules. In Excel 2013 use [ALT  H  L  C].  Select “Clear Rules from worksheet.”

You can show conditional cell formatting based on a range of specific values. This video shows you how: Conditional Formatting

 

Creating a Dashboard using Conditional Formatting Rules

YouTube Video:  www.youtube.com/watch?v=HD2sNP5AKH0

  1. Copy and paste this table into the A1: G11 range in a new worksheet Change the name of the worksheet to DashBoards

 

KPI’s   (%s)

 

Time

Cost

Quality

Resources

Efficiency

Reliability

Product 1

75

67

70

90

72

80

Product 2

80

75

85

90

92

80

Product 3

90

80

95

88

85

90

Product 4

90

95

95

85

82

95

Product 5

95

90

98

94

90

95

 

 

 

 

 

 

 

 

Green: 90% to 100% acceptable performance.

 

 

 

Yellow: 80% to 89% acceptable performance.

 

 

 

Red: Under 80% acceptable performance.

 

 

 

Next we will set up the conditional formatting:

  1. Click and drag to select the range B3:G7

Note: In our example we are using numbers in the dashboard table. We could also use formulas and functions in these cells.

  1. On the Home ribbon click on the Conditional Formatting icon [ALT  H  L]. Go down to the bottom and click on New Rule. In Excel 2013 use [ALT  H  L  N]
  1. The New Formatting Rule dialog box opens.

 

In the top half of the dialog box use the default setting:

“Format all cells range based on their values.”

 

In the bottom half of the dialog box change the “Format Style” setting to “Icon Sets”.

 

In Excel 2016 they are using a different dialog box. Watch this video

Conditional Formatting Excel 2016

 

  1. Click the pull-down menu next to “Icon Style” and select a set of icons. I’m using the default set in the first row- green, yellow, and red traffic signals on a white background.
  2. In the bottom right of the dialog box, under Type, choose number for both rules.
  3. In the bottom middle of the dialog box, under value, type in 90 for the value for the green icon and type in 80 for the value for the yellow icon.
  4. The symbols to the left of the 90 and 80 should both be set to >=.

Note that you do not have to do anything to customize the red icon. It will show up by default if the first two conditions are not met. The conditional formatting acts like a nested IF Function.

 

  1. Click Ok. The traffic signal icons should appear in the selected range of the dashboard.
  1. Try changing some of the numbers and see if the icon changes.

 

Now let’s change the rules.

  1. Mouse over and select the range of cells with numerical values - B3:G7.
  1. On the Home ribbon, click on the Conditional Formatting icon [ALT  H  L]. Go down to the bottom and click on Manage Rules. Click on Edit Rules.
  1. Change the value settings to 95 and 85.
  1. Change the icon style. Click OK. Click OK again.

 

  1. Change the wording of your worksheet Key to reflect the new set of rules.

 

If you want to only show the dashboard there are icons on the View tab to hide and unhide Headings [ALT  W  V  H] Formulas [ALT  W  V  F] and Gridlines [ALT  W  V  G] .

 

Spark Lines

 

Spark lines are used to show trends in data.

 

Parts Inventory -  Six Month Trend

 

March

April

May

June

July

August

Trend Line

Part A17

369

254

330

387

497

236

 

Part B17

299

471

247

375

302

424

 

Part B23

349

413

424

241

487

320

 

Part C12

365

388

225

469

456

496

 

Part C31

388

468

345

421

435

329

 

Total

1770

1994

1571

1893

2177

1805

 

 

 

  1. Open a new worksheet.  Copy this table and paste it into the range  A1:H8
  1. Click and drag to select the cells in the range B3:G3. 

From the Insert tab, click on the Spark lines – Line icon [ALT  N  S  L]. A dialog box opens.

Type H3 in the location range box.

Click on OK.

A spark line should appear in cell H3.

The Spark lines tab should open in the ribbon.

  1. Click on Cell H3. Use the autofill handle to copy the spark lines into the range H4:H8
  1. To remove the trend (spark) lines, click and drag over the cells in the H3:H8 range. Click on the Spark lines tab in the ribbon and select the Clear icon [ALT  J  D  CL] and press Enter.

 

Create a checkbox

 

41.   Build a similar table in Excel in the range A11 to C18.

Enter the title To-Do List in Cell A11.  

The column labels in row 13 will be Done, Status and Tasks

Make up five tasks that you need to complete and enter them into the Tasks column.

Add fill colors to the ranges A:11-C11 and A13:C13.

  1. Type the word Done into cell B14, right below the word Status.
  1. In cell A14 enter the formula =IF(B14=”Done”,1,0).
  1. Click on cell A14.

Click on the conditional cell formatting icon [ALT  H  L].

Select Manage Rules.

Select New Rules.

A dialog box wll open.

In the field “Format all cells based on their values:” select “2-color scale” and use the dropdown menu to select “icon sets.”

You will see several choices of icon sets, Click on the checkmark icons.

Put a checkmark in the “Show Icon Only” checkbox.

In the icon options in the lower left, clcik on the middle option “when < 0  and “and choose the gray circle icon.

Change both Type fields to number.

Set the first Value field, >=, to the number 1.

Click Ok.

  1. Copy the formulas in A14 and B14 into the A15:B18 range.
  1. Select the A13:C18 raneg and convert it into a table [ALT  N  T] .

Done! Test it out by typing done in some of the cells in the B14:B18 range.

 

 

Using Pictures in Comments

  1. Click on cell B10
  2. From the Review tab of the ribbon, click on the New Comment icon [ALR  R  C].
  3. Edit the comment to remove the text showing the user name. Note: Some MAC users have told me this option is not available to them.
  4. Move the cursor above the edge of the comment box and position it over the border line. When the cursor turns into a four-sided arrow, right-click and choose Format Comment.
  5. A dialog box opens showing eight tabs. Select the Colors and Lines tab.
  6. Select the box for Fill Color and then select Fill Effects.
  7. A new dialog box appears. Select the Picture tab and click on Select Picture. Some Macbook users have told me this option is not available to them. You can use another type of graphic instead.
  8. Browse and select a picture. Click Insert, then click OK, then Click OK.  All of the dialog boxes should now be closed.
  9. Resize the comment box to adjust the shape of the picture.

56.   Select the Show All Comments icon on the Review tab [ALT  R   A] to hide all comments and to display all comments.

57.   Select the (Show/Hide Comment) on the Review tab [ALT  R  H] to show or hide a single comment.

In the illustration I added a second comment box with a caption for the picture.

 

 

 

 

 

 

 

 

 

 

 

Creating a Dashboard using Slicers

Note to MAC Users:

Slicers are not included as a feature in Excel for MACs. You can either skip this section of the assignment or do it on a PC.

 

  1. Download, save and open this file: http://bus91L.altervista.org/Data/2013Data.xlsx
  2. Select the Students worksheet.
  1. Select the data for the table by clicking anywhere in the student data. From the Insert Tab click on the Pivot Table icon. [ALT N V] A dialog box opens with the student data range elected. Select the new worksheet option. Click on OK. You are now working in the pivot table worksheet.
  1. To create a pivot table you click on fields in the Pivot Table field list, shown in a task pane on the right, and drag them into the values, row, filters, and column boxes.

Drag the Major field into the ROWS box, (lower left box)

Drag the Class field into the COLUMNS box, (upper right box)

Drag the GPA field into the VALUES box. (lower right box)

Note: different versions of Excel use different labels for the four boxes at the bottom of the task pane.

  1. Right click on any of the numerical values in the pivot table.

Choose Value Field Settings.

 

Change the “Custom Name” to Average GPA.

 

Change the ”Summarize value field By”  to Average.

 

Click on the Number Format button and change the format to Number with 2 decimal places. Click OK and Click OK.

 

  1. From the Analyze tab click on the Field Headers icon [ALT  J  T   H] to turn off the column and Row headers.
  1. Now we will add slicers. Slicers work like filters to hide some of the data, but they are easier to use because you can see them as a graphic in the worksheet.

 

Click anywhere within the pivot table. Click on the Slicers icon [ALT  J  T  S  L] In Excel 2013 use  [ALT  J  T  S  F] on the PivotTable Tools Options tab of the ribbon.

 

We want to add slicers for the City, M/F, Major, and Class fields. Select the four fields and click Ok.

  1. Click and drag on the slicers to rearrange them on the worksheet as shown in the picture shown a few steps below.
  1. Right click in the M/F slicer box.

 

Choose Slicer Settings….

 

Change the Caption of the Header to Gender.

 

Click Ok

  1. Click in the City slicer box.

Select the column icon [ALT  J  O  C] in the Slicer Tools Options tab of the ribbon. Change the number of columns setting to 3. 

68.   Click in the Major slicer box.

69.   Select the column icon [ALT  J  O  C] in the Slicer Tools Options tab of the ribbon.

Change the number of columns setting to 2.

  1. Resize the Major and City slicers (click in the slicer box to see the resize handles) so all of the field values are visible and the slicers boxes are the same width.

Resize the Class, Gender and Major slicer boxes so they all have the same height.

  1. Click on the pivot table. Select a new style from the PivotTable Styles options [ALT  J  Y  S]  on the PivotTables Tools Design tab of the ribbon. In Excel 2013 this tab is labeled Slicer Tools Options.
  1. Click on a slicer. Select a new style from the Slicer Styles options [ALT  J  O  Q]  on the Slicer Tools Options tab of the ribbon. Apply the same style to all four slicer boxes
  1. Right-click on the letter A column header at the top of the worksheet and select Insert. We want a blank column A so our dashboard will have a margin with some white space to the left of the dashboard.
  1. Go to the View Tab and turn off the formula bar, headings and gridlines.

 

  1. Experiment with the slicers. Click on different field entries to see what happens. Changes in one slicer filters (reduces) the options that are available in other slicers. To select more than one option in a slicer hold down the CTRL key when making your selections. To clear a filter from a slicer click on the icon in the upper right corner of the slicer box.

 

If you would like to learn more about dashboards, I have a follow up lesson here:

http://bus91l.altervista.org/Assignments/Dashboards2.htm

 

 

 

 

 

 

 

 

 

Review – Fine Points

 

 

 

Instructions for the Check for Understanding (CFU) form

A CFU form is turned in for every assignment.

Starting with the line for your name, copy and paste the CFU form into a Word document.

Fill in your name, date, section number

Circle (or indicate by highlighting) a Y or N response for each statement. I do not subtract points for No answers.

Below the list of Y/N statements, insert ONE screenshot of any part of your work. I just need to see something to prove that you did the assignment. I don’t need to see all of the assignment.  Resize the screenshot, if necessary, so both the form and graphic can fit on one page.

A copy of the CFU is due to be turned in during the next class session.  I do not accept CFU’s through email.

 

 

Name _______________________________      Date___________       Section _______

Check for Understanding –Dashboards One

Y/N I can use conditional cell formatting to visually represent data.

Y/N I can create a dashboard using Conditional Formatting. 

Y/N I can create a checklist dashboard.

Y/N I can use spark lines to show a trend 

Y/N I can create a dashboard using Pivot Table Slicers.