Applying Value Field Settings and Number Formats in Excel PivotTables

Course: Advanced Excel

Topic: Applying Value Field Settings and Number Formats

Course Link: Advanced Excel Training – JustAcademy

Demo Link: Register for a Course Demo

Website: JustAcademy Homepage

1. Introduction

PivotTables are powerful Excel tools used to summarize, analyze, and report large datasets. They allow users to calculate totals, averages, counts, percentages, and other business metrics without writing complex formulas manually.

Value Field Settings determine how numerical data is summarized or displayed in a PivotTable. For example, a Sales field can display Sum of Sales, Average of Sales, Count of Sales, or percentage contributions to a total.

Number Formats control how those values appear, such as currency, percentage, decimal numbers, dates, or numbers with thousands separators. Using appropriate settings makes reports easier to understand and helps prevent misleading interpretations.

2. Learning Objectives

• Understand the purpose of Value Field Settings in PivotTables.
• Change the summary calculation from Sum to Count, Average, Maximum, Minimum, and other available calculations.
• Use Show Values As to calculate percentages, running totals, differences, and rankings.
• Apply currency, percentage, decimal, and custom number formats.
• Understand the difference between changing source data and changing PivotTable display settings.
• Build a professional PivotTable report with accurate calculations and readable formatting.

3. What Are Value Field Settings?

Value Field Settings are options that control how a field placed in the Values area of a PivotTable is calculated and displayed. A single field can be summarized in different ways depending on the analytical requirement.

For example, a Sales Amount field may be summarized as total revenue, average transaction value, highest sale, or percentage of overall revenue.

Where to Find Value Field Settings

1. Create a PivotTable from your dataset.
2. Drag a numerical field, such as Sales Amount, into the Values area.
3. In the PivotTable Fields pane, locate the field under Values.
4. Click the drop-down arrow beside the field name.
5. Select Value Field Settings.
6. Use the Summarize Values By or Show Values As tab to configure the calculation.
7. Click OK to apply the settings.

You can also right-click a value inside the PivotTable and select Summarize Values By, Show Values As, or Value Field Settings, depending on the Excel version and context.

4. Understanding Summarize Values By

The Summarize Values By tab determines the calculation Excel uses to combine records into PivotTable results.

Important: Excel may choose Sum automatically for a field containing numeric data and Count for a field containing text or mixed values. If a field unexpectedly displays Count instead of Sum, check the source column for text-formatted numbers, blanks, or inconsistent data types.

5. Practical Example Dataset

Consider the following sales dataset. It contains transactions from different regions and products.

Suppose the objective is to analyze total sales, average sales, transaction counts, and regional sales contributions.

6. Applying Sum, Average, and Count

6.1 Calculate Total Sales

1. Insert a PivotTable using the complete dataset.
2. Drag Region into Rows.
3. Drag Sales Amount into Values.
4. Open Value Field Settings.
5. Select Sum under Summarize Values By.
6. Rename the custom value field to Total Sales, if desired.

The PivotTable calculates total sales for each region. For the sample dataset, North totals 70,000, South totals 70,000, and West totals 70,000.

6.2 Calculate Average Sales

1. Add Sales Amount to Values again, or edit its existing Value Field Settings.
2. Choose Average.
3. Rename the field to Average Sales.
4. Apply a suitable number format, such as currency with two decimal places.

Using the sample data, North has average sales of 35,000, South has average sales of 35,000, and West has average sales of 70,000.

6.3 Count Transactions

1. Drag a reliably populated field, such as Salesperson or Transaction ID, into Values.
2. Open Value Field Settings.
3. Select Count.
4. Rename the field to Transaction Count.

Count is useful for measuring the number of records, orders, or populated entries. Make sure the selected field is populated for every record you intend to count.

7. Understanding Show Values As

The Show Values As tab changes how values are presented relative to other values in a PivotTable. It does not replace the underlying source data; it changes the calculation used to display the summarized values.

The exact options available may vary according to the PivotTable structure, selected field, and Excel version.

8. Calculating Percentage of Grand Total

Percentage of Grand Total shows how much each category contributes to the overall total.

Steps

