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.

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.

If you do not want to use pivot table, you can also create magazine-like presentation. This Excel eBook will show you how.

Charley (the author of this eBook) has ingeniously used some of the most basic functions in Excel to create professional reports for management reporting. You do not even need to know macro to do that. The solutions are ingenious uses of basic functions and this means you could make use them with minimum effort.

His solutions also cover automatic updating, which is something you can do without if you are preparing the report on regular basis.

I have also purchased the eBook just to find out how he achieves the following tasks.

1) Make your chart change color based on the latest result presented, e.g. the chart could change to green if the sales rises, amber if it is flat and red if sales fall, just like the diagram shown below:

2) Synchronized the charts such that the y-axis in all the charts reflect the same scale.

3) Printing the report in portrait and landscape orientation with the page numbers printed at the same position?

4) Prepare magazine-quality figures.

I did try searching the web for the solutions but they cannot be found anywhere. That’s why I end up purchasing the book to find out how he did it.