Lessons

Updates

The TRANSPOSE function has changed in 2019. You no longer have to press the dumb Ctrl-Shift-Enter like you did when this class was recorded. Yay. See this video for new stuff: Transpose

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 8
Get notifications when this page is updated

Intro

In lesson 8 of the Microsoft Excel 2010 Expert series, you will learn how to protect worksheet data and workbook files, use advanced copy and paste techniques including all the paste special options, and work with the Office clipboard. We will also cover fill and series fill features and show you how to set up data entry forms for easier spreadsheet input. This lesson builds on previous expert classes and is designed for experienced Excel users looking to expand their skills.

Transcript

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

Today's class covers several different topics. We will begin with protecting worksheet data and protecting your workbook files. We will learn some advanced copy and paste techniques and all the paste special options. We will learn about the Office clipboard. We will learn about fill and series fill. And we will learn how to make data entry forms for our workbooks.

This course was designed for Microsoft Excel 2010. However, most of the material covered in today's class is valid for any version of Excel. This is an expert level class for Microsoft Excel. I strongly recommend that you take all of my beginner classes 1 through 5, plus my other expert classes 1 through 7, before taking this course. Today's class does build on some of the techniques covered in those earlier classes.

My courses are broken up into four different groups: beginner, expert, advanced, and developer. The beginner courses are for novice users with little or no experience with Excel. 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 we will cover more functions, features, tips, and so on.

When you have mastered the expert classes, move up to the advanced lessons. You will learn how to build 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 series is designed to teach you how to program in Visual Basic for Applications with Microsoft Excel. This will allow you to create Excel-based programs for your users, automate your spreadsheets, and integrate Excel tightly with the other Office applications.

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

Now, I will take a more detailed look at what you are going to learn in today's class.

In lesson one, we are going to learn how to protect the cells in our worksheets so that users can only change specific information.

In lesson two, we are going to learn how to protect workbook files as a whole, protecting the structure of the workbook, setting a password to open or modify your workbook, and more.

In lesson three, we are going to learn some advanced copy and paste techniques.

In lesson four, we are continuing our look at advanced copy and paste. In this lesson, we are going to look at the Office clipboard and some of its options.

In lesson five, we are going to learn about Auto Fill's cousin, Fill, and I will show you how to fill a series.

In lesson six, we will see how to set up a data entry form, which is a nice, pretty interface for someone to enter data into your spreadsheet who may or may not have a lot of Excel experience. You can set them up with a data entry form.

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 videos if you have an active internet connection. Here, you will see all the questions that other students have asked, as well as my responses to them and the comments that some of the other students may have made. I encourage you to read through these questions and answers as you start each lesson. 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 the 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 forum.

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 first topic covered in this Excel 2010 Expert Level 8 course?
A. Creating pivot tables
B. Protecting worksheet data
C. Importing data from Access
D. Printing worksheets

Q2. Which feature allows you to restrict users so they can only modify specific data in the worksheet?
A. Worksheet sharing
B. Cell protection
C. Data validation
D. Conditional formatting

Q3. What is the focus of lesson two in the course?
A. Chart creation
B. Protecting workbook files and structure
C. Advanced formulas
D. Mail merge

Q4. What does the course recommend regarding the sequence of taking classes?
A. Randomly select classes as needed
B. Start with advanced, then expert, then beginner courses
C. Complete all beginner and expert classes in order before this course
D. Begin directly with this course

Q5. What is the Office clipboard discussed in lesson four?
A. A template gallery
B. An area that stores multiple copied items for use in Excel
C. A setting for print margins
D. A password management tool

Q6. Which Excel feature will you learn about in lesson five?
A. Conditional formatting
B. Table styles
C. Fill and series fill
D. Chart templates

Q7. What is the benefit of using a data entry form, as discussed in lesson six?
A. It generates automatic graphs
B. It provides a user-friendly interface for data entry
C. It manages VBA code
D. It imports external databases

