Using Conditional Formatting

Category: Formatting | [Item URL]

Excel's conditional formatting feature (available in Excel 97 or later) offers an easy way to apply special formatting to cells if a particular condition is met. This feature is even more useful when you understand how to use a formula in your conditional formatting specification.

The worksheet below shows student grades on two tests. Conditional formatting highlights students who scored higher on the second test. This formatting is dynamic; if you change the test scores, the formatting adjusts automatically.

To apply conditional formatting, select range A2:C15 and choose Format, Conditional Formatting. The Conditional Formatting dialog box will appear with two input boxes. In the first box, choose Formula Is, pressTab, and enter the following formula:

=$C2>$B2

Click Format and choose a format to distinguish the cells (the example uses background shading). Click OK, and the formatting will be applied.

The conditional formatting formula is evaluated for each cell in the range. The trick here is to use mixed cell references (the column references are absolute, but the row references are relative). To see how this works, activate any cell within the range and choose Format, Conditional Formatting so you can examine the conditional formatting formula for that cell. You'll find that cell A7, for example, uses this formula:

=$C7>$B7


Fix Incorrect Decimal Places During Data Entry

Category: Formatting | [Item URL]

Q. When I enter a value, it appears with two decimal places. For example, when I enter 154 it shows up as 1.54. What's wrong?

Somehow Excel's fixed-decimal mode was turned on. To return to normal, select Tools, Options to display the Options dialog box. Then click the Edit tab and remove the checkmark from the "Fixed decimal" option.

Of course, this feature can be useful when entering some types of data, but most of the time, you'll want to keep the fixed-decimal mode turned off.



Display Text In Multiple Lines

Category: Formatting | [Item URL]

Q. How can I make text in a cell display in multiple lines?

When entering text into the cell, press Alt-Enter to insert a line break.

When you do so, Excel will automatically apply text wrapping to the cell. To reformat existing cells so they sport wrapped text, select the cells and then choose Format, Cells. On the Alignment tab, select "Wrap text," and click OK.


Changing The Default Cell Comment Formatting

Category: Formatting | [Item URL]

Excel's Insert - Comment command lets you annotate a cell by typing a comment. Once the comment is created, you can change its formatting. To do this, right-click the cell and select Edit Comment from the shortcut menu. When the comment is displayed, click anywhere on its border to select the entire comment object. Now you can use the standard toolbar buttons to change the font, text size, or colors.

To change the default formatting of your cell comments (so you don't have to repeat that process each time), you need to go outside Excel and use the Windows Display Settings dialog box. Excel uses the formatting specified for Windows' ToolTips (the text that appear when your pointer hovers near toolbar buttons).

You can access the Display Settings dialog box from the Control Panel. Or, you can right-click the desktop and choose Properties. Access the Appearance tab of the Display Properties dialog box, and select the ToolTip item. You can then modify the font, the font size, the background color, the foreground color, and the Bold and Italic attributes.

NOTE: If you're using Windows XP, you need to click the Advanced button in the Appearance tab of the Display Properties dialog box. This brings up the Advanced Appearance dialog box, where you can make the change.

Be aware that this will affect all comments that have not been formatting. If you've already applied formatting to a comment (for example, changed the background color), that comment will not be affected by this change.


Dealing With Negative Time Values

Category: Formulas | [Item URL]

Because Excel stores dates and times as numeric values, it's possible to add or subtract one from the other.

However, if you have a workbook containing only times (no dates), you may have discovered that subtracting one time from another doesn't always work. Negative time values appear as a series of hash marks (########), even though you've assigned the [h]:mm format to the cells.

By default, Excel uses a date system that begins with January 1, 1900. A negative time value generates a date/time combination that falls before this date, which is invalid.

The solution is to use the optional 1904 date system. Select Tools, Options, click the Calculation tab, and check the 1904 date system box to change the starting date to January 2, 1904. Your negative times will now be displayed correctly, as shown below.

Be careful if you workbook contains links to other files that don't use the 1904 date system. In such a case, the mismatch of date systems could cause erroneous results.



Converting Non-numbers To Actual Values

Category: Formulas | [Item URL]

Q. I often import data into Excel from various applications, including Access. I've found that values are sometimes imported as text, which means I can't use them in calculations or with commands that require values. I've tried formatting the cells as values, with no success. The only way I've found to convert the text into values is to edit the cell and then press Enter. Is there an easier way to make these conversions?

This is a common problem in Excel. The good news is the Excel 2002 is able to identify such cells and you can easily correct them If you're using an older version of Excel, you can use this method:

  1. Select any empty cell
  2. Enter the value 1 into that cell
  3. Choose Edit, Copy
  4. Select all the cells that need to be converted
  5. Choose Edit, Paste Special
  6. In the Paste Special dialog box, select the Multiply option, then click OK.

This operation multiplies each cell by 1, and in the process converts the cell's contents to a value.


Compare Ranges By Using An Array Formula

Category: Formulas | [Item URL]

In Excel, you can compare the cells in two ranges with an array formula. For instance, to see if all of the values in A1:A100 are identical to those in B1:B100, type this array formula:

=SUM(IF(A1:A100=B1:B100,0,1)) 

Note: This is an array formula and it must be entered using Ctrl-Shift-Enter.

The formula will return the number of corresponding cells that are different. If the formula returns 0, it means that the two ranges are identical.


Calculate The Number Of Days In A Month

Category: Formulas | [Item URL]

Excel lacks a function for calculating the number of days in a particular month, so you'll need to construct your own formula.

If cell A1 contains a date, this formula will return the number of days in the month:

=DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-1)


