Excel: Tracking Data Easily and Efficiently

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.

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