Lessons

Resources

Questions?

Please feel free to post your questions or comments below. If you have questions about a specific lesson, please specify the lesson number and the time index in the lesson where the issue occurs. Thank you!

Subscribe for Updates

If you would like to be notified whenever changes are made to this page, new lessons are posted, or comments are made, click on the subscribe button.

Subscribe to Excel Expert 1
Get notifications when this page is updated

Intro

In this lesson, you will learn expert-level techniques for working with formulas and functions in Microsoft Excel 2010. We will cover how to use the function library, explore the differences between relative and absolute references, and work with named cells and ranges using the Name Manager. You will also learn how to reference values on other worksheets and start working with important text functions like EXACT, CONCATENATE, LEFT, RIGHT, MID, LEN, FIND, SEARCH, SUBSTITUTE, REPLACE, and TRIM. This is the first level of the expert series for Microsoft Excel 2010.

Transcript

Welcome to Excel 2010 Expert, Level 1, brought to you by ExcelLearningZone.com. I am your instructor, Richard Rost.

In today's class, we are going to begin by learning a lot more about formulas and functions, including the different function libraries that are available. We will learn about the difference between relative and absolute references in our cells. We will learn how to create named cells and named ranges and work with the Name Manager. We will learn how to reference values on other worksheets. We will also begin looking at all the different functions that are available in Excel.

In today's class, we are going to start with the text functions, including EXACT, CONCATENATE, LEN, LEFT, RIGHT, MID, FIND, SEARCH, SUBSTITUTE, REPLACE, and TRIM. And that's just the beginning.

This class is designed to be used with Excel 2010, part of Microsoft Office 2010. If you are using an older version of Excel, you should visit my website at ExcelLearningZone.com and look for my older tutorials covering Excel 2000 through 2007.

If you are using Excel 2007, you should have little difficulty following along, as most of the concepts covered in this course are the same. If you are using an older version like 2003 or earlier, you really should either upgrade to Excel 2010 or purchase a tutorial for your version of Excel, which again you can find on my website.

This is an expert-level course for Microsoft Excel 2010. This class was designed for the user who has a good amount of experience with Excel and has completed all five courses in my beginner series. If you have not taken my beginner courses, I strongly recommend you at least go to my website and look over the outlines for them and make sure that you understand all the concepts taught in those classes.

Level 1 covers all the basics: how to move around in Excel, use the different menus, and enter and edit data. Level 2 is mostly about formatting data, cell formats and such. Level 3 introduces you to functions and inserting illustrations into your spreadsheets. Level 4 covers charts and tables, making graphs and such. Level 5 includes lots of miscellaneous topics like inserting text, working with headers and footers, borders, and more.

If you start working with today's class and you find yourself not understanding any of the terminology, again I strongly recommend you go back and take the beginner classes.

My courses are broken up into four different groups: beginner, expert, advanced, and developer. My beginner courses are for novice users who have little or no experience with Microsoft Excel. They are designed to give you an overview of the basic features and cover just what you need to know to be productive.

The expert series, which is what you are watching right now, is designed for more experienced users who are already comfortable with Excel. Expert classes go into a lot more depth about each topic than the beginner classes did and will cover more functions, features, tips, and techniques for power users.

After you have mastered the expert classes, move up to the advanced lessons. You will learn how to record macros, build user forms, create your own templates, and many more advanced features that not everyone will use, but they really add enhanced functionality and professionalism to your spreadsheets.

Finally, my developer-level courses will teach you how to program in Visual Basic for Applications for Microsoft Excel. This will allow you to create Excel-based programs for your users, automate your spreadsheets, and integrate Excel tightly with the other Microsoft Office applications.

Each of my series is broken down into different levels. For example, the beginner series contained five different levels, which you should have taken previously. This class is the first level of the expert series. Each level teaches you new and different topics in Microsoft Excel, building on the lessons learned in the previous levels. When you have finished all the expert classes, move up to the advanced series, and finally, the developer series.

Now let us take a more detailed look at exactly what we are going to learn in today's class.

In lesson one, we are going to learn more techniques for working with functions and formulas. We will learn about the function library, the different groups of functions, the AutoSum feature, and string concatenation.

In lesson two, we are going to learn the important difference between relative and absolute references.

In lesson three, we are going to learn about named cells and ranges, and how to use the Name Manager.

In lesson four, you will learn how to work with values on other sheets. You will learn about sheet name and cell name notation, how to copy and paste a link to a cell on a different sheet, and how to create a summary sheet to gather information from the rest of the sheets in your workbook.