1. Place Region in Rows and Sales Amount in Values.
2. Open Value Field Settings for the Sales Amount field.
3. Select the Show Values As tab.
4. Choose % of Grand Total.
5. Click Number Format and select Percentage.
6. Set the required decimal places and click OK.

In the sample dataset, each region contributes one-third of the overall sales because each region has sales of 70,000 out of a total of 210,000.

Formula concept:

Percentage of Grand Total = Category Total / Overall Total

For North:

70,000 / 210,000 = 0.3333 = 33.33%

This analysis helps managers compare the relative contribution of regions, products, departments, and sales teams.

9. Calculating Difference and Percentage Difference

Difference From and % Difference From are useful for comparing periods, categories, or other items within a PivotTable.

Example: Monthly Sales Comparison

The calculations are:

Difference = Current Period Sales - Previous Period Sales
Percentage Difference = (Current Period Sales - Previous Period Sales) / Previous Period Sales

To configure this in a PivotTable, put Month in Rows and Sales in Values. Open Value Field Settings, select Show Values As, and choose Difference From or % Difference From. Set Month as the base field and select the appropriate previous month as the base item. Depending on the data layout, the base item may need to be set to (previous).

Use a clear date sequence and ensure that all expected months are represented. A missing month or an incorrectly sorted month label can lead to misleading comparisons.

10. Applying Number Formats in PivotTables

Number formats control the appearance of values without necessarily changing the underlying numeric values. For example, the number 25000 can appear as 25,000, ₹25,000.00, or 2,500,000% depending on the chosen format. The last example illustrates why choosing the correct format matters.

Common Number Formats

11. How to Apply Number Formats Correctly

Applying the format through Value Field Settings is generally the best approach because it helps preserve the formatting when the PivotTable refreshes or recalculates.

1. Click any value in the PivotTable's value field.
2. Right-click and select Value Field Settings.
3. Click the Number Format button.
4. Select the required category, such as Number, Currency, Accounting, Percentage, or Custom.
5. Set decimal places, currency symbol, negative number style, or other options as needed.
6. Click OK to close the Format Cells dialog.
7. Click OK again to apply the Value Field Settings.
8. Verify that the format applies to all values in the selected field.

Important: Avoid relying only on the Home tab's number-format controls for PivotTable values. Direct cell formatting can sometimes be lost or behave inconsistently when the PivotTable is refreshed, expanded, or rearranged. Field-level number formatting is more reliable.

12. Formatting Currency Values

Currency formatting is useful when presenting revenue, costs, profit, budgets, or expenses. In Indian business reports, the Indian Rupee symbol may be appropriate; other reports may require a different currency.

Steps

1. Open Value Field Settings for the Sales Amount field.
2. Click Number Format.
3. Select Currency or Accounting.
4. Choose the correct currency symbol.
5. Set decimal places, commonly two for monetary values where paise or cents matter.
6. Choose how negative values should appear.
7. Confirm the settings and review the PivotTable.

Example: 50000 may display as ₹50,000.00. The underlying value remains 50000; the number format only changes its presentation.

13. Formatting Percentages

Percentage formatting is important for contribution analysis, conversion rates, profit margins, and growth comparisons.

1. Set the calculation under Show Values As, such as % of Grand Total.
2. Open Value Field Settings.
3. Click Number Format.
4. Choose Percentage.
5. Select an appropriate number of decimal places, such as two.
6. Confirm the formatting and validate the results.

For example, 0.25 displays as 25.00% when formatted as a percentage with two decimal places. Do not multiply the values by 100 manually and then also apply percentage formatting, as that would produce incorrect results.

14. Using Custom Number Formats

Custom number formats help tailor the appearance of values for specific reporting requirements. They can add labels, control decimal places, distinguish positive and negative values, and hide zeros from display.

To apply a custom format, open Value Field Settings, select Number Format, choose Custom, enter a valid format code, and confirm.

Custom formats affect display, not the underlying value. Always test them with positive, negative, and zero values before using them in a report. Some formats may need adjustment for regional settings or the type of value being displayed.

15. Displaying Running Totals

A running total shows the cumulative value across an ordered series, such as monthly sales from the beginning of the year.

Steps

