10 easy steps to customizing an Outlook 2010 form

Takeaway: Outlook forms can streamline your email chores — especially when they’re designed with your needs in mind. This walk-through shows how easy it is to set up your own custom forms.

Creating a custom Outlook form is easier than you might think. You’ll begin with a template that already contains most of the functionality you’ll need. You can add and delete fields and controls. You can even use VBScript to automate specifics tasks. While creating and implementing a custom form is easy, it does require several steps and for that reason alone, can be a bit confusing. I’ll walk you through the process and even show you how to add a bit of VBScript magic.

1: Determine your needs

You’ll always begin a custom form by modifying one of the default forms shown in Figure A. Perhaps users need an updated message or journal entry form. If you’re sharing calendar data, you’ll probably start with an appointment or even a task form. You’ll have to alter the default — that’s the whole point — but the default gives you a good head start.

Figure A

Base a custom form on one of the many default forms.

2: Display the Developer tab

The tools you need for customizing a form are on the Developer tab in Outlook 2010. To display this tab, click the File tab and select Options under Help. Then, click Customize Ribbon in the left pane, check Developer in the list to the right (under the Customize The Ribbon drop-down), and click OK.

3: Open a form template in design view

In step 1, you determined your task and chose an existing form to customize. Now, you’re ready to open that form in design view. To do so, click the Developer tab (step 2) and click Design A Form in the Custom Forms group. In Outlook 2007, choose Forms from the Tools menu and then choose Design A Form. The default library, the Standard Forms Library (shown in Figure A), is the right library. Select Contact and click Open. Outlook will open the default form in design view, as shown in Figure B.

Figure B

You’ll begin with a default form in design view.

4: Add and change fields

You’ll start with a default form and remove what you don’t need and insert what you do. Deleting a field is easy: Select it and press [Delete]. For instance, select the Business Fax components (one at a time or hold down [Shift] as you click each) and press [Delete] to delete them. To change a field’s label text, right-click the label, choose Edit, and enter the new text. Not all fields can be changed or deleted; if the background is gray and the Field Chooser (shown in Figure B) is visible, you can edit the contents.

5: Add new fields

To add a field, choose the appropriate category from the Field Chooser drop-down and then drag a field to the form. Figure C shows two new name fields: Assistant’s Name and Manager’s Name. If the Field Chooser isn’t visible, click Field Chooser in the Tools group. If the option’s dimmed, you can’t edit (add fields) to the current page. Spend a little time familiarizing yourself with the available fields.

Figure C

Drag and drop new fields onto the custom form.

6: Add a custom field

Many predefined fields are available, but you can create a custom field when necessary. To do so, click New at the bottom of the Field Chooser. Enter a name for the field, define a data type, and specify a format using the Type and Format drop-downs, respectively. Once you’ve defined the field, it will appear in the Field Chooser. Drag it to the form as you would a predefined field.

7: Add a control

Using the Control Toolbox, you can add any of the controls you’re used to working with: label, text, combo box, list box, check box, option button, toggle button, command button, and so on. If the Control Toolbox isn’t visible, right-click the form’s gray background and check Control Toolbox. Then, drag and drop a control to the form and update the label text, if necessary.

If you plan to use VBScript to program the field, click the Display tab and give the control a meaningful name you can refer to easily later. In Figure D, I’ve added a combo box and named itcboDepartment.

Figure D

Rename controls (and fields) if you plan to refer to them in VBScript.

8: Add a little VBScript

It’s likely that you’ll want to enhance the new form by using VBScript. For instance, to populate the combo box with a list of departments, click View Code in the Form group to launch the Script Editor. Then, in the Script Editor window, insert the following lines:

Sub Item_Open()

  Set FormPage = Item.GetInspector.ModifiedFormPages("General")

  Set Control = FormPage.Controls("cboDepartment")

  Control.PossibleValues = "Administrative;Accounting;IT"

End Sub

9: Publish your form

