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.

Step 4: Choose the Appropriate Visualization Type

The fourth step in creating a stunning Excel dashboard is choosing the appropriate visualization type for your data. Visualizations are ways to present your data graphically, such as graphs, tables, or maps. Using visualizations, you can highlight patterns, trends, relationships, or anomalies in your data. You can also make your dashboard more attractive and easy for your audience to understand.

However, not all visualization types are suitable for all data types. You need to choose the type of visualization that fits the purpose, audience, and context of your dashboard. You also need to consider factors such as the amount of data, the dimensions of the data, and the message you want to convey.

Here are some general guidelines for choosing the appropriate type of visualization:

• To show categorical data, such as products, categories, or regions, you can use bar, pie, or doughnut graphs.
• To display temporal data, such as dates, months, or years, you can use line, area, or spline graphs.
• To display numeric data, such as price, quantity, or sales, you can use columns, histograms, or Pareto graphs.
• To display hierarchical data, such as subcategories, subregions, or subproducts, you can use tree, treemap, or sunburst charts.
• To show geographic data, such as countries, provinces, or cities, you can use maps, heat maps, or bubble maps.
• To display correlational data, such as the relationship between two variables, you can use scatter, bubble, or radar graphs.

You can create your visualizations by using the Charts, PivotCharts, or Power View features in Excel. You can choose the type of visualization you want from the available gallery, or you can create your visualization by using the Custom Chart feature. You can also use Format, Design, or Layout features to change the colors, styles, or elements of your visualization.

Example: In the case of a sales dashboard, the visualization type selected for sales data is:

• Line graph to display the company’s monthly sales trend.
• The bar graph displays sales per product, category, and region.
• Pie graph to display target achievement percentage and annual sales growth.

To create the visualization, the steps that can be done are:

• Create a line chart by using the Charts feature and selecting Line Chart from the gallery.
• Add columns Date and Total Sales as the X and Y axes of the line graph.
• Change the color, style, and title of a line graphic by using the Format, Design, or Layout features.
• Create a bar chart by using the PivotCharts feature and selecting Bar Chart from the gallery.
• Add columns Product, Category, and Region as the X-axis and Sales per Product, Sales per Category, and Sales per Region as the Y-axis of the bar graph.
• Change the color, style, and title of a bar graph by using the Format, Design, or Layout features.
• Create a pie chart by using the Charts feature and selecting Pie Chart from the gallery.
• Add columns Target Achievement and Yearly Growth as the X and Y axes of the pie chart.
• Change the color, style, and title of a pie chart by using the Format, Design, or Layout features.

Step 5: Layout Your Dashboard

The fifth step in creating a stunning Excel dashboard is putting together your dashboard layout. Layout is how you organize visualizations, text, or other elements in an Excel worksheet. By putting together a good layout, you can make your dashboard neater, organized, and easy to navigate. You can also improve the aesthetics and readability of your dashboard.

Here are some general guidelines for putting together a good layout:

• Use grids to help you align your visualizations neatly and consistently. You can use the Align, Distribute, or Snap to Grid feature to help you organize your grid.
• Use white space to create distance between your visualizations and avoid coming across as cluttered or full. You can use Margins, Padding, or Spacing features to help you organize your free space.
• Use color to create a contrast between your visualization and the background. You can use the Theme, Color, or Fill feature to help you set your colors.
• Use size to create a hierarchy between your visualizations and highlight the most important ones. You can use the Size, Scale, or Resize feature to help you set your size.
• Use text to provide titles, labels, or captions for your visualizations and explain what they show. You can use the Font, Text Box, or Shape features to help you organize your text.

Example: In the case of a sales dashboard, the layout laid out for the dashboard is as follows:

• Use a 3×3 grid to align visualizations neatly and consistently.
• Use space to create distance between visualizations and avoid clutter or fullness.
• Use blue to create contrast between the visualization and a white background.
• Use large size for line graphs that show monthly sales trends, medium size for bar graphs that show sales per product, category, and region, and small size for pie graphs that show target achievement percentage and annual sales growth.
• Use text to provide dashboard titles, axis labels, and graphic captions.

Step 6: Make Your Dashboard Interactive

