Excel Basics – Building Data, Using Pivot Tables
My family and I recently went to dinner with some friends, and the topic of Excel came up (I know how to party right?). A couple of people mentioned they wish they knew more about Excel; this got me pumped up! Since we don’t live in the same city, in lieu of actually helping them in person, I’m going to write about some basics here. I also documented my master list of functions, which I’ll link below.
Previous Article, Jon’s Cheat Sheet/Master List of Excel Functions: https://jonamdall.com/2017/10/04/ms-excel-function-cheat-sheet-jons-personal-archivereference-page/
So, naturally the first step is to begin entering data. Before you get started though, I recommend deciding on some formatting standards you will use. I usually go with this set up:
- base data set – default (no fill) background,
- cells that are auto-calculated from functions – light orange,
- manually entered after initial import/paste – light blue
When entering data, generally you can use different columns for different categories. Once you get the hang of it, entering your data becomes like second nature. I’m going to build some nonsense for this example:
In the above example, as I mentioned, I built different categories for each column. A few notes: First, make sure you format cells appropriately. In this example, everything is fine as “General” except for DOB, which I’ve formatted as a Date (Right Click -> Format Cells -> Choose a Category). Next, you can see the last column is calculated from the DOB using a formula, and I’ve color-coded it (see previous linked article for formulas). Lastly, note the header column has little arrows; you can click those arrows to apply filters or sort columns. To add these, highlight your columns, go to the “Data” tab, and click “Filter.” Just make sure you have filters on all columns with data, or you’ll get things mixed up!
I recommend focusing on getting your base data set squared away before getting into much else. When you’re ready, the next (and biggest) step is to jump into creating your first Pivot Table! Pivot Tables are insanely powerful data manipulation and exploitation tools, built into MS Excel for your enjoyment. The primary requirements are that 1) you have properly structured data, and 2) that every column has a header. We meet both of those requirements, so let’s start.
The easiest way to start is to highlight all columns with data. In our case, we’re going to highlight columns A through F. Then, go to the “Insert” tab and click “Pivot Tables.” A menu will pop up asking you about a range and worksheet; the default should be fine.
Now, don’t be intimidated by this crazy screen. If you’re like me, you’ll eventually love it and feel relaxed by seeing this. Like Pavlov’s Dog, but much sadder. Now that you’re here, I recommend you jump right in and start messing around with your Pivot Table. The key areas are the “Rows” and “Values” at the bottom; dragging various fields here will create some interesting results. For example, if we want to see the distribution of favorite foods from our data, just drag Favorite Foods down to Rows and Values. You can also have multiple “values”, representing raw totals or percentage (Right Click -> Value Field Settings, Show Value As). Or, you can have multiple Rows to make things more interesting. Like in this example:
Here, I was able to very quickly figure out where people live and their favorite foods just by dragging fields around. Along with learning how to effectively apply formulas, building Pivot Tables is in my opinion the most important Excel skill to develop. We don’t have much data in this example, but imagine being able to quickly assess data sets with thousands of records/rows. Graphs are also easily built from these Pivots, because the tables are already structured well for them. Just click in your Pivot Table, go to the “Insert” tab, then click Pivot Chart. And there you go – Graphs the Easy Way.
I’m taking a few of my first hard labs this semester and have had to become literate in excel very quickly, so these kinda of tutorials are very helpful. A lot of excel tutorials are overly technical but you break it down in a very matter of fact way.
Also the Pavlovian reference made me laugh!
Excellent! I’m glad this helped someone a bit. That’s exactly what I was aiming for too; approachable text with less jargon/confusion.
If want to bounce any spreadsheet ideas off someone as you progress through your semester, feel free to pop back in. If it wasn’t painfully obvious already, I love Excel shop talk.