Before anyone can use the custom form, you must publish it to a library or a folder. While still in design view, click Publish in the Form group and choose Publish Form. (The Publish Form and Publish Form As options work similarly to the Save and Save As commands.) In the resulting dialog, choose Outlook Folders from the drop-down, click Browse, and select Contacts. After selecting the folder, click OK and name the form NewContactForm, as shown in Figure E. Finally, click Publish.

Figure E

Designate a folder and give the custom form a name.

To use the form, go to Contacts and click the New Items option in the New group (on the Home tab) to access the custom form, as shown in Figure F. The published form, shown in Figure G, displays the new fields and the combo box populated by VBScript. In Outlook 2007, choose Forms from the Tools menu and then select Choose Form.

Figure F

The custom form will be available for use as soon as you publish it.

Figure G

VBScript populates the combo box you added to the new contact form.

You can also save the custom form to a library by choosing Personal Forms Library instead of Outlook Folders.

10: Make the custom form the default

When using a custom form to modify the default form, you must override the built-in default. Right-click the folder — not the Contacts shortcut, but the Contacts folder under My Contacts, as shown in Figure H. Choose Properties from the resulting context menu. On the General tab (the default), choose the custom folder from the When Posting To This Folder Use drop-down and click OK.

Figure H

Use your custom form as the default, when appropriate.

The 10 most important things to teach your PowerPoint users

Takeaway: Effective presentations are within your users’ reach. They just need to embrace some best practices and master a few techniques.

Competent PowerPoint users have a variety of skills, whether they’re creating or delivering the presentation. But being effective is more about fulfilling a concept than the technical steps required to create individual slides. The following guidelines will help your users be competent, whether they’re creating, presenting, or both.

1: Know how to use the program

Your users must know how to run PowerPoint. More important, they must know how to adapt if the technology fails. Don’t turn your users loose until they’re prepared to face the giant blank screen of death. This advice seems obvious, but many presenters are lost if something technical goes wrong. Don’t let that happen to your users!

2: Know the material

#1 can be a showstopper, but it shouldn’t be. In fact, if the presenter is thoroughly familiar with the material, a technological mishap won’t even matter. Make sure your presenters can carry onwithout the visual aids. The audience will appreciate the message just the same, perhaps more.

3: Practice

Knowing how to deliver the material can mean the difference between an ordinary presentation and a great presentation. Make sure users know how to use PowerPoint’s stopwatch feature to rehearse their presentations. PowerPoint records the time spent on each slide, as shown inFigure A. This information will help users stay on track during the actual presentation. The Rehearse Timings feature is in the Set Up group on the Slide Show tab. In PowerPoint 2003, it’s on the Slide Show menu.

Figure A

Presenters will benefit from timing their rehearsal sessions.

4: Print a slide list

No matter how well presenters know their material, they can benefit from a slide list. First, it’s great for documenting the presentation. Second, it’ll help the presenter find specific data on the fly. (To jump to a specific slide, they can enter the slide number and press [Enter].)

To create a slide list based on slide titles, click the Outline pane’s Outline tab. For a shorter list of just slide titles, collapse the view by right-clicking the pane and choosing Collapse and then Collapse All, as shown in Figure B.

Figure B

Collapse the outline for a list of slides.

To print the list, click the File tab and choose Print. Click the second item under Settings and choose Outline from the Print Layout options. With Outline selected (Figure C), click Print to print the outline view — your slide list. In PowerPoint 2007, click the Office button and click Print. (Choose Print from the File menu in PowerPoint 2003.) Then, choose Outline View from the Print What drop-down and click OK.

Figure C

Choose Outline to generate a list of slides.

5: Keep it simple

Where text is concerned, less is more. Your presenters don’t want the audience reading slides; they want the audience listening to them. Your presenters are the text. Slides are just visual clues that support the discussion. For example, the slide in Figure D is bad — for a number of reasons — but perhaps the worst offense is the amount of text. The introduction is not necessary. That’s what the presenter should be saying.

Figure D

If the presenter says it, the slide doesn’t need to repeat it — delete it!

