Find Values Quickly on a Spreadsheet (Tech Beat)

Posted on

techbeatConditional 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.

ConditionalFormat_1

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”.

ConditionalFormat_2

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.

ConditionalFormat_3

This is just the beginning of all the cool things you can do with Conditional Formatting. In our next Tech Beat post, we will show you how to find duplicates, compare lists or values, and make a dynamic condition on the fly. Stay tuned…

Tell us what you think

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s