|
|
Cardinal Rules for Spreadsheets:
 | Never do the math yourself.
Use Excel to do ALL Math:
 | add (+), subtract
(-), multiply (*), divide
(/), |
| compare (IF & VLOOKUP) and functions (PMT, etc.). |
|
| ALWAYS instruct
Excel on how to perform the calculation (which cells, functions or
math operators) to use. This ensures that when the values
on which the computation is based change, the calculated value will
automatically change. |
| Check
your work that the numbers make sense!
Estimate what calculated cells should be. |
| Never type a value more than once.
ALWAYS point
to a cell (usually the nearest) that contains the value.
This is
critical
when the value you intend to use has been calculated by Excel. |
Good Practice
| Don't bury numbers
that could change (like a tax, discount rate or interest rate) in a formula. Put the number
in a cell and point to it. Numbers that don't change like 12 as in
12 months in a year, or 9 as in 9 square feet in a square yard, should
be put in to formulas. |
Common
Mistakes to Avoid:
| Print using
Print Preview so you know exactly what you are printing
before wasting the time and paper to print it. You can use Setup
to change from Portrait to Landscape, ad & remove Gridlines and row and
column heads and use Margins to modify column widths |
| Create a Header
with
| right the date and time |
| center: spreadsheet title
including your name in an interesting font, |
| left: Page
 of
 with
results in Page 1 of 3, page 2 of 3, etc. |
|
| Create a footer with,
| the Excel file name in a
tiny font size |
|
| When adding more than two numbers always use SUM
function (Σ, sigma icon)
not + + + (pluses). |
| When creating an average, use the Average function,
not SUM function (Σ) and
divide .
That way if another number is added to the group, the formula
will NOT have to be changed, it will automatically adapt. |
| Do NOT use sum, Σ, unless you are summing!
|
Format properly:
|
$ ONLY on dollar items in first and last (total) row. |
|
commas ,
on numbers 1,000 and over |
|
put one word over another in row 1 using Alt+Enter so columns are
minimum width |
|
run a Spell Check, use
F7 key or
 on the 2007 Review
Ribbon or 2003 Tools menu |
|
use 2 decimal places ONLY
|
for amount
under $100 or |
|
for a money transfer, otherwise just
whole numbers |
|
|
all numbers in a column must have the same number of decimal places
(pennies or no pennies) |
|
numbers are always right
aligned, so they can be visually totaled |
|
labels on the top row usually
look best centered |
|
add Bold, Italics, color etc.
to make the sheet more appealing and easier to read |
|
make rows taller by dragging
the line between the row numbers on the left down (never add blank rows
or columns) |
|
Since the spreadsheets
created in this course are not that complex, create the chart on the
sheet with the values so you can print values and chart together.
Never put a chart on the sheet by itself meaning Finish the Chart Wizard
at step 3. |
|
Highlight all the labels and values to be charted BEFORE clicking on
chart wizard
 .
If the values are not continuous (touching)
|
highlight the 1st value(s)
and
then hold down the Ctrl key while
highlighting the other values(s) and labels (names). |
|
With ALL values and
labels to be charted highlighted,
click the
 ,
Chart Wizard |
|
or if you forgot and now have 1, 2, 3 etc. for labels
 |
select the
entire chart |
|
click the
,
Chart Wizard, on
the tool bar, |
|
go
 , Next, to Step 2
|
|
select the Series tab,
|
|
click in the box next to
the category (x) labels, |
|
highlight the labels,
|
|
click the end of
selection icon
|
|
|
|
Never include
total value with detail values (it will be
half of the pie or a very tall column) |
|
Choose the
correct chart type
|
Pie charts are
good ONLY when charting one (1) column of values
|
explode
ONLY the one slice of a pie chart that you wish to emphasize, otherwise the pie looks like it was
dropped |
|
if you put labels around the
pie there is no need for a legend
|
|
|
Column and Bar (or
the cylinder, cone and prism) charts are good for charting
unrelated values (like apple sales and orange sales. |
|
Stacked bar and
column charts should be used when there is more than a dozen
values (a dozen bars) |
|
Area and line
charts are used when the numbers are related like month 1
payment, month 2 payment etc. |
|
|
Put patterns in slices and bar to make black
and white copies more readable
|
|
Never explode
the entire pie…just pull out the ONE slice you wish to emphasize
|
Check out the Hints for
- Formulas: Print non-repetitive formula (Highlight
JUST the cells to print, File menu, Print Area Set, Set Print Area)
- Reduce margins to 1/2 inch left and right so entire sheet
fits on1 or 2 landscape pages.
- For Formula Sheets ONLY: Print the Row number to the left (1, 2, 3,
4 ) and Column (A, B, C) Labels above.
Print gridlines (use File menu, Page Setup, Sheet tab)
- Resize columns wide enough to see entire formula but not too
wide so as to waste space.
- Charts: use patterns so that black & white printing
more clearly delineates the slices. Select the bar or pie
slice, double click, chose Fill Effects, Patterns tab and then click on a
pattern. OK OK.
|