When a text-heavy slide is necessary, the presenter should hold the slide back until it’s relevant and then pause so the audience can read it. When creating a self-running presentation, users can relax this rule quite a bit.

6: Don’t rely too heavily on bullet points

Most experts recommend that you not use bullet points (which run amuck with PowerPoint’s layout defaults). Here’s the general rule: Each bullet point should be a slide of its own. If this is too overwhelming at first, show users how to organize the information using bullet points and then move each bulleted item to a blank slide. From there, they can develop that single thought using graphics and (some) subtle animation. Naturally, the presentation will have a lot more slides, but the overall presentation will be more effective and memorable. Removing bullets doesn’t tie your users’ hands; it frees them up so they can move beyond mere bullet points.

Following the advice in #5, you’ll get rid of the introductory text in Figure D, leaving some ugly bulleted text to revamp. If you move each bullet to its own slide, you generate six slides, but they’ll be more memorable. The introductory slide shown in Figure E doesn’t need a bit of text — leave that for the presenter — but the message is clear and the audience will remember it.

Figure E

This slide makes its point without a single word of text.

To avoid the wrath that the above pronouncement is sure to unleash, I add this simple disclaimer: Bullet points aren’t bad or wrong. But use them sparingly because slide after slide of them is ineffective and boring.

7: Use Slide Master

Creating the presentation can be a laborious process, so efficiency matters. Show users how to configure the Slide Master first thing. It saves time up front and later. Users can quickly create a consistent look that’s easy to maintain. If they decide to change an attribute at some point, they need to change only the Slide Master, not every slide in the presentation.

Slide Master, shown in Figure F, is on the View tab in the Master Views group (Presentation Views in PowerPoint 2007). In PowerPoint 2003, choose Master from the View menu and then select Slide Master.

Figure F

Users will save time and effort with the Slide Master.

8: Use common fonts

PowerPoint doesn’t embed fonts by default. That presents a problem if the system that runs the presentation doesn’t have the presentation’s fonts installed. Windows will substitute a font, which might work; and then again, it might not. Users can avoid this potential problem by using common Windows fonts, such as Arial, Times New Roman, Tahoma, and so on. In fact, you might make a convention for using common fonts. Another workaround is to embed the fonts in the presentation. Doing so eliminates substitutions, but also increases the size of the presentation. This is one area where #7 can help: You can quickly avert disaster by updating the font via the Slide Master.

To embed fonts, click the File tab, choose Options (under Help), select Save in the left pane, and then check the Embed Fonts In The File option, shown in Figure G. In PowerPoint 2007, click the Office button and then click PowerPoint Options. In PowerPoint 2003, choose Options from the Tools menu.

Figure G

Embedding fonts prevents problems when the specified font isn’t available.

9: Use legible fonts

Users, especially those new to PowerPoint, will often go overboard with specialty fonts, attributes, and splashy colors, just because they’re available. Help users avoid this pitfall by applying these basic guidelines to improve legibility:

• Make sure the font size is large enough to see in a conference or meeting room (start at 26 points and don’t be afraid to go larger).
• Use a sans serif font.
• Keep attributes, such as bold, underline, italics, and shadows, to a minimum. While these attributes may be effective in print, they can appear out of focus on a slide.
• When using color, contrast is good.
• Use dark text on a light background or light text on a dark background, as shown in Figure H. Anything in between is a wash.

Figure H

The light versus dark concept at work.

10: Avoid clip art

Clip art has a tendency to be overdone. It’s just too complex for presentation purposes. Your users will ask, “If I limit the text and don’t use clip art, what’s left?” Let them create their own simple graphics using basic shapes and symbols. It takes practice and patience, and don’t expect them to reinvent themselves as graphic design artists. Just introduce PowerPoint’s graphic tools and see what happens.

Of course, you don’t have to ban clip art entirely. Show users how to make small adjustments to simplify and improve clip art. For example, Figure I shows a before-and-after view of the clip art flower used in Figure E. Removing the outline around the petals helped tone down the cartoon effect.

