Category: excel

How to Analyze Data in Excel Like an Expert

There’s a lot to understand within Excel. Most of us don’t use much of its most powerful tools. Even if you’ve mastered the advanced Excel formulas, there’s still a lot left to explore.

In this post, we’ll look at two data analysis methods that experts use to make more sense of their data. In each case, you’ll learn to visualize data: in a summary table and in a Google Map. Your type of data will determine which is most useful.

Basic Data Analysis with Pivot Tables

If you’ve been asked to “drill down”, process, or explain an extremely large dataset in excel, then you’ll want to make Pivot Tables part of your repertoire. Pivot Tables are an Excel feature that allows you to calculate, summarize, and analyze data.

Unlike other forms of data analysis, you don’t need to do very much to prepare your data. No need to arrange all the data alphabetically from top to bottom, ensure all decimals are set to a specific count, and you don’t need to remove your other formatting. All that’s required is a header at the top so Excel knows what category your data will fall into when pivoting.

Our data above has the headers of Date, Agent, and Sales so Excel will know what to define the dates, names, and dollars under once the pivot begins. Let’s build!

Within Excel, click the Insert Tab → Pivot Table. This will automatically grab the data on the worksheet and offer to create a pivot table.

As shown, Excel defaults to creating a new table and on a new worksheet and adds data to the data model for the Pivot Table. Typically you won’t need to play with any settings here and instead just click OK to continue. You’ve now created a basic Pivot Table, but let’s check out the settings.

Every good pivot tabler knows though the real driving force of a table are its fields. Remember earlier how we ensured that we added Date, Agent, and Sales as column headers? Well, those have now become our pivot table fields. These fields can be placed into the bottom to create a dynamic cross section that can be used for all sorts of examination.

Start by clicking the checkbox next to each field. Excel will auto-assign the field to what it believes is the best corresponding section. Sometimes this is all you need to do but with more intensive data, you’ll want to adjust the data to match what you want to see. Here are a few examples of what the data will look like and their corresponding pivot table.

View Sales by Agent and Date

Default settings after clicking each of our boxes puts the Date in the Columns area, Agents in our Row area and Sum of Sales in our values area.

What we get is a run down of how each agent performed for each day listed and a grand total over the course of time within.

Filter by a Specific Agent

Sometimes you don’t want to see all the data, even when it’s summarized. Building off the previous example, we can actually filter by the agent and get a whole different view of the statistics.

To see Jim’s totals for instance, you’d click the dropdown box on Row Labels → Uncheck Select All → Check Jim → then Click OK.

Oh look! A brand new analysis showing only Jim’s totals and dates so now we know how well Jim performed based on our original data.

View Sales by Date (with Agent Breakdown)

Perhaps we’re more interested in how well sales did per day in our data. Unfilter the previous example and swap the Date and Agent field from their spot in Columns to Rows and vice versa.

Now we have a nice analysis of how our fictional company did for each date listed.

Pretty great right? Even more ways to view the data can be done by stacking the Date field below the Agent field in the Rows area or vice versa. Play around with how you’d like it to look and what makes sense to you and you’ll be analyzing data in no time!

Format the Data in Your Pivot Table

You may have noticed there’s something a little off about our pivot examples. Each and every time we’ve changed it, our actual values seem to be lacking a little something: the dollar sign!

Every field within the Values area of a Pivot Table can be customized in various ways. With our default settings, we have Sum of Sales, but we can change that however we’d like. To edit a field within the Values area, click the drop down on the field and then click Value Field Settings.

From here, we have a few excellent options to really display your sales data in a different way.

To change our numbers to have dollar symbols, we would start from Value Field Settings, then click Number Format. This next window should look extremely familiar as it is the same format field from everything else you do within Excel. If you choose either Currency or Accounting, we will now see our dollars back on display which makes things a whole lot clearer.

Create Advanced Pivot Tables

Once you have the basics of Pivot Table creation and field editing down, there’s so much more you can get into. Let’s take a look at one more example and get a little more intricate. You’ll apply your new Pivot Table skills, as well as some new tricks, to understand if Acme Incorporated made or lost money in 2018.

Here’s our sample data:

Let’s start by first making a Pivot Table using our data.

