Office of Undergraduate Business Programs (UBP)

Excel Topics

The free resources below assist you in your studies of foundational to intermediate level Excel content necessary for your academic success, workforce success, and personal growth. Each topic requires about 60-90 minutes' worth of studying. This means, you will need to commit at least 12-15 hours to review, comprehend, and apply your learning. Commit at least 3-4 weeks for successful content acquisition.

Topics:

The following links take you to Official Microsoft trainings and documents. REVIEW the contents of each page and/or video. TAKE NOTES to remember the material. CREATE an Excel spreadsheet as shown in the videos or explained in the documentation. PRACTICE alongside all the materials you are reading/viewing.

1: Getting Started - videos (with transcripts), self-paced practice

Learning Objectives:
- Excel 2016 Quick Start
- What is Excel
- Create a workbook
- Add, delete, rename sheets
- Understand Spreadsheet Terminology and Components

2A: Designing, Formatting Workbooks - videos (with transcripts), self-paced practice

Learning Objectives:
- Hide or unhide columns
- Freeze top and left panes
- Move or copy worksheets
- Apply themes to change the look of a workbook

2B: Add text and data Add and edit data - videos (with transcripts), self-paced practice

Learning Objectives:
- Format numbers and cells
- Change formatting
- Autofill
- Move or copy cell contents
Note: Up to "Split and combine data"

3A: Understand and use cell references - video (with transcript), self-paced practice

Learning Objectives:
- Relative references
- Absolute references
- Mixed references
- Using autofill

3B: Formulas and Functions Version 1, Version 2 - videos (with transcripts), self-paced practice

Learning Objectives:
- Create formulas
- Name cells and ranges
- Use Trace to fix formula errors
- Locate functions
- Calculations using functions
- Basic math in Excel

3C: VLOOKUP What it is and when to use it - videos (with transcripts), self-paced practice

Learning Objectives:
- Enter VLOOKUP arguments in a formula.
- Use VLOOKUP to find values in Excel lists or tables.
- Avoid VLOOKUP errors.
- HLOOKUP

4: Data Visualization - videos (with transcripts), self-paced practice

Learning Objectives:
- Create charts
- Add sparkline charts
- Format charts
- Add trendlines and drop lines

5A: Excel Tables (Written guide) Excel Tables - Version 1, Excel Tables - Version 2 - video guides (with transcripts), self-paced practice

Learning Objectives:
- Sort and filter
- Use formulas
- Total Row
- Slicers
- More...

5B: Pivot Tables, Pivot Charts - videos (with transcripts), self-paced practice

Learning Objectives:
- Create PivotTables
- Work with PivotTables
- Group data in PivotTables
- Filter data with Slicers in PivotTables
- Create PivotCharts

6: Multiple Worksheets and Workbooks - Written guide

Learning Objectives:
- Group selected worksheets
- Ungroup selected worksheets
- Group all worksheets
- Ungroup all worksheets
- Create a 3-D reference

7A: Excel Applications: Macros - videos (with transcripts), self-paced practice

Learning Objectives:
- Work with macros
- Edit a macro
- Save your macro
- Assign a button to a macro

7B: Excel Applications: Data validation - videos (with transcripts), self-paced practice

Learning Objective:
- Apply data validation to cells

8: Advanced IF Functions - videos (with transcripts), self-paced practice

Learning Objectives:
- Advanced IF functions
- Nested IF functions
- IF with AND and OR
- COUNTIFS and SUMIFS
- AVERAGEIFS and IFERROR

9A: Financial Tools and Functions - videos (with transcripts), self-paced practice

Learning Objectives:
- Figure out the monthly payments to pay off a credit card debt.
- Figure out monthly mortgage payments.
- Find out how much to save each month for a dream vacation.
- Find out long it will take to pay off a personal loan.
- Figure out a down payment.
- See how much your savings will add up to over time

9B: What-If Analyses - documentation

Learning Objectives:
- Use scenarios to consider many different variables
- Use Goal Seek to find out how to get a desired result
- Use data tables to see the effects of one or two variables on a formula
- Prepare forecasts and advanced business models

10: Organize, Export, Import, Cleanse Data - videos (with transcripts), self-paced practice

Learning Objectives:
- Connect to external data
- Top ten ways to clean your data