DASHBOARD Making Tutorial:
Dashboard reports allow user to get high-level overview of the business and help them make quick decisions. Dashboards are often called as Management Information System (MIS), which provides information that organizations require to manage themselves efficiently and effectively.
Excel dashboards and executive reports are powerful, fairly easy to design and a great way to improve your Excel and data visualization skills. Because of its flexibility, you can virtually design any dashboard in Excel exactly the way you, or the users, imagined.
To Define KPIs (Key performance Indicator), Customer Service Dashboards or Project Management
Once you know what will the Excel dashboard be used for and what kind of metrics users expect, there are three major areas that you must address when creating it:
• How to bring data into your spreadsheets;
• How to manage the data and link it to the dashboard objects, like charts and tables;
• How to design the dashboard report.
Dashboard reports give valuable insights into the key performance indicators of the business, and perhaps most importantly; they’re interactive, which means the reader can filter and change views.
The good news is that creating Excel dashboard reports is easily done without the need for any other additional software.
Steps to Create Dashboard
1. Researching Your Dashboard
* Find out the underlying reason for the dashboard request.
* Are there specific KPI’s they want to see.
* Where will the data come from
* How often will it be updated
* Who will receive the report and in what format
2. Draw your Excel Dashboard ( Draw the Prototype on Paper)
3. Setting Up Your Excel Dashboard File
* Raw data (you might need more than one of these)
* Analysis (you might need more than one of these)
* Dashboard
4. Analysing your Data
Get Your Tools Ready with help of Excel formula’s and Funcations key ones are:
* SUMPRODUCT, SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS, COUNTIF & COUNTIFS
* COUNT, COUNTA, MIN, MAX, SMALL, LARGE, RANK
* Database Functions like DSUM, DAVERAGE, DMAX etc.
* VLOOKUP, HLOOKUP, INDEX and MATCH
* IF, Nested IF’s, IFERROR, OR and AND
* OFFSET, INDIRECT, CHOOSE
* GETPIVOTDATA
Other Tools in your Excel Toolbox are:
* PivotTables
* Excel Tables
* Data Validation
* Conditional Formatting
* Form Controls
* Shapes
* Named Ranges
5. Start Crunching the Data
6. Building Your Excel Dashboard