Hidden Format
You can format anything in Excel so it will not appear on the
worksheet but still be present in the worksheet. This works for both
screen and printing. The custom format characters are three semicolons
or ;;;
Creating the Hidden Format
The Accounting
Format
One common problem in earlier spreadsheets was to get numbers to
line up properly in columns when the currency format was used for only
the first number in the column.
Example of Numbers Not Lining Up Correctly
Excel added the
"Accounting Format" to solve this problem. With the accounting format
you can specify several different formats (i.e. show $ mark or not and
specify the number of decimal places) and the numbers will still line up
properly.
The Format Cells Dialog Box
The accounting format
can also be selected by choosing the comma and dollar mark icons from
the formatting toolbar. Although it is not clear that these icons
represent the accounting format from the description that appear when
you hold the cursor over the icons, they are in fact shortcuts to the
accounting format.
The accounting format
not only affects the way the numbers are lined up, it also impacts the
way the single and double underline work. With the general and currency
formats the single and double underlines are only as wide as the numbers
and change when the width of the numbers change.
This frustrates most
accountants and drives them to using borders rather than underlines.
If, on the other hand, you use the accounting format you will find that
your single and double underlines are always just a little bit narrower
than the width of the cells, no matter how wide your numbers are.
The Indent Icon
Accountants frequently use spreadsheets to prepare financial
statements with headings and sub-headings. Sometimes they use the
spacebar to indent the sub-headings and sometimes they place them in the
next column and make the first column the width of the indent they wish
for the sub-headings. An easier way is to use the Indent icon. The
size of the indent can easily be increased or reduced and you can do it
to an entire range of cells at the same time.
Center Without
Merging Cells
Using the Merge Cells command to center across columns causes key
problems in earlier versions of Excel when you later try to do things
like select print ranges, insert columns, highlight only specific
columns, etc and minor problems in later versions. A better approach is
to select the "Center Across Selection" feature from the Format Cells
Alignment menu.
Centering Across Selection Without Merging Cells
If this is a feature
you use often you would be well advised to add its’ icon to the Standard
Toolbar.
Master Templates
For many of the settings you use frequently such as, formats,
macros, sheet names, ranges, etc. you many want to create a Master
Template. The simple way to do this is start with a blank worksheet and
fix up all the items you will want to have present in all future
worksheets then save the file to be used as the starting point of all
new worksheets. The following are several different ways to preserve
this template for later use.
1. Save the
file as _blank.xls in the default directory. Whenever you want to start
a new worksheet you simply open this file, which will be the first file
in the default directory. Note: It is probably a good idea to change
the file attribute of this file to read only so you won’t forget to
rename the file before you save it.
2. Save the
file as a template with the XLT extension in the directory containing
the Excel templates. This directory is usually: C:\Program
Files\Microsoft Office\Templates
When you want to start a new file you can select File New and your
template will be listed as one of the available templates.
3. To
permanently change the default worksheet you get when you start Excel or
start a new file by clicking on the new file icon, save the file as a
template named BOOK.XLT in the Excel XLStart folder.
Excel will use this template as the default model from then on. You can
create a similar default file for individual worksheets using the
filename SHEET.XLT in the same directory.
- END -
