C
ClearInsight News

How do I change the range of a pivot table in Excel 2016?

Author

Sophia Edwards

Published Feb 23, 2026

How do I change the range of a pivot table in Excel 2016?

In the Data group, click on Change Data Source button and select "Change Data Source" from the popup menu. When the Change PivotTable Data Source window appears, change the Table/Range value to the new data source that you want for your pivot table and then click on the OK button.

Considering this, how do I convert a pivot table to a range?

Using that command, with the Value option, should do the job:

  1. Select the pivot table cells and press Ctrl+C to copy the range.
  2. Display the Paste Special dialog box.
  3. In the Paste Special dialog box, choose the Values option, and click OK.
  4. Display the Office Clipboard.

Additionally, how do you calculate a change in a pivot table? Edit a calculated field formula

  1. Click the PivotTable.
  2. On the Analyze tab, in the Calculations group, click Fields, Items, & Sets, and then click Calculated Field.
  3. In the Name box, select the calculated field for which you want to change the formula.
  4. In the Formula box, edit the formula.
  5. Click Modify.

Also to know is, how do you modify a pivot table in Excel?

In the Data group, click on Change Data Source button and select "Change Data Source" from the popup menu. When the Change PivotTable Data Source window appears, change the Table/Range value to the new data source that you want for your pivot table and then click on the OK button.

When should you convert a pivot table to formulas?

You can use the GETPIVOTDATA function in a formula to convert PivotTable cells to worksheet formulas when you want to work with non-OLAP data sources, when you prefer not to upgrade to the new PivotTable version 2007 format right away, or when you want to avoid the complexity of using the Cube functions.

Can you turn a pivot table into a table?

GO to the "Design Tab" under Pivot Table => Select the "Report Layout" Dropdown Menu =>Make sure your Pivot Table is in Tabular Format => select "Repeat all Item Labels." Once you've done this, you can convert the pivot table to Text, table, range etc. . . and you won't need to copy labels down in each column.

How do I turn a table into a list in Excel?

Convert an Excel table to a range of data
  1. Click anywhere in the table and then go to Table Tools > Design on the Ribbon.
  2. In the Tools group, click Convert to Range. -OR- Right-click the table, then in the shortcut menu, click Table > Convert to Range. Note: Table features are no longer available after you convert the table back to a range.

How do I Unpivot a pivot table?

So you need to unpivot data and make it Pivot Table friendly.

Unpivot Data Using Power Query

  1. Select any cell in the dataset.
  2. Go to the Insert Tab.
  3. Click on the Table icon.
  4. In the 'Create Table' dialog box, make sure the range is correct.
  5. Click OK.
  6. With any cell selected in the Excel Table, click on the Data tab.

How do I convert a pivot table to a flat file?

Excel: Create a Flattened Pivot Table for Reuse
  1. Select one cell in the pivot table.
  2. Choose the Design tab of the ribbon.
  3. Open the Report Layout dropdown.
  4. Change from Compact Form to Tabular Form.
  5. If you have Excel 2010, open the Report Layout dropdown again and choose Repeat All Item Labels.

How do I do a Vlookup in a pivot table?

To use VLOOKUP in pivot table is similar to using VLOOKUP function to any other data range or table, select the reference cell as the lookup value and for the arguments for table array select the data in the pivot table and then identify the column number which has the output and depending on the exact or close match

Does pivot table update automatically?

At any time, you can click Refresh to update the data for the PivotTables in your workbook. By default, PivotTables are not refreshed automatically, but you can specify that the PivotTable is automatically refreshed when you open the workbook that contains the PivotTable.

How do I manually edit a pivot table?

Click anywhere in a pivot table to open the editor.
  1. Add data—Depending on where you want to add data, under Rows, Columns, or Values, click Add.
  2. Change row or column names—Double-click a Row or Column name and enter a new name.
  3. Change sort order or column—Under Rows or Columns, click the Down arrow.

How do I change the date format in a pivot table?

To change the date format:
  1. Right-click a date in the pivot table (not the pivot chart).
  2. Click on Field Settings.
  3. Change the Number Format to the date format that you want.

Why is my pivot table not refreshing?

Right-click any pivot table and choose PivotTable Options from the resulting submenu. In the resulting dialog, click the Data tab. Check the Refresh data when opening the file option (Figure A). Click OK and confirm the change.

How do I add a row to a pivot table?

For items, do the following:
  1. In the PivotTable, select the item you want. This displays the PivotTable Tools tab on the ribbon.
  2. On the Design tab, in the Layout group, click Blank Rows, and then select the Insert Blank Line after Each Item Label or Remove Blank Line after Each Item Label check box.

How do I extract raw data from a pivot table?

To retrieve all the information in a pivot table, follow these steps:
  1. Select the pivot table by clicking a cell within it.
  2. Click the Analyze tab's Select command and choose Entire PivotTable from the menu that appears.
  3. Copy the pivot table.
  4. Select a location for the copied data by clicking there.

How do you expand data in a pivot table?

Expand or Collapse the Pivot Field
  1. Right-click the pivot item, then click Expand/Collapse. In this example, I right-clicked on Boston, which is an item in the City field.
  2. Select on of the Expand/Collapse options: To see the details for all items in the selected pivot field, click Expand Entire Field.

How do I show percentage change in pivot table?

Excel PivotTable Percentage Change
  1. Step 1: Start with a regular PivotTable, and add the field you want the percentage change calculation based on, to the values area twice:
  2. Step 2: Right-click any values cell in the Sum of Sales2 column > select Show Values As > % Difference From…:

How do you find the difference between two columns in a pivot table?

More videos on YouTube
  1. Right-click one of the Units value cells, and click Show Values As.
  2. Click Difference From.
  3. In the Show Values As dialog box, from the Base field list, choose Date.
  4. From the Base item list, choose (previous).
  5. Click the OK button, and the pivot table shows the differences in weekly sales.

How do I show percentage and count in pivot table?

Steps
  1. Create a pivot table.
  2. Add Department as a Row field.
  3. Add Last as a Value field. Rename to "Count" Summarize by Count.
  4. Add Last as a Value field. Rename to "%" Summarize by Count. Display Percent of Grand Total. Change number formatting to percentage.

How do you show values in a pivot table?

Right click one of the value cells in the PivotTable. One of the options is Show Values As – see Figure 2. The Show Values As option is in the Options ribbon tab when you select a value cell within a PivotTable – see Figure 3.

How do you find the percent of change?

Next, divide the increase by the original number and multiply the answer by 100: % increase = Increase ÷ Original Number × 100. If the answer is a negative number, that means the percentage change is a decrease.

What is the Excel formula for percentage increase?

Calculate Percent Increase

In the image below, you can see last month's value of 430 in cell B3, and this month's sales of 545 in cell C3. To calculate the difference as a percentage, we subtract this month's value from last month's, and then divide the result by last month's value.

How do you show the percentage of a subtotal in a pivot table?

Right-click the value field in the PivotTable, and then pick Show Values As. Click the option you want. The value that is entered in the field. Values as a percentage of the grand total of all the values or data points in the report.

Continue Reading

Where do you shave when growing a beard?

Are Cowin headphones good?

Why does my laptop make a buzzing noise?

How do I change the range of a pivot table in Excel 2016?