In this tutorial, we will take you through the steps needed to create an Excel Dashboard. We will also review some best practices when it comes to dashboard creation and visualization.

An Excel Dashboard is a powerful visualization tool that incorporates charts and other interactive visual features, to display important business related insights.

If you would like to learn more about other advanced Excel features, then please consider joining one of our top-rated Excel courses.

 

Excel Dashboards Explained

You can create a dashboard using other popular data analysis software, such as Power BI, Tableau or Qlik. When you create a dashboard in Excel, you will leverage certain key features.

A dashboard should provide insights and assist the organization with making business decisions.

Using just PivotCharts, Slicers and graphics, you can create quite a sophisticated Excel dashboard. Alternatively, you can incorporate VBA for advanced functionality. In this tutorial, we will focus on the technique that utilises PivotCharts, a Slicer and graphics.

You may have heard the terms dashboard and report used interchangeably, so let’s review what the difference is.

Graphic detailing the difference between a dashboard and a report.

 

If you would like to learn about how to create a dashboard in Power BI, then please read this post!

 

Planning Your Excel Dashboard

The first stage involved in Dashboard creation, is the Planning stage. Follow these easy steps for planning!

Graphic showing how to plan an Excel Dashboard

 

1. Consider the purpose of your prospective dashboard. The purpose will guide you, as you are creating the actual Dashboard. Since you maybe wondering about what information to include.

For example, let’s say a sales executive is given an instruction to prepare a Sales Dashboard. So, the purpose of the dashboard is to showcase the main sales KPIs and performance metrics. He may want to add a chart to his dashboard that shows the annual profits gleaned from each market. He may also want to include a graphic that highlights the top-selling products or top customers.

 

2.  You should also consider your audience and their skill level in Excel. For example, a financial analyst working with stocks could use an Open-High-Low-Close chart to display stock information. A biostatistician could use a histogram to display the frequency of certain eye colours, within a group from a specific area.

Ideally, one of your goals should be to create a user-friendly dashboard that conveys the key information, without the user needing to tweak the dashboard in any way

 

3.  It’s advisable to design an outline of your dashboard. You could use standard software such as PowerPoint or Word in order to create this outline. This will be a rough diagram of where you’d like to place your main visualizations (PivotCharts, Slicers and graphics).

 

4.  Think about the colour scheme,  font combination and any other graphics that would be appropriate for your dashboard.

A good rule of thumb, when it comes to colour schemes is to keep it simple. Don’t use too many colours. You want your colours to emphasize your key points, not distract from them.

If you are designing a dashboard for your company, you may need to use your company’s colours.

Try not to use more than two fonts on your dashboard.

In terms of other graphics that are not charts, use these sparingly. Since you don’t want your dashboard to be too cluttered. If you are designing a dashboard for a company that sells tractors for example, you could include a very small graphic of a tractor next to the heading.

Tip: Review some dashboard templates and online tutorials to get ideas and inspiration for your dashboard.

 

The Tools and Resources You Need To Make Your Dashboard

You will of course need Excel to create your dashboard. Additionally, you can use sites such as unsplash.com or pixabay.com in order to find sheet background images.

For other graphical elements/images you can also use those sites. Office 365 users can make use of the Stock Images… option.

For colour palettes/schemes, you can look at sites such as coloors.co and view the Trending Color Palettes – Coolors page. You don’t have to use the entire palette. In fact it’s advisable to just pick a few colours from your palette of choice.

Alternatively, you can use the built-in colour palette in Office that comes with each theme.

 

Designing Your Excel Dashboard

The second stage involved in Dashboard creation, is the Design stage.

Graphic showing the Design Stage of the Dashboard Creation Process

 

      • Importing and/or Cleaning The Source Data

If your source data is already in Excel and cleaned, then you are set for the next step. If you are unsure about what data cleanup entails, then please visit this post on Removing Blank Cells.

Alternatively, your data may be stored in an external source such as a SQL Server database, in which case you will need to import your data. You can use Power Query to import and clean your data if necessary.

To learn more about how to use Excel Power Query, to automate the process of importing and cleaning data, read here!

 

      • Adding a Heading and Formatting