The sixth step in creating a stunning Excel dashboard is to make your dashboard interactive. Interactive means that your dashboard can respond to input or actions from users, such as clicking, swiping, or selecting. By making your dashboard interactive, you can make your dashboard more dynamic, flexible, and attractive. You can also provide a better and richer experience to your audience.

Here are some ways to make your dashboard interactive:

• Use slicers to allow users to filter data based on specific criteria, such as product, category, or region. You can use the Slicer or Timeline feature to add a slicer to your dashboard.
• Use form controls to let users control specific values or options, such as targets, ranges, or scenarios. You can use the Form Controls or ActiveX Controls feature to add form controls to your dashboard.
• Use hyperlinks to allow users to move between different worksheets, files, or websites. You can use the Hyperlink or Link feature to add hyperlinks to your dashboard.
• Use macros to allow users to perform specific tasks or processes, such as importing data, refreshing dashboards, or sending emails. You can use Macro or VBA features to add macros to your dashboard.

Example: In the case of a sales dashboard, the ways to create an interactive dashboard are:

• Add a slicer to filter data by product, category, or region by using the Slicer feature.
• Add a form control to control sales targets by using the Form Controls feature.
• Add a hyperlink to move to a worksheet that contains sales data by using the Hyperlink feature.
• Add macros to refresh the dashboard by using the Macro feature.

Step 7: Test and Evaluate Your Dashboard

The seventh step in creating a stunning Excel dashboard is to test and evaluate your dashboard. Test and evaluation is the process of checking if your dashboard is functioning properly, accurately, and according to your goals and audience. By testing and evaluating your dashboard, you can find and fix any errors, flaws, or problems that may exist in your dashboard.

Here are some ways to test and evaluate your dashboard:

• Use test data to check that your visualizations, measurements, and data models are working correctly and producing the expected values. You can use the Data Validation, Formula Auditing, or Error Checking features to help you test your data.
• Use test scenarios to check if your dashboard is interactive and responsive to user input or action. You can use the Test Mode, Debug Mode, or Step Through features to help you test your interactivity.
• Use evaluation criteria to check if your dashboard is effective and meets your goals and audience. You can use Feedback, Survey, or Rating features to help you evaluate your dashboard.

Example: In the case of a sales dashboard, the ways to test and evaluate the dashboard are:

• Use test data to check whether the line graph displays monthly sales trends correctly, the bar graph displays sales per product, category, and region correctly, and the pie graph displays the target achievement percentage and annual sales growth correctly.
• Using test scenarios to check whether the slicer can filter data by selected product, category, or region, form controls can control desired sales targets, hyperlinks can move to worksheets containing sales data, and macros can refresh dashboards with the latest data.
• Use evaluation criteria to check whether the dashboard provides an overview of the company’s sales performance, answers key questions related to sales, tracks important sales metrics, and matches the needs and preferences of sales managers and company executives.

Step 8: Share and Update Your Dashboard

The eighth and final step in creating a stunning Excel dashboard is to share and update your dashboard. Sharing and updating is the process of distributing and maintaining your dashboard to keep it relevant, accurate, and useful. By sharing and updating your dashboard, you can reach a wider audience, improve collaboration, and increase the value of your dashboard.

Here are some ways to share and update your dashboard:

• Use the appropriate file format to store and send your dashboard, such as XLSX, PDF, or HTML. You can use the Save As, Export, or Publish feature to help you share your files.
• Use the appropriate platform to share and display your dashboard, such as email, website, or app. You can use Share, Embed, or Link features to help you share your platform.
• Use the appropriate schedule to update and refresh your dashboard, such as daily, weekly, or monthly. You can use Refresh, Schedule, or Automate features to help you update your dashboard.

Example: In the case of a sales dashboard, the ways to share and update the dashboard are:

• Use the XLSX file format to save and send dashboards to sales managers and company executives by using the Save As and Email features.
• Use the website platform to share and display dashboards to the public using Publish and Embed features.
• Use a monthly schedule to update and refresh dashboards with the latest sales data by using Refresh and Schedule features.

Conclusion

Excel dashboards are a very useful tool for displaying important data visually and concisely. With Excel dashboards, you can track performance, identify trends, measure targets, and make better decisions. Excel dashboards can also be customized to your needs and preferences, so you can create dashboards that fit your goals and audience.