Take a look at our selection of Pivot Table tips to make your day easier
Analysing data has become an important part of any modern organisation. It can optimise our time and it can help us to make better and quicker business decisions. However, when you have a lot of data and aren’t an expert, it can be difficult to know where to start. Pivot Tables in Excel are a great place to start and can be helpful in the process of getting data ready to analyse.
PivotTables are highly flexible and can be quickly adjusted depending on how you need to display your results. You can also create Pivot Charts based on Pivot Tables that will automatically update when your Pivot Tables do.
Below you can see a simple example of the use of Pivot Table to organize data.
Based on this data you can even create a Pivot Chart.
Work with Pivot Tables can be complex, if you are not already familiar with them. The very first step is to check if the source data is suitable with the Pivot Tables feature. Data in lists tends to be the most suitable, as you could see in the example above.
Pivot Table now available in Excel Online
Recently Microsoft made available Pivot Table in the Excel Online. Check below step by step to do it online:
1 - Select the table or range in your spreadsheet.
2 - Go to Insert > PivotTable.
3 - Excel will display the Create PivotTable dialog with your range or table name selected.
4 - In the Choose where you want the PivotTable report to be placed section, select New Worksheet, or Existing Worksheet. For Existing Worksheet, select the cell where you want the PivotTable placed.
5 - Click OK, and Excel will create a blank PivotTable, and display the PivotTable Fields list.
Working with Pivot Table values
Summarise values by
By default, Pivot Table fields that are placed in the Values area will be displayed as a SUM. If Excel interprets your data as text, it will be displayed as a COUNT. That’s why it's so important to make sure you don't mix data types for value fields. You can change the default calculation by first clicking on the arrow to the right of the field name, then select the value field settings option.
Next, change the calculation in the Summarize Values By section. Note that when you change the calculation method, Excel will automatically append it in the Custom Name section, like "Sum of FieldName", but you can change it. If you click the Number Format button, you can change the number format for the entire field.
Tip: Since the changing the calculation in the Summarize Values By section will change the PivotTable field name, it's best not to rename your Pivot Table fields until you're done setting up your Pivot Table. One trick is to use Find & Replace (Ctrl+H) >Find what > "Sum of", then Replace with > leave blank to replace everything at once instead of manually retyping.
Show Values As
Instead of using a calculation to summarise the data, you can also display it as a percentage of a field. In the following example, we changed our household expense amounts to display as a % of Grand Total instead of the sum of the values.
Once you've opened the Value Field Setting dialog, you can make your selections from the Show Values As tab.
Display a value as both a calculation and percentage.
Simply drag the item into the Values section twice, then set the Summarize Values By and Show Values As options for each one.
Excel Training
Even though we have loads of resources available online the best way to qualify yourself is investing in training with certified and qualified instructors. At New Horizons Ireland we provide a wide range of Microsoft Office courses led by experienced staff. Our Excel courses are divided into 3 levels, so you can choose the best option according to your previous knowledge.
Excel Dublin Clasroom Dates
Data Analytics Training
-
*This article is based on information provided by the Microsoft available here.
For no better reason than spring is in the air, we are having a promotion on our Excel classroom dates for the first week in April.
It's called Excel power week when each course is only €99 per person using discount code power.
Click below to view the outlines and use discount code power to book any date in the first week of April for €99.
Discounted dates:
*Offer applies to Dublin classroom dates for the first week in April for the above Excel classes only. Cannot be used in conjunction with any other offer.
Complimentary Office Skills Assessment
Looking to find out what level of Excel, Word or PowerPoint you need to enrol in or simply looking to find out your level of expertise in Office? Well click the link below and take the test no, completely obligation free.
Free skills assessments for: