There are two main methods to consolidate data from multiple worksheets into a single PivotTable in Excel:

Method 1: Using the PivotTable Wizard (Manual Selection)

1. Click in a blank cell where you want the PivotTable to be placed (preferably on a new sheet for better organization).
2. Go to the "Insert" tab on the Excel ribbon.
3. Click the "PivotTable" button in the "Tables" group.
4. In the "Create PivotTable" window:
- Choose "Select multiple consolidation ranges" under "Choose the location of your data."
- Click "Next."
5. In the "Select the Data" window:
- Click the "Add" button.
- Switch to the worksheet containing your first data set.
- Click and drag to select the entire data range you want to include. (Make sure to include header rows with field names.)
- Click "Add" again (if you have data on additional sheets).
- Repeat steps for each data set on other worksheets.
- Click "Next" when all data ranges are added.
6. (Optional) In the "Layout" window:
- You can choose to create a PivotTable with a "One page field for all worksheets" or separate page fields for each worksheet. (One page field is typically simpler.)
- Click "Finish."

Excel will create a PivotTable using the combined data from all your selected ranges. You can then use the PivotTable Fields pane to arrange your data and analyze it.

Method 2: Using Power Query (For Newer Excel Versions)

1. Go to the "Data" tab.
2. In the "Get & Transform" group, click "From Other Sources" and choose "From Workbook (Connection)."
3. Select the current workbook from the "Select Workbook" window and click "Open."
4. In the "Navigator" pane, check the box next to each data range you want to include. (Hold Ctrl for multiple selections.)
5. Click "Combine" in the "Transform" tab.
- Choose "Append" if your data has the same structure in all sheets.
- Choose "Union" if your data structures differ slightly but have common key fields.
6. Click "OK" in the "Combine Queries" window.
7. Click "Close & Load" in the Power Query Editor.
- Choose "PivotTable Report" to create a PivotTable directly.
- Choose "Table" to load the combined data as a regular table and then create a PivotTable from it.

Whichever method you choose, ensure your data sets across worksheets have a consistent structure and field names for seamless consolidation.