Figure I

Removing the outline from the petals renders the flower a bit (albeit not much) less cartoonish.

Custom graphics will better represent your core concepts because your users will be creating graphics that fulfill their purpose instead of forcing someone else’s generic designs to fit. If users simply can’t get the knack of graphics, consider hiring a consultant who specializes in presentation graphics. Sometimes, that’s what you need.

10 must-have Android apps (revisited)

There’s an old French saying (one I first heard in the Rush song “Circumstances”) that goes Plusça change, plus c’est la même chose. Roughly translated: “The more it changes, the more it’s the same.”

Changes to the Android platform have been tremendous over the past year. But our need for” best apps” lists remains the same. A year ago, I wrote 10 must-have Android apps. Now, many changes later, there is a need for a new version of that same article. More apps are available and some older apps have been vastly improved. It’s time they were given a list of their own. And this time around, I’ve also included tablet-friendly apps as well.

1: Google Docs (Free)

The Google Docs app is of exceptional value to anyone who regularly uses Google Docs. Although it is much more tablet- than smartphone-friendly, Google Docs makes it easy to manage and edit your Google documents. This app lets you collaborate with other Google Docs users; quickly make changes to spreadsheets; view your documents, PDFs, presentations, and more; upload and convert files to the Google Docs format; take a photo (with your device) of printed text and convert it to a Google document; and easily share docs with your device’s contacts.

2: Thumb Keyboard ($2.29)

If you use an Android tablet, you MUST buy and install Thumb Keyboard (Figure A). Your typing will not only improve, but you will be able to type longer and more comfortably. This is one of the first apps I install when testing or upon purchasing a new tablet. Once you’ve used this keyboard, you will never go back to the standard keyboard.

3: Intuit GoPayment (Free with approved account)

Intuit GoPayment allows you to accept payments from anywhere at any time. Money is deposited into a GoPayment Prepaid Visa Card account and can seamlessly integrate into QuickBooks. Once you download the app, you fill out the in-app application form. Once approved, you are ready to start taking payments. You will also receive a free credit card reader that attaches to your mobile device to make taking payments even easier. For those who are running their own business (and who use QuickBooks), this is a great app.

4: Kindle for Android (Free)

If you are a reader, you need Kindle for Android (Figure B). It’s one of the finest ereader apps available. Kindle for Android enables your tablet or smartphone to gain access to millions of books — and it allows you to read those books under any light, use a built-in dictionary, sync your ebooks with your Kindle or PC, and much more. This is an essential app for avid readers of nearly any genre.

Figure B

Kindle for Android

5: Google+ (Free)

Will Google+ overtake Twitter and Facebook as the number one social networking spot? Only time will tell. But for many businesses, Google+ seems to be a much better fit. So why not make use of this flexible social networking tool on your tablet or smartphone? With seamless integration into the Android platform, using Google+ on an Android device is even simpler and more elegant than the other social network apps.

6: LogMeIn Ignition ($29.99)

LogMeIn Ignition is the most expensive app on the list. But it’s also one of the most important apps on the list if you use LogMeIn to handle remote administration tasks. I use this tool daily, and on a number of occasions, I have deployed it for clients. Of all the remote administration tools you can have on a tablet or smartphone, LogMeIn Ignition is the best of the best.

7: Beautiful Widgets ($2.89)

If you’re looking for an awesome collection of widgets to add to your tablet or smartphone home screen, look no further than Beautiful Widgets (Figure C). Its biggest strength is the ability to personalize all the widget s. You can configure this collection of home screen tools to look and behave exactly how you like them. The features are too numerous to list. (Check out the apps page to see what all it includes.)

Figure C

Beautiful Widgets

8: ROM Manager ($5.99)

If you have a rooted device, you owe it to yourself to look into ROM Manager. With the help of a great UI, it lets you easily apply nightly builds. It also makes switching ROMs a snap and you can even back up your ROMs. This application will interest only a select number of users (power root users). But for them, this is without a doubt a must-have.

