Excel: Tracking Data Easily and Efficiently (2018)

Course details

Learn how to build a super-charged Excel spreadsheet to help you and your team easily track any kind of data-sales transactions, inventory levels, project statuses, employee paid time off, student progress, household spending, and more. Follow along with Excel MVP Oz du Soleil as he shows how to create a basic spreadsheet and transform it into an effective tracking system for any kind of data that is regularly updated. Learn spreadsheet basics, as well as more advanced techniques such as writing formulas that check that information is complete, protecting and hiding sheets, and creating alerts for deadlines and bad data. Oz wraps up with a challenge that pulls together everything you've learned into a polished final project.

Learning objectives
Planning your data tracker
Adding calculations and graphics
Protecting cells and sheets
Hiding sheets
Setting up alerts with conditional formatting
Merging data
Categorizing data
Formatting your tracker
Putting it all together

Concepts

0. Introduction

01 - Welcome

1. Intro to Tracking Data

02 - Examples - Good, bad, and ugly
03 - Introducing tables

2. Planning Your Data Tracker

04 - Key features of an effective data tracker

3. Tracker Structures

05 - Thinking about input, storage, and output
06 - Incorporating calculations and graphs
07 - Adding helper columns

4. Protecting Your Work, Sections, and Calculations

08 - Protecting cells and sheets
09 - Hidden sheets

5. Adjusting Inputs and Calculations

10 - Dropdown lists
11 - Formula triggers
12 - Setting up alerts with conditional formatting
13 - Data validation for reasonable values
14 - Time and dates
15 - Merging data with VLOOKUP
16 - Categorizing data with VLOOKUP
17 - XLOOKUP - The newest function in Office 365
18 - Bottom-up search with XLOOKUP
19 - Categorizing data with XLOOKUP
20 - A horizontal lookup with XLOOKUP

6. Pretty It Up

21 - Connecting a value to a shape
22 - Hiding zeros
23 - Conditional formatting to warn of critical dates or thresholds

7. Putting It All Together

24 - Building a tracker - Part one
25 - Building a tracker - Part two
26 - Building a tracker - Part three (XLOOKUP)