Notes from the Help Desk…

If you are working with a formula that may refer to a blank cell or otherwise generate an error message, you can use a combination of the IF-THEN-ELSE and the ISERROR function to handle the #DIV/0! or other error messages that may appear.

The IF-THEN-ELSE has 3 parts: the condition, the TRUE condition, the FALSE condition. The following formula tests A1 for the number 100. If it is found, the cell displays the contents of A1. If it is not found, it multiplies another cell (B1) by 2:

=IF(A1=100,A1,B1*2)

By using one of the ISERROR functions, you can test for an error condition and use the IF statement to display a message or nothing at all. The common IS functions are ISERR, which returns TRUE for any error value except #N/A and ISERROR, which returns TRUE for any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!).

Suppose a cell is going to contain the average of several cells. If the cells are empty, the result will be division by zero and the #DIV/0! error will appear. The following formula will take care of this condition:

=IF(ISERR(AVERAGE(A1:A10)),”No Data”,AVERAGE(A1:A10))

If the formula AVERAGE(A1:A10) returns an error (most likely because there are no numbers in the range), the cell will display the words “No Data”. If there is no error, the cell will display the results of AVERAGE(A1:A10).

Most of you probably know about the Extend box in Excel, even if you don’t know its name. The Extend box is the little square at the bottom right corner of the current cell or range. When you put your cursor on it, the cursor changes to a small black cross and the fun begins.

Extending a Cell’s Contents

The simplest way to use the Extend box is to drag it, either down or across, over a range of cells. When you let go, the range will fill with the contents of the original cell or range, based on these rules:

• Text will be copied
• A single number will be repeated.
• A range of numbers will create a series based on the differences between the numbers
• A formula will be copied and all cell references will change appropriately.
• A day, month, or quarter name will be extended into a list: Monday will be followed by Tuesday, Wednesday, etc.
• A custom list will be expanded. (You can create custom lists by highlighting the range, then selecting Tools | Options | Custom Lists and adding the range.)

Smart-Tag Options

With the advent of Smart Tags in Excel 2002, when you finish extending, a Smart Tag will appear. Clicking the Smart Tag will display a list of choices that will vary based on the type of data you are extending:

• Text or formula will let you extend with or without formatting, or just the formatting.
• Numbers will let you create a series (1, 2, 3, etc.) and extend with or without formatting.
• Dates will let you fill days, weekdays, months, years, with or without formatting.

Right-Dragging the Extend Box

If you extend by right-dragging, you’ll get a Quick Menu with the Smart Tag options as well as a link to the Fill Series dialog box, where you can create quite complex series, if you need them.

Double-Clicking Magic

Often, when you are working with a list, you often need to add a formula, as shown in the top picture above. If you double-click the formula (in C2 and D2 above), Excel will extend this formula to the length of the list, as shown in the picture on the bottom. (In our example, we used a range of two cells. We could have done C2 and D2 individually.) This is especially helpful if you list is a long one. Be aware that it will stop at the first blank cell in the column to the left.

This is a situation that often comes up in our Excel classes:

“I need to add data to my spreadsheet, but I want to lock the cells that contain formulas so they don’t get overwritten.”

It’s easy to Protect an entire spreadsheet from the Tools | Protection | Protect Sheet… menu item. However, this option protects all the cells in the spreadsheet.

Unless…you first unlock the cells you want to remain editable. Here’s how:

1. Select the cells you want to be editable.
2. Select Format | Cells, and click the Protection tab:



3. Clear the check from the Locked checkbox.
4. Click OK.
5. Select Tools | Protection | Protect Sheet…



6. Leave all the default items checked. (If you wish, you can add checks to any features you want users to access.) Add a password if you want to prevent a user from Unprotecting the sheet from the Tools menu.
7. Click OK.
8. Users will now be able to edit only the cells that are unlocked. If a user tries to edit a locked cell on a protected worksheet, he/she will see the following message:

By default, Word and Excel list the last four files you opened at the bottom of the File menu. You can increase the number of files on this list to nine by changing a program setting.

Here’s how:

1. Select Tools | Options.
2. Click the General tab.
3. Change the number in the Recently used file list to 9 entries.
4. Click OK.