It’s advisable to add a heading to your Dashboard sheet. Keep the heading short but descriptive, it should convey what the dashboard will be about. You should also add some basic formatting to your heading and a background colour to the heading section, if needed.

 

      • Creating the Table, PivotTables and Charts

In most cases you need to have a primary data table. You will then create PivotTables from that primary data table. PivotCharts will be the main graphical visualizations of your dashboard, when using this technique.

You need to customize the PivotCharts according to your chosen colour scheme and font choice.

You will eventually add the PivotCharts to your Dashboard sheet and position them according to their placement on the outline.

 

If you would like to learn more about PivotTable creation, then please read our guide here.

 

      • Creating/Inserting Additional Graphics

This is an optional step and involves creating/inserting any additional graphical elements other than PivotCharts and Slicers to the dashboard.

 

      • Inserting the Slicers.

Slicers provide a way of adding interactivity to your dashboard. When the user clicks on a button on the Slicer, certain PivotCharts in the dashboard will be filtered accordingly. You will place the Slicer(s) in the position where it’s shown on the outline.

You can customize the Slicer(s) according to your needs.

 

Step-by-Step Dashboard Example

Let’s see how this works, by looking at a simple example. This is for a hypothetical start-up company that has been operating for six months.

Graphic showing the instruction given to the sales executive regarding the Dashboard.

 

 

The Planning Stage

      • So, the purpose of the dashboard is to showcase the main metrics such as the most profitable flavours, the least profitable flavours and who the best customers are. He would also like to showcase the overall sales trend for all of the flavours.

 

      • The audience and main users of the dashboard are senior management, who are interested in the performance of the different flavours of coffee sachets. They would like to use the Dashboard to assist with Business Intelligence. The Dashboard should be well designed and easy to use.

 

      • The next step in the planning process is to design a simple outline of the Dashboard, in either PowerPoint or Word. In this case, we used Word. This is just a rough diagram that we are going to use to guide us, when designing the Dashboard. We created it using standard shapes and textboxes.

Graphic showing the outline of the Dashboard.

 

      • For the colour scheme, we decided to go with some neutral greys and blues from the palette of the Office theme, for the most part. We will also use two other grey tones.

 

We have planned the Dashboard accordingly, so let’s look at the Design stage.

 

The Design Stage

Step 1

Let’s review our source data set. It’s already in an Excel workbook and has been cleaned.

Screenshot showing the source data set in Excel.

 

We have the sales data for our hypothetical company. It includes the following columns:

Graphic showing the columns in the sales data set.

 

We have 193 rows of data including the headings. There are eight different flavours of coffee sachet. The data set is stored on a sheet called Data.

Step 2

We will create a new sheet and name the sheet Dashboard.

Select range A1:U2 and go to the Home Tab. In the Font Group, select Fill Color. Choose More Colours…

We would like a neutral grey colour. Using the Colors Dialog Box, select Custom and change the red value to 231, the green value to 230 and the blue value to 230.

Screenshot showing the RGB code for the custom grey colour.

 

Go to the Insert Tab, and in the Text Group, select TextBox.

Draw a Text box on the Dashboard worksheet, over the grey background area. Type the words Sales Performance Dashboard in the Text box. Centre the Text.

With the Text box still selected, go to the Home Tab. In the Font Group, change the font to Tenorite. Increase the size to 16 and the font weight to bold. Change the font colour to Black, Text 1, Lighter 25%.

Gif showing text added to the Text Box and the formatting of the Text Box.

 

Tenorite is a new font available to Office 365 subscribers. For older versions of Office, you can use any Sans Serif font such as Calibri.

Select the  Text Box and go to the Shape Format Tab. In the Shape Styles Group, select Shape Fill and then choose No Fill.

Then select Shape Outline and choose No Outline.

Reposition the Text Box.

Gif showing how to remove the Shape Fill and the Shape Outline of the Text Box.

 

Now go to the Insert Tab and in the Illustrations Group, select Pictures. Choose the Stock Images… option.

Screenshot showing the Stock Images... option highlighted.

 

Select the Icons option and type Coffee in the Search Box. Choose the following image and click on the Insert Button.

Screenshot showing the Coffee image of choice highlighted.

 

With the image selected, go to the Graphics Format Tab. In the Graphics Styles Group choose the Colored Fill – Accent 3, No Outline Preset.