9: Poweramp (Free)

If your Android device also serves as your music playback device, you owe it to yourself to installPoweramp. For me, this app is worth it just for the 10-band graphic EQ. For others, the support for mp3, mp4/m4a (including alac), ogg, wma (wma pro files may require NEON support), flac, wav, ape, wv, tta, mpc, and aiff formats makes this a must-have music app. You will also find plenty of other features, such as outstanding OpenGL-based cover art animation and gapless playback, to woo you away from the included player.

10: AirDroid (Free)

AirDroid is a handy little app that lets you take control of your Android device from within a Web browser. With this app, you can transfer files back and forth, cut/copy/paste/manage files on the SD card, install/uninstall applications, preview/delete/import/export photos, share contents of the clipboard, manage contacts, and much more. The one hitch with this application is that the PC and the mobile device must be on the same wireless network. But once you have AirDroid working, you will find it an incredibly easy way to work between your PC and your mobile device.

Something for everyone

The list of top Android apps could easily go on and on. Couple this list with the original one, and you have a collection of Android apps that are sure to please nearly any cross-section of users. And since these apps are all “must haves,” you should skip the previews and just install and run them. Tout de suite!

10 tips for troubleshooting Excel formulas and functions

Takeaway: Finding and fixing errors in formulas and functions can be tedious and time-consuming. Here are some practical tips to help you cut to the chase.

Troubleshooting an Excel worksheet’s formulas and functions can be a big job. Errors just come with the package. Excel offers some valuable auditing tools, found on the Formulas tab in the Formula Auditing group, but you won’t always need that much power. Sometimes, just a bit of special knowledge can help you resolve an error. These 10 tips will help even the most experienced Excel users find common errors quickly.

1: Error values are a clue, not a nuisance

If Excel can’t calculate a formula, it displays an error value. These values are valuable clues and knowing what they mean can help you quickly spot errors. The following error values are your first step, and often the only step, to debugging an error:

• #DIV/0: Excel can’t divide by 0 and your formula refers to a cell that contains or evaluates to 0 or is blank. This is one of the easiest to find and fix.
• #NAME?: This error occurs when you refer to a range incorrectly or forget to enclose text in quotation marks. When Excel encounters text in a formula, it tries to interpret the text as a cell reference, a range name, or a function name. When it can’t recognize the text as any of those things, it displays this error value. Check your function names, cell references, and range names (make sure the range name actually exists if you’ve spelled it correctly). If all ranges are correct, make sure you’ve delimited text using quotation marks.
• #NULL!: You specified an intersection between two ranges that don’t intersect, such as A1:C3 and D4:F6. If the ranges do intersect, you’ve probably used a space character (the union operator) instead of a comma to separate the two ranges (A1:C3 D4:F6 instead of A1:C3,D4:F6).
• #NUM!: This error value indicates a problem with a number in the formula. Either the argument is invalid or the result is too large or small.
• #REF!: You’ve referenced a cell incorrectly. Most likely, you’ve deleted a cell. For instance, the expression =A1+B1 won’t return an error if you delete the value in B1. But if you delete column B, it will.
• #VALUE!: You’ve expressed the wrong data type or operator. The most common cause for this error value is a reference to text by a numeric operation.

2: Use error handling for #N/A

Excel’s #N/A error value isn’t like the rest — there’s usually nothing wrong with the formula itself. It means that Excel can’t return a result that makes sense. This error shows itself a lot with VLOOKUP() and HLOOKUP(). When one of these functions returns #N/A, it usually means it can’t find the value you’re looking for or that you haven’t sorted the list (which isn’t always necessary). Fixing this error requires error handling. Specifically, in this case, you’d wrap the original function in an error-handling routine that displays a meaningful message or more appropriate value. For instance, you might display the message, “value not found” — the user understands that — whereas the #N/A value might cause confusion.

3: Evaluate components