When you are editing a large spreadsheet, you may want to lock the row or column headings into place so that they don’t scroll when you scroll the other cells in the worksheet. Excel calls this freezing panes. You can freeze any number of rows at the top of the screen or any number of columns on the left side of the screen, or both.

To keep row and/or column headings visible as you scroll:

1. To freeze one or more rows, select the row below the row you want to freeze.

To freeze one or more columns, select the column to the right of the column you want to freeze.

To freeze both rows and columns, click the cell below and to the right of the cells you want to freeze.

2. Select Window | Freeze Panes.

To remove “Freeze Panes” so that the worksheet scrolls normally:

1. Select Window | Unfreeze Panes.

When panes are frozen, you typically move in the bottom or lower right pane. However, you can click into any frozen pane and scroll around it.

2 + 3 * 4 =

Which is the correct answer?

a.) 24
b.) 20
c.) 14

If you were paying attention in junior high school math class, you know about the Order of Operations, which dictates that formulas evaluate multiplication and division before addition and subtraction.

Therefore, Excel reads this formula as 2 + 12, not 5 * 4, and the correct answer is 14. (24 is not an option, but someone in class always guesses it, so I thought I’d throw it in).

If you need a handy mnemonic for remembering the Order of Operations, or the order in which formulas are evaluated, remember

PEMDAS, or

P lease Excuse My Dear Aunt Sally

(P = Parentheses, E = Exponent, M = Multiplication, D = Division, A = Addition, S = Subtraction).

This problem seems to come up a lot in our classes: You have a list of names in a column. Each cell contains both the first and the last name.

You need to have the first name appear in one column and the last name in the next column. (Or you have the city, state and zip code in one column and you want to split them up into three columns.) And, of course, you don’t want to re-type the whole list!

Excel has a feature to deal with this problem, but most people don’t know about it. Here’s how to split up the text into different columns:
(Let’s assume the list of names is in column A and there is data in several columns to the right of column A.)

1. First, insert two columns to the right of column A. (You really only need one, but like me, you should be paranoid about losing important data. The extra column is a precaution.)
2. Select Column A.
3. Select Data | Text to Columns.
4. The Convert Text to Columns Wizard Step 1 will appear:



5. Assuming the first and last names are separated by a space, select Delimited and click Next.
6. The Convert Text to Columns Wizard Step 2 will appear:



7. In the Delimiters section, check Space, remove other checks, and click Next.
8. The Convert Text to Columns Wizard Step 3 will appear:



9. In the Column Data Format section, select General or Text, and click Finish.
10. Your text should now appear in two separate columns:



11. You can now delete the extra column you created as a precaution.

Here’s an Excel tip you can use to impress your friends (or boss)!

To quickly create an Excel chart:

Select the data to chart and press F11 (on the top row of the keyboard).

Voila! A chart!

Moving to different cells using the mouse is all well and good, but what happens when you have a list with 5,000 odd rows? Moving to the end of the list or, worse, highlighting to the end of the list is cumbersome with the mouse. (See the end of this tip for an exception.)

The solution is to use the END key. The END key is used differently in Excel from other programs. The behavior of the End key is taken from Lotus 1-2-3 so if you know that program, you can expect Excel to behave the same way.

To use the END key to jump:
1. Tap the END key.
2. Tap any arrow key. (Make sure NUM LOCK is not on if you use the keypad.)
3. The current cell will jump in that direction. Excel will stop when:
• It reaches a blank cell.
• It reaches the edge of the spreadsheet.
• It starts in a blank cell and it reaches a cell containing text.

To get to the end of a range containing blank cells, you may have to repeat the END key operation until you reach the last cell you want.

Keep in mind that using the END key is a sequential operation: you tap the END, then tap the arrow. You do not hold the END down and tap the arrow key simultaneously.

To use the END key to highlight:
1. Hold down the Shift key.
2. Tap the END key.
3. Tap any arrow key, keeping the SHIFT key held down.
4. The current cell will jump in that direction, highlighting as it moves.

Now that you’ve learned all of that, if you have Excel 2003, you can use the mouse to jump like the END key:

1. Put your mouse on the edge of the cell in the direction you want to jump. (If you want to jump down, place your cursor on the bottom edge of the cell.)
2. A four-way arrow will appear.
3. Double-click. The current cell will jump the same as it does with the END key.

If you hold down the SHIFT key when you double-click, Excel will highlight as it jumps.