For those who are new to dashboards, it would be ideal to get an understanding of the dashboards first. In this chapter, you will get to know the definition of dashboard, how it got its name, how they became popular in IT, key metrics, benefits of dashboards, types of dashboards, dashboard data and formats and live data on dashboards.
In information technology, a dashboard is an easy to read, often single page, real-time user interface, showing a graphical presentation of the current status (snapshot) and historical trends of an organization’s or department’s key performance indicators to enable instantaneous and informed decisions to be made at a glance.
Dashboards take their name from automobile dashboards. Under the hood of your vehicle, there may be hundreds of processes that impact the performance of your vehicle. Your dashboard summarizes these events using visualizations so that you have the peace of mind to concentrate on safely operating your vehicle. In a similar way, business dashboards are used to view and/or monitor the organization’s performance with ease.
The idea of digital dashboards emerged from the study of decision support systems in the 1970s. Business dashboards were first developed in the 1980s, but due to the problems with data refreshing and handling, they were put on the shelf. In the 1990s, the information age quickened pace and data warehousing, and online analytical processing (OLAP) allowed dashboards to function adequately. However, the use of dashboards did not become popular until the rise of key performance indicators (KPIs), and the introduction of Robert S. Kaplan and David P. Norton's Balanced Scorecard. Today, the use of dashboards forms an important part of decision making.
In today’s business environment, the tendency is towards Big Data. Managing and extracting real value from all that data is the key for modern business success. A welldesigned dashboard is a remarkable information management tool.
Stephen Few has defined a dashboard as “a visual display of the most important information needed to achieve one or more objectives which fits entirely on a single computer screen so it can be monitored at a glance”.
In the present terms, a dashboard can be defined as a data visualization tool that displays the current status of metrics and key performance indicators (KPIs) simplifying complex data sets to provide users with at a glance awareness of current performance.
Dashboards consolidate and arrange numbers and metrics on a single screen. They can be tailored for a specific role and display metrics of a department or an organization on the whole.
Dashboards can be static for a one-time view, or dynamic showing the consolidated results of the data changes behind the screen. They can also be made interactive to display the various segments of large data on a single screen.
The core of the dashboard lies in the key metrics required for monitoring. Thus, based on whether the dashboard is for an organization on the whole or for a department such as sales, finance, human resources, production, etc. the key metrics that are required for display vary.
Further, the key metrics for a dashboard also depend on the role of the recipients (audience). For example, Executive (CEO, CIO, etc.), Operations Manager, Sales Head, Sales Manager, etc. This is due to the fact that the primary goal of a dashboard in to enable data visualization for decision making.
The success of a dashboard often depends on the metrics that were chosen for monitoring. For example, Key Performance Indicators, Balanced Scorecards and Sales Performance Figures could be the content appropriate in business dashboards.
Dashboards allow managers to monitor the contribution of the various departments in the organization. To monitor the organization’s overall performance, dashboards allow you to capture and report specific data points from each of the departments in the organization, providing a snapshot of current performance and a comparison with earlier performance.
Benefits of dashboards include the following −
Visual presentation of performance measures.
Ability to identify and correct negative trends.
Measurement of efficiencies/inefficiencies.
Ability to generate detailed reports showing new trends.
Ability to make more informed decisions based on collected data.
Alignment of strategies and organizational goals.
Instant visibility of all systems in total.
Quick identification of data outliers and correlations.
Time saving with the comprehensive data visualization as compared to running multiple reports.
Dashboards can be categorized based on their utility as follows −
Strategic dashboards support managers at any level in an organization for decision making. They provide the snapshot of data, displaying the health and opportunities of the business, focusing on the high level measures of performance and forecasts.
Strategic dashboards require to have periodic and static snapshots of data (e.g. daily, weekly, monthly, quarterly and annually). They need not be constantly changing from one moment to the next and require an update at the specified intervals of time.
They portray only the high level data not necessarily giving the details.
They can be interactive to facilitate comparisons and different views in case of large data sets at the click of a button. But, it is not necessary to provide more interactive features in these dashboards.
The following screenshot shows an example of an executive dashboard, displaying goals and progress.
Analytical dashboards include more context, comparisons, and history. They focus on the various facets of data required for analysis.
Analytical dashboards typically support interactions with the data, such as drilling down into the underlying details and hence should be interactive.
Examples of analytical dashboards include Finance Management dashboard and Sales Management dashboard.
Operational dashboards are for constant monitoring of operations. They are often designed differently from strategic or analytical dashboards and focus on monitoring of activities and events that are constantly changing and might require attention and response at a moment's notice. Thus, operational dashboards require live and up to date data available at all times and hence should be dynamic.
An example of an operation dashboard could be a support-system dashboard, displaying live data on service tickets that require an immediate action from the supervisor on high-priority tickets.
Informational dashboards are just for displaying figures, facts and/or statistics. They can be either static or dynamic with live data but not interactive. For example, flights arrival/departure information dashboard in an airport.
The data required for a dashboard depends on its category. The premise for the data is that it should be relevant, error-free, up to date and live if required. The data can possibly be from various and different sources and formats (Spreadsheets, Text Files, Web Pages, Organizational Database, etc.).
The results displayed on a dashboard must be authentic, correct and apt. This is crucial since the information on a dashboard would lead to decisions, actions and/or inferences. Thus, along with the data being displayed, the medium chosen for the display is equally important as it should not give an erroneous impression in the data portrayal. The focus should be on the ability of the data visualization that would unambiguously project the conclusions.
As discussed earlier in this chapter, data warehousing and online analytical processing (OLAP) is making it possible to refresh the dynamic dashboards instantly with live data. It is also making those who design the dashboards be independent of the organization’s IT department for obtaining data.
Thus, the dashboards have become the most sought after medium from top management to a regular user.
You can create a dashboard in Excel using various features that help you make data visualization prominent, which is the main characteristic of any dashboard. You can show data in tables with conditional formatting to highlight the good and bad results, you can summarize the data in charts and PivotTables, you can add interactive controls, and you can define and manage KPIs and so on.
In this chapter, you will get to know the most important Excel features that come handy when you are creating a dashboard. These features help you arrive at the dashboard elements that simplify complex data and provide visual impact on the current status or performance in real time.
The most important component of any dashboard is its data. The data can be from a single source or multiple sources. The data might be limited or might span several rows.
Excel tables are well suited to get the data into the workbook, in which you want to create the dashboard. There are several ways to import data into Excel, by establishing connections to various sources. This makes it possible to refresh the data in your workbook whenever the source data gets updated.
You can name the Excel tables and use those names for referring your data in the dashboard. This would be easier than referring the range of data with cell references. These Excel tables are your working tables that contain the raw data.
You can arrive at a summary of the analysis of data and portray the same in an Excel table that can be included as a part of a dashboard.
You can use Sparklines in your Excel tables to show trends over a period of time. Sparklines are mini charts that you can place in single cells. You can use line charts, column charts or win-loss charts to depict the trends based on your data.
Conditional formatting is a big asset to highlight data in the tables. You can define the rules by which you can vary color scales, data bars and/or icon sets. You can either use the Excel defined rules or create your own rules, based on the applicability to your data.
You will learn these conditional formatting techniques in the chapter ─ Conditional Formatting for Data Visualization.
Excel charts are the most widely used data visualization components for dashboards. You can get the audience view the data patterns, comparisons and trends in data sets of any size strikingly adding color and styles.
Excel has several built-in chart types such as line, bar, column, scatter, bubble, pie, doughnut, area, stock, surface and radar if you have Excel 2013.
You will understand how to use these charts and the chart elements effectively in your dashboard in the chapter − Excel Charts for Dashboards.
In addition to the above-mentioned chart types, there are other widely used chart types that come handy in representing certain data types. These are Waterfall Chart, Band Chart, Gantt chart, Thermometer Chart, Histogram, Pareto Chart, Funnel Chart, Box and Whisker Chart and Waffle Chart.
You will learn about these charts in the chapter − Advanced Excel Charts for Dashboards.
Once you create charts, you need to place them in your dashboard. If you want to make your dashboard dynamic, with the data getting refreshed each time the source data changes, which is the case with most of the dashboards, you would like to provide an interface between the charts in your dashboard and the data at the backend. You can achieve this with the Camera feature of Excel.
When you have large data sets and you would like to summarize the results dynamically showing various facets of the analysis results, Excel PivotTables come handy to include in your dashboard. You can use either the Excel tables or the more powerful data tables in the data model to create PivotTables.
The main differences between the two approaches are −
| Excel Tables | Data Tables |
|---|---|
| Data from only one table can be used to create PivotTable. | Data from more than one table can be used to create PivotTable, defining relationships between the tables. |
| When the tables increase in the no. of rows, the memory handling and storage will not be optimistic. | Can handle huge data sets with thousands of rows of data with memory optimization and decreased file size. |
If you try to create a PivotTable with more than one Excel table, you will be prompted to create relationship and the tables with the relationship get added to the data model.
You will learn about PivotTables in the chapter − Excel PivotTables for Dashboards.
If you have data in the Data Model of your workbook, you can create Power PivotTables and Power PivotCharts that span data across multiple data tables.
You will learn about these in the chapter − Excel Power PivotTables and Power PivotCharts for Dashboards.
You can make your dashboard elements interactive with easy to use controls such as scrollbars, radio buttons, checkboxes and dynamic labels. You will learn more about these in the chapter − Interactive Controls in Excel Dashboards.
Excel Power PivotTables and Power PivotCharts are helpful to summarize data from multiple resources, by building a memory optimized Data Model in the workbook. The Data Tables in the Data Model can run through several thousands of dynamic data enabling summarization with less effort and time.
You will learn about the usage of Power PivotTables and Power PivotCharts in dashboards in the chapter - Excel Power PivotTables and Power PivotCharts for Dashboards.
Excel Power View Reports provide interactive data visualization of large data sets bringing out the power of Data Model and interactive nature of dynamic Power View visualizations.
You will learn about how to use Power View as dashboard canvas in the chapter - Excel Power View Reports for Dashboards.
Key Performance Indicators (KPIs) are integral part of many dashboards. You can create and manage KPIs in Excel. You will learn about KPIs in the chapter − Key Performance Indicators in Excel Dashboards.
If you have chosen Excel for creating dashboard, try to use Excel tables if they serve the purpose. With Conditional Formatting and Sparklines, Excel Tables are the best and simple choice for your dashboard.
In Excel, you can use conditional formatting for data visualization. For example, in a table containing the sales figures for the past quarter region-wise, you can highlight the top 5% values.
You can specify any number of formatting conditions by specifying Rules. You can pick up the Excel built-in Rules that match your conditions from Highlight Cells Rules or Top / Bottom Rules. You can also define your own Rules.
You choose the formatting options that are appropriate for your data visualization - Data Bars, Color Scales, or Icon Sets.
In this chapter, you will learn conditional formatting Rules, formatting options, and adding/managing Rules.
You can use Highlight Cells Rules to assign a format to the cells that contain the data meeting any of the following criteria −
Numbers within a given numerical range: Greater Than, Less Than, Between, and Equal To.
Values that are Duplicate or Unique.
Consider the following summary of results that you want to present −
Suppose you want to highlight the Total Amount values that are more than 1000000.
Greater Than dialog box appears.
In the Format cells that are GREATER THAN: box, specify the condition as 1000000.
In the box with, select the formatting option as Green Fill with Dark Green Text.
As you can observe, the values satisfying the specified condition are highlighted with the specified format.
You can use Top / Bottom Rules to assign a format to the values meeting any of the following criteria −
Top 10 Items − Cells that rank in the top N, where 1 <= N <= 1000.
Top 10% − Cells that rank in the top n%, where 1 <= n <= 100.
Bottom 10 Items − Cells that rank in the bottom N, where 1 <= N <= 1000.
Bottom 10% − Cells that rank in the bottom n%, where 1 <= n <= 100.
Above Average − Cells that are Above Average for the selected range.
Below Average − Cells that are Below Average for the selected range.
Suppose you want to highlight the Total Amount values that are in top 5%.
Top Ten% dialog box appears.
In the Format cells that rank in the TOP: box, specify the condition as 5%.
In the box with, select the formatting option as Green Fill with Dark Green Text.
Click the OK button. The top 5% values will be highlighted with the specified format.
You can use colored Data Bars to see the value relative to the other values. The length of the Data Bar represents the value. A longer Bar represents a higher value, and a shorter Bar represents a lower value. You can either use solid colors or gradient colors for Data Bars.
Select the column – Total Amount.
Click on Conditional Formatting in the Styles group under Home tab.
Click on Data Bars in the dropdown list.
Click on Blue Data Bar under Gradient Fill in the second dropdown list that appears.
The values in the column will be highlighted showing small, intermediate and large values with blue colored gradient fill bars.
Select the column – Total Amount.
Click on Conditional Formatting in the Styles group under Home tab.
Click on Data Bars in the dropdown list.
Click on Orange Data Bar under Solid Fill in the second dropdown list that appears.
The values in the column will be highlighted showing small, intermediate and large values by bar height with orange colored bars.
Suppose you want to highlight the sales as compared to a sales target, say 800000.
Create a column with values = [@[Total Amount]]-800000.
Select the new column.
Click on Conditional Formatting in the Styles group under Home tab.
Click on Data Bars in the dropdown list.
Click on Green Data Bar under Gradient Fill in the second dropdown list that appears.
The Data Bars will start in the middle of each cell, and stretch to the left for negative values and to the right for positive values.
As you can observe, the Bars stretching to the right are green in color indicating positive values and the Bars stretching to the left are red in color indicating negative values.
You can use Color Scales to see the value in a cell relative to the values in the other cells in a column. The color indicates where each cell value falls within that range. You can have either a 3-color scale or 2-color scale.
Select the column – Total Amount.
Click on Conditional Formatting in the Styles group under Home tab.
Click on Color Scales in the dropdown list.
Click on Green-Yellow-Red Color Scale in the second dropdown list that appears.
As in the case of Highlight Cells Rules, a Color Scale uses cell shading to display the differences in cell values. As you can observe in the preview, the shade differences are not conspicuous for this data set.
New Formatting Rule dialog box appears.
Click on Format all cells based on their values in the Select a Rule Type box.
In the Edit the Rule Description box, select the following −
Select 3-Color Scale in the Format Style box.
Under Midpoint, for Value – type 75.
Click the OK button.
As you can observe, with the defined color scale, the values are distinctly shaded depicting the data range.
You can use icon sets to visualize numerical differences. In Excel, you have a range of Icon Sets −
| Icon Set Type | Icon Sets |
|---|---|
| Directional |
|
| Shapes |
|
| Indicators |
|
| Ratings |
|
As you can observe, an Icon Set consists of three to five symbols. You can define criteria to associate an icon with the values in a cell range. E.g. a red down arrow for small numbers, a green up arrow for large numbers, and a yellow horizontal arrow for intermediate values.
Select the column – Total Amount.
Click on Conditional Formatting in the Styles group under Home tab.
Click on Icon Sets in the dropdown list.
Click on 3 Arrows (Colored) in the Directional group in the second dropdown list that appears.
Colored arrows appear in the selected column based on the values.
You can define your own Rules and format a range of cells satisfying a particular condition.
New Formatting Rule dialog box appears.
Click on Use a formula to determine which cells to format, in the Select a Rule Type Box.
In Edit the Rule Description box, do the following −
Type a formula in the box - Format values where this formula is true. For example, = PercentRank.INC($E$3:$E$13,E3)>=0.7
Click on Format button.
Choose the format. E.g. Font – bold and Fill – orange.
Click on OK.
Check the Preview.
Click on OK if the Preview is alright. The values in the data set that are satisfying the formula will be highlighted with the format you have chosen.
You can manage the conditional formatting Rules using the Conditional Formatting Rules Manager dialog box.
Click Conditional Formatting in the Styles group under Home tab. Click Manage Rules in the dropdown list.
Conditional Formatting Rules Manager dialog box appears. You can view all the existing Rules. You can add a new Rule, delete a Rule and/or edit a Rule to modify it.
If you choose charts for visual display of data, Excel charts help you to pick up and change the different views. Excel provides several chart types that enable you to express the message you want to convey with the data at hand in your dashboard with a graphical representation of any set of data.
In addition, there are certain sophisticated charts that are useful for some specific purposes. Some of these are available in Excel 2016. But, they can also be built from the built in chart types in Excel 2013.
In this chapter, you will learn about the chart types in Excel and when to use each chart type. Remember that in one chart in the dashboard, you should covey only one message. Otherwise, it may cause confusion in the interpretation. You can size the charts in such a way that you can accommodate more number of charts in the dashboard, each one conveying a particular message.
Apart from the chart types that are discussed in this chapter, there are certain advanced charts that are widely used to depict the information with visual cues. You will learn about the advanced chart types and their usage in the chapter – Advanced Excel Charts for Dashboards.
You can find the following major chart types if you have Excel 2013 −
To learn about these charts, refer to the tutorial − Excel Charts.
When you have mixed type of data, you can display it with Combo (Combination) charts. The charts can either have only the Primary Vertical Axis or a combination of Primary Vertical Axis and Secondary Axis. You will learn about Combo charts in a later section.
To display the data by a chart in your dashboard, first identify the purpose of the chart. Once you have clarity on what you want to represent by a chart, you can select the best chart type that depicts your message.
Following are some suggestions on selecting a chart type −
If you want to compare data values, you can choose a bar chart, pie chart, line chart, or scatter chart.
If you want to show distribution, you can do so with a column chart, scatter chart or line chart.
If you want to show trends over time, you can use a line chart.
If you want to represent parts of a whole, a pie chart can be an option. But, while you use a pie chart, remember that only two to three different data points with very different data values can be effectively depicted with the varying sizes of the Pie slices. If you try to depict more number of data points in a Pie chart, it can be difficult to derive the comparison.
You can use Scatter chart if any of the following is the purpose−
You want to show similarities between large sets of data instead of differences between data points.
You want to compare many data points without regard to time. The more data that you include in a Scatter chart, the better the comparisons you can make.
Recommended Charts in Excel helps you to find a chart type that is suitable to your data.
In Excel, you can create a chart with a chart type and modify it later any time easily.
Sparklines are tiny charts placed in single cells, each representing a row of data in your selection. They provide a quick way to see trends. In Excel, you can have Line Sparklines, Column Sparklines or Win/Loss Sparklines.
You can add Sparklines to your table quickly with the Quick Analysis tool.
Identify the data for which you want to add Sparklines.
Keep an empty column to the right side of the data and name the column. Sparklines will be placed in this column.
Select the data.
Quick Analysis tool button
appears at the bottom right corner of your selected data.
Click on the Quick Analysis
button. Quick Analysis tool appears.
Click on SPARKLINES. Chart options appear.
Click on Line. Line Charts will be displayed for each row in the selected data.
Click on Column. Column Charts will be displayed for each row in the selected data.
Win/Loss charts are not suitable for this data. Consider the following data to understand how Win/Loss charts look.
You can use Combo charts to combine two or more chart types to compare data values of different categories, if the data ranges are varying significantly. With a Secondary Axis to depict the other data range, the chart will be easier to read and grasp the information quickly.
You can fine tune charts quickly using the three buttons
,
and
that appear next to the upper-right corner of the chart.
With
Chart Elements, you can add or remove axis, axis titles, legend, data
labels, gridlines, error bars, etc. to the chart.
With
Chart Styles, you can customize the look of the chart by formatting the
chart style and colors.
With
Chart Filters, you can dynamically edit the data points (values) and names
that are visible on the chart being displayed.
You can select / deselect Chart Elements.
You can format the Gridlines to show the depth axis.
You can set a Chart Style.
You can choose a color scheme for your chart.
You can dynamically select values and names for display.
Values are the data series and the categories.
Names are the names of the data series (columns) and the categories (rows).
You can have aesthetic and meaningful Data Labels.
You can place Data Labels at any position with respect to the data points.
You can format Data Labels with various options, including effects.
You can change Data Labels to any shape.
Data Labels can be of different sizes. You can resize each Data label so that the text in it would be visible.
You can include text from data points or any other text for any of the Data Labels so as to make them refreshable and thus dynamic.
You can connect Data Labels to their data points with Leader Lines.
You can place Data Labels with Leader Lines at any distance from the data points by moving them.
You can format Leader Line to make them conspicuous.
You can choose any of these options to display the Data Labels on the chart based on your data and what you want to highlight.
Data Labels stay in place, even when you switch to a different type of chart. But, finalize the chart type before formatting any chart elements, including Data Labels.
You can depict forecast of the results in a chart using Trendlines.
You can insert different types of Shapes in your chart. After you insert a Shape, you can add Text to it, with Edit Text. You can Edit Shape with Change Shape and/or Edit Points.
You can change the Style of the Shape, choose a Shape Fill Color, Format Shape Outline and add Visual Effects to the Shape.
In 3-D Column charts, by default, you will have boxes.
To make your charts more conspicuous in dashboards, you can choose other 3-D column shapes like cylinders, cones, pyramids, etc. You can select these shapes in the Format Data Series pane.
You can create more emphasis on your data presentation by using a Picture in place of Columns.
If you have more data to display on the dashboard that does not fit into a single screen, you can opt for using Excel controls that come as a part of Excel Visual Basic. The most commonly used controls are scrollbars, radio buttons, and checkboxes. By incorporating these in the dashboard, you can make it interactive and allow the user to view the different facets of the data by possible selections.
You can provide interactive controls such as scroll bars, checkboxes and radio buttons in your dashboards to facilitate the recipients to dynamically view the different facets of data being displayed as results. You can decide on a particular layout of the dashboard along with the recipients and use the same layout then onwards. Excel interactive controls are simple to use and does not require any expertise in Excel.
The Excel interactive controls will be available in the DEVELOPER tab on the Ribbon.
If you do not find the DEVELOPER tab on the Ribbon, do the following −
One of the features of any dashboard is that each component in the dashboard is as compact as possible. Suppose your results look as follows −
If you can present this table with a scroll bar as given below, it would be easier to browse through the data.
You can also have a dynamic Target Line in a Bar chart with scroll bar. As you move the scroll bar up and down, the Target Line moves up and down and those bars that are crossing the Target Line will get highlighted.
In the following sections, you will learn how to create a scroll bar and how to create a dynamic target line that is linked to a scroll bar. You will also learn how to display dynamic labels in scroll bars.
To create a scrollbar for a table, first copy the headers of the columns to an empty area on the sheet as shown below.
Insert a scrollbar.
Click on the DEVELOPER tab on the Ribbon.
Click on Insert in the Controls group.
Click on Scroll Bar icon under Form Controls in the dropdown list of icons.
Take the cursor to the column I and pull down to insert a vertical scroll bar.
Adjust the height and width of the scroll bar and align it to the table.
Right click on the scroll bar.
Click on Format Control in the dropdown list.
Format Control dialog box appears.
Click on the Control tab.
Type the following in the boxes that appear.
Click the OK button. The scroll bar is ready to use. You have chosen the cell O2 as the cell link for the scroll bar, which takes values 0 – 36, when you move the scroll bar up and down. Next, you have to create copy of the data in the table with a reference based on the value in the cell O2.
In the cell K3, type the following −
= OFFSET(Summary[@[S. No.]],$O$2,0).
Hit the Enter button. Fill in the cells in the column copying the formula.
Fill in the cells in the other columns copying the formula.
Your dynamic and scrollable table is ready to be copied to your dashboard.
Move the scroll bar down.
As you can observe, the value in the cell - scroll bar cell link changes, and the data in the table is copied based on this value. At a time, 12 rows of data is displayed.
Drag the scroll bar to the bottom.
The last 12 rows of the data is displayed as the current value is 36 (as shown in the cell O2) and 36 is the maximum value that you have set in the Form Control dialog box.
You can change the relative position of the dynamic table, change the number of rows to be displayed at a time, cell link to scroll bar, etc. based on your requirement. As you have seen above, these need to be set in the Format Control dialog box.
Suppose you want to display the sales region-wise over the last 6 months. You also have set targets for each month.
You can do the following −
Select the data. Insert a clustered column chart.
Change the chart type to combo. Select chart type as Line for the Target series and Clustered Column for the rest of the series.
Create a base table for the Target Line. You will make this dynamic later.
Change the data series values for the Target Line to the Target column in the above table.
Click the OK button.
Change the color scheme for the Clustered Column. Change the Target Line into a green dotted line.
Insert a scroll bar and place it below the chart and size it to span from January to June.
Enter the scroll bar parameters in the Format Control dialog box.
Create a table with two columns − Month and Target.
Enter the values based on the data table and scroll bar cell link.
This table displays the Month and the corresponding Target based on the scroll bar position.
Now, you are set to make your Target Line dynamic.
Change the Target column values in the base table you created for the Target Line by typing = $G$12 in all the rows.
As you are aware, the cell G12 displays the Target value dynamically.
As you can observe, the Target Line moves based on the scroll bar.
This is the final step. You want to highlight the values meeting the target at any point of time.
Add columns to the right side of your data table − East-Results, North-Results, SouthResults and West-Results.
In the cell H3, enter the following formula −
= IF(D3 >= $G$12,D3,NA())
Copy the formula to the other cells in the table. Resize the table.
As you can observe, the values in the columns - East-Results, North-Results, SouthResults and West-Results change dynamically based on the scroll bar (i.e. Target value). Values greater than or equal to the Target are displayed and the other values are just #N/A.
Change the Chart Data Range to include the newly added columns in the data table.
Click on Change Chart Type.
Make the Target series be Line and the rest Clustered Column.
For the newly added data series, select Secondary Axis.
Format data series in such a way that the series East, North, South and West have a fill color orange and the series East-Results, North-Results, South-Results and WestResults have a fill color green.
Enter a Data Label for the Target Line and make it dynamic with the cell reference to the Month value in the dynamic data table.
Your chart with dynamic Target Line is ready for inclusion in the dashboard.
You can clear the secondary axis as it is not required. As you move the scroll bar, Target Line moves and the Bars will get highlighted accordingly. Target Line also will have a Label showing the Month.
Radio buttons are normally used to select an option from a given set of options. It is always depicted by a small circle, which will have a dot in it when selected. When you have a set of radio buttons, you can select only one of them.
In Excel, Radio buttons are referred to as Option Buttons.
You can use Excel Option Buttons in charts to choose the data specifics the reader wants to have a look at. For example, in the example in the previous section you have created a scroll bar to get a dynamic Target Line with target values based on Month. You can use Option Buttons to select a Month and thus the target value, and base the Target Line on the target value. Following will be the steps −
Steps 1 and 2 are same as in the previous case. By the end of the second step, you will have the following chart.
Insert an Option Button.
Click on the DEVELOPER tab on the Ribbon.
Click on Insert in the Controls group.
Click on Option Button icon under Form Controls in the dropdown list of icons.
Place it at the top right corner of the chart.
Right click on the Option button. Click the Format Control option in the dropdown list.
Enter the Option Button parameters in the Format Object dialog box, under the Control tab.
The cell F10 is linked to the Option Button. Make 5 copies of the Option Button vertically.
As you can observe, all the Option Buttons have the same name, referred to as Caption Names. But, internally Excel will have different names for these Option Buttons, which you can look at either in the Name box. Further, as Option Button 1 was set to link to the cell F10, all the copies also refer to the same cell.
Click on any of the Option Buttons.
As you can observe, the number in the linked cell changes to the serial number of the Option Button. Rename the Option Buttons to January, February, March, April, May and June.
Create a table with two columns − Month and Target. Enter the values based on the data table and scroll bar cell link.
This table displays the Month and the corresponding Target based on the selected Option Button.
Now, you are set to make your Target Line dynamic.
Change the Target column values in the base table you created for the Target Line by typing = $G$12 in all the rows.
As you are aware, the cell G12 displays the Target value dynamically.
As you can observe, the Target Line is displayed based on the selected Option Button.
This is the final step. You want to highlight the values meeting the target at any point of time.
Add columns to the right side of your data table − East-Results, North-Results, SouthResults and West-Results.
In the cell H3, enter the following formula −
= IF(D3 >= $G$12,D3,NA())
Copy the formula to the other cells in the table. Resize the table.
As you can observe, the values in the columns − East-Results, North-Results, SouthResults and West-Results change dynamically based on the scroll bar (i.e. Target value). Values greater than or equal to the Target are displayed and the other values are just #N/A.
Change the Chart Data Range to include the newly added columns in the data table.
Click on Change Chart Type.
Make the Target series be Line and the rest Clustered Column.
For the newly added data series, select Secondary Axis.
Format data series in such a way that the series East, North, South and West have a fill color orange and the series East-Results, North-Results, South-Results and WestResults have a fill color green.
Add a dynamic Data Label to the Target Line with value from the cell $G$12.
Clear the secondary axis as it is not required.
Under the VIEW tab on the Ribbon, uncheck the Gridlines box.
Change the Label option to High in the Format Axis options. This shifts the Vertical Axis Labels to the right, making your Target Line Data Label conspicuous.
Your chart with dynamic Target Line and Option Buttons is ready for inclusion in the dashboard.
As you select an Option Button, Target Line is displayed as per the target value of the selected Month and the Bars will get highlighted accordingly. Target Line also will have a Data Label showing the target value.
Checkboxes are normally used to select one or more options from a given set of options. Checkboxes are always depicted by small squares, which will have a tick mark when selected. When you have a set of Checkboxes, it is possible to select any number of them. For example,
You can use Excel Check Boxes in charts to choose the data specifics the reader wants to have a look at. For example, in the example in the previous section, you have created column chart that displays the data of 4 Regions – East, North, South and West. You can use Check Boxes to select the Regions for which data is displayed. You can select any number of Regions at a time.
You can start with the last step of the previous section −
Insert a Checkbox.
Click on the DEVELOPER tab on the Ribbon.
Click on Insert in the Controls group.
Click on Check Box icon under Form Controls in the dropdown list of icons.
Place it at the top left corner of the chart.
Change the name of the Check Box to East.
Right-click on the checkbox. Click on Format Control in the dropdown list.
Enter the Check Box parameters in the Format Control dialog box, under the Control tab.
Click the OK button. You can observe that in the linked cell C19, TRUE will be displayed if you select the Check Box and FALSE will be displayed if you deselect the Check Box.
Copy the Check Box and paste 3 times horizontally.
Change the Names to North, South and West.
As you can observe, when you copy a Check Box, the linked cell remains the same for the copied Check Box also. However, since Check Boxes can have multiple selections, you need to make the linked cells different.
Change the linked cells for North, South and West to $C$20, $C$21 and $C$22 respectively.
The next step is to have only the selected Regions’ data in the Chart.
Create a table structure as follows −
Add the Target column.
Change the Chart data to this table.
The Chart displays the data for the selected Regions that is more than the target value set for the selected Month.
You are aware that charts are useful in conveying you data message visually. In addition to the chart types that are available in Excel, there are some widely used application charts that became popular. Some of these are also included in Excel 2016.
In case you are using Excel 2013 or earlier versions, please refer to the tutorial – Advanced Excel Charts to learn about these charts and how to create them with the built-in chart types.
Following advanced Excel chart types will come handy to include in your dashboards −
Waterfall charts are ideal for showing how you have arrived at a net value such as net income, by breaking down the cumulative effect of positive and negative contributions.
Band chart is suitable to represent data across a time period graphically, confiding each data point to a defined interval. For example, customer survey results of a product from different regions.
A Gantt chart is a chart in which a series of horizontal lines shows the amount of work done in certain periods of time in relation to the amount of work planned for those periods.
When you have to represent a target value and an actual value, you can emphatically show these values with a Thermometer chart.
A Gauge Chart shows the minimum, the maximum and the current value depicting how far from the maximum you are.
Bullet chart can be used to compare a measure to one or more related measures and relate the measure to defined quantitative ranges that declare its qualitative state, for example, good, satisfactory and poor. You can use Bullet chart to display KPIs also.
Funnel chart is used to visualize the progressive reduction of data as it passes from one phase to another. E.g. Sales Pipeline.
Waffle chart is a good choice to display work progress as percentage of completion, goal achieved vs Target, etc.
A Heat Map is a visual representation of data in a Table to highlight the data points of significance.
If you have to display the changes that occur at irregular intervals that remain constant between changes, Step chart is useful.
Box and Whisker charts are commonly used in statistical analysis. For example, you can use a Box and Whisker chart to compare experimental results or competitive exam results.
A Histogram is a graphical representation of the distribution of numerical data and is widely used in Statistical Analysis.
Pareto chart is another chart widely used in Statistical Analysis for decision making. It represents the Pareto analysis, also called 80/20 Rule, meaning that 80% of results are due to 20% of causes.
Suppose you have to display the performance of the sales team quarterly on the dashboard. The data can be as given below.
You can display this information on the dashboard using Bullet chart as follows −
As you can observe, this occupies less space, yet conveys a lot of information.
Suppose you have to display the Profit % for the regions − East, North, South and West.
You can display this information emphatically on your dashboard with Waffle charts as shown below.
This display not only depicts the values, but also a good comparison.
If you have your data in a single Excel table, you can summarize the data in the way that is required using Excel PivotTables. A PivotTable is an extremely powerful tool that you can use to slice and dice data. You can track, analyze hundreds of thousands of data points with a compact table that can be changed dynamically to enable you to find the different perspectives of the data. It is a simple tool to use, yet powerful.
Excel gives you a more powerful way of creating a PivotTable from multiple tables, different data sources and external data sources. It is named as Power PivotTable that works on its database known as Data Model. You will get to know about Power PivotTable and other Excel power tools such as Power PivotChart and Power View Reports in other chapters.
PivotTables, Power PivotTables, Power PivotCharts and Power View Reports come handy to display summarized results from big data sets on your dashboard. You can get mastery on the normal PivotTable before you venture into the power tools.
You can create a PivotTable either from a range of data or from an Excel table. In both the cases, the first row of the data should contain the headers for the columns.
You can start with an empty PivotTable and construct it from scratch or make use of Excel Recommended PivotTables command to preview the possible customized PivotTables for your data and choose one that suits your purpose. In either case, you can modify a PivotTable on the fly to get insights into the different aspects of the data at hand.
Consider the following data range that contains the sales data for each Salesperson, in each Region and in the months of January, February and March −
To create a PivotTable from this data range, do the following −
Ensure that the first row has headers. You need headers because they will be the field names in your PivotTable.
Name the data range as SalesData_Range.
Click on the data range − SalesData_Range.
Click on the INSERT tab on the Ribbon.
Click on PivotTable in the Tables group.
Create PivotTable dialog box appears.
As you can observe, in Create PivotTable dialog box, under Choose the data that you want to analyze, you can either select a Table or Range from the current workbook or use an external data source. Hence, you can use the same steps to create a PivotTable form either a Range or Table.
Click on Select a table or range.
In the Table/Range box, type the range name − SalesData_Range.
Click on New Worksheet under Choose where you want the PivotTable report to be placed.
You can also observe that you can choose to analyze multiple tables, by adding this data range to Data Model. Data Model is Excel Power Pivot database.
Click the OK button. A new worksheet will get inserted into your workbook. The new worksheet contains an empty PivotTable.
Name the worksheet − Range-PivotTable.
As you can observe, PivotTable Fields list appears on the right side of the worksheet, containing the header names of the columns in the data range. Further, on the Ribbon, PivotTable Tools − ANALYZE and DESIGN appear.
You need to select PivotTable fields based on what data you want to display. By placing the fields in appropriate areas, you can obtain the desired layout for the data. For example to summarize the order amount salesperson-wise for the months − January, February and March, you can do the following −
Click on the field Salesperson in the PivotTable Fields list and drag it to ROWS area.
Click on the field Month in the PivotTable Fields list and drag that also to ROWS area.
Click on Order Amount and drag it to ∑ VALUES area.
Your PivotTable is ready. You can change the layout of the PivotTable by just dragging the fields across the areas. You can select / deselect fields in the PivotTable Fields list to choose the data you want to display.
If you are required to focus on a subset of your PivotTable data, you can filter the data in the PivotTable based on a subset of the values of one or more fields. For example in the above example, you can filter the data based on the Range field so that you can display data only for the selected Region(s).
There are several ways to filter data in a PivotTable −
You will get to know the usage of Report Filters in this section and Slicers in the next section. For other filtering options, refer to the Excel PivotTables tutorial.
You can assign a Filter to one of the fields so that you can dynamically change the PivotTable based on the values of that field.
The Filter with the label as Region appears above the PivotTable (in case you do not have empty rows above your PivotTable, PivotTable gets pushed down to make space for the Filter).
As you can observe,
Salesperson values appear in rows.
Month values appear in columns.
Region Filter appears on the top with default selected as ALL.
Summarizing value is Sum of Order Amount.
Sum of Order Amount Salesperson-wise appears in the column Grand Total.
Sum of Order Amount Month-wise appears in the row Grand Total.
Click on the arrow in the Region Filter.
Dropdown list with the values of the field Region appears.
Check the box Select Multiple Items. Check boxes will appear for all the values. By default, all the boxes are checked.
Uncheck the box (All). All the boxes will get unchecked.
Check the boxes - South and West.
Click the OK button. Data pertaining to South and West regions only will get summarized.
As you can observe, in the cell next to the Region Filter - (Multiple Items) is displayed, indicating that you have selected more than one value. But how many values and / or which values is not known from the report that is displayed. In such a case, using Slicers is a better option for filtering.
Filtering using Slicers has many advantages −
You can have multiple Filters by selecting the fields for the Slicers.
You can visualize the fields on which the Filter is applied (one Slicer per field).
A Slicer will have buttons denoting the values of the field that it represents. You can click on the buttons of the Slicer to select/ unselect the values in the field.
You can visualize what values of a field are used in the Filter (selected buttons are highlighted in the Slicer).
You can use a common Slicer for multiple PivotTables and / or PivotCharts.
You can hide / unhide a Slicer.
To understand the usage of Slicers, consider the following PivotTable.
Suppose you want to filter this PivotTable based on the fields − Region and Month.
Insert Slicers dialog box appears. It contains all the fields from your data.
Click the OK button. Slicers for each of the selected fields appear with all the values selected by default. Slicer Tools appear on the Ribbon to work on the Slicer settings, look and feel.
As you can observe, each Slicer has all the values of the field that it represents and the values are displayed as buttons. By default, all the values of a field are selected and hence all the buttons are highlighted.
Suppose you want to display the PivotTable only for South and West regions and for the February and March months.
Click on South in the Region Slicer. Only South will be highlighted in the Slicer – Region.
Keep Ctrl key pressed and click on West in the Region Slicer.
Click on February in the Month Slicer.
Keep Ctrl key pressed and click on March in the Month Slicer. Selected values in the Slicers are highlighted. PivotTable will be summarized for the selected values.
To add/remove values of a field from the filter, keep the Ctrl key pressed and click on those buttons in the respective Slicer.
When your data sets are big, you can use Excel Power Pivot that can handle hundreds of millions of rows of data. The data can be in external data sources and Excel Power Pivot builds a Data Model that works on a memory optimization mode. You can perform the calculations, analyze the data and arrive at a report to draw conclusions and decisions. The report can be either as a Power PivotTable or Power PivotChart or a combination of both.
You can utilize Power Pivot as an ad hoc reporting and analytics solution. Thus, it would be possible for a person with hands-on experience with Excel to perform the high-end data analysis and decision making in a matter of few minutes and are a great asset to be included in the dashboards.
You can use Power Pivot for the following −
Power PivotTable resembles PivotTable in its layout, with the following differences −
PivotTable is based on Excel tables, whereas Power PivotTable is based on data tables that are part of Data Model.
PivotTable is based on a single Excel table or data range, whereas Power PivotTable can be based on multiple data tables, provided they are added to Data Model.
PivotTable is created from Excel window, whereas Power PivotTable is created from PowerPivot window.
Suppose you have two data tables – Salesperson and Sales in the Data Model. To create a Power PivotTable from these two data tables, proceed as follows −
Click on the Home tab on the Ribbon in PowerPivot window.
Click on PivotTable on the Ribbon.
Click on PivotTable in the dropdown list.
Create PivotTable dialog box appears. Click on New Worksheet.
Click the OK button. New worksheet gets created in Excel window and an empty Power PivotTable appears.
As you can observe, the layout of the Power PivotTable is similar to that of PivotTable.
The PivotTable Fields List appears on the right side of the worksheet. Here, you will find some differences from PivotTable. The Power PivotTable Fields list has two tabs − ACTIVE and ALL, that appear below the title and above the fields list. ALL tab is highlighted. The ALL tab displays all the data tables in the Data Model and ACTIVE tab displays all the data tables that are chosen for the Power PivotTable at hand.
Click the table names in the PivotTable Fields list under ALL.
The corresponding fields with check boxes will appear.
Each table name will have the symbol
on the left side.
If you place the cursor on this symbol, the Data Source and the Model Table Name of that data table will be displayed.
The field Salesperson appears in the Power PivotTable and the table Salesperson appears under ACTIVE tab.
Both the tables – Sales and Salesperson appear under the ACTIVE tab.
Power PivotTable can be modified dynamically to explore and report data.
A Power PivotChart is a PivotChart that is based on Data Model and created from the Power Pivot window. Though it has some features similar to Excel PivotChart, there are other features that make it more powerful.
Suppose you want to create a Power PivotChart based on the following Data Model.
Create PivotChart dialog box appears. Click New Worksheet.
Click the OK button. An empty PivotChart gets created on a new worksheet in the Excel window. In this chapter, when we say PivotChart, we are referring to Power PivotChart.
As you can observe, all the tables in the data model are displayed in the PivotChart Fields list.
Two field buttons for the two selected fields appear on the PivotChart. These are the Axis field buttons. The use of field buttons is to filter data that is displayed on the PivotChart.
Drag TotalSalesAmount from each of the 4 tables – East_Sales, North_Sales, South_Sales and West_Sales to ∑ VALUES area.
As you can observe, the following appear on the worksheet −
You can remove the legend and the value field buttons for a tidier look of the PivotChart.
Click on the
button at the top right corner of the PivotChart.
Deselect Legend in the Chart Elements.
Right click on the value field buttons.
Click on Hide Value Field Buttons on Chart in the dropdown list.
The value field buttons on the chart will be hidden.
Note that display of Field Buttons and/or Legend depends on the context of the PivotChart. You need to decide what is required to be displayed.
As in the case of Power PivotTable, Power PivotChart Fields list also contains two tabs − ACTIVE and ALL. Further, there are 4 areas −
As you can observe, Legend gets populated with ∑ Values. Further, Field Buttons get added to the PivotChart for the ease of filtering the data that is being displayed. You can click on the arrow on a Field Button and select/deselect values to be displayed in the Power PivotChart.
Power Pivot provides you with different combinations of Power PivotTable and Power PivotChart for data exploration, visualization and reporting.
Consider the following Data Model in Power Pivot that we will use for illustrations −
You can have the following Table and Chart Combinations in Power Pivot.
Chart and Table (Horizontal) - you can create a Power PivotChart and a Power PivotTable, one next to another horizontally in the same worksheet.
Chart and Table (Vertical) - you can create a Power PivotChart and a Power PivotTable, one below another vertically in the same worksheet.
These combinations and some more are available in the dropdown list that appears when you click on PivotTable on the Ribbon in the Power Pivot window.
You can use Hierarchies in Power Pivot to make calculations and to drill up and drill down the nested data.
Consider the following Data Model for illustrations in this chapter.
You can create Hierarchies in the diagram view of the Data Model, but based on a single data table only.
Click on the columns – Sport, DisciplineID and Event in the data table Medal in that order. Remember that the order is important to create a meaningful hierarchy.
Right-click on the selection.
Click on Create Hierarchy in the dropdown list.
The hierarchy field with the three selected fields as the child levels gets created.
You can create a Power PivotTable using the hierarchy that you created in the Data Model.
As you can observe, in the PivotTable Fields list, EventHierarchy appears as a field in Medals table. The other fields in the Medals table are collapsed and shown as More Fields.
in front of EventHierarchy.
in front of More Fields.The fields under EventHierarchy will be displayed. All the fields in the Medals table will be displayed under More Fields.
Add fields to the Power PivotTable as follows -
As you can observe, the values of Sport field appear in the Power PivotTable with a + sign in front of them. The medal count for each sport is displayed.
Click on the + sign before Aquatics. The DisciplineID field values under Aquatics will be displayed.
Click on the child D22 that appears. The Event field values under D22 will be displayed.
As you can observe, medal count is given for the Events, that get summed up at the parent level – DisciplineID, that get further summed up at the parent level – Sport.
You can create calculations using a hierarchy in a Power PivotTable. For example in the EventsHierarchy, you can display the no. of medals at a child level as a percentage of the no. of medals at its parent level as follows –
Value Field Settings dialog box appears.
As you can observe, the child levels are displayed as the percentage of the Parent Totals. You can verify this by summing up the percentage values of the child level of a parent. The sum would be 100%.
You can quickly drill up and drill down across the levels in a hierarchy in a Power PivotTable using Quick Explore tool.
Click on a value of Event field in the Power PivotTable.
Click on the Quick Explore tool -
that appears at the bottom right corner of the cell containing the selected
value.
EXPLORE box with Drill Up option appears. This is because from Event you can only drill up as there are no child levels under it.
Click on Drill Up. Power PivotTable data gets drilled up to Discipline level.
Click on the Quick Explore tool -
that appears at the bottom right corner of the cell containing a value.
EXPLORE box appears with Drill Up and Drill Down options displayed. This is because from Discipline you can drill up to Sport or drill down to Event levels.
This way you can quickly move up and down the hierarchy in a Power PivotTable.
You can insert Slicers and share them across the Power PivotTables and Power PivotCharts.
Create a Power PivotChart and Power PivotTable next to each other horizontally.
Click on Power PivotChart.
Drag Discipline from Disciplines table to AXIS area.
Drag Medal from Medals table to ∑ VALUES area.
Click on Power PivotTable.
Drag Discipline from Disciplines table to ROWS area.
Drag Medal from Medals table to ∑ VALUES area.
Insert Slicers dialog box appears.
Two Slicers – NOC_CountryRegion and Sport appear.
Arrange and size them to align properly next to the Power PivotTable as shown below.
The Power PivotTable gets filtered to the selected values.
As you can observe, the Power PivotChart is not filtered. To filter Power PivotChart with the same filters, you can use the same Slicers that you have used for the Power PivotTable.
Report Connections dialog box appears for the NOC_CountryRegion Slicer.
As you can observe, all the Power PivotTables and Power PivotCharts in the workbook are listed in the dialog box.
Click on the Power PivotChart that is in the same worksheet as the selected Power PivotTable.
Click the OK button.
Repeat for Sport Slicer.
The Power PivotChart also gets filtered to the values selected in the two Slicers.
Next, you can add more detail to the Power PivotChart and Power PivotTable.
You can create aesthetic reports with Power PivotTables and Power PivotCharts and include them in dashboards. As you have seen in the previous section, you can use Report Layout options to choose the look and feel of the reports. For example with the option - Show in Outline Form and with Banded Rows selected, you will get the report as shown below.
As you can observe, the field names appear in place of Row Labels and Column Labels and the report looks self-explanatory.
You can select the objects that you want to display in the final report in the Selection pane. For example, if you do not want to display the Slicers that you created and used, you can just hide them by deselecting them in the Selection pane.
Excel Power View enables interactive data visualization that encourages intuitive ad-hoc data exploration. The data visualizations are versatile and dynamic, thus facilitating ease of data display with a single Power View report.
You can handle large data sets spanning several thousands of rows on the fly switching from one visualization to another, drilling up and drilling down the data and displaying the essence of the data.
Power View reports are based the Data Model that can be termed as the Power View Database and that optimizes the memory enabling faster computations and displays of data. A typical Data Model will be as shown below.
In this chapter, you will understand the salient features of Power View reports that you can incorporate in your dashboard.
Power View provides various types of data visualizations −
Table visualization is the simplest and default visualization. If you want to create any other visualization, first table will be created that you need to convert to the required visualization by Switch Visualization options.
Power View has following chart types in visualizations −
Line Chart
Bar Chart
Column Chart
Scatter Chart and Bubble Chart
Pie Chart
Map
Map with Pie Charts
Power View visualizations unlike Excel charts are powerful as they can be displayed as combination with each one depicting and/or highlighting significant results.
For example, you can have three visualizations in Power View −
Table visualization − displaying Country, Medal and Medal Count.
Stacked Column chart visualization − displaying Country, Gender and Medal Count.
Pie chart visualization − displaying Medal, Gender and Medal Count.
Suppose you click on a Pie slice in the above Power View. You will observe the following −
The Pie slice that is clicked will be highlighted while the rest of the Pie slices will get dimmed.
The Table will display only the data corresponding to the highlighted slice.
The Clustered column will highlight the data corresponding to the highlighted slice and the rest of the chart will get dimmed.
This feature helps you to enable your audience viewing results from large data sets to explore the significant data points.
You can use common Slicers in Power View to filter the data that is displayed by all the visualizations in Power View.
For example, in the following Power View, you have 2 visualizations −
Stacked Bar chart displaying Medal Count by Country and Medal.
Stacked Column chart displaying Medal Count by Sport and Medal.
Suppose you have two Slicers – one for Gender and one for Season, the data in both the charts will get filtered to the selected fields in the Slicers.
In Power View, Tiles help you to pick one data point of a field and view the corresponding values. Tiles can be used in Table, Matrix, Card, Stacked Bar chart and Map visualizations.
Tiles can be used with a combination of visualizations also.
You can use the interactive nature of the charts in such visualizations also.
You can produce aesthetic Power View reports that you can include in your dashboard.
This could be done by choosing a suitable background, choosing the font, font size, color scales, etc.
Key Performance Indicators (KPIs) are quantifiable measurements for assessing what is achieved against the set goals/ targets /business objectives. In dashboards, KPIs necessarily will have a position to display visually where a person / department / organization stands currently compared to where it is supposed to be.
Examples of KPIs include the following −
Sales department of an organization might use a KPI to measure monthly gross profit against projected gross profit.
Accounting department might measure monthly expenditures against revenue to evaluate costs.
Human resources department might measure quarterly employee turnover.
Business professionals frequently use KPIs that are grouped together in a business scorecard to obtain a quick and accurate historical summary of business success or to identify trends.
Dashboards either viewed publicly or selectively present continuously monitored KPIs and hence are chosen as the best monitoring and reporting tools.
A KPI essentially contains three components −
Though it is the Status that one would be interested in, the Base Value and Target Value are also equally important as a KPI need not be static and can undergo changes as the time proceeds.
In Excel, Base Value, Target Value and Status are defined as given in the following sections.
A Base Value is defined by a calculated field that resolves to a value. The calculated field represents the current value for the item in that row of the Table or Matrix. E.g. aggregate of sales, profit for a given period, etc.
A Target Value (or Goal) is defined by a calculated field that resolves to a value, or by an absolute value. It is the value against which the current value is evaluated. This could be one of the following −
A fixed number that is the goal all the rows should achieve. E.g. Sales target for all the salespersons.
A calculated field that might have a different goal for each row. E.g. Budget (calculated field), department-wise in an organization.
Status is the visual indicator of the value. Excel provide different ways of visualizing Status as against Target Value.
You can use Bullet chart to visualize KPIs. You can illustrate the status thresholds by shaded regions of a column and the status as a column superimposed on the status thresholds.
You can define and visualize KPIs in Power View also.
To define a KPI, you need have the following in place −
For example, to define a KPI to monitor sales performance, you need to do the following −
Identify the cell(s) that contain the calculated values of total sales. This is for Base Value.
Define Target Value that can be absolute or varying.
Define the Status thresholds that help you visualize the Status.
You can visualize KPIs with Bullet charts, in which the following will be clearly depicted.
You can visualize KPIs that are defined in Power View using icons.
You can also produce an aesthetic report in Power View with the KPIs that can be included in your dashboard.
As you can observe, in the Power View you could portray the results as follows −
Table visualization with icons for displaying KPI status.
100% Stacked Bar Chart visualization depicting the percentage achieved with respect to the Goal. You can also notice that it gives a clear comparison of the performance of all the Salespersons.
Card visualization depicting the KPI status of the Salespersons along with the Region they belong to. You can interactively scroll through the Tiles to display results for different Regions that would give scope to assess performance region-wise also.
In the previous chapters, you have learnt about various Excel features that come handy in setting up your dashboard. In this chapter, you will learn how to build a dashboard, i.e., the steps that are required to get the dashboard in place. You will also get to know the dos and don’ts regarding dashboards.
As any dashboard is based on the specific intent of what the audience is most interested about, dashboard components and dashboard layout varies from case to case.
The first step in building a dashboard is the initial preparation. Take some time in understanding the following −
Why do you need the dashboard? − Is this dashboard for a specific task, like showing the status of a project, or does it need to achieve a broader goal, like measuring business performance? Understanding why you are building the dashboard will guide you in the design.
What purpose the dashboard will serve?− Your dashboard should highlight only the data that adds value. You should understand the data that is required. Anything outside that is unnecessary.
What is the source of data? − You should understand from where the data comes. It can just be an Excel worksheet or it can be through data connections to your Excel workbook from various dynamic data sources.
Who is the audience for the dashboard? − Is this for a manager, an executive, a stakeholder, an external vendor or general audience? Understand their requirements and preferences such as how much time do they have to look at the dashboard, the level of detail they expect and how they would like to digest the information. For example, while choosing a chart type, knowing the audience helps you to decide on whether you have to show relations between values or you have to draw a specific comparison.
Does the dashboard need to be static or dynamic? − Can the dashboard be updated periodically, say, weekly or monthly, or does it require to get updated to continuously streamline the data changes that happen at the backend? This choice will change the way you build the dashboard.
Does the dashboard need to be just a display or is it to be interactive? − Can the dashboard have read-only access or do you have to provide interactive controls / features that enable certain people to explore the data as required? This choice also will change the way you build the dashboard.
Once you have answers to these questions, decide on what Excel features you need and you don’t need. This is because your goal and expertise is to produce an effective dashboard that suits the purpose.
Next, identify the components of the dashboard. These can be text, tables, charts, interactive controls, etc. Decide on the dashboard layout with these components.
Mockup your Excel dashboard on a PowerPoint slide. Draw boxes for each component to get a sense of the layout and add quick sketches of the components that you want to include. You can also do this on a piece of paper. Get approval for this mockup from the management and/or the key audience before you start working on the actual dashboard. This will save time on rework. However, it is quite possible that you might have to tweak in some changes to the dashboard as the dashboard gets into usage and you receive feedback. But, the approved dashboard mockup is a real good starting for your work.
Before building the dashboard in Excel, you need to organize the data source. In Excel, this is possible in various ways −
If the data is just an Excel table, establish a link to your workbook from the workbook where the data will get updated.
If the data is from multiple Excel tables, or if it is from various data sources, it is a good option to build the Data Model in your workbook.
You can either import the data into the workbook periodically or establish data connections so as to refresh the data as when it gets updated, based on whether the dashboard has to be static or dynamic.
Once you have organized the data, you need to structure your workbook. Insert two to three worksheets in the workbook − one worksheet for your dashboard and one or two worksheets for the data (data or PivotTable/PivotCharts or Power View Reports, which you can hide). This will help you to organize and maintain your Excel workbook.
Based on the choices you have, i.e. the answers you have for the questions in the Initial Preparation step, prepare the data for the Excel dashboard. The data can be any of the following −
You have learnt about the various Excel features that you can use in a dashboard. Based on your requirements for the dashboard at hand, select any of the following Excel features for the dashboard components.
Selecting the dashboard components will help you to align to your approved dashboard mockup layout.
Identify the static and dynamic components and the components that are to be grouped for Slicers, if any.
Identify those parts of the dashboard that require immediate attention, such % Complete or the Current Status. You can use bigger font and a striking font and font color for these.
Decide on how much color you want to incorporate in your dashboard. This decision can be based on the audience for the dashboard. If the dashboard is for executives and/or managers, choose colors that impact the visualization of the results being displayed. You can add a dashboard background color to make the dashboard components pop. You can use the same color code for similar charts or related results. You can use conditional formatting too.
A careful selection of the parts of the dashboard for highlighting makes it effective.
This is the crucial and final step in creating your Excel dashboard. This step involves assembling your dashboard components that you can efficiently and effectively do by using Excel camera. You will learn how to use Excel camera in the next section.
Once the dashboard components are assembled, give it a final touch −
In the next few chapters, you will learn how to use these steps for creating a dashboard based on some examples. There is no universal rule or layout for a dashboard apart from some commonality. It all depends on your requirements. And your goal is to produce an effective dashboard.
Excel camera helps you in capturing snapshots from your worksheets and place them in a different worksheet. For example, you can capture a table with conditional formatting on a worksheet and place it on your dashboard. Whenever the data gets updated, the dashboard will get refreshed to display the changed data.
You can include the Excel camera as part of your Quick Access Bar as follows −
Excel Options dialog box appears.
Click on the Add » button. Camera appears in the right side list.
Click the OK button. The camera icon appears on the Quick Access Toolbar in your workbook.
You can use Excel camera as follows −
Select the range of cells to be captured.
Click on camera on the Quick Access Toolbar.
The range of cells appear with a dashed line border.
Click on the worksheet where you want to place the captured region. It can be your dashboard sheet.
Click at a position where you want to place it.
The captured region appears at that point.
Whenever you make changes to the original data, the changes will get reflected in the dashboard.
You can incorporate a Date or Date and Time Stamp on your dashboard to display when the data was last update. You can do this using the Excel functions TODAY () and NOW ().
To incorporate a Date Stamp, enter =TODAY () in the cell where you want to place the Date Stamp on your data worksheet.
This will display the current date whenever the workbook is updated.
Ensure that the cell where you entered the TODAY () function is formatted to the Date format that you want to display.
Capture the display with camera and place it on the dashboard.
The date on the dashboard will reflect the date when the workbook was last updated.
You can incorporate a Date and Timestamp on your dashboard in a similar way with the function NOW ().
Enter = NOW () in the cell where you want to place the Date and Time Stamp on your data worksheet.
The date and time stamp will get incorporated on the dashboard and will reflect the date and time when the workbook was last updated.
You need to test your dashboard to ensure that it displays the data accurately.
You might have to do some trial runs to ensure that your dashboard is as you wanted.
The next step is to get the dashboard evaluated by sample audience, especially those who approved your mockup dashboard. As they will be using the dashboard, they will undoubtedly have input as to its use and effectiveness. This feedback helps you to ensure that your dashboard is effective. Do not hesitate to ask for feedback.
Once you get the feedback, enhance it with required changes, if any. Your Excel dashboard is ready to use.
You need to make the Excel dashboard available to the intended audience. You can do it in several ways.
Mail the Excel dashboard workbook (You have to hide the worksheets other than the dashboard worksheet. You can also protect the workbook.).
Save the Excel dashboard workbook on a shared network drive.
Share the dashboard online.
If the Excel dashboard is static you can mail it but if it is dynamic or has interactive controls, then it should have a connection to the backend data and hence requires sharing online.
You can share an Excel dashboard online with any of the following options −
Microsoft OneDrive.
With your Windows Live account, you will get access to OneDrive where you can post and share documents.
New Microsoft Office Online.
Microsoft SharePoint.
You can also save the Excel workbook file as an Acrobat Reader file (.pdf) and post it to web. But, once again this option is only for the static dashboards.
To make an Excel dashboard effective, you need to do certain things and avoid some. Some tips for effective Excel dashboards are as follows −
Keep it simple.
A simple, easy to understand dashboard is much more effective than a jazzy dashboard. Remember that it is the data that needs emphasis.
According to Glenna Shaw, you need to strike a balance between making your dashboard attractive enough to hold your audience’s interest, but not so stylized that it obscures the information displayed.
Better avoid 3D effects, gradients, extra shapes and unnecessary gadgets.
If you can achieve emphatic display with conditional formatting or Sparklines, prefer using Tables to Charts.
Use Excel Conditional Formatting.
Use Excel Conditional Formatting that provides several options to automatically update based on the values in your table.
Select appropriate chart types.
Remember that there is no general rule for using a chart type. Sometimes conventional chart types like column chart, bar chart, doughnut chart, etc. convey the message emphatically more than the sophisticated charts that are coming into existence.
You can use Excel Recommend Charts command to initially evaluate the suitable chart types.
As you can change the chart type with a single Excel command − Change Chart Type, you can play around to visualize the display and choose the appropriate chart.
Use interactive controls.
Use interactive controls such as scroll bars, option (radio) buttons and check boxes that help the user easily and effectively visualize the different aspects of the data.
Use Excel Data Model for Big Data.
If you have large data sets from various data sources, you better use the Excel Data Model that can handle thousands of rows of data with memory optimization and can group data tables with relationships.
Choose appropriate colors.
Be careful in choosing the colors. Use the colors judicially to provide sufficient impact, but not override their purpose. Moreover, if the audience is likely to include color blind, restrain from using Red and Green colors. In such cases, though traffic light symbols sound effective for the data being displayed, they are not suitable for the dashboard. Use a gray scale instead.
Use Slicers.
Slicers are more effective than dropdown lists as they have a visual impact.
You can group charts, PivotTables, PivotCharts to use a common Slicer.
Group the Excel dashboard components together.
You can add visual interest to your dashboard by inserting a shape, such as a rectangle and placing your dashboard components that can be grouped on top of that shape. For example, if you are using a common Slicer, you can group all the dashboard components that share the Slicer.
Use Excel data hierarchies.
If your data has innate data hierarchies, define them in the Data Model and use them to interactively drill up and drill down the data.
Avoid crowded dashboard layout.
Remember that display of more information than that is necessary will overwhelm the audience and beat the focus on the actual purpose.
Do not include any data or chart in your dashboard, if you can do away with it.
This is an important checkpoint while testing your dashboard. Evaluate each dashboard component if is necessary and sufficient.
The dashboard components and the layout should support the single purpose of your dashboard.
There are several possible types of dashboards. There are no set of standards for dashboards, except for some do’s and don’ts. You can understand the purpose and use your imagination to select the components and layout for your dashboard. However, you need to be on the same page with the viewers of the dashboard and hence their preferences are to be taken care to make it effective. A dashboard can get modified as the time proceeds based on the context and changing needs.
As you have learnt in the earlier sections, the purpose of a dashboard is to effectively display the necessary and sufficient data with added visual impact as required by the potential audience. The layout of the dashboard and its components vary across the different viewers based on their preferences.
In this chapter, you will learn about a sample Executive dashboard. Once again, this dashboard can vary from company to company based on the requirements and preferences.
Executive dashboards normally focus on the business performance, revenue, profits, new customers, etc. Hence, Executive dashboards essentially display the KPIs. The visual impact necessary in this case would be to provide results at a glance, with not much detail as the executives will not normally have much time to dwell into the specifics unless it is absolutely necessary.
An executive can be a head of the company or head of a specific department in a large company. The head of a company can be a CEO (Chief Executive Officer) or a MD (Managing Director). The executive would be mostly interested at a brief of the operational KPIs of the departments and the performance of the company as a whole.
Following are some examples of Departments and their operational KPIs −
For the display of the KPIs, most of the executives still prefer Gauge charts to Bullet charts. Ensure the preferences before you start designing the dashboard.
An example of a simple Excel Executive dashboard can be as shown below.
The purpose of a Project Management dashboard is to provide the project execution status and the salient project features to be available at a snapshot. A project manager should use this portrayed data not only for project monitoring, but also for reporting to the top management and customer.
The key metrics in project management would be the following −
To obtain a project snapshot, the major components that would be useful would be the following −
An example Excel project management dashboard looks as shown below.
Sales management involves examining the data region wise and analyzing quarterly so as to get insights into the sales trend and sales forecasting. This would help in comparing the sales performance with the competitors, appraising the sales team, identifying potential strengths and weaknesses and planning for the future.
The Key Metrics involved in the Sales Management are the following −
To represent the above given Metrics in a dashboard, the following Excel features can be used −
A sample sales management dashboard will be as shown below −
Training Management would normally be interested to have an overview the money spent and training coverage, so as to compare with the competitors. In addition to this, the training feedback given by the trainees would help the management to decide on where to leverage the training resources.
The key metrics in the training management would be the following −
Budget vs. Expenses.
Training Time – Planned vs. Actual.
Training Coverage – No. of People Targeted vs. No. of People Actually trained. This can be expressed as a percentage.
Training Feedback – For each of the trainings conducted, the average feedback from the trainees, in the scale 1 – 5 (1 – Lowest, 5 – Highest).
You can choose the following Excel features as components for the dashboard, based on the above given metrics.
Clustered Bar chart for – Budget vs. Expenses.
Area Chart for – Planned vs. Actual time for training.
Thermometer chart for – Training Coverage – as a % actual as compared to target as 100%.
Clustered Column chart for – Training Feedback for the trainings conducted.
A sample Training Management Excel dashboard can be as shown below −
Service Management or Support or Help Desk involves receiving service tickets and providing resolution as soon as possible. Hence, a dashboard updated daily would help to enhance the service management and thus improve customer satisfaction.
Key Metrics for service management would be the following −
The Excel features that can be used to represent the above given Metrics as components of a dashboard would be the following −
Additionally, you can include the date that corresponds to the data on the dashboard, with the Excel function - TODAY ().
A sample service management dashboard can be as shown below −
Dashboard can literally be used to display any results that require attention. The examples that you had seen in the previous chapters are only a hand pick of various applications where dashboards are being used. Further, the dashboard components and dashboard layout for the same purpose also can vary based on the user preferences. Hence there are no standard dashboard formats.
In this chapter, you will have a brief on some more areas where dashboards are being used. You can use your own imagination for a dashboard based on the data you have and the purpose it has to serve.
You can have a dashboard displaying the data analysis results of the Olympics data. Following is a sample dashboard that is created from 35000+ rows of data using Excel Data Model and Excel Power View.
A sample tourism dashboard regarding the number of visitors to a specific site can be as shown below.
A hospital management dashboard is a form of executive dashboard with the level of detail as required by the specific manager. A sample used in a hospital is as given below.
A sample dashboard that is used in a restaurant is as shown below.
Sports arena is the most sought-after place for dashboards. Every sport will have a live dashboard displaying the required statistics for the game that is on. A sample dashboard is as shown below.
