Excel Functions Tutorials
- Excel Tips
- Excel vs Access
- Excel Rows vs Columns
- Apple Numbers vs Excel
- 3D Reference in Excel
- Absolute Reference in Excel
- Mixed References in Excel
- Excel Reference to Another Sheet
- Array Formulas in Excel
- Arrays in Excel VBA
- Auto Numbering in Excel
- AutoFit in Excel
- AutoCorrect in Excel
- AutoSave in Excel
- AutoRecover in Excel
- Bullet Points in Excel
- Break Links in Excel
- Barcode in Excel
- Change Case in Excel
- CAGR Formula in Excel
- Calculate Age in Excel
- Calculate Percentage in Excel Formula
- Cell Reference in Excel
- Checklist in Excel
- Circular Reference in Excel
- Column Sort in Excel
- Column Lock in Excel
- Move Columns in Excel
- Custom List in Excel
- Consolidate in Excel
- Combine Cells in Excel
- Compare Two Columns in Excel
- Compare and Match Columns in Excel
- Compound Interest Formula in Excel
- Convert Columns to Rows in Excel
- Convert Date to Text in Excel
- Convert Numbers to Text in Excel
- Convert Text to Numbers in Excel
- Convert Excel to CSV
- Count Characters in Excel
- Count Rows in Excel
- Count Unique Values in Excel
- Countif not Blank in Excel
- Create Templates in Excel
- Family Tree in Excel Template
- Custom Number Format in Excel
- Delete Row Shortcut in Excel
- Divide in Excel Formula
- Drop Down List in Excel
- Dynamic Tables in Excel
- Dashboard in Excel
- KPI Dashboard in Excel
- Date to Text in Excel
- Date Format in Excel
- Database in Excel
- Delta Symbol in Excel
- $ Symbol in Excel
- Excel Column to Number
- Edit Drop-Down List in Excel
- Equations in Excel
- Exponents in Excel
- Excel Extensions
- Excel Translate
- Excel Not Responding
- Excel Find and Replace
- Find and Select in Excel
- Excel Subtraction Formula
- Excel Formula for Grade
- Excel as Calculator
- Excel Formula Not Working (Updating)
- Excel Table Styles & Formats
- Excel vs Google Sheets
- External Links in Excel
- Excel Alternate Row Color
- Excel Worksheet Tab
- Extract Number from String Excel
- Evaluate Formula in Excel
- Find Duplicates in Excel
- Finding Links in Excel
- Filter Shortcut in Excel
- Formatting in Excel
- Format Numbers to Millions & Thousands in Excel
- Format Phone Numbers in Excel
- Formula Errors in Excel
- Fractions in Excel
- Frequency Distribution in Excel
- Group in Excel
- Group Worksheets in Excel
- Group Columns in Excel
- Hide Formula in Excel
- Hiding a Column in Excel
- Highlight Every Other Row in Excel
- Highlight Duplicates in Excel
- How to Create a Formula in Excel?
- How to Create an Excel Spreadsheet?
- How to Add Text in Excel Formula?
- How to Create Dashboard in Excel?
- How to Copy Sheet in Excel?
- How to Delete Pivot Table?
- How to Calculate Percentage Increase in Excel?
- How to Multiply in Excel Formula?
- How to Unhide Columns in Excel?
- Insert Date in Excel
- Insert Calendar in Excel
- Import Data into Excel
- Insert Comment in Excel
- Insert Hyperlinks in Excel
- Insert Multiple Rows in Excel
- Insert Row Shortcut in Excel
- Insert New Worksheet in Excel
- Insert (Embed) an Object in Excel
- Insert Image in Excel Cell
- Insert Page Break in Excel
- Line Breaks in Excel
- Linear Interpolation in Excel
- Leading Zeros in Excel
- Last Day of the Month in Excel
- Logical Operators in Excel
- Lookup Table in Excel
- Mortgage Calculator in Excel
- Moving Average in Excel
- Not Equal to in Excel
- Numbering in Excel
- Name Manager in Excel
- Page Numbers in Excel
- Page Break in Excel
- Personal Budget Template in Excel
- Project Management Template in Excel
- Percentage Difference in Excel (Increase / Decrease)
- Pivot Table Calculated Field & Formula
- Pivot Table Sort
- Pivot Table From Multiple Sheets
- Print Comments in Excel
- Print Excel Gridlines
- Print in Excel
- Print Preview in Excel
- Print Area in Excel
- Print Titles in Excel
- Print Labels From Excel
- Project Timeline in Excel
- Protect Sheet in Excel
- Ratio in Excel Formula
- Random Numbers in Excel
- Randomize List in Excel
- Refresh Pivot Table in Excel
- Relative References in Excel
- Remove Blank Rows in Excel
- Remove Duplicates in Excel
- Remove Duplicates from Excel Column
- Remove Hyperlinks in Excel
- Remove Space in Excel
- Remove Leading Spaces in Excel
- Remove Watermark in Excel
- Row Limit in Excel
- Rows and Columns in Excel
- Rows to Columns in Excel
- Row Header in Excel
- Search in Excel
- Search For Text in Excel
- Share an Excel Workbook
- Shortcut to Merge Cells in Excel
- Show Formula in Excel
- Split Cells in Excel
- Separate Text in Excel
- Strikethrough in Excel
- Strikethrough Text in Excel
- Sum by Color in Excel
- Subscript in Excel
- Superscript in Excel
- Themes in Excel
- Timesheet Calculator in Excel
- Top 20 Keyboard Shortcuts in Excel
- Unmerge Cells in Excel
- Uppercase in Excel
- Word Count in Excel
- Word Cloud in Excel
- Watermark in Excel
- Weighted Average in Excel
- Wildcard in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tools (98+)
- VBA (162+)
Dashboard in excel is mostly a single page in excel which illustrates all the developments and changes in the data, simply looking at the raw data one cannot identify all the areas of the changes and the data, but having a quick look at the dashboard will give user an apt overview of the data, dashboard contains charts tables and views in a single page for the data and it changes as the data changes.
Dashboard in Excel (Table of Contents)
What is a Dashboard in Excel?
The dashboard in Excel is an Enhanced form of Data Visualization. It provides insight & analysis which helps out in decision making. It helps out to plan & achieve business targets across the organization. Also, A data source for creating excel dashboards can be Spreadsheets, Text Files, Web Pages, Organizational Database.
Tools Used to Create Dashboard
- Excel tables, Charts, Pivot table & Charts, Slicers, timelines, Conditional formatting (Data bars, color scale, Icon sets), Named ranges, Data validation, Sparklines, Macros, Auto-shapes & dashboard widgets.
- Interactive Controls – Scrollbar, radio button, checkbox, Drop Down List.
- Excel formulas like SUMIF, OFFSET, COUNT, VLOOKUP, INDEX MATCH & other formulas.
Note: Prior to working on excel dashboards, you need to find and remove duplicates, delete leading, trailing or double spaces & remove blanks and errors in the raw data file
How to Create a Dashboard in Excel?
Some of the examples of creating Dashboards in Excel are as follows:
Example #1 – Comparative Excel Dashboard – Sales Performance Of Two Companies
In raw data. I have a two-pharma company with monthly & quarterly sales data. I need to present it & compare the performance of 2 pharma company sales on a month & quarterly basis in Comparative Excel Dashboard.
With the help of Data Bars option under Conditional formatting, we can create comparative performance excel Dashboard.
Let’s create a Comparative excel Dashboard in another sheet with the help of raw data.
Copy Mankind sales data range in the first column, Month in the second column & Ajantha pharma sales data range in the third column.
Select a whole data range and increase the row height & length with the help of alt + HOH key i.e. from 15 to 25, for a better appearance of colored data bars.
Now select the mankind sales data range from Jan to Dec, now go to conditional formatting in the home tab, under that select data bars, and in the data bars, select more data bar rules.
New formatting rule window appears, our sales data range is from 0 to 9000, therefore under the “edit the rule description”, select the type as a number, and enter maximum value i.e. 9000.
In the Bar appearance, select the color of your choice under color options, since the data is on the right-hand side, therefore under the bar direction, select from right to left.
If you don’t want the appearance of sales data on the colored data bars, then select or click on show bar only. Click ok. Now you can see colored data bars appear in each row of sales data on a monthly basis.
Similar steps are followed for Ajantha pharma sales data, only the bar direction, the option needs to be changed i.e. from left to right (Since the data is on the left-hand side)
Note: Simultaneously, these steps are applied for quarterly sales data also, only the maximum number option needs to be changed to 25000 under edit rule description with a different color bar.
Here in sales data, you can check out sales progress, the company has made on a monthly & quarterly basis
Apart from data bars, another type of below-mentioned comparison indicators can also be used based on the dashboard requirement.
Example #2 – Create Sales Performance Dashboard Using Pivotchart & Slicer Object
With the help of PivotChart and Slicer objects, let’s create an excel dashboard where you can easily summarize sales data for each representative.
Below mentioned data contains a compilation of sales information by date, salesperson, and region.
Step 1: Create Table Object
Initially, Data set is converted to a table object, it can be done by, clicking inside the data set then click on the Insert tab then, Select Table.
A create table popup appears, where it shows data range & headers, and click OK.
Once the table object is created, it appears as below mentioned.
Step 2: Pivot Table
In the Excel dashboard, we need to summarize sales data for each representative by region wise & quarterly. Therefore, we need to create the two PivotTables.
- To create a pivot table, for the salesperson by region wise.
In the Tables object, click inside the data set, click on the Insert tab, then select the Pivot table and click ok. PivotTable Fields pane appears in another sheet.
In the PivotTable Fields pane, drag sales person to the Rows section, Region to the Columns section, and sales to the Values section.
A Pivot Table is created for Salesperson by Region wise.
Similarly, create second PivotTable in the same way
- To create a pivot table, for the salesperson by date wise or quarterly.
Drag date to the Rows section, sales person to the Columns section & sales to the Values section
A Pivot Table is created for Salesperson by Date wise.
Here We want to summarize data on a quarterly basis, their dates need to be grouped as “Quarter”. To do that, right-click on any cell in the Row Labels column and choose Group.
Grouping tab appears, with the start date & end date, In the BY list, unselect Months (default value) and others, Now only select Quarters, it appears in blue color after selection, then click OK.
After grouping to quarter, data appears as below mentioned.
Step 3: Pivot Chart
Here, we need to base a PivotChart on each of the created pivot tables in both the sheets.
- Go to the Pivot Table that is created for “Sales by Region” sheet, click inside the PivotTable, under the Analyze tab in a home, select PivotChart.
Insert chart popup window appears, in that Select Bar, under that select Clustered Bar chart.
Pivot chart appears for “Sales by Region”.
Here you can hide the region, sales person & sum of sales in pivot chart by right click, and select hide “legend field buttons on the chart” so that those three fields will not appear on a chart.
- Similarly, pivot chart is applied in “Sales by Quarter” sheet also, where you can choose a Pie chart for a quarterly sales data. Here also you can hide those 3 fields.
Step 4: Slicers
To check the performance of individual salesperson by region wise & quarterly data, Slicers will help you out
- Go to “Sales by Region” sheet, under the analyze tab. Click Insert Slicer in the Filter group.
Insert slicers window appears, in that select Region field & click OK.
Region wise Slicer will appear.
Similarly, you can add slicers in “Sales by Quarter” sheet also, under the analyze tab. Click Insert Slicer in the Filter group. Insert slicers window appears, in that select salesperson. click OK.
Sales_Person Slicer will appear
Step 5: Dashboard
Create a new sheet, with the name “Sales_DashBoard”, Uncheck or remove gridlines in that sheet in the view option of the home tab for a better appearance of excel DASHBOARD.
Copy these objects i.e. pivot chart & slicer from both the sheet to “Sales_DashBoard” sheet. Now you can analyze sales data by region wise & by individual sales person.
If you want the sales values to appear on data bars or pie chart. You can add it by click on data bars or pie chart and select “Add data labels”.
Types of DashBoards in Excel
Dashboards in excel can be categorized based on their usage & utility as follows –
- Strategic Dashboards – To track KPI (key performance indicators). Used in organizations for key decision making, for business opportunities, focus on future company growth & forecasting e.g. (monthly, quarterly and annual sales)
- Analytical Dashboards – Helps in or to identify current or future trends.
- Operational Dashboards – What is happening in an organization? Monitor company operations, activities and events.
- Informational Dashboards – It is based on facts, figures, and statistics. E.g. Player profile & performance dashboard, flights arrival/departure information dashboard at airports.
Things to Remember
- Key Metrics for monitoring company Sales, finance, productivity and other criteria’s
- Excel Dashboard helps out to set up organizational goals
- With the help of an Excel dashboard, you can identify negative trends & correct it immediately
- It helps out in a measurement of Efficiency of an employee based on work productivity & performance
This has been a guide to Dashboard in Excel. Here we discuss how to Create a Dashboard in Excel using Conditional Formatting, PivotCharts and Slicers along with practical examples and downloadable excel template. You may learn more about excel from the following articles –
- How to Create a Database in Excel?
- Conditional Format for Blank Cells
- How to use Excel Formula for Grade Calculation?
- VLOOKUP from Another Sheet
- Sorting a Pivot Table in Excel
- Calculated Pivot Tables of Field and Formula
- Applying Conditional Formatting in Pivot Table
- Apply Filters in Excel
- GetPivotData Excel
- Using Wrap Text in Excel