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

 


QUICK ARITHMETIC IN 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."

Return to Excel with Excel


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.

Return to Excel with Excel


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.

Return to Excel with Excel


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


COPYING VALUES IN 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.

Return to Excel with Excel


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.

Return to Excel with Excel




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.

Return to Excel with Excel




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.

Return to Excel with Excel


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.

Return to Excel with Excel




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.

Return to Excel with Excel




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




Formula settings Shortcut

  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




CONTROLLING EXCEL PRECISION

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

Entertainment Government Technology About Email Me