Excel basics

The videos and exercises below are taught using Microsoft Excel, however the concepts would apply to other spreadsheet programs, such as Google Sheets. Questions? Contact MaryJo Webster. If you like my work, please consider making a donation via PayPal. Thank you!

Intro to spreadsheets follow-along exercise: Download data. Exercise is here.

Tipsheet: Basic formulas and functions in Excel

One-page Excel cheat sheet

Follow-along Exercise: U of M tuition over time. Download the data. Here is the exercise.

DIY Exercise: County population estimates. Download the data. Exercise is here.
(When you're ready to check your work, the answers are here. No cheating!)

Data to practice importing data to Excel (data that's used in the video)

Tipsheet: Importing to Excel

Follow-along Exercise: Analyzing fire incident data. Download data. Exercise is here.

Tipsheet: Pivot Tables cheat sheet

And in case you're curious, read about how Pivot Tables came to be.

DIY Exercise: Analyze New Yorker cartoon characters. Download data. Download exercise.

DIY Exercise: St. Paul city salaries. Download the data. See the record layout. Exercise is here.
(When you're done, you can check your answers here. No cheating!)

DIY exercise: Trends in opioid-related deaths. Download the data. Get the record layout here. Exercise is here

Introduction to VLOOKUP

This is a little more advanced, but is such a useful tool to learn.

Follow along with the first dataset and the second dataset

DIY exercise using VLOOKUP: Analyzing kindergarten vaccination rates. Download the data. Exercise is here.
(When you're done, you can check your answers here. No cheating!)

Understanding Errors:

#DIV/0! : This almost always means the formula is trying to divide by zero or a cell that is blank. So to fix this, first check to make sure that your underlying data is correct. In many cases, you will have zeros. For example, the number of minority students in some schools in Minnesota might be zero, so I have to use an IF statement whenever trying to calculate the percentage of minority students. Here’s how I get around the error, assuming the number of minority students is in cell B2 and the total enrollment is in C2. If the number of minority students is greater than zero, it does the math. Otherwise it puts zero in my field.

=if(b2>0, b2/c2, 0)

#N/A: This is short for “not available” and it usually means the formula couldn’t return a legitimate result. Usually see this when you use an inappropriate argument or omit a required argument. Hlookup and Vlookup return this if the lookup value is smaller than the first value in the lookup range.

#NAME?: You see this when Excel doesn’t recognize a name you used in a formula or when it interprets text within the formula as an undefined name. In other words, you’ve probably got a typo in your formula.

#NUM!: This means there’s a problem with a number in your formula (usually when you’re using a math formula).

#REF!: Your formula contains an invalid cell reference. For example, it might be referring to a blank cell or to a cell that has since been deleted.

#VALUE!: Means you’ve used an inappropriate argument in a function. This is most often caused by using the wrong data type.