Collection of Excel tips 11/09/2001
Use
AutoFill to Quickly Copy Formulas and Formatting in Excel
From Aaron Warner, Saline, Michigan
Would you like an easy way to extend a series of numbers in Microsoft Excel
without typing each one individually? With AutoFill, you can quickly
copy data, formulas, or formatting to adjacent cells. This brief tutorial
will show you how:
1.
Select the cells that you would like to copy.
2.
Move the cursor to the bottom right corner of the highlighted cells.
Your cursor will then turn into a black plus sign.
3.
Click and hold down the right mouse button and drag across the cells
you want to fill.
4.
Release the mouse button, and when the shortcut menu appears, click
Fill Series.
By
following these steps, you will be able to save a lot of time creating your
spreadsheets.
Show or Hide the
Formulas in an Excel Spreadsheet
From Rifkin Young, San Francisco, CA
When you're working in an Excel
worksheet, you can alternate between viewing the values in the cells and
displaying the formulas. To toggle between the different views, press CTRL+`
(single left quotation mark).
Editor's Note: If you're having trouble finding the single left quotation mark, it's on the same key as the "~" symbol. On most keyboards, it's the key directly to the left of the "1" key.
Don't Lose Sight
of Your Column Headings in Excel
From Al Dyer, Cumming, Georgia
Would you like to see the column
headings on your Microsoft Excel spreadsheets no matter how far down you
scroll? Here is one way to keep the column headings constantly visible:
1.
Select the row just below your column headings.
2.
On the Window menu, click Freeze Panes.
The "frozen" column headings don't scroll, but remain visible as you move through the rest of the worksheet.
Edit Cells
Quickly in Excel—Without Using Your Mouse
From Stuart V. Begg, Australia
If you like to use your keyboard for
everything, editing a lot of data quickly in an Excel spreadsheet can be
difficult because you find yourself constantly reaching for the mouse when
you want to make changes to a cell. But there's a shortcut you can use so
that your hands never have to leave the keyboard—press F2. Here's how:
1.
Use the arrow keys to select the cell you want to edit.
2.
Then press F2 (or COMMAND-U, if you use a Macintosh computer) to edit
the cell contents.
3.
When you're finished, just press ENTER (or RETURN on a Macintosh
keyboard) to enter your changes. Or press ESC to cancel the changes.
Editor's Note: This tip is especially handy for editing hyperlinks in Excel because, if you use your mouse to click on a cell with a hyperlink, it automatically opens an Internet browser window. Using the keyboard lets you edit hyperlinks with ease.
Create an Excel
Chart with the Push of a Button
From Klaus Elfert, Greven, Germany
This is a very old Microsoft Excel
trick. To quickly create a chart, using only your keyboard, select the data
you want to plot and then press F11. Excel automatically creates the chart
for you.
Editor's Note: G. Raghavan of Tamilnadu, India, wrote in with another way to do this trick: After you select your cells, press ALT+F1 and you'll get the same result.
Select an Entire Range of
Cells in Excel
From Kees Podt, Voorburg, Netherlands
In Excel, if you want to quickly
select the entire range of cells you're working on, press CTRL+SHIFT+
ASTERISK (*).
For example, if you have a list of
customers in Excel, this command will select the entire list and the column
headings, but not the empty cells around the list—so you get only the cells
you need.
This tip is different from the Select All command, which selects every cell in the worksheet—even the ones that you are not using.
Insert Copied
Cells Between Existing Cells Safely
From Marcia Ferreira, Miami, Florida
If you want to insert a range of
copied cells between other rows or columns—instead of pasting over
them—there's an easy way to do it:
1.
Select the cells you want to copy.
2.
On the Edit menu, click Copy.
3.
Select the area on the worksheet where you want to place the copied
cells.
4.
Press Ctrl + SHIFT + Plus Sign (+).
5.
In the Insert dialog box, click the direction you want to
shift the surrounding cells, and press OK.
Now, the copied cells are inserted right where you want them, and none of your existing information is lost.
Build Vertical Titles in Excel
From Marcia Ferreira, Miami, Florida
Have you ever wondered how to create a
heading for a table that runs vertically along the side of a table instead
of above it?
Here's how I do it:
1.
Select the cell that contains your text as well as the surrounding
cells that you want your title to span.
2.
On the Format menu, click Cells, and then click the
Alignment tab.
3.
In the degrees text box, enter 90.
4.
Select the Merge cells text box and click OK.
Format Excel Cells Fast
From Carol Miller, Trenton, Ontario Canada
If you want quick access to the Format Cells dialog box in Microsoft Excel to change things like type style, alignment, or borders, select the cell you want to format and press CTRL+1.
Have Excel Save Your
Files Automatically
From Chetan Parmar, Hasbrouck Heights, New Jersey
Have you ever wanted Excel to
automatically save your spreadsheets for you so you don't lose your work?
Excel 2000 includes a feature that saves workbooks automatically at
specified intervals, but it's not installed by default. Here's how you can
install and use the Autosave Add-in:
First you need to load the add-in,
which will add it to your Tools menu:
1.
On the Tools menu, click Add-Ins.
2.
In the Add-Ins available list, select the Autosave Add-in
check box and then click OK.
(Editor's Note: If the Autosave
Add-in is not available, you may need to install it. For more instructions,
search for the phrase "Install or remove individual features of Microsoft
Office or Excel" in Excel 2000 Help.)
Then, to configure and use the
Autosave feature:
1.
On the Tools menu, click AutoSave.
2.
Select the Automatic save every check box.
3.
In the Minutes box, enter how often you want Excel to save
your workbooks.
4. Select any other options you want, and press OK.
Quickly Move Between Multiple Excel Workbooks or Worksheets
From N. L. Garg, Faridabad, India
When working with several Excel
workbooks or worksheets (the individual pages in workbooks) at once, you can
quickly move between them using shortcut keys.
§
To move
between open workbooks, press CTRL+TAB.
§
To move
to the next sheet in a workbook, press CTRL+PAGE DOWN.
§ To move to the previous sheet in a workbook, press CTRL+PAGE UP.
Do Fast Calculations in Excel
From Travis L. Reno, Lee's Summit, Missouri
Have you ever needed to know the
largest value in a series of cells? You can create a formula to do that, but
there is a faster way.
To view the largest value in a series
of cells:
1.
Select the cells in which you are interested, and you will see the
sum of the range displayed on the status bar, which is the horizontal area
below the worksheet window.
2.
If the status bar is not displayed, click Status Bar on the
View menu.
3.
Right-click the status bar, and then click Max. Now you can
see the maximum value displayed on the status bar.
You can use the same technique to find the average of, the sum of, or the minimum value in the selected range. You can also count the cells that contain numbers (by selecting Count Nums) or count the number of filled cells (by selecting Count).
Completely
Delete Cells in Excel Using This Shortcut
From Choon Soofen, Singapore
Have you ever wanted a keyboard
shortcut that completely deletes a cell from your worksheet, including the
formatting and comments? Just select the cells you want to delete, and then
press CTRL+MINUS SIGN (–). The surrounding cells will shift to fill the
space.
This is different from using the DELETE or BACKSPACE keys, which simply clears the contents of a cell without actually deleting it.
Copy the
Contents of an Entire Worksheet in Excel
From Bruno Rubio, Fall River, Massachusetts
Here's a timesaving tip for Excel
users who frequently need to copy an entire worksheet (a page within a
workbook) of information—such as a list of items for a monthly
inventory—from one workbook (Excel file) to another.
To copy an entire sheet to another
workbook:
1.
Open the workbook into which you want to paste the copied sheet.
2.
Switch to the workbook that contains the sheet you want to copy.
3.
Right-click the Sheet tab of the sheet you want to copy, and
then click Move or Copy on the shortcut menu.
4.
From the To book drop-down menu, select the workbook that will
receive the sheet. (To copy the selected sheet to a new workbook, click
New book on the drop-down menu.)
5.
Select the Create a copy check box. (If you don't select this
check box, the sheet will be moved instead of copied.)
6. Click OK.
Do Creative
Calculations with Paste Special
From Barry Gibbs, Leeds, England
I find this trick fantastically
useful. If you have a block of numeric data in an Excel worksheet and you
wish to change each entry to a negative value, use Paste Special with
a twist.
Here's how:
1.
In an empty cell, type -1.
2.
Select the cell, and click Copy on the Edit menu.
3.
Now select the cells containing the values you want to change.
4.
On the Edit menu, click Paste Special.
5.
Under Paste, click Values and under Operation,
click Multiply.
6.
Click OK.
All the numbers change from positive to negative, or vice versa. This method is also useful for reducing numbers by a factor of 1,000, 1,000,000, and so on.
Quickly View All
Worksheet Formulas in Excel
From Jason Harper, Ann Arbor, Michigan, and Sara R. Seals, Cleveland,
Ohio
With a quick keystroke, you can
display all the formulas in your worksheet, including the serial values
Excel uses to store dates.
To alternate between displaying cell values and displaying cell formulas, press CTRL+` (single left quotation mark, which usually can be found above the TAB key).
Add a Calculator to the
Excel Toolbar
From Mark Cross, The Villages, Florida
Did you know that you could add a
calculator to your Microsoft Excel toolbar? Here's how:
1.
On the View menu, click Toolbars, and then click
Customize.
2.
Click the Commands tab.
3.
In the Categories list, click Tools, and in the Commands list,
click Custom (the one with the gray calculator graphic).
4.
Drag the selected command from the Commands list to a toolbar. (Lift
your finger from the mouse when you see a plus sign next to your pointer.)
5.
Click Close.
Now click the button you just added to run the calculator.
Paste Information from
Excel as a Picture
From Philip Flint, United Kingdom
Do you want to place an image of an
Excel file into a Word document, image editing program, or other program?
It's easy to do.
1.
On the Excel worksheet or chart sheet, select the cells or click the
chart or object you want to copy.
2.
Hold down SHIFT and click Copy Picture on the Edit
menu.
3.
For best picture quality, make sure As shown on screen and
Picture are selected, and then click OK.
4.
Click the worksheet or other document where you want to paste the
picture.
5.
Click Paste on the Edit menu.
To make adjustments to the image after
you've pasted it, use the Picture toolbar. (To open it, point to Toolbars
on the View menu and click Picture.)
Editor's Note: Cell gridlines appear in the picture if they are displayed. To omit gridlines, in Excel click Options on the Tools menu, click the View tab, and then clear the Gridlines check box.
Protect Cells Using Data
Validation
From Arun Patel, Avenel, New Jersey
Here's a creative way to protect cells
in an Excel worksheet so that other users can't make changes to them:
1.
Select the cells you want to protect. (It's a good idea to make a
note of the cells you protect in case you need to remove that protection
later.)
2.
On the Data menu, click Validation, and then click the
Settings tab.
3.
Set the following restrictions: In the Allow box, click
Text Length; in the Data box, click between; in the
Minimum box, type 10000; and in the Maximum box, type 50000.
4.
Click the Error Alert tab.
5.
Make sure the Show error alert after invalid data is entered
check box is selected. In the Style box, click Stop.
6.
If you want a title to appear in the title bar of the message or in
the Office Assistant balloon if the Office Assistant is displayed, type the
text in the Title box. If you leave the Title box blank, the
title defaults to Microsoft Excel.
7.
If you want to display your own text for the message, type the text
in the Error message box, up to 225 characters. Press ENTER to start
a new line in the message. If you don't enter any text in the Error
message box, the message displays the following: "The value you entered
is not valid. A user has restricted values that can be entered into this
cell."
Excel displays the message only when a
user types data in the cell.
To remove data validation settings, select the protected cells, click Validation on the Data menu, and then click Clear All.
Created Date: 11/09/2001 Last Reviewed: 11/09/2001 Rev. Date: