Introduction

Welcome! Protect Sheets, Paste Tips & Data Forms

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

Welcome to Excel Expert Level 8. In this course we will cover topics like protecting worksheet data and workbooks, using advanced copy and paste methods including Paste Special and the Office clipboard, exploring fill and series fill options, and setting up data entry forms in Microsoft Excel. We will also discuss the structure of my course series, how you can interact with other students in the forums, and tips for getting the most out of these lessons. This level builds on prior lessons, and is designed for experienced Excel users preparing to move toward more advanced and developer topics.

Navigation

Prev: X08
Next: Lesson 01

Keywords

TechHelp Excel, protect worksheet data, protect workbook files, advanced copy and paste, paste special options, office clipboard, fill series, data entry forms, excel data protection, password protect workbook, autofill, excel expert, excel forms, vba, ma

Subscribe to Introduction
Get notifications when this page is updated

More Information

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 some of 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 to 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 that may or may not have a lot of Excel experience. You can set them up 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 the 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.

Intro

Quiz

Q1. What is the main purpose of protecting worksheet data in Excel?
A. To ensure users only change specific information
B. To increase the speed of calculations
C. To format the worksheet background
D. To share data with external applications

Q2. What can you do to enhance the security of an entire Excel workbook?
A. Protect the workbook structure and set a password to open or modify it
B. Change the font color of the worksheet labels
C. Increase the worksheet zoom level
D. Create a backup copy every hour

Q3. What feature allows you to copy and paste data using different formats and options?
A. Paste Special
B. Merge Cells
C. Conditional Formatting
D. Subtotal

Q4. What is the Office clipboard used for in Excel?
A. To store multiple items for advanced copy and paste
B. To hold only the most recent copied item
C. To display worksheet formulas
D. To create pivot tables

Q5. What is the purpose of the Fill and Series Fill features in Excel?
A. To automatically continue a logical pattern of data
B. To display cell references
C. To hide worksheet tabs
D. To add headers to worksheets

Q6. What is a data entry form in Excel designed to do?
A. Provide a user-friendly interface for entering data into a spreadsheet
B. Protect all worksheet data from editing
C. Automatically generate charts from tables
D. Export data to other applications

Q7. Where can students ask questions about the lesson topics covered in the course?
A. In the Excel Interactive Student forums
B. By emailing Microsoft support directly
C. In the help section of Windows
D. From the printer settings menu

Q8. What is the recommended method for learning from these Excel tutorials?
A. Watch each lesson once, then replay and build the sample spreadsheet step by step
B. Read the user manual instead
C. Try to memorize every keyboard shortcut
D. Skip to the developer lessons immediately

Q9. What is the order of the course series mentioned in the video?
A. Beginner, Expert, Advanced, Developer
B. Beginner, Developer, Expert, Advanced
C. Advanced, Expert, Beginner, Developer
D. Expert, Beginner, Advanced, Developer

Q10. Why should you avoid applying what you have learned in a lesson to other projects immediately?
A. You should master the sample spreadsheet before applying the concepts to your own projects
B. You might forget the lesson material
C. Other projects may be too complex
D. The course does not cover customization

Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A

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 the Excel Learning Zone is part of my expert level Excel 2010 training. I am Richard Rost, your instructor.

In this session, I will be covering a variety of important topics to help you become more proficient with Microsoft Excel. The class begins with ways to secure your worksheet data, including how to allow users to edit only certain cells, and how to protect your entire workbook file. I will also be going over advanced copy and paste methods, including a comprehensive look at all of Excel's Paste Special options, and I will introduce you to the Office clipboard and how you can use it to work more efficiently.

Next, I will show you how to use Fill and Series Fill, which are essential tools for quickly populating your spreadsheets with patterns and sequences. Later in the course, I will teach you how to create data entry forms, providing a user-friendly interface for adding information to your workbooks. This is particularly useful if you need others to input data while keeping your spreadsheets looking professional and avoiding accidental changes.

This course was created with Excel 2010 in mind, but most of what I am teaching applies to many other versions of Excel as well. Since this is an expert level class, I strongly advise that you complete my beginner courses (levels 1 through 5) and the earlier expert levels (1 through 7) before starting this one. Many techniques in today's class build on what was shown previously.

I have structured my training into four primary tracks: beginner, expert, advanced, and developer. The beginner classes are aimed at new users, while the expert series, which this lesson is part of, covers far more detail and depth, offering in-depth tutorials, tips, and a broader look at Excel's features. Once you are comfortable with the material in the expert classes, you should continue on to the advanced lessons, where you'll learn to create macros, custom user forms, templates, and other advanced tools that grant your spreadsheets more functionality and polish. For those interested in programming Excel, my developer series delves into Visual Basic for Applications, allowing you to automate tasks and integrate Excel with other Microsoft Office applications.

Each series is divided into separate levels, and every level introduces new concepts, building on your previous knowledge. This lesson is level eight of the expert track. After you finish the expert courses, you'll move on to advanced and, finally, developer training.

Here's a more detailed breakdown of today's lessons:

In the first lesson, I will demonstrate how to lock and protect cells in your worksheet, so that users can only make changes where you allow them. Lesson two focuses on protecting the structure and content of your entire workbook, including setting passwords for opening or editing files.

The next two lessons (three and four) explore advanced copy and paste skills. You will learn the full potential of the Office clipboard and its options, to streamline your workflow. Lesson five covers the Fill function, including how to create custom series in your data.

Lesson six is all about building data entry forms, which offer a clean and simple interface for anyone who needs to add information to your spreadsheets, regardless of their Excel experience.

