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.
Open the attached workbook.
Select cells A1:A100, Starting from cell A1.
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.
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.
Under Edit Rule Description ensure Cell Value is selected, then in the next box to the right select greater than.
In the next box to the right enter in 350
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.
Highlight the range B1:B100.
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
Under Edit Rule Description ensure duplicates is selected under Format all
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:
Highlight C1:C100
Select 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 Use a formula to determine which cells to format
Enter in =COUNTIF($C$1:$C$100,C1)>1
Click the Format button and choose any desired format to apply to any duplicate entries.
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:
On the Dynamic Named Ranges page highlight A1:E11
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 _)
Keep the range highlighted and go to the Insert tab and select PivotTable, then Enter
Ensure that the Item number is the Report Filter
The Row Labels are Description
The Values are Total and Quantity
That completes our PivotTable, but lets make it more user-friendly.
Click on the Options tab
Select Change Data Source under Data options
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:
Go to the Formulas tab and under Defined Name options select Define Name
Enter in the name Sales_Table and click tab to tab through to the Refers to: box.
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.
Go to the Formulas tab and under Defined Name options select Define Name
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.