|
Server : Apache/2.4.62 System : FreeBSD fbsdweb2.web.rcn.net 14.1-RELEASE FreeBSD 14.1-RELEASE releng/14.1-n267679-10e31f0946d8 GENERIC amd64 User : www ( 80) PHP Version : 8.3.8 Disable Function : NONE Directory : /domains/bburke/xInHouseWebPages/ |
Upload File : |
<html>
<head>
<meta http-equiv="Content-Language" content="en-us">
<meta name="GENERATOR" content="Microsoft FrontPage 5.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Microsoft Excel Tips</title>
<meta name="Microsoft Theme" content="copy-of-blends 011, default">
</head>
<body background="../_themes/copy-of-blends/blegtext.gif" bgcolor="#CCCCCC" text="#000000" link="#0000FF" vlink="#CC3300" alink="#0000FF"><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica">
<p align="center"><font size="7">Microsoft Excel Tips</font></p>
<!--msthemeseparator--><p align="center"><img src="../_themes/copy-of-blends/blesepa.gif" width="600" height="10"></p>
<div align="center">
<center>
<!--mstheme--></font><table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" width="400" id="AutoNumber2">
<tr>
<td width="200"><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica">
<p align="center"><a href="Index.htm">Main Page</a><!--mstheme--></font></td>
<td width="200"><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica">
<p align="center"><a href="MicrosoftOfficeTips.htm">Microsoft Office Tips</a><!--mstheme--></font></td>
</tr>
</table><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica">
</center>
</div>
<p align="center"><b><font size="4">Tips added 08/<font color="#000000">17</font>/2004</font></b></p>
<div align="center">
<center>
<!--mstheme--></font><table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" width="100%" id="AutoNumber1" bordercolordark="#000000" bordercolorlight="#999999">
<tr>
<td width="50%" align="center"><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica"><span style="font-face: Courier New">
<a href="#Control how strict Excel is in determining macro safety (2000/2002)">Control how strict Excel is in determining macro safety (2000/2002)</a></span><!--mstheme--></font></td>
<td width="50%" align="center"><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica">
<p align="center">
<a href="#Easily undo multiple actions in Excel (97/2000/2001/2002)">
Easily undo multiple actions in Excel (97/2000/2001/2002)</a><!--mstheme--></font></td>
</tr>
</table><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica">
</center>
</div>
<p style="margin: 1 5"> </p>
<p align="center"><b><font size="4">Tips added 08/<font color="#000000">31</font>/2004</font></b></p>
<div align="center">
<center>
<!--mstheme--></font><table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" width="100%" id="AutoNumber3" bordercolordark="#000000" bordercolorlight="#999999">
<tr>
<td width="50%" align="center"><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica"><span style="font-face: Courier New">
<a href="#Freeze Excel column and row titles (97/2000/2001/2002)">Freeze
Excel column and row titles (97/2000/2001/2002)</a></span><!--mstheme--></font></td>
<td width="50%" align="center"><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica">
<p align="center">
<a href="#Easily identify if two entries are identical (Excel 97/2000/2001/2002)">
Easily identify if two entries are identical (Excel 97/2000/2001/2002)</a><!--mstheme--></font></td>
</tr>
</table><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica">
</center>
</div>
<p style="margin: 1 5"> </p>
<p style="margin: 1 5" align="center"><b><font size="4">Tips added 11/<font color="#000000">02</font>/2004</font></b></p>
<p style="margin: 1 5" align="center"> </p>
<div align="center">
<center>
<!--mstheme--></font><table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" width="100%" id="AutoNumber4" bordercolordark="#000000" bordercolorlight="#999999">
<tr>
<td width="50%" align="center"><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica"><span style="font-face: Courier New">
<a href="#Combine data from multiple cells into one (Excel 97/2000/2001/2002)">Combine data from multiple cells into one (Excel 97/2000/2001/2002)</a></span><!--mstheme--></font></td>
<td width="50%" align="center"><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica"><span style="font-face: Courier New">
<a href="#Overcoming problems when concatenating dates (Excel 97/2000/2001/2002)">Overcoming problems when concatenating dates (Excel 97/2000/2001/2002)</a></span><!--mstheme--></font></td>
</tr>
<tr>
<td width="50%" align="center"><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica"><span style="font-face: Courier New">
<a href="#Create backups to revert from unwanted saved changes (Excel 97/2000/2001/2002)">Create
backups to revert from unwanted saved changes (Excel 97/2000/2001/2002)</a></span><!--mstheme--></font></td>
<td width="50%" align="center"><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica"><span style="font-face: Courier New">
<a href="#Prevent objects from appearing on printouts (Excel 97/2000/2001/2002)">Prevent objects from appearing on printouts (Excel 97/2000/2001/2002)</a></span><!--mstheme--></font></td>
</tr>
<tr>
<td width="50%" align="center"><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica"><span style="font-face: Courier New">
<a href="#Divide numbers in-cell and store static results (Excel 97/2000/2001/2002)">Divide
numbers in-cell and store static results (Excel 97/2000/2001/2002)</a></span><!--mstheme--></font></td>
<td width="50%" align="center"><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica"><span style="font-face: Courier New">
<a href="#Quickly import data from Access into Excel (97/2000/2002)">Quickly import data from Access into Excel (97/2000/2002)</a></span><!--mstheme--></font></td>
</tr>
<tr>
<td width="50%" align="center"><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica"><span style="font-face: Courier New">
<a href="#Format comments to see the cells beneath (Excel 97/2000/2001/2002)">Format
comments to see the cells beneath (Excel 97/2000/2001/2002)</a></span><!--mstheme--></font></td>
<td width="50%" align="center"><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica"><span style="font-face: Courier New">
<a href="#Reverse direction when searching in a worksheet (Excel 97/2000/2001/2002)">Reverse direction when searching in a worksheet (Excel 97/2000/2001/2002)</a></span><!--mstheme--></font></td>
</tr>
</table><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica">
</center>
</div>
<p style="margin: 1 5"> </p>
<p style="margin: 1 5" align="justify"><span style="font-face: Courier New"><b>
<a name="Control how strict Excel is in determining macro safety (2000/2002)">Control how strict Excel is in determining macro safety (2000/2002)</a></b><br>
<br>
When you open a workbook containing macros in older versions of
Excel, you're asked whether you want to allow the macros to run.
Beginning with Excel 2000, you can set specific macro security
levels that are as strict or lax as you want. To change the
security level, choose Tools | Macro | Security from the menu
bar. Simply choose the level of protection you want and click OK.<br>
<br>
The dialog box provides brief description of the settings, but
to put things in context, the Medium setting is pretty much what
you're used to from earlier versions of Excel. When using the
highest setting, macros that aren't digitally signed with an
authentication certificate that Excel is configured to trust are
automatically disabled (but you can still work with the file's
data). We agree with Microsoft's "not recommended" comment
regarding the lowest setting, which lets Excel open any and every
file. In fact, we'll go one step further: Don't select it. Ever.</span><span style="FONT-SIZE: 10pt; font-face: Courier New"><br>
<br>
</span></p>
<p style="margin: 1 5" align="justify"> </p>
<p style="margin: 1 5" align="justify"><b>
<a name="Easily undo multiple actions in Excel (97/2000/2001/2002)">Easily undo
multiple actions in Excel (97/2000/2001/2002)</a></b><br>
<br>
If you're a longtime keyboard jockey, you probably instinctively use the [Ctrl]Z
shortcut when you want to undo a change you've made. However, if you need to
backtrack through many steps, you may not recall exactly how many steps you need
to undo. Chances are you'll backtrack too far and then wind up redoing more
steps than you had planned. The next time you find yourself in this situation,
use the dropdown arrows on Excel's Undo and Redo
buttons. These provide you with a running list of your recent actions so you can
easily undo or repeat exactly the steps you want.</p>
<p style="margin: 1 5" align="justify"> </p>
<p style="margin: 1 5" align="justify"><span style="font-face: Courier New"><b>
<a name="Freeze Excel column and row titles (97/2000/2001/2002)">Freeze Excel
column and row titles (97/2000/2001/2002)</a></b><br>
<br>
When you work with a large Excel worksheet, it's often difficult to remember
exactly what kind of data columns or rows contain once you begin scrolling
around the sheet and lose sight of the column and row labels. Fortunately, you
can freeze rows and columns that contain headings so you always know what data
you're looking at. To freeze a row, select the row number or the cell in column
A that's immediately beneath the last row you want frozen. Then, select Window |
Freeze Panes from the menu bar. Excel inserts a thin line to show you where the
frozen pane begins. To freeze a column, select the column letter or the cell in
row 1 that's immediately to the right of the last column you want frozen. To
freeze horizontal and vertical areas simultaneously, select the cell that's in
the upper-left corner of the range you want to remain scrollable and then invoke
the Freeze Panes feature. If you later want to make all regions scrollable
again, simply select Window | Unfreeze Panes from the menu bar.</span></p>
<p style="margin: 1 5" align="justify"> </p>
<p style="margin: 1 5" align="justify"><b>
<a name="Easily identify if two entries are identical (Excel 97/2000/2001/2002)">
Easily identify if two entries are identical (Excel 97/2000/2001/2002)</a></b><br>
<br>
When you need to create a formula that compares two cell values, consider how
important it is to your results that the items match exactly. By default, Excel
ignores capitalization when comparing the values. To demonstrate, select cell A1
and enter the text value:<br>
<br>
INSIDE MICROSOFT EXCEL<br>
<br>
Then, select cell B1 and enter the value:<br>
<br>
Inside Microsoft Excel<br>
<br>
Now, in cell D1, enter the following formula:<br>
<br>
=A1=B1<br>
<br>
Although the capitalization is inconsistent between the two values, the formula
returns a result of TRUE because the letters<br>
match. If your formula depends on a result that takes capitalization into
consideration, use the EXACT worksheet function. To demonstrate, enter the
following formula in cell E1:<br>
<br>
=EXACT(A1,B1)<br>
<br>
This formula returns a result of FALSE.<br>
</p>
<p style="margin: 1 5" align="justify"><span style="font-face: Courier New"><b>
<a name="Combine data from multiple cells into one (Excel 97/2000/2001/2002)">
Combine data from multiple cells into one (Excel 97/2000/2001/2002)</a><br>
</b><br>
Excel makes it easy to combine information stored in separate cells into a
single cell. There are two ways to concatenate<br>
information--using the CONCATENATE function or using ampersand (&) concatenation
operator. To demonstrate, let's say that a<br>
worksheet stores name information in two columns. First names are stored in
column A and last names are stored in column B. A<br>
project you're working on requires that the data is combine into a "Last Name,
First Name" format. <br>
<br>
To examine both concatenation techniques, enter a first name in column A and a
last name in column B, as we described. Then, in cell C1, enter the following
formula:<br>
<br>
=CONCATENATE(B1,", ",A1)<br>
<br>
Notice that we combined cell references with a literal text string, the comma
and space that separate the last and first<br>
names. Although we didn't include any, you can also concatenate numeric data.
The CONCATENATE function can accept up to 30<br>
arguments. <br>
<br>
To produce the same result using the concatenation operator, enter the following
formula in cell D1:<br>
<br>
=B1&", "&A1<br>
<br>
Note that this approach can also concatenate numeric data, but it isn't limited
to 30 items.<br>
</span></p>
<p style="margin: 1 5" align="justify"><span style="font-face: Courier New"><b>
<a name="Create backups to revert from unwanted saved changes (Excel 97/2000/2001/2002)">
Create backups to revert from unwanted saved changes (Excel 97/2000/2001/2002)</a><br>
</b><br>
When you're working fast, you're bound to make mistakes. You'll often catch your
errors right away, but it's easy to make<br>
accidental changes to a workbook's existing data and not notice until it's too
late. If you've ever felt the nausea that accompanies the realization that
you've just saved workbook changes that unintentionally destroy large amounts of
data, you<br>
should investigate Excel's backup feature. Excel's backup capability provides a
way to restore a workbook as it existed<br>
prior to when you last saved the file. Unfortunately, the feature isn't as easy
to use as it probably should be--it's activated in<br>
an obscure manner and must be set up on a file-by-file basis. <br>
<br>
You specify whether automatic backups should be created when you save a
workbook. Once enabled, the setting remains active for all subsequent saves,
unless you specifically disable it. To activate the setting in a new file, click
the Save button or choose File|Save from the menu bar. If you're working with an
existing file, choose File | Save As from the menu bar.<br>
<br>
When the Save As dialog box appears, click the Tools button on the dialog box's
toolbar and select General Options from the<br>
subsequent menu (just click the Options button on the dialog box if you're using
Excel 97 or Excel 2001). Next, select the Always<br>
Create Backup check box and click OK. Finally, name and save the file as you
normally would.<br>
<br>
From now on, Excel creates a backup in the same folder as the original file
whenever you save the workbook. Instead of an .xls<br>
file extension, the backup uses an .xlk extension. Excel automatically names the
backup by incorporating the name of the original file, using the format Backup
Of Filename. <br>
<br>
Keep in mind that Excel only maintains one backup file. Each time you save your
workbook, the last backup file is overwritten<br>
with a new one.<br>
<br>
To restore a backup, choose File | Open from Excel's menu bar. Then, choose
Backup Files from the Files Of Type dropdown list, select the appropriate backup
file and click Open. You can create a new workbook from the backup by choosing
File | Save As and saving a regular Excel file.</span></p>
<p style="margin: 1 5" align="justify"> </p>
<p style="margin: 1 5" align="justify"><span style="font-face: Courier New"><b>
<a name="Divide numbers in-cell and store static results (Excel 97/2000/2001/2002)">
Divide numbers in-cell and store static results (Excel 97/2000/2001/2002)</a><br>
</b><br>
You probably frequently need to evaluate calculations as you enter data. For
example, you might need to determine a particular<br>
percentage that needs to be entered in a cell on which a formula depends. If
your worksheet doesn't have any reason to include the supporting information
that generates the value you actually need, you might crunch the numbers with a
calculator and then key the results into Excel. Or you may create simple
worksheet formulas that you later delete after figuring out the values you
require. When you're dealing with division results, you don't have to duplicate
work or copy numbers. Instead, you can take advantage of undocumented behavior
(in other words, what may be a bug) to calculate the result and store it as a
static value.<br>
<br>
Ordinarily, entering an expression like 30/100 in a cell inserts that specific
string. You can display the calculated result by<br>
entering a formula, such as =30/100. However, the cell then contains the
formula, not the actual value. The trick to having Excel calculate a division
equation and then store the final result is to first apply a number format to
the relevant worksheet cells. <br>
<br>
To demonstrate, open a new workbook. In cell A1, enter 30/100. In cell B1, enter
=30/100. Now, select cell C1 and choose Format | Cells from the menu bar. Click
on the Number tab. Then, select Number from the Category list box and click OK.
Finally, enter 30/100. <br>
<br>
Now, select each cell individually and examine the Formula Bar as you do. You'll
see that cell C1 contains the calculated<br>
result, 0.3, as a static value. </span></p>
<p style="margin: 1 5" align="justify"><span style="font-face: Courier New"><br>
Keep in mind that this technique only works numeric values--you can't
incorporate cell references. You can also use it when cells incorporate
Currency, Fraction, or Percentage number formatting. <br>
<br>
We've been unable to find the reason for this behavior, or why it's limited to
division operations. Our original assumption was<br>
that it's related to Excel's Transition features, which help you migrate legacy
spreadsheet documents to Excel. If anyone can shed light on the subject, we'd
like to hear from you at <a href="mailto:[email protected].">
[email protected].</a></span><span style="FONT-SIZE: 10pt; font-face: Courier New"><br>
</span></p>
<p style="margin: 1 5" align="justify"><span style="font-face: Courier New"><b>
<a name="Format comments to see the cells beneath (Excel 97/2000/2001/2002)">
Format comments to see the cells beneath (Excel 97/2000/2001/2002)</a></b><br>
<br>
Cell comments are meant to be informative, but you'll often find that they
obscure information you want to see. You can hide them or drag them out of the
way easily enough. However, the process can grow tiresome if you need to
repetitively toggle whether the comments are visible. Depending on your needs,
you may have an alternative. You can format comments so they're partially
transparent. That way you can view the comment's message and cell data beneath
it simultaneously. <br>
<br>
To demonstrate, open a workbook containing existing data. Select a cell in the
middle of the used range and then choose Insert | Comment from the menu bar.
Enter a brief note and then click on the comment box. Be sure that you click on
the box shape's edge, and not within the comment's text area. <br>
<br>
Now, choose Format | Comment from the menu bar. Next, click on the Colors And
Lines tab. Using the Transparency slider, or its associated spin box, choose a
setting of around 20 percent. Finally, click OK. You can now see through the
comment to the<br>
cells beneath it. Experiment with the Transparency setting until you find a
setting that you feel appropriately balances comment and cell data readability.</span><span style="FONT-SIZE: 10pt; font-face: Courier New"><br>
<br>
</span><span style="font-face: Courier New"><b>
<a name="Overcoming problems when concatenating dates (Excel 97/2000/2001/2002)">
Overcoming problems when concatenating dates (Excel 97/2000/2001/2002)</a></b><br>
<br>
We recently discussed how to combine multiple data items as a string, using both
the CONCATENATE function and the ampersand (&) operator. You may be surprised at
the result when you have a date value among the items you're concatenating. To
demonstrate what happens, open a new worksheet, select cell A1 and enter the
following:<br>
<br>
Today's Date Is: <br>
<br>
Then, select cell B1 and enter:<br>
<br>
=TODAY()<br>
<br>
Now, select cell A5 and enter the following:<br>
<br>
=A1 & B1<br>
<br>
Instead of the result you might expect, you find a number in place of the date.
That's because Excel treats dates and times as<br>
serial numbers. To see the number in a date structure, you need to format it
appropriately when it gets converted to a string. To do so, use the TEXT
function. In cell A6, enter the following formula:</span></p>
<p style="margin: 1 5" align="justify"><span style="font-face: Courier New"><br>
=A1 & TEXT(B1,"mm/dd/yyyy")<br>
<br>
The result now incorporates B1's value formatted as a date.</span><span style="FONT-SIZE: 10pt; font-face: Courier New"><br>
</span></p>
<p style="margin: 1 5" align="justify"><span style="font-face: Courier New"><b>
<a name="Prevent objects from appearing on printouts (Excel 97/2000/2001/2002)">
Prevent objects from appearing on printouts (Excel 97/2000/2001/2002)</a></b><br>
<br>
Worksheets often contain embedded chart objects, as well as objects like form
controls and pictures. Sometimes you'll want certain objects to only be visible
when the worksheet is viewed onscreen. For example, consider form controls like
spinner buttons or buttons that run macros. They're useful within Excel's
application environment, but may provide no benefit on a printout. Fortunately,
it's easy to configure objects so that they don't print. To do so, right-click
on the object ([control]-click on the Mac) and choose Format <object type> from
the shortcut menu. Then, click on the Properties tab. Finally, clear the Print
Object check box and click OK.</span><span style="FONT-SIZE: 10pt; font-face: Courier New"><br>
<br>
</span><span style="font-face: Courier New"><b>
<a name="Quickly import data from Access into Excel (97/2000/2002)">Quickly
import data from Access into Excel (97/2000/2002)</a></b><br>
<br>
It's usually much easier to manipulate data in Excel than it is in Access, so
people commonly import data from Access tables into Excel worksheets. Microsoft
recognizes this and includes the Office Links features in Access to simplify the
process of copying data to a worksheet. The feature works with Access tables,
queries, forms and reports. To use this feature, simply select the relevant
object in Access's Database window or open the object. Then, choose Tools
| Office Links | Analyze It With Excel. Excel creates a worksheet that
incorporates minor formatting from the original Access object. The type of
object you select in Access affects the worksheet layout and what data is
exported. For example, when using a form, just the record shown in the form is
exported. When exporting a grouped report, Excel applies outline levels to the
various groups.</span><span style="FONT-SIZE: 10pt; font-face: Courier New"><br>
<br>
</span><span style="font-face: Courier New"><b>
<a name="Reverse direction when searching in a worksheet (Excel 97/2000/2001/2002)">
Reverse direction when searching in a worksheet (Excel 97/2000/2001/2002)</a></b><br>
<br>
When you're using the Find or Replace feature in Excel, it's easy to
accidentally click the Find Next button one too many<br>
times. Fortunately, you don't have to cycle through all of the matches in the
worksheet to get back to ones you missed the first time. Instead, just hold down
the [Shift] key when you click the Find Next button. Excel searches in the
opposite direction through the worksheet as it hunts for matches to your search
string.</span><span style="FONT-SIZE: 10pt; font-face: Courier New"><br>
</span></p>
<!--mstheme--></font></body>
</html>