Q&A for How to Add a Custom Field in Pivot Table

Return to Full Article

Coming soon

Add New Question

Question

Every time that I create a new pivot table and I select a field (revenue) and I move it to values space, it defaults to count of revenue even though I've used sum of revenue in previous pivot tables.

Nicole Levine, MFA
wikiHow Technology Writer
Nicole Levine is a Technology Writer and Editor for wikiHow. She has more than 20 years of experience creating technical documentation and leading support teams at major web hosting and software companies. Nicole also holds an MFA in Creative Writing from Portland State University and teaches composition, fiction-writing, and zine-making at various institutions.

Nicole Levine, MFA

wikiHow Technology Writer

Expert Answer

It would default to count instead of sum if there are any blank cells or errors in your data--if you have any of either, replace them with 0 (zeros) and that should sort if out. If that doesn't work, you might have non-numerical characters in your data (or numbers that are formatted as text instead of numbers). Make sure your data is purely numerical and you should get a sum instead of the count.

Thanks! We're glad this was helpful.
Thank you for your feedback.
If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
Yes No

Not Helpful 0 Helpful 0
Question

How can I add a new field to the Filters or Rows section of the Pivot Table?

Nicole Levine, MFA
wikiHow Technology Writer
Nicole Levine is a Technology Writer and Editor for wikiHow. She has more than 20 years of experience creating technical documentation and leading support teams at major web hosting and software companies. Nicole also holds an MFA in Creative Writing from Portland State University and teaches composition, fiction-writing, and zine-making at various institutions.

Nicole Levine, MFA

wikiHow Technology Writer

Expert Answer

Once you create the new field, it will appear in the "Values" section of the PivotTable Fields panel on the right side of your sheet. To use the value as a filter, drag it over to the Filters section.

Thanks! We're glad this was helpful.
Thank you for your feedback.
If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
Yes No

Not Helpful 0 Helpful 0
Question

I would like to display the number of occurrences (count) of incidences where where column A is less than 100 when Column C is equal to a given value.

Nicole Levine, MFA
wikiHow Technology Writer
Nicole Levine is a Technology Writer and Editor for wikiHow. She has more than 20 years of experience creating technical documentation and leading support teams at major web hosting and software companies. Nicole also holds an MFA in Creative Writing from Portland State University and teaches composition, fiction-writing, and zine-making at various institutions.

Nicole Levine, MFA

wikiHow Technology Writer

Expert Answer

The COUNTIFS formula would get you that result, but you can't use that function in a calculated field. You might want to add a new column to your source data that contains an IFS statement, which you can count in your pivot table without adding any calculated fields.

Thanks! We're glad this was helpful.
Thank you for your feedback.
If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
Yes No

Not Helpful 0 Helpful 0
Question

I am trying to create a percentage in a pivot table for sum of contact/total accounts. I can manually figure out the formula, but cannot add it so that it represents in the pivot table. What am I missing?

Community Answer

Place the field in the "Value" section of the pivot table tools. Click the drop-down arrow on the object in the value section and select "Value Field Settings". In the box that opens up, click the "Show Values As" tab. Click the drop-down arrow on the "No Calculation" box. It shows you several percentage options to use to display the value. You can do this as a second value, using the same field, if you want both totals and percentage. It shows in the pivot table as a second field.

Thanks! We're glad this was helpful.
Thank you for your feedback.
If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
Yes No

Not Helpful 7 Helpful 0