Welcome Members News Search Committees
  You are not logged in Log in
You are here: Home » Members » dkalbus91's Home » Tips & Tricks

Tips & Tricks

I periodically send these out to Warren County Employees
“Tips & Tricks”

Create a range name for only one sheet (Excel 97/2000/2002)

Named ranges make it significantly easier to construct formulas and find particular sets of data. Typically, you can reference a name from any sheet. For example, if you create a range named Results on sheet 1, you can enter the formula


on sheet 2. Likewise, the name Results appears in the Name box and Go To dialog boxes. Sometimes, you may want to create a range that's applicable to a particular sheet. To do so, simply preface the range name with the appropriate sheet name using the sheet!rangename syntax. For example, to create a range that points to a summary section on sheet 2, you might name the range Sheet2!SummaryData. The SummaryData range name will only appear in the Name box, or dialog boxes that show range names, if you're working with sheet 2. If you create a formula that should reference a named range that was exclusively associated with another worksheet, you can do so using a fully qualified reference like

View function arguments as you enter a formula (Excel 97/2000/2002)

Chances are you've found yourself at a complete loss to remember the order of a particular function's arguments while you were in the middle of entering a formula. In such cases, you might launch the Formula Palette or scrap the formula altogether to look up the information in a reference. Fortunately, there's an easier way to jog your memory. After you enter the opening parenthesis for the function, press [Ctrl][Shift]A. Excel displays the argument names within your formula, which you can type over with the appropriate values.

Print an envelope without printing its attached letter

(97/2000/2001/2002) As you may already know, you can attach an envelope to a letter or other document by choosing Tools | Envelopes And Labels from the menu bar and clicking on the Envelopes tab. (In Word 2001, simply choose Tools | Envelopes from the menu bar.) Configure your envelope as desired, and then click Add To Document to attach it to the beginning of the current document. (In Word 2001, select the Insert This Envelope Into The Active Document check box, and then click OK.) However, if you've ever wanted to print the envelope without printing the document that it's attached to, you may find that your instincts deceive you. Word adds the envelope to the beginning of the document, so it would seem logical that you could print just the envelope by choosing File | Print from the menu bar, selecting the Pages option button, and entering 1 in the corresponding text box. However, when you click OK, you may be surprised to find that Word prints the first page of your letter or document, not the envelope. You could always tell Word to print the first section of your document, which houses the attached envelope, but there's actually an even easier way. When you attach an envelope to a document, Word considers the envelope as page 0, not page 1. To print the envelope alone, choose File | Print from the menu bar, select the Pages option button, and then enter 0 in the corresponding text box. (In Word 2001, choose the From option button, and then enter 0 in the From and To text boxes.) Click OK (Print in Word 2001), and Word prints your envelope without printing the document it's attached to.

Easily create formatted Excel sheets from Access data

Access provides an easy way to export data to Excel through the Office Links feature. To use this feature, simply select a relevant database object and choose Tools | Office Links | Analyze It With Excel. The worksheet Excel creates includes some minor formatting applied to the field headings that appear in row 1. However, you should be aware that some formatting in your original Access database affects the worksheet cell formatting as well. For example, if you're exporting from a datasheet, gridline and font attributes are carried over to Excel. If you use the Office Links feature to export data behind a form, text box shading and font properties are applied. The final result in Excel may not exactly match your Access data (for example, colors may get changed and bold formatting is lost); however, you'll probably find that less work is required to get your Excel version of the data into an easily readable state.

Reverse the order of Excel chart elements

Excel's chart wizard lets you create a graphical representation of data in a minimal amount of time, but you may not always want to keep the order that Excel uses for the chart categories or values. Fortunately, it's easy to reverse the order of categories and values. Just select the axis that contains the items you want to reverse. Then, choose Format | Selected Axis from the menu bar. When the Format Axis dialog box appears, click on the Scale tab. Finally, select the Categories In Reverse Order or Values In Reverse Order check box (depending on which axis you selected) and click OK.

Enter the same value in several cells at once (Excel 97/2000/2001/2002)

When you need to fill multiple cells with the same entry you may simply copy and paste the value or use Excel's Fill Handle to do the job. However, there's another technique that can at times be more efficient. Simply select all of the cells that you want to fill. Then, type the entry in the active cell and press [Ctrl][Enter]. Excel plugs the entry into all of the cells that were highlighted. Note that you can also use this technique to fill non-contiguous cells -- simply hold down the [Ctrl] key as you select each cell (use the [command] key on a Mac). Also, you can use the technique to enter a formula in multiple cells at once. If your formula contains relative cell references, Excel adjusts each formula to point to the appropriate relative ranges.

How to have your signature automatically applied in your email
Open Outlook
Go to Tools
Once the Options Dialogue box is open
Click on the Mail Format
In the bottom right corner - click on signature picker
New - Name the type of signature
Example: professional: with all office info.

Casual: name and email only
Pick your font, size, color, etc.
Click OK
Check the box: Use this signature by default.

Denise Kalbus
301 Buxton Street, Suite 103
Indianola, IA 50125
Office: 515.961.1009
Fax: 515.961.1079