This tutorial is suitable for individuals who generate reports for complex data processes using Ms. Excel charts, Gantt charts, Pivot tables among other tools.
What is excel dashboard?
A dashboard tracks metrics, data points, and KPIs in a single central place. It provides a quick visual status update of your projects and important details in one place. Using a dashboard, you can manage and communicate more with your team members.
Sometimes dashboards can act as a progress report for a particular business process. It enables the manager to monitor the performance of each individual in a department and know how the organization is performing.
The Key Performance Indicator (KPI) is a tool used to measure how effectively the company achieves its key business objectives.
Companies use KPI to evaluate their performance in achieving the set targets. They demonstrate how individuals, departments, and organizations are achieving the business objectives. They are evaluated after a specific set time parameter and the results are compared with past performance metrics.
Why use dashboards
- Provides you with a high-level view of your work enabling you to make informed decisions.
- Helps you keep up to date data on organizational performance
- Helps in simplifying complex data processes.
- Provides at-a-glance view of the organization's current status or performance in real-time.
- Provides you with a detailed report on new trends
-
Helps you align organizational goals and strategies
A dashboards cab be a chart, table, or numbers. You can create a financial dashboard, a marketing dashboard or project dashboard making them suitable for any application.
Factors to consider before creating a dashboard
Before creating a dashboard, ask yourself the following questions;
- Why do you need the dashboard? You need to know the reasons for creating the dashboard in order to come up with the right design and the kind of data needed.
- Do you need to track a certain KPIs with the dashboard? You need data that adds value to your organization. Know the Key Performance Indicator (KPI) and build your dashboard around this metric.
- Where to get the data from? Whether to automatically sync data from the company's database or manually import data.
- Who needs to use the dashboard? You need to know the people who will rely on dashboard data, how is their intelligent level and how much time do they have accessing the dashboard.
- How often do you need to update the dashboard? Does your dashboard need to be updated in real-time or after a week? Your dashboard design is directly proportional to the frequency of updates done
- What is your dashboard format? Based on the needs of your dashboard, you can choose to create a static or dynamic dashboard.
How to create dashboards
1. Create your table. You can import data, copy-paste or enter manually.
2. After adding the data, analyze it to find out which part of the data needs to be highlighted. What is the purpose of the dashboard?
Once you state your purpose, the following tools can be used to analyze data;
- Charts
- Gantt chart
- Pivot tables
- Excel formulas
- Excel tables
- Macros
- Data Validation
- Conditional formatting etc.
Creating dashboard using charts
1. Create the following table
2. Open a new sheet and rename it "Dashboard".
3. Click Insert menu. In the charts section tab, click insert columns chart to create a column chart.
4. After inserting the chart, right-click on the chart area and choose Select Data
5. A dialog box opens to allow you to select the chart data range
6. Click ok.
7. Double click on the chart title to add your project title.
Example 2: Creating dashboard using Gantt chart
Create the following table on a new sheet. Rename the sheet as "Project Data".
1. Open a new sheet to insert the dashboard. Rename it to "Gantt chart"
2. Click Insert menu. In the Charts section tab, click on the bar chart then choose any of the available options.
3. Right-click on the chart and click on Select Data
4. Add the Chart data range: Click on the Project Data sheet and select the cells with the project name and start date.
5. On legend entries, click on Add tab
6. On the series name, click on Project datasheet then select cell D2. On series Value, select the range of cells for the duration of each project.
7. Click OK.
8. If the X-axis is not well labeled, you can adjust the horizontal category axis in your project datasheet. You should always select what you want to be displayed on the X-axis. Then click OK to display the Gantt chart.
9. Add chart title by clicking on Design Menu and on chart layouts tab, click Add Chart Element the select chart title.
10. Type your title.
