When we last stalked Annie Tafel (in the 29 March Ancestry Daily News
www.ancestry.com/library/view/news/articles/3635.asp), she was
learning how to copy formulas with relative and absolute cell
references. It was very helpful to her to be able to calculate
approximate birth years from census information. As her spreadsheet
grew, it became somewhat of a haystack, and the information that she
was looking for, somewhat of a needle. What Annie came to realize was
that she needed some help identifying values that were unusual, so
that she could focus her attention on them. Today, we want to
understand more about the power of copying formulas, and learn about
cell formats.
Paste Special
Many of you are familiar with the COPY and PASTE options on the EDIT
MENU. Few people are equally familiar with the PASTE SPECIAL option.
If you copy anything, you've put it on the " windows clipboard." When
you paste it, you are copying from the clipboard to the new cell.
PASTE SPECIAL has three ways to change the information as you paste
it back.
When you select PASTE SPECIAL, a dialog box pops up with eight
options, two of which interest us. VALUES means that Excel will paste
the values into the target cells as "literals" instead of "formulas."
Literals can be copied all over the sheet and their values won't
change. They can be TRANSPOSED (changed from rows to columns, see
below) without changing either. FORMATS means that Excel will paste
the format of the source cells without changing the values in the
target cells. I often use the keyboard shortcut ALT-E,S,T to paste
formats.
The OPERATION section will allow you to perform arithmetical
operations on the source and target cells. I have never used this
feature.
The third section, which is not labeled, contains TRANSPOSE. This
feature will paste a row of numbers into a column, and vice versa.
This is useful when you change your mind about the layout after you
have already started.
Format Basics
Many of you are already familiar with the FORMATTING TOOLBAR. In
Excel, the VIEW menu has a TOOLBARS option. Excel installs with the
STANDARD TOOLBAR loaded. I like to use VIEW/TOOLBARS to show the
FORMATTING TOOLBAR as well. On that toolbar, you can change the font,
font size, bold/italic/underline, justification (left, right, and
center). You can also format numbers as currency or percentage, and
change the number of decimals displayed. You can set up the borders,
fill color (the color of the cell background), and font color. One of
my favorite tools on this toolbar is the MERGE AND CENTER tool, which
has a lowercase "a" with arrows pointing to the right and left. If
you select several adjacent cells and then click on this tool, the
cells are merged and the text in them is centered.
The Format Painter
One of the tools on the STANDARD TOOLBAR looks like a wet paintbrush,
maybe a 4-inch wide job. This icon is the FORMAT PAINTER. The Help
Text from Excel has this to say:
Copy formats from one cell or range to another
1. Select a cell or range that has the formatting you want to copy.
2. On the Formatting toolbar, click Format Painter.
3. Select the cell or range you want to copy the formatting to.
Tips
Continue copying: To copy the formatting in the selected cell or
range to several locations, double-click the Format Painter button.
As you select the targeted cells, the format will be copied into
them. When you finish copying the formatting, click the button again.
Copy column widths: Select the heading of the column that is the
width you want to copy, click the Format Painter button, and then
click the heading of the column you want to copy the width to.
Conditional Formatting
One of my favorite features in Excel is CONDITIONAL FORMATTING. Found
on the FORMAT MENU, this feature does just what it sounds like--
changes the format of a cell based on a condition that you specify.
The user can change the font, fill color, border, bolding, and
underlining using this option.
WARNING: Specifying these conditions correctly takes practice. It's
very easy to make the numbers blue for years before 1900 and red for
years before 1800. It's more complicated if you want to make the
numbers blue if they're more than 100 greater than the number to the
left.
I personally think that changing the FILL COLOR shows the information
pattern more clearly than changing the font color.
Genealogy Tie-In
Spreadsheets are very useful for spotting patterns in sets of
information. Making spreadsheets to organize rows and columns of
information is a very powerful way to discover patterns and to
identify holes in your research.
Link Me Up
(more stars is better)
About.com ***
www.businesssoft.about.com/compute/businesssoft/library/weekly/
aa121300a.htm
Karen Stille's example of Relative versus Absolute in Excel
Learning Excel ****
http://s9000.furman.edu/DD/labs/Excel/
A course from Furman University that you can read online.
Conditional Formatting **
www.sharbrough.net/Excel%20-%20Conditional%20Formatting.htm
I was astounding to see an article from my site come up 3rd on
Google.
Ask Mr Excel ***
www.mrexcel.com/
A series of articles about Excel use. If the pages weren't so ugly to
look at, I'd give it 4 stars.
What Else?
Interested persons can view examples on my Web site
(www.sharbrough.net/genarticles.htm). You can view the
examples in Web format, or download the worksheet (a better way to
see the formulas and try the examples).
Beau Sharbrough is the president of GENTECH, the founder of the
GENTECH and FGS Web sites, and a founder of the Lexicon Working
Group. He would like to hear from you at beau@sharbrough.net, but due
to the volume of e-mail received, he is unable to answer every e-mail
message received. Please note that he cannot assist you with your
individual computer problems. Visit Beau's Web site for information
about speaking engagements. Beau is the father of two college-age
girls and is a proud graduate of Texas A&M University.
Ancestry Daily News
