Learning how to create a Pivot Table in Excel is one of the most useful skills you can have when working with large amounts of data. A pivot table allows you to easily summarise, analyse, sort, group, sum, count, total, and calculate data all with a few simple steps. Pivot tables can seem quite daunting to users who have not used them before. Let me show you through some simple steps to get you started using Pivot Tables.

Create a Pivot Table

Firstly we need to start with a data set of information. My data set is relatively small in comparison but I have approximately 100 records in my worksheet.

  1. Open Microsoft Excel
  2. Open an existing workbook containing data you would like to use or you can download a copy of this data.
  3. My data is already formatted as a table, you don’t need to have done this for the pivot table to work.
  4. Make sure your cursor is on a cell which contains the data you wish to turn into a pivot table
  1. Select the Insert tab from the ribbon.
  2. Choose PivotTable from the options (it is the first button on the left).
  3. The Create PivotTable window will appear:
  1. This will confirm the data you want to analyse and also where you want the PivotTable to be placed.
  2. Click OK.
  3. A new worksheet will be created with a blank PivotTable area displayed:

Add Fields to the Pivot Table

Now that you have a pivot table area set up and ready, you need to add the fields from your data that you wish to include in the pivot table structure. I highly recommend you have a good play with the options. There is no right or wrong location to put each field within the structure as it really comes down to how you want to look at the data. What perspective do you want to gain? Do you want a particular field displayed in columns or rows within the pivot table? Do you have a specific field you’d like to be able to filter by within the pivot table?

You have 4 areas to work with within the pivot table area.

  • Filters (Fields go here if you want to be able to filter based on them)
  • Columns (Fields go here to be displayed in column format)
  • Rows (Fields go here to be displayed in row format)
  • Values (Fields go here containing number values you can then calculate)

There are a few ways you can add the fields.

  • tick the checkbox next to each field and Excel will automatically add them into the pivot table areas, OR
  • drag and drop the fields into the area you want them displayed.

Now let’s go ahead and add some fields into the pivot table area.

  1. For my data I am going to add the Make and Model fields into the pivot table along with the Price field
  2. If I just tick the box for each field, Excel automatically adds the Make and Model fields into the Rows area and the Price field into the Values area.
  1. The Price field being added into the Values area automatically means the Sum function is applied to the field data. I can see the total sum for each Make and Model in my data.
  2. If you look at my Rows area, the Model field is first in the list with the Make being second. Excel is starting with the first field and then using the second field in that area as the second level field. In this instance I want to change it so that it groups the make and models correctly. If I drag the Make field and drop it above Model in the Rows area the pivot table will adjust the layout of the data.

Filter in a Pivot Table

At the moment I am seeing a summary of my entire data set. Maybe I’d like to see the sales figures for a specific salesperson within the business. So I’d like to filter out the other salespersons and leave the data for just one, or even two.

  1. Click and drag a field into the Filters area. In my case I am going to drag the Salesperson field into the Filters area.
  2. On row 1 of the worksheet I now have the Salesperson field showing with a drop-down menu in cell B1.
  3. I can click the drop-down menu and choose a specific salesperson to filter the data by and then click OK.
  1. Now I am seeing the data for Make and Model but only for the sales by Belinda Anderson (me!).
  2. If I want to select multiple filtering options at once, tick the Select Multiple Items checkbox and then place a tick in the box for each record you want included in the filter.
  3. To remove the filter, click back in the drop-down menu and choose All then click OK

Sort data in a Pivot Table

Let’s say now I want to just look at the bigger picture here and see my data across one pain field E.g. all the car makes.

  1. I’m going to remove a field from the pivot table
  2. There are two ways to achieve this, untick the option for the field you want removed, or drag and drop the field out of the pivot table area back into the Fields list
  3. I simply just removed the tick from the Model field
  1. I’d now like to put this data into sort order by price (highest to lowest order)
  2. Quickest way to do this is using the right mouse button, right mouse click on any of the cells within the Price column
  1. Choose Sort > Sort Largest to Smallest
  2. The data will be sorted

Apply Number Formatting

Now that we have some data showing in our pivot table, you will see we are displaying the Sum of the Price field. At the moment it’s formatted as a plain number and I’d really like it displayed as currency with the dollar sign and decimal places. Let’s see how we can do that.

  1. I’ve added the Model field back into my pivot table
  2. Click on the Sum of Price field in the Values area.
  3. The Field menu will appear.
  1. Select Value Field Settings from the options.
  1. From the dialog box click the Number Format button in the bottom left corner.
  2. The Format Cells dialog box will appear.
  3. Select the Currency category, make any changes you need, and then click OK.
  4. Click OK again to close the Value Fields Settings window.
  5. The Price field will now be displayed as Currency within the pivot table.
  6. Be aware that if you remove the field from the pivot table and add it back in again later, the number formatting will not be applied and you will have to repeat the process.

I hope this has given you some useful tips to begin your journey working with pivot tables in Excel.

Reference: https://www.thetraininglady.com/pivot-table-excel/