Pivot Tables – Excel (Part 1)

Pivot Tables are one of the greatest features of Excel. They allow you to quickly summarize large data sets. You can quickly gain further insights into your data, and in a business setting pivot tables are usually made for the purposes of presenting the data to a key decision maker.

Once you get your hands dirty with a few examples, you’ll see how truly easy it is to create a pivot table.

Pivot Table Data

To quickly create a pivot table summary of your data, make sure of two things:

  1. Your data has a header row (with no blanks)
  2. There are no huge gaps in your data

pivot_table

Creating a Pivot Table

To create a pivot table, simply click anywhere inside the range of data and click “insert – pivot table.” Next, it will ask you if you want to place the pivot table on a new sheet or a current one (up to you!).

pivot_table_created

Customizing Your Table

The next thing  to do is choose which data will fall in rows vs. columns vs. filters (also called metrics vs. dimensions, but that’s a discussion for another time). It takes a few times making these to get an instinct as to which metrics will make for better rows vs columns vs filters.

Don’t know what rows or columns or filters are? Don’t fear! I’ve got your back.

Below, we’ve selected Year for the rows and “Sales” for the values. By default, the pivot table chose to sum the values. Looks good!

pivot_table_sales

Our pivot table looks good so far and we can see very clearly how well we did each year. But let’s see how each BRANCH did during each year.

pivot_table_by_branch

This looks pretty good, but let’s try moving branch over to be a column:

pivot_table_row

Much better! Now we can quickly scan year to year, branch to branch to see who our top performers are.

Part 2: Conditional Formatting inside a Pivot Table

Even though our pivot table looks ready to send off, let’s put a nice finishing touch before shipping it.

click here to see part two of this Pivot Table Guide

giphy.gif

Comparison Operators – Excel

Comparison operators are very helpful in Excel to force a Boolean result from a formula. Once we test two values against each other, we can return a desired result based on whether the outcome is TRUE or FALSE.

Here is a list of the most common comparison operators:

  1. Equal Sign (=): Checks if two values are equal to each other. Returns TRUE if values are EQUAL
  2. Not Equal (<>): Checks if two values are not equal to each other. Returns TRUE if values are NOT equal.
  3. Less Than (<): Checks if first value is less than second value. Returns TRUE if first value is less than second value.
  4. Greater Than (>): Checks if first value is greater than second value. Returns TRUE if first value is greater than second value.

Example:

download

What’s the Point?

You may be wondering why it would ever be useful to compare numbers in this way. Even if it is useful, what value beyond TRUE/FALSE can you extract from this comparison?

Below we have two data sets. Sales by Month are broken out. The first data set was pulled internally by your team, but the second set of data was pulled by 3rd party marketing analysts who are helping your company improve its sales strategy. Are the two data sets equal? We’ll find out…

excel-equal

Hard to tell at first glance… Is the data in column A – C equal to E – G?

Next, we’ll add a column that compares the sales figures from the first pull to the second. The format is:

=CELL1=CELL2

excel-comparison-operator

After applying the formula across the entire data set, we can clearly see that there are two values that do NOT match the original pull of data that your team did.

Although this may not be exactly the type of data you’re working with, it should be obvious that comparison operators can be very helpful in comparing data sets, especially across dozen, hundreds, or thousands of rows.

REMOVE DUPLICATES in Excel

How to Remove Duplicates in excel in a few clicks

Purpose of the Feature: Remove Duplicates is a feature in Excel that can removes duplicate values from a set of data, often very useful when creating index tables, cleaning data, and matching up disparate data sets.

Final Result: A list of cells with all of the duplicates removed.

Example: 

Below we have a list of videos, their categories, and views for this period. (There are 100+ videos in our data set and the pictures only shows a fraction of our data). We know we a wide variety of categories across the set, but how many unique categories do we have? 

We can find out using REMOVE DUPLICATES

remove duplicates

To do this, select the category column and paste it into it’s own column to move it away from the original data set.

Select “OK” and Excel will remove all of the duplicates in that column

It turns out we had only 7 unique categories across all of our videos.

remove duplicates

Knowing we now have the final list of UNIQUE values, we can perform further analysis on the categories.