Excel 2013 ｜ Emphasize values to quickly check information
"Conditional formatting" that highlights the required data and makes it easy to identify at a glance. This time, check the rule settings for highlighting!
Excel not only creates tables and graphs, but also has a full range of functions for grasping and analyzing input data.
Among them, "conditional formatting" is the most basic function. Automatically reformat cells that match user-specified criteria. You can pick up the size and rank of the entered numerical value, duplicate data, etc. and highlight them by changing the color of the characters.
This time, I will introduce two items from the "Conditional Format" menu, "Cell Highlighting Rule" and "Color Scale".
Format with string
↑ Not only numerical values but also character strings can be specified as conditions for changing the format. Pick up without missing the matching cell.
↑ Compare the data in the cell and check for duplication. Cells with the same data can be found at a glance.
Highlight by numerical change
↑ When the data matches the numerical value set in the condition, the format such as cell and character color is automatically changed.
↑ There is also a "color scale" function that analyzes the numerical values within the range and changes the color according to the order of the values.
Extract the desired cell with the highlighting rule
"Conditional formatting" is available on the "Home" tab. When you click the button, there are 5 types of basic menus such as "upper / lower rules" and "data bar", and you can also select submenus. Here, I will introduce two types, "cell highlighting rule" and "color scale".
● Step01 Specify a range and set "conditional formatting"
↑ Select the range of cells for which you want to set conditions. For cells that match the specified conditions, the font type and cell pattern will change.
↑ Select the "Conditional Format" button on the "Home" tab. Select "greater than the specified value" from "cell highlighting rule" in the menu.
● Step02 Automatically change the format of cells that match the conditions
↑ On this screen, enter a number in the "Format cells larger than the following value" field. In addition, select the changed format in the "Format" item.
↑ When the condition is confirmed, the format of the cell that matches the condition changes. The text and background color of the three matching cells have changed.
● Step03 The unit of time to be displayed can be converted to each other.
↑ If you select "within the specified range" in the "cell highlighting rule" ...
↑ Enter the lower and upper limits. Select "Red text" for the changed format.
↑ Cells that meet the conditions of 100 or more and 200 or less change. The usage is the same for "less than the specified value" and "equal to the specified value".
● Step04 Extract character string data by keyword search
↑ First, select the range. Select "Character string" in "Cell highlighting rule".
↑ Enter a keyword in "Cell Formatting with the following string".
↑ Search for cells within the specified range and change the format. It is also changed when the keyword is used in part instead of the full text match.
● Step05 Check for duplicate values within the specified range
↑ It is also possible to extract duplicate data. You don't have to specify keywords or numbers, just select "duplicate" or "unique".
↑ Find the cell with the same data and change the cell format. If "Unique" is selected, unique data will be extracted.
● Step 06 "Color scale" that you can see the ranking at a glance
↑ "Color scale" is a function that changes the color of cells according to the position of the value. First select the range.
↑ All you have to do is select "Color Scale" in the "Conditional Format" menu and select the color scheme.
↑ Here, select a pattern in which the color becomes lighter as the ranking goes down. You can intuitively grasp the ranking of numerical values.
