Select any topic link, listed below, for a variety of Excel Tips & Tricks:

Note: If the phrase "Tips & Tricks" is included in a topic link, there's numerous tips & tricks on that particular topic .

Which Key to use!

We often get asked how to use the Enter key to move them in a direction other than "down one cell" when pressed. But to get to other cells, you don't need to fiddle with the End key; you just need to know these keys:

• To move one cell in ANY direction after completing an entry, press the appropriate arrow key.
• To move UP one cell after completing an entry, press Shift + Enter.

back to top

Make the Icons Large

If you've got a big monitor--17-inch or larger--you may be using a sharper screen resolution (800 x 600, 1024 x 768, and so on) that lets you see more of your Excel worksheet on-screen.

That's great--except the higher your screen resolution, the smaller those little toolbar buttons get. Tired of squinting at them? Make them bigger as follows:

1. Choose Tools + Customize.
2. In the Customize dialog box, click the Options tab.
3. Select Large Icons.
4. Click Close.

Your buttons get bigger--much bigger. But note: They also get bigger in Word, Publisher, and any other Microsoft Office application you've installed.

back to top

DAY, MONTH, YEAR
Using the European date format in Excel - Version 4.x, 95

Here's a date format question:

"I sometimes need to set columns of dates to the European format (dd/mm/yy) in Excel. However, this doesn't appear to be a valid option in Excel 95. All I see are 4-Mar, 4-Mar-95, and 04-Mar-95. What I need is 04/03/95 for 4 March, 1995. Is there no way to get this format?"

As you've pointed out, Excel doesn't offer dd/mm/yy as a standard format. However, you can select it as a custom format.

To do this:

1. Enter 04/01/99 into cell A1.
2. Now click cell A1 and choose Format, Cells.
3. When the Format Cells dialog box opens, click the Number tab.
4. Now, select Custom from the Category list.
5. Under Type, select any date format.
6. Next, select the date format in the Type entry box and delete the current entry.
7. Type in dd/mm/yy and click OK to close the dialog box and record your selection.
8. Your date will now read 01/04/99

Note: There is a problem with using this format--you must enter dates in the standard American format first for this to work. For example, if you want to enter 2 June, 1999 into cell A1, you'd type 6/2/99 and Excel would display 02/06/99.

The best use for this particular formatting method is when you need to convert a column of dates that you've already entered using the standard mm/dd/yy format.

1. Just select the entire column and choose Format, Cells.
2. Select Custom and proceed as we described above.
3. After you enter dd/mm/yy, click OK and all the dates in the selected column will appear in dd/mm/yy format.

If you must make a change to any date in this column, use the standard mm/dd/yy format to enter the date. Excel will automatically convert it to dd/mm/yy.

back to top

Create Maps to view your data

We're sure you know by now that Excel can create a map based on regional (state, national, international) data. But how many of you know that you can use the map feature to simply draw a map anywhere on your worksheet?

So next time you'd like to illustrate a worksheet with a pretty picture of a map, try this trick:

