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. Example: 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!

Removing conditional formatting To remove conditional formatting: Click the Conditional Formatting command. A drop-down menu will appear. Hover the mouse over Clear Rules, and choose which rules you want to clear. In our example, we'll select Clear Rules from Entire Sheet to remove all conditional formatting from the worksheet. To assign a number format that would display 'Yes' for 1 and 'No' for 0, select the column where you want Yes or No to be displayed. Press Ctrl+1 to display the Format Cells dialog. Or, to use standard commands.In Excel 2003, choose Format, Cells.In Excel 2007 and above, choose Home, Cells, Format, Format Cells.

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. In the left box, you enter the value that the cell content must be in order to “trigger” the conditional formatting. Best free code editors. 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 arrow next to the right box and select one of the other presets. In tip 1 you learned how to highlight values in your sheet that are below 100. You can use the same method to pinpoint exactly where a certain string of text is. So if we’re looking for all the M compatible adapters (in the sample file) then we don’t have to use our eyes (very much).

Instead, we can let conditional formatting do all the hard work and identify the results easily ourselves. Select some data with text. In the sample file, we’ll select the range from B4 to B26. So basically all our “Item” names. Then click the ‘Conditional Formatting’ button on the ‘Home’ tab, hover your mouse over ‘Hightlight Cells Rules’ and click ‘Text that Contains’. Below we’ll go through the different options shown in above screenshot. • Here you can change the ‘ Rule Type’.

If you don’t want the conditional formatting to apply when “something” happens in a cell, you can change it to apply on all cells and filling them with color based on their value compared to the average of the data. You can also make the rule more advanced by basing it on a formula.

Watch tv and movies for free mac. • If you keep the ‘Rule Type’ you can change the “ trigger” of the formatting. Here you edit your rule so it no longer “triggers” on a cell containing specific text, by choosing “Blanks” or “Cell Value”. This means that the formatting applies when a cell is empty or, for example, holds a value greater than, or less than, 100.

• If you keep the ‘Specific text’ you can decide here whether the cell should contain, not contain, begin with or end with the text that you enter in the field to the right. • Here you enter your new text.

• From this little (familiar) box you can change the formatting that you applied in the first place. If you don’t want your cells to turn green when it includes the text “L compatible” (or something else), you can have it turn yellow instead. As you see there’re enough options, but wait – there’s more!

All of the above is very nice, but if you accidentally selected the wrong area when you made the conditional formatting rule – then it doesn’t help to change the ‘Rule Type’ or whether the cells should be red or blue. And if you spent 5 minutes entering a unique formula to “trigger” the formatting, then it sucks to delete it and do it all over. To change the range of cells that the conditional formatting rules applies to, you don’t need to go to the ‘Edit Formatting Rule’ box above.

You just need to click in ‘Applies to’ at the rule you want to change in the ‘Conditional Formatting Rules Manager’ box. If you’re an inventory manager and your main supplier are closing for restoration for 3 months, you’ll have to refill your stock now before it’s too late.

It may be bad timing because you aren’t low on any items, but you have to order some anyway. You need to pick 8 items that you have the least amount of in stock – and it has to be done in 15 minutes (before office hours ends). How can you make it in time? In Excel, there are several ways to tell you the 8 lowest items in your inventory, but if you have less than 500 different items in your inventory, I’d chose the fastest way possible: Conditional formatting. Click ‘Conditional Formatting’ in the ‘Home’ tab. Hover your mouse over Top/Bottom Rules and click ‘Bottom 10 Items’. • In the field to the left, you can click the tiny arrows to change the number of items from the bottom.