One of the quickest ways to track down a problem is to evaluate individual components in the Formula bar or even right in the cell. Specifically, you highlight a cell reference or simple equation, press [F9], and Excel evaluates the highlighted expression and returns the result in the Formula bar. Either way, you can quickly pinpoint specific problems with logic. (To edit in-cell, simply double-click the cell. If that doesn’t work, check the Advanced Editing Options.) Press [Esc] when you’re done.

Figure A shows a simple IF() function that returns the string “is down” if the yearly total for 2011 is less than 2010 — well, it’s supposed to. When evaluating the expression in the Formula bar, as shown in Figure B, you can quickly see that the true and false actions don’t match the conditional statement’s intent. This works, this time, because the expression follows the conditional logic: $44,075 is less than $45,962. But that won’t always be the case. You can also use the Formula bar to learn a constant’s value and to discover errors caused by invalid data. It’s not just for troubleshooting formulas.

Figure A

Shouldn’t this expression return “is down?”

Figure B

Evaluating a portion of the formula in the Formula bar helps you pinpoint logic errors.

The Evaluate Formula option on the Formulas tab (in the Formula Auditing group) works similarly. It’s a bit more methodical but certainly useful. In Excel 2003, you’ll find Evaluate Formula on the Tools menu under Auditing.

4: Check for multiple lines

Check the right end of the Formula bar if a formula looks too simple. Figure C shows a double arrow, which indicates that the formula comprises multiple lines. Click the down arrow to walk through the lines or click the Expand Formula Bar button (to the right) to display the full formula.

Figure C

The down arrow to the right indicates that there’s more to this formula.

To separate formula components, simply position the cursor where you want the formula to wrap to a new line and press [Alt]+Enter. Breaking an expression across lines can help you confine expression logic, where each line contains a step toward the resulting value. (Doing so often confuses users, though.)

5: Display formulas for easy checks

To quickly display all formulas, press [Ctrl]+~ (that’s the tilde sign to the left of 1). Figure D shows a sheet’s formulas. It’s a toggle, so pressing [Ctrl]+~ a second time will display results. Display formulas to check consistency and spot potential errors. Printing the formulas lets you continue your audit away from your computer. Most users already know how to do this, but you can use this tip to work more efficiently by using two windows, one with formulas, one with results, as shown inFigure E.

Figure D

Toggling between formulas and their results can help spot errors and inconsistencies.

Figure E

Display both formulas and their results to work more efficiently while troubleshooting.

You can also click Show Formulas in the Formula Auditing group on the Formulas tab to toggle between these two views. In Excel 2003, choose Options from the Tools menu. Then, click the View tab and check or uncheck the Formulas options in the Window Options section.

6: Select formula cells

Locating all the formulas in a complex sheet can be tedious work and you’re apt to miss some if you rely on your memory or eyesight. Instead, let Excel do the work for you as follows:

1. Press [F5] to display the Go To dialog and click the Special button at the bottom. In Excel 2003, choose GoTo from the Edit menu and then choose Special.
2. In the resulting window, check the Formulas option, as shown in Figure F.
3. Click OK. Figure G shows the result — in this case, a small range of formulas (B2:B4).

Figure F

Check the Formulas option.

Figure G

Use the Special option to select cells that contain formulas.

If you need a more permanent solution, read Identify which cells in Excel are formula cells.

7: It’s a date; no, it’s a number; no, it’s an error!

Sometimes, a formula returns a date or time value instead of the number you were expecting. This usually happens when a formula references a cell you’ve formatted as a date or time. In this case, there’s nothing wrong with the formula; simply reformat the referenced cell correctly and the error value should disappear.

8: Check the number of characters

Older versions of Excel limit formulas to 1,024 characters; Excel 2007 has increased the maximum character limit to 8,192. It’s doubtful that Ribbon users will ever hit that wall, but those still using the menu version might keep the 1,024-character limitation in mind. In addition, function nesting has increased from seven to 64 — it boggles my mind. For more limits on Excel 2003 and Excel 2007/2010, read the following articles:

