Search code examples
excelpivot-table

Excel Pivot Table Counts the Numbers, but does not Display number as Discrete value

Software: Excel

Application: Pivot Table

I create table in Excel

enter image description here

I convert to Pivot Table, but it does not display number of files detected

enter image description here

I tried toggling through all the value field settings, but no success

enter image description here

Help!

Solution

  • Currently, your pivot table has value field setting as count. That means for each month for each year, you only have 1 value. If you look at 2022, it has the count as 8. That is because you have 8 values in 2022, 1 value per month. This does not look at the actual amount of the value. You will need to switch the value field setting to sum. When sum is selected, it will sum all values for each month for each year. If you switch the pivot table to "sum of files detected" and it does not fix it, I would try two things. The first thing I would do is restart my computer and Excel. Sometimes excel pivot tables do not update even if you change a setting. It is a bug I face every once in a while. If that does not work, I would delete the pivot table and create a new one. Source for sum value in pivot table https://support.microsoft.com/en-us/office/sum-values-in-a-pivottable-9ee73790-646a-42c9-9fc7-e1ca30096d9c

    As user11222393 suggests, it is possible your data is not considered numbers in Excel. You will need to select all the values in "files detected" and change the number format to "Number". It is possible the format is "Text" or "General" https://support.microsoft.com/en-au/office/convert-numbers-stored-as-text-to-numbers-40105f2a-fe79-4477-a171-c5bad0f0a885