'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Create a PivotTable from Another Worksheet in Excel for the Web
Question details
The user wants to generate a PivotTable in Excel for the web using a data range that is located on a different worksheet.
- Product
- Excel for the Web
- Device & OS
- not provided
- Scenario
- Analyzing data online and attempting to reference source data on a separate tab to build a new PivotTable summary.
- Observed behavior
- Excel for the web restricts creating PivotTables directly from data ranges on another worksheet. It currently only permits generating a PivotTable from a data range in the current active sheet or a connected Power BI data source.
Ensure your source data has clear column headers and no completely blank rows or columns, as these are required for creating an accurate PivotTable.
Copy the Source Data to the Current Worksheet
Since Excel for the web limits cross-worksheet references for new PivotTables, you can temporarily duplicate the source data into your active sheet.
Excel for the web has a functional limitation preventing the direct creation of a PivotTable using a data range from another worksheet. The easiest workaround is to copy the target data into the worksheet where you intend to build the PivotTable.
Navigate to the worksheet containing your source data. Highlight the entire data range, including all column headers, and press Ctrl+C (or Cmd+C on Mac) to copy it.
Go to the worksheet where you want to place your PivotTable. Select an empty area with enough space, click a starting cell, and press Ctrl+V to paste the copied data.
Highlight the newly pasted data range. Navigate to the 'Insert' tab on the top ribbon, click on 'PivotTable', and follow the prompts to generate your table.
Easily Create Cross-Worksheet PivotTables with WPS Office
Bypass the limitations of Excel for the web by using WPS Office. As a powerful, free desktop alternative, WPS Office allows you to create PivotTables from any worksheet instantly without needing to duplicate data.
- 1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your existing spreadsheet containing the data.
- 2. Initiate the PivotTable tool: Navigate to the 'Insert' tab and click on 'PivotTable'. Choose where you want the PivotTable to be placed.
- 3. Select the external data range: In the 'Select a table or range' field, click the selection icon, navigate to the other worksheet, and highlight your source data directly.
Frequently Asked Questions
Can I create a cross-worksheet PivotTable on the desktop and view it on the web?
Yes. If you create a PivotTable that references another worksheet using the desktop version of Excel (or a compatible desktop program like WPS Office), the PivotTable will display and function normally when you open the file in Excel for the web.
Does Excel for the web support external workbook connections for PivotTables?
Currently, Excel for the web only supports external data connections for PivotTables if the data is hosted as a Power BI dataset. Standard external links to other workbooks or worksheets are restricted during creation.
Why is the PivotTable option greyed out in my Excel web view?
The PivotTable option may be disabled if you have multiple worksheets grouped or selected at the same time, if you are working in a restricted editing mode, or if the selected data lacks valid column headers.
