Excel Pivot Table: How to Use the Same Field Twice

Yes. In Excel for Microsoft 365 on Windows, you can add the same source field to a PivotTable’s Values area more than once, then set each copy to a different calculation or display—for example, show Sales as both a dollar total and a percentage of the grand total.

How to add the same field twice

Start with a PivotTable built from a source range or Excel table that has a header for each column. Click anywhere in the PivotTable to show the PivotTable tabs. If the field list is hidden, select PivotTable Analyze > Field List. In the PivotTable Fields pane, drag the field you want to repeat into the Values area a second time. Dragging is useful because it lets you place the extra instance precisely, rather than relying on Excel’s default placement.

Click a cell in the PivotTable and open the PivotTable Fields pane if necessary.
Find the field in the field list, such as Sales, and drag it into Values.
Drag that same field into Values again. Excel adds another value column, usually with a generated name such as Sum of Sales2.
Right-click a value in the first or second column and choose Value Field Settings to configure that instance.
Repeat for the other instance, then give the two columns clear names.

The two entries are separate summaries of the same source column; changing one does not automatically change the other. For example, one can summarize Sales by Sum and the second can summarize it by Average. The source data is not duplicated or altered. Excel simply calculates and displays two views of it in the PivotTable.

What repeating a field lets you compare

Using the field twice is handy when a report needs two answers side by side. A sales report might show total sales for each product and the same product’s share of all sales. A service report might show both the number of cases and the average time per case. A budget report could compare total spending with a count of transactions. The categories in Rows or Columns can remain the same while the repeated value field supplies a different measure.

Each copy can use a different summary operation. Depending on the data and PivotTable type, common choices include Sum, Count, Average, Max, and Min. A numeric field is commonly summarized by Sum. A field containing text, or a numeric field Excel cannot interpret as consistently numeric, may default to Count instead. Check the selected operation rather than assuming that a heading beginning with Sum reflects the result you intended.

To change a copy, right-click a cell in its value column and select Value Field Settings. Under Summarize Values By, choose the operation for that particular copy. The dialog applies to the selected value field, so select the other column separately if you want to configure both.

Summary calculation versus Show Values As

There are two different ways to make repeated values tell different stories. Summarize Values By changes the underlying aggregation: Sum adds the source values, Count counts records, and Average calculates a mean. Show Values As changes how the aggregated result is presented relative to other PivotTable results. For example, the second copy can still be based on the sum of Sales but display each category’s share of a total.

For a percentage column, right-click a value in the second copy, choose Value Field Settings, and open the Show Values As tab. Choose an option such as % of Grand Total, % of Row Total, or % of Column Total, as appropriate. The first copy can remain a regular sum. The exact choice matters: % of Grand Total compares each displayed result with the entire PivotTable total, while a row or column percentage compares it with the corresponding row or column total.

A percentage display is not the same as changing the source values to percentages. It is a PivotTable calculation based on its current layout and filters. If you rearrange the report or filter out categories, the displayed shares may change because the comparison total changes. This is useful for interactive reporting, but it means the percentage should be interpreted in the context of the current PivotTable view.

A concrete example: sales and share of total

Suppose a source table has Region, Product, and Sales columns, and the PivotTable lists Product in Rows. Add Sales to Values twice. Keep the first instance summarized by Sum and rename it Total Sales. For the second instance, keep Sum as the summary operation, set Show Values As to % of Grand Total, and rename it Share of Total. The PivotTable can then show each product’s sales amount beside its contribution to the total represented by the current report.

If the displayed total is $20,000 and a product’s total is $5,000, its share is 25% because $5,000 divided by $20,000 equals 0.25. If a Region filter limits the report to one region, the grand total used for the percentage is generally the total in that filtered view, not necessarily the total across every region in the source. Make the report’s filters visible or explain them when readers need to understand the denominator.

This setup is often clearer than replacing the sales amount with a percentage. Readers can see both scale and proportion: a large percentage may come from a small overall market, while a substantial dollar amount may represent a modest share of a much larger total.

Rename repeated values and organize the layout

