Introduction

Welcome! Custom Views, Subtotals, Data Tools

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

Welcome to Excel Expert Level 10. In this course we will focus on topics including custom views, the subtotal function, creating outlines, data validation, and drop down lists. We will discuss manual and outline subtotals, grouping and ungrouping data, and explore a range of data validation techniques such as custom rules, formatting, and creating drop down and cascading drop down lists. This course builds on material taught in previous beginner and expert levels and is designed to help experienced users become more comfortable with advanced Excel features. Guidance for participating in student forums is also provided.

Navigation

Prev: X10
Next: Lesson 01

Keywords

TechHelp Excel, custom views, subtotal function, data validation, drop down lists, outlines, filtered data, grouping data, ungrouping data, outline subtotal, manual outline, custom range formats, cascading drop down lists, Dget function, social security n

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

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

Today's class is going to focus on custom views, the subtotal function, creating outlines, data validation, and drop down lists.

Today's class was designed for use with Excel 2010. Most of the material covered in today's class will work with most previous versions of Excel. This is an expert level class for Excel 2010. I strongly recommend that before taking this class, you have taken all of my beginner classes 1 through 5, and the expert classes levels 1 through 9. I will be using some functions and other techniques in today's class that were covered in previous lessons, so knowing those functions will help you with today's class.

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 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. Not everyone will use these, 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 contained five different levels, which you should have taken previously. This is the tenth level of the expert series classes. 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 then finally, the developer series.

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

In lesson one, we are going to learn about custom views, which allow us to quickly jump between different sets of filtered data in our spreadsheets.

In lesson two, we are going to learn about the subtotal function, so that our calculations work with filtered data.

In lesson three, we are going to learn about another type of subtotal, called an outline subtotal.

In lesson four, we will be learning how to create a manual outline, which is grouping and ungrouping of data in our spreadsheets.

In lesson five, we are going to begin taking a look at data validation, where we can control what the users type into the cells.

In lesson six, we are continuing with data validation. We will look at some custom range formats, and we will learn how to create drop-down lists for your data.

In lesson seven, we are continuing with data validation. We are going to learn more about custom rules, and I will show you how to create a data validation rule for a social security number field.

In lesson eight, we are going to finish data validation. We will see how to make cascading drop-down lists, so you can pick a make of a car, and then only see a list of models for that manufacturer. Then we will see how to use the Dget function to look up the price of that car based on the selected make and model.

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 on 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.

Quiz

Q1. What is the main focus of this expert-level Excel class?
A. Custom views, subtotal function, outlines, data validation, and drop-down lists
B. Macros, Visual Basic programming, and automation
C. Basic spreadsheet formatting and navigation
D. Creating charts and graphs

Q2. What is one of the uses of custom views in Excel?
A. Quickly switching between different sets of filtered data
B. Formatting cells with different color schemes
C. Creating automatic charts
D. Protecting the spreadsheet with a password

Q3. How does the subtotal function benefit your calculations in Excel?
A. It works with filtered data for accurate summaries
B. It merges duplicate cells
C. It changes the color of subtotaled rows
D. It locks cells so they cannot be edited

Q4. What is the difference between a regular subtotal and an outline subtotal?
A. Outline subtotals summarize groups of data with grouping and collapsing levels
B. Regular subtotals use only the SUM function
C. Outline subtotals can only be created in the Home tab
D. There is no difference; they are the same

Q5. What feature allows you to manually group and ungroup rows or columns in a spreadsheet?
A. Manual outline
B. Conditional formatting
C. Goal seek
D. Cell references

Q6. What does data validation in Excel allow you to do?
A. Control what users can enter into specific cells
B. Link Excel to external databases
C. Protect the entire worksheet
D. Automatically create pivot tables

Q7. What is one function of a drop-down list created through data validation?
A. Restricts user input to pre-defined choices
B. Automatically calculates totals
C. Highlights duplicate values
D. Sets the font style

Q8. What is meant by cascading drop-down lists?
A. Drop-down options change based on a selection in another drop-down
B. Multiple drop-down lists appear at the same time
C. Drop-downs that collapse automatically
D. Drop-downs used to change worksheet views

Q9. When should you move up to the advanced series of classes?
A. After completing all the expert level classes
B. Right after finishing beginner classes
C. As soon as you can open Excel
D. Before learning about data validation

Q10. What is covered in the developer series of courses?
A. Programming in Visual Basic for Applications and automating Excel
B. Creating and formatting tables
C. Advanced graphing techniques
D. Data entry and basic calculations

