Building Interactive Dashboards in Excel

This presentation offers an introduction to creating interactive dashboards in Excel, focusing on techniques suitable for users who may not have access to Power BI. The speaker guides viewers through the process of data preparation and organization, emphasizing the importance of putting data into a table for easy updates. Key steps covered include designing a wireframe layout, building various chart types like line, map, bar, and donut charts from pivot tables, and incorporating calculated metrics using formulas. The demonstration highlights how to assemble these elements onto a single dashboard, add interactive filters called slicers, and connect them to the charts.

Building Dynamic Excel Dashboards

Based on the provided source, an Excel dashboard is essentially a report that helps you display important data or information in a single place. The primary purpose is to allow your audience to easily see key metrics or statistics that are important to them at a glance.

Creating a dashboard in Excel is useful for analyzing data, extracting data, and presenting key metrics, highlighting pain points in a much more visual way. Dashboards use charts and color to get across the story of your data. When designing a dashboard, it is crucial to consider what questions you are trying to answer and what your audience wants to know.

While applications like Power BI are popular for creating reports and visualizations, Excel remains a preferred choice for many people for several reasons. One significant factor is cost, as Power BI requires an additional payment and is not part of a standard Microsoft 365 subscription. Excel is also a familiar application for most people, making them feel more comfortable using it when first learning about dashboards. Additionally, some users find Excel to be more flexible than Power BI, particularly when it comes to dashboard design and achieving a desired look.

Building an Excel dashboard involves several steps:

• Data Preparation and Organization: It’s important to start with clean and consistent data. While the provided session’s data was already clean, cleaning techniques were covered in a previous webinar. A critical step is to put your source data in a table if you want your dashboard to update easily. You can check if data is in a table by looking for the “Table Design” ribbon, or by pressing Ctrl+T if it’s not. Standardizing naming conventions is also recommended for organization. For instance, naming tables with “TBL_”, charts with “CHT_”, and pivot tables with “PVT_” makes it easier to identify elements when linking them later. The source data used in the example was a downloaded sales data set with about 5,000 rows. Organizing different components (data, wireframe, charts, calculations, dashboard) into separate, color-coded tabs can help manage complexity.
• Wireframing: Planning the dashboard layout is a good idea before starting. This involves noting exactly what metrics you want to display and how you want to lay out your dashboard, including where charts, slicers, and formatted sections will go. This plan, or wireframe, can be sketched on paper or in an Excel tab using shapes.
• Creating Core Components: The dashboard often includes pivot tables and pivot charts built from the source data. The session demonstrated creating four specific charts:
• A line chart showing total profit by year. Line charts are often suitable for time-based data.
• A map chart showing average unit sold by country. Map charts colorize geographical regions based on data. Note that map charts cannot be created directly from pivot table data; the data must be copied out first, and then the chart’s data source needs to be pointed back to the pivot table. Not every chart type is suitable for all data; for example, too many countries would make a column chart unreadable, but are fine for a map chart.
• A bar chart showing revenue by sales channel and item type. For charts with a lot of data categories, like many items, applying a filter to show only the top items (e.g., top 3 or 5) can make the chart more manageable and readable. Bar charts can be made more visually effective by increasing the bar width and adding data labels instead of using a horizontal axis for values.
• A donut chart showing the count of orders by region. Donut charts (and pie charts) are generally best for representing a small number of items (e.g., two or three), as they can become confusing with more data.
• Incorporating Calculations: Dashboards can display key metrics that are not represented in charts, often shown as “cards” or summary statistics at the top. These metrics need to be calculated on a separate worksheet and then linked to the dashboard. Examples of calculations shown included finding the most profitable item, most profitable region, count of cancelled orders, and top sales channel. This involves using functions like UNIQUE (to get a list of distinct values), SUMIF (to sum values based on a condition), COUNTIF (to count items based on a condition), MAX (to find the highest value), and INDEX/MATCH (to look up corresponding text for a value). Linking these calculations ensures the dashboard updates dynamically when the source data changes.
• Assembling and Formatting: To make the dashboard look professional, it’s recommended to turn off grid lines on the dashboard sheet. Components like charts and calculated metrics (often placed inside shapes) are then brought onto the dashboard sheet and arranged. Basic formatting includes resizing elements, changing background fills, applying consistent fonts and colors, and using alignment tools. Using company branding colors is also a good practice. Removing chart borders can help them blend into the dashboard. Adding custom headings using shapes allows for consistent formatting across all elements.
• Adding Interactivity (Slicers): Slicers are interactive filters that can be added to the dashboard. They are inserted from the PivotChart Analyze tab. Slicers represent column headings from your data, allowing users to click buttons to filter the displayed information. Slicers can be formatted (e.g., changing the number of columns, removing headers, modifying styles). Crucially, slicers need to be connected to the specific charts or pivot tables you want them to control using the “Report Connections” setting (also called “Filter Connections”). If not connected, a slicer may only control the first chart it’s associated with.
• Updating the Dashboard: If your source data is in an Excel table, adding new data to the bottom should automatically expand the table. To update the dashboard components (pivot tables, charts, calculations), you can use the Refresh All button, typically found under the PivotChart Analyze tab. This process aims to provide a one-button update for the entire dashboard, though the live demonstration encountered an issue due to the source data not being properly formatted as a table initially.

Mastering Excel Pivot Tables for Dashboards