In lesson five, we will begin working with text functions. We will learn about EXACT, go over CONCATENATE again, learn LEFT, RIGHT, MID, the LENGTH function, FIND and SEARCH, and I will show you how to separate first and last names if you have them in the same column.

In lesson six, we are going to finish up with text functions. We are going to learn SUBSTITUTE, REPLACE, and TRIM.

If you need help with the topics covered in today's lessons, please feel free to post your questions in the Excel interactive student forums. If you are watching this course using my custom video player software or online in my web theater, you should see the student forum for each lesson appear in a small window next to the class video. If you have an active internet connection, you will see all of the questions that other students have asked, as well as my responses to them and any other comments that other students have made. I encourage you to read through these questions and answers as you start each lesson and to post your own questions and comments as well.

If you are not watching your lessons online, you can still visit the student forums later by visiting ExcelLearningZone.com/forums.

To get the most out of this course, I recommend you sit back, relax, and watch each lesson completely through once without trying to do anything on your computer. Then, replay the lesson from the beginning and follow along with my examples. Actually create the same spreadsheet that I make in the video. Build a spreadsheet with me step by step.

Do not try to apply what you are learning right now to other projects until you have mastered the sample spreadsheet. If you get stuck or do not understand something, watch the video again from the beginning or tell me what is wrong in the student forums.

Most importantly, keep an open mind. Excel might seem intimidating at first, but once you get the hang of it, you will see that it is really easy to use.

Quiz

Q1. What is the primary focus of Excel 2010 Expert, Level 1?
A. Formatting cells and simple data entry
B. Learning advanced formulas, functions, and referencing techniques
C. Creating PowerPoint presentations
D. Introduction to Access databases

Q2. Which of the following topics will be covered in today's class?
A. Creating animations in Excel
B. Working with formulas and functions, named ranges, and referencing values across worksheets
C. Building custom macros and user forms
D. Programming in Visual Basic for Applications

Q3. What is the difference between relative and absolute references in Excel?
A. Relative references always stay the same; absolute references change with movement
B. Relative references change when copied; absolute references remain fixed
C. Absolute references are limited to formulas; relative references are not
D. There is no difference

Q4. Which of the following is NOT a text function mentioned in the introduction?
A. VLOOKUP
B. CONCATENATE
C. LEN
D. LEFT

Q5. The Name Manager in Excel allows you to:
A. Change sheet colors
B. Manage named cells and ranges
C. Automate repetitive tasks
D. Edit cell formats

Q6. What is the recommended approach if you do not understand the material in the video?
A. Ignore it and continue
B. Go back and review the beginner courses and outlines
C. Immediately jump to the advanced course
D. Contact Microsoft for technical support

Q7. Which lesson will teach you about referencing values from other sheets?
A. Lesson one
B. Lesson two
C. Lesson three
D. Lesson four

Q8. What sequence does the instructor recommend for getting the most out of the course?
A. Immediately try on your own without watching the video
B. Watch the lesson once, then follow along replaying the examples in Excel
C. Only listen to audio
D. Skip directly to projects

Q9. After completing all expert-level classes, students should:
A. Take the developer course directly
B. Move up to the advanced series for more features like macros and templates
C. Retake expert-level lessons
D. Only learn formulas and skip functions

Q10. Where can students interact and ask questions about lessons?
A. In the Excel program itself
B. On ExcelLearningZone.com forums and in the online course player
C. In Word documents
D. Only via email

Q11. What type of user is the expert-level series intended for?
A. Someone with no prior Excel experience
B. Users comfortable with basic Excel concepts seeking more depth
C. Professional programmers only
D. Anyone using Microsoft Access

Q12. Which function is used to combine text in Excel?
A. CONCATENATE
B. FIND
C. TRIM
D. SUM

Q13. If you are using Excel 2003 or earlier, what does the instructor recommend?
A. Continue with Excel 2010 Expert Level 1
B. Upgrade to Excel 2010 or find a course specific to your version
C. Do not use Excel at all
D. Start learning PowerPoint instead

Answers: 1-B; 2-B; 3-B; 4-A; 5-B; 6-B; 7-D; 8-B; 9-B; 10-B; 11-B; 12-A; 13-B

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Excel Learning Zone covers the first level of the Excel 2010 Expert course. My name is Richard Rost, and I will be guiding you through the material.

We will start this class by diving deeper into formulas and functions. There are several different function libraries in Excel, and I will introduce you to how these work. We will also explore the important distinction between relative and absolute references in cell addresses, which is essential for working efficiently with formulas.