1. Choose "Map" icon from the "Standard" Toolbar.
2. Choose "Map" from "Insert" menu.
3. Draw a box where you'd like your map to appear.
4. In the Unable to Create Map dialog box (don't believe what you read--it is able), choose the map you want to draw.
5. Choose "OK".

Excel creates the map.

Maps of the major highways in America or the capitals of Europe!

1. Choose "Map" from "Insert" menu.
2. Draw a box where you want your map to appear.
3. Choose type of map you want to draw.
4. Choose "OK".

How would you like to create a map of all the major highways in America? Or all the capitals of Europe? 

Follow the steps below:

1. Choose "Map" icon from the "Standard" toolbar.
2. Draw a box where you'd like your map to appear.
3. In the Unable to Create Map dialog box, select the area you'd like to map.
4. Choose "OK".
5. Right-Click on Map once it has been created.
6. Choose "Features" from the shortcut menu.
7. Select the Map feature you'd like. 
8. If you want to change the shape or color of the marker to be used, choose  "Custom" and then click the object button to make the changes.
9. When finished making your changes, Choose "OK".

back to top

Don't count the blank cells

Using Excel's Average function with blank cells
Version 4.x, 95

Question:
"I need to use the Average function in several columns that can sometimes contain blank cells. Is there a way to tell Excel to ignore blanks so I don't have them included in my averages?"

Excel ignores blank cells by default. As long as a cell is blank (or contains text), Excel won't include it in the calculations.  If you'd like to check this out, enter into cells A2 through A6 the following text.

1
2
3
4
5

Now, move to cell A10 and enter:

=average(a1:a8)

The average is 3. So, the blank cells A1, A7, A8, and A9 don't figure into the calculation at all.

back to top

Make a perfect Circle or Square

You know that you can create a perfect circle by clicking the Oval button on the Drawing toolbar, holding down the Ctrl key, and dragging anywhere on your worksheet. But if even that simple procedure is too much for you, you can eliminate the drag as follows:

1. Choose the "Oval" icon from the "Drawing" toolbar.
2. Hold down the CTRL key.
3. Click wherever you want the center of your circle to appear.

Excel creates the circle--no dragging required! Of course, if the circle isn't the exact size you need, you have to do some dragging, but there's no sense doing it until it's absolutely necessary, is there?

Note that you can use the same technique with the Rectangle button--or with many of the AutoShapes--to create equilateral or perfectly symmetrical shapes.

back to top

COUNTIF function does not use allow you to use multiple cells or ranges.

A user asks, "Can Excel's COUNTIF function work with a list of discreet cells as opposed to a range of cells? For example, can it tell me how many of, say, every third cell is not equal to 0?"

In a word, no. The COUNTIF function uses the following syntax:

=COUNTIF(Range,"Value")

where Range is a range of contiguous cells and "Value" expresses the condition

(as in >40).

You can't use multiple cells or ranges for the Range, as you can with, say, the SUM or AVERAGE functions.

The reason for this? COUNTIF is a database function. In most cases, database functions are designed to work on database fields, which are contiguous ranges (columns) of data.

Sorry.

back to top

Displaying Excel formulas

This Excel question comes from a user:

"I once saw an article on an easy way to switch an Excel worksheet between data display and formula display. Could you cover this topic?"

To switch the Excel display, all you have to do is press Ctrl-Tilde (~). This key combination acts as a toggle. The first time you press the keys, Excel will turn on formula display. When you press the keys again, Excel turns data display back on.

back to top

Prevent Duplicate entries within a range of cells....

Suppose you want to prevent duplicate entries in the range of cells A1:A10:

1. Select cell A1.
2. Choose "Validation" from "Data" menu.
3. Choose "Settings" tab.
4. Choose "Custom" from "Allow" drop-down list.  
5. In the Formula box, enter the following 

=NOT(OR(COUNTIF($A$1:$A$10,A1)>1))

6. Choose the "Error Alert" tab in order to set an alert style.
7. Set Style to Stop (the alert message with a Stop sign on it).
8. In the Title box, enter what you want to appear in the Error Alert's title bar (for example, "Duplicated Data"); in the Error Message box, enter the message box text ("Please type an entry not entered previously," for example).
9. Choose  "OK".
10. Back in the worksheet, select cell A1; then, using the square AutoFill handle, drag to fill cells A2:A10.

back to top

DRAWING TIP

Want to align an object--a shape, chart, picture, or anything else--to the gridlines on your worksheet? Well, as we've told you in the past, you could turn on the Snap to Grid feature (click the Draw button on the Drawing toolbar and then choose Snap + To Grid), but why go through all that when you can just do the following:

• Hold down the Alt key while you draw or size any object.

Excel snaps the object to the worksheet grid.

Calculate how much longer!

Calculate how many days to an future event in Excel.

1. In any cell (but for the purposes of this example, cell A1), type the big day in mm/dd/yy format (05/10/99, for example) and press Enter.
2. In another cell (for our purposes, cell A2), type =TODAY() and press Enter.
3. In a third cell (A3), type a formula subtracting today's date from the big day (in our example, =A1-A2) and press Enter.
4. Right-click the formula cell and choose Format Cells.
5. In the Format Cells dialog box, click the Numbers tab.
6. Under category, select Number, set Decimal Places to 0, and click OK.

Excel tells you how many days you have to wait.

back to top

Writing a function to calculate letter grades in Excel worksheets

This Excel formula calculates the letter that the entered numerical scores represent:

=if(a1>95,"a+",if(a1>89,"a",if(a1>79,"b",if(a1>69,"c",if(a1>59,"d",if(a1>50,"d-","f"))))))

Another teacher sent this Excel function to perform the same task. Although the formula submitted by Teacher #1 works quite well, you may prefer to use the function submitted by Teacher #2.

To enter the function into the workbook you use for grades (let's say the file's name is Grades.xls),

1. Press Alt-F11.
2. When the Basic editor opens, click ThisWorkBook and then choose Insert, Module.
3. In the module, enter the code shown here:

Function Grade(Score)
Select Case Score
Case Is
Grade = "Error
Case Is
Grade = "F"
Case Is
Grade = "D-"
Case Is
Grade = "D"
Case Is
Grade = "C"
Case Is
Grade = "B"
Case Is
Grade = "A"
Case Is
Grade = "A+"
Case Else
Grade = "Error"
End Select
End Function

4. Now press Ctrl-S to save the Grades.xls workbook, and your new function along with it. Let's see how the function works now.
5. Go to cells A1 through A5 and enter

56
68
75
89
96

6. Move to cell B1 and enter

=Grade (A1)

7. Select cell B1 and grab the handle at the lower right side with the mouse.
8. Drag down until you reach B5. This copies the function into cells B2 through B5.

At this point, Excel should display

56 F
68 D
75 C
89 A
96 A+

From this point on, you can calculate those letter grades by simply entering your new Grade function.

back to top

Using a background picture in Excel 95 - Version 95

"I recently saw an Excel worksheet that used a scanned photo as the background. I found this a very effective method for presenting the worksheet data. I don't know which version of Excel was being used, but I was wondering if I could add a background in an Excel 95 worksheet."

Yes, you can add a background picture to an Excel 95 worksheet. Let's say you have a logo that you'd like to use as a background.

1. Choose Format, Sheet, Background.
2. When the Sheet Background dialog box opens, locate your picture file and select it. Click OK to insert the background and close the dialog box. Unless the picture is a BMP file, you'll get a dialog box that offers to convert the file.
3. Click OK to continue. Excel will tile the logo to cover the entire worksheet.

There are a few possible problems that you need to know about. For example, if you decide to use a scanned photograph, you'll have to choose a rather light picture. If the picture is too dark, you'll have trouble seeing your data entries.

4. To remove the background, choose Format, Sheet, Background.
5. When the Sheet Background dialog box opens, click None.

back to top

"How do you calculate the difference between two times in Microsoft Excel?"

"It depends." Suppose you have a start time in cell A1 and a finish time in cell A2. If the times occur within the same day--that is, they don't span midnight--you could calculate the difference in cell A3 as follows:

Make sure the times in the cells--A1 and A2 in our example--are entered in AM/PM format (as in 2:53 PM) or 24-hour format (as in 14:53). Excel needs to know this type of thing.

1. Select the cell you want the difference to appear in (cell A3, in our example).
2. Enter your formula (A2-A1 in our example) and press Enter.
3. Press Ctrl + 1 to display the Format Cells dialog box.
4. From the Category list, select Custom; from the Type list, select hh:mm.
5. Click OK.

Simple enough. But what happens, you ask, when you want to calculate the time between two times that DO span midnight?  For example, suppose in cell A1 you have a time from one day, such as 11:45 PM, and in cell A2 you have a time from the next day, such as 7:30 AM. How do you calculate the difference in cell A3?

Like this:

1. Select the cell you want the difference to appear in (in our example, cell A3).
2. Enter the following formula, substituting your cell addresses for ours:

A2-A1+IF(A1>A2,1)

3. This formula adds the number 1, which, in Excel's time coding, amounts to 24 hours, or an entire day.
4. Press Enter.
5. Press Ctrl + 1 (to display the Format Cells dialog box).
6. From the Category list, select Custom; from the Type list, select h:mm.
7. Click OK.

back to top

BORED OF BORDERS?

Here are two little known, hardly talked-about keystroke shortcuts that may make your work with cell borders a tad easier at times, and definitely more interesting:

1. To quickly add an outline border to a cell or a range of cells, press Ctrl + Shift + &. Excel will add a hairline-weight outline border to the cells.
2. To REMOVE all borders from a cell or range of cells, press Ctrl + Shift + _ (the underscore key).

Yeah, we bet you can find use for those shortcuts.

WEB PUBLISHING? ON NO CONDITION

A budding Web publisher writes:

"Excel's conditional formatting works great, except when I save a worksheet in HTML format; then the conditional formatting is lost. Is there a way to preserve the conditional formatting, or is this a bug in Excel?"

It's a bug -- and it's known to Microsoft. However, they've stated no plans for dealing with it. So at least for now, if you want formatting to appear in HTML, you'll have to apply it directly rather than conditionally.

back to top

A USER'S TIPS FOR SPECIAL AUTOFILLS

Every so often we hear from users who make our jobs easier --

A user submitted not one, but TWO excellent ways to use Excel's AutoFill to generate special lists. The first tip:

To fill a range of cells with EVEN numbers,

1. In one cell, type the number 2.
2. In the cell below or to the right, type the number 4.
3. Select both cells.
4. Using the AutoFill handle -- that little black square in the bottom right corner of the range -- drag down or to the right, covering the remaining cells you'd like to fill.

Excel will fill the range with even numbers. Think that was cool? Wait till you see what this user has for us next time.

back to top

ANOTHER AUTOFILL TIP!

Yesterday, an Excel user showed us how to use AutoFill to fill a range with even numbers only. Today we use it to tackle a different problem. Suppose you want to create a list of the next 20 Sundays?

1. Type the date of a Sunday you know -- such as 4/18/1999 -- in any cell.
2. In the cell below or to the right, type the date of the NEXT Sunday -- in our example, 4/25/1999.
3. Select both cells.
4. Using the AutoFill handle, drag down or to the right, until you've generated all the Sundays you need.

back to top

WE CAN'T HELP BUT MENTION...

Last time we showed you how to use AutoFill to generate the dates of all Sundays in the foreseeable future. Today we'll show you another way Excel can help you with dates -- especially if you don't have a calendar handy.

Suppose you want to know the day of the week of a particular day in the future -- such as March 23, 2017?

1. Type the future date -- i.e., 3/23/2017 -- in any Excel cell.
2. On the Standard toolbar, click the Paste Function button.
3. In the Paste Function dialog box, under Function Category select Date & Time; under Function Name, select Weekday. Then click OK.
4. With the cursor in the Serial Number text box, click the cell containing your date.
5. Tab down to the Return Type text box, check the number next to Formula Result. Given that 1 = Sunday, 2 = Monday, etc., determine the day of the week.
6. Click Cancel (so as not to enter the formula into your worksheet).

Sure, it's not that smooth, but who has a calendar that extends 18 years into the future?

back to top

USING PI
Working with PI in Excel worksheets - Version 95, 4.x

Here's an Excel question from a user:

"I need to use the value of PI in an Excel worksheet. Could you discuss how to write a function that would calculate the value of PI?"

You don't need to write a function to use PI (Pi, as in 3.14) in an Excel worksheet--Excel has a built-in PI function.

• To see the value of PI, move to a blank cell and enter =PI()
• When you press Enter, Excel will display 3.141592654

You can easily use the PI function in your worksheets.

• Let's say you need to calculate PI times 2.5.

• You'd simply type =2.5 * PI() and press Enter.

back to top

MAKING EXCEL LIKE PIER 1

A user asks: "How do I import or convert a Lotus 1-2-3 97 file into Excel 97?"

Well, we're afraid that before you can import the file into Excel, you'll first need to save it as a 1-2-3 Release 4 (.wk4) file in 1-2-3 -- because that's the latest 1-2-3 version Excel 97 appears to support. See your 1-2-3 help for instructions -- and to find out which 1-2-3 97 features may be lost in the conversion. Once you've done that, you can import the file into Excel pretty easily:

1. From the Excel menu, choose File + Open.
2. Set Files of Type to Lotus 1-2-3 Files.
3. Find and select your 1-2-3 file.
4. Click Open.

back to top

A KEYSTROKE TO REMEMBER

Okay: We all know that pressing the Del key deletes the CONTENTS of the selected cell(s). But what do you press if you want to delete the cell itself (cells themselves)?

Ctrl + - (the hyphen, or "minus sign").

How you use this shortcut depends on what you're doing. For example, to delete a cell:

1. Select the cell.
2. Press Ctrl + -.
3. In the Delete Cells dialog box, specify how you want the other cells to move in response to the deletion; then click OK.

To delete a row or a column:

1. Select the row or column (by clicking the row or column heading).
2. Press Ctrl + -. (The row or column disappears. You don't have to mess with a dialog box.)

back to top

"Is there is a way to lock titles in Excel?"

 To "freeze" column and row titles in an Excel worksheet:

1. Select the topmost, leftmost cell you DON'T want to freeze. (For example, if your column titles are in row 1, and your row titles in column A, select cell B2).
2. Choose Window + Freeze Panes.

That's it--your titles remain in place as you scroll through the sheet.

BLAST-FROM-THE-PAST FORMATTING

A spreadsheet nostalgia buff asks, "Is there a way to format the Excel Worksheet to look like the paper used in continuous-feed dot-matrix printers? In other words, is there a way to have one row in white, the next row in green, the next one in white, and so on?" Well, here are a few things to consider. First, the format of that paper was two rows green, two rows white. Second, you can format any Excel range to look almost just like that paper, as follows:

1. Select the range you want to format.
2. Choose Format + AutoFormat.
3. In the Table Format list, select List2.
4. Click OK.

What do we mean by "almost"? Excel formats the first single row of the range a darker green--to accent the column labels. Don't try to apply this AutoFormat--or any other--to the entire worksheet; you'll cause Excel to crash.

back to top

FORMULAS--SELECT 'EM ALL!

In the past, we've told folks how to print all their formulas. How about selecting all the formulas in one quick step so that you can know, at a glance, which cells contain formulas and which don't?

1. Choose Edit + Go To (or press Ctrl + F5).
2. Select Special.
3. Select Formulas.
4. Click OK.

Excel selects every one of your formulas; they appear in black.

back to top

SORRY

A frustrated Excel user recently asked this perfectly logical question: "In Word, you get buttons for border thickness. Is there a similar button for Excel? The keyboard shortcut (Ctrl + 1) is okay, but a simple selector on the toolbar would be preferable."

Indeed it would--but we can't find it. So for now, until one of our crack researchers OR our even more crack customers finds the missing button, the answer is no.

back to top

NO COMMENT

Yes, Excel's little comment notes are a blast. But wouldn't it be even better if, instead of double-clicking the comment notes, you could read your comment whenever you selected the cell to which it was attached? Sure it would.

Here's how to add your comments when the cell in question contains a formula or function:

1. Double-click the cell containing the formula or function you want to annotate.
2. Press End to move the cursor to the end of the formula or function.
3. Type ONE space, a plus sign, another space, and then the letter N and an opening parenthesis.
4. At the end of what you just typed in Step 3, type your annotation text in quotation marks, followed by a closed parenthesis.
   For example, an annotate formula summing a column might look like this:
   =SUM(A4,A10) + N("The sum of all regional sales")
5. Press Enter.

Now whenever you select the cell, the formula and your note appear in the formula bar.

back to top

MAKE A DATE WITH THE NEXT MILLENNIUM

An Excel user asks, "Is there a way to enter a date so that all four digits appear in the year--so that the year 2000 will appear as '2000' instead of '00'?"

Well, you can't enter days to display that way, but you can format them to, as follows:

1. Select the cell(s) containing the dates you want to format.
2. Right-click the selected cell(s) and choose Format Cells. The Format Cells dialog box appears.
3. Click the Number tab.
4. In the Category list, select Custom.
5. In the Type text box, type mm/dd/yyyy
6. Click OK to close the Format Cells dialog box.

Excel now formats the cells to display the entire year.

back to top

DATES AND NUMBERS:
Working with dates in an Excel cell

Here's a question from a user:

"I was recently trying to use Excel's Datevalue function. According to the documentation, Datevalue should convert a date in text format to a date serial number. I'd like to go to cell A1 and enter a date. Then I'd like to go to cell A5 and enter =datevalue(a1) to get the serial value of the date in cell A1. This doesn't work. All I get is #VALUE! in cell A5. Is it because the date is not in text format? Can you help?"

As you've already concluded, the problem is most likely because the date isn't in text format.

Try this:

1. Enter into cell A1 '04/01/99 (the apostrophe before the date sets it to text format)
2. Then move to cell A5 and enter =datevalue(a1) The result should be 36251.

If you need to work with dates in date format, you can use the Text function to convert the date to text and then get the serial number.

For example:

1. Enter 4/1/99 into cell A1.
2. Now, move to cell A5 and enter =datevalue(text(a1,"mm/dd/yy"))

The Text function will convert the date to text in the mm/dd/yy format. Datevalue will then calculate the serial number from the text version of the date.

back to top

Error-free formula entry in Excel 97

An Excel user offers this tip:

"I have found I make fewer errors in formula entry if I use the mouse to click the correct cells--as opposed to typing in cell references. I wonder if a number of Excel users aren't aware of this point-and-click method of formula entry. Perhaps this would make a useful tip."

The user is correct. You can enter formulas using Excel's point-and-click method. Let's look at an example of how this works.

1. Open a blank worksheet and enter four or five numbers into column A.
2. Let's say you use 1, 2, 3, 4, and 5 in cells A1 through A5.
3. Move to cell A7 and enter =3Dsum(
4. Now click cell A1 and drag the mouse down through cell A5 to select the entire range.
5. Press Enter, and Excel completes the formula and displays the result (15).

back to top

Copying From Web Pages Into Excel

 
Want to copy financial data from a Web site into your Excel 97 worksheet? As long as the data is in an HTML table--as financial data usually is--you can do it as follows:

1. On the Web page, select the data.
2. Press Ctrl + C to copy the data to the Windows Clipboard.
3. Switch to Excel.
4. Select the cell where you want the top-left number of the copied data to appear.
5. Choose Edit + Paste (or press Ctrl + V).

The data appears but may include additional cells. Delete rows containing any HTML-specified formatting that you don't need, and you're done.

How to copy graphics (such as charts) from a Web page into an Excel worksheet:

1. In your browser (Netscape or Internet Explorer), right-click the chart or graphic you want to copy.
2. Select Copy from the shortcut menu.
3. Switch to Excel.
4. Select the cell where you want the top-left corner of the chart or graphic to appear.
5. Choose Edit + Paste Special.
6. In the As box, select Bitmap.
7. Click OK.

Windows pastes the chart or graphic into your worksheet as a bitmap image. Keep in mind that you can't edit the image and, if you enlarge the graphic beyond its original size, the on-screen and printed image won't be as sharp.

back to top

CLOSE EVERYTHING!

So when the day is done and you need to close your Excel workbook files, instead of closing them one at a time, shut 'em all down as follows:

1. Hold down the Shift key.
2. Choose File + Close All from the menu. 

Holding down the Shift key changes Excel's File + Close command to a File + Close All command. You'll be off to the washroom and out of the parking garage a lot sooner than before.

back to top

GETTING SHIFTY AGAIN

Another magical quality of the Shift key to make pasting pictures easier.

Suppose, for example, that you copied a presentation page from PowerPoint or a table from Word, and now you want to paste a picture or linked picture of that object into your Excel worksheet. Although you could use Excel's Edit + Paste Special command, why not use a faster method?

Here's how:

1. Hold down the Shift key.
2. Choose Edit + Paste Picture to paste an unlinked picture of the object into your worksheet.
3. Alternatively, choose Edit + Paste Picture Link to paste a linked picture of the object (a picture that changes to reflect changes in the source file).

That's the sum total of our shiftiness, for now.

BE SELECTIVE ABOUT WHAT YOU DELETE

When you select a cell and press Delete, Excel deletes the contents of the cell. But you may want to delete other elements as well--formatting, comments, or everything:

1. Select the cell(s) from which you want to delete something.
2. Choose Edit + Clear.
3. From the menu list, select the elements you want to delete.

THIS TIP IS A DRAG

Tired of dragging a range or an object to the bottom of your Excel screen--only to have the screen scroll down several rows, even though you really didn't want it to? Next time, hold down the Alt key as you drag. When you hold down the Alt key, the Excel display doesn't scroll as you drag. You won't want to use this technique all the time, but when you need to drag something to an edge of the display, it's a frustration-fighter.

The Alt key makes dragging (copying or moving) cells or objects to another worksheet in your workbook easier.

Next time you want to move a range or an object from one workbook to another, try this:

1. Hold down the Alt key.
2. Drag the range or object to the worksheet tab of the worksheet you want to move to. That worksheet appears on-screen.
3. Drag the range or object to the desired location on this worksheet.

To copy something from one worksheet to another, use the same steps, except hold down both the Alt and Ctrl keys while you drag.

USERS SUBMIT THE DARNEDEST TIPS

An Excel user suggested this great little tip:

"In Excel, you can customize the toolbars by adding and moving buttons. However, if you don't want to take up space on the display, try dragging the buttons you use most to the end of the menu bar!"

What a great idea!

To drag buttons to the end of the menu bar:

1. Hold down the Ctrl and Alt keys.
2. Click and drag an oft-used button, such as the Print button, to the end of your menu bar (right after Help).
3. Repeat Steps 1 and 2 to add other buttons to the menu.

Now, even if you remove all your toolbars from the display, you still have access to your most important buttons!

FASTER FORMULA HELP

The Insert + Function command gives you step-by-step help through the process of creating a formula. But if you're a little more familiar with formulas--but not with the syntax and arguments of a particular formula--try the following:

1. Begin entering your formula--that is, type the name of the formula (such as "SUM" or "AVERAGE").
2. Press Ctrl + Shift + A. The formula's arguments appear right in the cell.
3. Complete the formula simply by replacing the arguments.
4. Press Enter when your formula is complete.

   ---

CLICK TO SET UP

An Excel user asks, "Is there an easier way to get to the Page Setup dialog box in Excel? I would like an icon or keyboard method."

The answer is an icon:

1. Select :Customize" from "Tools" menu to launch the Customize dialog box.
2. Click the Commands tab.
3. In the Categories list, select File.
4. In the Commands list, drag the Page Setup button (it has the words Page Setup and no picture) to the Standard toolbar. We recommend positioning it just to the left of your Print button.
5. Click Close to make the Customize dialog box disappear and save your changes.

From now on, to set up the page, just click the button.

PRICE QUOTES WIDTH STYLE

An Excel user asks, "I frequently use Excel 97 to quote prices for my customers. My quotes would look neater if I could vary the width of cells in a particular column. Is this possible?"

Not just as you describe it.  The cells in a single column must all be the same width. But you can work around this by using two adjacent columns as one.

• Merge adjacent cells when you need a "wider" cell.  Just select both cells and click the Merge and Center button on the toolbar. When you need a "narrower" cell, use a single cell from one of the columns.
• Let us know if this trick works for you.

  ---

IT'S ALWAYS MY DEFAULT

An Excel tedium-fighter asks, "I create lots of graphs with Excel. Unfortunately, the options to which Excel defaults are not the ones I use most. Is there any way to change the graph defaults to the ones I prefer?"

You bet:

1. Right-click a chart that uses the defaults you prefer.
2. Choose Chart Type from the menu that appears.
3. Click the Set as Default Chart button.
4. Click OK.

From now on, Excel creates charts based on the default you prefer, which will save you a couple of clicks per chart, at least.

Prune Your Rows

You're all familiar with the Excel column-width adjustment shortcut: Just double-click the right edge of a column's heading, and the column width adjusts to fit the largest entry. But did you know that you can adjust row heights the same way?

To adjust the height of a row to fit the tallest entry, double-click the bottom edge of the row's heading.

This trick is especially useful when you've increased a row height BEFORE entering data in a row and want to retrofit the row afterward.

Symbolic Gesture

An Excel user asks: "Is it possible to use AutoCorrect to insert symbols in Excel 97 the same way you can in Word 97? Specifically, I use the check mark from the MonoType Sorts font a lot--can I set AutoCorrect to enter it, without having to switch to the font?"

No , I'm afraid not.  AutoCorrect can replace certain characters with other characters, but it CAN'T change fonts for you. Sorry about that.

Window Tiles: An Expanded View

An Excel user asks: "Is there any way to display two worksheets from the same workbook at one time in Excel?"

Yes there is.  Assuming you have the workbook in question open, follow these steps:

1. Click Window New Window.
2. Click Window Arrange.
3. In the Arrange Windows dialog box, choose Tiled and click OK.
4. In one of the windows, click the sheet tab for the second sheet.

Now you can see both worksheets!

Help Your Help

Excel's Help screens contain plenty of information. But sometimes you discover additional information while using a feature--information you'd like to remember. No problem: Excel's Help lets you add your   information to any Help screen, as follows:

1. From the Help screen menu, click Options Annotate.
2. In the dialog box that appears, type the information you want to add to the Help topic.
3. Click Save.

Excel adds a tiny paper-clip icon to the Help topic screen. From now on, to read your annotation, just double-click the paper clip.

back to top

NAME 'EM

You've created a table with handy-dandy column labels (such as "Jan," "Feb," and so on) and equally handy-dandy row labels (such as "East," "West," and so forth).

Here's how to quickly turn these labels into even handier, dandier "names" (a name describes a cell or range and can be used to refer to data in formulas):

1. Select the entire table.
2. Press Ctrl + Shift + F3.
3. In the Create Names menu that appears, select both Top Row and Left Column.
4. Click OK.

Excel quickly creates names for each column and row in the table, based on the row and column labels you had already entered.

back to top

THINK OF THEM AS CONTROL PAGES, NOT WORKSHEETS

A multi-sheet application builder asks, "Is there a shortcut key or keystroke combination for switching between sheets (not windows) in a spreadsheet?"

You bet there is:

• To move to the next sheet in a workbook, press Ctrl + PgDn.
• To move to the previous page in a workbook, press Ctrl + PgUp.

It's that easy--our favorite kind of question to answer!

back to top

PRINTING, ON THE DOUBLE

An Excel user asks, "How can I get a single-spaced spreadsheet to print out double-spaced?"

Well, the Print dialog box doesn't have a "print double-spaced" option; Excel pretty much prints things the way they appear on-screen (double-spaced printing commands went by the wayside with DOS).

But you CAN quickly change your worksheet to look like a double-spaced worksheet, as follows:

1. Select the entire worksheet (press Ctrl + A).
2. Right-click any cell and choose Row Height.
3. In the box that appears, type 25.5 (or whatever number is double the number that's already in there).
4. Click OK.
5. Now print your worksheet (choose File + Print).
6. When you're done, to return things to normal, just choose Edit + Undo Row Height.  

back to top

DON'T OVERHEAT FROM TOO MANY SHEETS

Here's an interesting pickle: You've created a workbook with many, many worksheets--so many, in fact, that you can't see all the tabs at any given time. How can you select a tab you can't see? By using the tab scroll buttons, to the left of the worksheet tabs!

Here's how:

• To scroll to the first tab in the workbook, click the left-most scroll button.
• To scroll to the final tab in the workbook, click the right-most scroll button.
• To scroll one tab to the left or to the right, click the second or third scroll button, respectively.
• To scroll several tabs at once, hold down the Shift key and click one of the two middle buttons.

Hint: The tab scroll buttons only work when you have so many sheet tabs that you can't see them all. (After all, if you can see them all, you can simply click the tab you want to go to.)

back to top

MIX AND MATCH CHART TYPES

Sometimes, charts can be so one-dimensional--all bars or all lines or all pie slices--but they don't have to be. With Excel, you can combine chart types within a single chart by using a different chart type for a particular data series within the chart. Although doing so isn't always a good idea, it can sometimes help you add extra meaning to a data series or make one data series stand out from the rest. For example, you have a bar chart but would like to represent one of the series as a line chart to show not only the values in the series but also the trend over a period of time.

To combine chart types, follow these steps:

1. If the Chart toolbar isn't already displayed, right-click any toolbar and select Chart.
2. On the chart, click the series you want to change.
3. On the Chart toolbar, click the arrow next to the Chart Type button and then select the new chart type for the series (in our example, a line chart).

Excel changes the chart type for the selected series only.

back to top

GET THINGS IN LINE

You've got a few objects on your Excel worksheet--pictures, charts, text boxes, org chart elements, whatever. These objects look nice individually, but, well, they don't line up. In fact, they line up so poorly, they look like they were placed haphazardly onto your worksheet--a look that works at museums of modern art but doesn't really wow them at the office.

Luckily for you, the Drawing toolbar makes aligning objects easy:

1. Hold down the Ctrl key and click each object you want to align.
2. On the Drawing toolbar, click the Draw button.
3. Choose Align or Distribute from the pop-up menu.
4. Select the alignment option you prefer; the diagrams next to each option illustrate the result.

Excel aligns the objects. Because the objects are still selected, you can move them together to another location, if you want, without disturbing the alignment.

Calculations/Formulas

Controlling Calculation Precision

Excel numbers should be rounded off to avoid problems high-precision calculations may cause. You can set Excel's calculation precision.   The problem with this method is that Excel calculates ALL the workbook numbers at the formatted precision.  In short, you could lose some needed precision in your calculations

If you would like Excel to calculate using the cell format's precision:

• Choose Tools, Options.
• The Options dialog box appears, click the Calculation tab.
• Select the Precision As Displayed check box.
• Click OK to close the dialog box and save your setting.

BACK TO TOP

Cells

Auto Fill

Who wants to type the same data over and over?   Use a Auto Fill operation to copy data  from cell to another.

• Type your data in a cell.
• Click and hold the mouse button on the fill button within the cell--the small black box in the lower right corner.
• Drag across cells to the right or left in the row or up or down in the column.
• Release the mouse button.

A copy of your data appears in all the cells you selected by dragging.  In fact, if you wanted, you could have dragged across a rectangular area, of row and column, to fill the entire area with the data.

A fill automatically copies information into one or more cells.   Most experienced spreadsheet jockeys use the fill at some point to extend a number down a long column or across some portion of a row.  Using fills in this way saves having to type a number in over and over. You can even fill a calculation, not just raw data. It turns out you can also fill from one worksheet of a workbook to other worksheets.

To fill across worksheets:

• Hold the CTRL key.
• Click on the tabs of the sheets you want to fill to.
• Select the cells to copy (as a row, column, or area).
• Choose Edit,  Fill.
• Select Across Worksheets.
• Select the kind of fill: Contents, Formats, or All (which is Contents and Formats).
• Click OK.

BACK TO TOP

Cell Borders

To add a red border to workbook cells to a specific number reference, for example whenever anyone enters a number less than -10.00.

• First select the entire workbook by holding down the CTRL key while selecting all the sheet tabs.
• Press the CTRL+A keys.
• Choose Format, Conditional Formatting.
• The Conditional Formatting dialog box appears, click the arrow at the right side of the entry box to the right of Cell Value Is.
• Select Less Than from the list.
• Press the TAB key to move to the number entry box and enter -10.00 (for example).
• Click Format to open the Format Cells dialog box.
• The Format Cells dialog  box appears, click the Border tab.
• Click the arrow to the right of the Color list box and select red (or whatever color you want).
• Click the Outline preset, then click OK to close the dialog box and save your color and outline selection.
• Back in Conditional Formatting, click OK to close the dialog box and apply your new format.
• If you enter any number less than -10.00 anywhere in your workbook, Excel will apply a red border to that cell.

BACK TO TOP

Copy Contents of Single Cell to Several Cells

• Hold down the CTRL key.
• Select the cells into which you want to enter the information.
• Type your entry.
• Press CTRL + ENTER keys.

BACK TO TOP

Copy not AutoFill

When you enter a day, such as Monday, in a cell and  drag that cell over adjacent cells, Excel automatically enters successive days--Tuesday, Wednesday, Thursday, etc.--in adjacent cells.

If you want to copy text in a cell into  adjacent cells:

• Select the cell with the specific text or number to be copied.
• Hold down the CTRL key as you drag.
• This copies the contents of the current cell to the cells you drag over.

BACK TO TOP

Enough with AutoFill

• When you enter a day, such as Monday, in a cell and then drag that cell over adjacent cells:
  • Excel automatically enters successive days--Tuesday, Wednesday, and Thursday--in the adjacent cells. 
• But if you just want to COPY the original day into the adjacent cells--that is, create a row full of Mondays:
  • Hold down the CTRL key as you drag.
  • This simply copies the contents of the current cell to the cells you drag over.

BACK TO TOP

Erasing Cell Contents with the Mouse

This tip is handy when you have your hand on the mouse and don't want to reach over to press the Delete key.

• Select the cells that contain the data you want to erase.
• Now use the handle to drag backward over the cells.
• The cells turn gray as you do this.
• When you release the mouse button, the cell contents disappear.

This operation is exactly the opposite of dragging the handle to AutoFill cells.

BACK TO TOP

Format Cell Contents Quickly

To format a cell's contents quickly:

• Right-click the cell.
• Choose Format Cells from the Pop-Up menu.
• The Format Cells dialog box appears, select any formatting choices.
• Click OK.

BACK TO TOP

Format Painter

For a complicated cell format you'll use day after day, you make a style. But for a complicated cell format you'll only use during this work session, go for the Format Painter button instead.

• Format a cell as you like, adding color and fancy effects to your heart's desire.
• Click that cell.
• Click the Format Painter button (the paintbrush on the toolbar).
• Click any other cell you want to format the same way.
• Excel applies the format, with all of its choices, in one sweep (or click, rather) to the new cell.

BACK TO TOP

Long Cell Entries

Those of you who deal with really large formulas -- and we're talking REALLY large -- or who create really large text entries (as in a database field) should know the following things:

• An Excel cell can contain up to 32,767 characters -- about 30 pages of text!
• While you can see ALL of these characters in the Excel formula bar, in an Excel cell you will typically not see more than 1,024 characters or so.

BACK TO TOP

Select All Cells in a Column of a Filled Range

A quick way to select all cells in a column of a filled range:

• Click anywhere in the column.
• Press the CTRL+SHIFT+8 (the number 8)keys.
• All consecutively filled cells in the column will be selected.
• If you hold the SHIFT key and move the ARROW keys you can select adjacent columns.

BACK TO TOP

Sorting Cells

You can sort cells in an Excel worksheet without disturbing the formulas.  If you set the calculation mode in Excel to manual, the formulas will not be disturbed by a sort.  When the sort complete, you can set the calculation mode back to automatic. 

• Open the worksheet you want to sort and choose Tools, Options.
• The Options dialog box appears, select the Calculation tab.
• Select the Manual radio button.
• Choose OK.

To set the calculation mode back to normal:

• Choose Tools,  Options.
• The Options dialog box appears, select the Calculation tab.
• This time, select the Automatic radio button
• Choose OK.

BACK TO TOP

Text Wrap

Excel lets you wrap text in a cell.  What most folks DON'T know is how easily you can do it--that is, without having to resort to right-clicking the mouse or going to the menu.

• Type some text in a cell.
• Press the ALT+ENTER keys to insert a hard return wherever you'd like the text to wrap to a second line.
• Type some more of your text.
• Press the ENTER key when you're done.
• Excel wraps the text to fit in the cell.
• Double-click the right edge of the cell's column heading button.
• The column widens to accommodate everything before the hard return.

That's easy--you're wrapping while you type!

BACK TO TOP

Turn Off AutoComplete

The AutoComplete feature can save you time.   It records what you type and, when you start to type the same thing again, it finishes the typing for you.   However, AutoComplete can be frustrating when it keeps guessing wrong. You type "No," and it thinks you're going to go on to type "North Carolina,"--even though you intend to type "North Dakota."   Not a big deal, but it frustrates some people.

To turn AutoComplete off:

• Choose Tools, Options.
• Select Edit.
• Deselect Enable AutoComplete for Cell Values.
• Click on OK.

BACK TO TOP

Columns/Rows

AutoFit Columns

Columns aren't always an easy fit, either, what with all the variety in data length that can appear in a long

column.   With Autofit, you don't have to carefully drag the column border to make sure all data appears in full.

• Double-click the right border of the column; it automatically pops to the right width.

BACK TO TOP

Compare

Let's set the scene: You've got a really, really long row or column of numbers or text entries in Excel.   Most of the entries are the same as the leftmost or topmost entry, but a few aren't--and you want to find those few. You are not sentenced to the fate of searching for these variants by eye.

To find the cells unlike the first cell in a row or column:

• Select the row or column (just click the appropriate row button in the worksheet frame).
• Press the CTRL + \ keys, if you selected the row; press the CTRL + | keys, if you selected the column.
• Excel selects all the cells differing from this first "comparison" cell.

Suppose you want to find cells that differ from a cell other than the row's or column's first(leftmost or topmost) cell.

To find cells that differ from any cell in a row or column:

• Select the row or column.
• Press the TAB key until you've selected the cell to which you want  to compare the others.
• Press CTRL + \ , for the row,  press CTRL + |, for the column.

BACK TO TOP

Flipping Column or Row

Ever create an Excel table that's just perfect--except that you wish the columns were rows and the rows were columns?

• Select the table.
• Choose Edit, Copy.
• Select the cell where you want the new table to begin (this cell CAN be in the old table).
• Choose Edit,  Paste Special.
• In the Paste Special dialog box, select Transpose and click OK.
• If necessary, delete the remains of your old table.

BACK TO TOP

Freezing Column or Row

Worksheets often have labels in the first cells of each column or row.   As the worksheet grows larger than one screen, though, scrolling to see or change information on the edges means scrolling away from those labels.   In other words, soon you find yourself trying to enter information when you can't quite pinpoint which row or column represents what. The answer is freezing.

To freeze a row or column:

• Click a cell just to the right of the row or just below the column with your titles. (Some worksheets use more than one column or row to hold titles.)
• Choose Window, Freeze Panes.
• Now the rows to the left and the columns above your labels stay on-screen, no matter how far you scroll.
• To turn this feature back off at any time, choose Window, Unfreeze Panes.

BACK TO TOP

Resetting Column & Row Back to Default Setting

To reset rows to the default row height:

• Select the rows.
• Choose Format,  Row,  Height.
• In the Row Height dialog box, type "12.75" (without the quotation marks).
• Press Enter.

To reset columns to the default column width:

• Select the columns.
• Choose Format,  Column, Width.
• In the Column Width dialog box, type "8.43" (without the quotation marks).
• Press Enter.

BACK TO TOP

Slanted Column Headings

In the days when people made spreadsheets by hand, you would often see a slanted column heading used to save space and enhance the spreadsheet's appearance. It's very easy to do the same thing in an Excel worksheet.

• All you have to do is select the cell or block of cells that contains the header text.
• Choose Format, Cells.
• When the Format Cells dialog box opens, click the Alignment tab.
• Now use the Degrees spin box to set the direction and amount of slant you want for the headings.
• When you finish, click OK to close the dialog box and apply your new text setting.

BACK TO TOP

Copy & Paste into Word

• Open the worksheet containing the data o be copied in Word.
• Open  a new document in Word, and position your cursor where you want the data to appear.
• Choose Edit, Paste Special.
• In the Paste Special dialog box, under As select Formatted Text (RTF).
• Click OK.
• Word pastes the data (formatted as it is in Excel) into a Word table that spans as many pages as necessary.

BACK TO TOP

Copyright and Trademark Symbols

Excel automatically adds trademark and copyright symbols to your text . 
Note:  Please include the open and closed parenthesis.

BACK TO TOP

Day/Time

Military Time

Excel assumes a 24-hour military-style clock.   If you type 6:30 for a time,  Excel assumes you mean 6:30 in the morning.

To indicate 6:30 in the evening, you have to type either of the following:

• 6:30 PM (be sure to keep that space between the numbers and the PM).
• 18:30 (military style adds 12 to hours after noon) .

BACK TO TOP

Weekday Function

The easiest way to convert a date into its corresponding day of the week.  

• Select the cell containing the date.
• Right-click on the cell and choose Format Cells from the shortcut menu.
• The format cells dialog box appears.
• Select the Number tab.
• Under Category, select Custom.
• Under Type, enter dddd.
• Choose OK.

BACK TO TOP

File Management

File Properties

You can sign your file--put your name to it so anyone who looks can see who created it and when.  Having such a signature can be especially important when you make lots of worksheets that you intend to share with others in a collaborative group.

To add a signature to your worksheets:

• Choose File, Properties.
• The Properties dialog box appear, click the Summary tab.
• In the appropriate text boxes, enter the title, subject, author, manager, company, category, keywords (for finding this file later, these are descriptive terms), and any comments.
• Note: The Author and Company may already be filled.
• Click OK.

BACK TO TOP

Recently Used Files

At the bottom of the File menu is a list of Recently Used Files.   With this list, you can open a file without using the Open command and working your way through folder directories.   Choose a file from this list.  

If you want more than 4 files shown in the  Recently Used Files list:

• Choose Tools, Options.
• Select General.
• Make sure the Recently Used File List box is checked.
• Put the number of files you want listed into the Entries text box.
• Click OK.

BACK TO TOP

Sorting Files

Did you know that you  can sort the files by date and time? 

• Choose File, Open.
• The Open dialog box appears, click the Commands And Settings button (it looks like a window with a check mark in the foreground).
• When the menu opens, choose Sorting to open the Sort By dialog box.
• Now click the arrow at the right side of the Sort File By list box and select Modified from the list.
• Select the radio button labeled Descending.
• Click OK to close the dialog box and sort your files.
• Now you should see the last modified file at the top of the list.
• The new setting remains in effect unless you elect to change it.

NOTE:  This method works in all the Microsoft Office 97 programs.

BACK TO TOP

Switching Between Files

If you have several  Excel files open and working on them simultaneously.  To switch from one file to another:

• Hold down the CTRL key and lightly tap the F6 key.
• This will toggle between the open files.

NOTE:  This method works in all the Microsoft Office 97 programs.

BACK TO TOP

Find & Replace

To delete every occurrence of a word or phrase, replace it with nothing:

• Open Edit, Replace.
• The Replace dialog box appears, in the Find what line, type what you want to delete.
• Leave the Replace with line blank.
• Click on Find Next (to make sure you typed the Find what phrase correctly).
• If Excel correctly found what you want deleted, click on Replace All.

As you use this deletion method, you'll discover that sometimes it works better to replace with a single blank space instead of with nothing at all.

BACK TO TOP

Repeating a Find

• When you want to find a specific word in a worksheet:
  • Press CTRL+F keys to open the Find And Replace dialog box.
  • Type the word and click Find Next.

• If you would like to locate all occurrences of the same word without leaving the Find And Replace dialog box open:
•  
  • Click the double down arrow at the bottom of the scroll bar.
  • This takes you to the next occurrence of the word you entered in the dialog box.
  • You can repeat this until you have located all occurrences.
  • When you click the double down arrow, Excel searches from the current point downward.
  • If you want to search from the current point upward, click the double up arrow.

BACK TO TOP

Graphics

Draw Floating Shape

With Excel, you can draw simple shapes that "float" over a worksheet; that is, they do not directly interfere with or become part of the calculations on the sheet:

• Click the Drawing button on the toolbar (the  icon resembles three small shapes).
• The Drawing toolbar appears near the bottom of the worksheet window.
• Click a shape on the toolbar.
• Move the mouse cursor where you want the shape and press the mouse button.
• Holding the mouse button, drag the mouse to form the shape.
• Release the mouse button.

That's all there is to it! To change the look of the shape, right-click it and use the formatting options

BACK TO TOP

Inserting Picture or Chart

Images aren't as natural an element in worksheets as they are in word processor documents. Still, sometimes a little graphic can go a long way toward improving the look and power of a worksheet. Generally, you include charts in worksheets, but occasionally you may want to include a drawing or photo. Here's how to insert a picture of any kind:

• Place the cursor in the cell where you want the picture.
• If you want a pie, bar, line, or other chart that derives from numbers, choose Insert, Chart.
• Then follow the instructions in the Chart dialog box.
  
•  If you're more interested in decoration than analysis, choose Insert,   Picture
• Choose one of the following:
  • Clip Art (little drawings)
  • From File (drawings, photographs, or a chart stored on disk)
  • AutoShapes (geometrical basics)
  • Organization Chart (for a personnel tree or for a hierarchical display that follows that pattern).
  • WordArt (for special effects applied to readable text as a decoration)

BACK TO TOP

Picture Borders

Pictures you insert into a worksheet are  frameless, floating above the cells. If you want a more formal division between the picture and the cells' contents, put a border around the picture:

• Right-click the picture and, from the pop-up menu, choose Format Object.
• The Format Object dialog box appears, click the Colors and Lines tab.
• In the Line section of the dialog box, click on a color for the line(Black is generally the best).
• Select different style and weight options.
• Click OK.

BACK TO TOP

Headers & Footers

To change Excel's default settings for footers.

• Create a new workbook.
• Adjust your footer (and any other characteristics- font, type size, etc.).
• Save the file as book.xlt in the XLStart folder (or, as directed by Excel's Help file, to the alternate start-up directory).
• Now, the new default workbooks will have the footer you're looking for.

BACK TO TOP

Help

The "What's This" help is often easier to use than the menu help because it doesn't force you to search through an index of help topics.

• Press Shift + F1.
• Move the mouse pointer to the screen area you're wondering about.
• Click the mouse button.
• Help information about that screen area appears.

BACK TO TOP

Hiding Data

You can keep some of your entered and calculated data secret.   This isn't true password security, mind you, but it does keep others from instantly seeing what you're up to.

• To hide an entire workbook: 
  • Choose Window,  Hide.
• To hide a worksheet:
  • Select the worksheet (click on its tab), choose Format, Sheet, and select Hide.
• To hide a row or column:;
  • Select the row or column, choose Format, Row or Column, and select Hide.

Keep these little tricks in mind if, for example, you don't want people to be able to wander by your desk and glance at your display.

BACK TO TOP

Hyperlinks

To edit a hyperlink cell:

• Right-click the hyperlink.
• In the shortcut menu, choose Hyperlink, Edit Hyperlink.
• The Edit Hyperlink dialog box appears, make the changes.
• Click OK.

BACK TO TOP

Keyboard Shortcuts

BACK TO TOP

Numbers

Number Alignment

To quickly left align numbers that will not be used in calculations:

• Type an apostrophe(') before the number.
• The number will appear to the left of the cell rather than the right.

BACK TO TOP

Treat Number Like Text

If you type letters within an Excel cell, the program treats your input as text data and left-justifies it   If you start with numerals, on the other hand, Excel treats your input as numeric data and right-justifies it.    Sometimes, though, you want to type a number that should be treated like text--for example, a phone number, zip code, or SKU (retail sales code). Unfortunately, this throws Excel for a loop; it thinks you typed a number and formats and processes it accordingly. You, being smarter than Excel, must help the poor program by starting any such entries with a single apostrophe, '. With the initial apostrophe, Excel knows that it should treat what follows as text, no matter what it is. Oh, the things we must do for the less fortunate.

BACK TO TOP

Pivot Tables

Pivot Table Wizard

The Pivot Table wizard can create Pivot Tables from data you selected in an Excel worksheet.  It's a great tool for seeing trends in a big pile of cells filled with date.  You could take an entire course in Pivot Table analysis, but here's the quick look at how to make one:

• Open the worksheet with the data.
• Select a cell within the data (or drag across the full data area you want to analyze, including column titles).
• Choose Data, Pivot Table Report.
• The Pivot Table Wizard dialog box appears, because you want to analyze an Excel list, which is selected by default, click Finish.
• Your Pivot Table appears as part of a new worksheet in this same workbook.
• It summarizes the information surrounding the cells  specified.

BACK TO TOP

Printing Options

AutoFormat

AutoFormat does a great job of sprucing up your worksheets.    If you use a black-and-white laser printer, you should select one of the simpler black-and-white formats.  Otherwise, your printout may look too dark and cluttered.  Even if you have a color printer, some of the more colorful formats can prove difficult to read.

• Select the data you want to format.
• Choose Format, AutoFormat.
• The AutoFormat dialog box opens, select the type of format you want to apply.
• Click OK to close the dialog box and apply your formatting selection.

BACK TO TOP

Collating Printing Option

When printing multiples copies of a multiple-page worksheet, the pages usually print as a bunch of page 1s, followed by a bunch of page 2s, followed by a bunch of page 3s, etc. With the tip below, your pages will print  out grouped, pages 1,2, and 3 together, then another page 1,2, and 3, together, etc. 

• Open File, Print.
• The Print dialog box appears.
• At the lower right area you can dictate how many copies to print.
• Place a checkbox To Collate and click OK.

BACK TO TOP

Fast Print Range Settings

To set a print range in Excel, you could select the area you want to print and choose File, Print Area , Set Print Area. But why use the menu when you can use a button?   How? you ask.

By adding the Set Print Area button to your Standard toolbar:

• Choose Tools,  Customize.
• Click the Commands tab.
• Under Categories, select File.
• Under Commands, find the Set Print Area icon and drag it into place on the Standard toolbar.
• Click Close.

Now, next time you want to set the print area, click the Set Print Area button and select the Print area.

BACK TO TOP

Hiding Data From the Printer

When you want to print an Excel worksheet, in some situations you may prefer not to print the entire area--perhaps you have some data in the print area you don't want readers to see on the printout.   All you have to do is temporarily hide the data cells you don't want printed.

To hide the cell data:

• Set the font to white.
• Select the cells and choose Format, Cells.
• When the Format Cells dialog box opens, click the Font tab.
• Now click the arrow at the right side of the Color list box and select white from the list.
• Click OK to close the dialog box and apply your new font color.

You can now print the worksheet without showing the unwanted cells.    After you finish printing, select the cells again and set the color back to black (or automatic