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+)
- Power Bi (35+)
How to Create an Excel Dashboard?
A dashboard in excel is a glance of previously done calculations based on specific data. It shows the performance of the key areas or the matrices in the data.
In excel, dashboard is based on the set of data which is represented by some set of calculations. A dashboard can be viewed as charts or in tables. Before creating any excel dashboard we need to be clear about the structure of the dashboard, for example, which specific thing is to be represented where and what not.
To create an effective and illustrative dashboard in excel we need to keep a few things in mind. First, make two or more sheets in a workbook. In one sheet copy the raw data if it is from any other source or insert the raw data in it. In the other worksheets we can either create tables and calculations based on formulas and make it more visual we can use charts to represent the data.
Customizing the charts and tables helps a viewer to have a better look at the data represented by the dashboard.
Creating Dashboard in Excel Example #1
We have students data with the marks of their five subjects. We will create an interactive excel dashboard in which we can marks of the students and see if they are eligible to be awarded or not. First, we must be clear what we are trying to make, so let us have a look at the raw data.
In such a format, raw data will be pasted. The rules are if the total of the student marks is above 400 he will be awarded or else declared as fail. So the very simple dashboard should look like this,
K column will identify if the student is to be awarded or not.
- In J column Type, =G3
- Press Enter and drag it to the end of the data.
- Now in K3 type,
- Press Enter and drag it to the end of the data.
- This dashboard is based on the data provided but if we change the data, the dashboard changed too. For example, increase the marks of Vikas in English from 65 to 88 and see the result.
From Fail, the status was changed to awarded.
The above example was a very basic dashboard using just sum and If excel function.
Creating Dashboard in Excel Example #2
In this example we will create a complex excel dashboard, using many functions and charts and graphs. First, we need to have the data in excel. If the data is from any other source, we need to import it in excel. We will make two worksheets, one in which there will be raw data and in another, we will make our dashboard based on that raw data.
First, we will make tables based on the raw data provided. Then based on those tables we will make the charts and the dashboard.
The employees of the company have some baselines defining their performance and we will make the dashboard in the employee performance. Have a look below at the raw data,
The total performance score is calculated by summing each parameter by multiplying by its weightage. Then we will show the performance of the employee based on his performance score and we will also show the key parameters in which the employees need to work. We can create this excel dashboard by making tables and slicers but we will use charts as they are more illustrative.
- Calculate the employee performance score, in cell Q2, write the formula,
- Select the array from B3 to F3.
- After inserting a comma, select the array from J3 to N3.
Currently, the parameter score is zero so performance score is displayed as zero.
- Drag the formula to cell Q6.
Now enter the data to the Parameter.
Then the Performance score will Change as given below.
- Go to the dashboard worksheet, and in the Insert Section, under the charts category click on, any chart, in this case, we will choose a line chart.
- Right Click on the chart and click on select data.
- A dialog box pops up.
- In the Chart Data range box, go to the raw data sheet and select the performance score section.
- Click on OK and the chart is prepared in the dashboard sheet.
We can insert any more tables or charts on the basis of the data we calculated, and as soon as the data changes the results in dashboard changes too.
Explanation of the Dashboard in Excel
- Dashboards are a very effective form to represent data. It can give a simple status update to a finalized report for any work. In day to day lives, almost everybody uses dashboard at some point in their lives. They are very easy to create if we have a clear mindset of what we are trying to make and we analyze our data properly.
- A dashboard is a glance of previously done calculations based on specific data.
Things to Remember About Creating a Dashboard in Excel
There are a few things we need to remember while creating a dashboard in Excel:
- Have data setup in a worksheet. Analyze the data properly on what data ranges need to be visualized.
- We need to have a clear mindset about how the dashboard will look alike.
- Customize the charts and tables to have a better visual of the data.
This has been a guide to creating Dashboards in Excel. Here we discuss how to create a Dashboard in Excel along with practical examples and downloadable excel template. You may learn more about excel from the following articles –