Lessons

Resources

Full Course Outline
Skills Test Quiz - Under Construction
Certificate of Completion
Post-Class Survey

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 2003 Expert 230
Get notifications when this page is updated

Intro

In this lesson, you will learn advanced techniques in Microsoft Excel, including working with multi-sheet (3D) ranges, using the Go To Special feature, and exploring powerful Find and Replace options. We will also talk about file management tools like Auto Recover, password protection, and encryption. You'll see how to use templates and custom fields to build a database for your spreadsheet summaries. This class is designed for Excel 2002, 2003, 2000, or 97 users, and builds on concepts from previous lessons such as Excel 224 and earlier.

Transcript

Welcome to Microsoft Excel 230 brought to you by ExcelLearningZone.com. I am your instructor, Richard Rost.

In today's class, we are going to talk about a lot of different topics. We are going to start by talking about multi-sheet ranges, which are 3D ranges that not only have height and width, but also have depth. You can go through multiple spreadsheets with them.

We are going to talk about the Go To Special feature. We are going to talk about advanced Find and Replace options. Now, we talked a little bit about finding and replacing in an earlier lesson. Today we are going to see all the different options you can use, and some powerful techniques with Find and Replace.

We will learn about different file options, Auto Recover, password protecting your spreadsheets, encryption, and how to create a little database to store summary information about all of your different spreadsheets using custom fields.

We are going to see how to use templates to create workbooks that you do not have to keep reformatting over and over again. If you want to do an invoice or a sales report, you just save one template and then you can create multiple copies of it based on the template.

This class follows Excel 224. You should have taken all the classes up to and including 224, but if you skipped some of them, as long as you have covered up to about Excel 104, you should be fine. There is nothing really super advanced in this class that you need to know from the last class's material; you should be all right.

In this class, I am using Microsoft Excel XP, which is the 2002 version. If you have 2003, 97, or even 2000, you should be able to follow just about everything in this lesson, except for the advanced Find and Replace. Some of that functionality was added in Excel 2000 and 2002, so if you have 97 or 95, you are not going to have these features.

If you are using Excel 2007, some of the screens are significantly different.

As always, I strongly recommend you sit back, relax, and watch the lesson through once, then follow along a second time and do the examples on your computer.

You can download all the spreadsheets that I am using from the website. I will give you the address in a second, or you can type them in yourself. Obviously, you will get more out of the class if you build the spreadsheets yourself, but if you do not feel like doing that, feel free to grab them off the website.

Here is the address: excellearningzone.com?goxl230. That is a special page just for this class.

If you are watching this class in the interactive browser, the Emichron video player, and you are online, you should notice this class page automatically loads up. Or there is a link where you can click on it and go right to this class page. You will see the sample spreadsheet files, any additional notes that I may have added, and sometimes I come up with things that I put on the website after the class is done.

You will also see some student interaction on there as well, where you can read questions that other students have posted, ask your own questions, and get involved and chat with the other students.

Of course, if you are having problems or need help, you can get a hold of me at excellearningzone.com/contact.

Quiz

Q1. What is a multi-sheet range in Excel?
A. A range that uses only the height and width of cells on one sheet
B. A named range that contains formulas from one worksheet
C. A 3D range that can cover multiple worksheets, adding depth
D. A type of formula that includes conditional formatting

Q2. Which feature allows you to easily navigate and select special types of cells in Excel?
A. AutoFilter
B. Go To Special
C. Flash Fill
D. Sort and Filter

Q3. What version of Excel introduced some of the advanced Find and Replace options discussed in the lesson?
A. Excel 95
B. Excel 97
C. Excel 2000 and 2002 (XP)
D. Excel 95 and 97

Q4. According to the video, which option allows you to recover your work if Excel unexpectedly closes?
A. Password Protection
B. File Export
C. Auto Recover
D. Track Changes

Q5. What can you use to avoid reformatting documents like invoices or sales reports every time?
A. Data Validation
B. Templates
C. Charts
D. Conditional Formatting

