It is possible to use Excel for data entry and miss out some of the tools that can make analysing and summarising data easy-peasy. For me, this was Pivot tables. I avoided Pivot tables because a) I didn’t know what they were and b) I thought it would be too difficult to use as they sounded complicated.
My solution needed a calm and experienced trainer and an Excel intermediate course to see the benefits of the Pivot table, with ready-made data to practise on (so I couldn’t break anything). Here is what I’ve learnt….
A Pivot table is a tool used for creating reports that summarise the data in your database. They can be created in a few mouse clicks (yes, really they can…).
Why is is called a Pivot table?
The pivot part refers to the way you can use a table to analyse data from different angles and perspectives. The pivot part means you can move around your information, manipulate it, rotate it, group items, turn the data around to present it in the most digestible format for your needs.
Pivot tables are dynamic and interactive.
With Pivot tables you can create summaries with whatever data you want to focus on, update them with new data by refreshing the table, and even add formulas to help you make new calculations.
Pivot tables are flexible.
This means that you have the flexibility to choose what you want to summarise and how you want to show it (by frequency, by month, by team, by customer, by account etc).
Pivot tables are easy to update.
This means when you enter or amend data into your database source, you can update your pivot table with the new figures by refreshing the content.
You can customise they way they look.
You can use the Pivot table to create professional reports and customise them to meet your company branding.
Why are Pivot Tables useful?
They Analyse data to meet your needs with a visual summary.
It is much easier to analyse data in a large data sheet if it can be broken down into usable chunks of information, that are easy to read and understand.
For example, if you wanted to analyse expenditure across five departments to identify trends, spot good practise (who is keeping overheads low) and see if there could be budget savings, or map expenditure to sales by month to identify gaps.
You can use Pivot tables to answer specific questions about your data.
You have the flexibility to find out answers from your data. Anything you are keeping in your database can be queried. Such as: Do you want to know which month of the year has the most staff requests for annual leave? Who is the member of staff generating the most sales? Are there peaks of staff illness in the last year indicating a problem which could be tackled by addressing office environment?
Now you have the definition and basics of why pivot tables are useful, you are closer to getting more out of your data. Take the plunge and explore with an Excel intermediate course.
