Excel: Tracking Data Easily and Efficiently

Course details

Too often, Excel users struggle to effectively track data in Excel because they start with the final report in mind. While this seems intuitive, setting up a spreadsheet in this way limits what a person can get from their data. In this course, follow along with Excel MVP Oz du Soleil as he shows how to use Excel features and functions—including Power Query and dynamic arrays—to build and organize basic spreadsheets and transform them into robust tracking systems for any kind of data that is regularly updated. Learn how to create a tracker in Excel that you and your team can use to easily track any kind of data: sales, donations, inventory levels, project statuses, student progress, household spending, and more. Oz wraps up with challenges that invite you to pull together everything you've learned.

Concepts

0. Introduction

01 - Efficient data tracking in Excel

1. Planning Your Data Tracker

02 - Introducing tables
03 - Key features of an effective data tracker

2. Helpful Data Tracking Features

04 - Thinking about input, storage, and output
05 - Incorporating charts into your tracker
06 - Adding helper columns
07 - Using Slicers to filter for details
08 - Summary pages and dashboards
09 - Ad hoc vs. at-a-glance needs

3. Protecting Your Work and Calculations

10 - Protecting cells and sheets
11 - Hidden sheets
12 - Hidden columns and rows

4. Managing Inputs and Calculations

13 - Using dropdown lists for accuracy and consistency
14 - Applying formula triggers to prevent inaccurate results
15 - Using cross-footing as a check for data quality
16 - Data validation for reasonable values
17 - Times - Checking data quality

5. Midterm - Building a Tracker

18 - Challenge - Building a data tracker for event details
19 - Solution - Building a data tracker for event details

6. Dynamic Arrays and the New Excel Calculation Engine

20 - Dynamic Arrays - One formula, many results
21 - Dynamic Arrays - FILTER, UNIQUE, SORT
22 - Dynamic Arrays - TOCOL, dropdowns, and conditional formatting

7. VLOOKUP and XLOOKUP

23 - VLOOKUP
24 - Categorizing bonuses with VLOOKUP
25 - Merging data with XLOOKUP
26 - Categorizing bonuses with XLOOKUP

8. PivotTable Basics

27 - What is a PivotTable
28 - Preparing and connecting a PivotTable to your data
29 - Building and using a PivotTable

9. Using Power Query to Clean, Format, and Automate

30 - Power Query overview
31 - Splitting columns
32 - Append in Power Query
33 - Unpivot and fill-down

10. Pretty It Up

34 - Data bars for easy visual comparison
35 - Connecting a value to a shape
36 - Hiding zeroes
37 - Conditional formatting to warn of deadlines

11. Get Hands On

38 - Challenge - Building a tracker 1
39 - Solution - Building a tracker 1
40 - Challenge - Building a tracker 2
41 - Solution - Building a tracker 2

Conclusion

42 - Continue your data tracking journey in Excel