Q6. For which versions of Excel are most of the techniques shown in the lesson applicable, aside from advanced Find and Replace?
A. Only Excel XP (2002)
B. Excel 2003, 2002, 2000, and 97
C. Only Excel 2007 and higher
D. Only Excel 95

Q7. What is recommended as the best way to use the video tutorial?
A. Try to do everything at once during your first viewing
B. Only read the lesson transcript
C. Watch the lesson once, then follow along and do the examples again
D. Only download the sample spreadsheets

Q8. How can you get the files and additional materials used in the class?
A. Email the instructor directly
B. Only purchase them from a bookstore
C. Download them from excellearningzone.com using the provided link
D. They are not available for download

Q9. What additional feature is available if you are watching the class in the interactive browser video player?
A. Direct access to Microsoft technical support
B. Automatic loading of the class page with sample files and notes
C. Ability to edit the video content directly
D. Built-in Excel formulas editor

Q10. What kind of online interaction is encouraged for students in this class?
A. Competing in live quizzes with other students
B. Reading and posting questions, and chatting with other students
C. Posting video responses only
D. Requesting refunds after watching the lesson

Answers: 1-C; 2-B; 3-C; 4-C; 5-B; 6-B; 7-C; 8-C; 9-B; 10-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 focuses on several important features and techniques in Microsoft Excel. We begin with a discussion on multi-sheet ranges, also known as 3D ranges. These ranges extend not just across the height and width of a single worksheet but also incorporate depth by spanning multiple worksheets. This allows you to perform calculations or reference data across several sheets at once.

Next, we explore the Go To Special feature, which offers a powerful way to select specific types of data or cells within your spreadsheet quickly. Following this, we look at advanced Find and Replace options. While basic Find and Replace was introduced in an earlier lesson, today we will examine all the available options and share some powerful strategies for working with this tool.

We also cover file management topics. This includes using Auto Recover to protect your work, setting up password protection for your spreadsheets, encrypting your files for added security, and organizing information about your spreadsheets using custom database fields. These features help keep your data secure and make it easier to manage multiple Excel files.

Templates are another important topic in today's lesson. By creating and saving templates for documents like invoices or sales reports, you can save yourself the effort of reformatting these workbooks each time you need a new copy. After setting up a template once, you can generate new documents from it at any time.

This class is designed to follow Excel 224. Ideally, you should have completed all the previous classes up to 224. However, if you have at least covered the content through Excel 104, you should have enough background to keep up with the material in this class. Most of the advanced techniques from the last lesson are not required here.

During the lesson, I am using Microsoft Excel XP, which is the 2002 version. If you are using Excel 2003, 2000, or 97, nearly everything presented here will apply, though some advanced Find and Replace functions are only available starting with Excel 2000 and 2002. If you are using Excel 97 or 95, those features may not be present. For those working with Excel 2007 or newer, keep in mind that some screens might look quite different, but the core functionality remains similar.

As always, I recommend watching the lesson through once without interruption, then replaying it while performing the examples on your own computer. This approach will help reinforce what you learn.

All of the spreadsheets demonstrated in class can be downloaded from the website, or you can enter the data yourself. Although building the spreadsheets from scratch yourself will help you remember the material better, the files are available for anyone who prefers to download them.

The specific page for this class is located at excellearningzone.com?goxl230.

If you are viewing this lesson with the Emichron video player while online, the class page should appear automatically or be available via a quick link. That page contains the sample files, any additional notes or resources uploaded after the lesson, and a space for interacting with other students. You will find posted questions, answers, and opportunities to discuss the material with classmates.

If you have questions or need help, you can always contact me through the contact page at excellearningzone.com.

A complete video tutorial with step-by-step instructions and demonstrations for everything covered here is available on my website at the link below. Live long and prosper, my friends.

Topic List

Multi-sheet ranges (3D ranges)

Using Go To Special feature

Advanced Find and Replace options

File options overview

Setting up Auto Recover

Password protecting spreadsheets

Encrypting Excel files

Creating a database for spreadsheet summaries

Using custom fields for spreadsheet information

Working with Excel templates to streamline workbook creation