Amdall Gallery

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:

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.

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:

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.

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