Q8. According to the video, what should you do to get the most out of this course?
A. Immediately apply lessons to all active projects
B. Skip to the end of the lessons for summaries
C. Watch each lesson all the way through, then follow along and recreate the sample spreadsheets
D. Only post questions after finishing the entire course

Q9. Where can students post questions about the lessons?
A. Only via email
B. In the Excel Interactive Student forums
C. On social media
D. By calling customer support

Q10. After completing the expert series, what is the next series recommended for students?
A. Developer series
B. Advanced series
C. Beginner series
D. Data analysis series

Q11. What is the focus of the developer series in ExcelLearningZone courses?
A. Document formatting tips
B. Creating complex charts
C. Programming with Visual Basic for Applications (VBA)
D. Data import/export methods

Answers: 1-B; 2-B; 3-B; 4-C; 5-B; 6-C; 7-B; 8-C; 9-B; 10-B; 11-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 focuses on several advanced Excel topics that will help you become more proficient with your spreadsheets. As your instructor, I will be guiding you through content that is particularly relevant for users of Microsoft Excel 2010, but you will find that almost all of what we cover applies to other versions of Excel as well.

This expert-level class builds on concepts taught in my earlier courses. If you have not already completed my beginner classes (levels 1 through 5) and previous expert level courses (1 through 7), I highly recommend doing those first. We will expand on some of the skills learned in those lessons.

My curriculum is divided into four groups: beginner, expert, advanced, and developer. Beginner classes provide a solid foundation for new users. The expert courses, such as this one, cater to those who are already comfortable working with Excel and want to explore its features in more detail. In these expert classes, you will encounter more advanced functions, detailed options, and practical tips for getting the most out of the software.

Once you complete the expert series, you can move on to my advanced lessons. These focus on more complex tasks, such as creating your own macros, developing user forms, making templates, and other specialized techniques that can help bring additional functionality and a more professional feel to your spreadsheets.

For those interested in programming, my developer series teaches you how to use Visual Basic for Applications with Excel. With VBA, you can create powerful custom solutions, automate tasks, and fully integrate Excel with other Office programs.

Each of these groups is structured into levels, and each level covers new topics, building on your existing knowledge step-by-step. This particular course is level eight in the expert series.

Let me walk you through what to expect in today's class.

First, we will explore how to protect cells in a worksheet, allowing users to edit only specific data while keeping the rest secured.

Next, we will cover methods to protect your entire workbook, including safeguarding its structure and using passwords to control access and modifications.

In our third lesson, I will show you advanced copy and paste approaches. Following that, we will continue with more copy and paste features, taking a closer look at the Office clipboard and what it can do for you.

We will then examine the Fill feature, going beyond Auto Fill to see how you can fill a series efficiently.

Finally, I will demonstrate how to set up a user-friendly data entry form. This can be especially helpful if you need someone without much Excel experience to input data into your spreadsheet.

If you feel uncertain about any of the subjects we're covering, remember that you can always ask questions in the Excel Interactive Student forums. Students watching with my custom video software or through the online web theater can view the forum related to each lesson in a sidebar as long as you are connected to the internet. This is a great place to review previous questions and answers and to participate in discussions with me and your fellow students.

Even if you are not watching online, you can access the forums any time at the excellearningzone.com website.

To make the most of this course, I suggest you watch each lesson all the way through first without trying to follow along in Excel. Once you have a sense of the material, play the lesson again and go through the examples with me, step by step. It's best to recreate the demonstrated spreadsheets before trying to apply these techniques to your own projects. If you get stuck, watch the lesson again or post your questions in the forum for help.

Above all, approach Excel with an open mind. Although it might seem complex at first, you will find that it becomes much easier and even enjoyable as you gain experience.

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

Topic List

Protecting worksheet cells to restrict editing

Protecting the workbook structure

Setting workbook passwords to open or modify files

Advanced copy and paste techniques

Using the Office clipboard in Excel

Using Fill and Series Fill for data entry

Creating data entry forms in Excel