How to create a pivot table in Excel to slice and dice your data

how to create a pivot table in Excel
Mark Coppock/Digital Trends
Microsoft Excel 2016, found in the Office suite, is the latest and greatest spreadsheet application from Redmond, and there’s a good reason so many number crunchers use it for all of their number crunching needs. While Excel is fine for simple spreadsheets to track expenses or build calendars, it comes into its own when you need to slice and dice and then present complex data. Here, we show you how to create a pivot table in Excel to take advantage of one of the application’s most powerful tools.

Before we start, just what exactly are pivot tables good for? Simply put, pivot tables let you look at the same data in different ways and from different angles, to make it easier to perform in-depth analysis and to spot important trends. When you’re evaluating sales results, for example, you may want to look at an individual person, a specific product, or a specific timeframe. With a pivot table, you can create one pool of information and then easily change your focus from one thing to another — an analysis that would be tedious to perform manually.

Note that you’ll need the full version of Excel 2016 to use pivot tables. The Universal Windows Platform (UWP) Windows 10 version of Excel doesn’t include that and many other advanced Excel features. Finally, if you want to try out new Excel features, join the Office Insider program and perhaps be the first to experience a new pivot table function.

Step 1: Prepare your data

Perhaps the most important step in using Excel pivot tables is to carefully organize your data. The easiest way to do this is to use Excel tables, which let you add rows that will be included with your pivot table whenever you hit refresh. But at the very least, you want your data to be in tabular form with informative column headers and with consistent data within columns.

For example, let’s say that you want to analyze sales information for your company. You have six salespeople who sell three products across a number of regions. Your products are tailored for each customer and so pricing varies. Here’s a sample table with fictional information to give you an idea of how data can be organized with a pivot table in mind.

The table was created simply by first entering the data, then selecting the entire range, and then going to Insert > Table. Again, you don’t have to take this step but it’s recommended if you want to add more rows of data later and make it easier to update your pivot table.

how to create a pivot table in excel tables starting 3
Mark Coppock/Digital Trends

Mark Coppock/Digital Trends

Step 2: Try a recommendation

Excel is full of nifty tricks to make working with data easier, and whenever possible it will try to guess what you want to accomplish and then automatically carry out a few steps. This helpful nature is demonstrated here by the Recommended PivotTables tool, which takes a look at your data and offers up some logical options on how to analyze and present things.

To use a recommended pivot table, simply go to Insert > Recommended PivotTables. Excel will present a few options for you to consider. In our example, Excel offers to create 10 different pivot tables that take a look at a number of different angles on our sales data. Note that how you label your columns matters; Excel reads these headers and offers up recommendations that make the most sense. If you want to look at sales prices, for example, don’t use the term “cost,” because Excel will base its recommendation accordingly.

One recommendation is “Sum of Total Sales by Customer.” If we choose that option, then Excel will proceed to create the pivot table.

how to create a pivot table in excel tables recommended
Mark Coppock/Digital Trends

Mark Coppock/Digital Trends

Notice that the pivot table is displaying only the data that’s pertinent to our present analysis. On the right-hand side, you’ll find the criteria that Excel used to create it in the PivotTable Fields dialog. We’ll cover what each of these field means in the next section on customization.

Step 3: Customize your pivot table

Each of the items in this dialog is important to determine how your pivot table will work. Click the configuration cog to alter this dialog’s look to whatever works best for you.

Fields selection

Here, you are choosing which columns to include in your pivot table. How that data will populate in the pivot table is determined by the type of data that it represents — Excel will figure out for you whether to add a column to your pivot table or add the field’s data within the table. For example, if you select “Items,” Excel assumes you want to add the number of items for each customer.

how to create a pivot table in excel tables add items
Mark Coppock/Digital Trends

Mark Coppock/Digital Trends

On the other hand, if you select “Date,” Excel places the data into the table and organizes the sales by when they occurred.

how to create a pivot table in excel tables time
Mark Coppock/Digital Trends

Mark Coppock/Digital Trends

As you’re working on your own pivot tables, you can experiment to see how added and removing fields affects the data that’s displayed. You’ll find that Excel does a great job of making selections that make sense, but you can also change things around if Excel’s choices don’t make sense.

Filters