For this Pivot Table, let’s walk through our process to intelligently see if we made profit in 2018. For this example, we’ll be creating a pivot table that displays each agent’s name in the rows, adds the accounting type in the columns, provides a grand total in dollars for all and also is filterable by date and account.

  1. Add the Date and Account fields to the Filter Area. Order doesn’t matter as they are filtered independently.
  2. Add the Accounting Type field to the Columns area.
  3. Add the Agent Field to the Rows Area
  4. Add the Amount field to the Values field

Now use your formatting prowess to display the numbers: edit the amount field number format to be Currency and check the option to display negative numbers in read and parenthesis.

And voila! These changes now provide a powerful analysis covering a full year’s worth of data in a matter of seconds and mouse clicks. 2018 proved profitable for Acme Incorporated with 14 million in profit and the most profitable agent being Kelly.

This is just the cusp of what can be done with Pivots, but this knowledge alone will get you super far with your data analysis. Real quick, we’ll outline a few more advanced tricks you can use to make your data analysis with Pivot Tables work even better.

Replace empty cells or error values in data with 0s

  • With a cell in your pivot table selected, navigate to the contextual PivotTable Tools ribbon and click on the Analyze tab then options on the far left.
  • Within this new menu, locate the Format section
  • You can put a check into either For error values show: or For empty cells show: and then add the value 0 into the input box.
  • What this will do is add a value into those places where your base data may have errors and allow it to compute correctly regardless of if someone botched the actual entry before. This saves countless time and if something doesn’t add up, now you can look for 0s within the Pivot Table and head back to the core data to fix it.

Refresh Pivot Table Data whenever you open the workbook

  • Click Options from the left side of the Analyze tab under the contextual PivotTable Tools ribbon.
  • Click the Data Tab then check Refresh Data when opening the file.
  • This will ensure your pivot table data will always update depending on what is in your base data saving you LOTS of time.

Add a slicer

Once of a data analyst’s favorites, adding a slicer to a pivot table gives a clean way to filter the data and is very intuitive for someone who has never touched a pivot. In other words, you can share your Excel document with someone else and let them click a few buttons to see some numbers and start data analyzing with the best of them.

  • To add a slicer, Click the Analyze tab under the contextual PivotTable Tools ribbon. Next, select Filter → Insert Slicer
  • Next, you can pick exactly what you’d like to filter by and as you click each option, it will auto sort the data in your table to match!
  • To clear the slicer, click the filter with a red x in the top right corner and your data will turn right back.
  • Another great way to use a slicer is to have one for each filter you’d like to see. For example, we could make one for an agent, accounting type, and account then drill down depending on what we click on each slicer!

Perform Geographic Data Analysis with a Map

Pivot Tables are great ways to analyze data within Excel. If you have geographic data, such as addresses or cities, you’ll want to display it on a map. BatchGeo can help you visualize location data.

For example, you can take sales data by location from Excel and display it in a clustered map.

View Household income, average clustering in a full screen map

Or filter by specific groups of data, choosing which to include or remove from the map.

View Graduation Rates vs Incarceration Rates in a full screen map

Create Your First Map for Free

Use the Excel mapping tool to copy-paste your data from your spreadsheet directly to a map, no coding necessary.

5 Excel Tips From the Guy Who Built It

Microsoft Excel, and similar spreadsheet software, may be the most used application beyond word processing. Certainly, Excel has a diverse user base and laundry list of use cases, which probably even includes actual laundry lists. Many of us are casual Excel users, and it seems there’s always a new feature to learn.

In a 45 minute video originally shared with his internal teams, former Microsoft Excel Program Manager Joel Spolsky drops gem after gem of Excel knowledge. You can watch the video above and find the top five insights we’ve already put into action in our spreadsheets.

1. Use Tables For More Than Just Pretty Formatting

Excel table sortingYou have probably noticed the table feature in Excel. It’s really useful for quickly switching style templates and implementing banded data, where rows alternate in color. In addition to borders and other stylistics, tables also come with some powerful data manipulation abilities.

When you use tables, it becomes really easy to sort, sum, and add new data. The formatting is useful for end users, but also helpful in that it signifies to Excel where certain types of data begin and end. Each field in the header row of a table has a dropdown that gives you quick access to sorting and filtering. Fields in the total row let you select from SUM, AVERAGE, and other common Excel functions.