Throughout this course, you will learn how to create named cells and named ranges, and we will look at the Name Manager tool. I will show you how to refer to values from different worksheets within the same workbook, opening up even more ways to organize and analyze your data. We will also begin working with some of the wide range of functions available in Excel.

Our main focus in this class will be on text functions. I will teach you how to use functions such as EXACT, CONCATENATE, LEN, LEFT, RIGHT, MID, FIND, SEARCH, SUBSTITUTE, REPLACE, and TRIM. These functions allow you to manipulate and analyze text data in your spreadsheets, and we will be covering each one in detail.

This class is built around the features in Excel 2010. If you are using Excel 2007, you should find that most of the lessons apply to your version as well. However, if you are using an older version like Excel 2003 or earlier, I suggest upgrading to a newer version or checking my website for tutorials that cover your specific version.

This expert-level course is intended for users who are already comfortable with the basics of Excel and have completed my five-part beginner series. If you have not yet gone through those beginner classes, I encourage you to review their outlines on my website and make sure you understand the foundational concepts before starting this course.

To recap, the beginner series covers the following: Level 1 focuses on basic navigation and data entry; Level 2 goes into formatting; Level 3 introduces functions and working with illustrations; Level 4 deals with creating charts and tables; and Level 5 discusses additional topics such as inserting text, headers and footers, and borders.

If you are unfamiliar with any of the terms or topics in today's class, please consider reviewing the beginner courses.

My complete curriculum is divided into beginner, expert, advanced, and developer levels. The beginner level is best for those new to Excel, focusing on the key features you need to be productive right away. The expert series, which this course begins, is for more experienced users who want to dig further into Excel's features. These lessons go further into functions, tips, and power user techniques.

Once you have finished all the expert courses, you can move on to the advanced series, which covers recording macros, building user forms, creating templates, and more advanced topics that add sophistication to your spreadsheets. Finally, the developer series will teach you how to program with Visual Basic for Applications in Excel, giving you the skills to automate your spreadsheets and connect them with other Office applications.

Each stage in my series builds on the material from the previous levels. The beginner course has five levels, which I recommend completing first. This expert class is the first in its own series, and each subsequent level introduces more advanced topics. When you finish the expert section, continue with the advanced series, and ultimately, the developer series.

Let us review what you will learn in today's class step-by-step:

In lesson one, I will show you additional techniques for working with functions and formulas, including an overview of the function library, function groups, using the AutoSum tool, and ways to join text strings together.

Lesson two is dedicated to understanding the difference between relative and absolute references, which is crucial for copying formulas accurately.

Lesson three will focus on naming cells and ranges and using the Name Manager feature.

In lesson four, you will learn how to work with data located on other sheets within the same workbook. We will cover how to use sheet and cell naming, copying and pasting links between sheets, and setting up a summary sheet that compiles information from multiple sheets.

Lesson five focuses on text functions, covering functions like EXACT, CONCATENATE, LEFT, RIGHT, MID, LEN, FIND, and SEARCH. I will also show you how to separate first and last names when they are stored in the same column.

In lesson six, we will finish our exploration of text functions with SUBSTITUTE, REPLACE, and TRIM.

If you need extra help with anything we cover today, I encourage you to use the Excel interactive student forums. If you are watching this course using my custom video player or my web theater, a student forum will appear next to the video so you can ask questions and read through the discussions from other students and see my replies. Even if you are not watching the lessons online, you can access these forums later by visiting my website.

To get the most from this course, I recommend watching each lesson all the way through first before you try anything on your own computer. After watching, play the lesson again and work through the examples step by step with me. Build the same sample spreadsheet that I build in the video. This will help reinforce what you have learned.

Try to avoid applying new ideas to your personal projects until you are comfortable with the examples from the class. If something does not make sense, rewatch the lesson or reach out to me in the forums.

Most importantly, keep an open mind while working with Excel. It can feel overwhelming at first, but once you learn the basics, you will find that Excel is actually quite straightforward and powerful.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

Function library overview in Excel 2010
Using different groups of functions
AutoSum feature explained
String concatenation techniques
Relative vs absolute cell references
Creating and using named cells
Defining and managing named ranges
Working with the Name Manager
Referencing values on other worksheets
Sheet name and cell name notation
Copying and pasting links to other sheets
Creating a summary sheet from multiple sheets
EXACT function for text comparison
Using CONCATENATE for joining text
LEFT, RIGHT, and MID text functions
LEN function for text length
FIND and SEARCH functions
Separating first and last names in one column
SUBSTITUTE function for text replacement
REPLACE function for modifying text
TRIM function for removing extra spaces