Learn how to create data trackers in Excel to track any kind of data—from sales activity and inventory levels to vacation budgets using basic Excel and its newest features.
Overview
Syllabus
Introduction
- Efficient data tracking in Excel
- Introducing tables
- Key features of an effective data tracker
- Thinking about input, storage, and output
- Incorporating charts into your tracker
- Adding helper columns
- Using Slicers to filter for details
- Summary pages and dashboards
- Ad hoc vs. at-a-glance needs
- Protecting cells and sheets
- Hidden sheets
- Hidden columns and rows
- Using dropdown lists for accuracy and consistency
- Applying formula triggers to prevent inaccurate results
- Using cross-footing as a check for data quality
- Data validation for reasonable values
- Times: Checking data quality
- Challenge: Building a data tracker for event details
- Solution: Building a data tracker for event details
- Dynamic Arrays: One formula, many results
- Dynamic Arrays: FILTER, UNIQUE, SORT
- Dynamic Arrays: TOCOL, dropdowns, and conditional formatting
- VLOOKUP
- Categorizing bonuses with VLOOKUP
- Merging data with XLOOKUP
- Categorizing bonuses with XLOOKUP
- What is a PivotTable?
- Preparing and connecting a PivotTable to your data
- Building and using a PivotTable
- Power Query overview
- Splitting columns
- Append in Power Query
- Unpivot and fill-down
- Data bars for easy visual comparison
- Connecting a value to a shape
- Hiding zeroes
- Conditional formatting to warn of deadlines
- Challenge: Building a tracker 1
- Solution: Building a tracker 1
- Challenge: Building a tracker 2
- Solution: Building a tracker 2
- Continue your data tracking journey in Excel
Taught by
Oz du Soleil