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)
Using calculated fields in a Pivot Table and an introduction to joining data with VLOOKUP
Part 3 - Pivot Tables
Data file: fatal_encounters.csv
Exercise/tipsheet
All about Pivot Tables
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.