Excel 2010/2013 Excel 2007 Excel 2003 Tips & Tricks Excel Forum Course Index CIG Excel Book

 Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us More... What's New? Tips & Tricks Access Index Access Tips Excel Index Excel Tips Waiting List

 NEW Release: Access Advanced Level 1    dismiss

 < Previous: Excel Beginner 5 Next: Excel Expert 2 >

Excel 2010 Expert Level 1
Microsoft Excel Expert Tutorial - 68 Minutes

This is the first class in our Excel 2010 Expert Series. It picks up where the Beginner series left off. You will learn more about working with functions and formulas, including:

 - Ways to Enter Functions & Formulas   - Relative vs. Absolute References   - Named Cells & Ranges  - Referencing Values on Other Sheets  - Creating a Summary Info Sheet  - Text Functions  - Exact, Concatenate, Left, Right, Mid  - Find, Search, Substitute, Replace, and more! Order Now

If you would like a preview of what's covered in this class,

Excel 2010 Expert Level 1
 Description: Excel 2010 Expert Level 1 Versions: Microsoft Office Excel 2010 Pre-Requisites: Running Time: 68 Minutes Cost: \$19.99

This 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 concatenation.

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 absolute reference.

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 2010. 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 AVERAGE.

Complete Outline - Excel 2010 Expert Level 1

 00. Intro (7:20) 01. Working With Functions (10:06) Typing in a function Function library Grouped categories of functions AutoSum button Changing the AutoSum function Insert Function dialog CONCATENATE function 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 Name Manager 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 =SheetName!CellName Moving a Named Cell Copy, Paste Link Creating a Summary Sheet 05. Text Functions 1 (14:10) Checking if Two Strings are Equal =A1=B1 EXACT CONCATENATE =A1&B1 LEN LEFT RIGHT MID FIND Separate First and Last Names SEARCH 06. Text Functions 2 (6:23) SUBSTITUTE REPLACE TRIM 07. Review (4:27)

Keywords: microsoft excel tutorial, microsoft excel 2010 tutorial, microsoft office excel 2010 tutorial, microsoft excel 2010 training, function, function library, autosum, relative, absolute, reference, sales tax, named cell, named range, define name, name manager, value on another sheet, paste link, summary sheet, exact, concatenate, text functions, len, left, right, mid, find, search, substitute, replace, trim

Student Interaction: Excel 2010 Expert 1

 Richard on 3/19/2011:  This is the first tutorial in the Excel 2010 Expert series. It covers lots of new techniques for working with formulas and functions. We will begin by closely examining all of the popular functions in Excel, starting with the text manipulation functions in this class. - Ways to Enter Functions & Formulas - Relative vs. Absolute References - Named Cells & Ranges - Referencing Values on Other Sheets - Creating a Summary Info Sheet - Text Functions - Exact, Concatenate, Left, Right, Mid - Find, Search, Substitute, Replace, and more!
 Lynn Robbins on 10/2/2011: I very much enjoyed the Excel Expertcourse oneReply from Richard Rost:Thank you!
 Svend Christensen on 12/8/2011: Hi Rich,First of all, thank you for your excellent tutorial series.I am working with office 2007.However I can't find the paste options you refer to in this video clock counter 04:30Does this option not exist in office 2007?Thank you in advance Brgds,Svend
 Becky Young on 1/12/2012: I have 2003 version should i upgrade to 2007 or 2010?Reply from Richard Rost:Go right to 2010 unless you find a REALLY good deal on 2007. They're almost identical in function, but 2010 has some nifty new features.
 Phil Gatchell on 2/24/2012: Enjoying beginner 1 with Exel 2010.
 Phil Gatchell on 2/24/2012: I must say that Expert 1 was rather confusing, but by playing it over and over, if necessary, I will have to figure out where I can use it my finance ledgers, reports, etc. I'm sure I can use it in my quarterly and annual reports. Thanks Richard - should make those reports easier.Reply from Richard Rost:There's a lot of tough material in that class. That's why you're an EXPERT now. :) A lot of the material (like absolute references) make much more sense after you go through it a second time. Do you think more examples are needed?
 Patrick Breuer on 1/31/2013: Richard,I always thought that when you link data from one sheet to another the way you had with the Sales Tax Example (around the 4 minute marker) the link in your Summary Sheet would not refer to it as \$B\$1, rather as its named reference, SalesTaxRate.Appreciate your feedback.Patrick
 Laurie Jones on 4/12/2013: Great tips on naming cells, Richard. Thank you!
 Laurie Jones on 4/13/2013: Regarding Absolute References, is it possible to have an Absolute Reference in a Header? Thanks.Reply from Richard Rost:I'm not sure what you mean. You want to display a value from your sheet in the header?
 Laurie Jones on 4/15/2013: I want to have the name of the document in the header, but keep the information on a summary sheet for easy updating.
 Laurie Jones on 4/16/2013: I want to have the name of the document in the header, but keep the information on a summary sheet for easy updating. Is it possible to use an Absolute Reference in the header while keeping the information on a summary sheet? Thank you. Reply from Richard Rost:I'm sorry, Laurie, but I'm still not following. The reason to use an Absolute Reference is so that copy and paste operations (or AutoFill) don't CHANGE the reference to the cell as you move down a column or across in a row. It wouldn't be necessary to do this in the page header.As far as putting the "name of the document" in the header, there are element codes for the FilePath, FileName, and SheetName. You can use those or type in your own.
 Roy Gushwa on 5/9/2013: Good day. Every time I try the concatenate feature, my results always give a an error message followed by the cell changing to 0. I have tried it many times. Not sure what I might be doing wrong. Thanks.Reply from Richard Rost:Well, in order for me to tell you what you're doing wrong, I need to see your concatenation. Copy and paste your formula here so I can see it. :)
 Jodi Grunewald on 1/23/2014: Is there a place in Excel that one could print out a "cheat sheet" of functions and how to use them for a ready reference until one could do them by memory?

 You may want to read these articles from the 599CD News:
 5/4/2014 Microsoft Access Expert 20 3/5/2013 Excel Tip: Price Markups 2/3/2013 Signed Copies of my Excel Book 9/27/2012 Excel Expert 10 Handbook Ready 9/18/2012 Two New Excel Tips: Date/Time Differences 6/20/2012 Excel Tip: Sum Cells Between Two Values 6/16/2012 New Excel Tip: Paste Link 3/26/2012 Excel Tip: 2D Matrix Lookups 11/18/2011 Dashboards in Excel 10/6/2011 Excel Expert 11 On Hold

Learn

 Access index Excel index Word index Windows index PowerPoint index Photoshop index Visual Basic index ASP index Seminars More...
Customers

Online Theater
Insider Circle
Student Databases
Info

Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Help

Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services

Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order

Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact

Live Chat
General Info
Support Policy
Contact Form
Email Us