Excel: Tracking Data Easily and Efficiently

Excel: Tracking Data Easily and Efficiently
Excel: Tracking Data Easily and Efficiently
English | MP4 | AVC 1280×720 | AAC 48KHz 2ch | 1h 29m | 162 MB

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.

Topics include:

  • 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
Table of Contents

1 Welcome

Intro to Tracking Data
2 Examples Good, bad, and ugly
3 Introducing tables

Planning Your Data Tracker
4 Key features of an effective data tracker

Tracker Structures
5 Thinking about input, storage, and output
6 Incorporating calculations and graphs
7 Adding helper columns

Protecting Your Work, Sections, and Calculations
8 Protecting cells and sheets
9 Hidden sheets

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

Pretty It Up!
17 Connecting a value to a shape
18 Hiding zeros
19 Conditional formatting to warn of critical dates or thresholds

Putting It All Together
20 Building a tracker Part one
21 Building a tracker Part two