Q11. What is the recommended method for getting the most out of the video lessons?
A. Watch each lesson through once, then replay and follow along step by step
B. Only read the transcript
C. Make changes to your own projects immediately
D. Skip to the exercises at the end

Q12. Where can students go to ask questions about the material?
A. The Excel Interactive Student forums on the course website
B. Call customer support
C. Send an email to Microsoft
D. Attend in-person classes

Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-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 Excel Learning Zone covers expert level topics for Excel 2010. My name is Richard Rost, and I am your instructor. In this lesson, we will focus on areas that will help you take your Excel skills to the next level: custom views, the subtotal function, creating outlines, data validation, and setting up drop down lists.

This class is tailored specifically for Excel 2010, but most of what I cover will apply to earlier versions as well. Since this is the tenth level of the expert series, I highly recommend completing my beginner classes 1 through 5, and expert levels 1 through 9 before you begin. The material that follows builds on functions and tools introduced in those earlier lessons, so having a solid background will make these new concepts much easier to master.

Let me take a moment to explain how my course series is structured. There are four groups: beginner, expert, advanced, and developer. The beginner series is intended for those who have little to no experience with Excel, providing a solid foundation. The expert series, which you are working through now, is meant for users with some experience who want to dig deeper into Excel's capabilities, learn powerful functions, time-saving tricks, and advanced features.

After you finish the expert classes, you can advance to the next level, which covers building macros, setting up user forms, designing templates, and working with more sophisticated tools. These topics give you even more control and polish when working with spreadsheets. The developer series goes even further, teaching you how to program with Visual Basic for Applications right inside Excel. With VBA, you can automate tasks and create programs that interact with Excel and other Office applications.

Each series is divided into different levels, much like this one. You've probably noticed that the earlier beginner series included five levels that should be completed first. Now you're in expert level 10. Every level is designed to build on the knowledge from previous lessons.

Once you complete every expert class, you should move on to the advanced series and finally, when you're ready, the developer lessons.

Next, I want to give you an overview of the topics we'll cover in today's class.

In the first lesson, we'll discuss custom views. This feature allows you to save different sets of filtered or formatted data, so you can quickly switch between them in your spreadsheets.

Lesson two is all about the subtotal function. With this, you can perform calculations that update dynamically as you apply filters to your data.

The third lesson introduces outline subtotals, a different way to calculate and summarize data, especially useful for organizing large lists.

In lesson four, you'll learn how to create manual outlines by grouping and ungrouping data in your spreadsheet, helping to make complex data sets easier to navigate.

The next set of lessons shift focus to data validation. Lesson five introduces the basics, showing you how to control what users can enter in specific cells.

Lesson six continues with data validation, exploring custom range formats and how to set up convenient drop-down lists for data entry.

In lesson seven, we'll dig deeper into creating custom validation rules, including how to set up a rule specifically for entering valid social security numbers.

Lesson eight rounds out our look at data validation by showing how to create cascading drop-down lists. For example, you could select a car make in one list and then only see relevant models in the next. I'll also show you how to use the Dget function to look up the price of a vehicle based on the choices users make.

If you have questions about any of the topics from today's lessons, I encourage you to visit the Excel Interactive Student forums. If you are watching this course online using my custom player or web theater, you should have access to the forums right next to each lesson, provided you have an internet connection. Here, you can read through the questions other students have asked, see my answers, and benefit from comments left by fellow learners. Be sure to browse these resources before starting each lesson, and don't hesitate to post any of your own questions or comments.

If you are not studying online, you can still access the student forums by visiting excellearningzone.com/forums.

To get the full benefit from this course, my advice is to relax and watch each lesson straight through first, just to get an overall feel for the material. When you replay the lesson a second time, follow along with me step by step and create the same example spreadsheet as I do. Build it right alongside me rather than trying to apply the concepts to other projects immediately. This approach will help you thoroughly learn the techniques before you start using them on your own tasks.

If you run into any problems or something is unclear, go back and watch the relevant part of the video again, or reach out to me in the student forums and let me know what you are having trouble with.

The most important thing is to keep an open mind. Excel can seem overwhelming at first, but with some practice, you will discover how powerful and user-friendly it can be.

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

