In this window, go to the “Data” tab. In the Data Tab, Uncheck “Save Source Data with File”. After doing this, Save the file again. Now, proceed with “Show Filter Report Pages”. It will create multiple worksheets in the same file. Go to each worksheet and Move/Copy it to a new file and save it. Steps: Right-click on the Pivot Table. Select PivotTable Options…. A Pivot Table Options box will appear. Click on the Data tab. Now, check the Refresh data when opening the file box. We will have the refreshed Pivot Chart when opening the file. Read More: Use Excel VBA to Create Chart from Pivot Table. Step 2: Create Your Pivot Table. Select Data: Click anywhere within your table. Create Pivot Table: Go to the 'Insert' tab, choose 'Pivot Table,' and select 'From Table or Range.'. Choose Location: Decide whether you want to place your pivot table in a new worksheet or an existing one and click 'OK.'. You now have a blank pivot table sheet. Pivot Table in excel can be created using the following steps. Click a cell in the data worksheet. In the “Tables” section of the “Insert” tab, click “Pivot Table.”. A “Create Pivot Table” window appears (as shown below). Now under the option “Choose the data that you want to analyze,” Excel automatically selects the data range. When using GETPIVOTDATA to fetch information from a pivot table based on a date or time date or time, use Excel's native format, or a function like the DATE function. For example, to get total Sales on April 1, 2021 when individual dates are displayed: = GETPIVOTDATA ("Sales",A1,"Date", DATE (2021,4,1)) When dates are grouped, refer to the When you refresh the PivotTable, new and updated data from the Excel table is automatically included in the refresh operation. Using a dynamic named range To make a PivotTable easier to update, you can create a dynamic named range, and use that name as the PivotTable's data source. If the named range expands to include more data, refreshing the Steps to Create Hierarchy Using Power Pivot: First, select the dataset in the Excel spreadsheet. Then go to the Insert tab and click on PivotTable from the Tables group. In the next box that pops up, select whether you want the pivot table in the existing worksheet or a new one. Counting Unique Values in a Pivot Table. We will click on any count in Column G of the Pivot Table. We will right-click and click on Value Field Settings. Figure 9- Value Field Settings Dialog box. We will select distinct count in the “summarize values by” field. We will click on OK. lsjnM4Q.