Tip 1: Skip to important data with highlighted cells
Sometimes you’re not reading all your data in your sheets, you’re just looking for specific numbers.
That can be a pretty tedious process – especially if you have a large set of data.
Let’s say you have an inventory list, with multiple items and their corresponding quantity in stock.
If the quantity in stock of an item reaches below 100 it’s critical that you find out, so you can buy more units of that particular item.
If you’re unaware of conditional formatting, you might place the tip of your finger on your screen and start moving it downwards to see if there are any numbers in that column that is below 100.
This is not a very effective method in a dataset with 50+ rows (and the risk of human error is high).
Instead, let conditional formatting do the job!
After this tip, the only thing you need to do is click a few times with your mouse. You can even follow along as we go with this sample file, which I strongly recommend you do.
The first thing you need to do is to select your quantity column. In our case, it’s range C4:C26.
In the ‘Home’ tab of the ribbon (that’s geek for the menu in the top with all the buttons) click ‘Conditional Formatting’.
Then hold your pointer over the option ‘Highlight cells rules’ and click ‘Less than…’
After this, you’ll see a pop-up box with some very simple options.
In the left box, you enter the value that the cell content must be less than in order to “trigger” the conditional formatting.
So if you enter 100, something will happen when the value is less than 100. What that “something” is we’ll define in the box to the right.
As you see the standard option is “Light Red Fill with Dark Red Text”.
If you just click OK now, all the quantities that are below 100 will be formatted this way.
If you want to change that (and you probably will) click the drop-down arrow next to the right box and select one of the other presets.
If you’re not happy with the preset formatting provided by Excel, you can make your own.
Click ‘Custom Format…’ and pick your favorite cell formatting for the cells that are below 100.
Now you can easily identify specific value intervals in your data – in this case, 0 to 99.
This technique can be applied to many other situations, and perhaps a situation where you need to identify cells that have certain text in it.
Read the next tip to learn how…