Screenshot showing the Colored Fill – Accent 3, No Outline Preset option highlighted.

 

Resize the image to 0.45″ by 0.45″ and position it as shown below. Use the arrow keys on your keyboard, for more precise positioning.

Screenshot showing the size of the image highlighted.

 

Step 3

We will go back to our sheet called Data. Since we want to create a Table that will update automatically, when we add new rows. With one cell in the range selected, press CTRL-T on your keyboard to create a Table.

The Create Table Dialog Box should appear.

Screenshot showing the Create Table Dialog Box.

 

Click Ok.

Screenshot showing the created Table.

 

With one cell in the Table selected, go to the Table Design Tab. In the Properties Group change the name of the Table to SourceDataT.

Screenshot showing the Table Name in the Properties Group, highlighted.

 

Select any cell in the Table and go to the Insert Tab. In the Tables Group, select the PivotTable option. Using the PivotTable from table or range Dialog Box, ensure New Worksheet is checked. Click Ok.

Gif showing how to create a PivotTable from a Table.

 

You should see the following.

Screenshot showing the PivotTable created on a new sheet.

 

We need five PivotTables in total. Three for PivotChart creation and two for the graphical elements creation.

So make four copies of Sheet3, and name:

        • Sheet3 – PT1
        • The first copied sheet – PT2
        • The second copied sheet – PT3
        • The third copied sheet – GE1
        • The fourth copied sheet – GE2

Screenshot showing the copies of the original PivotTable sheet.

 

Step 4

Now, we will add fields to our first PivotTable. We will then create a PivotChart by using this PivotTable.

So go to the sheet called PT1.  We want to add the Coffee Flavour field to the Row Labels section and the Profit field to the Values section.

Using the PivotTable Fields Pane, right-click the Coffee Flavour field and choose the Add to Row Labels option. Right-click the Profit field and choose Add to Values.

Gif showing the Coffee Flavour added to the Rows Label, and the Profit Field to the Values section.

 

We want the flavours ordered, in terms of profit from highest to lowest generating. So right-click one of the cells in the Sum of Profit column in the PivotTable and choose Sort, Largest to Smallest.

Gif showing the Sum of Profit Column sorted from Largest to Smallest.

 

We want to show the numbers in the Sum of Profit column, in the PivotTable as currency values. So, select any cell in this column and right-click and choose Value Field Settings

Screenshot showing the Value Field Settings...

 

Using the Value Field Settings Dialog Box, select the Number Format option.

Screenshot showing the Number Format option highlighted.

 

Using the Format Cells Dialog Box, select Currency. Set the decimal places to 0 and select the appropriate currency.

Screenshot showing the Format Cells Dialog Box.

 

Click Ok and then Ok again.

Screenshot showing the numbers formatted as currency values with no decimal places.

 

We can now insert the first PivotChart. We do this by selecting a cell in the PivotTable and then going to the PivotTable Analyze Tab. In the Tools Group, select the PivotChart option.

Using the Insert Chart Dialog Box, we select Pie. Then select the pie of pie option. Click Ok.

Gif showing how to insert a PivotChart.

 

A Pie chart is useful when you want to show the contribution of each segment (slice) to the whole (pie).  If you have many slices, then a pie of pie chart is the most suitable type of option to use.

There are extra things on our PivotChart that are not needed, for our purposes. So, the first thing we would like to do, is hide all the field buttons on the chart and delete the legend.

To do this, right-click the Sum of Profit button on the chart and select the Hide All Field Buttons on Chart option. Then select the legend and press the Delete key on your keyboard.

Gif showing how to hide all the field buttons on the chart and remove the legend.

 

Select the current title Total on the chart. Change the text to Percentage Each Flavour Contributes to Profit.

Gif showing the new chart title.

 

With the title selected, go to the Home Tab. In the Font Group, change the font to Tenorite, the font size to 12, the weight to bold and the colour to Black, Text 1, Lighter 35%.

Gif showing how to format the Chart title.

 

Select the chart and go to the Format Tab. In the Size Group, change the dimensions of the chart to 4.01” by 5.34”.

Screenshot showing the new dimensions of the chart.

 

Now, increase the size of the plot area of the chart. Select the plot area and make it bigger as shown below.