Based on the source provided, Pivot Tables are presented as a core component when building interactive dashboards in Excel. They are described as a straightforward way to analyze and summarize data.

Here’s a discussion of Pivot Tables based on the source:

1. Purpose in Dashboards: Pivot Tables are crucial for creating the underlying data or information that will be displayed on a dashboard, particularly in Pivot Charts. The dashboard demonstrated is “pretty heavy on pivot tables and charts”. They are used to extract and present key metrics from your raw data.
2. Creation Process:

• They are created from your source data, which ideally should be in an Excel table to allow for easy updates.
• To create a Pivot Table, you click within your data, go to the Insert ribbon, and select Pivot Table.
• It’s strongly recommended to place each Pivot Table on a new worksheet to help organize a complex dashboard workbook.
• It’s important to rename both the Pivot Table worksheet and the Pivot Table itself using a consistent naming convention (e.g., starting with “PVT_”) to make them easier to identify later, especially when connecting them to slicers.

1. Working with Pivot Tables:

• The Pivot Table Fields area lists all the column headings from your source data.
• You build the report by dragging and dropping these fields into four areas: Filters, Columns, Rows, and Values. The source demonstrates using Rows and Values frequently.
• The Values area is typically where fields containing numbers (like profit or units sold) are placed. By default, Excel often tries to sum values, but you can change how the field is summarized (e.g., to Average or Count) using Value Field Settings.
• Number formatting should be applied to the values in the Pivot Table to make them tidier and easier to read.
• Excel can automatically group date fields (like splitting a date column into Years, Quarters, and the original Date), which you can then manipulate by dragging out groupings you don’t need.
• You can turn off Grand Totals if they are not needed for the chart or display you are creating.
• For efficiency when building multiple pivot tables for a dashboard, you can copy an existing pivot table worksheet and then modify the fields and settings as needed, which is faster than starting from scratch each time.

1. Pivot Tables and Charts:

• Pivot Tables are the basis for Pivot Charts, which are then used on the dashboard.
• However, the source highlights a specific limitation: Map charts cannot be created directly from data inside a Pivot Table. To work around this, you must first copy the data out of the pivot table (pasting values only), create the map chart from the copied data, and then point the chart’s data source back to the Pivot Table’s data range.
• You can apply filters within the Pivot Table (like the “Top 10 Filter,” which can be configured for any number, not just 10) to refine the data before creating a chart, especially when you have a large number of categories that would make a chart unreadable.

1. Interactivity and Updating:

• Slicers, which are interactive filters, can be inserted from the Pivot Chart Analyze tab and are connected to Pivot Tables and Pivot Charts using Report Connections (also called Filter Connections). This allows users to filter the dashboard data by clicking buttons. Naming your Pivot Tables and charts correctly is important for easily selecting which ones a slicer should control.
• If your source data is in a table and new data is added, the Pivot Table’s source range will update automatically. To update the dashboard components (including Pivot Tables), you use the Refresh All button, typically found under the PivotChart Analyze tab. This is intended to provide a quick way to update the entire dashboard based on new data.

In summary, Pivot Tables in the context of this source are powerful tools for summarizing and manipulating large datasets. They serve as the foundation for creating the visualizations and summary statistics that populate an Excel dashboard, allowing for dynamic reporting and analysis. Effective use of Pivot Tables involves careful data organization, understanding how to summarize and filter data within them, and properly linking them to charts and slicers for interactivity and dynamic updates.

Creating and Using Excel Pivot Charts

Based on the source provided, Pivot Charts are a fundamental element in creating interactive dashboards in Excel, specifically designed to visually represent the summarized data from Pivot Tables. The dashboard demonstrated in the source is described as being “pretty heavy on pivot tables and charts”.

Here’s a discussion of Pivot Charts based on the source:

1. Purpose and Connection to Pivot Tables: Pivot Charts serve to display important data and key metrics from your raw source data in a visual format on a dashboard. They are inherently linked to Pivot Tables; you create a Pivot Chart directly from an existing Pivot Table. Visualizing data through charts is highlighted as a way to “get across the story of your data” and highlight “pain points”.
2. Creation Process: Once you have created a Pivot Table populated with the data you want to visualize, you create a Pivot Chart by clicking within the Pivot Table, navigating to the “Pivot Table Analyze” ribbon, and selecting “Pivot Chart”. From there, you can choose the desired chart type.
3. Types of Pivot Charts Demonstrated: The source demonstrates creating four specific types of pivot charts for the dashboard:

• A Line Chart, used to show total profit by year. Line charts are noted as often suitable for “time based data”.
• A Map Chart, intended to show average unit sold by country.
• A Bar Chart, used to display revenue by sales channel and item type.
• A Donut Chart, created to show the count of orders by region. Donut charts (and pie charts) are generally suggested as “good for when you have maybe two or three things” to represent, as more data can make them confusing. Column charts are mentioned as generally suitable for most data.

1. Suitability of Chart Types: The source emphasizes that “not all charts are created equally” and some are “more suited to certain types of data”. For example, while a map chart works well for visualizing data across many countries, a column chart with that much data would be “absolutely horrendous and nobody would be able to read it”.
2. Limitations and Workarounds (Map Charts): A significant point raised is that you cannot create a Map chart directly from data inside a Pivot Table. The workaround involves copying the data out of the pivot table (pasting values only), creating a regular Map chart from this copied data, and then importantly, pointing the chart’s data source back to the Pivot Table data range using the “Select Data” option on the “Chart Design” ribbon. This ensures the chart updates when the pivot table data changes.
3. Formatting and Customization: Pivot Charts offer various formatting options to enhance their appearance and readability on the dashboard:

