How to use Conditional Formatting to conditional formatting in Excel
Excel has a Condititional Formatting tool that is quite adequate for the needs of users. However, the built-in formats of Excel are not enough to meet your needs, Excel supports users to easily create new rules.
The format rules are available in Conditional Formatting
Conditional Formatting is located in the Home tab. To set the format for a range / column / row of data, select (highlight) the data range. Select the Home tab (1) and click on the Conditional Formatting icon (2) .
Highlight cells Rules - Rules highlight cell colors according to value rules
- Greater than: Greater value.
- Less than: Prices are smaller.
- Between: Value in the range.
- Equal to: Value is equal to.
- Text that Contains: The value contains a string of characters.
- A Date Occciring: The value contains a predefined date.
- Duplicate Value: Duplicate value.
For example, in the table above, you find the revenue values greater than 4,000,000,000 and highlight that cell color => You select Greater than. In the value comparison section, you type the number 4,000,000,000,000 (1) . In the highlighted color section (2) , you can choose from the available Excel formats or customize by selecting Custom Format .
After selecting all the options, click the OK button. As a result, cells containing values greater than 4,000,000 are highlighted.
Top / Botton Rules - The rules for determining cells by rank
Excel determines the cell's rank in the data range and formats that cell.
- Top 10 Items: Format the 10 cells with the largest value.
- Top 10%: Format 10% of the maximum number of cells.
- Bottom 10 Items: The 10-cell format with the lowest value.
- Bottom 10%: Format 10% of the minimum number of cells.
- Above Average: Format cells that are larger than the average of the data range.
- Below Average: Format a cell smaller than the average value of the data range.
For example, the table above, you need to format the color cells smaller than the average value of the data range, select Below Average. The format options window appears, you can choose the available formats of Excel or customize by selecting Custom Format . Then press the OK button .
Data Bar - Format the size of each value
With this format, the size of each cell in the data range will be determined by long or short highlighting.
Color Scale - A rule to organize data in ascending and descending order according to the color density
This rule will arrange the order of each cell in the string and highlight the color according to the format that the user chooses.
Icon Set - Add symbols in the value box
With this type of format, Excel groups data by special symbols such as arrows, traffic lights, etc.
Create a new format rule in Excel
If the types of formatting rules available do not meet the needs of the report, you can set up the new format by:
Step 1: Select (black out) the data area to be changed. On the Home tab (1 ), click on the Conditional Formatting icon (2) and select New Rule (3) .
Step 2: New Formatting Rule window appears.
(1) Select a Rule Type : select the type of data classification you need to perform.
- Format all cells based on their values: Apply background color to all cells based on the value of the data range.
- Format only cells that contain: Apply cell background color that contains a certain data.
- Format only top or bottom ranked values: Highlight the first or last ranking cells in the selected data range.
- Format only values that are above or below average: Highlight the cells with values above or below the average value.
- Format only unique or duplicate values: Highlight cells with unique values or duplicates in the selected data range.
- Use a formula to determine which cells to format: Use another formula to color.
(2) Edit the Rule Description : Optionally set the description for the data classification type you selected above.
With the above settings, Dexterity Software has set the format for the largest number, the color is orange and darker to the smallest. The results are as follows:
Delete and manage formatting rules
To check and manage format rules, go to Conditional Formatting and select Manage Rules. The Manage window will appear, based on this window you can know which cell rules are applying. Here you can add formatting, edit / edit formats and delete formatting.
To delete all Conditional Formatting formats , you cannot edit by Fill (highlight the cell color), but you must delete the installed format by:
Step 1: You only black out the data area but need to delete the format. On the Home tab (1 ), click on the Conditional Formatting icon (2) and select Clear Rules (3) .
In which: Clear rules from selected Cells is to delete the format in the currently selected cell.
Clear rules from entire Sheet is to delete all formatting from the open worksheet.
Above Software Tips showed you how to use Conditional Formatting format tool in Excel. Good luck!.
You should read it
- Ms Excel - Lesson 13: Use conditional formatting in Excel
- How to format conditional cells in Google Sheets
- How to create Progress bar using conditional formatting in Excel 2013, 2010 and 2007
- Use conditional formatting to format even and odd rows
- How to format data based on other cell conditions in Excel
- Conditional counting function in Excel
- How to list conditional lists in Excel
- Some new features of Access 2010
May be interested
- Filter data that doesn't overlap in Excel - Filter for unique values in Excelin a spreadsheet with large information data, it is difficult to check and filter which data is duplicated and which is not. especially if you manually / eye check it is very time consuming. thankfully, excel has a tool that makes it possible for users to filter for duplicate values in excel (unique values).
- How to format currencies in Excelhow to format currencies in excel. if you want to represent numbers as currency, you must format the numbers as currency. to do this, you use excel's format cellss tool.
- How to automatically wrap lines in Excel (Wrap Text in Excel)the line break in excel is not as simple as pressing enter as in ms word software. today, dexterity software will guide you to the warp text tool to automatically wrap lines that match the width of the column!
- Absolute and relative addresses in Excelabsolute and relative addresses in excel. relative addresses are addresses that change when copying a formula. this is the default address when we formulate the formula. for example a1, b2 ... the absolute address is the one that was not changed when copying the formula. the absolute address is distinguished from the absolute address with the character $. for example $ a1 $ 1, $ b1 $ 2….
- Quickly insert multiple images at once in Exceltoday dexterity software will guide you how to quickly insert rows of images at once in excel with the tool ablebit for excel.
- Excel date function - Usage and examplesthe date counting function in excel is one of the most effective functions of this software. because excel usually works in the field of statistics and calculations, it is extremely necessary to record dates. to help you understand this function, let's come to the following article of tipsmake.