Excel Tips: Conditional formatting, formula updates & quick selection

Copying Conditional Formatting

Conditional formatting is part of the regular formatting of a cell. If you want to copy the conditional format to a range of cells you need to do the following:

In Excel, conditional formatting is considered part of the regular formatting of a cell. If you want to copy conditional formatting from one cell to another, you can do so by simply copying the cell and pasting it (or its format) to another cell. If you want to copy a conditional format to a range of cells (and only the conditional format), the easiest way to do so is by following these general steps:

1. Select the cell that contains the conditional format you want copied.
2. Display the Home tab of the ribbon.
3. In the Styles section, click Conditional Formatting. Excel displays various options related to conditional formatting.
4. Click Manage Rules. Excel displays the Conditional Formatting Rules Manager. The format you want copied should already be filled in. Select whatever appears in the Applies To box.
5. Using the mouse, click and drag to select the range of cells to which the conditional format should be applied.

That's it. Excel does the rest and copies the conditional formatting, as you desired. 

Alan Salmon, founder, K2E Canada

Quickly Selecting Cells

Instead of using the mouse to select a range of cells, here is a quicker way of getting the job done.  

You can use the mouse to select a range of cells by holding down the left mouse button and dragging the mouse to make a selection. There is an even quicker way to select cells using the mouse. Here is how to do this:

1. Select the cell that marks one corner of the range of cells you want to select.
2. Move the mouse pointer so it points to the cell at the opposite diagonal corner of the range you want selected.
3. Hold down the Shift key as you click once on the left mouse button.

That will select all the cells in the range.

Watching a Formula Update on Another Sheet

The Watch Window in the Formula Tab lets you watch a cell update as you make changes to it. This is highly useful when you have formulas that span multiple sheets and you need to watch a cell on one sheet while you make changes to another sheet.  

1. Select the Formula tab.
2. In the Formula Auditing group select Watch Window.
3. Click Add Watch to open the Add Watch dialog box.
4. Select the cell on the worksheet that you want to watch update and click Add.
5. Repeat steps 3 and 4 to add additional cells to monitor.

If you leave the Watch window open and make changes to cells that will affect the watched cells you will see the changes in the Watch Window. 

Click on the Watch Window icon again to minimize the Watch window area.

Alan Salmon is recognized as Canada’s leading analyst in accounting technology. As the founder of K2E Canada Inc., Alan has over 34 years of business, management systems, education and journalism experience