Intermediate Microsoft Excel Training (EXC365.2)

This Intermediate Microsoft Excel course is designed for professionals who already have a basic understanding of Excel and are looking to enhance their skills to make the most out of this powerful tool. Ideal for employees seeking to improve productivity and efficiency, the course covers a wide range of functions and techniques to take your Excel capabilities to the next level.

We start with lessons on Common Functions, providing you with a solid foundation in using named ranges in formulas and working with data across multiple worksheets. You’ll practice using essential functions like IF, AND, OR, NOT, and SWITCH. Additionally, we cover functions such as COUNTIF, AVERAGEIF, and SUMIF, along with financial functions like PMT and NPER. You will also explore text functions, including the versatile TEXTJOIN function, and various date and time functions, with hands-on exercises to hone your skills.

Next, we’ll explore Working with Lists. In this section, you’ll learn what constitutes a list of data, and how to efficiently manage it. Topics include removing duplicates, sorting, and filtering data. You will also add subtotals to lists, with accompanying exercises to reinforce these techniques.

The course continues with Visualizing Your Data. This section covers the basics of chart creation and provides tools for editing charts. You’ll learn how to use the Chart Design Tool Tab, the Format Tool Tab, and the Format Task Pane. We introduce a variety of chart types, including line and area charts, hierarchy charts, statistic charts, and combo charts. You’ll work on sparklines, learning how to insert and customize them, and gain proficiency in adding and formatting objects, working with shapes, icons, and SmartArt. The Quick Analysis Tool is also featured to help make data visualization more accessible.

In Working with Tables, you’ll discover how to format data efficiently as a table, using the Table Design Tool Tab and formatting individual cells. You’ll learn how to select table rows and columns, create and modify tables in Excel, and utilize structured references. Converting tables to ranges is also included, ensuring you can manage and present your data effectively.

We’ll then cover Advanced Formatting. This section covers a variety of advanced formatting options to make your spreadsheets more effective and visually appealing. You’ll explore cell styles and engage in exercises to apply what you've learned. The segment on conditional formatting includes creating and managing rules, such as highlighting cells based on criteria, top/bottom rules, data bars, color scales, and icon sets. You’ll also learn how to remove conditional formatting when necessary, with exercises to practice these skills.

By the end of this course, you’ll be. able to manage complex data sets, create detailed visualizations, and apply advanced formatting. Whether you’re training a team or upskilling individually, this course will significantly enhance your productivity and efficiency in using Microsoft Excel.

Excel Version: This course primarily targets Excel 365 and Excel 2021; however, we draw attention to features that are not supported in earlier versions of Excel, and point out alternative options, when available.

1. Common Functions
   1. Using Named Ranges in Formulas
   2. Using Named Ranges in Formulas (exercise)
   3. Using Formulas That Span Multiple Worksheets
   4. Entering a Formula Using Data in Multiple Worksheets (exercise)
   5. Functions: IF and IFS
   6. Using the IF Function (exercise)
   7. Functions: AND and OR
   8. Functions: NOT
   9. Functions: SWITCH
   10. Functions: COUNTIF, AVERAGEIF, and SUMIF
   11. Functions: PMT and NPER
   12. Using the PMT Function (exercise)
   13. Functions: TEXTJOIN and CONCAT
   14. Using the TEXTJOIN Function (exercise)
   15. Text Functions
   16. Creating Ordinal Numbers (exercise)
   17. Date and Time Functions
   18. Using Date Functions (exercise)
2. Working with Lists
   1. What is a List of Data?
   2. Removing Duplicates from a List
   3. Removing Duplicates from a List (exercise)
   4. Sorting Data in a List
   5. Sorting Data in a List (exercise)
   6. Filtering Data in a List
   7. Filtering Data in a List (exercise)
   8. Adding Subtotals to a List
   9. Adding Subtotals to a List (exercise)
3. Visualizing Your Data
   1. Chart Basics
   2. Tools for Editing Charts
   3. Using the Chart Design Tool Tab (exercise)
   4. The Format Tool Tab
   5. Using the Three Chart Buttons
   6. The Format Task Pane
   7. Useful Charts
   8. Line and Area Charts
   9. Hierarchy Charts
   10. Statistic Charts
   11. Other Charts
   12. Combo Charts
   13. Using Recommended Charts
   14. Sparklines
   15. Inserting and Customizing Sparklines (exercise)
   16. Add and Format Objects
   17. Working with Shapes
   18. Working with Icons
   19. Working with SmartArt
   20. Using the Quick Analysis Tool
4. Working with Tables
   1. Format Data as a Table
   2. Table Design Tool Tab
   3. Formatting Individual Cells
   4. Selecting Table Rows and Columns
   5. Creating and Modifying a Table in Excel (exercise)
   6. Structured References
   7. Convert a Table to a Range
5. Advanced Formatting
   1. Cell Styles
   2. Working with Cell Styles (exercise)
   3. Conditional Formatting
   4. Conditional Formatting: Highlight Cells Rules
   5. Conditional Formatting: Top/Bottom Rules
   6. Conditional Formatting: Data Bars, Color Scales, and Icon Sets
   7. Conditional Formatting: Create a New Rule
   8. Removing Conditional Formatting
   9. Using Conditional Formatting (exercise)

Each student receives a comprehensive set of materials, including course notes and all class examples.

Experience in the following is required for this Microsoft Excel class: