50 essential Excel tips
Get great Excel tips like these sent directly
to your inbox!
Would you like to become a Microsoft Excel pro? We’ve
got the answer with our Excel TechMail. This message is loaded with valuable
information that can save you time and effort. Below, you’ll find a sample
of what the Excel TechMail has to offer. Get valuable tips on macros,
formulas, formatting workbooks, links to Excel resources, and much more, all
delivered straight to your inbox every weekday. Best of all, it’s absolutely
free.
Sign up for the Excel TechMail today!
EASY DATA-POSTING TO THE WEB.
2
OPEN WEB PAGES RIGHT IN EXCEL.
2
O2K'S DETECT AND REPAIR.
2
USER-LEVEL PREVENTION IS A MAGIC CURE.
3
OPENING MULTIPLE WINDOWS.
3
CREATING CUSTOM LISTS.
3
COPYING STYLES BETWEEN WORKBOOKS.
4
A MACRO THAT PULLS SOURCE DATA FROM CHARTS.
4
UPDATE FOR EXCEL 2000 TEXT EXPORT.
4
CREATING A WORKSPACE IN EXCEL.
4
PRINTING FORMULAS.
5
SHIFTING A SCATTER CHART'S AXIS.
5
RECOVERING DATA WITH THE SYLK FORMAT.
5
FASTER ACCESS TO WORKSHEETS.
6
INSERTING THE FULL FILE PATH IN HEADERS AND FOOTERS.
6
EXCEEDING THE 30-ARGUMENT LIMIT.
6
THE LOCKSMITH: UNLOCKING LOST PASSWORDS ON
PROTECTED FILES.
6
ASK MR. EXCEL.
6
CATCHING DATA ENTRY ERRORS.
7
QUICKLY SETTING A PRINT AREA..
7
COUNTING THE DAYS VIA CELL SUBTRACTION.
7
QUICKLY SHUFFLING TOOLBAR BUTTONS.
7
OPENING A WORKBOOK AT STARTUP.
8
HIDING DUPLICATE RECORDS.
8
CONVERTING JULIAN DATES.
8
SAVING TIME WITH CUSTOM VIEWS.
8
BEWARE OF SHARING DATES BETWEEN EXCEL FOR WINDOWS
AND EXCEL FOR MACS.
9
DON'T USE AUTOFORMAT ON AN ENTIRE WORKSHEET.
9
KEEP TRACK OF CHANGES.
9
CONDITIONAL FORMATTING..
10
DATA ENTRY SOLUTIONS WITH THE TEMPLATE WIZARD.
10
CUSTOMIZING "DIVISION BY 0" ERROR MESSAGES.
10
DEBUGGING YOUR CODE.
11
DEBUGGING USING WATCHES.
11
ENSURE PROPER DATA ENTRY WITH VALIDATION.
11
HYPERLINK TO A SPECIFIC CELL.
12
MACRO SECURITY SETTINGS.
12
EXPRESS YOURSELF WITH COMMENTS.
12
MAP YOUR DATA—ON REAL MAPS.
13
CREATE AN OUTLOOK MESSAGE FROM EXCEL.
13
REPAIRING BROKEN FILE TYPE ASSOCIATIONS.
14
WEB PUBLISHING WIZARD ERROR WITH OFFICE 2000.
14
USE AUDITING TO TROUBLESHOOT.
14
USING GOAL SEEK.
15
USING SHARED WORKBOOKS.
15
USE THE IMMEDIATE WINDOW..
15
SUMMARIZE DATA WITH GROUPING..
16
TAKE A FEW SHORTCUTS.
16
ANALYZING EXTERNAL DATA IN EXCEL.
16
USING PERSONAL MACRO WORKBOOKS.
17
If your organization is using an intranet to share
public data—and whose organization isn't these days—your users will want an
easy way to post this information. Excel 97 offers an easy, wizard-driven
system to walk even the most apprehensive users through this process.
1.
Select the cell range that contains the data to be published.
2.
Select Save As HTML from the File menu.
3.
Answer the questions posed by the Internet Assistant Wizard.
The wizard walks users through four steps that define
how information will be converted for Web display. Various options include:
·
Inserting the resulting HTML tables into an existing HTML
page, or creating an entirely new page.
·
Specifying format options, such as rules between data.
·
Saving your new HTML page as a freestanding file or inserting
it directly into a FrontPage Web.
If you or your users need to analyze data found on a
Web site, you might be in for a bunch of tedious retyping. Under the right
circumstances, however, Excel 97 can save you a lot of time by letting you
open an HTML page in Excel directly from the Web.
The procedure is just a slight modification of the
standard file-opening procedure:
1.
Choose Open from the File menu.
2.
In the File Name text box, type the URL of the Web file you want to
open. NOTE: Be sure to include the prefix http://.
3.
In the Files Of Type drop-down menu, select HTML Documents (*.html,
*.htm).
4.
Click Open.
Excel opens HTML pages based on the HTML table
structure of the page, so some Web design treatments may lead to some weird
results in Excel. For pages that cleanly present data in a table format,
however, you'll find this approach a real time-saver.
Microsoft Office 2000 programs, including Excel 2000,
contain a command line called Detect And Repair, which checks and fixes
problems with Office program files, DLLs, and Registry settings. (This
utility does not affect worksheets or other document files.)
Detect And Repair, which you access as a command-line
option of the Setup utility, performs the following actions:
·
Reinstalls Office .exe and .dll files if missing, out-of-date,
or corrupt
·
Reinstalls all Windows Installer shortcuts, overwriting any
existing shortcuts
·
Rewrites all required local machine registry values
·
Rewrites all required user registry values
You can run Detect And Repair from within Office, but
that won't do much good if a serious error is preventing your user from even
launching an Office application. Fortunately, you can run the utility from
the Run dialog box with the following entry:
drive:\setup.exe /focums install.msi
Every support pro knows that most of their problems
could be avoided if their users would just follow a few simple, commonsense
precautions. If you keep getting calls on corrupted Excel documents, pass
around this list of Microsoft-endorsed tips to help users keep their data
safe and your days a little less stressful.
·
Move radios, fax machines, televisions, stereos, speakers, and
other sources of electromagnetic radiation away from computers.
·
If your users are still using floppies, make sure they are not
putting them on top of their monitors.
·
Regularly run Norton AntiVirus or another utility to scan for
viruses.
·
Store floppy disks in a disk storage container.
·
Don't be a slob—prevent dust from building up around your
computer.
·
And, most important, exit Windows before restarting or
shutting down a client system.
One of the most frustrating problems for Excel users is
viewing all the information they need, especially because it often lives in
multiple notebooks. Let your users know about this simple way to view
multiple worksheets, and they'll love you forever.
1.
Click on the tab of the worksheet you want.
2.
Choose New Window from the Window menu.
3.
Repeat this process for each of the worksheets you want to display.
(Excel opens each sheet in a window that sits on top of the previous one.)
4.
To see them all at one time, choose Arrange from the Window menu.
5.
Select Tiled, Horizontal, or Vertical.
6.
Select the Windows Of Active Workbook check box.
7.
Click OK.
Here's another timesaving tip to pass on to your users.
If your users often make use of the same lists of data, you can teach them
to create a custom list instead of typing the same information repeatedly.
If the data you want to copy is already in a worksheet,
follow these steps:
1.
Select all of the cells containing the information you want to copy.
2.
Go to the Tools menu.
3.
Select Options.
4.
Click the Custom Lists tab.
5.
Click Import.
If you want to create a list from scratch, follow these
steps:
1.
Go to the Tools menu.
2.
Click Custom Lists.
3.
Click in the List Entries box.
4.
Type each item for your list, hitting [Enter] after each item.
5.
When you're finished, click OK to create the list.
To use a custom list, type the first entry of the list.
Use the fill handle at the bottom right corner of the cell to drag the whole
list across adjacent cells.
Creating formatting styles is a real timesaving trick
for users. So why should they waste time recreating these styles in multiple
workbooks? If they have formatted a workbook in a particularly useful way
and want to continue to use the format in other workbooks, teach them how to
copy the style from one workbook to another.
1.
Open the source workbook and the destination workbook.
2.
Making sure that the destination workbook is active, go to the Format
menu and choose Style.
3.
Click the Merge button.
4.
Within the Merge Styles dialog box, select the source workbook from
the Merge Styles From list box.
5.
Click OK.
When building charts in Excel, many users reference
information in other workbooks to keep spreadsheets fairly streamlined. But
what do you do if a user complains that the data source has become corrupted
or that the file is no longer available in the network?
It's a little bit of a headache to read, but Knowledge
Base article Q137016 includes the full contents of a macro that can strip
important data from the graphic elements of a chart.
http://support.microsoft.com/support/kb/ARTICLES/Q137/0/16.asp
After creating the simple VBA macro, you only need to
run it and drop the information into a worksheet called ChartData. Don't go
volunteering this information to all of your users, but if a VP loses an
important sales spreadsheet, you can pull out this nifty power trick and
save the day.
Microsoft has released a patch that fixes a formatting
problem when exporting Excel 2000 files to .txt documents. On machines
running a Microsoft OS earlier than Windows 2000, four-digit year entries
are automatically truncated to two-digit entries, regardless of formatting
applied within Excel.
This behavior occurs only when users employ a macro or
procedure to export to a text file, not during manual saves to a text file
format. If your users are using Visual Basic for Applications to export
files to .txt, .prn, .csv, or .dif formats (and you are not using Windows
2000), you need to install an update released by Microsoft. For more
information, read the Microsoft Knowledge Base article Q247796, XL2000:
Programmatically Exporting Text File Truncates Dates.
http://www.officeupdate.com/2000/downloadDetails/xl9p2pkg.htm
http://support.microsoft.com/support/kb/articles/Q247/7/96.asp
Often, your users will be working with several
workbooks at a time. They will be happy to know that they can arrange the
workbooks once, save them as a workspace, and then open everything at once
in the viewing configuration they prefer. Here's how:
1.
With all the workbooks in the preferred positions, go to the File
menu.
2.
Choose Save Workspace.
3.
Type a name for the file.
4.
Click Save.
The next time they want to work with those workbooks,
they can go to File | Open and select their workspace.
If your users have invested time in writing numerous
formulas, they may want to print them for safekeeping. Although Excel
doesn't include a simple "Print Formulas" button, functionality is built in.
Here's how to find it:
1.
Go to the Tools menu.
2.
Click Options.
3.
Within the section called Window Options on the View tab, select
Formulas.
4.
Click OK.
There is also a keyboard shortcut for this function.
Press [Ctrl][~].
In both cases, the result is that the formulas, rather
than their results, will appear in the worksheet.
In most cases, Excel's default behavior of making the
x-axis and y-axis of scatter charts intersect at 0 is desirable. In some
cases, however, you or your users may want to change that intersection
point. An obvious example is if you'd like to highlight just a selected
number of years of data but don't want to create an entirely new chart.
To reset an axis, follow these steps:
1.
Activate the chart for editing.
2.
Click to select the axis you want to modify.
3.
Choose Selected Axis from the Format menu.
4.
Select the Scale tab.
5.
Type a number in the Value (X or Y) Axis Crosses At text box.
6.
Click OK.
If a user complains of being unable to save a workbook,
the problem is usually user error. Things do go wrong, however. If you've
tried all the commonsense remedies and the worksheet does seem to be
corrupted, you can try to revert to the old Symbolic Link (SYLK) format.
This approach will let your users recover most of their
essential data by stripping away the more complex information that tends to
be the source of corruption. Unfortunately, this trick won't help them get
back any data, including charts and graphics, that wasn't supported back in
the days of Excel 2.0.
Follow these easy steps in Excel 97:
1.
Open the file that appears to be corrupted.
2.
Select Save As from the File menu.
3.
In the Save As Type list, select SYLK (Symbolic Link).
4.
Click Save.
5.
Click OK in the warning dialog box that appears when you save in SYLK
format.
6.
Close the file.
7.
Click No to move on past the warning that the file you are closing is
not in the normal Microsoft Excel format. (Clicking Yes will resave the file
in the normal Microsoft Excel format.)
8.
Select Open from the File menu.
9.
Select All Files (*.*) in the Files Of Type drop-down list.
10.
Select the SYLK file you saved in step 3 and click Open.
11.
Select Save As from the File menu.
12.
Select Microsoft Excel Workbook (*.xls) in the Save As Type drop-down
list.
13.
Click Save.
For additional information, check out Microsoft
Knowledge Base article Q45557.
http://support.microsoft.com/support/kb/ARTICLES/Q45/5/57.asp
When working with large workbooks in which all of the
worksheet tabs are not visible, your users probably get frustrated with
scrolling to find the sheet they need. Here's a really quick tip to pass on.
Right-click on any of the tab-scrolling buttons (to the left of the sheet
tabs), and a shortcut menu will pop up. You can then select the tab you
want.
Excel 97 doesn't offer you the option of displaying the
full file path in worksheet headers and footers. With a little basic VBA
editing, you can add this functionality to users' systems.
1.
Go to Tools | Macro | Visual Basic Editor.
2.
Go to the View menu and select Code.
3.
Type the following:
Sub PathFooter()
ActiveSheet.PageSetup
LeftFooter=ActiveWorkbook
FullName
End sub
4.
Go to File | Close.
Return to your workbook and save the file. You can now
run your macro by navigating to Macros and choosing the one named PathFooter.
Excel has a 30-argument limit for statistical
functions. It's easy to get around, though, if you group some of your
arguments within parentheses.
Instead of entering AVG(A1,A2,A3…,A33), you can enter
AVG((A1,A2,A3)A4…,A33), and Excel will accept the grouped arguments as a
single argument within the formula.
Microsoft Word and Excel allow you to password-protect
data files. Here's how to unlock files with long-lost passwords, as well as
files unintentionally or maliciously password-protected.
http://www.techrepublic.com/article.jhtml?id=r00219990721eje06.htm
Excel power users and support pros: Check out
MrExcel.com. Mr. Excel answers one reader's question per week, according to
what piques his interest, often including full macros to be copied and put
to use.
Because this is a side project for the site's operator,
he can't answer every question submitted, but he has provided a message
board for other Excel users to exchange information. Other site features
include past week's tips, quick answers, book reviews, and the opportunity
to find out whether you can answer the monthly challenge.
If you work extensively with Excel and Visual Basic,
this may be one of the resources you've been looking for!
http://www.mrexcel.com
It's a fact: Shared workbooks generate more errors than
workbooks with only one owner. Here's one way to flag errors as they occur:
1.
Select all cells in which data will be entered.
2.
Go to Format | Conditional Formatting.
3.
In the Conditional Formatting dialog box, specify which Cell Values
to flag.
4.
Click Format.
5.
In the Format Cells dialog box, click the Patterns tab and select a
color to signal errors.
6.
Click OK.
Now, when someone enters an error, the error will be
flagged with your selected color.
Your users probably waste a lot of time and paper
sending entire worksheets to the printer when all they need is a printout of
a few columns or rows. Teach them this quick trick to set specific print
areas within the Print Preview mode.
1.
Select View | Page Break Preview.
2.
Select the cells you want to print.
3.
Right-click the selection and choose Set Print Area from the shortcut
menu.
4.
Click Print.
By default, Excel 97 adjusts the display of dates to
friendly displays that users can identify. But behind the scenes, Excel
still thinks in numbers. So, finding the number of days between two days is
as easy as simply subtracting one cell that contains a date, regardless of
format, from another date-carrying cell.
For example, let's suppose a user has entered the date
2/20/2000 in cell A1 and 3/10/2000 in cell A2. The formula A2-A1 will return
the result 19 (remember, this is a leap year). NOTE: You must use the dates
that fall later in the calendar year as the first argument of the equation,
or the equation will return an error.
By default, Excel will format the result of a formula
in the same format as the source cells, so in our example it reports that
the difference between 2/20/2000 and 3/10/2000 is 1/19/2000. However, simply
right-clicking the formula cell, selecting Format Cells, and selecting a
Number format will fix this problem.
Here's a quick tip that you can pass on to your users
who like to modify their Excel toolbars.
Instead of right-clicking a toolbar and selecting
Customize to launch the Customize dialog box, users can simply press the
[Alt] key and click and drag a button to any toolbar they want. Of course,
both the Source and Target toolbars must be visible for this procedure to be
effective.
One quick word of warning: This technique moves
buttons, so they will no longer be on the default toolbar. If users want to
copy a button to a new toolbar while retaining its position on the original
one, they can hold down [Ctrl][Alt] while clicking and dragging the button.
If some of your users spend much of their day working
in a specific workbook, you can teach them how to designate that workbook to
open each time Excel launches. All they need to do is place the workbook (or
a shortcut to the workbook) in the XLStart folder. This technique also works
with network shortcuts.
You'll find the XLStart folder in the same place as the
Excel program files—typically C:\Program Files\Microsoft Office\Office.
After placing the file or shortcut in the XLStart folder, the user can just
restart Excel to see the file pop open automatically.
One of the most common mistakes users make in preparing
a report or sorting data is including redundant entries. For example, if a
worksheet keeps a running tally of purchases made by Customer 1, there's no
need to report cells other than the ones keeping the composite figures. A
built-in filter in Excel 97 will take care of this problem.
Follow these steps:
1.
Choose Data | Filter | Advanced Filter.
2.
In the Advanced Filter dialog box, drag across the worksheet to
select the lists containing duplicate entries.
3.
Select Unique Records Only.
4.
Click OK.
Excel will now hide any duplicate records in the
selected range.
Here's a little piece of post-Y2K calendar trivia for
you. Since the 1950s, astronomers and geophysicists have used Julian dates
to track time in continuous, absolute terms, without bothersome seasons,
leap years, or man-made conventions. Julian dates associate a date with the
number of days elapsed since January 1 of the same year. (For example, Jan.
2, 2000, has the value of 2.)
Excel doesn't have a built-in option to convert dates
to Julian time, but you can use this shortcut to convert today's date to the
Julian format.
Just type this equation into any cell in your
worksheet:
=VALUE(RIGHT(YEAR(TODAY()),2)&TEXT(TODAY()-DATE(YEAR(TODAY())-1,12,31),"000"))
Now, you'll need to format this value to display five
digits.
1.
Right-click the cell that contains the value and select Format Cells.
2.
On the Number tab, choose Custom from the Category list box.
3.
In the Type text box, enter 00000.
4.
Click OK.
Custom Views is a tool in Excel that lets you assign a
name to a particular sheet layout so you can recall it for later viewing.
You can access it by selecting View | Custom Views. In the Custom Views
dialog box, you'll see a listing of your saved views. You can add or delete
views. When adding a view, you have the option of including print settings,
hidden rows and columns, and filter settings. Creating a new view even
remembers where the active cell was when the view was saved, so when you
open that view, it will ”jump” to the exact spot in your book that
corresponds to that view.
Making good use of custom views can save a great deal
of time. For example, you can eliminate repetitive hiding, resizing,
filtering, and other changes for producing various printed reports. Each set
of options can be saved as a view. Then, all you need to do is apply the
view before you print.
Excel supports two different data systems: 1900 and
1904. Early Macintoshes did not support dates before Jan. 1, 1904. For this
reason, Excel for Macs renders entered dates as the number of days since
1/1/1904. Excel for Windows, on the other hand, supports dates starting Jan.
1, 1900, and so renders dates as the number of days since 1/1/1900.
For example, the date 1/1/1904 is represented as a 0 in
the 1904 date system. The same date in the 1900 date system is represented
by the number 1,462. This is because 1/1/1904 is 1,462 days since 1/1/1900,
the day the 1900 date system begins its "counting."
What all this means to you is that if you have users in
both Macs and PCs using Excel and copying data between workbooks, there's
the chance of incorrect date representation.
One solution is to set your PCs so that they all use
the 1904 date system. Then they'll be compatible with the Macs. To do so,
open a workbook and select Tools | Options. Click the Calculation tab. Under
Workbook Option, select the 1904 Date System check box and click OK, or
check out Microsoft's Knowledge Base article Q214330, which covers this
issue in-depth.
http://support.microsoft.com/support/kb/articles/Q214/3/30.asp
Microsoft has confirmed that there's a potential
problem when AutoFormat is selected for a whole worksheet rather than a
specific range.
Depending on your OS and specific configuration, you
could see symptoms ranging from Excel not responding to various error
messages concerning page faults, low virtual memory, and access violations.
To resolve this issue, make sure you select only the
particular range of cells you want formatted when you select Format |
AutoFormat.
For more information, check out Microsoft Knowledge
Base article Q211478.
http://support.microsoft.com/support/kb/articles/Q211/4/78.ASP
Track Changes works hand-in-hand with workbook sharing.
It keeps a log of all changes to a workbook. It tracks cell changes,
worksheet additions and deletions, column changes, and other types of
changes as well as the date, time, and name of the user who made the change.
When data is changed, it keeps a record of the old and new values and the
exact sheet and cell where the edit was made.
One of the best parts of this tool is the ability to
have Excel make a list of the changes in a separate worksheet. When this
option is selected, Excel builds a listing of each change and even turns on
AutoFilter to make finding specific changes easy. It also allows you to
review each change and either accept or reject it. Rejecting a change
restores the original value.
To turn on this tool, just select Tools | Track Changes
| Highlight Changes. The Highlight Changes dialog box provides options such
as tracking who makes the change, when the change is made, highlighting the
change on-screen, and listing the changes.
When using this feature, you might like to see the
Summary sheet when you open your workbook. Excel removes it when you save
the sheet, but you can use this macro to have Excel quickly rebuild it.
Sub ViewChangeSummary()
With ActiveWorkbook
HighlightChangesOptions When:=xlAllChanges
ListChangesOnNewSheet = True
HighlightChangesOnScreen = True
End With
End Sub
Conditional formatting is one of Excel's better
features. It allows you to preset certain font styles, colors, and
cell-background colors based on cell values. This can be very useful for
highlighting important information and values outside an accepted range or
providing a visual cue to associate value ranges with color codes.
The best part is that conditional formatting is very
easy to set up. Just click the cells you'd like to format and select Format
| Conditional Formatting. The Conditional Formatting dialog box lets you set
up the conditions by which the formatting of the cell will occur. You pick
the operator (between, equal to, less than, etc.) and the value or range of
values. Click Format to open the Format Cells dialog box, where you can
select the colors and styles to be used.
Each cell can have several conditional formats. For
example, you might say that if a certain cell's value is between 20 and 50,
the text should be blue on a yellow background. But you can format that same
cell to exhibit red, bolded text on a green background if it contains a
value between 51 and 100.
One of the hardest parts of gathering data from users
is creating the right collection application. The data collection front end
is often a series of forms in Access or ASP pages. However, the setup and
security issues associated with these methods are sometimes too much to deal
with for smaller data collection needs.
Excel 2000 ships with an add-in that can help this
situation. The Template Wizard with the Data Tracking add-in will help you
create an Excel template that will accept data from users in a familiar
environment and then transfer the data to an Access database after it's
saved. The wizard will walk you through the steps of defining the tables
where the data should be inserted.
This is a great solution for such things as purchase
order requests, invoices, or expense forms. Your users would enter their
data in Excel, and the data structures set up by the Wizard would place that
data into the Access database. The data would be collected into one
repository for easy reporting and analysis.
The Wizard is easy to follow, but Microsoft Knowledge
Base article Q214246 explains the process in detail.
http://support.microsoft.com/support/kb/articles/Q214/2/46.ASP
When using complex worksheets, you may have encountered
that annoying "#DIV/0!" error when the divisor of your formula is a zero.
Using the built-in IF function, you can create your own message for display
when you divide by zero.
=IF (DIVISOR = 0,"Your Text", DIVIDED/DIVISOR)
The IF function evaluates the first parameter (DIVISOR
= 0). If it's TRUE, then it places the second parameter ("Your Text") in the
cell. If it's FALSE, it places the third parameter (DIVIDED/DIVISOR) in the
cell.
This helps you control the message that's displayed
when your formula produces a zero value that is then used to divide another
value.
A great tool for finding errors in your code is the
ability to "step" through your code one line at a time. Using the Step Into
button on the Debug toolbar in the VBA Editor, you can execute your code one
statement at a time.
To start your macro, just click Step Into on the
toolbar, and it will work through each individual statement. This allows you
to take advantage of some of the other debug tools such as the Locals and
the Watched windows.
While stepping through your code, you can also use the
Step Out command to run the remaining code as normal. So even if you stepped
through half the macro one statement at a time, you could then run the last
half normally by clicking Step Out.
You've just spent over an hour writing just the right
macro. It's the one that will make your life easier, and you just got it
right—or so you thought. But now it's providing the wrong data output, and
you can't figure out why. All the code looks right.
One of the great debug tools in the VBA Editor is
called Watches. Watches allow you to see the values of the variables in your
macro at any given time. You can use them with the Step commands to walk
though your code. These values can be a big help in finding out why your
macro is generating the wrong data. You can use the watches to see your
variable values and then compare them to what they would be if your macro
were working correctly.
To access the Watch window, just click View | Watch
Window in the VBA Editor. To add a watch, right-click a variable and select
Add Watch from the shortcut menu. This brings up a dialog box that gives you
options about the new watch.
Data validation rules in Excel automatically check an
entry made by a user against rules you have preset in the worksheet. This is
very helpful when you have formulas that depend on certain expected input
from the user. These formulas can often return errors if the format of the
entered data is wrong.
To set data validation rules, select Data | Validation.
The dialog box allows you to pick a data type to require and then a range of
values to accept. On the Input Message tab, you can choose to display an
instructional message when the cell is selected. This message appears in a
pop-up, note-style display. It can be used to provide users with information
about what data you expect them to enter. You can also provide a message
from the Error Alert tab if the data entered does not meet your rules.
Don't assume your users will enter the data you expect.
If your worksheet functionality depends upon consistent data entry, then
look into using data validation.
Did you know that you can create a hyperlink from a Web
page on your intranet or the Internet to a particular sheet, range, and cell
in an Excel workbook? For example:
<a href="http://yourwebserver/hyperlinktesting
.xls#Sheet1!A1">Link Test </a>
This hyperlink will open your file called
Hyperlinktesting.xls and then select cell A1 on Sheet1.
Using this kind of linking, you can specify exactly
where your user will enter the workbook. Users' views of the workbook won't
be bound by the state at the last save.
This can be helpful if you insert the link to a Web
page that asks the user to enter data in a particular cell or area of a
sheet. You can select the cell for them when they click the link.
For more information on linking to worksheets, see
Microsoft Knowledge Base article Q211693.
http://support.microsoft.com/support/kb/articles/q211/6/93.asp
If you write macros that other people in your
organization use, you should be aware of the Macro Security settings in
Excel (and in all Office applications). These settings control the messages
you receive when you open a workbook that contains macros; they ask if it's
OK to enable the macros.
In the "good old days," a developer didn't have to
worry about such things. But with the advent of macro viruses, you now need
to take such user-level security settings into account when developing code.
In Office 2000, there are three levels of security for
macros contained inside of files.
1.
High: This setting automatically disables any macro not "signed" with
a digital signature. It will only allow macros to be enabled from sources
already deemed to be "trusted."
2.
Medium: With this setting, users are prompted to decide if a macro
should be enabled.
3.
Low: This setting does not notify users that files contain macros.
All macros are enabled for opened files.
The security settings on your users' machines might be
dictated by policy or may be up to the user's discretion. Either way, the
safest action for a developer of Office-based macros is to obtain a digital
signature to sign macros with. With a digitally signed macro, even the High
setting will enable your code, provided the user approves.
We've all opened the workbook from hell. The formatting
was a mess, and there were formulas everywhere. A day later you almost have
it decoded enough to work on it.
Get your users into the habit of documenting workbooks
with comments. They can enter text descriptions into any cell. These
descriptions are only visible if you hover the mouse over the cell. A cell
with a comment is marked with a small red triangle in the upper right corner
of the cell. Your users can easily add a comment to a cell by clicking
Insert | Comment.
Excel has a Reviewing toolbar that facilitates working
with the comments in a workbook. The options on this toolbar allow users to
add and delete comments and move from one comment to another in a workbook.
With the growing use of teams and collaboration, it's
more important than ever to ensure your users' intentions are clearly
communicated to everyone who needs to use a workbook. Comments are a good
way to start.
If you often analyze geographically based data in
Excel, you should check out Microsoft MapPoint 2000. MapPoint lets you
create customized maps, and you can arrange your data on a map to suit your
needs. For example, MapPoint can show average household income demographic
data (which is built into MapPoint) and then show your company sales data
for the same regions. This can help you see trends in how your organization
sells across demographic groups. You can then save these maps, annotate
them, and use them later.
If your organization tracks anything by region, city,
etc., then take a look at MapPoint. It's also a great general-purpose
mapping application that will let you create maps for your Windows CE
devices using Microsoft Pocket Streets.
http://www.microsoft.com/office/mappoint/
The ability to create objects from other Office
applications is one of the great things about using VBA. For example, you
can use a macro that will create an Outlook message object and attach a
shortcut to the currently open workbook. There is a button on the toolbar
that will perform this same action, which is fine if that's all you want to
do. But using the sample macro below, you can add other text to the message,
pre-address the message, or perform other actions. You can also tie the
creation of the message to some other user action. For example, you could
automatically send a message to certain people if the user saves the
workbook and if the values of certain cells match criteria you set—all
without any user interaction. This sample macro will get you started in the
right direction.
Sub CreateOutlookMail()
Dim olApp As
Outlook.Application
Dim olMailMessage As
Outlook.MailItem
' Create new instance of
Outlook or open current instance.
Set olApp = New
Outlook.Application
' Create new message.
Set olMailMessage =
olApp.CreateItem(olMailItem)
With olMailMessage
.Subject = "Testing mail
by Automation"
.Body = "This message was
created by VBA code running Outlook through Automation." _
& Chr(13)
.Attachments.Add
Application.ActiveWorkbook.Path & "\" & _
Application.ActiveWorkbook.Name, olByReference
.Display
End With
Set olMailMessage =
Nothing
olApp.Quit
Set olApp = Nothing
End Sub
How many times has one of your users mysteriously lost
his or her file associations? How many times have you gone to the File Types
tab and re-associated Excel (or Word and PowerPoint for that matter) to the
.xls and .xlt file types?
There's a faster way to fix Office file type
associations, in one quick step. Using the /regserver command-line switch
will make Excel reregister itself and rewrite all its registry entries,
including its file type associations. You can reset all your users' file
extensions for Excel, Word, PowerPoint, and Access using this same method.
From the command line, type C:\Program Files\Microsoft
Office\Office\Excel.exe /regserver. You can also modify a shortcut to Excel
by adding /regserver after the end quotes in the Target field of the
Shortcut tab on the shortcut's Properties sheet.
When trying to run the Setup utility after your initial
Office 2000 setup, you may receive the following error:
Insert Disk
The file 'Crswpp.dll' on Web Publishing Wizard disk
cannot be found.
Insert Web Publishing Wizard disk in the selected
drive, and click OK.
Further efforts to run Setup will produce similar
errors.
Microsoft has identified the problem as being connected
to the installation of some modem hardware. But typically, clicking Cancel
when you receive this error message will prompt Setup to run successfully
with no harmful effects.
For more information about all the causal factors,
including a complete description of the problematic modem hardware, and
possible resolutions, check out Microsoft Knowledge Base article Q234688.
http://support.microsoft.com/support/kb/articles/Q234/6/88.asp
We've all clicked a cell to see the formula, wanting to
find out where the result came from, only to find a formula that went on
forever! It has everything: nested functions inside of conditionals, pulling
data from cells all over the workbook. Just when you think you'll never
figure this monster out, you remember Excel's Auditing features.
Select the cell containing the complex formula, click
Tools | Auditing | Trace Precedents, and Excel will draw blue arrows from
every cell the formula references to the cell containing the formula. Now
you can see where that data is coming from.
Auditing also allows you to select a cell containing a
value and choose Trace Dependants, which will draw arrows from the cell to
all the cells containing formulas that reference that value.
And if you have a cell that shows an error value, you
can select it and choose Trace Error, and Excel will show you all the cells
that contribute to the error.
You have a worksheet that has the price of a house,
closing costs, and interest rate, and it calculates your monthly payment. It
works great, but now you want to be able to enter a desirable payment and
find a house price you can afford in order to get that payment.
This is a common situation when dealing with
spreadsheets (although usually more complex than a simple mortgage formula),
especially when dealing with projections or forecasts. Goal Seek is just
what you need for this situation. It allows you to specify a target value
for a cell and tell Excel to change the value of another related cell in
order to obtain this value.
Highlight the cell you wish to change and click Tools |
Goal Seek. The cell reference for the selected cell will appear in the Set
Cell field. Choose a target value and enter it in the To Value field, then
pick which other cell should be changed in order to meet your target. When
you click OK, Excel will attempt to meet your target.
If you have several users who need to work from the
same workbook at the same time, familiarize yourself with Excel's Shared
Workbook feature. This feature can be a lifesaver for workbooks that must be
updated by multiple users.
To use this feature, select Tools | Share Workbook, and
Excel will allow multiple users to edit the workbook. You can set options
that control when changes are updated and how to handle conflicting edits by
different users. You can choose to save updates when a user clicks Save, or
you can specify a time interval for Excel to automatically save user
updates. Also, in the Share Workbook dialog box, you can decide how changes
should be logged by the Track Changes feature, which integrates with
workbook sharing. Then you can see exactly what changes were made to cells,
when sheets were added or deleted, how conflicting edits were resolved, etc.
Users can also see the names of all the other users who have the workbook
open for editing.
One of the tools provided for you in the VBA Editor is
the Immediate Window. You can use it as a general-purpose scratch pad while
testing your code. For example, you can place "print" commands at various
places in your code to output variable values or the results of calculations
to this window. For example:
For Count = 1 to 20
Debug.Print Count
Next Count
This code prints the numbers 1 through 20 to the
Immediate Window.
While using the Step Into commands to debug your code,
you can use the Immediate Window to check or change variable values or
object properties to test the results on your running code. You can also
call other procedures from this window. For example:
Debug.Print A * 125
This multiplies the current value of the variable "A"
by 125 and prints the results in the window.
Activesheet.Name = "New Name"
This changes the name of the active sheet.
The Immediate Window is an excellent way to test the
outcome of new values or property changes on your code without inserting the
new code into your procedure.
Grouping is a feature in Excel 97 and 2000 that allows
you to create outline structures with your data. For example, you have a
column of numbers, and at the bottom there's a cell that contains the sum of
these numbers. You could just leave it at that and have other worksheet
users view the entire column every time they open the workbook. Or, you
could use Grouping to hide the numbers, showing only the sum, but providing
the option of expanding the outline to see the hidden data.
Grouping adds a small column to the left of your data
with plus and minus symbols to let you expand or hide the detailed data. To
build this outline, select the columns of data that will be hidden in your
outline and choose Data | Group And Outline | Group.
Excel will add the expand/collapse symbol to the left
of your data. Your users can now choose whether to see the source data for
the calculations or just the totals.
You can also choose whether the outline should assume
the Totals row is above or below the data. And you can summarize rows in the
same way, so an outline would expand or collapse across rows instead of
columns.
Who doesn't need to save a little time here and there?
Keyboard shortcuts can help you do just that. Here's a list of shortcuts for
Excel that will make life a little easier:
·
[Ctrl][Shift]A: Use this combination after typing a function
name, and it will automatically enter the parentheses and the argument names
for the function you typed.
·
[Alt][F8]: Displays the Macro dialog box.
·
[Alt][F11]: Opens the VBA Editor or switches to it if it's
already open.
·
[Ctrl]1: Displays the Format Cells dialog box.
·
[Ctrl][Shift][~]: Applies the General format.
·
[Ctrl][Shift][$]: Formats the current cells as Currency.
·
[Ctrl][Shift][#]: Applies the Date format.
·
[Shift][F10]: Displays the shortcut menu that applies to where
the cursor is located.
·
[F6]: Moves to the next pane in a split worksheet.
·
[Shift][F6]: Moves to the previous pane.
·
[Ctrl][F6]: Moves to the next workbook window.
·
[Ctrl][Shift][F6]: Moves back to the last workbook window.
·
[F3]: Opens a dialog box to paste a Named Range into a
formula.
·
[Ctrl][F3]: Opens the Define Name dialog box.
·
[Ctrl][Shift][F3]: Automatically creates Named Ranges from the
headers for the selected table of data with row or column headers.
·
[Shift][F3]: Inserts an equal sign (=) and opens the Paste
Function dialog box to help you pick a formula.
·
[Alt][=]: Inserts the AutoSum function.
·
[Ctrl][;]: Inserts the current date.
Your users might not know that they can use Excel to
analyze data from the various databases in your organization. They can use
the powerful analytical tools of Excel on data they previously only viewed
or edited within the database user interface. Using these features, they can
pull data from your customer database, sales tracking database, product
catalog, and HR systems into various sheets in a workbook and perform
complex PivotTable analysis not possible using the separate applications.
This ability is provided by the use of Microsoft Query,
an Office application that lives "behind" Excel. You can access Microsoft
Query by clicking Data | Get External Data. You'll find three options: Run
Web Query, Run Database Query, or Create New Query. The Query interface is
very similar to the Design view of a query in Access. Point Query at a
database, and it will allow you to build complex relational queries to get
the data you need. Running the query will pull the data returned into your
worksheet. Now you can build your pivot tables and perform your analysis.
The Personal Macro Workbook is the Excel macro cousin
to the Normal.dot file in Word. It's a file that opens with Excel in the
background and makes your saved macros available for any open workbook. This
workbook is one of the options available when recording new macros in Excel.
We recommend saving all macros that will be used across
multiple workbooks into the Personal Macro Workbook. This will keep things
simpler for your users. If they try to save a macro into each workbook where
it will be used, they'll have a tough job of updating if the macro requires
a change. If it's saved in the Personal Workbook, then it will only need to
be changed once, and that change will be available across all workbooks.
When writing macros in the VBA Editor, macros should be saved in
Personal.xls.
Remember to retain this workbook when doing backups. It
can be a valuable file once you begin creating macros that help in your
work.
Created Date: 10/17/2001 Last Reviewed: 10/17/2001 Rev. Date: