http://www.zdtips.com -- Your #1 source for software tips and techniques
[ PG@home | PG multiMinda ]
Auto correction of commonly misspelled words and
typed data
Are you a bad typist? Don't be shy--we know there are a few of you out there.
Well, Excel has a feature for you: the Auto Correct feature. To access this feature,
select the Tools | AutoCorrect menu bar option. This will bring up the AutoCorrect dialog
box. Your insertion point will be positioned in the box entitled Replace. Simply type (as
usual) the wrong word in this section. For instance you can type paralell in this box. Now
press [Tab] to go to the box entitled With, and in this box type parallel. As soon as you
enter data into the With box, you'll notice that the Add button becomes available. Click
this button. You're done. You can add all your favorite mistakes so they'll automatically
be corrected for you. If you make a mistake entering the data in either the Replace or
With box, select the entry in the list and click Delete. When you're done making changes
in the AutoCorrect dialog box, click OK to exit.
TOP--^
Inserting a carriage return in the formula bar
If you're entering a long formula or text string, you may want to insert carriage
returns to make everything more readable. Here's how: Just press [Alt][Enter] and your
insertion point moves down to a new line. This works when you're entering in the formula
bar or directly in a cell.
TOP--^
Performing case insensitive comparisons with
StrComp()
When you use Excel's IF worksheet function to compare two strings, the comparison
is case insensitive. For example, assume you have a worksheet with the value John in cell
A1 and JOHN in cell B2. The formula:
=IF(A1=B1,"Same","Different")
returns the result Same.
However, if you try to perform the same comparison in a VBA macro, the IF comparison is
case sensitive. For example, the following macro
Sub Comparison( ) If [A1] = [B1] Then result = MsgBox("SAME", vbOKOnly) Else
result = MsgBox("DIFFERENT", vbOKOnly) End If End Sub
returns the result DIFFERENT. You can make your macro comparisons case insensitive by
using the StrComp( ) function.
Sub Comparison( ) If StrComp([A1], [B1], 1) = 0 Then result = MsgBox("SAME",
vbOKOnly) Else result = MsgBox("DIFFERENT", vbOKOnly) End If End Sub
This function compares two strings and, as long as neither string is Null, returns an
integer from -1 to 1. The 1 argument in the StrComp( ) function forces a textural
comparison. An answer of 0 means the first string is equal to the second string.
From--John E. Curtis
TOP--^
Change the width of multiple columns
If you want all the columns in your worksheet to be the same width, first click
the Select All button, which is the rectangle in the upper-left corner of your worksheet
at the intersection of the row and column headings. Then, move the mouse pointer to the
line that separates any column headings. When the mouse pointer changes to a resizing
tool, click and drag to resize all the columns on your sheet. If you want to resize just a
group of columns, select their column headings and then use the resizing tool. These
techniques work for changing row heights as well.
TOP--^
Date and time shortcuts
Here's a quick way to insert the current date in your worksheet:
[Ctrl];
To insert the current time, press [Ctrl][Shift];
These shortcuts can be performed in the same cell, and even in the middle of a text
string.
From--Steve Jenkins
TOP--^
Changing what cell [Enter] moves to
As the saying goes, old habits are hard to break. If you've spent years navigating through
a program using the [Enter] key, you may be frustrated at the way in which Excel moves
your cell selector. For instance, if you've used a program where the [Enter] key moved
horizontally through cells or fields, you may be disappointed to find that you have to use
the [Tab] key instead because [Enter] moves the cell selector down the column. Or, you may
want [Enter] to simply commit your entry and not move to a new cell at all. Fortunately,
this is easy to configure in Excel. Just open the Tools menu and select Options. Then,
click on the Edit tab. If you don't want the cell selector to move when you press [Enter],
deselect the Move Selection After Enter check box and click OK. Otherwise, make sure Move
Selection After Enter is selected, choose which cell you want to make active from the
Direction dropdown list, and click OK.
TOP--^
Printing the full path and filename on your
worksheet
The request to insert the full pathname into a workbook is one of the most frequently
asked Excel questions. Unfortunately, Excel doesn't make this as straightforward as it
should be. Excel's filename code only inserts the workbook name in your header or footer,
not the full path. You can accomplish this with a macro; however, there's an even easier
way. The caveat is that the first row of your worksheet will need to be repeated on all
pages, so this technique may not be suited for all tasks.
First, select a cell within one of the rows you'll repeat (A1 is a good one to use). Then,
enter the formula
=cell("filename")
Next, select File/Page Setup. Click on the Sheet tab, set the Rows To Repeat At Top to the
appropriate rows you used, and click OK. Save your file and then press [F9]. From now on,
the full pathname will appear at the top of each page of the printed worksheet.
TOP--^
Avoid needlessly opening files
You probably thought the names you chose for your Excel workbooks were incredibly
descriptive six months ago, but you'll most likely find that when you need to find some
specific data you're stuck opening and closing a myriad of files because you can't
remember what's in each workbook. There's an easy way to avoid this time-waster: Save
preview pictures of your workbooks when you save them. This allows you to see a snapshot
of the workbook in Excel's Open dialog box. To use this feature, open the workbook you
want to save a preview picture for. Then, select File/Properties. On the Summary tab,
select the Save Preview Picture check box. Finally, click OK. To see the preview picture
when you're searching through files in the Open or Save As dialog boxes, just click the
Preview icon (it's the second icon from the right at the top of the dialog box). When you
click on worksheets that have saved pictures, you'll see the snapshot in the preview pane.
TOP--^
Select multiple worksheets without a mouse
If you're constantly switching between sheets in an Excel workbook, try these
keyboard shortcuts instead of using your mouse:
[Ctrl][Page Down] moves to the next spreadsheet
[Ctrl][Page Up] moves to the previous spreadsheet
[Ctrl][Shift][Page Up] or [Ctrl][Shift][Page Down] moves and selects spreadsheets.
TOP--^
Pivot Tables - Are they for you?
Do you ever have to sort data in order to determine how many of one thing is in a column?
This is best illustrated by example. For instance, you have a column of people's names
(heading--Names) and they've responded either yes or no to a question, and that y/n data
is in a second column (heading --Responses). Now, you want to determine how many people
answered yes and how many people answered no. Before you count the information yourself,
let me tell you that Excel has a feature just for you--the Pivot Table. Simply click the
Pivot Table Wizard button and let Excel guide you through the four Pivot Table setup
dialog boxes. When you reach the third dialog box, make sure you enter the Count of
Responses in the data field. When you're finished, your results will look similar to that
below, except that the data will be in table form.
Count of RESPONSES
----------------------------------------- RESPONSES Total
----------------------------------------- N 3 Y 5
----------------------------------------- Grand Total 8
No more manual counting those data regions; simply use the Pivot Table Wizard to your
advantage.
TOP--^
Setting Excel's Print Area
Have you ever printed a worksheet that had very little data on it, but that produced lots
of blank pages? Or tried to print a large worksheet and only received output for a small
range? Chances are somebody changed the print area for the sheet. Normally, Excel prints
the range that accommodates all your data; nothing more and nothing less. Selecting a
print area overrides this. To reset Excel to print all your data, select File/Print
Area/Clear Print Area.
If you want to set up a print area so you can consistently print just a specific part of a
worksheet, first select the area you want to print. Then, choose File/Print Area/Set Print
Area. From now on, only the selected range will be printed.
TOP--^
Quickly adjusting column width
You may know that you can easily make your worksheet columns adjust to the width of your
data by selecting the column and choosing Format/Column/AutoFit Selection. However, you
can also do this by double-clicking on the line that separates the column headings. The
column width will automatically expand or shrink to accommodate the longest entry.
TOP--^
Worksheet navigation tips
You may know that you can quickly move your cell selector to the last or first cell in a
filled column by pressing [Ctrl] and the appropriate directional arrow. This also works
for moving to the last or first cell in a filled row ([Ctrl] and the left or right arrow
key). However, a lesser-known feature of Excel is the ability to do this with just your
mouse. All you need to do is double-click on the border of the cell selector. Excel will
move the selector to the last filled cell in the direction corresponding to the edge you
clicked on.
From--Kees Brantjes
TOP--^
Quick access to the toolbars options
If you'd like to add or subtract certain toolbars from your working Excel session,
bringing up your choices is as easy as a single mouse click. Position your mouse pointer
over any open toolbar and right-click your mouse. Poof--the toolbars menu appears, and by
clicking your mouse on any option, you can select (or de-select if it's already checked)
any toolbar option.
TOP--^
*******************************************************************