Excel: Tracking Data Easily and Efficiently

Introduction

• Efficient data tracking in Excel

1. Planning Your Data Tracker

• Introducing tables
• Key features of an effective data tracker

2. Helpful Data Tracking Features

• 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

3. Protecting Your Work and Calculations

• Protecting cells and sheets
• Hidden sheets
• Hidden columns and rows

4. Managing Inputs and Calculations

• 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

5. Midterm: Building a Tracker

• Challenge: Building a data tracker for event details
• Solution: Building a data tracker for event details

6. Dynamic Arrays and the New Excel Calculation Engine

• Dynamic Arrays: One formula, many results
• Dynamic Arrays: FILTER, UNIQUE, SORT
• Dynamic Arrays: TOCOL, dropdowns, and conditional formatting

7. VLOOKUP and XLOOKUP

• VLOOKUP
• Categorizing bonuses with VLOOKUP
• Merging data with XLOOKUP
• Categorizing bonuses with XLOOKUP

8. PivotTable Basics

• What is a PivotTable?
• Preparing and connecting a PivotTable to your data
• Building and using a PivotTable

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

• Power Query overview
• Splitting columns
• Append in Power Query
• Unpivot and fill-down

10. Pretty It Up!

• Data bars for easy visual comparison
• Connecting a value to a shape
• Hiding zeroes
• Conditional formatting to warn of deadlines

11. Get Hands On

• Challenge: Building a tracker 1
• Solution: Building a tracker 1
• Challenge: Building a tracker 2
• Solution: Building a tracker 2

Conclusion

• Continue your data tracking journey in Excel