Search code examples
excelexcel-2010excel-2007pivot-table

How to show less than a specific percentage in an Excel pivot table?

I have a pivot table made in Excel and I already have all the % collected data there.

Now, what I need to do is show only the critical ones which are less than 85%, as I don't need anything above that being shown.

Is there a way I can show less than 85% or any other amount in the future?

Solution

  • Add another column to your raw data. Set formula to [assuming the % is in column A]:

    =A1<85%

    Add this column to your pivot table as a filter. Filter for where your new column = TRUE.

    Edit for new info

    Per your comments below, the %'s are not actually calculated "within" the pivot table, they are next to the pivot table on the same tab. So, simply add an "if" statement to that formula, like so:

    = if(('Total Paid'+'Total Contr. Adj.')/('Total Billed Amount'-'Other Adj.'-'Write-Off')<85%,('Total Paid'+'Total Contr. Adj.')/('Total Billed Amount'-'Other Adj.'-'Write-Off'),"")

    This will check to see if your formula is <85%. If it is, the formula will appear there.