A Pivot Table is a tool that you can use in Excel to be able to view your data in multiple ways. You can make quick summaries based on different scenarios or create quick charts; all with little effort and without using formulas.
If you’re already familiar with Pivot Tables, this article is a good refresher but this tutorial is geared towards those who are unfamiliar with Pivot Tables, or who understand what they are, but not how to create one.
How Pivot Tables are Useful
Let’s say that you have a large spreadsheet with many columns of data. This may be a spreadsheet of orders, transactions, employee info, etc. The larger a spreadsheet gets, the harder it is to get summaries without using some fancy formulas. This is where your Pivot Table will come in handy. With just a few clicks, you can get summaries sliced and diced however you like.
Let’s Create a Pivot Table
Below, is some basic order data in a spreadsheet.
Let’s say that I needed to sort this data by Vendor Name. Here is how I would do that with a Pivot Table.
- Select the cell that you want your Pivot Table to begin. For larger sheets, I recommend creating a new sheet (bottom tab) to put your pivot in.
- From the Insert Ribbon (top tab) choose the arrow below Pivot Table, then choose Pivot Table.
- In the popup window Create Pivot Table, put your cursor in the open field for Table/Range. Then select the entire range of data that is going into the Pivot Table. For this example, the whole data group will be selected, if you have a large spreadsheet, you may only want a select few columns or rows.
- When this Pivot Table was started, the cursor was already in the right spot, so there is no need to change where the Pivot Table will go. But if a different location is desired, you can select that location in the Existing Worksheet section.
- Select OK to move to the next part.
Getting your Data in the Right Place
When the Pivot Table is created, it is going to look like this:
This tells us nothing. That is because we have to add the data to the right spot.
To the right of the Pivot Table, you will see the Pivot Table Fields window. This is where the magic happens.
The 3 areas we are going to focus on are the Columns, the Rows, and the Values.
As mentioned above, this Pivot Table will be sorting these orders by Vendor. So, I am going to put Vendor Name in the Rows section.
- Select Vendor Name in the Pivot Table Fields window. If it goes into the incorrect section at the bottom you can select it and drag it to where you want it. In this scenario, the Vendor Name should be in the Rows section.
Let’s observe the data in the Pivot Table now. Notice that the original data Vendor Name column has more items listed than it does on the Pivot Table. This is because you’re only going to see one of each reoccurring item in the Pivot Table.
Next, drag the Amount of Purchase to the Values Section.
You can select the drop-down arrow of your Amount of Purchase in the Values section to change Sum to Min, Max, or other variables. In this scenario, we are keeping the Sum.
Your result is the sum of all orders in the table that were for each vendor.
Now let’s add some dates. Select the Date of Purchase Pivot Table Field and put it in the Columns section.
Notice that it automatically broke it down into Years and Quarters. (This is a newer feature, very handy in my opinion!)
It looks a bit jumbled. There is too much information here.
To fix that, unselect the Date of Purchase and Quarters to get your dates sorted by Year.
Let’s say now you want to see the same data as above, but you want to know each customer that matches up with the vendor.
- Select Customer Name and put it below the Vendor name in the Rows Section.
And this is what you will see.
Think of all the ways you can slice and dice this data. How can a Pivot Table help you in your own work processes?
Let’s do a Pivot Chart
An offshoot of the Pivot Table is a Pivot Chart. This is basically the same thing using a visual representation.
Same as when creating the Pivot Table, put your cursor in the cell where you want to create the chart. Then select the Insert Ribbon, Pivot Chart and Pivot Chart.
Select your table range, the same as you did with the Pivot Table.
For the below example, I put the Vendor Name in the Columns section and the Amount of Purchase (sum) in the Values section.
And here is the result:
There are endless possibilities with what you can do with Pivot Tables. They can make your life easier! How do you think you can benefit from using Pivot Tables or Pivot Charts?
We only scratched the surface in this quick tutorial. Pivot Tables can become massive and can help you analyze data quickly and efficiently. Don’t forget, Harbor Computer Services offers training in just about any technology area you can think of. If you desire training on Pivot Tables, or if you want help with your current Pivot Tables, we’ll be happy to help. Just give us a call!
About Harbor Computer Services
Harbor Computer Services is an IT firm servicing Southeastern Michigan. We work exclusively under contract with our clients to provide technology direction and either become the IT department or provide assistance to the internal IT they already have. We have won many awards for our work over the years, including the worldwide Microsoft Partner of the Year in 2010. Most recently we were recognized as one of the top MSP’s in the nation by ChannelFutures coming in at #40 nationwide. And in 2016 as the top Michigan IT firm for Manufacturing. There are a few simple things that make Harbor Computer Services the best choice for your business. •We are Professionals •We are Responsible •We care about your business
