Pivot Tables – Excel Tool for Data Analysis

CA Preeti Gupta

As we are all aware excel is widely used for accounting and financial purposes.

In the past they were used only to perform basic moths, such as adding, multiplying, subtracting and dividing. Today’s spreadsheets can be significantly different and far more advanced. A CA office cannot work without excel. But do we CA’s utilize excel to its optimal? Sadly no!  Most of us never realize the full potential of using Excel. EXCEL has numerous features which can reduce your time & your client’s time in day to day analysis and reporting.

These features can simplify your work in relation to Auditing, Investigation of frauds, MIS Reporting, Compiling data for Income Tax Assessments or Vat Audit.

In this article we shall deal with one of the most dynamic & import feature of Excel: Pivot Table:

Pivot Tables are interactive table that allow the user to group and summarize large amounts of data in a concise, tabular format for easier reporting and analysis within matter of minutes.

One limitation of the standard Excel worksheet is that you can’t easily change how the data is organized on the page. For example, in a worksheet in which each column represents an hour in the day, each row represents a day in month, and the body of the worksheet contains total sales for every hourly period of the month, you can’t change the worksheet quickly so that it displays only sales on Tuesdays during Afternoon.

One advantage of Pivot Table is that it allows you to sort, filter, rearrange, hide, and display different category fields within the Pivot Table to provide alternate views of the data.

Steps in creating Pivot table:

Step 1: Entering the Pivot Table Data

The first step in creating a pivot table is to enter the data into the worksheet.

i. Data should be in tabular format.

ii. Headings. This is a must! They should always be in the row directly above your data.

iii. Avoid blank rows or columns when entering the data. This includes NOT leaving a blank row between the column headings and the first row of data.

iv. It is important to enter data correctly. Errors, caused by incorrect data entry, are the source of many problems related to data management.

Data can also be imported from various sources.

In the example we have tabulated sales data of a company. Various columns are Date, Product, State, Customer and Revenue.

(You can access the excel working sheet through this link)

 Step 2: Insert → Pivot Table → Wizard as shown below will be displayed → Select source data → Select worksheet where Pivot Table is to be displayed → Click Ok     

Step 3: Pivot Table Field List Task Pane will be displayed as under:

Pivot Table contains four areas namely Report filter, Column Labels, Row Labels & Values.

All the headings in our data are fields.

Just drag fields into particular area to get the desired reports.

Below example shows State wise, Product wise revenue.

State field is dragged into Row area. Product in Column area & Revenue in Value area.

We have now observed how Pivot Tables can be easily used to compile and analyze large voluminous data in just few clicks!!!

Changes in Pivot table:

It is very simple to make changes in Pivot Tables.

If in above example Product wise, state wise Sales are required. Just move state to Column area and Product to Row area.

Or State Wise, Customer wise, Product wise Sales is required. Add Customer field to row area.

 Similarly if we want Quantity details along with revenue. Quantity field can be inserted in Values.

Numerous reports can be created from any given set of data. The possibilities are endless and the reports are displayed at just flash of a moment.

It is important to note that the order in which you enter the fields in the Row labels and column labels areas affects how Excel organizes the data in Pivot Table.

We find the best part about Pivot Tables is their ability to be manipulated via ‘Trial-and-Error’ and immediately show the result. If its’ not what you expect, simply use the Undo feature and/or have another go!  What you must always remember is that you are not changing the structure of your original table in any way at all, so you can do no harm!

Well, basically Pivot Table allows us to pivot our data via drag-and-drop to produce meaningful information. This makes Pivot Tables interactive .Once the table is complete we can very easily see what effect moving (or pivoting) our data has on our information. This will become patently clear later on, once we have produced a Pivot Table. Believe me, no matter how experienced you get at Pivot Tables there will always be an element of trial-and-error involved in producing the desired results! What this means is you will find yourself pivoting your table a lot!

Drill down to detail:

It is also possible to drill down to the details of the report by double clicking on particular cell.

In above example, If we double click on Product 1: Maharashtra i.e. Rs 10600000. Details as under will be displayed on separate sheet.

Refresh Pivot Table when source data Changes:

If any changes are made in source data, these are not directly refreshed in Pivot Table. Doesn’t worry you need not create Pivot table all over again. You can simply right click in your Pivot Table and select Refresh Data from the popup menu.  Excel pops up an information message telling us the Pivot Table report has changed. Simply select OK.

Filtering Data in Pivot Table:

Filter option is available on row and column labels within the body of Pivot Table. Clicking either of those headers enables you to select a field by which you can sort or use filters or advanced filter option.

If in above example we want State wise sales of Product 1 & Product 2 only:

Similarly one can use advanced filters options also within pivot table.

Similarly data can be sorted in Pivot Table as required.

Change in field Settings:

One can change field setting of data in Pivot Table. For e.g. if instead of Sum of revenue, we want Average Revenue.

Right click →Value Field settings → Select Average → Click Ok.

 There are many other options in Value Field settings namely: Max, min, count etc. 

Formatting cell in Pivot Table:

We can easily format a cell in Pivot Table as we do in normal excel worksheet. Just right click and format. It helps to emphasis on particular data or presenting the data as desired.

Layout and Format of Pivot Table:

Right Click → Pivot Table Options → One can select from various options available and obtain desired layout

Creating charts from Pivot tables:

It is rightly said a diagram is worth hundred words. We can create charts from Pivot Table. Any changes made in the Pivot Table on which the Pivot Chart is based are reflected in the Pivot chart. For e.g. If you filter the content in Pivot Table the filter will be reflected in Pivot chart. You can also filter data in Pivot chart directly.

Pivot table Tools → Options → Pivot Charts → Select the appropriate chart type.

In this case we have selected Columnar Chart. 

We can now filter and view chart just for Product 1 & Product 2:

One can easily customize the appearance of chart created through Pivot table as in case of normal charting function. Select the chart layouts and Chart styles from various options available.

As a Chartered Accountant, Pivot Tables and Pivot charts can simplify numerous tasks on daily basis. To give a few examples:

1)   In MIS reporting: Party wise sales analysis, Product wise sales analysis, Area wise sales analysis, and Monthly sales analysis. Cost analysis. Expenses analysis. Profit analysis, Employee Performance Analysis, Payroll Function, Budgeting, Ratio Analysis etc etc….

2)   Fraud investigation.

3)   Effective Audit tool to summaries and analyise data and find variances.

4)   In can simplify work of preparing & consolidating data for Income tax Assessments.

5)   Preparing Bank summary from Bank statements available in Excel.

6)   Summarizing Party wise Sales, Purchases, and Vat etc for Vat Audit.

and the list goes on…….

No wonder Pivot Table is a very powerful analysis tool.

(The author, Preeti Gupta, is a Practicing Chartered Accountant in Mumbai. She can be reached at ca.preeti.279@gmail.com)