Introduction

Welcome! Formulas, Functions & Named Ranges

S M L XL FS | Slo Reg Fast 2x | Bookmark

Welcome to Excel Expert Level 1. In this course we will focus on formulas and functions in Microsoft Excel, including how to use the function library, understand the difference between relative and absolute references, and create named cells and ranges with the name manager. We will discuss referencing values on other worksheets, introduce various text functions such as exact, concatenate, len, left, right, mid, find, search, substitute, replace, and trim, and cover the structure of the course series. We will also discuss how to use the student forums to get help and suggestions for practicing what you learn.

Navigation

Prev: X01
Next: Lesson 01

Keywords

TechHelp Excel, Excel 2010 formulas, Excel 2010 functions, function library, relative vs absolute references, named ranges, name manager, reference other worksheets, text functions, concatenate, len, left, right, mid, find, search, substitute, replace, tr

Subscribe to Introduction
Get notifications when this page is updated

Intro

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 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 is 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 and 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 is lots of miscellaneous topics like inserting texts, working with headers and footers, borders and much 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's 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, we will learn about the autosum feature, and we will learn about 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, 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, we will go over concatenate again, we will 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, here 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 feel free 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 the expert level, level 1 class in this course?
A. Learning advanced chart design
B. Learning more about formulas, functions, and text functions
C. PivotTables and PivotCharts
D. Macro programming

Q2. What is the difference between relative and absolute references in Excel?
A. Relative references change when copied; absolute references stay fixed
B. Absolute references change when copied; relative references stay fixed
C. Both change when copied
D. Neither change when copied

Q3. What can the Name Manager be used for in Excel?
A. Formatting charts
B. Managing named cells and ranges
C. Creating PivotTables
D. Editing conditional formatting

Q4. Which of the following is NOT specifically mentioned as a text function taught in this class?
A. SUBSTITUTE
B. LEFT
C. VLOOKUP
D. CONCATENATE

Q5. If you need help with a lesson, what resource is recommended by the instructor?
A. Contacting Microsoft support directly
B. Student forums on the training website
C. Searching Google
D. Consulting a printed manual

Q6. What is one recommended way to get the most out of this course?
A. Only read the course transcript
B. Watch the lesson completely through first, then follow along and build the sample spreadsheet
C. Try to apply the skills to a completely unrelated project immediately
D. Skip lessons you already think you know

Q7. What should you do if you find yourself not understanding the terminology in the expert class?
A. Ignore it and keep going
B. Take the advanced class instead
C. Revisit the beginner course outlines or take the beginner classes
D. Look up terms in Google every time

Q8. Which group of users are the expert-level courses specifically designed for?
A. Users new to Microsoft Excel with no experience
B. Experienced users who are already comfortable with Excel
C. Users wanting to immediately learn VBA programming
D. People only interested in basic data entry

Q9. What is a summary sheet in Excel used for?
A. Formatting text
B. Creating PivotTables
C. Gathering information from other sheets in your workbook
D. Sorting data alphabetically

Q10. What should you do if you get stuck or do not understand something?
A. Skip the lesson and move to the next one
B. Watch the video again or ask for help in the student forums
C. Try to guess and move on
D. Only rely on the textbook

Q11. After completing all expert-level classes, what does the instructor recommend next?
A. Start over from the beginning
B. Move up to the advanced lessons
C. Take a break from learning Excel
D. Focus on other Office applications

Q12. Which of the following tasks is NOT specifically mentioned as being covered in the beginner courses?
A. Moving around in Excel and editing data
B. Formatting cells
C. Building user forms
D. Working with charts and tables

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

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 is the first level in my Excel 2010 Expert series. My name is Richard Rost and I will be your instructor as we explore more advanced features of Microsoft Excel.

In this class, our focus is on developing a stronger understanding of formulas and functions, including how to use the different function libraries available in Excel. I will explain how relative and absolute references work in formulas, and show you how to create and manage named cells and ranges using the Name Manager. You will also learn the techniques for referencing data from other worksheets within your workbook.

We will start diving into the various functions in Excel, beginning with text functions such as EXACT, CONCATENATE, LEN, LEFT, RIGHT, MID, FIND, SEARCH, SUBSTITUTE, REPLACE, and TRIM. This is just the start of what this course will cover.

