2009
07.27
07.27
katy c asked:
I am subtotaling a spreadsheet in excel. Most of the balances are zero, but I am only concerned with the balances that are not. I need to keep the detail. What is the easiest, least manual way to seperate those subtotals with balances from those with zeros?
Jo
I am subtotaling a spreadsheet in excel. Most of the balances are zero, but I am only concerned with the balances that are not. I need to keep the detail. What is the easiest, least manual way to seperate those subtotals with balances from those with zeros?
Jo
Derrick
Maybe you could use an IF function in your subtotal formulas to determine if the subtotal is a zero or not e.g.
=IF(SUM(A1:A10)=0,”",SUM(A1:A10))
June
The above is a good answer
You could also consider applying conditional formatting to highlight the non-zero balance totals by colour, font size etc. (Select balance totals then Format -> Conditional Formatting)
This approach could be used either by itself or in combination with the above.
Donald
You could reset Excel not to display 0’s by going to Tools,Options,View and uncheck Zero values. This would clear zeros. OR
You could also use conditional formatting; Format. Conditional Formatting—-If cell value is less than .011 change font color to background color. OR, If cell value is greater than .01 change font to different color or bold, or both by choosing add>>. Maybe this will work for you.
Harvey
namesalary
john 112000
john 218000
john 324000
john 430000
john 536000
john 642000
john 748000
john 80
john 90
john 100
subtotal=SUBTOTAL(9,B2:B11)
sumif=SUMIF(B2:B11,”>0″)
The 9 in SUMIF is a argument type for sum.
The SUBTOTAL command also works well if you use it on a filtered list. Works for all versions of Excel.
You might want to consider the DSuM function as well