Communicating Insights from Your Data Model Using Dashboards – Overview and Uses

We have done a lot of data transformations and calculations in our previous chapters. It is now time to communicate insights to our users using dashboards in Excel. In today’s data-driven world, the ability to effectively communicate insights derived from data analysis is crucial. Dashboards in Excel allow users to transform raw data into compelling visual representations, making it easier to convey complex information to various stakeholders.

This chapter covers the use of dashboards in Excel for communicating insights from your Data Model. Dashboards are a powerful tool for visualizing data and presenting insights in an easy-to-understand format. This chapter will discuss the basics of dashboards, including what they are and how they can be used. It will also cover important factors to consider when laying out your dashboard and common dashboard elements. In the next chapter, we will...

What are dashboards?

A dashboard is a visual representation of data that is designed to provide an overview of key performance indicators (KPIs) and other important information. Dashboards can be used to present data in a variety of formats, including charts, graphs, and tables. They are typically used to communicate insights from data to stakeholders, such as managers and executives, in an easy-to-understand format.

Dashboards in Excel are dynamic visual interfaces that consolidate and display key information in a concise and visually appealing manner. By leveraging Excel’s extensive data manipulation capabilities and charting features, we can create interactive dashboards that enable the exploration and analysis of complex datasets. The primary objective of a dashboard is to provide a comprehensive overview of key metrics, trends, and patterns, enabling users to make informed decisions quickly. The following is a sample Personal Finance Dashboard to help a user compare...

Factors to consider when laying out your dashboard

Before we dive deep into creating our own dashboards, let’s talk about various factors that may influence the choice of the elements on your dashboards.

When laying out your dashboard, there are several important factors to consider to ensure that it is effective in communicating insights from your data.

These factors include the following:

• The purpose and audience
• Clarity and simplicity
• Hierarchy and organization
• Consistency and visual harmony
• Interactivity and user controls
• Data visualization techniques
• Accessibility and documentation

Let’s now explore these in detail.

Purpose and audience

Before designing your dashboard layout, clearly define its purpose and identify the target audience. Understand what insights or information the dashboard needs to communicate and who will be using it. Tailoring the layout to the specific needs and preferences of the audience will...

Common dashboard elements

In Excel, creating a dashboard isn’t just about presenting numbers. It’s also about telling a story and making that story interactive and engaging. The dataset in our Data Model already contains valuable information on customers, products, stores, locations, sales, and returns. Think of each piece as a chapter of your story.

Let’s dive into how you can use the dataset you already have to craft this story with some common dashboard elements.

PivotTables

PivotTables provide a powerful way to summarize and analyze large datasets dynamically. PivotTables are normally used to stage the summary reports that most of the charts in our dashboard are created on. This is very useful because if there are changes in your source data, the summary reports will also be updated after clicking on refresh. Most charts in your Excel dashboard will be driven by a PivotTable.

Figure 7.9 – Inserting a PivotTable from...

Making your dashboard interactive

While the elements described are very useful in creating dashboards, the key objective is to engage your reader. Your dashboards should have options for them to interact with the data, explore different scenarios, and come away with insights that matter to them.

Excel offers several features and techniques to make dashboards interactive. Here are some ways you can make your Excel dashboards interactive.

Slicers

Slicers are interactive controls that allow users to filter and slice the data displayed in a dashboard. By connecting slicers to tables or PivotTables, users can easily select specific data subsets or filter by various criteria, such as dates, categories, or regions. Slicers provide a user-friendly way to explore data and customize the dashboard’s view.

Let’s proceed to add a Region slicer to our dashboard. The objective is to make the user see the key indicators on the dashboard filtered by the selected region in...

Summary

In summary, this chapter discussed the use of dashboards in Excel for communicating insights from your Data Model. Dashboards are a powerful tool for visualizing data and presenting insights in an easy-to-understand format. The chapter covered the basics of dashboards, including what they are and how they can be used. It also covered important factors to consider when laying out your dashboard and common dashboard elements.

Finally, it discussed how to make your dashboard interactive to engage your audience. It’s important to keep in mind that dashboards are only effective if they are well designed and easy to read and the audience and purpose of the dashboard are taken into account. In the next chapter, we will apply these elements to build the dashboard that will cap our learning in this book.