What this lesson covers
Day 3 introduces the building blocks used to turn a data table into an interactive Excel dashboard. The lesson covers data preparation, summaries, charts, filters, dates, and key performance indicators.
Day 3 topics and objectives
The goal is to learn how the different Excel features fit together to help summarize and explore data in a dashboard.
Using Excel to build a dashboard
The lesson uses Excel features such as PivotTables, PivotCharts, Slicers, Timelines, KPI Cards, and GetPivotData to build and update a dashboard.
Why data analysis and dashboards matter
Data analysis and dashboards can help people understand patterns in data and use that information to support business decisions. The quality of the decision still depends on the data and the interpretation.
Excel and CSV files
Before building a dashboard, understand the file format and preserve the features needed for analysis.
Difference between a CSV file and an Excel workbook
A CSV file stores tabular data as plain text and does not retain workbook features such as formatting, formulas, or multiple worksheets. An Excel workbook can preserve those features.
Save as an Excel workbook to preserve formatting
If you need formulas, formatting, or worksheets, save the data in an Excel workbook format such as .xlsx rather than relying on CSV to preserve them.
Conditional Formatting
Conditional Formatting applies visual formatting when data meets set conditions. It helps important values stand out so they can be found more quickly.
Use Conditional Formatting to highlight data
Set rules to highlight values or patterns in the data. The lesson demonstrates options including Data Bars, Color Scales, and Heat Maps.
Use Data Bars and Color Scales
Data Bars show a bar whose length reflects a cell’s value. Color Scales apply colors across a range of values, making relative differences easier to scan.
Create a custom Conditional Formatting rule across columns
Custom rules can highlight data using more complex conditions, including comparisons across columns. Choose the range and formula carefully so the rule applies to the intended rows.
Create a PivotTable
A PivotTable summarizes and analyzes a larger data set by grouping and aggregating fields. The lesson reviews how dimensions and values can be selected to produce a report.
Review PivotTable basics
A PivotTable can summarize many records by grouping them according to selected dimensions. The structure depends on which fields are placed in rows, columns, filters, and values.
Choose dimensions and values for a report
Dimensions might include dates or products; values might include sales or quantities. Select the fields that answer the question the report is meant to address.
PivotCharts
A PivotChart displays information from a PivotTable as a chart. Choose a chart type that fits the data and keep the connection between the chart and table in mind.
Turn a PivotTable into a PivotChart
Create a PivotChart from the PivotTable and choose a chart type that makes the relevant comparisons clear.
How PivotTables and PivotCharts are connected
A PivotChart is linked to its PivotTable. When the table’s summarized data changes, the chart can update to reflect the change.
Slicers
A Slicer provides an interactive way to filter a PivotTable or PivotChart. The lesson covers adding a Slicer and connecting it to the relevant reports.
Create a Slicer to filter data
A Slicer lets a user select which categories to display by clicking options on the control.
Connect a Slicer to PivotTables and PivotCharts
When the Slicer is connected to the relevant PivotTables and PivotCharts, a selection filters the associated reports and visualizations.
Timelines
A Timeline is a control for filtering date-based data over a selected period.
Create a Timeline to filter by date range
Add a Timeline to select the period to include in the analysis.
Use a Timeline to analyze data
Changing the Timeline range lets you compare trends across different periods.
Format dates correctly as YYYY-MM-DD
Make sure date values are recognized consistently by Excel. The lesson recommends the YYYY-MM-DD format for its example so the Timeline can work with the dates.
KPI Cards
A KPI Card displays a key performance indicator in a form that is easy to read. The lesson demonstrates creating a card and displaying values drawn from a PivotTable.
Create a KPI Card for a key figure
A card can show a key figure such as sales, customer count, or a growth rate, making the selected measure easier to spot.
Display PivotTable values in a card
Link the card to a value in the PivotTable so the displayed figure follows the summarized data.
GetPivotData
GetPivotData is an Excel function for retrieving information from a PivotTable for use in formulas. The lesson covers the function and a dynamic formula.
Use GetPivotData to retrieve a PivotTable value
GetPivotData can return a selected value from a PivotTable for use in another calculation or display.
Create a dynamic GetPivotData formula
A dynamic formula can adjust the requested value when the PivotTable’s contents or selections change. Check the field names and references used in the formula.
Review the six dashboard building blocks
The lesson brings together six components: PivotTable, PivotChart, Slicer, Timeline, KPI Card, and GetPivotData.
Review the six core components
Each component has a different role: summarize the data, visualize it, filter it, select a date range, display a key number, or retrieve a PivotTable result for another formula.
Apply the components to a working dashboard
Combine the features according to the questions the dashboard should answer. Test that the filters, dates, charts, and displayed figures update as expected when the underlying data changes.
Contact
For a free consultation, contact us on LINE.
