Pivot Table

Pivot Table is a tool which is perhaps the most technologically sophisticated component in Excel. It helps us summarize large amounts of data in rows and columns into a concise report. With only a few clicks of the mouse, and no complex formulas, we can summarize thousands of rows of data to show sums, averages, counts and other calculations.

Pivot Table is a very exciting and quick way to slice and dice a large amount of data. Using Pivot Table, we can turn our data inside out, upside down, sideways and backwards to see the business trends.

Some of the major benefits of Pivot Table are: we can rearrange the information in almost any way, we can insert complex formulas that perform new calculations and we can apply formatting to convert it into an attractive report.

Below are the some common uses of Pivot Table:

• Transposing data – i.e. moving rows to columns or columns to rows. Listing unique values in any column of a table.
• Sub-totals, custom formats and make dynamic pivot chart.
• Filtering, sorting and drilling-down data in the reports without using any formula or macro.
• Linking data sources outside Excel to make a pivot table on that data.

Let’s show an example of how to create a simple pivot table using the data below



Suppose the above table is of thousands of rows and we want to get a report for a customer’s monthly saving account. Using the following steps, we can get dynamic report:

Step 1 – Select the data and go to Insert -> PivotTable



Step 2 – After the selection of PivotTable, the window below will open. Make sure in Table/Range, the address is covering all of the data. If you want to put the pivot table in the same worksheet, select Existing Worksheet and put the range for where you want to place the pivot table; otherwise, select New Worksheet. Press OK.



Next window



Step 3 – We want reports for Customer so we select CustomerName at Row Labels. Pivot will remove duplicates of CustomerName here.

Select Month at Column Labels and Sum of Savings at Values. We can also use other functions at Values like Count, Average, etc.



Now we can format the report in any way we want.