1. Place Month in the Rows area.
2. Place Sales Amount in the Values area and set it to Sum.
3. Open Value Field Settings.
4. Select Show Values As.
5. Choose Running Total In.
6. Select Month as the base field.
7. Apply a suitable currency or number format.
8. Verify that the months are sorted chronologically.

Example: monthly sales of 100,000, 120,000, and 150,000 produce running totals of 100,000, 220,000, and 370,000.

Running totals are useful for year-to-date sales, cumulative expenses, project progress, and accumulated production.

16. Ranking Values in a PivotTable

Ranking helps identify the highest- and lowest-performing products, branches, employees, or sales categories.

Steps

1. Place the category field, such as Region or Product, in Rows.
2. Place Sales Amount in Values and summarize it by Sum.
3. Open Value Field Settings and select Show Values As.
4. Choose Rank Largest to Smallest or Rank Smallest to Largest.
5. Select the appropriate base field, such as Product.
6. Review the ranking and apply a whole-number format if necessary.

Use Rank Largest to Smallest when rank 1 should represent the highest value. Check how ties are displayed, because categories with equal values may receive the same rank or be handled according to the calculation method.

17. Adding Multiple Calculations for the Same Field

Excel allows the same field to be added to the Values area multiple times. This makes it possible to compare several metrics in one PivotTable.

For example, Sales Amount can be used three times:

• Sum of Sales Amount: total revenue.
• Average of Sales Amount: average transaction value.
• % of Grand Total: percentage contribution.

Procedure

1. Drag Sales Amount into Values.
2. Drag Sales Amount into Values a second time.
3. Drag Sales Amount into Values a third time.
4. Open Value Field Settings for each instance separately.
5. Set the first to Sum, the second to Average, and the third to Sum with Show Values As set to % of Grand Total.
6. Rename the fields to Total Sales, Average Sales, and Sales Contribution.
7. Apply currency formatting to the first two fields and percentage formatting to the third.

This creates a more informative report without requiring separate PivotTables for every metric.

18. Renaming Value Fields

PivotTable headings such as Sum of Sales Amount or Average of Sales Amount may be technically accurate but less suitable for management reports. Custom names make the report easier to understand.

1. Open Value Field Settings for the relevant field.
2. Enter a descriptive name in the Custom Name box.
3. Use names such as Total Revenue, Average Order Value, Transaction Count, or Revenue Contribution.
4. Click OK.

Choose names that describe the calculation accurately. For example, do not label a sum as Average Revenue or a count as Total Sales.

19. Troubleshooting Common Problems

20. Best Practices for Professional PivotTable Reports

• Choose the summary calculation that matches the business question.
• Use Sum for additive measures such as revenue and units sold, and Average for metrics where the mean is meaningful.
• Use Count or Count Numbers carefully, based on what needs to be counted.
• Apply number formats through Value Field Settings instead of formatting individual cells only.
• Use the correct currency symbol and avoid mixing currencies without conversion.
• Use percentage formats for ratios and contribution values.
• Use consistent decimal places across comparable metrics.
• Give each value field a clear and accurate custom name.
• Verify totals, percentages, rankings, and running totals against the source data.
• Refresh the PivotTable after modifying the source data.
• Use Excel Tables as data sources when suitable, so new rows can be incorporated more easily.
• Keep reports simple and avoid excessive decimal places, colors, or custom formats.
• Document special calculations so other users understand the report.

21. Practical Exercise

Task: Create a regional sales analysis PivotTable using the sample dataset in this lesson.

Requirements

1. Insert a PivotTable with Region in Rows.
2. Add Sales Amount to Values and display it as Total Sales using Sum.
3. Add Sales Amount again and display it as Average Sales using Average.
4. Add a populated transaction identifier or Salesperson field and summarize it using Count.
5. Add Sales Amount a third time and display it as % of Grand Total.
6. Format Total Sales and Average Sales as currency.
7. Format Sales Contribution as a percentage with two decimal places.
8. Rename the value fields to Total Sales, Average Sales, Transaction Count, and Sales Contribution.
9. Refresh the PivotTable and verify that the formatting remains appropriate.

Expected Results for the Sample Dataset

