Excel 2021 Full Course: Beginner to Advanced

Description

Who it's for: Beginners - experienced, everyday users looking to sharpen their skills and increase efficiency in Microsoft Excel.

What it is: Microsoft’s spreadsheet application used to clean and organize data.

What you'll learn: Feel like you're the only person who doesn't know Excel and worried about getting up to speed? You're not alone, and we can help! Our Excel Introduction class leads you, step-by-step, through the basics of this popular spreadsheet program. There's plenty of time for practice and hands-on exercises to cement your new knowledge.

As we go through the training, you'll recognize — and finally understand — the very same features you've seen in Excel spreadsheets at work. By the end of this class you'll be making those kinds of spreadsheets yourself, documents such as:

• Lists. These could be lists of employees, products, customers, or anything else... Lists take advantage of Excel's formatting in rows and columns. You'll learn how to format and freeze table headers; change fonts, sizes and colors; and set printing specs.

• Tabulations. Excel is great at number-crunching and offers a slew of arithmetic functions you can apply to your data, like averages, sums, and counts.

• Computations. Typically financials, these could be budgets, income statements, invoices, tax computations or similar. These spreadsheets contain values that are tied together by formulas so that changing one instantly changes the others: for example, the same way changing revenue immediately flows through to a change in profits. The key to creating these tied-together spreadsheets is learning how to correctly reference the spots where data is kept ("cells") using formulas in other cells.

• Advanced Tables. Beyond organizing data in rows and columns, we'll show you how to create filters that display only certain rows: for example, all the rows where the "City" column is set to "New York." You'll also learn how to sort entire tables based on values in one or more columns: for example, sorting customers by annual revenue. You'll learn how to instantly colorize, stripe, filter and subtotal a simple table of data using Excel's ever-popular "Format As Table" feature.

• Charts. Excel has 10 major chart types, everything from basic line charts to scatter plots. We'll show you how to add impressive data visualizations to your spreadsheets: line charts, bar graphs, pie charts, and more.

• Pivot Tables. One of Excel's most powerful — and least understood — features, pivot tables auto-magically convert static rows and columns of data into a responsive table that will re-organize itself to instantly display subtotals, averages, or other aggregate functions — depending on which fields you choose. Instead of creating 3 different tables to see sales by customer rep, customers by city, and revenue by state, you'll learn how to create a single pivot table to display all 3 sets of information in one place, based on your selection of different field groupings. You'll also learn how to generate pivot charts from the data.

• Tips & Tricks. The training also covers a few techniques we've selected as the most useful in your journey toward Excel mastery. These include "conditional formatting": coloring cells based on their values (e.g., heat maps); removing duplicates (common in data cleanup); data validation: preventing anything except certain data types (e.g., dates) being entered in a cell; and cell protection: preventing accidental typing into a cell with a formula.

• Database Features. You may be thinking “I use spreadsheets, not databases!” — but there are a few indispensable Excel features that mirror the most useful of features from the world of databases: lookups and aggregate functions. A “lookup” in Excel is when your formula looks up values in a secondary worksheet (just like a database!) and puts them in the primary worksheet: for example, looking up in worksheet B the inventory amount adjacent to a cell containing a particular SKU, and putting it in worksheet A, adjacent to the cell containing that same SKU. Likewise, the SUMIF(), COUNTIF(), and similar Excel functions are counterparts of “aggregate data” calculations in databases – useful, for example, if you wanted to sum up the order values shipped to a particular zip code.

• Errors. You’ve probably encountered your fair share of errors that Excel has vociferously pointed out with: “#N/A” and “#NUM!” It turns out there’s an easy way to deal with these errors, either by intercepting them and providing a backup value, or by auditing and fixing them once and for all. We’ll show you the two surefire methods of handling these errors.

• Finding the Right Value(s). Sometimes you need to measure variations in a single cell value, like product price, to optimize the value of another cell, like profits. Other times, you need to optimize multipleinterconnectedcells, like price, volume, and inventory – to find the optimal value for profits. These two what-if scenarios are handled by the “Goal Seek” and “Solver” functions in Excel. Get ready to impress your boss.

• Macros. These are recorded steps that can be memorized for later playback. For example, if you find yourself splitting text into columns along a delimiter and then conditionally formatting certain rows, you might want to record those steps and then instantly reapply them to a new table. These steps are saved in Excel's internal programming language, VBA (Visual Basic for Applications). Once you've recorded your steps, you can dive into the behind-the-scenes VBA code that activates those steps and customize them even further.

• Pro Techniques. On your way to becoming an Excel pro, you’ll need a toolkit of useful standard tips, tricks, and techniques. Among these, you’ll probably find that functions such as LEFT(), MID(), and Excel’s Text-To-Columns and Consolidating Data features are the ones you’ll use the most often. We’ll show you why those are so popular and how to use them.

What You Will Learn!

• Module 1: Excel Essentials & Formulas and Functions
• Module 2: Formatting, Spreadsheet Design, Page Setup & Printing
• Module 3: List/Table Essentials & Charting
• Module 4: Pivot Tables, Protecting & Linking Data
• Module 5: Advanced Functions
• Module 6: Using the "What If" Analysis Toolpack & Implementing Macros

Who Should Attend!

• This Excel Introduction class is for anyone who would like to learn how to build and format spreadsheets with functions and formulas between cells.

TAKE THIS COURSE