Course Description

These days, any managerial or analyst role requires that you work with data. Excel remains one of the most powerful and easy-to-use tools when it comes to crunching data. Learning Excel in a proper, structured way not only helps you save time, but also helps you become better at your job and gives you time to focus on the things and people that matter.

This is an ideal course for users who desire to manage data, sort and filter, analyze data, and learn to automate some common Excel tasks.

Prerequisites

Experience in creating, editing, formatting, saving, and printing basic spreadsheets in Microsoft Excel is necessary.

Course Outline

Module 1: Extract Data into Excel

Often, we use Excel to summarize detail that comes from some other program or accounting system. There are ways to get data from another program into a destination Excel worksheet, namely copy/paste, pull, etc. So the million-dollar question is: which way is better than the others? For example, importing text files into Excel can be a labor-intensive process if you’re going through the same steps over and over again. With proper use of the data import options Excel offers, you can save yourself quite some work and at the same time improve consistency.

• Microsoft Query
• Import/Export Text Files

Module 2: Data Cleansing

Misspelled words, stubborn trailing spaces, unwanted prefixes, improper cases, and nonprinting characters make a bad first impression. And that is not even a complete list of ways your data can get dirty. Roll up your sleeves. It is time for some major spring-cleaning of your worksheets with Microsoft Excel.

• Remove Duplicates
• Change Case
• Removing Spaces and Nonprinting Characters from Text
• Merging
• Text to Column

Module 3: Data Managing

Worksheets with a lot of content can sometimes feel overwhelming and even become difficult to read. Fortunately, Excel can organize data in groups, allowing you to easily show and hide different sections of your worksheet. If your data is extremely large, you can even customize what to view. You can also sort your data, summarize different groups using the Subtotal command and create an outline for your worksheet. Last but not least, you can do a quick filter to select what data to view or create complex criteria to pull only the required data to analyze.

• Advanced Filtering
• Extract Specific Columns to Another Worksheet
• Setting Up the Criteria Range
• Using Wildcards in Criteria
• Filter Unique Records
• Computed Criteria
• Database Functions
• Autofilter
• Advanced Autofiltering
• Filter by Color
• Sorting
• Custom Sorting
• Custom Lists
• Sorting Multiple Levels
• Subtotal Command
• Outline
• View

Module 4: Table Formatting

Advanced Excel users know how to structure & present their data so that it looks impressive. A good understanding of Excel features like tables, cell styles and formatting options is necessary to make awesome Excel workbooks.

• Reconciling Table Data by Matching
• Tables
• Convert the Table to Range
• Table Formulas

Module 5: Advanced Formulas

Formulas make Excel smart. Without them, Excel is just a data-keeping tool. But by using formulas, you can crunch data, analyze it and get answers to the most complex questions.

• If/Nested If
• And/Or
• If + And
• If + Or
• Count/Counta/Countblank
• Countif/Countifs
• Sumif/Sumifs
• Averageif/Averageifs
• Splitting

Module 6: Macro

One of the more powerful, but seldom used, functions of Excel is the ability to very easily create automated tasks and custom logic within macros. Macros provide an ideal way to save time on predictable, repetitive tasks as well as standardize document formats.

• Activate the Developer Tab
• Set Excel Macro Security
• Record a Macro
• Stop Recording a Macro
• Run a Macro
• Delete a Macro
• Save a Macro