Notes from the Help Desk…

On some charts, you may want to plot a data series that contains different numbers on a different scale.  For example, you may want to show monthly sales figures and percentages on the same chart.  If you use the same scale for both data series, the percentages will be too small to show up on the chart.

To plot a data series on a secondary axis:

1. Create your chart.
2. Select the smaller data series by clicking the drop-down list on the Layout tab of the Chart Tools ribbon.
3. Click the Format Selection button.
4. Excel will display the Format Data Series dialog box
5. In the Series Options category, select Secondary Axis.
6. Click the Close button.
7. Excel will plot the data series on a secondary axis.

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.

You can use this tip to automatically create a list of items that you use frequently.

For example, let’s say that you need to list the days of the week as follows:

S = Sunday
M = Monday
T = Tuesday
W = Wednesday
Th = Thursday
F = Friday
Sa = Saturday

You probably know that you can List the full name of the days or three-letter abbreviations by typing any one day in a cell and using Extend (the thin black cross in the lower right corner of the cell) to fill in succeeding days.

However, there are probably other lists (such as the one above) that you have to type more than once that are not stored in Excel by default. (Examples might be lists of employees, products, parts, sales regions, etc.)

To use Extend to create your own custom list:

1. Type the list into a spreadsheet (down a column or across a row, doesn’t matter which).
2. Select the list.
3. Select Tools | Options.
4. Click on the Custom Lists tab.
5. Click the Import button in the lower right corner.



6. Excel will add the new list.
7. Click OK.
8. Test the list by typing the first (or any) item on the list and use Extend.

To edit the list:

1. Select Tools | Options.
2. Select the Custom Lists tab.
3. Select the list you want to edit from Custom Lists on the left.
4. Edit the list entries.
5. Click the Add button.
6. Click OK.
7. Test the edited list with Extend.

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!