Conditional formats

Most financial analysis focuses on highlighting exceptions in rows or columns of data, and not the whole table.

With conditional formatting, you can highlight specific values or cells, by changing what a cell looks like based on a set of conditions that you select. For example, you might want to highlight cells in S Series models that have higher sales than L Series models.

You can apply conditional formatting to both numeric and text values, and you can highlight empty cells by using conditional formatting.

Conditional formatting can also be applied to cell values that have been added to the sheet.

You can watch this video to see a demonstration of conditional formatting.

Video that describes how to create conditional formats.

https://youtu.be/lyeopaHk-Vw

Note: You cannot add conditional formatting on the Apple iPad.

Procedure

  1. Ensure that you are in edit mode.
    Note: You can add conditional formats when you are not in edit mode, but you can't save them.
  2. Right-click on a row or column header, or cell value that has been added to the sheet, and select Conditional format. In the Conditional format window, the member in the header that you selected is displayed. Change this selection by clicking the member name and selecting a new member.
    1. Choose one of the operators that you want to apply.

      For numeric values, you can choose one of the following options:

      <
      Less than
      >
      Greater than
      =
      Equal
      <>
      Not equal to
      >=
      Greater than or equal to
      <=
      Less than or equal to
      For text values, you can select one of the following options:
      • Equals
      • Contains
      • Starts with
      • Ends with
      • Is empty
    2. Select whether you want to compare the first member to another member, to a value, or to text.
    3. Select the member, enter the value, or enter the text that you want to compare the first member to. If you want to highlight empty text cells, leave this cell empty.
  3. Click edit icon and select what you want the cell to look like. You can select cell format (cell color and border color), font color or style, and icon sets.
    A preview of the formatting is shown.
    For example:
    Image showing the selection S Series > member - L Series. A preview of 100 is shown in bold and red.
  4. Click Apply.
  5. To add more conditional formats, click Add icon.
  6. Reorder the conditional formats by using the arrow keys.Move upMove down
    The order of conditional formats determines which formatting is used when there are conflicts between formats.

    If you have a format on both a row and on a column, and there are conflicts between the formats, the column formatting applies. For example, suppose that you have a different color background in the row and column, the color selected for the column is used.

  7. Click Save.
  8. To clear formatting, right-click on the column heading and select Conditional format. Select the formats that you want to delete, and click Delete icon.