Excel-generated labels such as Sum of Sales and Sum of Sales2 are functional but can make a finished report hard to scan. In Value Field Settings, enter a useful name in Custom Name, such as Total Sales or Sales as % of Total. Each displayed value field needs a distinct caption. If Excel rejects a caption because it conflicts with an existing field name, use a more specific label—for instance, Sales Total rather than Sales.

Formatting is separate from the summary calculation. In Value Field Settings, use Number Format to choose currency, number, or percentage formatting for that value field. This is preferable to applying ordinary cell formatting alone because PivotTable refreshes and layout changes can otherwise make manual formatting unreliable. For a percentage display, choose a percentage number format and a sensible number of decimal places. For totals, use the relevant currency or number format.

When a PivotTable contains more than one value field, Excel usually presents a Values field in the layout, often as headings across columns. You can rearrange the value fields in the PivotTable Fields pane to change their order. If the report becomes too wide, consider shortening captions, moving a category field, or placing related values in separate PivotTables rather than making readers scroll across many measures.

Common problems and their fixes

The second copy does not appear: Confirm that you dragged the field into Values, not merely checked it in the list. If necessary, drag it from the field list again and look for a second entry in the Values area.
Excel shows Count instead of Sum: Inspect the source column for numbers stored as text, text entries, or inconsistent data. Correct the source values, refresh the PivotTable, and check the summary setting again. A count may be appropriate if the field represents records rather than amounts.
The custom name is rejected: Give the value a distinct caption. For example, use Sales Amount and Sales Share instead of trying to name a value field exactly Sales when Sales is already a source-field name.
The percentage seems unexpected: Check whether you selected the grand total, row total, or column total option. Also review filters, slicers, and the PivotTable layout, because they affect what is included in the comparison.
Settings are unavailable: Some PivotTables connected to external sources or the Data Model have different capabilities from a standard PivotTable. The source connection and Excel version can affect which value calculations are available.

After changing source data, refresh the PivotTable so the report reflects the updated records. Select a PivotTable cell and choose PivotTable Analyze > Refresh. Refreshing updates the report from its source; it does not repair incorrect source values, so clean the underlying data first when the summary is wrong.

When a calculated field or separate PivotTable is better

Adding a field twice is best when you want two summaries or displays of the same source column. It is not a way to create a new row-by-row source calculation. For a derived measure—such as revenue calculated from quantity multiplied by unit price—you may need a calculated column in the source table or, for a standard PivotTable, a calculated field. In Excel for Windows, the calculated-field command for supported non-Data-Model PivotTables is on PivotTable Analyze > Fields, Items, & Sets > Calculated Field. Data Model and OLAP PivotTables use different calculation features, so that command may be unavailable.

A second PivotTable can be a better choice if the two views need different row or column layouts, different filters, or independent formatting. Repeating a value field keeps related measures together and makes direct comparison easy, but all copies belong to the same PivotTable and respond to its layout and filters. Choose the arrangement that makes the comparison understandable without creating a crowded report.

In Excel for Mac, the PivotTable field pane and tab names can be arranged differently, but the general task is the same: add the field to Values again and edit each value field’s settings. In Excel for the web, available PivotTable controls can vary; if a particular summary or Show Values As option is missing, open the workbook in the desktop app. The desktop paths above describe Excel for Microsoft 365 on Windows.

Frequently Asked Questions

Can I put the same field in Values twice in Excel?
Yes. Add the field to the Values area twice. Configure each instance separately so one can show a sum and the other a count, average, or different display such as a percentage of a total.

Can I show Sum and Count for the same field?
Yes, provided the field and PivotTable support the desired summaries. Add the field twice, set one instance to Sum and the other to Count in Value Field Settings, and use distinct custom names so readers can tell them apart.

Can I show a number and its percentage of the total?
Yes. Keep one copy as a regular sum. For the other, open Value Field Settings, choose % of Grand Total under Show Values As, and apply percentage number formatting. Check the report’s filters because they influence the total used.

Why does Excel rename the second field with a 2?
Excel needs separate captions for the repeated value fields, so it may append a number to the generated name. You can replace that label with a clearer, unique Custom Name in Value Field Settings.

Does using a field twice duplicate the source data?
No. The PivotTable displays two calculations or presentations based on the same source field. It does not create a second copy of the records in the source table.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.