Jan 08 2022 01:27 PM
Can I use the if function to sort my budget into colors using the following classes:
< $100k
≥$100k - ≤$500k
>$500k - ≤$1M
> $1M
Jan 08 2022 01:35 PM
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.
Jan 08 2022 01:43 PM
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.