If you need additional help with any of these topics, you are welcome to ask questions in the Excel Interactive Student forums. When watching this course online or using my video player software, the student forum for each lesson will be visible if you have an active internet connection. In the forums, you can see questions and answers from other students, as well as my own comments. I highly recommend looking through these discussions as you go through each lesson. Post your own questions and comments as needed.

For anyone not watching online, you can still access the forums by visiting my website at excellearningzone.com/forums.

To make the most of this course, I recommend first watching each lesson from beginning to end without trying to follow along on your computer. Afterwards, watch the lesson again and recreate my examples step by step, building the same spreadsheet I do in the video. Once you have mastered the sample exercises, then you can begin to apply your new knowledge to your personal projects.

If you find yourself stuck or confused by something, watch the video again and if you are still having trouble, submit a question in the student forum.

Above all, remember to stay open to new concepts. Although Excel can appear a bit intimidating at first, once you start to understand the way it works, it becomes quite straightforward 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

Protecting worksheet cells so users can only change specific data
Protecting the workbook structure
Setting a password to open or modify a workbook
Advanced copy and paste techniques
Using all Paste Special options
Using the Office clipboard
Using Fill and Fill Series
Creating data entry forms for spreadsheets

Article

Welcome to this expert level tutorial on Microsoft Excel. In this lesson, I will guide you through several powerful features that will help you protect your data, enhance your productivity with advanced copy and paste options, utilize the Office clipboard, create series fills, and design practical data entry forms. These tools will not only secure your work but also make data management more efficient and accessible. Whether you are an experienced Excel user or looking to expand your skills, these techniques will deepen your understanding and improve your workflow.

We will begin by discussing how to protect individual cells within a worksheet. By default, every cell in a worksheet is locked, but this does not take effect until you activate protection for the sheet as a whole. If you want your users to be able to edit only specific areas, start by unlocking those cells. Select the desired range, right-click, and choose Format Cells. On the Protection tab, uncheck the Locked option and click OK. Now, go to the Review tab, choose Protect Sheet, and set up a password if you want. Once protection is enabled, only the unlocked cells can be edited, and all others are read-only. This ensures that users can only change the information you allow, keeping your formulas and key data safe from accidental modification.

Next, it is important to know how to protect your entire workbook file. You can secure the structure of your workbook to prevent others from adding, deleting, hiding, or rearranging worksheets. Go to the Review tab and select Protect Workbook. Set a password if you want additional security. In addition to workbook structure protection, you can require a password to open or modify the file. When saving your workbook, use the Save As dialog, click on Tools, select General Options, and enter passwords required to open or modify the file. This helps restrict access and editing capabilities to authorized users.

Many users are familiar with the basic copy and paste functions, but Excel offers advanced options through Paste Special. After copying a cell or range, right-click in the destination cell and choose Paste Special. With Paste Special, you can choose to paste only values, which strips away formulas, paste only formatting, formulas, or even comments. For example, if you have a calculated value that you need to capture as a static number, Paste Special Values is the tool to use. You can also perform quick mathematical operations, like adding or multiplying a constant to a range of cells by using the operation options in Paste Special.

Excel also lets you manage copied items with the Office clipboard. When you copy multiple selections, Excel stores them in the clipboard. Open the clipboard pane by clicking the launcher in the Home tab's Clipboard group. You will see up to 24 items stored here, and you can paste any item into your worksheet as needed. This feature is very useful if you need to copy and paste multiple pieces of data repeatedly without returning to the original source each time.

Another essential feature is the Fill tool, especially the Series Fill option. While Auto Fill lets you drag to extend a series such as numbers or dates, Fill enables advanced series creation. For example, if you want to fill a column with a custom sequence like odd numbers or months, select your start cell, then click Fill on the Home tab and choose Series. You can specify the type (linear, growth, etc.), the step value, and the stop value. This can also be used for quickly generating days, months, or years without typing each entry manually.

For efficient and user-friendly data entry, Excel allows you to create data entry forms. A data entry form provides a simple interface for inputting records into your worksheet without scrolling through columns or worrying about cell formatting. To use a built-in form, first set up your table headers in the first row. Then, select any cell in your data range and add the Form button to the Quick Access Toolbar if it is not already there by customizing the toolbar and choosing Form from the list of commands. Clicking Form will open a simple dialog where users can add, edit, and navigate records easily. This is especially helpful when sharing a spreadsheet with someone who may not be experienced with Excel.

If you want even more control, you can create custom forms using Visual Basic for Applications (VBA). This involves opening the VBA editor, inserting a UserForm, and customizing it with input controls like text boxes and buttons. With VBA, you can automate data entry, add validation, and create custom actions suited to your workflow. A simple example of VBA code that adds form data to a worksheet might look like this:

Sub AddData()
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
ws.Cells(lastRow, 1).Value = UserForm1.TextBox1.Value
ws.Cells(lastRow, 2).Value = UserForm1.TextBox2.Value
'Add more lines for additional fields
End Sub

With these techniques, you can protect both your worksheet and workbook, master advanced paste options, harness the Office clipboard for easier management of copied data, quickly fill series for large datasets, and provide seamless ways for users to enter data. As you develop your skills, I encourage you to practice each technique by recreating sample spreadsheets and experimenting with the options discussed. This hands-on approach is the best way to build confidence and mastery in Excel.

If you have questions as you explore these topics, reach out to Excel user communities and forums where you can find guidance and shared experiences. By becoming comfortable with these features, you will gain greater control, efficiency, and professionalism in your Excel projects. Stay persistent and patient, and remember that with regular use, Excel becomes easier to understand and far more powerful in meeting your needs.

Primary Topics

worksheet protection, workbook protection, password protection, advanced copy and paste, paste special, office clipboard, fill and series fill, data entry forms

Secondary Topics

course progression guidance, student forums