Gif showing how to increase the size of the plot area.

 

There are too many colours, on our chart that don’t fit into the desired colour scheme. To sort this out, we first have to select the chart.

With the chart selected, go to the Design Tab. In the Chart Styles Group, click on the Change Colors button. In the Monochromatic section, choose the Monochromatic Palette 1 option.

Screenshot showing the Monochromatic Palette 1 option highlighted.

 

We now want to add and format the Data Labels. Select the chart and click on the Plus sign. Expand the Data Labels option and click on More Options…

Screenshot showing More options... highlighted.

 

Using the Format Data Labels Pane, check Category Name and Percentage and uncheck Value.

Screenshot showing the Format Data Labels Pane.

 

Select all of the Data Labels, by clicking on one of them. With all of the Data Labels selected, go to the Home Tab and in the Font Group, change the font to Tenorite.

Click off the chart to deselect everything.

We want to change the font colour, of some of our Data Labels in the darker slices, to white.

Click on one Data Label to select all the Data Labels, then click on the Chocolate Data Label again to select it. So, we selected only Chocolate.

With only the Chocolate Data Label selected, go to the Home Tab. In the Font Group change the font colour to white and move the label to the position shown below.

Gif showing how to change the font colour of one Data Label.

 

Change the font colour of all the other Data Labels in dark slices, in the same way. Then reposition the Data labels according to your preference.

Screenshot showing the Data Labels repositioned.

 

We have to change the background of the entire chart. To do this select the chart, right-click and choose Format Chart Area…

Screenshot showing the Format Chart Area...option highlighted.

 

Using the Format Chart Area Pane, go to the Fill & Line section. Expand the Fill option and choose Gradient fill. Select the Top Spotlight – Accent 3 option.

Screenshot showing the Top Spotlight – Accent 3 option highlighted.

 

Step 5

We will now create the next PivotTable we need. Go to the spreadsheet called PT2 and add the Date Ordered field to the Row Labels section.

Screenshot showing the Date Ordered field, added to the Row Labels section of the PivotTable.

 

Click on the Date Ordered field, in the Row Labels section and select Remove Field. Since we only want to show Months.

Screenshot showing the Remove Field option highlighted.

 

You should see the following.

Screenshot showing the Months field in the Row Labels section, highlighted.

 

Add the Profit field to the Values section. Format the Sum of Profit Column to show currency with no decimal places.

Screenshot showing the Rows, Values and Sum of Profit Column highlighted.

 

With one cell in the PivotTable selected, go to the PivotTableAnalyze Tab. In the PivotTable Group rename the PivotTable to PivotTable2.

Screenshot showing the new name of the PivotTable.

 

Go to the PivotTable Analyze Tab and in the Tools Group, select PivotChart. The Insert Chart Dialog Box should appear. Select Line and choose the first option. Click Ok.

Screenshot showing the Line Chart option highlighted.

 

You should see the following.

Screenshot showing the Line Chart.

Line charts are useful, when you need to show a trend over time. They are very simple and easy to understand.

Hide all the Field buttons on the chart. Delete the y-axis. You can delete the vertical axis by selecting it and then pressing the Delete key on the keyboard. In addition, delete the legend and the gridlines on the chart.

Gif showing how to format the Line Chart.

 

Select the markers, by clicking on one of the points where the lines meet.

Screenshot showing the markers selected.

 

Right-click and choose Format Data Series.

Screenshot showing the Format Data Series... option highlighted.

 

Using the Format Data Series Pane, select the Fill & Line option. Change the Line Colour to one of the grey colours. In this case we used Light Gray, Background 2, Darker 50%.

Gif showing how to change the line colour of the Line Chart.

 

Using the Format Data Series Pane, select the Marker option. Expand the Marker Options section.  Choose the Built-in option. Select the circle type and set the size to 7.

Gif showing how to format the markers on the Line Chart.

 

Select the horizontal axis by clicking on it. Change the font to Tenorite, the font size to 10, the font weight to bold. Change the font colour to Black, Text 1, Lighter 25%.

Screenshot showing the Font Settings of interest, highlighted.

 

With the horizontal axis selected, go to the Format Tab.  In the Shape Styles Group, select the Shape Outline option.

