How to Create a Pivot Table using Multiple Sheets in Google Sheets. Step 1: Validate the Source Datasets. Step 2: Consolidate/Combine the Datasets. Option 1: Using Regular Cell Reference to Retrieve the Data as an Array. Option 2: Using QUERY Function (Recommended) Step 3: Create the Pivot Table. Final Words. Step 3: In the ‘Create pivot table’ box, if you want to display your pivot table in a new sheet, then select the radio button next to ‘New sheet’. If you want it in the same sheet, select the radio button next to ‘Existing sheet’. The data range option lets you choose the data set you want to use for your pivot table. In the Pivot table editor that appears on the right side of the screen, click Add next to Rows and choose Region. Then click Add next to Values and choose Revenue: By default, the pivot table will attempt to summarize the Revenue values by using SUM. To instead display the MEDIAN of Revenue, click the dropdown arrow under Summarize by and click On the Pivot table editor panel, add a “Calculated Field” to Values by clicking the “Add” button against “Values.”. Remove the existing formula, i.e., =0 in the formula entry field that appears. Select “Custom” under summarize by. Insert the following XLOOKUP formula. Click outside the formula field to activate it, and voila! To combine columns horizontally in Google Sheets, follow these steps: Type =ARRAYFORMULA ( to begin your formula for combining columns. Type the address for the first column that you want to combine with, such as A1:A. Type the address of the other column that you want to combine with, such as B1:B. Press enter on the keyboard. Add or edit pivot tables. On your computer, open a spreadsheet in Google Sheets. Select the cells with source data you want to use. Important: Each column needs a header. In the menu at the top, click Insert Pivot table. Click the pivot table sheet, if it’s not already open. In the side panel, next to "Rows" or "Columns," click Add, then You can also do this by using the Pivot Table. Just by using one more Query formula in the last Sheet named “Consolidate,” we can achieve the required result. =query (Combined!A1:H10,"select D, sum (H) where A is not null group by D") See this formula explained with the help of an image below. Follow the steps below to connect to the DSN in Power Pivot. In Excel, click the Power Pivot Window icon on the Power Pivot tab to open Power Pivot. Launch the Table Import Wizard: Click the Get External Data from Other Data Sources button. Select the OLEDB/ODBC source option. Click Build to open the Data Link Properties dialog. Mb0YM2.