If function

Occasional Visitor

Can I use the if function to sort my budget into colors using the following classes:

< $100k


≥$100k - ≤$500k


>$500k - ≤$1M


> $1M


2 Replies


Select the cell(s) that you want to color.

Set the fill color to the color you want to use for amounts <$100K. this will be the default.


On the Home tab of the ribbon, select Conditional Formatting > New Rule...

Select 'Format only cells that contain'.

Leave the first drop-down set to 'Cell Value'.

Select 'greater than or equal to' from the second drop-down.

Enter 100000 in the box next to it.

Click Format...

Activate the Fill tab.

Select the color for amounts between $100K and $500K.

Click OK twice.


Repeat the above steps, but with 'greater than' and 500000, and the color you want to use for amounts between $500K and $1M.


Finally, repeat them again, but with 'greater than' and 1000000, and the color you want to use for amounts over $1M.


Attached is an example.


In office 365 you can use this formula to sort your budget and exclude the 0.



In the new column use conditional formatting and then you are going to have your budget sorted by color.