Creating a Pivot Table in Excel…how do you do it? Watch the video to find out

Creating a pivot table and chart in Excel – how do I do this? Find out how this week below…

creating a pivot table in excel: Excel iconThis week’s hint and tip is about pivot tables and how to create them in your spreadsheets in Excel. This is on our Advanced Excel training course but as they are so popular we decided to do a hint and tip on it too. We are going to go through it now below.

 

Pivot Tables

One of the most useful and powerful features companies can use in Excel is the ability through Pivot Tables to analyse and summarise data. We deliver the following training locally:

  • IT Training Warwickshire
  • IT Training Worcestershire
  • IT Training Gloucestershire
  • IT Training Herefordshire

We also train IT in other counties and always seek to provide the essential skills in Excel and there’s no better way to analyse data in Excel than through a Pivot Table so… Firstly, what is a Pivot Table and Chart and secondly how simple is it to create one?

 

Data Sheet

It is really important to set up your list with details of the transactions in a certain way. Follow these rules and it’s a great start:

  • All your data on 1 sheet (even months/years as it’s very easy to filter by month/year)
  • Row 1 to contain field column headings: all different and not too long
  • Rows 2 onwards with no blank rows and no blank columns to contain your transaction data
  • No merge cells anywhere

 

Pivot Table layout

Once you have your Pivot Table again follow these steps:

  • Fill in the 4 boxes at the bottom right of your screen which allow you to design the PT with column/rows/filters and at least 1 value. The value may well be a quantity/sales value/product margin etc.
  • Drag and drop these fields (this is simple to do) until you are happy with the layout
  • Use Slicers to make filtering of the data easy

Below is a video which takes you through a live example on creating a Pivot Table and then a Pivot Chart.

Once you have these in place it’s a case of updating your transaction data and updating the data source so that the PT takes on board the latest data.

We hope you liked this hint and tip on creating pivot tables in Excel, why not take a look at our previous one on the BODMAS concept?

 

We have delivered Excel training courses in Worcestershire and Herefordshire recently where Pivot Tables have made a major impact on the efficiency of those businesses.

For all details of our Excel courses and details of IT training Herefordshire as well as the other counties we train in, please visit our web site here or phone us 07903 840105.