• Excel specifications and limits (2003)
• Excel specifications and limits (2010)

9: When your function returns the function

If you enter a function, press [Enter], and Excel displays the function, Excel is interpreting the cell contents as text. There are two possible fixes:

• If there’s an apostrophe character at the beginning of the function, delete it.
• Make sure you haven’t formatted the cell as Text. If you did, change it to General or something more appropriate.

10: When your formula doesn’t recalculate

You know the formula or function works — it was fine until you changed one of the dependent values. When you did that, the function did not recalculate and show the updated result. The most likely reason is that you’ve turned off Excel’s automatic recalculation. It’s easy to turn this option off while working and forget to turn it back on. Simply reset the calculation method to automatic as follows:

1. Select the File tab and click Options. In Excel 2007, click the Office button and click Excel Options. In Excel 2003, choose Options from the Tools menu.
2. Select Formulas in the left pane. In Excel 2003, click the Calculation tab.
3. In the Calculation sections, check the Automatic option.
4. Click OK.

10 cool ways to use Excel's conditional formatting feature

Formatting, such as currency, alignment, and color, determines how Excel displays a value. But conditional formatting is more flexible, applying specified formatting only when certain conditions are met. Here are some creative ways you can push conditional formatting beyond its expected uses.

This article assumes a basic knowledge of Excel’s conditional formatting feature. In Excel 2007 and 2010, Conditional Formatting is in the Styles group on the Home tab. In Excel 2003, Conditional Formatting is on the Format menu.

Note: This list is also available as a photo gallery, and you can download a sample workbook (xls and xlsx format) that includes examples of the techniques described here.

1: Distinguish business rule violations

Using conditional formatting, you can visually discern when something is breaking a business rule. For example, Figure A shows a simple timekeeping sheet that highlights a workday that’s greater than eight hours. Why? Because your organization requires approval for anything over an eight-hour day.

Figure A

Alert users to overtime hours requiring approval.

Working with the time values complicates things a bit, as you can see in Figure B. This solution uses >.34 to represent time values greater than eight hours, which will work in most cases — you can’t use the value 8 or even the time value 8:00. Or you could use the predefined Greater Than rule in Excel 2007 and 2010, which will automatically use the more accurate value of 0.333333….

Figure B

This formula returns True when a time value is greater than eight hours.

2: Display simple icons

Using conditional formatting (in 2007 and 2010), you can display icons that are often easier to interpret than the values they represent. For instance, a simple checkmark might be quicker to discern than the text value yes, on, true, and so on. Figure C shows an icon solution for the same rule violation in #1.

Figure C

Use icons instead of traditional formats to represent conditional rules.

First, select the values in column E and apply one of the default icon sets. Then, use Manage Rules to manipulate the results. Figure D shows the final settings (click Reverse Icon Order first).

Figure D

These settings display green and red icons to alert users to an overtime violation.

3: Highlight a row based on a single value

Filters are great for limiting what you see, but sometimes you want to compare records. When this is the case, conditional formats can distinguish records. Figure E shows a data set of products with a conditional format highlighting only Condiment records.

Figure E

You can distinguish records based on a single value.

Select the entire data range (not the column headings) so Excel can format the entire record (row). Figure F shows the formula-based settings. The $G2 component creates a relative address, which updates with each row: G4, G5, G6, and so on. When the value in the referenced cell equals the string “Condiment,” Excel highlights the entire row.

Figure F

You can highlight a row by applying the conditional format to the entire data range instead of a single column.

4: Create a dynamic record highlight

