Find Values Quickly on a Spreadsheet

Conditional Formatting is a largely underused and undervalued feature in Excel. Very often I see people re-sort the entire spreadsheet or use the search feature to find specific values. These can be easily done instead with Conditional Formatting.

Ranking

Instead of re-sorting your spreadsheet, try selecting the data cells you wish to rank, then click “Conditional Formatting” on the command ribbon and select “Data Bars”. Hover your mouse over the fills and watch the data bars appearing on your spreadsheet where you can easily locate the high and low values.

Colour Scales

If you keep track of workflow, transactions, cashflow, etc., this trick is great for getting a quick visual of your busy/normal/slow periods. Highlight your data cells, click “Conditional Formatting” and select “Color Scales”.

Top/Bottom Values

The “Top/Bottom Rules” allow you to quickly identify the top and bottom values, top and bottom percentages, above-average and below-average values.

This is just the beginning of all the cool things you can do with Conditional Formatting.

[This tip originally appeared on the Law Society of Saskatchewan Library’s Legal Sourcery Blog]