Change the Outline colour to Black, Text 1, Lighter 25%. Set the Weight of the Outline to 1 ½ points. 

Gif showing additional formatting.

 

Change the title of the chart to Trend showing Profit by Month. Set the font to Tenorite, the font size to 12, the font weight to bold and the font colour to Black, Text 1, Lighter 35%.

Screenshot showing the Font Settings of interest, highlighted.

 

Select the chart. Right-click and choose Format Chart Area… The Format Chart Area Pane should appear. Expand the Fill options and choose Gradient fill. Choose the Top Spotlight – Accent 3 gradient fill.

Screenshot showing the Top Spotlight - Accent 3 option highlighted.

 

Select the chart and go to the Format Tab. In the Size Group set the dimensions of the chart to 1.68” by 5.74”.

Screenshot showing the chart with the new dimensions.

 

Step 6

We will now create the next PivotTable that we need. Go to the spreadsheet called PT3.

Note: If your Field list is not showing for whatever reason, then click on one cell in the PivotTable. Go to the PivotTable Analyze Tab and in the Show Group, click on the Field List Button.

Rename the PivotTable to PivotTable3. Add Company Name to the Row Labels section and Profit to the Values Section. Format the Sum of Profit column to show currency with no decimal places.

Screenshot showing the PivotTable with the fields added and the Sum of Profit column formatted.

 

With one cell in the PivotTable selected, go to the PivotTable Analyze Tab. In the Tools Group, select PivotChart.

Choose Column and then select the Clustered Column option.

Screenshot showing the Clustered Column option, highlighted.

 

Click Ok.

You should see the following.

Screenshot showing the PivotChart.

 

A Column chart is useful when you want to display data for each category in columns. This assists with comparative analysis.

Hide all the Field Buttons on the chart, delete the legend and the gridlines.

Screenshot showing the result of hiding the field buttons, deleting the legend and the gridlines on the chart.

 

Change the title of the chart to Profit Breakdown by Customer. Set the font to Tenorite, the font size to 12, the font weight to bold and the font colour to Black, Text 1, Lighter 35%.

Screenshot showing the Font Settings, highlighted.

 

Change the background of the entire chart, to the gradient fill – Top Spotlight – Accent 3. Set the size of the chart to 2.29” by 5.74”.

Screenshot showing the formatted chart.

 

Select the data series, by clicking once on one of the blue columns. With the Data Series selected, go to the Format Tab. In the Shape Styles Group, change the Shape Fill to White, Background 1, Darker 15%.

Gif showing the Shape Fill Colour being set.

 

With the Data Series still selected, go to the Format Tab. In the Shape Styles Group, select Shape Effects. Under Preset select Preset 8.

Gif showing how to format the Data Series on a Column Chart.

 

The result is the following.

Screenshot showing the result of formatting the Data Series on the Column Chart.

 

Step 7

Let’s get started with creating part, of our first graphical element. Our first graphical element is going to showcase the top three most profitable flavours. Go to the sheet called GE1 and rename the PivotTable to PivotTable4.

Create a PivotTable with Coffee Flavour in the Row Labels section and Profit in the Values section. We then need to sort our Sum of Profit column, so that is it sorted from largest to smallest.

The result is the following.

Screenshot showing the PivotTable with the desired sort added.

 

We want to get the top three flavours in terms of profit, from the PivotTable.  Since we sorted the Sum of Profit Column from largest to smallest this is easy to do. Since these are the first three rows.

In cell E4 enter = UPPER(A4), in cell E5 enter = UPPER(A5) and in cell E6 enter = UPPER(A6).

Gif showing the formulas being entered into the cells.

 

In this way these cells will always return the top three flavours, in terms of profit.

So let’s look at creating the first part, of our second graphical element. Go to the sheet called GE2. We want our bottom three flavours in terms of profit generation, on this sheet.

Create a PivotTable with Coffee Flavour in the Row Labels Section and Profit in the Values section. This time sort from smallest to largest. Rename the PivotTable to PivotTable5.

In cell E4 enter = UPPER(A4), in cell E5 enter = UPPER(A5) and in cell E6 enter = UPPER(A6).

The result is the following.

Screenshot showing the result of the formulas.

 

Step 8

