How to Create a Stunning Excel Dashboard in 8 Easy Steps

Excel dashboards are powerful and interactive data visualization tools that can help you present important information concisely and engagingly. With Excel dashboards, you can track performance metrics, identify trends, analyze data, and make decisions based on the insights you gain.

However, creating a great Excel dashboard is not as easy as inserting multiple charts and tables into a worksheet. You need to plan, design, and optimize your dashboard to fit your goals and audience. You also need to make sure that your dashboard is easy to understand, navigate, and update.

In this article, Bardimin will show you how to create a stunning Excel dashboard in 8 easy steps. Bardimin will use an example of a sales dashboard that displays monthly sales data from a company. You can follow these steps to create your dashboard with your data.

Step 1: Define Your Dashboard Goals and Audience

The first step in creating a stunning Excel dashboard is defining the purpose and audience of your dashboard. You need to answer the following questions:

• What is the main purpose of your dashboard? What do you want to achieve with your dashboard?
• Who is the primary audience of your dashboard? Who will see and use your dashboard?
• What are the main questions you want answered with your dashboard? What do you want to convey to your audience?
• What are the key metrics you want to track with your dashboard? What do you want to measure and monitor with your dashboard?

By answering these questions, you can determine the scope, content, and layout of your dashboard. You can also customize your dashboard to the needs and preferences of your audience.

Example: In the case of a sales dashboard, the primary purpose of the dashboard is to summarize the company’s sales performance. The dashboard’s primary audience is sales managers and company executives. The main questions you want answering with dashboards are:

• What is the company’s monthly sales trend?
• What are the company’s best and worst-selling products, categories, and territories?
• How does the company’s sales performance compare to the target and the previous year?
• What are the factors affecting the sales of the company?

The main metrics you want to track with dashboards are:

• Total sales
• Sales per product
• Sales per category
• Sales per region
• Target achievement percentage
• Annual sales growth

Step 2: Collect and Organize Your Data

The second step in creating a stunning Excel dashboard is collecting and organizing your data. You need to ensure that your data is complete, accurate, and relevant to your dashboard’s goals and audience. You also need to make sure that your data is well-structured and easily accessible to Excel.

You can collect your data from a variety of sources, such as databases, text files, CSV files, Excel files, or web applications. You can import your data into Excel by using the Get & Transform Data (Power Query) or Data Connection features. You can also manually enter your data into an Excel worksheet, but this is not recommended as it is error-prone and inefficient.

Once you’ve collected your data, you need to organize it in a way that makes it easy for you to analyze and visualize it. You need to clean your data from duplicates, errors, missing values, or inconsistencies. You also need to normalize your data by using appropriate formatting, such as dates, numbers, or text. Besides, you need to convert your data into an Excel table by using the Format as Table feature. Using Excel tables, you can take advantage of features such as filters, sorting, data validation, and structured references.

Example: In the case of a sales dashboard, the company’s monthly sales data is stored in a separate Excel file for each month. The data has the following columns:

• Date
• Product
• Category
• Region
• Price
• Quantity
• Sales
• Target
• Previous Year

To collect and organize the data, the steps that can be taken are:

• Import all Excel files into an Excel worksheet by using the Get & Transform Data (Power Query) feature.
• Combine all Excel files into one table by using the Append Queries feature.
• Clean data from duplicates, errors, missing values, or inconsistencies by using features Remove Rows, Replace Values, Fill, or others.
• Normalize data by using appropriate formatting, such as dates, numbers, or text by using the Change Type feature.
• Convert the data into an Excel table by using the Format as Table feature and naming the table as SalesData.

Step 3: Create Your Data Model

The third step in creating a stunning Excel dashboard is to model your data. A data model is a collection of tables, columns, relationships, and measurements used to analyze and visualize your data. By creating a data model, you can correlate your data from multiple sources, make complex measurements, and group your data based on different attributes.

You can model your data by using the Data Model or Power Pivot features in Excel. You can add tables, columns, relationships, and measurements to your data model by using the Add to Data Model, Create Relationship, or New Measure features. You can also use the Power Pivot feature to manage and modify your data model more easily.

Example: In the case of a sales dashboard, the data model consists of one main table, namely SalesData, which contains the company’s monthly sales data. The data model also has several measurements that are used to calculate sales metrics, such as:

• Total Sales = SUM(SalesData[Sales])
• Sales per Product = SUMX(VALUES(SalesData[Product]), [Total Sales])
• Sales per Category = SUMX(VALUES(SalesData[Category]), [Total Sales])
• Sales per Region = SUMX(VALUES(SalesData[Region]), [Total Sales])
• Target Achievement = DIVIDE([Total Sales], SUM(SalesData[Target]), 0)
• Yearly Growth = DIVIDE([Total Sales] – CALCULATE([Total Sales], SAMEPERIODLASTYEAR(SalesData[Date])), CALCULATE([Total Sales], SAMEPERIODLASTYEAR(SalesData[Date])),0)

To create a data model, the steps are:

• Add the SalesData table to the data model by using the Add to Data Model feature.
• Make the above measurements using the New Measure feature.
• Use the Power Pivot feature to manage and modify data models more easily.