Drag items from the fields selection list into the “Filters” section if you want to limit which data is shown. For example, if you drag “Customer” into the “Filters” section, you can easily show only the data from one or a selection of customers.

how to create a pivot table in excel tables filtered
Mark Coppock/Digital Trends

Mark Coppock/Digital Trends

Columns

By dragging fields into the “Columns” section, you can expand how your data is being reported. Again, when you drag a field into this section, Excel will try to figure out how you want the data presented. For example, if you drag “Date” into the “Columns” section, then Excel will display the sales as summarized for the most logical timeframe, which in this case is per month. This would be helpful if your primary concern was how much was sold on a monthly basis with an eye on customer purchasing patterns.

how to create a pivot table in excel tables colulmns
Mark Coppock/Digital Trends

Mark Coppock/Digital Trends

Rows

Similarly, you can drag fields into the “Rows” section to embed different data into pivot table rows. Again, if we drag “Date” into the “Rows” section, Excel will break out the sales by customer per month, but the data will be summarized by customer and not by month as in the previous example. In this case, you’re mostly worried about how much you sold to each customer, but you also want to spot any time-based trends.

how to create a pivot table in excel tables from rows time
Mark Coppock/Digital Trends

Mark Coppock/Digital Trends

Values

Finally, the “Values” section determines how you’re analyzing your data. In all of our examples so far, we’ve been looking at total sales. If you click on the down arrow key in this section, you can configure your value field settings to look at a different numerical calculation.

how to create a pivot table in excel tables values
Mark Coppock/Digital Trends

Mark Coppock/Digital Trends

For example, let’s say you want to look at sales averages instead of total sales. You would simply select “Average” in the value field settings dialog. You can also set the number format so that the results make the most sense.

how to create a pivot table in excel tables values averages
Mark Coppock/Digital Trends

Mark Coppock/Digital Trends

Now, instead of considering total sales by customer, and then calculating a grand total, we’re looking at average sales by company and then average sales across the company. This would be helpful in evaluating which customers are above or below average in sales, for example, and therefore which deserve the most (or least) attention. In our example, perhaps Acme Engineering and Jones Roofing don’t merit as much sales attention as the others.

Clearly, pivot tables offer a slew of options to make slicing and dicing your data easy. The trick to using pivot tables effectively is to decide exactly what you want to see before you start applying options. You also want to make sure that your data corresponds to how you’ve laid out your table and how you’ve named your headers. The more careful you are in setting things up, the more effective your pivot tables will be.

Step 4: Create your own pivot tables from scratch

Once you’ve played around with the pivot table feature and gained some understanding of how the various options affect your data, then you can start creating a pivot table from scratch. The process is similar to using a recommendation, only you go to Insert > PivotTable and then manually select your data as your first step.

In our case, that means selecting Table1, but we could also select a range of data or pull from an external data source. We can also decide if we want to create a new worksheet or place the pivot table next to our data at a certain location on the existing worksheet.

how to create a pivot table in excel tables from scratch
Mark Coppock/Digital Trends

Mark Coppock/Digital Trends

Once we’ve made our selection, we’re presented with a blank pivot table and our PivotTable Fields dialog.

how to create a pivot table in excel tables blank
Mark Coppock/Digital Trends

Mark Coppock/Digital Trends

Creating our own pivot table is then just a simple matter of selecting fields and determining how we want the data calculated and displayed. Let’s say we want to see how salespeople performed per month, with a grand total of sales for the year. We would select the “Salesperson,” “Date,” and “Total Sales” fields, drag the “Salesperson” field to the “Filters” section, and configure the values to display as currency. Excel automatically adds the relevant date items to the “Rows” section and assumes that we want to see sums.

how to create a pivot table in excel tables from scratch selected 2
Mark Coppock/Digital Trends

Mark Coppock/Digital Trends

By default, Excel will show all of the data for all salespeople. We can also select a single salesperson to see just his or her data. In this case, we see that Judy French had sales in only three months, even though her sales totals were significant. That could mean that her sales process was longer because she was going after whales instead of fishes — a valuable conclusion, if accurate. Perhaps investing in an assistant to help Judy close her sales more quickly would be a good idea.

how to create a pivot table in excel tables from scratch judy french
Mark Coppock/Digital Trends