• Hiding “gray filter buttons” (field buttons on chart) to make the chart look cleaner.
• Removing the legend if it doesn’t add valuable information.
• Adding or modifying chart titles.
• Changing chart colors and styles.
• Formatting axes (e.g., changing bounds to adjust the visual range).
• Formatting the data series (e.g., changing bar width, varying bar colors by point, adjusting donut hole size).
• Adding and formatting data labels (e.g., position, color, boldness), sometimes used instead of displaying values on an axis.
• Deleting grid lines within the chart area.
• Adding a border around the chart’s data series (e.g., bars).
• On the dashboard itself, removing the default chart borders helps charts blend into the background.
• Using custom headings added with shapes on the dashboard instead of the chart’s built-in title allows for consistent formatting across the dashboard.

1. Efficiency in Creation: When creating multiple pivot charts, copying the worksheet containing an existing pivot table and chart, then deleting the chart and modifying the pivot table, is suggested as a quicker method than creating each one from scratch from the source data.
2. Interactivity with Slicers: Pivot Charts are designed to work interactively with Slicers. Slicers act as visual filters that allow users to dynamically change the data displayed in the chart by clicking buttons. To connect a Slicer to specific Pivot Charts (or their underlying Pivot Tables), you must use the “Report Connections” (or “Filter Connections”) setting found by right-clicking the slicer. Properly naming your Pivot Charts and Pivot Tables helps in identifying them when establishing these connections. If connections aren’t made, a slicer may only control the first chart it’s associated with.
3. Updating: Once the source data is updated (ideally in a table format), the Pivot Charts can be updated automatically by refreshing the linked Pivot Tables. This is done using the “Refresh All” button, typically found under the “Pivot Chart Analyze” tab. The goal is a “one-button update” for the entire dashboard.

In essence, Pivot Charts translate the powerful data summarization capabilities of Pivot Tables into visual insights, forming the central graphical components of interactive Excel dashboards, while requiring careful handling, especially with chart types like maps.

Visualizing Data in Excel Dashboards

Based on the source provided, Data Visualization is presented as a key trend and a fundamental aspect of analyzing and presenting data effectively, particularly in the context of building interactive dashboards in Excel.

Here’s a discussion of Data Visualization based on the source:

1. Purpose of Data Visualization: The popularity of analyzing and extracting data, and presenting key metrics is rising, with a focus on doing so “in a much more visual way than we ever have done before”. The goal is to “really get across the story of your data” and highlight “pain points” to the audience. Dashboards themselves serve to “display important data or information in a single place so that your audience can easily see key metrics or statistics that are important to them”.
2. Methods of Visualization: Data is presented visually “using charts using color”.
3. Role in Dashboards: Data visualization, particularly through charts and pivot charts, is a central component of the dashboard creation process discussed. The dashboard built in the source is described as “pretty heavy on pivot tables and charts”. These visualizations allow users to quickly see key metrics and statistics.
4. Specific Chart Types: The source demonstrates creating several types of charts for the dashboard, all linked to underlying pivot tables:

• A Line Chart to show total profit by year.
• A Map Chart to show average units sold by country.
• A Bar Chart to display revenue by sales channel and item type.
• A Donut Chart to show the count of orders by region.
• Column charts are mentioned as generally suitable for most data.

1. Chart Suitability: The source emphasizes that “not all charts are created equally” and some are “more suited to certain types of data”. For instance, a map chart is good for geographical data across many countries, whereas a column chart with that much data would be “absolutely horrendous and nobody would be able to read it”. Donut and pie charts are suggested as “generally good for when you have maybe two or three things that you want to kind of represent”.
2. Using Color: Color is used as part of visualizing data. It can also be used for design purposes on the dashboard and to help organize tabs in the workbook. Using company branding colors is also suggested for consistency.

In essence, Data Visualization, primarily through the use of charts derived from summarized data (often via Pivot Tables), is presented as a crucial technique for making data analysis accessible, insightful, and actionable within the context of Excel dashboards. It’s about transforming raw data into visual elements that tell a clear story and highlight important information for the audience.

Excel Dashboard Data Organization Principles

Based on the provided source, Data Organization is highlighted as a crucial element when building interactive dashboards in Excel, particularly because dashboards can become quite complex with potentially “lots and lots of different tabs”. Effective organization helps manage this complexity and ensures the dashboard functions correctly and updates easily.

Here are the key aspects of Data Organization discussed in the source:

