Archive for the 'MS Excel' Category

Hiding the #DIV/0! Message

January 29th, 2007

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. […]

Supercharged Copying and Pasting - Using the Extend Box in Excel

February 9th, 2006

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 […]

Locking Part of an Excel Spreadsheet

November 11th, 2005

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 […]

Lengthen Your Recently Used Files List in Word and Excel

November 10th, 2005

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:

Select Tools | Options.
Click the General tab.
Change the number in the Recently used file list to 9 entries.
Click […]

“Freezing” Rows and Columns in Excel

October 21st, 2005

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 […]

Excel Quiz Question

October 21st, 2005

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 […]

Splitting Text into Different Columns

October 15th, 2005

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 […]

Create Custom Lists in Excel

September 3rd, 2005

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 […]

Create an Excel Chart with One Keystroke

September 3rd, 2005

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 and Selecting Cells in Excel

August 29th, 2005

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 […]