Advanced Excel and PivotTable Reports

One of the many features of Advanced Excel is the PivotTable. A PivotTable is an interactive table that automatically extracts, organises, and summarises your data in report form, with the assistance of a Wizard.

Pivot tables allow you to quickly summarise and analyse large amounts of data in lists and tables - independent of the original data layout in your spreadsheet - by dragging and dropping columns to different rows, columns, or summary positions.

You can use PivotTables and PivotCharts to analyse the data, make comparisons, detect patterns and relationships, and discover trends. A PivotTable organises and then summarises large amounts of data from any range that has labelled columns and rows. Any number of column headings can act as category items.

The PivotTable is known as such because the headings can be rotated around the data so as to view or summarise it in different ways. You can filter the data to display just the details for areas of interest. Alternatively, you can choose to create a PivotChart which will summarise the data in chart format rather than as a table. An advanced Excel course, led by a Microsoft certified trainer, is highly recommended as you will gain the necessary knowledge to make effective use of Advanced Excel, including PivotTables and PivotCharts.

PivotTables have for a long time been very useful for accountants, but have great advantages to managers too, as a data analysis tool. The source data for the PivotTable can include an Excel worksheet database or list, or any data range that has labelled rows and columns. This data can be in Excel or in an external application such as Microsoft Access. The general rule is that you need more than two criteria of data with which to work otherwise there will be nothing to pivot.

PivotTables are especially well-suited for taking enormous amounts of data and summarising that data into useful reports which can help you analyse your data from multiple perspectives, making it easier for you to discover patterns and exceptions and therefore make faster, better business decisions. In fact, PivotTables might just be the single most powerful tool in Excel for budgeting, financial reporting, and data analysis.

A few of the many advantages of PivotTables are that one can generate and extract meaningful information within a matter of minutes, and the fact that they do not use up a lot of memory from the computer. The drag-and-drop functionality makes PivotTables interactive in that once the table is complete you can very easily see what effect moving, or pivoting, the data has on the information.

Having the information update each time the workbook is opened and/ or by clicking the refresh tab is another feature. Some of the additional features of PivotTables are that they work well with other Excel tools such as AutoFilters, subtotalling, grouping, outlining, and publishing to the web, sending email, and copy and paste linking to MS Word and MS PowerPoint.

In conclusion, some of the many features of PivotTables are that you can summarise and analyse data, quickly rearrange the layout of the report, and they are a convenient way to build intelligent, flexible summary tables.