Use formulas with conditional formatting
※ Download: Ms excel conditional formatting
Figure A Alert users to overtime hours requiring approval. Excel highlights the cells that are greater than 80. It is possible something will be changed with the next version of Excel.
You could also set a condition to change the font weight or color to denote the amount is negative or has fallen below a preset threshold. Here's the macro, though I don't know if it will help you, since it's specific to my sheet and somewhat more complex than the simple example I cooked up for here. Here's the spreadsheet we'll be working on.
Use formulas with conditional formatting - Excel displays the Text that Contains dialog box.
The options are endless and, in addition to all the preset formats, you can create your own custom formatting rules. The best thing about this feature is that it provides a quick snapshot of your spreadsheet when you view it or show it to others. Because the formatting is based on values, you don't have to do anything to make it work except update your data. Format cells that meet these value conditions 1. Open a sales spreadsheet or enter a dozen names with sales totals for the first four months of 2017; that is, Jan, Feb, Mar, and Apr. Highlight the Jan column of sales totals. Excel displays the Greater Than dialog box. In the field box under Format cells that are GREATER THAN, enter a number for this example, 5000 and click OK. Use the same instructions above to highlight and format cells that are less than 5,000. The other options that apply to values are Format Cells BETWEEN two numbers, Format Cells that are EQUAL TO a specific number, and Format Cells that CONTAIN Duplicate or Unique values. Note the highlighted cells in the Mar and Apr columns. Format cells that contain specific text You can also use the Conditional Formatting feature to make certain text letters, word, phrases, etc. Highlight the first column A of names. Excel displays the Text that Contains dialog box. In the field box under Format Cells that Contain the Text, enter the text you'd like to highlight e. Note that all the Smith names are now highlighted in light red with a dark-red font. Format cells that contain a Date within a range 1. Enter some dates in column F. Excel displays the A Date Occurring dialog box. In the field box under Format Cells that Contain a Date Occurring, enter a date range such as Today, Yesterday, Tomorrow, This Month, Next Month, etc. Note that all the dates for April are now highlighted in light red with a dark-red font. The Top and Bottom Percent and the Top and Bottom Items are self explanatory, right? In each highlighted range, Excel formats the highest or lowest numbers and the highest or lowest percent. Excel displays the Top 10 Items dialog. The system defaults to 10, then prompts for a preset format. Select Light Red Fill with Dark Red Text, and click OK. Next, highlight column C and follow the instructions above to format the Bottom 10 Items. Excel displays the Top 10% dialog. The system defaults to 10, then prompts you for a preset format. Select Yellow Fill with Dark Yellow Text, and click OK. Next, highlight column E and follow the instructions above to format the Bottom 10%. Note: You can enter any number between 1 and 9999 for the Top and Bottom Items and any number between 1 and 999 for the Top and Bottom percent. In column C, the average is 5523. Highlight column B for the Above Average formats and column C for the Below Average formats. Follow the instructions above to select a preset format for each column. For example, Data Bars compare values—obviously the longest bars represent the highest values and the shortest bars denote the smaller values. Conversely, the Color Scales show a range between the highest, lowest, and mid-range values. Notice that the Data Bars function like a horizontal bar chart inside each cell. Expand the column width to see a better visual for the Data Bars. Notice how the colors change when the numbers break, and note the difference between columns D Mar and E April. Icon Sets The Icon Sets also show a range of highest, lowest, and midrange values. Notice that Excel averaged out the numbers in the list and determined that all 1000s and 2000s are marked with red dots, 3000s and 4000s have yellow dots, and 5000s have green dots. Highlight columns C, D, and E and select one Icon Set from each of the remaining three sets. Notice the difference between how the values are grouped in the Icon Sets with three icons, four icons, and five icons. You can easily change the formatting levels to more accurately fit your specific needs. We'll cover that next.
But click OK when you're done. Next, highlight column C and follow the instructions above to format the Bottom 10 Items. Under the Conditional Formatting tab, select the Icon Set called 3 Symbols Circled. Copy A16:G16 to A17:G17. In our example, we've selected when the cell value is less than 10. Rule 2 All quantities below 200 must be colored red. Not only does it make your spreadsheet look awesome, it also enables you to make sense of your data and spot important cues in the blink of an eye. Next, we need to select what formatting to apply when this condition is met.