We are now going to start putting everything together. Return to the Dashboard sheet to complete the dashboard.

The first thing, we will do is select the range A3:U40 and fill it with this grey colour RGB(221,221,221).

Screenshot showing the RGB code for the grey colour.

 

We want to create a decorative line underneath the text Sales Performance Dashboard. Go to the Insert Tab and in the Illustrations Group, choose Shapes. Select the standard Rectangle.

Draw the following rectangle underneath the Sales Performance text, the size should be 0.05” by 3.45” as shown below.

Screenshot showing the size of the rectangle highlighted.

 

Note: You can zoom in when working with shapes and other graphics in order to see more clearly. The Zoom level is set to 120% for this step.

With the rectangle still selected, go to the Shape Format Tab. In the Shape Styles Group select Shape Effects. Choose Preset and then choose Preset 8.

Gif showing how to format the decorative line.

 

Let’s complete our first graphical element, which will showcase our top three most profitable coffee flavours on the Dashboard sheet. So, go to the Dashboard Sheet.

Go to the Insert Tab and in the Illustrations Group, select Shapes. This time choose the Rectangle with Rounded Corners.

Screenshot showing the Rounded Rectangle option highlighted.

 

You can set your Zoom level back to 100%.

Draw a rectangle on the sheet of size 0.34” by 2.1”.

Screenshot showing the dimensions of the Rounded Rectangle highlighted.

 

Select the rounded rectangle that we just created and go to the Shape Format Tab. In the Shape Styles Group, select the Subtle Effect – Blue, Accent 5 Theme Style.

Screenshot showing the Subtle Effect - Blue, Accent 5 Theme Style.

 

With the rectangle still selected, go to the Home Tab. In the Font Group, change the font to Tenorite. Set the font size to 9.5, the font weight to bold, and the font colour to Black, Text 1, Lighter 15%. Top Align and Center Align.

Enter the text, Top 3 Profitable Flavours.

Gif showing how to format and enter text in the Rounded Rectangle.

 

Create another rounded corner rectangle of dimensions 1.15” by 2.1”. With this rectangle selected, go to the Shape Format Tab. In the Shape Styles Group, select the Subtle Effect – Blue, Accent 5 Theme Style.

Screenshot showing the second larger rounded corner rectangle.

 

We want to align the left edges of both rectangles. So, we do this by selecting both. Click on the larger rectangle and then while holding the CTRL key, click on the smaller rectangle.

With both rectangles selected, go to the Shape Format Tab. In the Arrange Group, choose Align and then the Align Left option.

Gif showing how to align the left edges of the two rounded corner rectangle.

Now we want to insert a Text box on the second larger rounded corner rectangle. The size of the Text box should be 0.28” by 1.54”.

Screenshot showing the dimensions of the Text box highlighted.

 

Select the Text box if you haven’t already.  In the Formula Bar type an = sign. Then go to the GE1 spreadsheet and select cell E4. Press Enter.

This means that the value in the Text box, will always display the value in cell E4 on sheet GE1.

Gif showing how to link the Text box to the value in cell E4 on the other sheet.

 

With the Text box still selected, change the font to Tenorite, the size to 9, the font weight to bold. Change the font colour to Black, Text 1, Lighter 15%. Centre align the text.

Gif showing how to format the Text box.

 

Insert another Text box of size 0.28” by 1.54 and place it below the first Text box.

Screenshot showing the second Text box.

 

Select the second Text box and type an = sign in the Formula Bar. Then go to the GE1 sheet and select cell E5.

Press Enter. This means that the value in the second Text box will always display the value in cell E5 on sheet GE1.

With the second Text box still selected change the font to Tenorite, the size to 9, the font weight to bold. Change the font colour to Black, Text 1, Lighter 15%. Centre align the text.

Insert another textbox of size 0.28” by 1.54 and place it below the second textbox.

Screenshot showing the placement of the third textbox.

 

Select the third Text box and type an = sign in the Formula Bar. Then go to the GE1 sheet and select cell E6.

Then press Enter. This means that the value in the third Text box will always display the value in cell E6 on sheet GE1.

With the third Text box still selected change the font to Tenorite, the size to 9, the font weight to bold. Change the font colour to Black, Text 1, Lighter 15%. Centre align the text.