1. Starting with Clean Data: The source emphasizes that the raw data used for the dashboard should be “nice and tidy” and “consistent”. Data downloaded from third-party systems or websites may not be in the perfect format and might require cleaning using Excel functions. While the source doesn’t detail cleaning methods, it mentions that a previous webinar covered these techniques.
2. Using Excel Tables for Source Data: A “really important point” for organization and dashboard functionality is to put your Source data in a table. If your data is in a table, it allows your dashboard to “update with the click of one button” when new data is added. You can check if data is in a table by looking for the “Table Design” ribbon when clicked inside the data, and you can convert data to a table using the keyboard shortcut Ctrl + T.
3. Standardizing Naming Conventions: It is “really important” to name your table and standardize your naming conventions for different elements. This means using prefixes like TBL_ for tables, CHT_ for charts, and PVT_ for pivot tables, followed by a descriptive name (e.g., TBL_sales_data). This standardized naming makes it “easier to identify the different elements in your dashboard”, which is particularly helpful when linking elements like tables and charts to slicers.
4. Organizing Worksheets/Tabs: With potentially many components (source data, extra data, wireframe, calculations, pivot tables, charts, dashboard), organizing your tabs is essential. The source recommends putting each pivot table and pivot chart on a new worksheet to avoid confusion. Furthermore, using color-coded tabs helps separate different groups of worksheets, such as data tabs, calculation tabs, and chart tabs, making it “easier for me to organize all of these different tabs”.
5. Separating Calculations: Calculations used for key metrics displayed on the dashboard (like most profitable item or region, count of cancelled orders) are housed on a dedicated “calculations worksheet”. These calculations link back to the source data, ensuring they update when the source data changes.
6. Handling Data Extraction for Specific Charts: For certain chart types, like Map charts, you cannot create them directly from Pivot Table data. The workaround involves copying the data out of the pivot table and pasting it as “values only” onto a separate range. While this extracted data isn’t automatically linked, the chart created from it is then pointed back to the original Pivot Table data range using the “Select Data” option.

In summary, effective data organization in Excel dashboards, as described in the source, involves ensuring source data is clean and in a Table format, adopting standardized naming conventions for key elements, strategically organizing components onto separate, color-coded worksheets, and managing calculations and specific chart data appropriately. This structured approach helps maintain clarity and enables the desired interactivity and easy updating of the dashboard.

Building Dashboards in Excel: A Guide

Microsoft Excel Dashboards: A Study Guide

I. Introduction to Dashboards in Excel

• What is a Dashboard?A report that displays important data and information in a single place for easy understanding of key metrics and statistics.
• Designed to answer specific questions for a target audience.
• Often incorporates visual elements like charts and color to tell the story of the data.
• Excel vs. Power BIPower BI is a dedicated application for creating reports and dashboards, often considered the “latest buzzword” in data analysis. It is a paid product outside the Microsoft 365 subscription.
• Excel is a familiar and widely used application. Many prefer it for dashboard creation due to its cost (often included in existing subscriptions), user comfort, and perceived flexibility in design and layout.
• Purpose of Dashboards:Visualize data and highlight key metrics.
• Identify pain points or areas of interest.
• Present data in a more visual and digestible way than raw data or traditional reports.
• Enable informed decision-making.

II. Building a Dashboard: The Process

• Agenda for the Session:Introduction to dashboards.
• Viewing a completed example.
• Data preparation and organization.
• Creating a wireframe (planning the layout).
• Setting up information using pivot tables and charts.
• Incorporating calculations with formulas.
• Assembling the dashboard.
• Basic formatting.
• Adding interactivity with slicers.
• Updating the dashboard.
• Example Dashboard Components:Title
• Key statistics/metrics (displayed as “cards” or highlighted areas).
• Pivot charts (line chart, map chart, bar chart, donut chart demonstrated).
• Inspiration and Design:Look at examples from others (e.g., Pinterest) for ideas on structure and design.
• Consider company branding and theme colors.
• Use color to separate and organize different groups of information (e.g., colored tabs for data, wireframe, calculations, dashboard).
• Remove gridlines on the dashboard sheet for a cleaner look.

III. Data Preparation and Organization

• Source Data:Use a clean and consistent data set. Data may need cleaning using Excel functions before analysis.
• Ensure the data is in an Excel Table. This is crucial for automatic updating when new data is added.
• To put data in a table: Select data and press Ctrl + T.
• Naming Conventions:Standardize naming for different Excel elements (tables, charts, pivot tables).
• Prefixes like TBL_ for tables, CHT_ for charts, and PVT_ for pivot tables help with identification.
• Proper naming makes it easier to link elements (e.g., connecting slicers to charts).

IV. Planning the Dashboard: The Wireframe

• Purpose: To plan the layout and content of the dashboard before beginning the building process.
• Process:Determine the key metrics and information to be displayed.
• Sketch out the desired arrangement of elements (title, key statistics, charts, slicers).
• Can be done on paper or using shapes in an Excel tab.

V. Setting Up Information: Pivot Tables and Charts

• Creating Pivot Tables:Start with your data in a table.
• Go to Insert > PivotTable.
• Choose your table range (automatically selected if clicked within the table).
• Create the pivot table on a new worksheet for organization.
• Rename the pivot table worksheet and the pivot table itself using standardized naming conventions (e.g., PVT_line, PVT_line_chart_effect).
• Build the pivot table by dragging fields into the Rows, Columns, Values, and Filters areas.
• Example Pivot Tables:Profit by Year (Order Date in Rows, Total Profit in Values).
• Average Unit Sold by Country (Country in Rows, Unit Sold in Values, change summary function to Average).
• Revenue by Sales Channel and Item Type (Sales Channel and Item Type in Rows, Total Revenue in Values).
• Count of Orders by Region (Region in Rows, Order ID in Values, change summary function to Count).
• Pivot Table Settings:Automatically splits date fields (Years, Quarters, Dates). Can remove unwanted levels.
• Change the summary function (Sum, Count, Average, etc.) using Value Field Settings.
• Apply number formatting to values.
• Turn off Grand Totals if not needed for charting.
• Creating Pivot Charts:Click inside the pivot table.
• Go to PivotTable Analyze > PivotChart.
• Choose the appropriate chart type for the data.
• Line Chart: Good for time-based data.
• Map Chart: Good for geographical data.
• Bar Chart: Suitable for most data, but be cautious with too many categories.
• Donut Chart: Limited to a few categories; shows parts of a whole.
• Map Chart Consideration: Cannot create a Map Chart directly from a pivot table. Need to copy and paste the pivot table data as values, create a regular map chart from that, and then point the chart’s data source back to the pivot table range.
• Chart Formatting:Remove gray filter buttons (right-click, Hide All Field Buttons on Chart).
• Remove unnecessary legends.
• Add a chart title (can be replaced later on the dashboard).
• Change colors and chart style using the Design tab.
• Format data series (e.g., gap width for bar charts, donut hole size).
• Add and format data labels.
• Delete gridlines.
• Remove chart borders on the dashboard for a cohesive look.
• Copying Worksheets: Hold down Ctrl and drag a worksheet tab to create a copy, saving time when creating multiple similar pivot tables/charts.

