What is a Dashboard in Excel?
The dashboard in excel is an enhanced visualization tool that provides an overview of the crucial metrics and data points of a business. By converting raw data into meaningful information, a dashboard eases the process of decision-making and data analysis.
For example, a department dashboard consists of the following information:
- Financial coverage includes revenues, expenses, profits, operating expenses, and so on.
- Non-financial coverage includes staff turnover, recruitment procedure, quality of new hires, training mechanisms, and so on.
The key metrics incorporated in an excel dashboard can relate to finance, marketing, operations, human resources, banking, and other areas of an organization. With a dashboard catering to such varied fields, the end-users also vary accordingly.
The excel dashboards assist the organization in setting new goals and revising the existing ones based on past performance and the current market trends. Since the negative trends can be identified, quick corrective measures can be implemented.
In addition, the efficiency of employees, teams, and departments can also be assessed with dashboards.
The data source for creating an Excel dashboard can be a spreadsheet, text file, business report, web page, and so on. A dashboard can be static or dynamic depending on the requirement.
Types of DashBoards in Excel
The excel dashboards are categorized as follows:
- Strategic Dashboards in Excel–They track the relevant KPIs and forecast performance. They also help in attaining the targeted growth numbers. For instance, a strategic dashboard displays the monthly, quarterly, and annual sales figures of an organization.
- Analytical Dashboards–They help in identifying the current and future market trends. Based on these projections, it becomes easier to make decisions.
- Operational Dashboards–They monitor the operations, activities, and events taking place within an organization.
- Informational Dashboards–They are based on facts, figures, and statistics. For instance, an informational dashboard displays an overview of a player’s profile and performance, the details of a flight’s arrival and departure etc.
How to Create a Dashboard in Excel?
Let us go through a few examples to understand the creation of a dashboard in Excel.
Example #1–Comparative Dashboard
The following tables show the monthly and quarterly sales (in $) of two pharmaceutical companies–“Ajantha” and “Mankind.”
We want to compare the performance of the two companies with the help of a comparative excel dashboard. The purpose is to examine the progress made by both the companies on the revenue front.
The steps to create a dashboard in excel are listed as follows:
- In column A, enter the sales of “Mankind,” followed by the corresponding month in column B and the sales of “Ajantha” in column C.
- Select the whole data and create colored data barsData BarsData bars are a type of conditional formatting option in Excel that are used to highlight cells or data ranges in a worksheet based on certain criteria.. For this, increase the row height from 15 to 25, as shown in the following image.
To open the “row height” box, press the excel shortcut key “Alt+HOH” one by one.
- Select the sales data range of “Mankind.” In the Home tab, click the conditional formatting drop-down. Select “data bars” and click “more rules.”
- The “new formatting rule” window appears. In “edit the rule description,” select the “type” as “number” under both “minimum” and “maximum.”
In “value,” enter 0 and 9000 under “minimum” and “maximum” respectively.
- In “bar appearance,” select the required color in the “color” option. In “bar direction,” select “right-to-left,” as shown in the following image.
- Click “Ok.” In case you do not want numbers to appear with the colored data bars, select “show bar only” under “edit the rule description.”
The colored data bars appear in each row of column A, as shown in the following image.
- Likewise, create the same colored bars for the company “Ajantha.” In “bar direction,” select “left-to-right.”
The colored bars appear in each row of column C, as shown in the following image.
- Similarly, create colored bars for the quarterly sales data of the two companies as well. In “edit the rule description,” enter 25000 under “maximum.” Select a different color in the “color” option under “bar appearance.”
The colored bars appear in each row of columns F and H, as shown in the following image.
Hence, with the help of the colored bars, the user can glance through the monthly and quarterly sales figures of both the companies.
In addition to the colored bars, the following comparison indicators can also be used in a dashboard depending on user requirements.
Example #2–Performance Analyzer Excel Dashboard
The following table shows the region-wise sales revenue (in $) generated by the different sales representatives of an organization. It also displays the corresponding dates of making these sales.
We want to create an Excel dashboard with the help of a PivotChart and slicer. The purpose is to glance through a summary of the progress made by each salesperson.
The steps to create a performance analyzer dashboard in excel are listed as follows:
Step 1: Create a table object
a. Convert the existing data set into a table object. For this, perform the following actions in the mentioned sequence:
- Click anywhere within the data set.
- In the Insert tab, select “table.”
The same is shown in the following image.
b. The create table popup appears. It shows the table range and the checkbox for headers, as shown in the following image. Click “Ok.”
c. The table appears, as shown in the following image.
Step 2: Create a Pivot Table
To summarize the progress made by each representative, we want to organize the sales data by region and quarter. For this, we need to create two PivotTables.
a. Create a region-wise PivotTable for the different sales representatives
Perform the following actions in the mentioned sequence:
- Click anywhere within the table.
- In the Insert tab, select “PivotTable.”
- In the “create PivotTable” window, click “Ok.”
The PivotTable fields pane appears in another sheet.
Perform the following actions in the PivotTable fields pane:
- Drag the “salesperson” tab to the “rows” section.
- Drag the “region” tab to the “columns” section.
- Drag the “sales” tab to the “values” section.
b. The region-wise PivotTable for the different sales representatives appears, as shown in the following image.
c. Create a date-wise PivotTable for the different sales representatives
Likewise, create the second PivotTable. Perform the following actions in the PivotTable fields pane:
- Drag the “date” tab to the “rows” section.
- Drag the “salesperson” tab to the “columns” section.
- Drag the “sales” tab to the “values” section.
d. The date-wise PivotTable for the different sales representatives appears, as shown in the following image.
e. Group the dates on a quarterly basis. For this, right-click any cell in the column “row label” (column A) and select “group.”
This is done to view the revenue generated by every representative for all the quarters.
f. The “grouping” window appears with the start date and the end date. Under “by,” deselect “months” (default value) and select “quarters.” Click “Ok.”
g. The quarterly sales data for each representative appears, as shown in the following image.
Step 3: Create a PivotChart
The PivotChartPivotChartIn Excel, a pivot chart is a built-in feature that allows you to summarize selected rows and columns of data in a spreadsheet. It is a visual representation of a pivot table that helps in the summarization and analysis of datasets, patterns, and trends. should be based on the PivotTables created in the preceding step (step 2).
a. Create a PivotChart for the first PivotTable (created in step 2a), which shows the sales data by region. Click inside this PivotTable. In the Analyze tab, click “PivotChart.”
b. The “insert chart” popup window appears, as shown in the following image. From the “bar” option, select “clustered bar chartClustered Bar ChartA clustered bar chart represents data virtually in horizontal bars in series, similar to clustered column charts. These charts are easier to make. Still, they are visually complex..”
c. The PivotChart showing the region-wise sales for the different representatives appears.
d. To hide the label “region” of the legend, right-click on it and select “hide legend field buttons on chart.”
To hide the labels “sum of sales” and “sales_person,” right-click on either of these and select “hide all field buttons on chart.”
e. All the labels of the chart disappear, as shown in the following image.
f. Likewise, create a PivotChart for the second PivotTable (created in step 2c), which shows the sales data by quarters. In the “insert chart” window, select a pie chart. Click “Ok.”
g. The Excel Pie ChartExcel Pie ChartMaking a pie chart in excel can help you with the pictorial representation of your data and simplifies the analysis process. There are multiple kinds of pie chart options available on excel to serve the varying user needs. showing the quarterly sales data for the representative Blake appears, as shown in the following image. We have hidden the labels.
Step 4: Add slicers
Slicers can be created for the different regions and the various sales representatives. This helps sort the sales by region. It also allows viewing the quarterly performance of the individual salesperson.
a. In the first PivotChart (created in step 3a), click “insert slicer” from the “filter” group of the Analyze tab.
b. The “insert slicers” window appears, as shown in the following image. Select the option “region” and click “Ok.”
c. The slicer appears, displaying the names of the five regions. Hence, the performance of every salesperson for a particular region can be analyzed.
d. Similarly, add slicers to the second PivotChart (created in step 3f). In the “insert slicers” window, select the option “salesperson” and click “Ok.”
e. The slicer appears, displaying the names of the different sales representatives. Hence, the performance of every salesperson for the four quarters can be examined.
Step 5: Create a Dashboard
a. Create a new sheet with the name “sales_dashboard.” In this sheet, remove the gridlines by deselecting “gridlines” under the View tab. This enhances the appearance of the dashboard.
b. Copy the PivotCharts (created in step 3) and the slicers (created in step 4) to the “sales_dashboard” sheet.
The user can glance through the region-wise sales data. Moreover, the quarterly progress made by every sales representative can also be analyzed.
c. For viewing the sales figures on the data bar or the pie chart, right-click the same and select “add data labels.”
d. The sales figures appear on the pie chart, as shown in the following image. The chart shows the quarterly sales numbers of the representative Blake.
The Tools Used to Create an Excel Dashboard
The tools used in the creation of an Excel dashboard are listed as follows:
- Visualization elements–This includes tablesTablesIn excel, tables are a range with data in rows and columns, and they expand when new data is inserted in the range in any new row or column in the table. To use a table, click on the table and select the data range., charts, PivotTables, PivotCharts, slicers, timelines, conditional formatting (data bars, color scale, icon sets), sparklinesSparklinesSparklines in Excel are similar to a chart within a cell. They are tiny visual representations of the data's trend, whether it is increasing or decreasing, auto-shapes, and widgets.
- Interactive controls–This includes a scroll bar, radio buttonRadio ButtonIn Excel, radio buttons or options buttons record a user's input. They can be found in the developer's tab's insert section. , checkbox, and drop-down list.
- Excel formulas–This includes SUMIFSUMIFThe SUMIF Function is a conditional sum function that calculates the sum of given numbers based on a condition. It only uses one condition, and the syntax for using this function is =SUMIF (Range, Criteria, Sum range)., COUNT, VLOOKUP, INDEX MATCH, etc.
- Other tools–This includes named rangesNamed RangesName range in Excel is a name given to a range for the future reference. To name a range, first select the range of data and then insert a table to the range, then put a name to the range from the name box on the left-hand side of the window., data validationData ValidationData validation in Excel is used to limit a user's inputs to specified cells or input ranges by offering pre-defined inputs for selection to avoid multiple redundant data inputs., and macros.
Purpose of Creating a Dashboard
Dashboards are used by several industries for varied purposes. The objectives of creating a dashboard are listed as follows:
- To structure business information and create a consolidated data summary
- To plan the future course of action of a business
- To measure the key performance indicators (KPIs) that help evaluate the overall performance of an organization
- To study the effectiveness of various business processes and analyze the forecasted results against actual figures
- To improve business productivity
The Considerations While Creating a Dashboard
An excel dashboard should be created keeping in mind the following aspects:
- The purpose of creating a dashboard
- The intended audience for whom the dashboard is being created
- The relevant metrics to be included based on which decisions will be taken
- The data source to be populated in a dashboard
- The task of renewing dashboard information that can be periodic or as and when required
The Cautions While Creating an Excel Dashboard
The cautions to be observed while creating an excel dashboard are listed as follows:
- Ensure that the data file is appropriately structured by removing duplicates, blanks, leading and trailing spaces, and errors.
- Insert only the relevant information in a dashboard so that it is easy to interpret and not overcrowded.
- Simplify a complicated dashboard by creating a user guide or an instruction manual that assists in navigation.
Frequently Asked Questions
A dashboard in excel is a tool that displays the key metrics of an organization in one place. These metrics may relate to finance, human resources, operations, and so on. The dashboard helps in glancing through, analyzing, and making decisions on the crucial business information.
Excel Dashboards are made up of graphical content like tables, charts, PivotTables, widgets, and so on. Since a dashboard displays only the relevant data, its overview returns quick solutions.
Prior to creating a dashboard, it is essential to study the goal which it intends to meet. Based on the objective, the metrics to be included in the dashboard as graphs and numbers can be selected.
The dashboard must be easy-to-understand, meaningful, and user-friendly.
An Excel dashboard is used for the following reasons:
• It helps transform raw data into useful information, thereby making it easier to reach solutions.
• It facilitates instant and calculated decisions to be taken. Since such decisions are backed by relevant data and careful analysis, their accuracy tends to be high.
• It replaces the traditional system of creating detailed reports. Previously, such reports had to be consolidated, analyzed, and interpreted by the management before their practical application.
• It provides quick visibility of the entire situation, thereby helping managers make adjustments to an existing process or a project. Such adjustments act as a response to the changing business environment.
A KPI excel dashboard displays the key performance indicators (KPIs) that are responsible for the success of an organization. Tracking these critical metrics helps the management in setting and achieving organizational objectives.
A KPI excel dashboardKPI Excel DashboardIn Excel, KPI dashboard is a single, multiple charts panel view. It is very important to analyze an organization based on their Key Performance Indicators (KPI). The dashboard projects the crux of indicators at one place. is essential from a strategic and an operational perspective. This is explained as follows:
• From a strategic viewpoint, a KPI dashboard removes obstacles that impact the achievement of long-term goals.
• From an operational viewpoint, a KPI dashboard fixes the problems that impact the day-to-day processes of an organization.
For example, a KPI dashboard of a car manufacturer includes current market share, profit forecast, cost per unit, customer retention ratio, cost of customer acquisition, and so on.
A KPI dashboard should be simple, easy to navigate, and focused.
This has been a guide to the dashboard in Excel. Here we discuss how to create a dashboard in Excel along with practical examples and a downloadable Excel template. You may learn more about Excel from the following articles–