How to Create a Stunning Excel Dashboard in 8 Easy Steps

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.