Intermediate Excel Training
Ted Altenberg, Presenter
Tuesday, March 10, 2009: Formatting Excel Tables
Tuesday, March 17, 2009: Mathematical Functions & Formulae
Wednesday, March 25, 2009: Work on Your Own Projects
Wednesday, April 29, 2009: Work on Your Own Projects
A Few Useful Links:
Excel Files:
March 10, 2009: Formatting Excel Tables
Overview:
The real power of Microsoft Excel (or any full-featured spreadsheet program)
is its ability to perform mathematical operations, from simple arithmetic
to complex statistical, trigonometric and financial analysis, as well as
its programming functionality.
However, spreadsheets can also be used to create all sorts of tables, charts
and graphs, and as a part of the Microsoft Office suite, Excel has quite a number
of formatting tools to help you make your tables and charts look professional and pretty.
Handout for today’s workshop:
Formatting Tools:
Some of the most useful tools for formatting tables include:
- Row heights and column widths
- Inserting and deleting cells, rows and columns
- Hiding rows and columns
- Merging Cells
- Cell formatting (borders, alignment, colors, etc.)
- Font formatting (font, size, style, etc.)
- Page breaks and print area, to control what prints on a page
- Page headers and footers
- Repeating rows and/or columns on every page
- Page margins, page alignment, "shrink to fit" sizing
Know Time-Savers, Customize to Match Your Preferences:
All of the tools listed above are found on the Format menu, or under
File --> Page Setup. And—as is always the case with
Office programs-there are always 3 or more ways to do any one thing: Dropdown
menus, toolbar buttons, keyboard shortcuts, links form one dialogue box to
another (e.g. from Print Preview to Page Setup, etc.), etc.
My preferred order for these methods, what I find most time-saving, is:
- Learn the keyboard shortcuts for commonly performed functions;
- Customize the toolbars by adding buttons for things I use regularly;
- Using dropdown menu items.
Tuesday, March 17, 2009: Mathematical Functions & Formulae
Overview:
- The real power of Microsoft Excel (or any full-featured spreadsheet program) is
its ability to perform mathematical operations, from simple arithmetic to complex
statistical, trigonometric and financial analysis, as well as its programming functionality.
- This workshop explores the many ways you can format numbers in Excel, then
begins to explore basic mathematical functions and formulae.
Today's Handout
Number Formats:
You can format numbers in many ways:
- General (Excel decides, based on the characteristics of the number)
- Number (You set the number of decimal places; Excel rounds; has other options)
- Currency & Accounting (Adds the currency symbol; have other options)
- Date & Time (Many options; can perform time calculations)
- Percentage & Fractions
- Custom
- A few others…
Cell Addresses:
- Excel's vertical columns are lettered A, B, C… and its horizontal rows are numbered 1, 2, 3…
- Each cell therefore has an "address" (name) of a letter followed by a number.
The top left first cell is A1; the 28th cell down the 28th column is AB28…
Relative, Absolute and Mixed References:
Functions and formulae very often refer to data in other cells. These references can be relative, absolute, or a mix of relative and absolute:
- Relative Reference: In a formula, the address of a cell is
based on the relative position of the cell referred to, to the cell that
contains the reference. If you copy and paste the formula to another cell,
the reference automatically adjusts to refer to a new cell-with the same
relative position. A relative reference takes the form of A1.
- Absolute Reference: In a formula, the exact address of a cell,
regardless of the position of the cell that contains the formula. If you
copy and paste the formula to another cell, the reference remains the same
— to the same cell. An absolute cell reference takes the form $A$1.
- Mixed Reference: You can mix absolute and relative references
so that either the row or the column adjusts, but the other does not, as shown in this table:
| Reference (Description) |
Changes to |
| $A$1 (absolute column and absolute row) |
$A$1 |
| A$1 (relative column and absolute row) |
C$1 |
| $A1 (absolute column and relative row) |
$A3 |
| A1 (relative column and relative row) |
C3 |
Formulas & Functions:
A formula is an equation that perform calculations on
values in your worksheet. A formula starts with an equal sign (=).
=(argument) or
For example:
=(64*12)
=B5
 =(B5*23);
A function is a preset formula in Excel. Like formulas, functions
begin with the equal sign (=) followed by the function's name and its arguments. The
function name tells Excel what calculation to perform. The arguments are contained
inside parentheses, like this:
=function(argument) or
For example:
=sum(A1+1)
=average(B2:B12)
=SUM((D3+D4+D6)*0.03+(D5)*0.12)
=LOOKUP(AG20,G2:IV2,G16:IV16)
HINT:Type your formulas and functions in lower case. When you press
<enter>, Excel converts all correct data to ALL CAPS, but will leave in lowercase
any data it cannot interpret!
A few handy uses:
- Create an auto-numbered list, in either a row or column, with sum(A1+1).
Copy-paste that formula into a row or column of cells and it will count.
- Create a calendar by using the count function above, with numbers formatted
as dates and a spreadsheet arranged as a calendar.
Wednesday, March 25 and Wednesday, April 29, 2009:
Work on Your Own Projects
These sessions (3:30–5:30 pm, DO Tech Training Lab)
will each be “Open Lab Work Session” which means:
- You bring files and project ideas with which you want help;
- I’ll do my best to move from person to person, and provide you with 1:1 coaching/training/support
so you can learn the skills you need;
- Bring your files on CD, or flash drive (aka “thumb drive” or
“stick drive” or “flash memory”), or email them to yourself and
access your email via web, or email them to me and I’ll bring them…
If you email them to me you can ask me questions ahead of time and I’ll try to figure it out ahead of time…
If you want, and if that’s appropriate (re: file content).
Feel free to email or call me ahead of time
with your questions/issues, even without sending me a file.
