Flout Formula For Excel PivotTables

Sat 4th February 2012

To compare and contrast figures there'll need to be calculations of some sort and Excel has so many options that you could feel overwhelmed - which is when PivotTables can come to your rescue.

Formulas are the backbone of many an Excel spreadsheet and the programme offers various ways to compare statistics. In fact, the software is so diverse that some of its users don't know where to start and either stick to simple summaries, or highlighting various information and explaining themselves the impact of the data, rather than show it via the programme. It seems a shame to leave figures stuck in cells, especially when Excel could help you transform spreadsheets into informative reports that illustrate what you are trying to express.

It may be tempting to leave worksheets in a simple format of columns and rows if you have a small spreadsheet, but once it starts to accumulate large amounts of data then PivotTables can help you spot trends and show off figures. If you're unsure how well Excel is being put to use within your company then a training needs analysis is useful for identifying issues of productivity and highlights where tutorials could prove useful.

Take the work out of worksheets

Let's say you're looking at a range of data about employees in your firm and their sales figures. You'd like to compare and contrast this information with the number and type of training courses that they have completed. Rather than applying individual formulas and coming up with entirely new spreadsheets, instead you could create a PivotTable, without the need to use any formula.

This element of Excel can prove very useful as it's a quick way to view the information you need. Icons allow you to quickly navigate to the necessary functions needed to do this within the programme. By using simple drag-and-drop technology you can take field data (from the original spreadsheet) and use it to build your PivotTable. This would then allow you to compare the type of training with the number of sales in seconds.

Refresh and rearrange

Another useful aspect to PivotTables is that they are entirely flexible. Once you have created one, it can also be changed in seconds to reflect other kinds of data, this is more difficult to do with spreadsheets. So, if you'd prefer to compare the amount of profit generated by one employee (rather than the number of units sold) with their training qualifications then this is possible, and could also highlight areas where a full training needs analysis may be useful.

As well as being able to rearrange PivotTables so easily, you're also free to refresh them when new data hits the original spreadsheet. This means that if you change the values and add that colleagues have completed extra training courses, this will be reflected in the PivotTable so it is kept up to date.

PivotChart your progress

Excel has several formatting options that give you the chance to highlight statistics when they are in a simple spreadsheet. You can use sparklines, for example, that give an easy-to-understand visual representation of how the information in a cell compares to that held in a different cell. There are similar options when it comes to PivotTables, so you can create graphs of the information. These are called PivotCharts and are great for revealing the statistics summarised by PivotTables.

Author is a freelance copywriter. For more information on excel training uk, please visit https://www.stl-training.co.uk

Original article appears here:
https://www.stl-training.co.uk/article-2039-flout-formula-excel-pivottables.html