This course has been designed specifically for Excel 2010, which is part of the Microsoft Office 2010 suite. If you are using an earlier version of Excel, like anything from 2000 to 2007, I recommend checking my website, excellearningzone.com, where you can find tutorials tailored to those versions. Most of the core concepts I teach in this class will still apply to Excel 2007. However, if you are working with Excel 2003 or earlier, I strongly suggest either updating to Excel 2010 or purchasing a tutorial designed for your version.

Because this is an expert-level course, it is intended for students who have solid experience with Excel and have completed my five beginner courses. If you have not yet worked through my beginner series, I advise taking a look at the outlines on my website to make sure you have a good understanding of all the essential basics covered there.

To recap, Level 1 of the beginner series introduces how to navigate Excel, use the menus, and work with data. Level 2 is all about formatting and working with different cell formats. Level 3 covers the basics of functions and how to insert illustrations. Level 4 focuses on creating charts and tables, and Level 5 addresses a variety of additional topics such as inserting text, headers, footers, borders, and more.

If at any point in this course you find the content confusing or the terminology unfamiliar, it is a good idea to revisit the beginner material.

Let me give you a quick rundown of how my courses are structured. The beginner series is for those brand new to Excel and covers just what you need to get productive. The expert series, which you are now watching, goes much deeper and is ideal for those who already feel comfortable with Excel and want to become power users. After completing all of the expert classes, the next step would be the advanced series, where you will learn how to record macros, create user forms, build your own templates, and other advanced features that can really enhance your spreadsheets. Then, for those interested in automation and programming, my developer courses will show you how to use Visual Basic for Applications to build custom solutions, automate your spreadsheets, and integrate Excel with other Microsoft Office programs.

Each series, like the beginner or expert tracks, is divided into different levels. For example, the beginner series has five levels. This video is the first level in the expert sequence, and every level introduces new topics that build on what you have already learned. When you finish all expert classes, you can proceed to the advanced and developer courses.

Here is a breakdown of what we will cover in each lesson of this course. In lesson one, we will cover more techniques for working with formulas and functions, explore the function library, discuss different groups of functions, look at the Autosum feature, and learn about string concatenation. Lesson two focuses on the vital difference between relative and absolute references in Excel. Lesson three is all about named cells and named ranges and how to use the Name Manager. In lesson four, you will see how to work with values from other sheets using sheet and cell referencing, how to paste links to cells in different sheets, and how to create a summary sheet to collect information from your workbook. Lesson five will get you started with text functions like EXACT, CONCATENATE, LEFT, RIGHT, MID, LEN, FIND, and SEARCH, plus how to split first and last names if they are combined in one cell. Lesson six wraps up text functions by teaching SUBSTITUTE, REPLACE, and TRIM.

If you have questions about anything covered in today's lessons, you are welcome to post in the Excel Interactive Student Forums. If you are following the course using my video player or web theater, you will find the student forum for each lesson displayed beside the class video - provided you are connected to the internet. Here, you can see questions from other students, my responses, and additional comments. Even if you are not using the online player, you can always access the forums directly through excellearningzone.com/forums.

To get the most value out of this course, I recommend that you first watch each lesson all the way through without trying to follow along in Excel. Then, replay the lesson and build the same examples with me step by step. Work through the exercise spreadsheet thoroughly before applying these concepts to your own projects. If you run into difficulty or are confused by something, replay the lesson or post your issues in the forums.

Most importantly, keep an open mind. Excel may appear complex at first, but as you work with it, you will find that it becomes much easier to use.

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

Formulas and functions overview
Using the function library
Groups of functions in Excel
Autosum feature
String concatenation
Relative and absolute references
Creating named cells
Creating named ranges
Using 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
Text functions overview
Using the EXACT function
Using the CONCATENATE function
Using the LEFT function
Using the RIGHT function
Using the MID function
Using the LEN function
Using the FIND function
Using the SEARCH function
Separating first and last names in columns
Using the SUBSTITUTE function
Using the REPLACE function
Using the TRIM function

Article

Welcome to this tutorial on advancing your skills in Microsoft Excel. In this article, we will explore formulas and functions in greater detail, learn how to work with named cells and ranges, understand the important concept of relative and absolute references, and use a variety of text functions that can enhance your productivity and help organize your data more effectively.

To begin, it is important to have a solid foundation in Excel basics, such as moving around the interface, entering and editing data, and understanding formatting and charts. If you are comfortable with these areas, you are ready to dive deeper into formulas, references, and more advanced features.

