Overview
-
Learn how to build a super-charged Excel spreadsheet to easily track any kind of data -from sales activity and inventory levels to household spending.
Syllabus
-
Introduction
- Welcome
- Examples: Good, bad, and ugly
- Introducing tables
- Key features of an effective data tracker
- Thinking about input, storage, and output
- Incorporating calculations and graphs
- Adding helper columns
- Protecting cells and sheets
- Hidden sheets
- Dropdown lists
- Formula triggers
- Setting up alerts with conditional formatting
- Data validation for reasonable values
- Time and dates
- Merging data with VLOOKUP
- Categorizing data with VLOOKUP
- XLOOKUP: The newest function in Office 365
- Bottom-up search with XLOOKUP
- Categorizing data with XLOOKUP
- A horizontal lookup with XLOOKUP
- Connecting a value to a shape
- Hiding zeros
- Conditional formatting to warn of critical dates or thresholds
- Building a tracker: Part one
- Building a tracker: Part two
- Building a tracker: Part three (XLOOKUP)
