Pivot Table
Pivot table is the third function you should learn after sorting and Auto-filter.
Yes, Pivot Table is labeled as an Advanced Excel function in a lot of places. But the truth is, it is one function that can help you get things done especially if you are preparing reports or doing some analysis.
With Pivot Table, you do not have to learn worksheet functions like SUMIFS and COUNTIFS. The numbers are auto-calculated when you put them into Pivot Table. All you have to do is to learn how to drag the drop the fields into the Pivot Table report. It is 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 on 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.
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.
e. Drag the fields into the respective places as shown below and click ok.
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.
