Are you looking to create a well-designed, sleek, and interactive business dashboard in Excel? Well, your search stops here!
This article is a comprehensive how-to guide to create beautiful and responsive dashboards for your organization.
Also read: Creating Fully Automated User Forms in Excel [No VBA] PART 1
What are business dashboards?
Dashboards are organizational tools that help a business or a company to track KPIs (key performance indicators), crucial data metrics to design actionable business strategies.
Dashboards display historical organizational data, employee performance reports, real-time reports, geographical performance reports, and much more. These reports constructed in Excel are dynamic and provide real-time data to the user.
Steps to create a business dashboard in Excel
Let’s begin this interesting tutorial on creating interactive business dashboards.
In this ultimate PART 1 guide to creating a business dashboard, there’s a lot we’re going to cover.
We will create metric charts from a database, create visual key performance reports, trendlines, create a map, bar, doughnut charts, and more using pivot tables, pivot charts, maps, and more.
1. Create a database
First, you would require to have a set of data in a sheet that you wish to present on the dashboard.
Here, we have a small dataset of 80 entries below.
Recommended read: How to Create a Database in Excel?
2. Create pivot tables and charts for metrics
The next step is to create a pivot table for distinct metrics like sales by employee, sales by company, or region, sales trendline, and more based on the data present in the database.
Most reports are created using pivot tables so the basic steps are more or less the same for all the charts.
The tables will be represented as charts on the dashboard. Let’s begin creating a pivot table.
If you don’t know to create a pivot table, here’s a detailed guide!
Also read: Create a Pivot Table from Scratch – The Complete Beginners’ Guide
3. Creating a sales trendline chart
To create a pivot table for sales trendline chart.
- Go to the Insert tab.
- Click on PivotTable.
- Select the entire database. Press and hold the Shift key and navigate using arrow keys to select the range easily.
- Check Add this data to the Data Model. It will help save time and avoid selecting the range repeatedly for a new pivot table.
- Choose New Worksheet.
- Rename the new sheet to Sales Trendline.
- Click OK.
A windowpane opens on the right side of the Sales Trendline worksheet named PivotTable Fields with options.
- Drag and drop the Months field to the Row area. Drag Months when you have only one year’s data. Drag Date when you have more than one year’s data.
- Drag Revenue to the Values area. The table is showing the sum of revenues in a month.
- Click PivotChart in the Insert tab.
- Create a Line with Markers chart in the Line tab and click OK.
Yay! The Sales Trendline chart is ready!
4. Creating sales by region chart (Conditional)
If you have geographical data in your database, then this part is for you! You can skip this part if you don’t have any geographical data in your database.
Create a Sales by Region chart in a new sheet. Follow the steps to create a sale by region pivot chart in Excel. The regions in this database indicate the region where a particular salesperson works in.
To create a pivot table for sales by region chart.
- Go to the Insert tab.
- Click on PivotTable.
- Choose Use this workbook’s Data Model.
- Choose New Worksheet.
- Rename the new sheet to Sales by Region.
- Click OK.
To create the sales by region chart, follow the steps below.
- Drag Region to the column area.
- Drag Revenue to Values area.
You can’t create a map chart from a pivot table in Excel. So, you have to copy the pivot table and paste it into a different area in the sheet and create a map chart from it.
- Make sure to exclude the Column Labels and Grand Total sections of the pivot table from the selection like in the image above.
- Copy CTRL+C and paste CTRL+V the selection below the pivot table.
Now, create a reference of all values in the copied table to the pivot table like this.
In the video:
- Click on a value in the table.
- Press = and click on the same value in the pivot table.
- Hit ENTER.
- You can rename “Sum of Revenue” to “Revenue” in the table.
You can create a map chart for this table now. Follow the steps below to continue.
- Click on the table and go to the Insert tab.
- Open Maps.
- Click Filled Map.
You can see that a stunning map chart of the data gets created!
5. Create sales by employee chart
Now, you know the drill.
Create a pivot table first and follow the steps below.
- Drag the Salespersons fields to the Column area.
- Drag Date to the Row area.
- Drag Revenue to the Values area.
Now create a pivot chart for this data.
- Go to the PivotTable Analyze tab.
- Click PivotChart.
- Click OK to create a Clustered Column chart from the Column tab.
Don’t worry about the appearance of the chart, because we will discuss in detail designing your dashboard in the further parts of this tutorial.
6. Create an item share chart
This chart will represent the total sales per item in your company. Create a pivot table to create this chart and follow the steps below.
- Once the pivot table is created, drag the Item field to the Rows area.
- Drag Revenue to Values.
- Create a pivot chart by clicking PivotChart in the PivotTable Analyze tab.
- Click on the Pie tab, and click on the Doughnut chart.
- Click OK.
You are done with creating the Item Share chart successfully!
7. Create a customer revenue chart
This chart will represent the revenue earned from each customer company. This one is the last chart we will create for the business dashboard. If you have more data metrics, you can create several charts as you like.
Create a pivot table for this chart and follow the steps below.
- Drag Company/Customer Name field to the Row area.
- Drag Revenue to the Values area.
- Now, arrange the pivot table in ascending order by pulling down on Row Labels.
Now, create a pivot chart for this table.
- Create a pivot chart by clicking PivotChart in the PivotTable Analyze tab.
- Click on the Bar tab, and choose Clustered Bar chart.
- Click OK.
The Customer Revenue chart gets created successfully!
We have completed creating the necessary data charts for the business dashboard.
The next step is to create a stunning and interactive dashboard using these data charts. Continue to PART 2 of the guides below to get started!
Create a Sleek and Interactive Business Dashboard in Excel [PART 2]
Conclusion
This article is a comprehensive how-to guide to create beautiful and responsive business dashboards for your organization.
In Part 1, we learned to create metric charts from a database, visual key performance reports, trendlines, create the map, bar, doughnut charts, and more using pivot tables, pivot charts, maps, etc.
Part 2 is the most exciting part of this guide as we learn to create a stunning, sleek, and interactive dashboard.