VI. Incorporating Calculations with Formulas

• Purpose: To display specific key metrics as individual values on the dashboard.
• Process:Create a separate worksheet for calculations.
• Use Excel formulas to extract and calculate the desired metrics from the source data.
• Example Formulas:UNIQUE: To get a list of unique values from a column.
• SUMIF: To sum values based on a condition.
• COUNTIF: To count entries based on a condition.
• MAX: To find the maximum value in a range.
• INDEX and MATCH (or XLOOKUP in newer versions): To perform lookups and return corresponding values based on a condition (e.g., finding the item type associated with the maximum profit).
• Link the results of these calculations to the dashboard.

VII. Assembling and Formatting the Dashboard

• Bringing Elements Together:Copy and paste pivot charts from their respective worksheets onto the dashboard sheet.
• Arrange charts according to the wireframe plan.
• Link key metric “cards” (shapes) to the cells containing the calculation results on the calculations sheet using the formula bar (=).
• Apply basic formatting to the dashboard elements (colors, fonts, alignment).
• Consider adding text boxes or shapes for consistent headings instead of relying on chart titles.

VIII. Adding Interactivity with Slicers

• What are Slicers? Interactive filters that allow users to easily filter the data displayed on the dashboard by clicking on buttons.
• Inserting Slicers:Click on a chart connected to a pivot table.
• Go to PivotChart Analyze > Insert Slicer.
• Choose the fields you want to use for filtering (e.g., Sales Channel, Order Priority).
• Formatting Slicers:Use the Slicer contextual ribbon to change the number of columns.
• Right-click the slicer and go to Slicer Settings to deselect “Display header” for a cleaner look.
• Modify the Slicer Style to change colors and remove the white background to blend with the dashboard design.
• Connecting Slicers to Charts:By default, a slicer inserted from a chart only controls that specific chart.
• Right-click the slicer and go to Report Connections (or Filter Connections).
• Select the checkboxes for all the pivot tables (and consequently their linked charts) that you want the slicer to control. Ensure pivot tables are named correctly to easily identify them.

IX. Updating the Dashboard

• Requirement: Source data must be in an Excel Table.
• Process:Add new data to the bottom of the existing data within the source data table. Excel Tables automatically expand to include new data.
• Go to the dashboard or any pivot table.
• Go to PivotTable Analyze > Refresh (or Refresh All).
• The dashboard, including pivot tables, charts, and linked calculations, should update automatically to reflect the new data.

Quiz (Short Answer)

Answer each question in 2-3 sentences.

1. What is the primary purpose of creating a dashboard in Excel?
2. Why might someone choose to create a dashboard in Excel instead of Power BI?
3. What is a wireframe in the context of dashboard design?
4. Why is it important for your source data to be in an Excel Table for dashboard creation?
5. Suggest a standardized naming convention for an Excel Table containing sales data.
6. What is the advantage of creating each pivot table for a dashboard on a separate worksheet?
7. Describe a scenario where a Map Chart might be a suitable visualization for your data.
8. What workaround is necessary to create a Map Chart using data from a pivot table in Excel?
9. How do you link a shape on your dashboard to a calculation result on a separate worksheet?
10. What is a slicer and how does it enhance the interactivity of a dashboard?

Answer Key (Quiz)

1. A dashboard in Excel helps to display important data and key metrics in a single, visually appealing place. This allows users to easily understand information and can aid in decision-making by highlighting trends or pain points.
2. Users might choose Excel over Power BI because Excel is often more familiar and comfortable to work with. It is also generally included in a Microsoft 365 subscription, avoiding the additional cost of Power BI.
3. A wireframe is a plan or sketch of how the dashboard will be laid out. It helps organize thoughts about which information to include and where different elements like charts and slicers will be positioned before starting the building process.
4. Having source data in an Excel Table is crucial because tables automatically expand when new data is added. This allows the dashboard to be easily updated by simply adding new data and refreshing the pivot tables.
5. A standardized naming convention for an Excel Table containing sales data could be TBL_Sales_Data. This prefix indicates it’s a table, and the rest of the name describes its content.
6. Creating each pivot table on a new worksheet helps to keep the workbook organized, especially for complex dashboards with many components. It prevents pivot tables from getting mixed up with the source data or other elements.
7. A Map Chart is suitable for visualizing geographical data, such as sales performance by country or region. It uses color gradients on a map to quickly show variations across different locations.
8. To use pivot table data for a Map Chart, you must first copy the data from the pivot table and paste it as values onto a new range. Then, create a regular Map Chart from this copied data and finally, update the chart’s data source to point back to the original pivot table range.
9. To link a shape on your dashboard to a calculation result, select the shape, then go to the formula bar. Type an equals sign (=), navigate to the worksheet containing the calculation, click the cell with the result, and press Enter.
10. A slicer is an interactive filter that appears as a set of buttons. Clicking on a button in a slicer filters the connected charts and data on the dashboard, allowing users to easily explore different segments of the data.