Custom views for filtered data
Using the subtotal function with filtered data
Creating outline subtotals
Manual outlining with grouping and ungrouping
Data validation basics
Creating drop down lists with data validation
Custom data validation rules
Data validation for social security number fields
Cascading drop down lists
Using Dget to look up data based on selections

Article

In this tutorial, we will focus on some advanced Excel features that help streamline your workflow and enhance your ability to manage and validate data. We will cover custom views, the subtotal function, outlines, data validation, drop-down lists, and more. These tools can make your spreadsheets more dynamic and user-friendly, whether you are working alone or sharing your files with others.

Let us start with custom views. Custom views enable you to save specific display and print settings in your worksheet, so you can quickly switch between different filtered sets of data or print layouts. For example, imagine you have a large sales report and frequently need to filter by region or department. By setting a custom view for each scenario, you can switch between them with just a couple of clicks, rather than constantly reapplying your filters and print settings.

Next, we look at the subtotal function. Subtotal helps you calculate sums, averages, counts, or other aggregates in a list or table, especially when working with filtered data. It is more powerful than the standard SUM or AVERAGE functions because subtotal automatically ignores rows that have been hidden by filters. For example, if you have a list of sales data and you filter to show only one region, the subtotal function will dynamically recalculate the sum or average based only on the visible rows. You can use it like this: =SUBTOTAL(9, B2:B50). In this example, 9 tells Excel you want the sum function. There are other function numbers as well, such as 1 for average or 2 for count.

We can also create what is called an outline subtotal, which is a way of grouping data and automatically inserting subtotals and grand totals. This is very useful when you have sorted your data by a particular category, such as product or region, and want to quickly see both the totals for each group and an overall total. You can find this feature under the Data tab using the Subtotal button. Once applied, Excel will insert subtotal rows and automatically create an outline structure with plus and minus symbols in the left margin, so you can expand or collapse groups as needed to make your sheet easier to read.

In addition to automatic outlines, you can also group and ungroup data manually. Creating a manual outline gives you more control over which rows or columns are collapsed together. For example, you may have several rows of details for each customer and want to be able to hide or show these details quickly. Simply select the data you want to group, use the Group function on the Data tab, and Excel will add the familiar expand/collapse buttons. To remove a group, use the Ungroup option.

Another essential feature for managing data entry is data validation. Data validation allows you to control what kinds of information users can type into a cell. For instance, you might restrict a cell to accept only numbers between 1 and 100, or only dates within a certain range. To apply data validation, select your cell or range, go to the Data tab, and choose Data Validation. From there, you can specify the validation criteria, such as whole numbers within a range, decimal values, specific dates, or even custom formulas you write yourself. This not only prevents incorrect data from being entered, but can also provide clear instructions via input messages and alerts that help users enter valid information.

A very popular and user-friendly form of data validation is the drop-down list. With this feature, you can let users choose from a predefined list of values rather than typing them manually, which helps eliminate data entry errors and ensures consistency. For example, if you are creating a list of employee departments, you can make a list somewhere in your spreadsheet such as Accounting, Marketing, and HR, then instruct Excel to use that list in your data validation. When users click the cell, they will see a drop-down arrow and can simply select the correct department.

Sometimes you need to get even more specific with validation. For example, you might want to validate a field to ensure only a valid Social Security number is entered. Using data validation, you can write a custom formula that checks the length and format of the entry, ensuring it fits the pattern of a Social Security number.

You can also create cascading drop-down lists, where the selection in one drop-down list determines the choices available in another. For instance, selecting a car make updates the second drop-down to only show relevant models. This requires a bit of setup using named ranges and the INDIRECT function in your data validation formulas. For example, if you pick Ford, only Ford models will be offered. This makes your spreadsheets much more interactive and user-friendly.

For additional functionality, you can use the DGET function. This function searches a database range for a record that matches specific criteria and returns the value from a specified field. For example, you can use DGET to look up the price of a car based on the selected make and model from your drop-down lists.

Whenever you work with these advanced features, the key is to practice them in a sample spreadsheet first. Set up your own data, apply the features step by step, and see how each setting affects your worksheet. If you run into problems or want to experiment further, do not hesitate to play around with different options.

Remember, learning Excel is about building on each skill, one at a time. Start by familiarizing yourself with the concepts and then try applying them to your own projects. If you get stuck, refer to help resources or community forums where you can ask questions and see how others have solved similar problems. With these tools and techniques, you will be able to manage, analyze, and validate your data much more effectively. Excel is a powerful tool, and mastering these features will help you work smarter and more efficiently every day.