If function

Copper Contributor

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

< $100k

 

≥$100k - ≤$500k

 

>$500k - ≤$1M

 

> $1M

 

2 Replies

@AT-2022 

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.

@AT-2022 

Attached is an example.

 

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

=FILTER(SORT(A1:A100),A1:A100<>0)

 

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

 

alannavarro_0-1641678144623.png