Note: The grand total for Average Sales is 42,000 because it averages all five transactions, rather than averaging the three regional averages equally. If the field used for Count contains a blank, the count may be lower than the transaction total.

22. Mini Project: Monthly Revenue Performance Report

Build a PivotTable report for a business dataset containing Date, Region, Product, Salesperson, Sales Amount, and Units Sold.

Project Steps

1. Convert the source dataset into an Excel Table.
2. Insert a PivotTable and group dates by month and year if appropriate.
3. Place Month in Rows and Region in Columns.
4. Add Sales Amount to Values as Sum and format it as currency.
5. Add Sales Amount again as % of Grand Total and format it as a percentage.
6. Use another copy of Sales Amount with Difference From or % Difference From to compare periods.
7. Use Units Sold as Sum to analyze sales volume.
8. Add a running total by month to show cumulative revenue.
9. Apply clear field names and consistent number formats.
10. Validate the grand totals and selected regional totals against the source data.

Deliverable: A professional PivotTable showing monthly revenue, regional contribution, unit volume, period-over-period change, and cumulative revenue.

23. Interview Questions and Answers

Q1. What are Value Field Settings in Excel?

Answer: Value Field Settings control how fields in the Values area of a PivotTable are summarized and displayed. They include summary calculations, Show Values As options, and number formatting.

Q2. What is the difference between Sum and Count?

Answer: Sum adds numeric values, while Count counts non-empty entries in the selected field. Count Numbers counts entries recognized as numeric.

Q3. How can you calculate average sales by region?

Answer: Place Region in Rows, add Sales Amount to Values, open Value Field Settings, and select Average.

Q4. How do you display sales as a percentage of the grand total?

Answer: Open Value Field Settings, choose Show Values As, select % of Grand Total, and apply Percentage number formatting.

Q5. What is the difference between Number Format and Show Values As?

Answer: Show Values As changes the calculation used to present a value relative to other values. Number Format changes how the resulting value appears, such as displaying 0.25 as 25.00%.

Q6. Why should number formatting be applied through Value Field Settings?

Answer: Field-level formatting is generally more reliable for PivotTables than formatting individual cells because the PivotTable can rearrange or refresh its displayed cells.

Q7. Can the same field be used multiple times in the Values area?

Answer: Yes. The same field can be added several times and configured independently to show different calculations, such as Sum, Average, and percentage contribution.

Q8. What does Running Total In do?

Answer: It displays cumulative values across items in a selected base field, such as cumulative monthly revenue.

Q9. What is the purpose of Difference From?

Answer: It shows the numerical difference between each value and a selected base item, such as the difference between the current month and the previous month.

Q10. Why might a PivotTable display Count instead of Sum?

Answer: Excel may detect text or inconsistent data in the source field. Check for numeric values stored as text, clean the data, refresh the PivotTable, and choose Sum where appropriate.

Q11. Does number formatting change the actual numeric value?

Answer: Normally, no. Number formatting changes the appearance of the value, not the stored numeric value.

Q12. What is the purpose of a custom number format?

Answer: Custom number formats allow users to control decimal places, separators, negative values, zero displays, and labels without changing the underlying number.

24. Quick Revision Checklist

• Value Field Settings control summary calculations and value presentation.
• Summarize Values By includes options such as Sum, Count, Average, Max, and Min.
• Show Values As supports percentage contributions, differences, running totals, and rankings.
• Number Format controls currency, decimals, percentages, and other display styles.
• Use Percentage formatting for ratios and contribution calculations.
• Use the Number Format button inside Value Field Settings for more reliable field-level formatting.
• The same field can be added multiple times for separate calculations.
• Refresh the PivotTable after changing the source data.
• Validate totals and calculations before sharing reports.

25. Conclusion

Applying Value Field Settings and Number Formats is an essential Advanced Excel skill for building accurate and professional PivotTable reports. By selecting the right summary calculation, configuring Show Values As, and applying appropriate number formats, users can transform raw data into meaningful business insights.

Whether you are preparing sales summaries, financial reports, performance dashboards, or monthly comparisons, these settings help improve readability, consistency, and analytical accuracy.

Explore more learning opportunities at JustAcademy, review the Advanced Excel course, or register for a course demo.