CONDITIONAL FORMATTING/DYNAMIC NAMED RANGES

LESSON WORKBOOK:  Level 3 Lesson 8 2007.xlsx

Conditional Formatting

In Excel 97, Microsoft introduced a nifty new feature called Conditional Formatting. It can be found via the Home tab under Styles options. It allows us to format a cell, or range of cells, based on a specified criteria for the cell(s), or other cell(s).

A simple example would be to use Conditional Formatting to highlight all cells in any given range that are greater than 100. Try this.

  1. Open the attached workbook.

  2. Select cells A1:A100, Starting from cell A1.

  3. This will ensure A1 is the active cell in the selection and from this, Excel will know all other cells we specify are relative to cell housing the Conditional Formatting.

  4. Now go to Conditional Formatting under Styles options on the Home tab and choose New Rule.  Under Select a Rule type: at the top of the dialog, select Format only cells that contain.  Have a look at the other Rule types while you are here.

  5. Under Edit Rule Description ensure Cell Value is selected, then in the next box to the right select greater than.

  6. In the next box to the right enter in 350

  7. Click the Format button then go to the Fill tab and select a colour and click OK, then OK again.


You should see cells only with numbers greater than 350 filled with the colour you selected.

Lets try another example.

  1. Highlight the range B1:B100.

  2. Now go to Conditional Formatting under Styles options on the Home tab and choose New Rule.  Under Select a Rule type: at the top of the dialog, select Format only unique or duplicate values

  3. Under Edit Rule Description ensure duplicates is selected under Format all

  4. Click the Format button then go to the Fill tab and select a colour and click OK, then OK again.  Note other options for formatting here include the Font tab and Borders tab.

If you want to see and even edit the rules on your worksheet, go to Conditional Formatting under Styles options on the Home tab and choose Manage Rules.  Under Show formatting rules for: at the top of the dialog, ensure This worksheet is selected.

You could do the same thing with a formula, like this:

  1. Highlight C1:C100

  2. Select Conditional Formatting under Styles options on the Home tab and choose New Rule

  3. Under Select a Rule type: at the top of the dialog, select Use a formula to determine which cells to format

  4. Enter in =COUNTIF($C$1:$C$100,C1)>1

  5. Click the Format button and choose any desired format to apply to any duplicate entries.

  6. Click OK then OK again and we are done!
     

Now, all duplicates in the range C1:C100 will have the format you have chosen. It is important to note the Absolution of $C$1:$C$100 in the COUNTIF function and that the single reference (C1) is a Relative reference. This is why step 1 states to Start from C1 (in this case) ensuring C1 becomes the active cell. The "Formula is" in the Conditional Formatting for C2 will change automatically to;

=COUNTIF($C$1:$C$100,C2)>1

Note the $C$1:$C$100 never changes due to the use of Absolution on both the column ("C") and the rows (1:100)

Other Options

Select Conditional Formatting and check out the other Options available to you.  The top two options Highlight cell rules and Top/Bottom Rules are self explanatory.  Ensure you have your range selected however before you try to activate them.

Now highlight the range B1:B100 again then Conditional Formatting under Styles options on the Home tab and choose Data bars.  Notice how the bars are staggered according to the cell values.  Also note that the Conditional Formatting remains.  Experiment with Colour scales and Icon sets to see the results.

Dynamic Named Ranges

Dynamic Named Ranges are possibly one of Excels most underutilized aspects.  A named range is a user-friendly name given to a block of cells. 

Open up the attached workbook and try the following:

  1. On the Dynamic Named Ranges page highlight A1:E11

  2. Click in the Name box (above column A) and enter in the word Sales and then click Enter (Note the Name box does not like spaces in names, but it will allow an underscore _)

  3. Keep the range highlighted and go to the Insert tab and select PivotTable, then Enter

  4. Ensure that the Item number is the Report Filter

  5. The Row Labels are Description

  6. The Values are Total and Quantity

That completes our PivotTable, but lets make it more user-friendly.

  1. Click on the Options tab

  2. Select Change Data Source under Data options

  3. Replace 'Dynamic Named Ranges'!$A$1:$E$11 in the Table/Range box with the word Sales and click OK.

Much more user-friendly I am sure you will agree.  Now this works, if we want to update our PivotTable, all we have to do is right click on it and select Refresh  and the new figures will flow through.  Try it by going back to the Dynamic Named Ranges worksheet and changing cell C2 to read 5.  Now click back on your PivotTable, right click inside it and select Reresh     You should see the new figures flow through.

But what if we want to add extra rows to our data and we always want our PivotTable to pick up the extra rows.  (Remember at present our PivotTable range is Sales which is in effect $A$1:$E$11).

To do this we would use a Dynamic Named Range like this:

  1. Go to the Formulas tab and under Defined Name options select Define Name

  2. Enter in the name Sales_Table and click tab to tab through to the Refers to: box.

  3. Enter in =OFFSET($A$1,0,0,COUNTA($A:$A),5) and click OK.

Now go back to your PivotTable and change the source data to Sales_Table and then go back to your source data on the original sheet and enter in a new row of data.  Now go back to your PivotTable and right click to see the new information flow through.  You will only ever have to refresh the PivotTable as this Dynamic Named Range will expand down as many rows as there are text or numeric entries.

Here are some more examples of Dynamic Named Ranges:

For ALL examples you will need to Fill Column A with a mix of text and numeric entries.

  1. Go to the Formulas tab and under Defined Name options select Define Name

  2. Enter in the name My_Range and click tab to tab through to the Refers to: box.

Expand Down as Many Rows as There are Numeric Entries.
    In the Refers to box type: =OFFSET($A$1,0,0,COUNT($A:$A),1)

Expand Down as Many Rows as There are Numeric and Text Entries.
    In the Refers to box type: =OFFSET($A$1,0,0,COUNTA($A:$A),1)

Expand Down to The Last Numeric Entry
    In the Refers to box type: =OFFSET($A$1,0,0,MATCH(1E+306,$A:$A,1),1)
If you expect a number larger than 1E+306 (a one with 306 zeros) then change this to a larger number.

Expand Down to The Last Text Entry
    In the Refers to box type: =OFFSET($A$1,0,0,MATCH("*",$A:$A,-1),1)

Expand Down Based on Another Cell Value
    Put the number 10 in cell B1 first then:
In the Refers to box type: =OFFSET($A$1,0,0,$B$1,1)
Now change the number in cell B1 and the range will change accordingly.

Expand Down One Row Each Month
    In the Refers to box type: =OFFSET($A$1,0,0,MONTH(TODAY()),1)

Expand Down One Row Each Week
    In the Refers to box type: =OFFSET($A$1,0,0,WEEKNUM(TODAY()),1)
Requires the "Analysis Toolpak" to be installed. Tools>Add-ins-Analysis Toolpak

As you can see, using a Dynamic Named Range makes working with larger ranges much more user-friendly and efficient.

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid.com is in no way associated with Microsoft.

OzGrid.com accepts no responsibility for any adverse affect that may result from undertaking our training.