Welcome to Blaisdell's Little Corner of the Web
| Home | Browsers | MsOffice 97 & 2000 | Site Search | Windows |
Freeware | Freeware From A-Z | Security | Virus Information |
|
Office Index II | Office Index I
|
| This site was Updated on 02/28/2006 | |
Welcome to Excel Archives
Page 1
Also see:
Return to Excel with Excel
If you thought that the only way to handle simple arithmetic in an Excel cell is to add an equal sign in front of the equation (=2 * 2), may I suggest that you change Excel to suit your needs. This is an option users can change. Choose Tools, Options. When the Options dialog box opens, click the Transition tab. Select the Transition Formula Entry check box, then click OK to close the dialog box and apply your settings. Excel will do your calculations automatically when you type something such as 27 * 3 without any equal or plus sign."
GET THE REAL INTEREST RATE WITH EXCEL
The function you need is called Effect.
Suppose you're dealing with a quoted interest rate of 9 percent, compounded monthly for
one year.
What is your actual interest rate? Run Excel and enter
=effect(.09,12)
in cell A1, then press Enter. Excel now displays the actual interest rate of 0.093807,
which is 9.38 percent.
Invoke AutoSave and keep it from asking for imput everytime it saves:
Here's how to invoke AutoSave in the first place.
Run Excel and choose Tools, Add-Ins. When the Add-Ins dialog box opens, select the
AutoSave check box and click OK. At this point, AutoSave starts working and attempts to
save the current worksheet every 10 minutes. It also prompts you before saving.
You need to take the next step.
Choose Tools, AutoSave. When the AutoSave dialog box opens, you can change the time period
between saves, and you can also deselect the Prompt Before Saving check box. Click OK to
close the dialog box and apply your changes.
Jump quickly to sections of your Excel spreadsheet with this
little known or used Excel command.
PROBLEM: You'd like to be able to jump quickly to a variety of places in
Microsoft Excel or even to collections of cells and objects, such as in-cell Comments,
cells with data validation, and so on.
SOLUTION: One of Excel's most powerful but hidden navigation tools is the
Go To Special dialog box. To access it, select Edit, choose Go To, and click on the
Special button. From here you
can select a myriad of Excel objects: Comments, Constants, Formulas, Blanks, Objects,
Conditional Formats, and more.
Return to Excel with Excel
How to make Excel do a backup copy of a Workbook.
You know how to have Microsoft Word automatically create a backup copy of any document
whenever you make changes to that document (check "Always create backup copy"
under
Tools, Options, Save, then click on OK). Excel doesn't seem to have the same feature,
though.
SOLUTION: You have to set the option for each Excel workbook. To set it
before a new workbook has been saved, select File, then choose Save As instead of Save.
Click on the dialog box's Tools button, then click on the General Options button. Check
the "Always create backup" box, click on OK, then Save.
To activate the option for a previously saved workbook, follow the same initial procedure.
When Excel prompts "The file <filename>.xls already exists. Do you want to
replace the existing
file?" click on Yes. Excel then creates a backup copy in the form "Backup of
<filename>.xlk".
Return to Excel with Excel
You would like to copy some data from one group of cells to another. The problem is that you have several formulas in the source group and don't want to copy the formulas. You want a way to copy just the data generated by the formula to another cell.There is a way to do this in Excel: Firest open a blank worksheet and enter 1 2 3 4 5 into cells A1 through A5. Now click in cell A7 and type =sum(a1:a5) Next, select cells A1 through A7. Use the right mouse button to drag the selected cells to their new location. When you release the right mouse button, a pop-up menu appears. From this menu, choose Copy Here As Values Only. The data in cell A7 appears in the new location. The formula does not appear.
FILLING IN MULTIPLE CELLS IN EXCEL
If you need to fill a group of cells with the same information, you can first select the cells you want to use (To select contiguous cells, hold down on the shift key, click on the first cell and then, while holding down the shift key, select the last cell in a row or column) and then type the information into one cell. Press Ctrl-Enter, and the data appears in all the selected cells.
You can also enter the data into groups of noncontiguous cells. To do this, select the first group of cells, then press and hold the Ctrl key while you select other ranges. When you finish selecting all the ranges, type in your data. For this to work, you simply type your data as soon as you finish selecting cells. Don't click a cell first--this will deselect all your cells.
IMPORTING TEXT INTO AN EXCEL WORKSHEET
There are several approaches to this problem. If all your data is in a Word table, you can select the table and press Ctrl-C to copy it to the Clipboard. Now move to a blank worksheet, click the starting cell, and press Ctrl-V. This should place all your data in columns.
If your data is not in a table but is tab defined, you can use the same approach--copy and paste.
If this doesn't work for you, go ahead and import the data just as you have been doing.
Next, select the column that contains the data and choose Data, Text To Columns. Excel
then opens a wizard that will guide you through the rest of the process.
Return to Excel with Excel
A SIMPLE EXCEL CALCULATION
If you want to sum a row of numbers, you can simply hold down the Alt key while you type
in an equal sign (=).
Suppose you enter
1
2
3
4
5
into cells A1 through A5. Then click in cell A7 and press Alt as you type the equal
sign. This enters the Sum formula. Finally, press Enter, and Excel takes care of the sum
for you."
Return to Excel with Excel
How to hide the data in an Excel cell. To test this tip do the following:
Enter
1
2
3
into cells A1 through A3.
Now, in cell A5, type
=sum(a1:a3)
and press Enter.
Now click cell A5 and choose Format, Cells. When the Format Cells dialog box opens, click the Number tab. Select Custom from the Category list. In the Type entry box, delete whatever is there and type three semicolons (;;;).
Click OK to close the dialog box and apply the format.
You no longer see the data in cell A5. However, if you click the cell, the formula appears in the Formula bar.
Similar to this tip, suppose you want to hide several cells. Hold down on your control key while clicking on each cell you want to apply this format to. Perform the above procedure and poof! All the cells you selected are hidden. Try it for yourself.
If you want to unhide the cells, open the format cells dialog box as stated and select Number tab and Select Custom from the Category list. Now just click on the general selection and hit your enter key to unhide the cells.
EXCEL SHORTCUTS FOR MORTALS! taken from Woody's Office for
Mear Mortals Click here to go to Woody's
site to apply for your own online News Letter, OMM build.
Learn a shortcut to work faster in Woody's Office for Mere Mortals. These are the so-called 'simple' shortcuts that many computer instructors assume you know and so never bother mentioning <sigh>. These shortcuts work in Excel 2000, 97, and 95.
- Use Ctrl+Home to make cell A1 the active cell.
- Use Home to make the first cell in the active row (the cell in column A) the active cell. So if you are in row 7, Home will make A7 the active cell.
Use a hyperlink to move around in an Excel workbook or page.
For example, if you wnat to move to a certain cell, then move on to another cell in
another wookbook, hyperlinks work fine. Of course you could manage large wookbooks and
sheets using the. "Paste Special Command".
If you would like to give this a try, open a blank worksheet and enter some data in column A1 through A5. In A7, enter
=sum(a1:a5)
and press Enter. Move to Sheet2 and again enter data in A1 through A5 and then type
=sum(a1:a5)
in cell A7 and press Enter. Next, back in Sheet 1, click A7 and choose Insert, HyperLink. When the dialog box opens, click the Named Location In File (Optional) list box and type
Sheet2!A7
Then click OK to close the dialog box and save your selection. When you click cell A7
in Sheet1, Excel will take you to cell A7 in Sheet2.
Return to Excel with Excel
Use the paste special command in Excel to view accumulated data in a spreadsheet or
workbook.
For example, lets say you have a workbook that has sums located in other workbooks and you want to display those figures on the first page of your workbook for quick reference.
Lets say the workbook you want to find the figures on are in workbook named "Saales Figure for the Month of March 2000" and the information is in cell D16.
Go to the workbook titled, Saales Figure for the Month of March 2000 on cell D16. Right
click on the cell and select copy from the menu. Now go to the cell where you want the
link to appear and select it. Righ click on the cell and select, Paste Special and select,
Paste Link. It is that simple. If you are one who likes to use the keyboard and not the
mouse you can mananage the same techneque by selecting the cell and type, "='Saales
Figure for the Month of March 2000'!$D$16".
Return to Excel with Excel
Set a file to open and save to a particular folder and a particular type of file with Excel
You can tell Excel to open and save files in any folder, and you can choose from a variety of file types. Let's say you want to save the files to a new folder you've created, called My Excel Files.
To change the default settings, run Excel and choose Tools, Options. When the Options dialog box opens, click the General tab. Click the Default File Location entry box and type the name of the new folder you want to use (in our example, that would be My Excel Files).
Now click the Transition tab and click the arrow at the right side of the Save Excel Files As list box. When the list opens, select the file type you want.
After you make all your selections, click OK to close the dialog box and save your changes.
You would like to set the dates in an Excel column to display the entire month name.
Let's say you want to set column A to your date format of February 29, 2000. Click the
A label to select the entire column, then choose Format, Cells. When the Format Cells
dialog box opens, click
the Number tab. Now, in the Category list, click Custom to select it. Under Type, click
General.
Double-click General in the Type entry box and delete it. Enter
mmmm d, yyy
and then click OK to close the dialog box and apply your changes. The four M's should work just fine.
The trick is that you have to use Custom to set up this date format.
Four Excel shortcuts, but only because they are inter-related and make more sense as a
group. These shortcuts will help you find cells that are linked to the current cell
you have selected.
The first two help you select cells that have a bearing on the value in the selected cell. This selects the cells pointed to using Tools | Auditing | Trace Precedents.
CTRL+[ (opening bracket) Select only cells that
are
directly referred to by
formulas in the selection
CTRL+SHIFT+{ (opening brace) Select all cells that are
directly or indirectly
referred to by formulas in the
selection
The second pair of shortcuts do the reverse, they show the cells that need the value in the current cell to work out their result. This selects the same cells pointed to using Tools | Auditing | Trace Dependants.
CTRL+] (closing bracket) Select only cells
with
formulas that refer directly
to the active cell
CTRL+SHIFT+} (closing brace) Select all cells with formulas
that refer directly or
indirectly to the active cell
Return to Excel with Excel
Set a default file location and set Excel to save to a specific type of file by
default?
To answer the first question, you can specify a folder. Run Excel and choose Tools, Options. When the Options dialog box opens, click the General tab. Now click in the entry box labeled Default File Location and enter your new folder's path.
You want to tell Excel to save in Wordpad format. You can't save Excel files in
Wordpad; however, you can save them in text format. Choose Tools, Options and click the
Transition tab when the Options dialog box opens. Now click the arrow at the right side of
the Save Excel Files
As list box and choose a format from the list, which includes several text formats.
After you make your selections, click OK to close the dialog box and save your changes.
Return to Excel with
Excel
STORING MACROS AS WORKBOOKS IN EXCEL
Excel macros get saved in a workbook rather than in a template, as they would in Word. You can save Excel macros in personal.xls.
By default, when you save a macro in Excel it goes into the current workbook. However, you can save any macro in personal.xls and use it with any workbook.
What to do if you don't have a file named personal.xls. To see if you have a copy and it's loading, run Excel and see if Windows, Unhide is available. If it isn't, you don't have personal.xls loaded.
To create a personal.xls, load a blank workbook and choose File, Save As. When the Save As dialog box opens, name the file Personal and locate the folder \Program Files\Microsoft Office\Office\XLSTART. Click the XLSTART folder to select it, then click Save.
Now that you have personal.xls in XLSTART, it will load whenever you run Excel. So you don't see this workbook all the time, run Excel and choose Window, Hide. You can load a workbook, and personal.xls loads invisibly.
When you want to save a macro in personal.xls, choose Window, Unhide, then enter and save your macro. Finally, choose Window, Hide to hide personal.xls again. This makes the new macro available to all workbooks.
Return to Excel with
Excel
Return to top of page
PROBLEM: You'd like to create more than one print area in Excel.
SOLUTION: Unfortunately, the Print Area feature (File Print Area) allows only one print area per worksheet. If you need to designate more than one area for printing, use range names instead. First, select the group of cells you want to print. Then choose Insert, Name, Define and type in a name for that group of cells. Click on OK. Repeat this process for each group you want to print.
When the time comes to print one of the groups, click on the Name Box drop-down button
on the Formula Bar and select the desired range name. Then choose File, Print and click on
Selection
in the Print dialog box (you can't use the Print button on the Standard toolbar). Preview
if desired, then print.
Note: Range names cannot have spaces in them; use an underscore to create a space (West_Region, for example) or initial cap the words (WestRegion).
Return to Excel with
Excel
Return to top of page
Select a whole block of cells in a single
keystroke
Possibly my favorite Excel shortcut lets you select a whole block of cells in a single keystroke. Click on a cell then use the shortcut - the entire region around the cell is selected. The 'region' is bounded by blank rows and columns or the borders of the worksheet.
CTRL+SHIFT+* (asterisk) to select the region around the active cell
Return to Excel with
Excel
Return to top of page
Shortcut Keyboard Navigation with Excel
Ctrl-End always takes you to the last cell in which you entered data.However, you can
still go to the last data cell if you press Ctrl-downarrow to move to the last cell in a
column, then press Ctrl-right
arrow and hold the keys down until the cursor moves beyond your last data cell. Now press
Ctrl-left arrow to jump back to the last cell in the last row.
Return to top of page
Return to Excel with Excel
In Excel it's easy to get confused about what formula settings are required and in what order - there's a handy shortcut that will insert the necessary brackets and commas plus reminders of what each argument should be.
Just type in the function name (like SUM, STDEV etc) then the shortcut key Ctrl + Shift + A and the rest will be filled in.
Return to Excel with
Excel
Return to top of page
We recently suggested that one should round off Excel numbers to avoid problems high-precision calculations may cause. As reader John B. points out, you can set Excel's calculation precision.
If you would like Excel to calculate using the cell format's precision, choose Tools, Options. When the Options dialog box opens, click the Calculation tab. Now select the Precision As Displayed check box and click OK to close the dialog box and save your setting.
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.
Return to Excel with
Excel
Return to top of page
MAKING A CUSTOM FILL LIST IN EXCEL
"I am a high-school teacher, and I need to make up some sheets showing the days of the week in the approved school format. We have to use the following:
S = Sunday
M = Monday
T = Tuesday
W = Wednesday
Th = Thursday
F = Friday
Sa = Saturday
Of course, it isn't all that much trouble to enter the letters, but I was just wondering if Excel can do this automatically."
You can create a new custom list and then enter the S and drag down (or across) six cells to fill in the other entries. Now choose Tools, Options. When the Options dialog box opens, click the Custom Lists tab. Click in the List Entries dialog box and type
S, M, T, W, Th, F, Sa
in the entry box. Click Add, then click OK to close the dialog box and save your new
list.
Let's see how the new list works. Go to cell A1 and type
S
Then press Enter. Select cell A1, grab the small handle at the lower right side of the cell highlight outline, and drag down to cell A7. Cells A1 through A7 should now display
S
M
T
W
Th
F
Sa
How's that for easy?
Return to Excel with
Excel
Return to top of page
Formatting date and time cells in Excel can be done with a
click of the keyboard:
Apply the Date format CTRL+SHIFT+#
Apply the Time format CTRL+SHIFT+@
More shortcuts and tips coming as the need arises, so come back
often. Do you have an Excel solution or shortcut? Share it with us Email
me
Or, if you have a question or would like to know how to do something
in Excel, click
here to send me your question.
Return to Excel with Excel