1. Visualizing disaggregated data with PivotCharts
And we're back for the final chapter of this course! Get ready to immerse yourself in the realm of PivotCharts and dashboards as we unveil the secrets of visualizing disaggregated data.
2. Aggregated vs. disaggregated data?
Let's start by introducing the concept of aggregated and disaggregated data.
Imagine you have a box filled with Lego blocks of various colors. When you pour them onto the floor, the scattered blocks represent your disaggregated data. Next, you select one block of each color and separate them to create categories. Then, you count the number of blocks for each color, resulting in aggregations like 45 yellow blocks and 123 blue blocks.
Finally, you can further break down these aggregations into more specific categories, such as square red and rectangular blue blocks, and count them accordingly. These counted blocks represent your aggregated data.
3. Aggregated vs. disaggregated data?
In our exercises thus far, our primary focus has been on aggregated or summarized data. We delved into sales data organized by regions, quarters, and products.
However, this chapter will focus on disaggregated data, also known as raw and unsummarized data, displayed on the right-hand side.
This type of data is typically sourced from systems like point of sales or other business support systems (BSS) and is characterized by its transactional nature. Each record within the dataset represents an individual order or event.
4. From tables and charts to PivotTables and PivotCharts
When visualizing aggregated data, we typically require multiple tables, each serving a specific use case. As shown on the slide, both the pie chart and the bullet chart rely on their respective data tables.
However, when visualizing disaggregated data, we cannot simply create a chart based on a raw data table, as it would not yield meaningful insights. Instead, we utilize PivotTables and PivotCharts. As illustrated on the right, PivotCharts dynamically respond to any modifications made to the PivotTable. This includes adding dimensions or measures, incorporating category splits, and more.
5. Exploring large datasets efficiently
Using PivotTables and PivotCharts offers a significant advantage: there is no longer a need to pre-aggregate various views before visualizing them. Instead, we can directly work with a single underlying dataset and dynamically explore and visualize the data on the fly using PivotTables and PivotCharts. Transposing the data, or altering the arrangement of rows and columns, is effortlessly achieved by reorganizing the PivotTable.
Moreover, as only one dataset is involved, any updates or corrections will automatically propagate to all linked PivotTables and PivotCharts. This ensures that our visualizations remain up-to-date and effortlessly reflect the most recent data.
This brings us close to what is called in data "Once source of truth".
6. Enabling interactivity and building (mini) dashboards
From this point onwards, we are just one step away from constructing mini dashboards. As demonstrated here, Excel allows us to incorporate two interactive elements: a date-based Timeline and categorical Slicer filters. By connecting these elements to the underlying PivotTables and PivotCharts, we can effortlessly create interactive dashboards in Excel, allowing for seamless data exploration.
7. How to print in Excel?
Finally, suppose we'd like to print out a chart or a dashboard to capture insight and share information with senior executives.
Printing in Excel is slightly more complex than printing a Word document. You have many options, including print area, page layouts, and custom formatting like scaling, margins, and orientation. We will see them in practice in this chapter.
As a last remark, before hitting that print button, always check how many pages you will print and consider if it's really necessary!
8. Let's practice!
Onto you, let's test what you've learned.