Tips to Excel

  Graeme Nicholas gdnic@ozemail.com.au
Graeme is a former Club Mac Administrator and Treasurer, a former CFO and semi retired MYOB consultant

Last time I covered the Excel feature: Adding Comments to Cells.
But, I really hankered to be able to explain how you might dismiss or rather delete the title of one’s name from the Comment Sticky Header appearing on your worksheets - period.
I discovered, when looking through my notes, an earlier reference I kept (from the dark side), that explained how you might fiddle with the set-up and different menu selection to dismiss the default format of the user’s name attaching to every damn Comment Stickie you used - that is, in Excel 97. What happens in later versions on Wintel I simply do not know, nor care.
Well the note explained a complicated way to expunge the dreaded format 97 and was obviously no use to me here with Excel 98.
So wondering, I went to the Tools>Preference >General tab in my Excel 98 sundry worksheet and found my name in the bottom field of the dialogue box and deleted it… thinking this may work!
Checking back, my name reappeared, but alas!
All you need to do, is delete your name from each comment stickie and as far as I can tell, it will stay deleted.
So you get a slimmed down Comment Stickies to use, without your name hanging 5 on every note, which is really pleasing to relate.
For the time being, anyone with Excel 2000 or Excel X that has a different experience, I would appreciate an e-mail to that effect to gdnic@ozemail.com.au if you have time, thanks, and I’ll pass on your experience to the members in coming edition/s.

In this edition I just cover off on a number of tips below that may be of interest.

Creating a series from one starting point

The fill handle is the small square found in the lower-right corner of the cell selector. Dragging this handle to other cells can perform many functions. Although it serves many purposes, Excel's fill handle is an especially convenient tool for sequentially numbering adjacent cells. However, if you currently use the fill handle this way, you may not be using it as efficiently as possible if the sequential numbers increment by one.
Typically, you manually enter the first two values in the series so that Excel can figure out what increment to use. For example, you might enter 5 in cell A1 and 10 in cell A2. Then, you select both cells, range A1: A2, and drag the fill handle down to create a series of numbers that increment by five.
If your series increments in units of one, there's no reason to enter data into a second cell. However, you can't just drag the fill handle--doing so will simply copy your starting value to all of the cells. To increment from your starting value by one, enter the first number in the series and re-select the cell. Then, drag the fill handle while holding down the [Ctrl] key (use the [Alt] key on a PC). Doing so will force Excel to treat the new data as a series, instead of a copy of the original value.
This feature is especially handy doing traditional financial spreadsheets using calendar months.
In these instances you do not need to evoke the CTRL key to get your monthly series across the columns as illustrated simply in Figure 1 below.

It appears this may apply to Date sequences as well. I use weekly 5 workday series all the time - (i.e. between a row break - for week ago comparisons, for example).
I force the series further down for the next week to save me formatting the cells. Then I delete the weekend dates to set up the next couple of weeks. Its a lazy way of course, but there you are.

Copy objects quickly with control

Often you need to copy something, such as a range, a sheet or drawing object. Rather than use CTRL+C and CTRL+V or some other method, dragging with the CTRL key held down can be faster. As you drag with CTRL held down, a small plus symbol appears to let you know that you are copying and not moving. Be sure to release the mouse button before the CTRL key.

Using Segments of a Toolbar for accessibility

I don’t use the Draw Toolbar very often for effect, but if you do, you may find it tiresome going back to the Toolbar every time for a line graphic or to use an arrow say for impact on your worksheet.
I don’t use floating toolbars, or multiple toolbars that you can leave buried under your main toolbar. I check the Customise dialogue box (CTRL click on a vacant spot of your toolbar) to check/activate say the Draw toolbar as used for this illustration see Figure 2 below.

The example concerns the use of the illustrated Autoshapes/Line facility in Fig. 2 above from the floating Draw Toolbar (which is usually docked to the bottom of my screen by default).
What I have done is opened the Autoshapes menu highlighted the line menu and dragged the selection onto my spreadsheet. a form of undocking the menu selection.
From there you point and click the shape or line you wish to put on your spreadsheet and rather than holding the mouse click down, just take the mouse pointer to the worksheet and you will be carrying across a black cross. So point the cross at a cell to achieve an insertion of that line or arrow or shape in the selection. See Figure 3 below.

Handles should appear for the shape to allow you to lengthen or position it to the desired size.

Simplify maintaining formulas with named constants

When you create a set of formulas that incorporate a value that doesn't change, you can make your formulas more readable by replacing the value with a named constant.
Just as you can name worksheet ranges, you can assign a name to a specific value.
To name a constant, display the Define Name dialogue box by choosing Insert | Name | Define from the menu bar.
After typing a name for the constant, replace the information in the Refers To text box with the constant value and click OK. You can now substitute the name you used in place of the value as you construct formulas.

Use SUM to sum though worksheets

The task is to sum a single cell in sheets 1 to 10. A formula such as =Sheet1!G5+Sheet2!G5+Sheet3!G5+ ..etc..is long-winded and prone to error. Instead, use the following technique:

(1) type =SUM( and click the first sheet tab (Sheet1)
(2) hold down the Shift key and click the last sheet tab (Sheet10) to group
the sheets
(3) click on the cell to sum (G5)
(4) Press Enter

Hey Presto
Excel creates the formula =SUM(Sheet1:Sheet10!G5).

And a final TIP: Double-click to remove split

To see different parts of the same sheet on screen, you may have split the window into two panes (Window|Split). To remove the split, you can drag the split line to the edge of the spreadsheet. There is a quick way, though: double-click the split line.