Pivot Table Creation in Excel

So you want to know how to create a pivot table in Excel. Most people are not Excel experts. They only know how to enter data, maybe add it together, but not the more advanced functions like pivot tables. Pivot tables in Excel are a tool that can seem intimidating, but in reality, are simple and an important tool for anyone who wants to analyze data. Once you learn how to create a pivot table in Excel, you will be accurately and efficiently analyzing large amounts of data in no time.

Pivot Tables in Excel

The pivot tables in Excel are an easy and efficient way to analyze large amounts of data. Learning how to create a pivot table in Excel is easier when it is hands-on. Pivot tables allow you to look at a data set from multiple angles. You can, as the name suggests, pivot the table to see the data from any perspective you need. You can remove data as needed without losing the original table so data analysis does not get too chaotic when dealing with large data sets. 

As you follow the step-by-step instructions, make sure to follow along with your own data set. Learning how to create a pivot table is easier to do by trying it yourself. Open up an Excel spreadsheet and import an example set of data to work with. Then you can begin experimenting with the different pivot table functions without fear of messing up. 

Create a Pivot Table

Learning how to create a pivot table will help with data analysis. It will sort and extract the data you want to work with. When you learn how to create pivot tables in Excel, data analysis will become less of a hassle. You will be able to organize data to help measure goals like marketing performance and make informed business decisions.

How to Generate a Pivot Table

1. Choose the data you want to create a pivot table from. Select the cells that contain the data by left-clicking and dragging down until all the cells you want to include in the pivot table are highlighted. 
2. From the tab bar at the top of the spreadsheet, select insert. 
3. Select Pivot table. A pop-up box will appear.
4. The first section in the pop-up box is titled Choose the data you want to analyze. Choose Select a table or range.
5. In the table/range subsection, under the Choose the data you want to analyze section should be the range of data you selected earlier. Verify that the range contains the data you want to use to create a pivot table.
6. The next section in this popup box is called Choose where you want the PivotTable report to be placed. If you want the pivot table to be placed in a new Excel worksheet, select new worksheet. If you want it to be placed on the same worksheet, select existing worksheet. 
7. Select OK. 

How to Build Out a Pivot Table

1. In the pivot table field pane, select the field name checkbox to add a field. 
2. You can move fields around by clicking and dragging them to your desired area.

Modifying a Pivot Table

The biggest advantage that learning to create a pivot table in Excel carries is that is is modifiable after creating. If you want to look at the data in a different way or only look at a small section of data at a time, you will not lose the original pivot table. It is easy to make changes and undo any changes you made by checking and unchecking fields and moving them around to your desired areas. 

Why Use Pivot Tables in Excel?

So why is it important to learn how to create a pivot table in Excel? When you have to deal with copious amounts of data, organizing it is imperative. Otherwise, you are very likely to get lost in the clutter. Pivot tables are an easy way to understand and organize large amounts of data. Here are some specific things you can do with pivot tables once you learn how to create a pivot table in excel.

Find Unique Values in a Column

Because a pivot table can organize data, you can use it to find inconsistencies, outliers, and typos in your data. You can quickly find these unique values and work with them accordingly. 

Compute Average, Maximum, and Minimum

Learning how to create a pivot table in Excel will give you the ability to compute some of the most often needed calculations. The average, maximum, and minimum of a data set are commonly calculated values. Pivot tables will calculate these values for you in a few steps. 

1. When creating a pivot table, all you have to do to calculate the average is check the field you want to average. 
2. The field will appear in the row labels list. Drag the field from this section to the sum values section.
3. Select the drop down arrow and select Value field setting.
4. Select Average.
5. Finally, select OK. The pivot table will now display the average values of your desired field.

The steps for calculating the maximum and minimum are exactly the same except instead of selecting average from the drop down menu, you should select max to calculate the maximum and min to calculate the maximum.

Sort Table

When dealing with large amounts of data, finding the data point you want is a struggle. Luckily, pivot tables have a function to make finding your desired data points simple. Pivot table rows and columns can be sorted according to alphabetical order, time order, or value order. You can also sub-order your rows and columns as well in the case that some data points are equal according to the primary order. 

Filtering

Filtering removes the data from the pivot table that you do not want to look at so that you only see the data you want to see. To filter a pivot table manually:

1. Select the column header for the column you wish to filter. It looks like an upside-down arrow. 
2. Select all the boxes that you want to filter your Pivot table by. Be sure to deselect Select all.

How to Create a Slicer

Another filtering option when learning how to create a pivot table is to use a slicer. Slicers are a helpful tool in Excel that makes filtering the data in a pivot table quick and easy. When you have to filter data in multiple ways and unfilter it multiple times, a slicer is essential.

1. To add a slicer to your pivot table, begin by clicking anywhere in the pivot table.
2. While in the Home tab, go to insert and then click slicers. 
3. A dialog box will appear. Select all the fields that you want to filter your data by. 
4. Select OK.

Compare Totals

Pivot tables make comparing multiple totals easier. Businesses can quickly compare sales totals for different products and services. Instead of manually searching through the table and adding up the totals by hand for more than one product, you can use a pivot table to automatically add up each product or service and compare them to each other. With this information, they can make informed marketing decisions about what products they should put more money into marketing. 

Create a Second Pivot Table

Did you know you can create a second pivot table from your first one? This is a great option for when you want to break your data down even further than you can with the first pivot table. For example, you might have a pivot table breaking down your sales by quarter, but you want to further break this down by the different services your business offers. You can create a pivot table for each service you offer. To do this:

1. Filter the original pivot table by your desired filter. For example, a specific service you offer.
2. Click any cell within the pivot table and select the PivotTable Analyze tab.
3. Click Field list.
4. Drag Type to the filters list.
5. Once you have it filtered, click any cell in the pivot table again so you can click the PivotTable Analyze tab again.
6. Click Options.
7. Select Show Report Filter Pages.
8. A pop up box wil appear. Select Type.

After completing these steps, you will have a new pivot table for every service you offer. Once you have completed these pivot tables, you will be able to further analyze these pivot tables to inform your decision-making process.

Use Pivot Tables in Excel

Now that you know how to create a pivot table and some of the most common pivot table functions, you can begin using pivot tables for your own business. Up-to-date and accurate data is an important aspect of any small business. A successful business might have pages of data just for sales. Instead of manually going through hundreds of data points for hours, now that you know how to create a pivot table in Excel and how to use it, you will analyze your data in a quarter of the time and make faster and more accurate decisions.

FAQ:

• What is the purpose of a pivot table?
• How do I modify a pivot table?
• How can I create a pivot table from a pivot table?
• What can I do with a pivot table?
• How do pivot tables make data analysis easier?