Excel 2007 Conditional Row Formatting

It’s pretty easy to highlight every other row in Excel using a condition. In the matters of highlighting a row based on a value of single cell is another story. Not entirely complex but nonetheless requires some knowledge of writing formulas and knowledge of the INDIRECT function. Bastien blogged about Conditional row color based on a cell value which illustrates the process quite nicely. The steps I am about to cover mimic Bastien’s procedures with the exception of targeting Excel 2007 instead of Excel 2003.

Sample data set

Step 1: Highlight the rows

You will need to highlight the rows that are targeted in which you wish to apply the conditional formatting.

Step 2: Click on Conditional Formatting

Conditional Formatting is found under the Home tab of the Ribbon.

Step 3: Select Manage Rules

Step 4: Click New Rule

Step 5: Select Use a formula to determine which cells to format

Step 6: Enter the following formula which uses the INDIRECT function

=INDIRECT("C"&ROW())="M"

Then click the Format button to specify the formatting options

Step 7: Click the Fill tab

Step 8: Select the background color

Once you have selected a color click the OK button to proceed

Step 9: Click OK

Step 10: Click OK to apply formatting

Final results

To highlight the rows that have a Gender value of F in column C simply repeat steps 4 – 10 and switch the conditional value to F as in (Step 6)

Formula Syntax:

=INDIRECT("C"&ROW())="F"
Advertisements

Your mailbox is over the limit!

Where I work we have a very small storage alottment per our exchange profiles. We are limited to roughly 256 mb (give or take), which you can imagine can and will fill up very quickly. Especially with such high volume of email exchanges nowadays. In order to circumvent the “nasty graham” reminders I setup two client-only outlook rules after a suggestion from a colleague of mine.

I created two folders nested in a backup folder. One called Inbox and the other Sent. Keep in mind that I only check work email on one laptop but I do have a blackberry which the Sent Route Filter does not apply to. So any reponses from my blackberry will be found in the Sent Items folder. So keep that in mind. If you check mail from multiple pcs using the Outlook client (not including the web client) you will have issues with your emails scattered between systems so also be aware of that.

Step 1: Disable saving to Sent Items

Open up Outlook then go to Tools –> Options

In the Options dialog click the E-mail Options… button

Uncheck Save copies of messages in Sent Items folder then click OK

Click OK to close the Options dialog

Step 2: Setup the Rules

Go to Tools –> Rules and Alerts…

Click on the New Rule… button

Select Check messages after sending then click Next

Select on this machine only then click Next

Select move a copy to the specified folder, then click on the specified link in step 2

Select your new destination folder, then click OK

Click Next on the Rules Wizard dialog

Click Next again on the Rules Wizard dialog

Finally specify a name for the rule and click Finish

Side Note

If you receive this message simply click OK to proceed

Click Ok to close the Rules and Alerts window

Step 3: Test the Rule

Send a message from within Outlook and it should end up in your new destination folder

Success!

Final words

Now I have walked you through the process of creating a rule that moves all your sent mail to a custom destination folder, its your turn to do it for the inbox. Now create a rule for your inbox following similar procedures from (Step 2) with a slight variation of selecting a different inbox folder and specify a different rule name.

Good luck!

Analysis Tool Pak Plug-in for Excel 2007

In the event you have Office 2007 and need to install the Analysis Tool Pak plug-in here is the step-by-step.

Installing the Add-in

1) Click the Office button

2) Select Excel Options

3) Select Add-ins

4) Select Analysis Tool Pak

5) Click OK

Validate the Install

1) Select the Data Tab

2) Analysis Group is Missing

Load the group

1) Click the Office button

2) Select Excel Options

3) Manage Add-Ins

4) Select Analysis Tool Pak

5) Click OK

Validate the Group

1) Select the Data Tab