Pivot Table in Excel: Everything You Need to Know
If you’re studying Excel as a beginner or an advanced student, you’ve no doubt come across the phrase ‘pivot table’. Like most students, you may have seen a pivot table, but didn’t know how it was made. This post will explain what a pivot table is, how it’s used, and some important things to remember when setting one up.
What is a Pivot Table?
Before getting into how to create a pivot table, let’s first explore exactly what it is and why it’s necessary.
A pivot table is a lifesaver for people who have to summarize and process large amounts of data from Excel tables and lists. In essence, a pivot table is a tool that makes summarizing this information much easier. Without pivot tables, you’d have to manually select, copy, paste, drag and drop the info into new cells and organize it all into a neat new table. If you want to compare data from a single list or table, you’d have create a separate table for each list manually. This is extremely time consuming and leaves room for too many human errors. This is why businesses as well as individuals who use Excel frequently, prefer to use pivot tables.
As mentioned above, a pivot table summarizes information from lists and tables in Excel. You don’t have to drag over a single cell, copy any info or type any formulas. It does it all for you, and here’s how.
How To Use a Pivot Table
Okay, before we walk you through creating a pivot table, it’s important to note that not all data is suitable for one. There are specific criteria that the data should meet for the function to work properly.
The criteria are as follows:
- Duplicate Value. One or more of the columns must contain duplicate values. For example your Column A would be “Fruit” and your column B would be “Color”. In column B at least two cells should contain the same value. Since we’re working with colors, we’ll say “red” is in B:1 and B:2, which corresponds with apple (A:1) and strawberries (A:2).
- Numbers. At least one of the columns has to contain a numeric value not just words. Without numbers you won’t be able to generate values, summaries, subtotals or averages.
Creating a Pivot Table
There’re only two steps you need to complete to create a pivot table. Before beginning, select the cells that you want to add into the pivot table, then run the Pivot Table Wizard.
- Select the option that says ‘Microsoft Office Excel List or Database’, then click next.
- Select the cells you’re going to use from the drop down menu, then click next.
- Select where you want the pivot table to go. The options available are ‘New Worksheet’ and ‘Existing Worksheet’. Once you’ve made your selection, click ‘Finish’.
If you selected ‘New Worksheet’ in step three, you will be given the option to create a layout for the pivot table. If you want to save time, it’s best to organize the layout after you’ve created the table.
The pivot table contains clickable regions. When you click a region, options will appear on the right, which you can select to add the corresponding info for display in that field. After you insert all of the data, you can go to the layout option to create the look of your pivot table.
The bigger a pivot table is, the more complex it becomes to create; but rest assured that following these steps will help you through creating one — no matter how simple or complex. If you feel you still need help creating pivot tables, you can take an Excel 2010 Intermediate, Microsoft Excel 2013 Training Tutorial or Advanced Excel Training course, which will walk you through many hands on examples.
Top courses in Excel
Excel students also learn
Empower your team. Lead the industry.
Get a subscription to a library of online courses and digital learning tools for your organization with Udemy for Business.