Intermediate Excel Tutorials

These are a series of tutorials designed for someone comfortable with the basics of Excel but ready to learn some more complex skills. These exercises incorporate several of the tricks shown in the Excel Magic portion of this website, but the first exercise in particular is more of a refresher of some of the basics. Exercises 2 and 3 focus on Pivot Tables because they are so crucial to know how to use well. Later exercises are designed to take you through all the things you might need to do on a dataset, including some cleanup and joining with other data.

Questions? Contact MaryJo Webster. If you like my work, please consider making a donation via PayPal. Thank you!

Part 1- Basics refresher

Data file: mn_cause_of_death.xlsx

Exercise/tipsheet

Tidy data principles; wide versus long; SUM; AVERAGE; percent change; making a simple chart

Part 2 - Census 2020 data

Data file: census2020_ca_counties.xlsx
(This is a slice of a file originally obtained from Big Local News)

Exercise/tipsheet

Using calculated fields in a Pivot Table and an introduction to joining data with VLOOKUP

Do-it-yourself work:

Census population estimates data file and exercise

Part 3 - Pivot Tables

Data file: fatal_encounters.csv

Exercise/tipsheet

All about Pivot Tables

Do-it-yourself work:

New Yorker cartoons data file and exercise

Part 4 - Importing

The video is a simple tutorial in how to use the legacy import wizard in Windows (which is what you'll find on a Mac), and how to use the newer Get & Transform import option in Windows (not available on a Mac)

How to turn on legacy import wizard: Click on “File” –> “Options” –> “Data” and set the corresponding checkmarks for reactivating the “Text Import Wizard” in Excel. Start the text import by clicking on “Data” –>”Get Data” –> “Legacy Wizards” –> “From Text (Legacy)”.

Data file: importing_sample_data.csv

Part 5 - Baby names exercise

Data file: namesbystate.zip (Unzip this file and extract the text file for your state)

Exercise/tipsheet

This exercise is split into three videos. These cover: Importing, Pivot Tables, using VLOOKUP, an introduction to IF statements

Baby Names - video 2


Baby Names- video 3


Do-it-yourself work:

School Diversity Exercise, and download data file 1 and data file 2, and record layout
When you're finished you can compare your results with this.

Part 6 - Opioid deaths analysis


Data: opioid_deaths_data.csv

Record layout: opioid_deaths_record_layout.pdf

Other data tables we’ll use: opioid_lookup_tables.xlsx

More about Date functions


Exercise/tipsheet: opioid_exercise.pdf

This exercise is split into 2 videos. Using Pivot Tables and VLOOKUP to clean inconsistent values and to recode values; using date functions; and making charts

Part 6 - video 1


Part 6 - video 2


Do-it-yourself work:

MMR kindergarten vaccination exercise, data file 1, data file 2 and data file 3 (be sure to download all three data files). When you are finished you can compare yours with this file showing the answers/completed work.

Part 7 - Other useful things