Class Central is learner-supported. When you buy through links on our site, we may earn an affiliate commission.

LinkedIn Learning

Excel: Tracking Data Easily and Efficiently

via LinkedIn Learning

Overview

Learn how to build a super-charged Excel spreadsheet to easily track any kind of data -from sales activity and inventory levels to household spending.

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.

Syllabus

Introduction
  • Welcome
1. Intro to Tracking Data
  • Examples: Good, bad, and ugly
  • Introducing tables
2. Planning Your Data Tracker
  • Key features of an effective data tracker
3. Tracker Structures
  • Thinking about input, storage, and output
  • Incorporating calculations and graphs
  • Adding helper columns
4. Protecting Your Work, Sections, and Calculations
  • Protecting cells and sheets
  • Hidden sheets
5. Adjusting Inputs and Calculations
  • Dropdown lists
  • Formula triggers
  • Setting up alerts with conditional formatting
  • Data validation for reasonable values
  • Time and dates
  • Merging data with VLOOKUP
  • Categorizing data with VLOOKUP
  • XLOOKUP: The newest function in Office 365
  • Bottom-up search with XLOOKUP
  • Categorizing data with XLOOKUP
  • A horizontal lookup with XLOOKUP
6. Pretty It Up!
  • Connecting a value to a shape
  • Hiding zeros
  • Conditional formatting to warn of critical dates or thresholds
7. Putting It All Together
  • Building a tracker: Part one
  • Building a tracker: Part two
  • Building a tracker: Part three (XLOOKUP)

Taught by

Oz du Soleil

Related Courses

Reviews

Start your review of Excel: Tracking Data Easily and Efficiently

Never Stop Learning!

Get personalized course recommendations, track subjects and courses with reminders, and more.

Sign up for free