It’s better if the data is all on one sheet, but if you don’t have that option, multiple consolidation ranges will pull all the data into one pivot table.
In Excel 2003 you can open the Pivot Table and Pivot Chart wizard by choosing Data | Pivot Table and Pivot Chart Report.
We usually maintain the data in multiple worksheets to check the data for each instance and to view them individually but it would be great to merge all the data from different worksheets in Excel spreadsheet to analyze it.
This is our most ambitious [...] VLOOKUP is one of the most well-known Excel functions – and not without reason.
VLOOKUP’s ease of use and simplicity when “looking up” data is unparalleled in Excel.
However, while you’re setting up the pivot table you can create one or more page fields, and create labels for the data ranges.
There are written instructions, and details on the pivot table limitations, on the Contextures website: Excel Pivot Table Tutorial — Multiple Consolidation Ranges This video shows you the steps in Excel 2007, to create the pivot table from multiple sheets, and set up a page field.
As you can see in the screenshots below that we have included three worksheets containing students records in each exam.