Essay Format Questions

1. Compare and contrast the strengths and weaknesses of using Excel versus Power BI for creating interactive data dashboards, considering factors like cost, user familiarity, design flexibility, and updating capabilities based on the provided text.
2. Discuss the importance of data preparation, organization, and standardized naming conventions in the process of building a complex Excel dashboard. Explain how these steps contribute to efficiency, maintainability, and the functionality of interactive features like slicers and automatic updates.
3. Elaborate on the role of the wireframe in the dashboard design process. Explain how planning the layout beforehand can impact the effectiveness and clarity of the final dashboard, including considerations like the target audience’s needs and the selection of appropriate chart types.
4. Describe the different types of pivot charts demonstrated in the source material and explain for each type the kind of data it is best suited to visualize. Include a discussion of any specific challenges or workarounds mentioned for creating these charts from pivot table data in Excel.
5. Analyze how calculations using formulas, such as SUMIF, COUNTIF, UNIQUE, MAX, INDEX, and MATCH, are integrated into the dashboard process. Explain how creating a separate calculations worksheet and linking these results to the dashboard contributes to its dynamism and usefulness.

Glossary of Key Terms

• Dashboard: A report that displays important data and information in a single place, often using visualizations, to provide an overview of key metrics and statistics.
• Power BI: A separate Microsoft application designed specifically for creating reports and dashboards, often used for data analysis and visualization.
• Microsoft 365: A subscription service from Microsoft that includes various applications like Excel, Word, and PowerPoint; Power BI is not typically included.
• Key Metrics: Important data points or statistics that are central to understanding performance or trends.
• Pain Points: Areas or issues highlighted by the data that require attention or investigation.
• Wireframe: A preliminary sketch or plan of the layout and content of a dashboard.
• Pivot Table: A tool in Excel used to summarize, analyze, explore, and present summary data from a larger data set.
• Pivot Chart: A chart that is linked to a pivot table and visually represents the summary data from the pivot table.
• Line Chart: A chart type often used to display data over time or in a continuous sequence.
• Map Chart: A chart type that uses geographical regions (like countries or states) and shades them based on data values.
• Bar Chart: A chart type that uses rectangular bars to represent data values, often used for comparing categories.
• Donut Chart: A chart type similar to a pie chart, showing parts of a whole, with a hole in the center.
• Calculations Worksheet: A separate sheet in a workbook dedicated to performing formulas and calculations that are then used on the dashboard.
• UNIQUE function: An Excel function that returns a unique list of values from a range.
• SUMIF function: An Excel function that sums values in a range that meet a specified criterion.
• COUNTIF function: An Excel function that counts the number of cells within a range that meet a specified criterion.
• MAX function: An Excel function that returns the largest value in a set of values.
• INDEX function: An Excel function that returns a value or the reference to a value from within a table or range.
• MATCH function: An Excel function that searches for a specified item in a range of cells, and then returns the relative position of that item in the range.
• Lookup (using INDEX and MATCH or XLOOKUP): A method of finding and returning a value from a table based on a matching criterion.
• Slicer: An interactive visual filter that allows users to easily filter data in pivot tables and pivot charts by clicking on buttons representing different categories.
• Report Connections (Filter Connections): A setting for slicers that determines which pivot tables and charts the slicer will control.
• Excel Table: A structured range of data in Excel that has specific features, including automatic expansion when new data is added, which is beneficial for dynamic dashboards.
• Number Formatting: Applying specific display formats to numbers (e.g., currency, percentage, reducing decimal places) to improve readability.
• Standardized Naming Conventions: Using a consistent system for naming different elements within an Excel workbook (e.g., tables, charts, sheets) for better organization and ease of reference.
• Data Labels: Values displayed directly on a chart element (like a bar or point on a line) to show the exact data value.
• Gap Width: A formatting option for bar charts that controls the spacing between the bars in a data series.
• Donut Hole Size: A formatting option for donut charts that controls the size of the central hole.
• Refresh (Refresh All): A command used to update pivot tables and connected charts and elements when the source

Briefing Document: Interactive Dashboards in Excel

Subject: Review of key concepts and practical steps for building interactive dashboards in Microsoft Excel, covering the advantages of Excel over Power BI, data preparation, planning (wireframing), creating pivot tables and charts, incorporating calculations, and adding interactivity with slicers.

Summary:

This briefing document summarizes the key takeaways from a webinar on building interactive dashboards in Excel. The session, led by IT trainer Deborah Ashby, highlights the increasing popularity of visualizing data and the benefits of using Excel for dashboard creation compared to Power BI, primarily due to cost and familiarity. The core of the webinar focuses on the practical steps involved, including data preparation, using pivot tables and charts (line, map, bar, and donut), integrating calculations using formulas, and making the dashboard interactive with slicers. The importance of planning (wireframing) and standardized naming conventions is also emphasized. While a final demonstration of refreshing the dashboard with new data encountered a technical issue, the overall process and key concepts for creating dynamic Excel dashboards were clearly outlined.