The best part of using tables is how easy it is to add new data. If you include a formula within one cell of a column, it will optimistically be copied throughout the rest of the column. Or, need to add new rows? Simply tab from the last field of the last row and a new row is added above the total row. Sort by a column and the header and total rows stay put. Also, the sorting doesn’t interfere with any other data on this sheet. Everything is contained in its own little world in the table. This becomes especially useful once you name columns and look up data with INDEX, but we’re getting ahead of ourselves.

2. Paste Values to Remove Formatting and Formulas

Copying and pasting is the Excel user’s best friend. It makes some powerful assumptions about cell references that make it incredibly useful. However, there are times when what you see is what you want. That’s where Paste Values comes in, a little trick used by Spolsky throughout the session.

Paste Special menu in Excel

For example, you might generate a full name field by using a formula to combine first names and last names (don’t forget the space between). If you later want to remove the first and last name columns, your full name column will disappear… unless you replace it with the values created by your formula.

Try it out:

  • Select an entire column that was generated by a formula
  • Copy the column to your clipboard
  • From the Edit menu, choose Paste Special, then select Values

You can now remove the first and last name columns (or whichever extraneous columns you used to create the column whose values you’ve just pasted). Note that you can also paste formulas, formats (including conditional formatting), and more. Paste Special is, indeed, special.

3. Provide Your Data Room to Breathe

Look at the breathing roomYou may notice what first appears to be a strange habit as Spolsky adds data to his spreadsheet. He always leaves Row 1 and Column A blank. Why do cells need room to breathe? Is it just his fastidious need for margin? Nope, it turns out there is a more functional reason: selecting and sorting.

When you leave space around a table of data, whether it’s an official table or not, Excel is able to make intelligent assumptions about what goes together. When it comes time to sort data, for example, Excel automatically constrains itself to the “island” of data surrounding the selected cell(s).

One of the reasons this habit may seem strange is if you don’t already put your data in tables. But you’ll probably do that now after reading the first item in our tips from Spolsky’s presentation. If you aren’t already convinced, the next two tips should make you a believer.

4. Give Names to Columns and Cells

Column named StateHave you ever found yourself including a value in a function, copying the function down an entire column, only to have to change the value in the function later? Take the example Spolsky uses in the video: he multiplies each employee salary by a fixed tax rate. A better method is to include that value somewhere in your spreadsheet and reference the cell. That way, you can alter the tax rate and all the data that relies upon it will update.

It’s great to pull repeated values from other locations in your spreadsheet, but then you need to memorize the cell value. Worse yet, when someone attempts to understand your formula later on, they have to figure out where the cell is and what it represents. If you declare a name for that cell, you’ll be able to reference it not as J2 (or worse, $J$2 to create an absolute reference), but as TaxRate.

Similarly, you can name entire columns or other ranges of data. Spolsky named the Salary column, so that his formula could be simply =Salary*TaxRate — Excel took care of the rest, intelligently calculating the correct values.

5. Superpower Your Spreadsheets With INDEX and MATCH

Things really got interesting when Spolsky added multiple tables to the same spreadsheet. By using official tables, giving them room to breathe, and naming columns, you can perform some powerful lookups on your data.

First, create a lookup table. Spolsky used city names and tax rates for a simple two column table. His main table also had a city name field, so he used the MATCH function to find which row of the lookup table’s city column matched the city for each employee. Like so:
=MATCH([@Location], TaxRates[City])

Then, he used INDEX to find the tax rate based on the matching city:
=INDEX(TaxRates[TaxRate], MATCH([@Location], TaxRates[City]))

Finally, as shown in the image below, Spolsky multiplied the tax rate he looked up by the employee’s salary.

INDEX and MATCH example

Be sure to sort your lookup table alphabetically by city name (or whatever value you are using MATCH on), otherwise it won’t know how to find what you’re looking for. But that prep is minimal when compared to the time these functions save you.

6. BONUS!

While not part of Spolsky’s video, one of our favorite uses of Excel is to store geographic data like addresses and postal codes. Selfishly, the reason we like it for location data is because of the really cool mapping application we’ve been building for the last decade. You can use all the tricks above to organize your data, then simply copy and paste into our mapping tool to generate a map.

Try BatchGeo for free right now!