One of the central tools in Excel is the function library. This library places a wide variety of pre-built functions at your fingertips. You can find mathematical functions, text manipulation tools, logical operators, and many more. For example, the Autosum feature lets you quickly add up a list of numbers by clicking the Autosum button, saving you from having to type out a formula like =SUM(A1:A10) by hand.

Let us start with a simple example using text string concatenation. Concatenation is the process of joining text from two or more cells into one. Previously, you might have written =A1 & B1 to join the contents of two cells. There is also a CONCATENATE function available, which works like this: =CONCATENATE(A1, B1). If A1 contains "John" and B1 contains "Smith," the formula would result in "JohnSmith." If you want to add a space between the first and last name, your formula would be =A1 & " " & B1, which returns "John Smith."

Understanding the difference between relative and absolute references in formulas is critical when working with spreadsheets. A relative reference changes as you copy a formula to other cells. For example, if a formula in cell C1 is =A1+B1 and you copy it down to C2, it automatically becomes =A2+B2. An absolute reference stays the same even when you copy it. This is indicated by the dollar sign, such as $A$1. No matter where you copy the formula, a reference to $A$1 will always point to cell A1. You can also create mixed references, such as $A1 or A$1, to lock either the column or the row.

Named cells and ranges allow you to assign a meaningful name to a cell or group of cells. Suppose you have the value 500 in A1, and you give that cell the name "Sales." Anywhere in your workbook where you use =Sales, Excel will treat it as if you wrote =A1. To manage all your names, use the Name Manager, which lets you create, edit, or delete named ranges. Names make formulas easier to read and maintain, especially in complex spreadsheets.

Often, you will need to reference values that are on other sheets within your workbook. To reference a value from another sheet, the syntax is straightforward. For example, if you want to reference cell A1 on a sheet called Budget, your formula would be ='Budget'!A1. You can also copy and paste a cell as a link. Right-click on a cell and use "Paste Special" and select "Paste Link" to create a formula pointing back to the original cell. You can build summary sheets that collect information from various sheets to present an overview in one location.

Text functions are another essential part of working efficiently in Excel. The EXACT function checks whether two text values are exactly the same, returning TRUE if they are and FALSE if not. This is useful for comparing values when case sensitivity matters. The LEFT, RIGHT, and MID functions help you extract specific parts of a text string. For example, =LEFT(A1,4) will give you the first four characters from the text in A1. Similarly, =RIGHT(A1,3) returns the last three characters, and =MID(A1,2,5) returns five characters starting from the second character.

The LEN function tells you the length of a text string. For example, =LEN("Excel") will return 5. FIND and SEARCH help you locate where one substring appears within another text string. FIND is case-sensitive, while SEARCH is not. For example, =FIND("n","Finance") returns 3 because n is the third character. SUBSTITUTE allows you to replace occurrences of specific text within a string, such as =SUBSTITUTE(A1,"old","new"). REPLACE lets you substitute characters at a specific starting position for a certain number of characters, such as =REPLACE(A1,2,3,"yes") to replace three characters starting from position two. TRIM is very useful for cleaning up text, as it removes extra spaces from a string except for single spaces between words.

Suppose you have a list where first and last names are in the same column, like "John Smith" in cell A1, and you want to split them apart. You can use the FIND function to locate the space, then use LEFT to get the first name and MID to get the last name. For example, =LEFT(A1,FIND(" ",A1)-1) gives you "John," and =MID(A1,FIND(" ",A1)+1,LEN(A1)) gives you "Smith."

If you are interested in automating tasks, Excel allows you to use Visual Basic for Applications, or VBA, to write code that automatically manipulates your spreadsheets. Here is a very simple code snippet that puts "Hello World" in cell A1:

Sub HelloWorld()
Range("A1").Value = "Hello World"
End Sub

You can access the VBA editor by pressing Alt+F11 in Excel.

As you work with these lessons, a good approach is to try out each example exactly as shown to gain hands-on experience. Once you are comfortable, you can start modifying the examples to fit your own needs. If you get stuck or do not understand something, reviewing the steps and practicing will help reinforce the concepts.

Excel is a powerful tool, and once you master these concepts, you will find that working with formulas, references, and functions can dramatically improve your ability to analyze and present data. Remember, learning new features and functions takes a bit of patience, but with practice, Excel becomes a natural and incredibly useful part of your workflow.