Pivot Table

Pivot table is one of the great functions ever put into Excel. Its drag and drop function make it so easy to use that minimum or no training is required. A demo is what it takes to use them. That is for the user. Training is however required to set up this Excel built-in function and to learn about the options to make it easier for the user to manipulate it.

Another key feature is the ability to filter the records conveniently. For example, users can filter the sales data to look at the records for a particular month at the click of a button.

The info can be presented in multiple levels. For eg., U can present the details in product group, followed by product name, then product dimension.

Like Crystal reports, you can make use of it to retrieve data from desktop databases (e.g. Access, DBase IV) and database servers (sql servers, mysql servers). While its capability is not as powerful as reporting tools such as Crystal reports, impromptu, business objects, it is able to meet the needs of most users at no cost to the user since Excel is a basic office productivity tool.

Here are the steps to create a pivot table:

a. Select Data -> PivotTable and PivotChart Report.

b. In step 2 of 3 of the wizard, highlight the data range. Select the option Excel list and PivotTable and click the Next button.

pivot table wizard step 1

c. Highlight the range of the data source for the pivot table.

d. When you come to the wizard step 3 of 3, click on Layout button.

pivottable_table_wizard Step 3

e. Drag the fields into the respective places as shown below and click ok.

pivottable_table_wizard Step 3a

f. At Step 3 of 3 of the wizard, click on the Finish button and a simple pivot table will appear on a new worksheet.

You can format your pivot table with colours, fonts, etc to make it professional enough for your presentation without buying any add-ins or solution. The solution given here is the same and you have more control over your pivot table presentation. The step-by-step guide to format pivot table is given on this page.




New! Comments

Have your say about what you just read! Leave me a comment in the box below.