Data Visualization in Excel

Ever felt overwhelmed by a massive spreadsheet filled with data, wondering how to make sense of it all?

In today's fast-paced environment, being able to quickly visualize and interact with your data is crucial for making informed decisions.

Here's how you can transform a sea of data into an insightful and interactive dashboard in Excel, all within 20 minutes.

Dashboard Design Tips Video

For step-by-step instructions check out the video below.

Download Completed Excel Dashboard & Data

Design Tips Guide to Creating Excel Dashboards

1. Prepare Your Data and Dashboard Sheet

Before diving into the creation of the dashboard, I'll start by organizing my data. For this example, I used a sales dataset spanning 12 months, categorized by seller, product, state, sales, and profit.

I formatted the data in an Excel Table to allow for automatic updating of my dashboard.

I prepared five PivotTables to support the charts I plan to insert on the dashboard.

Get up to speed with PivotTables here.

2. Designing Your Dashboard Canvas

The foundation of a good dashboard is its design. Begin by setting the dashboard's background color.

Click the arrow at the intersection of the row and column labels to select the whole sheet, then apply the cell fill color.

I'm using a dark indigo, providing a visually appealing contrast for my charts and data elements. This is on a newly inserted blank sheet in my Excel workbook.

3. Inserting Titles and Subtitles

To provide clear understanding, insert a main title, "Sales Performance Dashboard," and a subtitle indicating the time frame, "January - December 2024."

4. Highlighting Key Figures

At the top of your dashboard, highlight important figures like total sales, profit, and profit margin. Microsoft 365 users can use icons (available free for from within Excel) next to each figure to visually represent their meaning - a telemarketer for sales and a money symbol for profit. These icons not only draw attention but also aid in quick comprehension.

I'll come back to the pie chart for the profit margin.

5. Creating and Positioning Charts

The visual heart of your dashboard will be the charts:

- Sales by Month: A column chart showing trends over time.

- Sales by Salesperson and Category: Two bar charts providing insights into sales distribution.

- Map Chart: Though Excel doesn't directly support creating map charts from PivotTables, you can trick it by copying the PivotTable data as values and then inserting a filled map chart.

- Profit Margin: I created an icon from a pie chart. I removed the legend and title and made it very small to fit beside the headline figure:

6. Interactivity with Slicers

To allow users to interact with the dashboard, insert Slicers for different categories like seller, category, and state. This lets users customize the view to see exactly what's relevant to them.

Ensure these slicers are formatted to match the dashboard's color theme and are positioned strategically for easy access.

Connect the Slicers to each dashboard through the Report Connections menu (right-click > Report Connections):

Check out this post on custom formatting Slicers.

7. Finishing Touches

With all elements in place, use shapes and formatting tricks to give each visual element its own distinct space, enhancing overall readability.

I added a rounded rectangle shape behind each chart and slicer in a shade slightly lighter than the background:

For the border, apply an inner shadow:

Additional Resources

Creating an effective dashboard doesn't have to be a time-consuming process. With some preparation and the right techniques, you can turn your raw data into a dynamic tool for exploration and decision-making in no time.

If you're interested in diving deeper into dashboard creation, consider taking my Excel Dashboard course.

This transformation from raw data to an interactive dashboard not only simplifies data analysis but also empowers users to make more informed, data-driven decisions quickly and efficiently. Whether you're in business, academia, or any field that relies on data, mastering these skills can significantly enhance your productivity and analytical capabilities.