Highlighting an entire record (#3) is convenient, but you might want the conditional format to be more… conditional. For instance, suppose you want users to choose the category on the fly, as shown in Figure G. First, use the Advanced Filter feature to copy a unique list to an out-of-the-way spot, as shown in Figure H. Then, use the Data Validation feature to create a list, also shown in Figure H.

Figure G

Let users choose the highlighted category from a validation list.

Figure H

You’ll need a unique list and a validation list.

With the list in place, update the conditional format formula to reference the input list cell, as shown in Figure I. Instead of referencing a cell within the row, the formula references the validation list in B2. Selecting an item from the validation list updates the conditional formatting.

Figure I

This rule references the validation list instead of a literal string value.

5: Compare values

It’s common to compare values. For instance, you might track inventory levels by comparing the stock on hand to a reorder level. Using conditional formatting, you can alert users when it’s time to reorder, as shown in Figure J. Select the values you want to format — in this case, that’s B2:B46. (You could highlight the entire row or one of the inventory values.) Then, apply the format shown in Figure K.

Figure J

Highlighted items that are running low.

Figure K

This rule highlights the product in column A when the in stock value goes below the reorder level.

6: Compare lists

You can find discrepancies between two lists using a conditional formatting rule, as shown inFigure L. This rule, shown in Figure M, compares each value in column A to its counterpart in column B. If they’re not the same, Excel highlights the value in column A. To highlight the values in column B instead, select the values in column B and update the rule formula to reference the values in column A.

Figure L

This rule highlights values in column A when they differ from the values in column B.

Figure M

Apply this rule to compare the two side-by-side lists.

7: Create alternating bands

Many sheets highlight every other row (banding) to improve readability. The Table feature offers several predefined formats that include bands, but you end up with a table instead of a plain data set, and that might not be what you want. When you don’t want a table, use conditional formatting to create alternating bands, as shown in Figure N. The rule shown in Figure O highlights cells to achieve the alternate band effect.

Figure N

Banding can improve readability, but you’ll probably want to highlight even or odd rows and not both, as shown here.

Figure O

Apply either of these rules to create an alternate band effect.

8: Find duplicates

To find duplicate values or records, you can use a filter, but conditional formatting can pinpoint duplicate values on the fly. For instance, the sheet in Figure P shows duplicate values in a single column. Select the values you want to format and apply the formula-based rule shown in Figure Q.

Figure P

Highlight duplicate values for a quick alert.

Figure Q

This rule highlights all occurrences of the same value.

To ignore the first occurrence and highlight only subsequent values, use this formula:

=COUNTIF($A$2:$A2,A2)>1

If you want to check for duplicate values across multiple columns, concatenate the values and apply a similar rule to the results, as shown in Figure R. You can also hide duplicates (which I don’t always recommend) by selecting a font color that matches the sheet’s background.

Figure R

The concatenating formula in column C finds duplicates across multiple columns.

You can use Excel 2007 and 2010’s built-in Find Duplicates rule, but it has a few limitations. First, you don’t have much flexibility with the actual formats. Second, this rule selects all duplicate values; you can’t choose to ignore the first occurrence and highlight just the subsequent values, as this more flexible formula-based rule can.

9: Find discrepancies

Verifying data is an important task, and Excel’s conditional formatting can help by alerting you to inconsistencies. Figure S shows a common accounting tool known as cross-footing — the process of double-checking totals by comparing subtotals across rows and columns — in cell F16. Adding the conditional format makes the discrepancy hard to miss when the two totaling values don’t match. Select either of the cross-foot formulas and apply the rule shown in Figure T.

Figure S

Combine conditional formatting with cross-footing for a hard-to-miss alert.

Figure T

This rule compares two values.

10: Find the smallest or largest duplicate

It’s easy to find the smallest or largest value using the predefined rule shown in Figure U, but you’ll need a more complex rule to highlight the smallest or largest duplicate value. As you can see in Figure V, the value 3 is the smallest value in the column, but Excel highlights each occurrence of the value 5.

Figure U

This option finds the smallest or largest value in a range.

Figure V

A more complex rule highlights the smallest or largest duplicate value in a range.

This rule is unstable if you use normal referencing, so apply a range name to your data set before applying the conditional formatting rule. The rule shown in Figure W will highlight the value 3 in the range named List only if 3 is also a duplicate. To find the largest duplicate value, substitute the MIN() function with MAX().

Figure W

This rule ignores the smallest value unless it’s also a duplicate value.