Excel Microsoft 365 Intermediate

Sort data and apply filters to isolate pencil sales, then refine results by central region, date, and cost in a large Excel worksheet.

Learn to create and use Excel tables with insert, headers, and filters. Name the table, enable the total row, and apply design styles for easy data analysis.

Add charts and graphics to worksheets in Excel 365 intermediate, distinguish charts from graphs, and create, enhance, modify, and format charts with shapes and pictures.

Discover the difference between charts and graphs, and how graphs are a type of chart that shows numerical relationships on axes, with examples like bar charts and line graphs.

Create a chart to compare items sold using a clustered column chart from recommended charts, then use filters to focus on specific items and explore a pivot chart.

Enhance your Excel charts by selecting individual chart elements, applying design styles, adjusting colors, and moving or resizing charts across worksheets for clear, customizable visuals.

Learn how to modify chart data in Excel 365, observe dynamic updates as values change, and add a shipping cost series to a blue sales and orange shipping bar chart.

Explore how to change chart types in Excel Microsoft 365 intermediate, switching between column, line, pie, and surface charts to better visualize income and shipping costs.

Learn to annotate charts in Excel by inserting shapes, rectangles, and arrows, add text boxes and floating balloons, and highlight the best month, such as October, with comments.

Explore how to customize charts by using chart elements, adding a chart title, legend, and data table, and adjusting grid lines to clearly display monthly sales and shipping amounts.

Format charts in Excel by applying chart design templates, adjusting shape styles, borders, colors, backgrounds, and fonts to customize bar graphs and overall chart appearance.

Insert pictures into a bar chart by selecting the plot area and using the format tab to apply a background image, texture, or gradient.

Create in-cell sparklines to display mini charts in a single cell, using a column sparkline. Highlight high and low points, first and last points, and adjust styles.

Explore chapter three of Excel 365 intermediate to master functions, statistical and text functions, including present value, loan payments, principal and interest, and date and time functions.

Learn to use Excel's statistical functions—counta, count, average, median, minimum, and maximum—via the formula bar and the statistics menu for range analysis.

learn to calculate the present value of a loan in excel using the pv function, applying the monthly rate, total payment periods, and monthly payment to find the loan amount.

Learn to compute monthly loan payments with Excel's PMT function and determine payoff time with the end per function, using rate, present value, and payment signs.

Learn to calculate principal and interest for a 36-month loan using the PMP function, applying absolute and relative references to show rising principal payments and falling interest over time.

Learn to calculate loan interest using the rate function and convert monthly rates to annual rates, illustrated with a 500000 house loan over 30 years, plus Excel auto calculations.

Explore text functions in Excel 365, using proper to capitalize names, replace to fix underscores, and concat to create a full name for mailing labels.

Learn to create and manage named ranges in Excel, define names, reference ranges across sheets, and update ranges with the name manager for accurate summing.

Learn to use Excel date and time functions to display today and now, calculate days between dates, and extract month, year, weekday, and week from a date.

Explore how to use logical functions in Excel 365, including and or functions, with relational operators for numbers and text, and see dynamic results from comparing A15 and B15.

Copy formula values in Excel by selecting cells, choosing paste values to keep only the results and remove all formulas, then reuse the values elsewhere.

Explore the if functions and how to use VLOOKUP with exact and approximate matches, along with COUNTIF, in Excel 365 intermediate.

Learn to use the if function in Excel to return a true or false value based on a condition, with examples for total hours, regular hours, and overtime hours.

Learn to use the if function with relational operators and logical functions to calculate a bonus based on full-time status, hours worked, and a job rating of four or five.

Learn how to use the IFS function in Excel to replace nested if statements by mapping job ratings to bonuses with logical tests and corresponding values.

Learn to use the vlookup function to find items by row in a table, retrieve unit prices, and apply absolute referencing with a table array for accurate lookups.

Use vlookup to retrieve approximate table data and calculate the commission rate from a rate table, using range_lookup and a two-column setup.

Learn to use vlookup with exact match to prevent incorrect results from approximate data, ensuring the item is on the left and the price on the right, with absolute references.

Master the countif family by using named ranges to count desks and binder sales over 150 with practical examples.

Explore countif family functions in Microsoft 365 Excel, including sumif and averageif, using multiple criteria, date ranges, and currency formatting to analyze sales data.

Learn to finalize, print, and save Excel 365 workbooks with page layout and view tabs, headers and footers, page breaks, hyperlinks, arranging multiple workbooks, and emailing.

Explore proofing and research tools in Excel, including spell check, translate, and the review tab, to look up terms, apply changes, and insert suggested edits.

Explore the page layout and view tabs in Excel to adjust margins, orientation, size, and the print area, set print titles, review page breaks and gridlines, and manage headers.

Use the page layout and page setup tools to print wide worksheets by setting orientation to landscape, choosing margins, and scaling to fit on one page.

Explore Excel view options by switching from normal to page break preview and page layout, using the View tab and bottom right corner shortcuts to manage headers, footers, and ruler.

Learn how to adjust page breaks by moving the dotted line, choosing where the next page begins in the printed copy, and manually repositioning the break.

Learn how to add headers and footers to a worksheet for printing, using the page layout tools to insert the file name, sheet name, date, time, and page numbers.

Explore previewing and printing a worksheet, adjust print settings, margins, orientation, and page fitting, review print preview options, and export as a PDF to control how pages appear.

Master organizing and switching between multiple workbooks in Excel 365 by using the View tab to arrange windows (tile, horizontal, vertical, cascade) and shortcuts like Ctrl+Tab.

Learn to add hyperlinks in Excel to navigate between worksheets and cells, link to web pages, and move quickly around the workbook using the insert hyperlink feature.

Save workbooks in different formats by exporting the active worksheet, multiple sheets, or the entire workbook as Excel, pdf, or web page (html/htm) formats.

email a workbook in Excel Microsoft 365 by sharing it and attaching it as an Excel workbook or PDF, then send from your default email with a prefilled subject.

Explore chapter six of the Excel 365 intermediate course as it covers security and sharing, including unlocking cells, protecting worksheets and workbooks, password protection, sharing, and tracking changes.

Learn to unlock cells and protect worksheets with passwords, creating a read-only sheet and selectively unlocking regions such as the region column while keeping the unit price locked.

Protect a workbook from the review tab by setting a password, locking its structure and preventing adding, deleting, renaming, or moving worksheets, while data on cells remains editable.

Learn to assign a password to open Excel workbooks, encrypting it so it cannot be opened without a password. Go to File, Info, Protect Workbook, Encrypt with a password, confirm.

Learn to share workbooks in Excel 365, remove passwords, enable coauthoring on OneDrive, address table and XML map limitations, and create a share workbook button.

Learn to coauthor Excel workbooks in Office 365, replacing the old shared workbook, by sharing via OneDrive or SharePoint, enabling real-time collaboration with multiple editors.

Learn how to track changes in a shared workbook in Excel 365, including turning on track changes, highlighting edits, and accepting or rejecting edits with timestamps and author details.

Conclude the Excel 365 intermediate course by reviewing how to create tables and charts, use if, look up, count if, and ifs, and finalize, save, and share workbooks.