Quick Excel Tips to Boost Productivity Easily – PivotTables

A PivotTable is a table that summarizes a more extensive data range in a Microsoft Excel worksheet. If you have loads of data in a worksheet, you can use a PivotTable to analyse based on each available variable. Here are some of the ways a PivotTable can help you make quick data analysis:

• Segment data by date or any available variable in your data range.
• Create subtotals of the whole data within moments.
• Run automatic calculations on counted values.
• Organise data into columns and rows in order to run automatic calculations.

How It Works

A PivotTable lets you reorganise or summarize specific rows and columns in order to analyse data and create a desired report. It allows you to see subtotals of individual columns out of a series easily and instantly. Where you have very long rows and wide columns, a PivotTable makes it easy to make sense of certain aspects of the data without analysing everything in your Microsoft Excel worksheet. When you create a PivotTable, you are ‘pivoting’ it along a certain axis to have a different perspective without changing the content of the rows and columns.

PivotTables are pretty simple to add, and yet they create a powerful impression for the data analyst. Simply select the data range you wish to analyse from your Microsoft Excel worksheet. Then click on insert and select PivotTable. Under the drop down menu, select PivotTable again, and then choose whether you want Excel to insert the table. You can have it created in the same worksheet or another worksheet. When done, click OK. Consider the data range below:

Now we can create a PivotTable to analyse this data at any level at all, be it the vehicle colour, its mileage, price or number of seats. First, click on Insert and then PivotTable on the Menu as follows:

Then choose the data range by editing the values in the popup or selecting it manually.

Once you click OK, the PivotTable will be created in a New Worksheet where you will have the following:

In the PivotTable Field List, tick the fields you wish to have in your table to do your analysis. For example, we want to see the different vehicles by colour and number of seats. Clicking on the respective values will give us the following table:

If we only wanted the vehicle colours and corresponding mileage, we would have something like this:

What’s good about PivotTables is that even after creating one, you can still edit which values you want Excel to show. For example, we can choose to not see values for the colour black. We do this by clicking on the arrow next to Row Labels. From the drop down menu, we deselect Black in the list of available colours. Instantly, the PivotTable changes to reflect our new selections as follows:

Say you have population figures of two million people with respective columns for age, sex, income, etc. A pivot table will help you quickly check income levels for all females over a specific time period without you having to go through all rows and columns of the data. Though seemingly complex, a few tries should make you master the art of Microsoft Excel’s pivot tables.

Try your hands on it and let us know how it goes. If you have any difficulties, don’t hesitate to leave a comment. We’ll come to your rescue.