How to use Microsoft Excel’s Conditional Formatting - raffertyforion73
Microsoft Excel's conditional formatting is a wonderful "automatic" feature that allows you to formats cells supported the value of those cells or the value of the formulas in those cells. For exercise, you can narrow that all the sales totals in your spreadsheet that exceed $5,000 are highlighted in jaundiced; or all the dates preceding to the current year use of goods and services a greenish font; operating room use a shape or ratings icon to flag all duplicate values above 12,000. The options are endless and, in plus to wholly the predetermined formats, you can create your own custom-built formatting rules.
The best matter about this feature is that it provides a excitable snapshot of your spreadsheet when you prospect IT or show IT to others. Because the data format is founded on values, you don't have to do anything to pull through work except update your information.
A. Initialize cells that meet these value conditions
1. Open a gross revenue spreadsheet or enter a dozen names with sales totals for the prototypical four months of 2017; that is, Jan, Feb, Mar, and April.
2. Highlight the Jan column of sales totals.
3. Select Home > Conditional Formatting > High spot Cells Rules > Greater Than.
4. Excel displays the Greater Than dialog box. In the field box under Formatting cells that are GREATER THAN, enter a number (for this example, 5000) and detent OK.
5. Note that all the sales totals in column B that are greater than $5,000 are now highlighted in brightness level green with a green font.
PC World / JD Sartain Format altogether cells greater than 5000.
6. Use the same instructions above to spotlight and format cells that are to a lesser degree 5,000.
PC World / JD Sartain Format all cells less than 5000.
7. The other options that apply to values areFormat Cells BETWEEN two numbers, Format Cells that are Isoclinal TO a specific number, and Format Cells that CONTAIN Duplicate Beaver State Unique values. Note the highlighted cells in the Mar and Apr columns.
PC Globe / JD Sartain Format cells 'tween two Numbers, equal to a specific number, or containing unique or matching values.
B. Format cells that contain specific schoolbook
You can also use the Counterfactual Formatting feature to make certain text (letters, word, phrases, etc.) stand out.
1. Foreground the first column (A) of names.
2. Select Home plate > Provisionary Formatting > Highlight Cells Rules > Text that Contains.
3. Excel displays the Text that Contains dialog box. In the field box under Format Cells that Carry the Text, enter the text you'd like to highlight (e.g., Ian Douglas Smith) and click OK.
4. Banker's bill that all the Smith names are now highlighted in light red with a dark-red font.
PC World / JD Sartain Format all cells that contain particular school tex.
C. Format cells that contain a Escort within a graze
1. Enter more or less dates in column F.
2. Select Home > Conditional Formatting > Highlight Cells Rules > A Date Occurring.
3. Stand out displays the A Date Occurring dialog box. In the subject field box under Format Cells that Hold in a Date Occurring, go into a go steady range such as Today, Yesterday, Tomorrow, This Month, Next Month, etc., and click OK.
4. Eminence that all the dates for April are immediately highlighted in light violent with a inactive-red face.
PC World / JD Sartain Format cells that contain a Date within a range.
D. Top/Bottom rules
The next subdivision of Conditional Formatting features Best/Bottom rules, which provides formatting for the Upside 10 Items, Top 10%, Tail 10 Items, Tush 10%, Preceding Average, and Below Average items. The Top and Bottom Percent and the Crowning and Bottom Items are self explanatory, right? In to each one highlighted range, Excel formats the highest or lowest numbers and the highest or last-place percent.
1. High spot pillar B.
2. Select Home > Conditional Data formatting > Teetotum/Bottom Rules > Top 10 Items.
3. Excel displays the Top 10 Items dialog. The system defaults to 10, then prompts for a preset format. Quality Light Red Fill with Pitch-black Red ink Text, and click OK.
4. Next, highlight column C and follow the operating instructions above to data formatting the Bottom 10 Items.
PC World / JD Sartain Format cells for the Round top 10 or Bottom 10 Items in a range.
5. High spot tower D.
6. Select Home > Qualified Formatting > Top/Arse Rules > Top 10%.
7. Stand out displays the Top 10% dialogue. The system defaults to 10, then prompts you for a preset format. Select Yellow Occupy with Dark Xanthous Text, and click Sooner State.
8. Next, highlight pillar E and follow the operating instructions above to data formatting the Bottom 10%.
Note: You can embark any number between 1 and 9999 for the Top and Bottom of the inning Items and whatsoever number between 1 and 999 for the Top and Bottom percent.
PC Public / JD Sartain Format cells for the Upper side 10 or Nates 10 percent in a range.
9. The Preceding and Below Medium options are calculated like this: Excel averages the values in the highlighted column and determines the "average" number. In newspaper column B, the average is $3231. In column C, the average is 5523.50 You can see to it this aside using the control =Ordinary(B2:B13) and/or =AVERAGE(C2:C13).
10. Highlight column B for the Above Mediocre formats and column C for the Downstairs Average formats.
11. Keep an eye on the instructions above to blue-ribbon a planned format for each column.
PC Earth / JD Sartain Format cells for the Above and Below Average values in a range.
E. Information Parallel bars &ere; Color Scales
Data Bars and Color Scales are formatting tools that spice up your spreadsheets with visuals that mean something. For example, Data Bars equate values—obviously the longest parallel bars represent the highest values and the shortest bars denote the smaller values. Conversely, the Color Scales show a grasp between the highest, last-place, and middle-range values.
1. Highlight column B and choose a Gradient Fill Data Bar from Conditional Formatting > Data Bars.
2. Foreground column C and choose a Upstanding Fill Data Bar from Conditional Formatting > Data Bars.
3. Notice that the Data Bars function like a horizontal stop chart inside each cell. Expound the editorial width to realise a better visual for the Information Bars.
4. Highlight column D and choose a Coloring Scale from Conditional Formatting > Color Scales, past spotlight column E and choose a different Colorize Scale.
5. Notice how the colors change when the numbers breakout, and Federal Reserve note the difference betwixt columns D (Mar) and E (April).
PC Universe / JD Sartain Use Information Parallel bars & Color Scales to zest your spreadsheets with visuals that ungenerous something.
E. Picture Sets
The Icon Sets besides show a range of highest, lowest, and midrange values.
1. For example, highlighting column B and choose Place > Conditional Formatting > Icon Sets > Shapes and choose the first solidification of dots on the list. Notice that Excel averaged out the numbers in the list and determined that wholly 1000s and 2000s are marked with red dots, 3000s and 4000s have yellow dots, and 5000s give birth green dots.
2. Highlight columns C, D, and E and blue-ribbon incomparable Icon Set from each of the leftover three sets. Notice the difference between how the values are grouped in the Icon Sets with threesome icons, four icons, and five icons.
PC Existence / JD Sartain Preset Icon Sets provide three, quaternary, and five levels of formatting.
Note: All the Image Sets in this group: Directing, Shapes, Indicators, and Ratings provide three, four, and/or basketball team levels of formatting, which again, determine the highest, lowest, and midrange values. You can well modify the formatting levels to more accurately conniption your specific needs.
As a matter of fact, you can custom-make all of the Provisory Formatting features using self-outlined rules that are easy to make up through Excel's simple "walk-through" menus. We'll track that next.
Source: https://www.pcworld.com/article/406082/how-to-use-microsoft-excels-conditional-formatting.html
Posted by: raffertyforion73.blogspot.com
0 Response to "How to use Microsoft Excel’s Conditional Formatting - raffertyforion73"
Post a Comment