How to Create a Stunning Excel Dashboard in 8 Easy Steps

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.
Other Interesting Articles

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.

Latest Articles

Related Articles