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 try to keep my base data set with a standard color (no fill) background, cells that are auto-calculated from functions/formulas light orange, and after-the-fact manually entry 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:

Sample Data Sheet

Figure 1. Starting data, formatted with headers. This is ready for Pivot Tables!

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.

Pivot Main Screen Start

Figure 2. Starting screen of a newly created Pivot Table.

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:

Pivot Example 1

Figure 3. Example Pivot Table with a few fields dragged down to Rows and Values.

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.

Pivot Example 2

Figure 4. Example Pivot Table and Graph, with just a single field (Home) used under Rows and Values.

Advertisements

2 comments

  • 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!

    Liked by 1 person

    • 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.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s