In Excel 2007

1. Select the cells that the user needs to complete. (Remember, you can use Ctrl-Click to select non-adjacent cells.
2. On the Home Tab of the ribbon, click on the Conditional Formatting Tool and select New Rule from the pop up menu.

   

3. You see the New Formatting Rule dialog.

   

4. Select Use a formula to determine which cells to format.

   

5. In the Format values where this formula is true: area, enter the following formula. (Replace A1 with the value of your first active cell. This should be the number visible in your toolbar.)
   =ISBLANK(A1)

   

6. Click Format. You see the Format Cells dialog.

   

7. Click the Fill tab, and select the color to use for highlighting.

   

8. Click OK, then OK to accept the conditional formatting. All the empty cells should now be highlighted.

   In Excel 2003
   

9. Select the cells that the user needs to complete. (Remember, you can use Ctrl-Click to select non-adjacent cells.
10. Select Format|Conditional Formatting. You see the Conditional Formatting dialog.

    

11. Select Formula is from the Condition 1 drop-down.
12. In the formula area, enter the following formula. (Replace A1 with the value of your first active cell, which is visible in your toolbar)
    =ISBLANK(A1)

    

13. Click Format. You see the Format Cells dialog.
14. Select the Patterns tab and select the desired color to use for highlighting.

    

15. Click OK, and OK to accept the conditional rule. All the empty cells should now be highlighted.

Follow these steps to hide those zeros:

1. Right-click on one of the data labels, and select Format Data Labels From the pop up menu. You see the Format Data Labels dialog.
2. On the Number tab, select the Custom option. You will see the default format options (most likely #,##0;-#,##0). These represent the formatting to use for positive, negative, zero, and text, each separated by a semicolon. We want nothing to appear when the value is zero, so change this to #,##0;-#,##0;;
3. Click Add to add and apply the new Custom formatting.
4. Repeat for the other data labels, applying the new Custom formatting to each.

   

For those of you who haven’t attempted macros yet, I’ve included step by step instructions. For those of you comfortable with macros, just skip down to the code.

As always, I suggest you make a copy of your spreadsheet and follow these instructions on the copy just in case.

1. Open your spreadsheet and select Tools|Macro|Macros.
    

   

2. You see the Macro Dialog. Enter SetPrintArea in the Macro name field, then click Create.
    

   

3. You see the Visual Basic Editor. It should have some code already, with the name you entered.

   

4. Select all the code in the window, and paste the following code (just the green stuff):

   Sub SetPrintAreas()
   Dim sPrintArea As String
   Dim wks As Worksheet

   sPrintArea = InputBox(“Enter print area range”)
   For Each wks In ActiveWindow.SelectedSheets
   wks.PageSetup.PrintArea = sPrintArea
   Next
   Set wks = Nothing
   End Sub
   

   Your screen should look like this:

   

5. Save (click the disk icon) and close the Visual Basic Editor by clicking the red ‘X’. This should close the window and take you back to your workbook.
6. Now, select the sheets you want to modify.
7. Select Tools|Macro|Macros to display the Macro dialog box.
8. Select your macro and click Run.

   

9. You’ll see a dialog box like this:

   

10. Enter the range you want to use for the print area (for example A1:M23) and click OK.
11. The print range will now be applied to all the selected sheets.

HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

• Lookup_value The value you are looking for, usually the value the table is organized on. It might be an ID, date, or other value.
• Table_array Where you are searching.
• row_index_num The column that contains the value you want returned.
• range_lookup Determines if it only finds exact matches.

HLOOKUP with Range

HLOOKUP works like VLOOKUP when your data is organized horizontally. For our example, we’ll look at a very simple lookup table, a table showing the letter grades for scores:

And here is our gradebook, where we’ll enter scores.

Now, lets add the HLOOKUP function:

1. Place the cursor in cell C2, where we want to perform the first lookup.
2. Enter the formula =HLOOKUP(B2,GradeScale!$B$1:$N$2,2,TRUE).
   
   Here’s an explanation of all those values:

• B2 is the Lookup value; we want to look for the value that appears in cell B2. There are no $ on the B or the 2 because we want the values relative; we’re going to copy the formula into the remaining cells.
• GradeScale!$B$1:$N$2 is the cells containing our grading scale. These are absolute references because we don’t want them to change when we copy the formula down into the other cells. (We could also have given our grading scale a name, and then we wouldn’t have to include the absolute references.)
• 2 tells the formula to return the value in the second row.
• True tells it to return an approximate value. Lookup functions let you search either for an exact match, or the closest value. Sometimes, you want to only return exact matches (you only want the employee with a specific ID, or a product with a specific number.) Other times you will only want the closest value (for example, on a grading scale.)
  The difference between the two options is what happens if the value isn’t found. If you set range_lookup to false (telling it to only return exact matches) and the value isn’t there, it will return an error message. If you set range_lookup to true and the value isn’t there, it will return the largest value less than what you are looking for (that is, the value that
  appears before where your value would appear in the list.)
  In either case it’s best to always organize your lookup table so it is sorted on the index value, with the lowest value at the top.

1. Finally, copy the formula down into the other cells. Here is the result:
   

HLOOKUP with Exact Match

In the next example, we’re going to look at a case where you want to look for an exact match. In this case, we’re creating a student summary. We’ll enter a student ID, and look that ID number up in a student list. If the student ID doesn’t exist, we want to get an error, not the closest match.

Here is our student list:

And Here is our summary (without the lookup functions):

Now add the HLOOKUP function:

1. Put the cursor into cell B2 and enter the formula =HLOOKUP(A2,Student_Info!$B$1:$G$5,2,FALSE).  Copy the formula down into the other rows.

   

2. Now, repeat the process in C5, this time using the formula =HLOOKUP(A2,Student_Info!$B$1:$G$5,5,FALSE).
   

Using CONCATENATE with HLOOKUP

In our exact match example above, the student name column only returns the student’s first name, We want to see the student’s entire name. We can do that by using the CONCATENATE function. CONCATENATE takes the syntax CONCATENATE(text1,text2,text3…) We’re going to want to combine three text strings, the results of our current HLOOKUP, a space, and the results of the HLOOKUP returning the value in the next row.

=CONCATENATE(HLOOKUP(A2,Student_Info!$B$1:$G$5,2,FALSE)," ",HLOOKUP(A2,Student_Info!$B$1:$G$5,3,FALSE))

Here’s how that formula breaks down:

Once you modify the formula in cell B1, here’s what you get:

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

• Lookup_value The value you are looking for, usually the value the table is organized on. It might be an ID, date, or other value.
• Table_array Where you are searching.
• col_index_num The column that contains the value you want returned.
• range_lookup Determines if it only finds exact matches.

VLOOKUP with Range

VLOOKUP is a good choice if you have data organized vertically. For our example, we’ll look at a very simple lookup table, a grading scale. Here is our Lookup table:

And here is our gradebook, where we’ll enter scores.

Now, lets add the VLOOKUP function:

1. Place the cursor in cell C2, where we want to perform the first lookup.
2. Enter the formula =VLOOKUP(B2,GradeScale!$A$2:$B$14,2,TRUE).

   Here’s an explanation of all those values:

• B2 is the Lookup value; we want to look for the value that appears in cell B2. There are no $ on the B or the 2 because we want the values relative; we’re going to copy the formula into the remaining cells.
• GradeScale!$A$2:$B$14 is the cells containing our grading scale. These are absolute references because we don’t want them to change when we copy the formula down into the other cells. (We could also have given our grading scale a name, and then we wouldn’t have to include the absolute references.)
• 2 tells the formula to return the value in the second column.
• True tells it to return an approximate value. Lookup functions let you search either for an exact match, or the closest value. Sometimes, you want to only return exact matches (you only want the employee with a specific ID, or a product with a specific number.) Other times you will only want the closest value (for example, on a grading scale.)
  The difference between the two options is what happens if the value isn’t found. If you set range_lookup to false (telling it to only return exact matches) and the value isn’t there, it will return an error message. If you set range_lookup to true and the value isn’t there, it will return the largest value less than what you are looking for (that is, the value that
  appears before where your value would appear in the list.)
  In either case it’s best to always organize your lookup table so it is sorted on the index value, with the lowest value at the top.

1. Finally, copy the formula down into the other cells. Here is the result:
   

VLOOKUP with Exact Match

In the next example, we’re going to look at a case where you want to look for an exact match. In this case, we’re creating an invoice. We’ll enter a product number, and look that product number up in a product table. If the product number doesn’t exist, we want to get an error, not the closest match.

Here is our Product Table:

And Here is our invoice (without the lookup functions):

Now add the VLOOKUP function:

1. Put the cursor into cell B5 and enter the formula =VLOOKUP(A5,ProductTable!$A$2:$G$10,2,FALSE). Copy the formula down into the other rows.
   The False tells Excel to search for an exact match in the lookup table. Because there is no UJ06TM, it returns NA.

   

2. Now, put your cursor into D5 and enter the formula =VLOOKUP(A5,ProductTable!$A$2:$G$10,5,FALSE). This looks up the same value in the same table, but returns a different value; this time the price.
   

Using IF with VLOOKUP

In our exact match example above, the #N/A makes it clear that the item number isn’t valid. However, it goofs up all of the other calculations and doesn’t give you a total. We can use the IF statement to customize the error message for the Item and return a 0 for the item price.

1. Change the formula in cell B5 to:
   =IF(ISERROR(VLOOKUP(A5,ProductTable!$A$2:$G$10,2,FALSE)),"Incorrect Item #",VLOOKUP(A5,ProductTable!$A$2:$G$10,2,FALSE))
   Copy this value down into the other rows.
2. Change the formula in cell B5 to:
   =IF(ISERROR(VLOOKUP(A5,ProductTable!$A$2:$G$10,2,FALSE)),0,VLOOKUP(A5,ProductTable!$A$2:$G$10,2,FALSE))
   Copy this value down into the other rows.
3. Here’s what you end up with:
   

1. Press Ctrl-H (or select Home|Editing|Replace in 2007 or Edit|Replace in 2003.)
2. Enter the text to search for in the Find what box (SuperProduct in our example.)
3. Enter the replacement text in Replace with box (OkieDokieProduct in our example.)
4. Click Replace All.
   

Find and Replace will fix all of the references with little pain on your part. However, the Find and replace tool does a lot more than just that. Here’s a quick look at some of the power uses of Find and Replace.

Find Text

To find text, you just type what you want to find into the Find what box. Click on More to see a variety of options to give your search more power.

• Match Case Check this box to match the case as you’ve entered it. Enter UM and it won’t find um or Um.
• Find whole words only Check this box to match only whole words. Enter doll and it will find doll but not dollhouse or ragdoll.
• Use wildcards Check this box to use wildcards to search. Enter nurs* and it will find nurse, nursing, and nurses.If you don’t check the box and search for nurs*, it will only match nurs*. (See the section below for more details on wildcards.)
• Sounds like (English) Finds homonyms (words that sound the same but are spelled differently.) Enter hear and it will find here and hear.
• Find all word forms (English) Will find all word forms, such as plurals and other tenses. Enter run and it will find run, runs, and running.
• Match prefix matches all words beginning with the entry. Enter pre and it will find prepare, preposterous, but not apprehend.
• Match suffix matches all words ending with the entry. Enter ing and it will find running, swimming, but not ringtone.
• Ignore punctuation characters will find all words spelled the same, ignoring punctuation. Enter its and it will find its and it’s.
• Ignore white-space characters will find all words spelled the same, ignoring any white spaces. Enter doghouse and it will find doghouse and dog house.

A big part of the problem is due to the fact that Excel actually provides users with three different lookup functions, and each one works differently. If you get them confused, you can end up with unexpected results. Or worse, a bunch of errors.

What Do Lookup Functions Do?

In general, all lookup functions do the same thing. They take a value you supply, look it up in a table and return another value based on where they find your value in the table.For example, you might provide the formula with an employee’s name. The function looks the value up in a staff table, and returns the employee’s phone number:

You give the lookup function the value Jake Owens. The function starts at the top of the table, searches through the first column until it finds Jake Owens, and returns the value in the next column, 555-2341.

Which Function Do You Want?

The first thing to ask yourself when deciding which function to use is how your data is organized.

Vertical data

If your data is vertically organized, it will usually be taller than it is wide. The data you look for will be in one column, and the value you want returned will be in another column.

VLOOKUP and LOOKUP can be used for vertically organized data.

Horizontal data

If your data is horizontally organized, it will usually be wider than it is tall. The data you look for will be in one row, and the value you want returned will be in another row.

HLOOKUP and LOOKUP can be used for horizontally organized data.

Other Structures

Finally, if your data is not organized in a table, say two columns on different pages, you can only use the LOOKUP function. (In fact, the LOOKUP function will let you search a row of values and return a value from a column if you need to.)

Only Exact Matches?

The next thing to consider is what you want to search for, exact matches or closest values.

If your are searching a staff table, you only want to find exact matches. If the staff member is not in the table you don’t want it to return someone else’s information.

On the other hand, if you are searching a grade scale, you don’t want to find an exact match, you just want to know which range the value falls into.

LOOKUP does not allow you to search for exact matches, it will return the closest match.

If you want to search for exact matches, you must use either HLOOKUP or VLOOKUP .

Summary

So here’s a quick comparison of the three different lookup functions you can use in Excel.

While your cell is still in edit mode, and while your cursor is in the cell reference you want to change, press F4. It will cycle through all four combinations, so just keep pressing F4 until things look the way you want. No need to grab your mouse and click, no need to type.

Here is the order Excel follows when you press F4:

• A1
• $A$1
• A$1
• $A1

Create Your Spreadsheet

Create your spreadsheet, and determine which cell will contain your pull down list. For this example, we’ll create a simple task list. Each task has three possible statuses, Not Started, In Progress, or Completed.

Create your list

Next, you will define the options to appear on the drop down list by entering the values you want, and giving the range a Name. You can create the list on the same worksheet in a hidden area, or on another worksheet. For this exercise, We’ll use the second worksheet.

Click on Sheet2, and enter your options.

Select your options, and select Insert|Name|Define. You see the Define Name dialog box.

Name the range (for example, TaskOptions) and click OK. You can now easily reference this list from your spreadsheet.

Create the Dropdown

Return to Sheet1. Select the cells that will contain your drop down list.

Select Data|Validation. You see the Data Validation dialog box.

From the Allow list, select List.

In the Source field, enter = followed by the name you gave your range of cells. (For example, =TaskOptions.)

Click OK.

Now, when users click on a sell in that column, users will be able to select from the options you have defined.