This course is about building a strong foundation of skills. Whether you're a beginner in Excel, or you've taught yourself how to use it, there are a set of tools - keyboard shortcuts, best practices, and calculations - that everyone should have in their toolbelt. This course strives to set the stage for the incredible, powerful features of Excel. This course is for you if:
This is a class for people who are new to Excel, or who have been self-taught. Included in this course are keyboard shortcuts, demonstration files, and exercise files to be completed on your own.
This video describes some of the topics we'll cover in this video course.
In this lesson, we launch Excel and describe the various parts of the Excel interface. Learn where we'll be going for different tools and settings throughout the rest of the course.
Open the File menu to control the 'big-picture' features of the program. Also, customize the Quick Access Toolbar to make the program easier to use.
A huge part of using Excel effectively is getting to the right spot in the spreadsheet, and selecting the right content to make changes. Learn these foundational skills to get started on the right foot.
Adding content to the spreadsheet can be done in a number of ways. Learn the simplest techniques for creating and editing information in the cells.
This lesson covers how to work with the content you've already added to the spreadsheet. Learn techniques for removing information, or stopping before you add new information.
Essential to becoming proficient, learn how to perform all the steps from the previous lessons with keyboard shortcuts instead! Warning: add these one-at-a-time - there's a lot to learn.
Besides customizing the Quick Access Toolbar, you can also customize the Ribbon of tools at the top of the screen. This makes everything more efficient, since you're moving things around to suit your workflow.
The foundation of calculating data in Excel is to refer to other cells. In this lesson, we'll use what's called a relative reference to another cell, in order to use it in a calculation.
A user-created calculation is called a formula, and it uses operators like +, -, *, and / to do simple math. This lesson is devoted to understanding how to build these formulas.
The calculations that are built-in to a spreadsheet are called functions. In this lesson, we'll use some of the most popular functions in Excel - sum, average, and more.
Creating many copies of the same calculation is a very common practice in Excel. The fastest way to duplicate your efforts is with a tool called AutoFill.
One of the most critical and misunderstood parts of calculation is the use of relative and absolute references. This lesson demonstrates the difference between them, as well as how to choose the right one for each situation.
A blend between Absolute and Relative references, the mixed reference is only used very seldom, but it can make some tasks much easier.
Besides using the location of a cell as its name, you can also select a cell or set of cells and name it. This creates a named range, and incredibly useful and flexible way of referencing cells.
Creating a named range gives you an absolute reference that is easily reusable. A large part of its power, though, is in the flexibility to change what the named range refers to. This management allows us to change an entire spreadsheet with only a few clicks.
Choosing the appropriate file type for saving is key for compatibility and speed.
If you have coworkers or clients who use older copies of Microsoft Excel, you'll need to know exactly how to handle compatibility issues between the systems. This lesson covers those details.
Once you've inserted a function or formula, you'll often need to move it or duplicate it. This lesson covers those ideas.
AutoFill can be used for a number of duplications and patterns, not just with calculations. This lesson details the different types of AutoFill patterns available to us.
To manage your worksheets effectively, you need to be able to add and remove rows and columns at will.
An Excel 2016 workbook begins with a single, blank worksheet. To contain the relevant data, you may need to insert many more sheets - this lesson covers how to accomplish that.
Scrolling around a large sheet of data can be time-consuming and difficult to manage. There are two tools that make this easier - freeze panes and splitting the window.
Sometimes you add information to a column, and still need in the spreadsheet - but you don't want it to show up. That's where hiding columns comes in.
Find and Replace can be used for simple ideas, like replacing one word with another across an entire spreadsheet. It can also be used for more complex problems, like replacing formatting, references, or using wildcard characters.
This lesson covers the details of page layout, headers and footers, and sending different types of pages to the printer.
In this lesson, we cover direct formatting of cells - changing color, font, size, and much more - and discuss number formatting a little bit. Number formatting is changing a plain number into something more fitting, like Currency formatting.
Direct formatting is typically frowned upon, in lieu of using styles. These easily reusable formatting choices are controlled by the spreadsheet's theme, which we also discuss in this lesson.
Less discussed but more essential than color-and-font formatting, number formatting is how Excel takes a number value and outputs a date, time, currency, or other useful piece of information. In this lesson, we'll discuss how to use custom number formatting for information like dates and phone numbers.
A theme is used across all the Office programs - which means it can make every chart, slide, and diagram across Word, Excel, and PowerPoint look like it came from the same designer. In this bonus video, learn to create a custom theme and apply it to spreadsheets and other document types.
For dozens of tools and techniques in Excel, the structure of your data is important. This lesson covers the fundamentals of setting up your list, before sorting, filtering, charting, or other tools are used.
The list of information you type into Excel can remain as-is, but by converting it into a 'Table' in Excel, you get a ton of additional features. Explore the benefits of using this tool.
A fundamental technique in Excel is 'sorting' the records you have in an order that makes it more easily understood. In this lesson, we'll sort by number, date, text, and other options.
There are a few special circumstances that call for 'custom sorting' - learn what they are, and how to sort by color and custom lists.
A new, simple tool Excel introduced in recent versions is 'Remove Duplicates' - the ability to delete duplicated records in large lists of information.
Filtering is the act of hiding records that don't meet a specific criteria - a great tool for showing what's important to you at any given moment. The AutoFilter tool in Excel does this quickly and easily, through the use of drop-down menus.
There are a few situations that require the more powerful and flexible type of filtering in Excel - Advanced Filters. Learn how to hide records based on many criteria.
Into a long list of information, it's possible to put a series of summary functions - subtotals - that give you calculations for subsets of the data. They always begin with sorting the data, then you add the calculation that's valuable to you.
In the latest versions of Excel, Microsoft has introduced some incredibly simple-to-use features to automatically chart, color-code, or otherwise analyze your data for you. Drag-select a set of cells, and see what Microsoft recommends!
Two incredibly useful features of Excel that are similar, but not the same, are AutoFill and Flash Fill. They automatically create lists of calculations, text information, or other things that you'd otherwise need to type out by hand.
In this bonus video, learn how the Text-to-Columns feature helps us break up data that is improperly entered.
Neil Malek – Microsoft Certified Trainer, CompTIA CTT+, and Adobe Certified Expert – is founder and principal at Knack Training, a Southern California education company. He has over 15 years of experience teaching software and professional development skills, and has worked with individuals, non-profits, small businesses, and Fortune 100 companies to identify and address training needs.
Knack Training offers courses in Microsoft Office and SharePoint, the Adobe Creative Cloud, Google Drive, Android and iOS devices, and professional development topics. Neil studied Computer Science at the University of Florida, followed by becoming certified as a Microsoft Certified Technology Specialist, Microsoft Certified Professional Developer, and CompTIA Certified Technical Trainer