FORMATTING:
Add
borders, patterns (background
colors). Center, Bold, Italicize,
change font/font size. Apply an
AutoFormat that includes borders,
patterns, and other formatting.
Convert numbers to currency or
percentages. Add ClipArt and
pictures, rename a spreadsheet tab,
etc.
AUTOSUM:
Use
AutoSum to add numbers.
FORMULAS:
Use
Formulas to add, subtract, multiply,
and divide numbers, calculate
percentages. Learn how to
Fill (copy) Formulas. Much of the
class will be devoted to creating
formulas.
EDIT A SPREADSHEET:
Select (highlight) cells. Copy,
Move, Format, and Delete text and
numbers. Insert additional sheets, columns, and rows.
Delete sheets, columns, and rows. Resize columns
and rows.
Simple sorting of data. Learn about various
printing options, including how to
make titles (column headings) print
out on every page of your
spreadsheet.
CHARTS & GRAPHS:
Insert, Move, Edit, and Resize a variety of different charts.
Intermediate Excel Course
Description:
Excel-2003-Int
Excel-2007-Int
ADVANCED FORMULAS:
Create more complex formulas using
Absolute Cell References, and the
Order of Operations.
3-D FORMULAS:
Combine data from different sheets
located within the same workbook
(file).
LINK WORKBOOKS & CONSOLIDATE
DATA:
Link data from different workbooks
(files).
VALIDATION:
Use Validation to improve speed and
accuracy of data entry, set limits
on what can be entered, add
drop-down lists and pop-up
explanation boxes.
PIVOT TABLES:
Use Pivot Tables to summarize rows
of data for reports.
GROUP / OUTLINE / SORT / FILTER /
SUBTOTAL DATA:
Sort and Filter data, Subtotal data,
Group and Outline data. Create Forms
to speed up data entry, editing, and
searching.
TEMPLATES:
Create and edit a Workbook Template
so you can base new spreadsheets on
it - complete with formulas,
functions, AutoSum, titles, borders,
clip art, etc.
CONDITIONAL FORMATTING:
Use Conditional Formatting to change
the appearance of values or cell
background colors.
Advanced Excel Course
Description:
FUNCTIONS:
Learn useful Functions such as:
-
Average
(average of a range of
cells)
-
Max
(highest number in a
range cells)
-
Min
(lowest number in a
range of cells)
-
Sum
(sum or total of a range
of cells)
-
Count
(count cells containing
a number)
-
CountA
(count cells containing
text or a number)
-
CountIf
(count only those cells
meeting a single
criteria - a word, a
number, etc.)
-
SumIf
(sum or total only those
cells meeting a single
criteria - a word, a
number, etc.)
-
SumProduct (count or sum
cells meeting multiple
criteria - words,
numbers, etc.)
-
If (check
to see if cells meet a
certain criteria and
assign a result - we
also cover AND OR conditions)
-
HLookup
& VLookup (type
something in a cell and
have Excel look up data
in a table)
-
Pmt (calculate payment
and interest rates)
-
Concatenate & Trim
(combine data from
multiple fields into
one)
-
Date (calculate
days/months/years
between two different
dates)
-
Learn how to "nest" one function
within another
-
Use proper use of
Commas, Colons,
Parentheses, Exclamation
Points, Brackets, etc.
Example:
=SUM([jan_sales.xls!A1:D1],[feb_sales.xls!A1:D1])
|
GOAL SEEK, SOLVER, SCENARIOS,
"WHAT-IF" TABLES:
Goal Seek allows you to
calculate an unknown value in a
given formula, but is only useful
for problems that involve finding a
single variable. When the desired
result of a calculated cell is
known, but not the input value that
calculation needs to reach that
result, you can use Goal Seek.
Solver is a tool that helps
you find solutions involving
multiple variables.
Scenarios are part of a group
of commands that can be called
what-if analysis tools. A scenario
is a set of values that a user can
create and save and then substitute
at any time in the worksheet. The
user can then switch to any of these
new scenarios to view different
results in the worksheet. To compare
several scenarios, you can create a
report that summarizes them on the
same page.
What-If Tables allow you to
analyze data and produce a table to
show the results. When a single
variable like the unit price
changes, use a one-input What-If
Table. If the unit price AND number
of units sold changes, use the
two-input What-If Table.
MACROS:
Create Absolute and/or Relative
Macros to automate common,
repetitive tasks.
PASSWORD PROTECT ALL OR PART OF
WORKBOOK:
Password Protect an entire workbook
(file) or individual cells within a
worksheet so they cannot be opened
and/or edited. |