You should see the following.

Screenshot showing the three Text boxes.

 

Select all three Text boxes. You do this by selecting the first Text box, then while holding the SHIFT key, click on the second Text Box, then while still holding the SHIFT key, click on the third Text Box.

With all three Text boxes selected, go to the Shape Format Tab.  In the Shape Styles Group, in the Shape Fill option, choose No Fill. Then remove the outline.

Gif showing how to remove the Fill and the Outline from the three Textboxes.

 

We want to add a graphic that will emphasize the three most profitable flavours. Go to the Insert Tab and in the Illustrations Group, select the Pictures option. Choose Stock Images…

Select the Icons option and type Champion in the Search Box. Select the following image and click to Insert.

Screenshot showing the selected image highlighted.

 

Resize the image by dragging the handles to make it smaller and place it in the position shown.

Screenshot showing the Champion image.

 

We want to insert another image now. Go to the Insert Tab and in the Illustrations Group, select Pictures. Choose the Stock Images…option.  Select Icons. Type Coffee in the Search Box and select this image.

Click to Insert.

Screenshot showing the selected image, highlighted.

 

Resize the image to 0.7” by 0.7” and position it as shown below.

Screenshot showing the resized and repositioned image.

 

With the image still selected, go to the Graphics Format Tab. In the Graphics Styles Group, choose the Colored Fill – Accent 3, Dark 1 Outline preset.

Screenshot showing the Colored Fill – Accent 3, Dark 1 Outline preset, highlighted.

 

We now want to complete our second graphical element. Create a rounded corner rectangle of dimensions 0.34” by 2.1.

Screenshot showing the third rectangle.

 

Now select the Top 3 Profitable Flavours rectangle and go to the Home Tab. In the Clipboard Group, select Format Painter and now click on the third rounded rectangle. This will change the format of the third rectangle to look like the first.

Gif showing how to use Format Painter.

 

Now change the text in the third rounded corner rectangle to read Least 3 Profitable Flavours. Draw another rounded rectangle underneath the third one of dimensions 1.15” by 2.1”.

With this rectangle selected, go to the Shape Format Tab. In the Shape Styles Group, select the Subtle Effect – Blue, Accent 5 Theme Style.

Screenshot showing the two new added rounded corner rectangles.

 

Create three Text boxes on the fourth rounded corner rectangle. Ensure that the dimensions of all the Text boxes are 0.28” by 1.54.

        • Select the first Text box if you haven’t already.  In the Formula Bar type an = sign. Then go to the GE2 spreadsheet and select cell E4. Press Enter. This means that the value in the first Text box will always display the value in cell E4 on sheet GE2.
        • Select the second Text box.  In the Formula Bar type an = sign. Then go to the GE2 spreadsheet and select cell E5. Press Enter. This means that the value in the second Text box will always display the value in cell E5 on sheet GE2.
        • Select the third Text box.  In the Formula Bar type an = sign. Then go to the GE2 spreadsheet and select cell E6. Press Enter. This means that the value in the third Text box will always display the value in cell E6 on sheet GE2.

Ensure that the formatting of these Text boxes, is the same as the first three. You should see the following.

Screenshot showing the three Text boxes.

 

Go to the Insert Tab and in the Illustrations Group, select the Pictures option. Choose Stock Images…

Select the Icons option and type Exclamation in the Search Box. Select the following image and click to Insert.

Screenshot showing the selected image, highlighted.

 

Resize the image and position it as shown below.

Screenshot showing the second graphical element.

 

Step 9

We will now add the needed charts to our Dashboard sheet. So go to sheet PT1 and cut and paste our pie of pie Pivot Chart to the Dashboard sheet. You can use the CTRL-X shortcut.

Use the Align options if you need to, in order to align the top of the chart to the top of the first rounded corner rectangle.

Screenshot showing the pie of pie chart pasted on the Dashboard sheet.

 

Go to sheet PT2 and do likewise for the Line chart. Position the PivotChart on the Dashboard sheet as shown below.

Screenshot showing the position of the second Pivot chart.

 

Go to sheet PT3 and do likewise for the Column chart. Position the PivotChart on the Dashboard sheet as shown below.

Screenshot showing the position of the third Pivot chart.

 