Identify Formulas By Using Conditional Formatting

Category: Formulas | [Item URL]

How many times have you accidentally deleted or overwritten cells containing formulas only to discover the mistake after it's too late? One solution is to write-protect important cells. Another approach is to give those cells a visual flag.

This clever technique was submitted by David Hager. It uses Conditional Formatting (available in Excel 97 or later) to apply special formatting to cells that contain formulas--something that's not normally possible. With this technique you can set up your worksheet so that all formula cells get a yellow background, for example, or so that negative values are in boldface.

Follow these steps:

  1. Select Insert, Name, Define.
  2. In the Define Name dialog box, enter the following in the 'Names in workbook' box
    CellHasFormula 
  3. Then enter the following formula in the "Refers to" box
    =GET.CELL(48,INDIRECT("rc",FALSE))
  4. Click Add, and then OK.
  5. Select all the cells to which you want to apply the conditional formatting.
  6. Select Format, Conditional Formatting
  7. In the Conditional Formatting dialog box, select Formula Is from the drop-down list, and then enter this formula in the adjacent box (see the figure below):
    =CellHasFormula
  8. Click the Format button and select the type of formatting you want for the cells that contain a formula.
  9. Click OK.

After you've completed these steps, every cell that contains a formula and is within the range you selected in Step 4 will display the formatting of your choice.

How does it work? The key component is creating a named formula in Steps 2 and 3. This formula, unlike standard formulas, doesn't reside in a cell, but it still acts like a formula by returning a value -- in this case either 'True' or 'False'. The formula uses the GET.CELL function, which is part of the XLM macro language (VBA's predecessor) and cannot be used directly in a worksheet. Using a value of 48 as the first argument for GET.CELL causes the function to return 'True' if the cell contains a formula. The INDIRECT function essentially creates a reference to each cell in the selected range.



Calculating A Conditional Average

Category: Formulas | [Item URL]

In the real world, a simple average often isn't adequate for your needs.

For example, an instructor might calculate student grades by averaging a series of test scores but omitting the two lowest scores. Or you might want to compute an average that ignores both the highest and lowest values.

In cases such as these, the AVERAGE function won't do, so you must create a more complex formula. The following Excel formula computes the average of the values contained in a range named "scores," but excludes the highest and lowest values:

=(SUM(scores)-MIN(scores)-MAX(scores))/(COUNT(scores)-2)

Here's an example that calculates an average excluding the two lowest scores:

=(SUM(scores)-MIN(scores)-SMALL(scores,2))/(COUNT(scores)-2)


Page 12 of 17 pages
[Previous page]   [Next page]

Search for Tips


All Tips

Browse Tips by Category

Tip Books

Needs tips? Here are two books, with nothing but tips:

Contains more than 100 useful tips and tricks for Excel 2013 | Other Excel 2013 books | Amazon link: 101 Excel 2013 Tips, Tricks & Timesavers

Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks

© Copyright 2017, J-Walk & Associates, Inc.
Privacy Policy