Main Themes and Important Ideas/Facts:

• Rising Popularity of Data Visualization: The trainer notes a significant increase in the trend of analyzing and presenting data visually using charts and colors to convey insights and highlight pain points. “One thing that I’ve definitely noticed over the last few years is the R in popularity of analyzing data extracting data and presenting key metrics highlighting pain points in a much more visual way than we ever have done before so we present our data using charts using color so that we can really get across the story of our data.”
• Excel vs. Power BI for Dashboards:Power BI: Described as the “latest buzzword” for data analysis and visualization, used for creating “really nice looking reports and dashboards and visualizations.” However, it is an additional cost and “does kind of live outside of the Microsoft 365 family.”
• Excel: A popular alternative for creating dashboards due to its familiarity (“most of us use Excel or have used excel at some point or another”) and cost-effectiveness. The trainer also personally finds Excel “a little bit more flexible than powerbi particularly when when it comes to dashboard design and getting my dashboard to kind of look exactly as I want it to look.”
• What is a Dashboard?: A dashboard is defined as a report that displays “important data or information in a single place so that your audience can easily see key metrics or statistics that are important to them.”
• Audience-Centric Design: A crucial aspect of dashboard design is considering the audience and the questions the dashboard aims to answer. “One thing that is really important when you’re designing dashboards you need to think to yourself what questions am I trying to answer with this dashboard what do people want to know what does my audience want to know.”
• Planning (Wireframing): Creating a wireframe before starting the dashboard design is highly recommended. This involves noting the desired metrics and planning the layout and placement of charts, slicers, and other elements. The trainer demonstrates using shapes in an Excel tab as a wireframe. “It is a good idea to kind of have a plan as to what you want to go onto your dashboard before you even begin.”
• Data Preparation and Organization:Clean Data: Having clean and consistent source data is essential. While not covered in this session (referencing a previous webinar), it’s acknowledged as a necessary first step.
• Data in a Table: Putting the source data into an Excel Table is “a really important point if you want your dashboard to update with the click of one button.” This allows the table to automatically expand when new data is added.
• Standardized Naming Conventions: Naming elements like tables, charts, and pivot tables using a consistent system (e.g., TBL_, CHT_, PVT_) is vital for organization and ease of use, especially when linking elements to slicers. “It’s so important to have like a standard nameing convention so it’s easy to identify the different elements in your dashboard is because it’s going to make your life a lot easier when we start having to link our tables and our charts to things like slices.”
• Using Color for Organization: Employing color coding for different types of tabs (data, wireframe, charts, calculations, dashboard) can significantly improve organization, especially in complex workbooks.
• Key Components of the Dashboard (as demonstrated):Title: A clear heading for the dashboard.
• Summary Statistics/Cards: Displaying key metrics (e.g., most profitable item, number of cancelled orders) at the top of the dashboard. These are often derived from calculations performed on a separate sheet.
• Charts: Visual representations of data. The webinar demonstrates creating:
• Line Chart: Used to show “total profit by year,” suitable for time-based data.
• Map Chart: Used to show “average unit sold by country,” suitable for geographical data. Important Note: Map charts cannot be created directly from pivot table data and require copying and pasting values before creating the chart and then re-pointing the chart’s data source back to the pivot table. “You can’t create this chart type with data inside a pivot table… you have to pull it out of the pivot table first of all.”
• Bar Chart: Used to show “revenue by sales channel and item type.” Suitable for comparing categories. The trainer advises using filters (e.g., Top 3) for large datasets to avoid overcrowded charts.
• Donut Chart: Used to show the “count of orders by region.” The trainer expresses a preference for other chart types for more than a few categories.
• Pivot Tables and Pivot Charts: The dashboard heavily relies on pivot tables as the source for the charts. Each pivot table and chart is ideally placed on its own sheet for clarity and organization.
• Calculations: Demonstrates using Excel formulas like UNIQUE, SUMIF, MAX, INDEX, MATCH, and COUNTIF on a separate sheet to derive key metrics for the summary statistics displayed on the dashboard. These calculations are linked to the source data to ensure dynamic updates.
• Formatting: Basic formatting techniques are discussed, including removing grid lines, adding shape outlines, and customizing data labels and axis. The use of company branding colors is also mentioned.
• Interactivity with Slicers: Slicers are described as “interactive filters” that allow users to easily filter the data displayed on the dashboard. They can be customized in appearance.
• Report Connections: Slicers need to be connected to the specific charts they are intended to control. This is done via the “Report Connections” (or “Filter Connections”) option, emphasizing the importance of naming charts correctly to easily identify them.
• Updating the Dashboard: The intended workflow for updating a dashboard involves adding new data to the source table (which auto-expands) and then using the “Refresh All” button on the “Pivot Chart Analyze” tab. A technical issue prevented a successful demonstration of this step in the webinar, but the principle was explained. “If you have your data in a table when you build your dashboard if you add data into the bottom the dashboard can be updated simply by clicking on refresh and everything will pull through nicely.”

Key Quotes:

