Want to Learn about Excel Pivot Tables? Part 2. By David Carter

Once you've mastered the basics of pivot tables Want to Learn about Pivot Tables? Start here try of these more advanced tutorials. The first, on using vlookup, is essential.

Tutorial: Using the VLOOKUP function
A common problem when you export data into Excel is that you can't include certain essential fields in your data file. Don't worry. You can create a second file that holds these fields and use Excel's vlookup function to pull them into the main file. Anyone who uses pivot tables seriously will find themselves regularly using vlookup. Tutorial: using the VLOOKUP function to add fields

Tutorial: Consolidate multiple data ranges
Sometimes you will be supplied with data in the form of multiple spreadsheets, eg annual budgets from a dozen departments. They need to be consolidated into a company total. One advantage of consolidating in a pivot table via Multiple Data Ranges is that the spreadsheets don't have to be in exactly the same format. Consolidations with pivot tables - putting together a budget

Find out how to compile different worksheets into one summary pivot table that allows you to rotate and redisplay the data. Use this feature if, for example, you need to combine budgets from several departments into one company summary.

Tutorial: Financial modelling
This is another specialist area. To be honest, when we published the Modelling and "What If?" Analysis with Pivot Tables , some readers found problems with double counting in Excel. So I'm not sure how good pivot tables are for financial modelling, but it's worth a look because this is a very unusual but very simple way of using a pivot table.

In this exercise you are a magazine publisher who is forecasting future income streams dependent upon variables such as the number of new subscribers anticipated per month, number of pages per issue, estimated advertising rate per page, etc etc. It shows how, by using a pivot table for the underlying structure, you can build a model that is reliable, that is properly documented and whose structure can easily be changed at a later date.

Reporting Challenges
Several readers have asked for help in using pivot tables in their own company. They've provided sample sets of their data (suitably disguised) for the experts to come up with a solution.

Working your way through these real-life problems and suggested solutions is a valuable exercise in itself. It's also fun to read the opinions of readers. Invariably, there are heated arguments about whether Excel is suitable at all, and should a proper database like MS Access be used?

In the Report Planner Challenge reader Michael Norris was taking time sheet data from managers and trying to produce reports.

In the Stock Reporting challenge 1 and Stock Reporting - the answer reader Ray Yaukip was calculating stock balances on a pivot table and wanted to work out total movements between two dates.

If you have a problem and are willing to supply some sample data for the experts to look at, please email us at editor@accountingweb.co.uk