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 or via Venmo. Thank you!
Data file: mn_cause_of_death.xlsx
Exercise/tipsheet
Tidy data principles; wide versus long; SUM; AVERAGE; percent change; making a simple chart
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
Data file: fatal_encounters.csv
Exercise/tipsheet
All about Pivot Tables
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
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
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.
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
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.