course is for the expert user who
has good experience with Microsoft Excel or has completed the five
courses in our
Beginner Series. This course will teach you more about working with
functions and formulas. We'll start out by learning about
the different function libraries, multiple ways to create functions, the
AutoSum and Insert Function buttons, and how to perform string
Next, we'll learn about relative vs.
absolute references, why they're important, and when you should use
them. You'll see how absolute references behave when data is copied or
you use the AutoFill function. We'll calculate sales tax using an
You will then learn how to create
Named Cells and Named Ranges. These are handy so you don't
have to keep remembering which cell a particular value is in. You'll
learn how to use the Name Manager, and see how you can quickly
jump around between your defined named cells - a great technique for
working with large, complicated sheets.
You will learn how to refer to values
on other sheets. This way you can share data between the various
sheets in your workbook. You will learn about proper Sheet!Cell
notation. You will learn how to use Copy > Paste Link to quickly
create links between your spreadsheet data. We'll use all of these
techniques to build a sales summary sheet.
The previous lessons were to prepare you
for working with formulas in functions in Excel. Over the next couple of
course levels, we'll be examining most of the popular functions in
Excel. In today's class, we'll begin by looking at the functions to
manipulate and work with text.
We'll start out by learning: EXACT,
CONCATENATE, LEN, LEFT, RIGHT, MID, FIND, and SEARCH. We'll use these
functions to separate the first name and last name from a
column of cells where the user has entered them together.
Finally, we'll cover the functions
SUBSTITUTE, REPLACE, and TRIM. You'll see how to use these functions to
make replacements of text inside of cells.
Again, this is the perfect class
for anyone who wants to enhance their skills with Microsoft Excel.
If you have been working with Excel for a while and want to increase
your knowledge of Excel functions and formulas, then this
is the perfect class to start with. You should have good working
knowledge of the basics of Excel, as taught in my Beginner series. You
should also know how to use some of the simple functions, like SUM and
course, if you have any questions about whether or not this class is
for you, please contact me.
Complete Outline - Excel
Expert Level 1
00. Intro (7:20)
01. Working With Functions (10:06)
Typing in a function
Grouped categories of functions
Changing the AutoSum function
Insert Function dialog
Argument Select Buttons
Concatenate with the & sign
02. Relative v Absolute References (8:53)
What is an Absolute Reference?
What is a Relative Reference?
Reference behavior with AutoFill
Sales Tax Rate example
F4 keyboard trick
03. Named Cells and Ranges (7:03)
What is a Named Cell
Create a Name with the Name Box
Jump to a Named Cell
Define Name button
Edit, Delete Names
Create a Named Range
Named Range example with SUM
04. Values on Other Sheets (10:05)
Refer to a Cell on Another Sheet
Moving a Named Cell
Copy, Paste Link
Creating a Summary Sheet
05. Text Functions 1 (14:10)
Checking if Two Strings are Equal
Separate First and Last Names
06. Text Functions 2 (6:23)
07. Review (4:27)