Mark Coppock/Digital Trends

Step 5: Invest in some learning

If you want to get really good at using Excel pivot tables, invest some time in learning a bit more about how it uses the various data types. Microsoft offers its own training resources and there are a host of third-party trainers to consider.

Generally, though, this means digging into Excel in a way that’s beyond the scope of this guide. Nevertheless, hopefully you now see how pivot tables can be a powerful tool in analyzing your data, and it’s relatively easy to get started as long as you have your data configured into the right kind of table. And we can’t stress enough how important it is to know what you want to accomplish with your pivot table before you begin.

Computing

We want every laptop to be as thin as an iPhone. But is it practical?

The Acer Swift 7 is the thinnest notebook you can buy, and it feels like the notebook of the future. But it makes too many compromises along the way, and some weird design choices hold it back.
Smart Home

Facebook Portal and Portal+ video-calling devices gain new content and features

Facebook's Portal devices are video smart speakers with Alexa voice assistants built in that allow you to make calls. The 15-inch Portal+ model features a pivoting camera that follows you around the room as you speak.
Computing

Don't know what to do with all your old DVDs? Here's how to convert them to MP4

Given today's rapid technological advancements, physical discs are quickly becoming a thing of the past. Check out our guide on how to convert a DVD to MP4, so you can ditch discs for digital files.
Computing

Is your PC slow? Here's how to restore Windows 10 to factory settings

Computers rarely work as well after they accumulate files and misconfigure settings. Thankfully, with this guide, you'll be able to restore your PC to its original state by learning how to factory reset Windows.
Computing

Leaked AMD Ryzen 3000 mobile benchmarks look fit for thin, low-power laptops

AMD is poised to give Intel a run for its money in the ultra-low-power processor space for laptops. Leaked benchmarks for the Ryzen 3000 APU series show the AMD processor besting Intel's Core i7 Y series in multicore performance.
Mobile

Apple is spending $1 billion to hire up to 15,000 new employees in Austin

Apple has announced a series of expansions across the U.S. -- including a massive expansion to the company's Austin campus that will see it spending $1 billion to accommodate for up to 15,000 new employees.
Mobile

Leave the laptop at home, the iPad Pro is the travel buddy to take on vacay

The iPad Pro is a powerful tablet that's perfect for creatives and professionals. How does it fare when traveling with it as a laptop replacement? We took it on a two week trek in Japan to find out.
Computing

Will Windows 95 be reimagined? Microsoft’s tweet hints at a throwback

The classic Windows operating system may just be getting a reboot of its own. Microsoft tweeted a cryptic message involving the Windows 95 logo and saying that it had a special announcement for its customers today.
Computing

Microsoft Surface Pro 6: Everything you need to know

The Surface Pro 6 is officially here, though it's not as big of a redesign as you might have hoped. With a new coat of black paint and an 8th-gen processor, this is a small update. If you've been eyeing a Surface Pro, you may want to wait…
Computing

Style up your MacBook Air with one of these great cases or sleeves

Whether you’re looking for added protection or a stylish flourish, you’re in the right place for the best MacBook Air cases. We have form-hugging cases, luxurious covers and padded sleeves priced from $7 to $130. Happy shopping!
Computing

Want to make one hard drive act like two? Here's how to partition in Windows

If you don't want all of your files stored in one place but only have one drive to work with, partitioning is your best way forward. Here's how to partition a hard drive in Windows 10, step by step.
Computing

Does Qualcomm's latest laptop processor hold up against Intel's Core i5?

Qualcomm has been nipping at Intel's mobile CPU heels for years and now it might finally have overtaken it. To find out whether it's new SoC can hold its own in mid-range computing, we pitted the Snapdragon 8cx vs. Core i5.
Photography

Not just for Lightroom anymore, Loupedeck+ now works with Photoshop

Loupedeck+ can now help photographers edit in Photoshop too, thanks to physical controls for swapping tools, running actions, and more. The photo-editing console expanded to include Photoshop in the list of compatible editing programs.
Computing

Turn your Raspberry Pi into a Steam streaming hub with Valve’s Steam Link app

Valve's Steam Link app is now fully supported by Raspberry PI hardware, meaning that just about anyone with a few dollars to spare can build their own Steam streaming box in a matter of minutes.