Conditional Formatting

7 Key excerpts on "Conditional Formatting"

• 

  eBook - ePub

  Exploring Microsoft Excel's Hidden Treasures

  • David Ringstrom(Author)
  • 2022(Publication Date)
  • Packt Publishing

    (Publisher)

  4

  Conditional Formatting

  In this chapter, I’ll show you the wonders of Excel’s Conditional Formatting feature that can help you enliven your data with color, charts, or icons. You can, of course, use all three at once, but usually, less is more when it comes to data visualization. Conditional Formatting is a live overlay for worksheet cells that dynamically applies formatting such as font color, cell color, borders, and number formatting, based upon the cell contents matching specific criteria. The built-in Conditional Formatting rules eliminate the need to manually color-code data, and you can extend this functionality in limitless ways by crafting custom rules. Along the way, I’ll point out nuances that frustrate even the most experienced spreadsheet users.

  In this chapter, I’ll cover the following:

  • Formatting versus Conditional Formatting
  • Highlight Cell Rules
  • Top and Bottom Rules
  • Data Bars
  • Color Scales
  • Icon Sets
  • Wingdings symbols
  • Custom rules
  • Managing Conditional Formatting rules
  • Removing Conditional Formatting
  • Conditional Formatting nuances

  By the end of the chapter, you’ll see numerous ways that Conditional Formatting can identify and illustrate data based on criteria that you specify. Twenty years ago, Excel had a limit of no more than three Conditional Formatting rules per worksheet, but now there is no practical limit to the number of rules that you can apply to a worksheet.

  Technical requirements

  Everything in this chapter will work the same in Excel for macOS and Excel for Windows.

  At the time of writing, Microsoft has made some changes to the Conditional Formatting feature in Excel for the Web

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Excel for the CFO

  • P. K. Hari(Author)
  • 2012(Publication Date)
  • Holy Macro! Books

    (Publisher)

  Conditional Formatting is an Excel feature that helps you visually generate specific reviews about your data and easily answer questions at a glance. You can use Conditional Formatting to quickly identify and mark cells which meet a particular condition or criteria. If any cells in the selected range meet the conditions, they are applied with the formats that you have set. Cells that do not meet the conditions remain unchanged (no change in formatting). You can apply Conditional Formatting to a range of cells, an Excel table, or a Pivot Table report.

  Caution: There are certain critical differences you need to understand before you can apply Conditional Formatting on a Pivot Table report, as compared to applying the Conditional Formatting on an Excel table or range of cells.

  For instance, when you are analyzing a large volume of sales-related performance data, you may come up with the following questions, which could take quite some time to compute, analyze, and answer:

  • • Which products of the company have the highest profit-generating potential?
  • • Which salespeople have achieved more than 25% growth on YoY (Year-on-Year) basis?
  • • Who are the repeat customers of the company?
  • • Which product range should be phased out from the manufacturing line?
  • • What are the different market segment types for the company’s products?

  Conditional Formatting helps you to answer these questions and more by making it easy to highlight interesting ranges of cells, highlight deviations and outliers, and visualize data by using a combination of color scales, data bars, and icon sets. The following sections describe the various types of Conditional Formatting.

  You can download a sample workbook containing examples for this section on Conditional Formatting from

  http:// www.hari.ws/excel/ch11condformat.zip

  .

  Formatting Cells Using a Two-Color Scale

  You can use a two-color scale to compare a range of cells by using a gradation of two colors. The shade of the color represents higher or lower values. For example, in a red and blue color scale, you can specify that higher-value cells have a red color and lower-value cells have a blue color.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Office 2016 For Dummies

  • Wallace Wang(Author)
  • 2016(Publication Date)
  • For Dummies

    (Publisher)

  A formula in a cell can display a variety of values, depending on the data that the formula receives. Because a formula can display any type of a number, you may want to use Conditional Formatting as a way to highlight certain types of values.

  Suppose you have a formula that calculates your monthly profits. You can change the formatting to emphasize the results:

  • If your result is zero or a loss, display that value in red.
  • If you make a profit under $1,000, display that value in yellow.
  • If the profit is at least $100,000, display that value in green.

  Conditional Formatting simply displays data in a formula in various ways, depending on the value that the formula calculates.

  Comparing data values

  The simplest type of Conditional Formatting displays different colors or icons based on adjacent values, which makes it easy to compare different numbers at a glance.

  Excel offers three types of Conditional Formatting for identifying values, as shown in Figure 9-6 :

  • Data Bars: Higher values display more color while lower values display less color.
  • Color Scales: Different colors identify different ranges of values.
  • Icon Sets: Different icons identify different ranges of values.

  FIGURE 9-6: Conditional Formatting can identify values of different cells.

  To apply Conditional Formatting, follow these steps:

  1. Select the cells that you want to apply Conditional Formatting.
  2. Click the Home tab and then click the Conditional Formatting icon in the Styles group.
     A menu appears (refer to Figure 9-6 ).
  3. Move the mouse over Data Bars, Color Scales, or Icon Sets.
     A menu appears.
  4. Click the type of Conditional Formatting you want.

  Sign up to read

  Learn more about book
