Intermediate Microsoft Excel 365

Lesson 1

Sorting and Filtering Data

This lesson focuses on organizing data to make it easier to interpret and analyze. You will begin with a review of basic sorting, then explore more advanced techniques such as sorting by multiple columns. You will also learn to filter data using built-in tools, date ranges, blank cells, and specific formatting. The lesson wraps up with an introduction to sorting and filtering using functions like SORT, SORTBY, and FILTER.

Adding Graphic Objects to a Worksheet

In this lesson, you will learn how to add visual elements to your worksheets. You will insert pictures from your computer or online, capture screenshots, and work with Excel's drawing tools. You will also practice sizing, positioning, and formatting these graphic objects to enhance both clarity and visual appeal.

Exploring Excel's Chart Types

This lesson introduces a wide variety of chart types and their uses. You will compare 2D and 3D versions of bar, column, line, area, pie, and doughnut charts. You will also explore specialty charts such as histograms, sunburst, scatter, radar, and funnel charts. These tools help you choose the most effective way to present your data.

Chart Formatting

Building on your charting skills, this lesson shows how to fine-tune the appearance of charts. You will learn to resize and move charts, customize elements like titles and legends, and apply different color schemes and styles. You will also work with the Format pane to add borders, fills, and effects, and modify details such as gridlines and 3D rotation.

Sparklines, Trendlines, and Error Bars

This lesson explores ways to add deeper meaning to your charts. You will learn how to switch data orientation, add data labels, and adjust axis settings. You will also insert trendlines to show patterns and make predictions, and use error bars to represent variability. The lesson concludes with sparklines, which help you show data trends in a compact, in-cell format.

Using Date and Time Functions

In this lesson, you will explore how Excel handles dates and times. You will practice using them in formulas and formatting them in different ways. You will also use functions to extract specific parts of a date or time, calculate differences between dates, and adjust dates by adding or subtracting months.

Using Text Functions

This lesson covers functions that work with text. You will learn how to change the case of text, convert between text and numbers, measure the length of a string, and extract parts of a text string. You will also clean up data with tools like TRIM and FIND, and learn to split or combine text using Text-to-Columns, CONCAT, and Flash Fill.

Using Math and Logical Functions

In this lesson, you will explore a wide range of functions for performing calculations and evaluating logic. Topics include basic math operations, square roots, powers, random numbers, rounding techniques, and number system conversions. You will also begin working with trigonometry functions to handle angles and ratios in formulas.

Using Advanced Logical Functions

This lesson builds on your understanding of logic in Excel. You will learn to write more complex IF statements and combine them with functions like AND, OR, and NOT. You will also explore action-based logic using conditional math functions like SUMIF, COUNTIF, and AVERAGEIF. Finally, you will learn to resolve errors in formulas using IFERROR and IFNA.

Goal Seeking and Solving

In this lesson, you will use Excel's built-in tools to solve for unknowns. You will use Goal Seek to adjust one input value to reach a specific result, and explore Solver for more complex problems involving multiple variables and constraints. You will also learn to create one-variable and two-variable data tables to see how input changes affect a formula's output.

Sharing and Protecting Workbooks

This lesson shows how to manage workbook properties, share files online, and collaborate with others. You will explore different methods of sharing, from links to email, and practice working with comments and notes. You will also apply protection features to restrict editing, lock specific cells or sheets, and secure the overall structure of your workbook.

Customizing Excel

The final lesson focuses on making Excel work the way you prefer. You will customize the Quick Access Toolbar, modify the ribbon, and change status bar and proofreading settings. You will also manage add-ins and explore the Trust Center to control security features and determine which file types and macros are allowed.