• “Dashboards can get really complex and you might find yourself with lots and lots of different tabs so we want to try and organize that as best as we can.”
• “It’s always good to sort of go away and take a look at what other people are doing… sites like Pinterest just to give yourself some inspiration.”
• “If you want your dashboard to update with the click of one button you want to make sure that you put your Source data in a table.”
• “The reason why it’s so important to have like a standard nameing convention… is because it’s going to make your life a lot easier when we start having to link our tables and our charts to things like slices.”
• “You have to think to yourself okay how am I going to design my dashboard so before you even begin you want to make sure that you make a note of exactly what you want to display on that dashboard.”
• “When you’re putting together a dashboard you want it to kind of look as clean and professional as possible.”
• “When you’re um creating charts not all charts are created equally some charts are more suited to certain types of data.”
• “If you have time based data like we do here 2015 to 2022 that’s often nicely represented in the form of a line chart.”
• “You can’t create this chart type with data inside a pivot table and that’s really important if you want to use pivot table data in a map chart you have to pull it out of the pivot table first of all.”
• “If you do have a lot of data and you’re trying to cram it into a certain chart type one little thing I would say to do is maybe start applying a filter to just show the top three or the top five.”
• “Pie charts and donut charts in general are not my favorite types of chart they’re quite limited with how much data you can actually display in them.”
• “Because of the way I’ve constructed this [calculations] and everything links back to that Source data if anything changes it’s going to feed through to my calculations worksheet which is in turn going to feed through to my dashboard.”
• “Slicers just act as little filters which we can click on and it changes what’s displayed on our dashboard.”
• “With these slices they will not be connected to all of your charts… we need to right click on the slicer and Report connections.”

Conclusion:

The webinar provides a comprehensive introduction to building interactive dashboards in Excel, emphasizing planning, data organization, utilizing pivot tables and charts, incorporating calculations for key metrics, and adding dynamic filtering with slicers. Despite a minor technical issue with the final refresh demonstration, the session successfully conveyed the fundamental principles and practical steps required to create visually engaging and informative dashboards in Excel. The trainer’s insights on comparing Excel to Power BI, the importance of naming conventions, and the utility of wireframing are particularly valuable.

What is an interactive dashboard in Excel?

An interactive dashboard in Excel is a single-page report that helps you display important data and information visually using elements like charts, tables, and key metrics. The goal is to present crucial statistics in one place for easy audience understanding. A key feature is interactivity, often achieved through elements like slicers, allowing users to filter and explore the data presented on the dashboard dynamically.

Why are dashboards useful, particularly in Excel?

Dashboards are useful for visually highlighting key metrics and pain points within data, telling a story with the numbers. They allow audiences to quickly grasp important information without sifting through raw data. While dedicated tools like Power BI exist, many prefer Excel due to its familiarity, cost-effectiveness (often included in Microsoft 365 subscriptions), and perceived flexibility in design.

What are some key steps involved in building an Excel dashboard?

Building an Excel dashboard involves several key steps. Initially, it’s crucial to prepare and organize your data, ensuring it’s clean and structured, ideally in an Excel table for easy updates. Planning is also vital, often done through creating a wireframe or sketch to outline the desired layout and content. The process then involves creating components like pivot tables and charts from your source data, assembling them onto the dashboard sheet, formatting for clarity and visual appeal, adding interactive elements like slicers, and finally, connecting these elements to ensure the dashboard updates with new data.

How does data preparation impact the creation of an effective dashboard?

Proper data preparation is fundamental to creating an effective dashboard. The source data should be clean, consistent, and ideally structured in an Excel table. Using a table allows the dashboard to update automatically when new data is added. Without clean and well-organized data, building accurate pivot tables and charts, and ensuring the dashboard functions dynamically, becomes significantly more challenging.

What is the importance of naming conventions and organization in Excel for dashboards?

Standardizing naming conventions for tables, charts, and other elements is crucial for organization, especially as dashboards can become complex with numerous components and worksheets. Using prefixes like TBL for tables, CHT for charts, and PVT for pivot tables helps in easily identifying and referencing these elements. This organization is particularly helpful when linking different parts of the dashboard, such as connecting slicers to specific charts.

What role does a wireframe play in dashboard design?

A wireframe serves as a planning tool before you start building the dashboard in Excel. It involves sketching out the layout and deciding where different elements like the title, summary statistics, slicers, and charts will be placed. This planning stage helps to ensure that the dashboard is designed in a logical and user-friendly way, preventing unnecessary rework during the creation process.

How are calculations and key metrics displayed on an Excel dashboard?

Key metrics and top-level statistics that aren’t best represented by charts can be displayed using linked cells or formulas on the dashboard. Calculations, such as finding the most profitable item or counting canceled orders, are often performed on a separate worksheet using formulas like UNIQUE, SUMIF, COUNTIF, MAX, INDEX, and MATCH. The results of these calculations are then linked to shapes or text boxes on the dashboard using cell references in the formula bar, ensuring they update automatically when the source data changes.

How do slicers provide interactivity in an Excel dashboard?

Slicers are interactive filtering tools that can be added to an Excel dashboard. They act as visual buttons that, when clicked, filter the data displayed in the connected pivot tables and charts. To connect a slicer to multiple charts, you need to use the “Report Connections” feature (or “Filter Connections” in newer versions) by right-clicking on the slicer. This allows users to easily explore different subsets of the data by simply clicking on the desired filter options within the slicer.

By Amjad Izhar
Contact: amjad.izhar@gmail.com
https://amjadizhar.blog

Affiliate Disclosure: This blog may contain affiliate links, which means I may earn a small commission if you click on the link and make a purchase. This comes at no additional cost to you. I only recommend products or services that I believe will add value to my readers. Your support helps keep this blog running and allows me to continue providing you with quality content. Thank you for your support!