Step 10

Now we will look at adding interactivity to our dashboard.

The first thing we have to do, however is add another rounded corner rectangle of size 0.42” by 2.1”. We will place it below the second graphical element.

Don’t worry about scrolling down – we will sort this out later, so that the end user won’t have to scroll down.

Screenshot showing another rounded corned rectangle.

 

Select the rectangle. With the rectangle selected,  go to the Shape Format Tab. In the Shape Styles Group select the Subtle Effect – Gray, Accent 3 Theme Style.

Screenshot showing the Subtle Effect – Gray, Accent 3 Theme Style, highlighted.

 

Enter the text Select Customer:. Change the font to Tenorite and the font weight to bold. Ensure that the text is middle and centre aligned.

Screenshot showing the grey rounded rectangle.

 

Now select the Line PivotChart. Go to the Insert Tab and in the Filters Group, select Slicer.

Screenshot showing the slicer option highlighted.

 

Using the Insert Slicers Window, select Company Name and then click Ok.

Screenshot showing the Insert Slicers Window.

 

You should see the following.

Screenshot showing the slicer on the worksheet.

 

Let’s look at formatting the Slicer.

Right-click the Slicer and select Slicer Settings.

Screenshot showing the Slicer Settings.. . option highlighted.

 

Uncheck Display header. Click Ok.

Screenshot showing the Display header option unchecked.

 

Select the Slicer. With the Slicer selected, go to the Slicer Tab. In the Buttons Group, change the number of columns to 6, the height to 0.2″ and the width to 1.8”.

While still in the Slicer Tab, in the Size Group change the Height to 0.66″ and the Width to 11.17″.

Screenshot showing the Slicer formatting.

 

In the Slicer Tab, in the Slicer Styles Group, click on the White, Slicer Style 3 Light 3 option.

Screenshot showing the Slicer Style of interest, selected.

 

Move the Slicer into the position shown below. Align the bottoms of the Slicer and the rounded rectangle, if need be.

Screenshot showing the rounded rectangle and the Slicer aligned by the bottoms.

 

We now want to link the Column Chart to the Slicer as well. If we click on a button(s) on the Slicer currently only the Line chart will be filtered.

So select the Slicer and go to the Slicer Tab. In the Slicer Group, click on Report Connections.

Screenshot showing the Report Connections option highlighted.

 

Check PivotTable2 and PivotTable3 only. Click Ok.

Screenshot showing the PivotTables that the slicer is connected to.

 

 

Hide all the other sheets, except the Dashboard sheet.

On the Dashboard sheet, go to the View Tab. In the Show Group, uncheck Gridlines, Headings and Formula Bar.

Screenshot showing the entire Dashboard Sheet.

 

Now let’s see what happens, if we click a button on the Slicer. Let’s say the Company ABC button.  We will see the sales trend and the total profit generated from Company ABC.

Gif showing how the two charts are filtered when a button on the Slicer is clicked.

 

Let’s say we want to see the combination of Company GHI and YZZ. We click on Company GHI, then holding down the CTRL key, we click on the other button to see the Line chart and the Column chart filtered accordingly.

Gif showing what happens if button GHI and YZZ on the Slicer are selected.

 

To return our charts back to their unfiltered state. Select the Slicer and then press ALT-C on the keyboard.

 

Key insights

Graphic showing the key insights and Business Intelligence gleaned from the Dashboard.

 

To learn more about Data Analysis in Excel, please read here!

Tip: You can add additional functionality to your Dashboard by using Form Controls such as scroll bars.

Graphic detailing the benefits of joining the Acuity on-site courses.

 

 

 Learning Objectives

You now know how to:

  • Plan your Excel Dashboard
  • Design your Excel Dashboard
  • Create PivotTables for your Dashboard
  • Create and Customize PivotCharts extensively for your Dashboard
  • Utilize Stock Images to enhance your Design
  • Add interactivity by using Slicers

Additionally, you have an understanding of:

  • How to use Your Dashboad to assist with Business Intelligence

 

Conclusion

A dashboard is useful when you need to present KPIs in a visual format. Excel allows you to create professional dashboards that can be easily customized to your needs.

Looking for more Excel tips? Check out the UNIQUE Function in Excel article here!