• 

  No longer available |Learn more

  Excel Basics

  Foundations • Formulas • Graphs

  • Jeffrey Hsu, Gary Bronson(Authors)
  • 2021(Publication Date)
  • Mercury Learning and Information

    (Publisher)

  The highlighting of cells, using background cell color and/or font styles and color, is accomplished by formatting. The selection of which cells to highlight, based on meeting specified conditions, is accomplished using Excel’s Conditional Formatting option. This option provides a complete sub-system of choices that permit you to select (or create your own) criteria, select (or create your own) highlighting formats, select where the conditional formats should be applied, and then permit you to add or change any of your individual choices. Additionally, Conditional Formatting provides other more advanced options, including in-cell bar graphs (data bars), color scales, and icon sets. In this section, we present the highlighting (Highlight Cells) option.

  Conditional Formatting has as its main the goal the creation of specific rules that control the formatting of designated cells. This feature is activated by clicking on the Home tab’s Conditional Formatting icon, as shown as item 1 on Figure 3.15 . Although it is preferable to select the cells you want formatted before clicking on this icon, the cell selection can be made and changed later.

  Figure 3.15 Activating Conditional Formatting

  Application Note

  Conditional Formatting Rules

  A Conditional Formatting rule used for highlighting requires three elements:

  1. Selecting where the formatting is to be applied
  2. A specification of the conditional criteria
  3. Applying the format to cells meeting the condition

  Each separate area (group of cells being formatted) requires at least one rule. Multiple rules can be applied to the same area. If two separate areas are designated, at least two separate and distinct conditional rules must be constructed.

  If more than one rule is specified, they are applied from the last rule entered down to the first rule entered, with each rule higher on the list having precedence over a rule lower on the list. Another way of saying this is that the last rule entered is placed on the top of the list of rules, and is applied first, and so on, until the first rule entered, which is at the bottom of the list, and is applied last. The rule order can be changed using the Manage Rules option.

  When Conditional Formatting is selected, the drop-down menu shown in the figure appears. In this section, we will choose the first option Highlight Cells Rules, which is identified as item 2 in Figure 3.15

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  New Perspectives Microsoft® Office 365 & Excel 2016

  Intermediate

  • Shaffer/Carey/Parsons/Oja/Finnegan, June Jamrich Parsons, Dan Oja, Patrick Carey, Carol DesJardins(Authors)
  • 2016(Publication Date)
  • Cengage Learning EMEA

    (Publisher)

  All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-300 Module 2 Formatting Workbook Text and Data | Excel EX 107 Highlighting Data with Conditional Formats Conditional Formatting is often used to help analyze data. Conditional Formatting applies formatting to a cell when its value meets a specified condition. For example, Conditional Formatting can be used to format negative numbers in red and positive numbers in black. Conditional Formatting is dynamic, which means that the formatting can change when the cell’s value changes. Each conditional format has a set of rules that define how the formatting should be applied and under what conditions the format will be changed. REFERENCE Highlighting Cells with Conditional Formatting Figure 2-32 Highlight Cells rules Rule Highlights Cell Values Greater Than Gr eater than a specied number Less Than Less than a specied number Between Between two specied numbers Equal To Equal to a specied number Text that Contains That contain specied text A Date Occurring That contain a specied date Duplicate Values That contain duplicate or unique values Carol wants to highlight important trends and sales values in the Sales Report worksheet. She asks you to highlight sales statistics that show a negative net change or negative percent change from the previous year to the current year. You will use Conditional Formatting to highlight the negative values in red. Excel has four types of Conditional Formatting—data bars, highlighting, color scales, and icon sets. In this module, you will use Conditional Formatting to highlight cells. Highlighting Cells Based on Their Values Cell highlighting changes the cell’s font color or fill color based on the cell’s value, as described in Figure 2-32. You can enter a value or a cell reference if you want to compare other cells with the value in a certain cell.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Advanced Excel Reporting for Management Accountants

  • Neale Blackwood(Author)
  • 2014(Publication Date)
  • Wiley

    (Publisher)

  Conditional Formatting is another important feature in formatting reports. It allows you to automatically apply different cell formats based on cell values or on cell values in relation to other values in a range. For example, you can change the fill colour of a cell to red if it is negative or green if it is positive. Conditional Formatting also allows you to build formulas to determine what format to apply. It is especially useful for variance reporting.

  Once you have set up a conditional format, it will continue to work in the background. Conditional formats, like any other format, can be copied and pasted between cells. The Format Painter option includes Conditional Formatting. Conditional formats can also be applied to pivot tables and formatted tables.

  Excel 2007 greatly expanded the options available for Conditional Formatting. It added icons that display when cells meet certain criteria. This is especially useful for dashboard reporting. Many conditional formats that required formulas in previous versions have been included as standard conditional formats in Excel 2007 and later versions.

  Excel 2003 had a limit of three conditions. Excel 2007 and later versions removed that limit. There is now no set limit to the number of conditions you can apply. You can also include different types of conditional formats together.

  Excel 2010 improved the Conditional Formatting feature and fixed a few bugs with it.

  Tip: Conditional Formats and Styles

  Unfortunately, you can’t include a conditional format in a style definition.

  Conditional formats can be used for the following types of reports:

  • Variance reporting. Using icons and colour to emphasise values outside expected levels.
  • Exception reporting. Using icons and colour to highlight values outside acceptable levels.
  • Pareto analysis. Identifying upper and lower values or percentages within a range.
  • Dashboards. Using icons and data bars (to be explained later) to visualise data in combination with charts
  • Validation. Automatically highlighting invalid, duplicate. or missing data.

  The Conditional Formatting icon is in the middle of the Home Ribbon tab. The built-in options are easy to use, but the results may not always be what you expect. Excel uses default settings to apply many of the built-in conditional formats. You can amend these settings to achieve the conditional format that you want.

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Illustrated Microsoft®Office 365 & Excel® 2016

  Comprehensive

  • Beskeen, Elizabeth Reding, Lynn Wermers(Authors)
  • 2016(Publication Date)
  • Cengage Learning EMEA

    (Publisher)

  Compare your results to FIGURE 3-19 . 7. Press [Ctrl][Home] to select cell A1, then save your work Apply Conditional Formatting So far, you’ve used formatting to change the appearance of different types of data, but you can also use formatting to highlight important aspects of the data itself. For example, you can apply formatting that changes the font color to red for any cells where the value is greater than $100 and to green where the value is below $50. This is called Conditional Formatting because Excel automatically applies different formats to data if the data meets conditions you specify. The formatting is updated if you change data in the worksheet. You can also copy conditional formats the same way you copy other formats. CASE uni00A0Mary uni00A0 is concerned about advertising costs exceeding the yearly budget. You decide to use Conditional Formatting to highlight certain trends and patterns in the data so that it’s easy to spot the most expensive advertising. You can also use the Quick Analysis tool to create data bars, but with fewer choices. QUICK TIP You can apply an Icon Set to a selected range by clicking the Conditional Format -ting button in the Styles group, then pointing to Icon Sets; icons appear within the cells to illustrate differences in values. QUICK TIP To define custom for-matting for data that meets the condition, click Custom Format at the bottom of the with list, and then use the Format Cells dialog box to set the formatting to be applied. QUICK TIP Copyright 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-300 Formatting a Worksheet Excel 65 Excel 2016 FIGURE 3-19: Worksheet with Conditional Formatting If you create a Conditional Formatting rule and then want to change a condition, you don’t need to create a new rule; instead, you can modify the rule using the Rules Manager. Click the Conditional Formatting button in the Styles group, then click Manage Rules.

  Sign up to read

  Learn more about book