Microsoft Excel for Professionals : Beginner to Advanced

Learn how to create basic formulas in Excel using equals, plus, minus, asterisk, and forward slash to perform addition, subtraction, multiplication, and division with automatic recalculation for membership totals.

Learn how to use the sum function and autosum to total sales data, with bracketed syntax, range operators, bottom-row totals, and copy via the fill handle.

Master average, max, and mean calculations in Excel using the insert function and auto fill, while preserving formatting and efficiently copying results down the table.

Master absolute cell referencing in Excel, learn the difference between relative references, how copying formulas updates references, and how to lock cells with dollar signs using practical examples.

Master absolute cell referencing in Excel by locking a cell with dollar signs, using the F4 shortcut, and understanding when to use relative, mixed, and absolute references.

Execute cross-sheet calculations by linking values from the 2015 sales sheet and 2016 total to build a dynamic, updating sales summary.

Use the home tab font tools to format worksheets for a professional look, adjusting font, size, bold, italics, underline, and color with shortcuts like ctrl+b, ctrl+i, and ctrl+u.

Apply borders to improve data readability and emphasize totals by using all borders, top and bottom borders, or outside borders; customize color and style in the more borders dialog.

Explore alignment tools in Excel to control horizontal and vertical alignment, wrap text, merge cells for heading layouts, and rotate or angle text to fit wide headings.

Learn to copy formatting with the format painter across cells and ranges, apply consistent styles quickly, and use clear formats to remove formatting without deleting data.

Learn to apply and customize Excel number formats, including general, currency, accounting, percentage, and date formats, use custom formats, and manage clear formats for accurate calculations.

Apply built-in styles from the home tab to ensure consistent headings and totals across worksheets, and use themes to align colors and fonts across Excel, PowerPoint, and Word.

Learn to delete or insert rows and columns in Excel, understand how deletion shifts data, add a middle column for average cost with a formula, and hide unused columns.

Learn to find and replace data in Excel using find and select, find all, and replace all. Practice updating addresses across the worksheet with copy-paste and precise matches.

Learn to apply Excel filters to narrow data, use the auto filter, filter by city and year 2016, and identify the top 20 orders by total.

Master single and multi-level sorting in Excel, sorting by account manager, then by total sales and date, with header recognition and ascending or descending options.

Master conditional formatting in Excel to highlight data with rules like greater than, text that contains, date-based highlights, data bars, color scales, and icon sets, and to clear rules.

Master print preview in Excel to print only the chosen data, avoid printing the whole workbook, and adjust margins, orientation, and print options for accurate results.

Use page layout view and orientation to landscape, adjust margins to narrow or custom, then apply scale to fit to two pages by width for a clean print.

Sort data by state, locate Rajastan and Maharastra records with find and match case, and insert page breaks to place each group on its own page for printing.

Learn to set up print titles by repeating rows and the first column, adjust print area, and choose page order and grid lines options for clear, accurate prints.

Add headers and footers via the page layout view, insert page numbers with totals, include the logo and an updating date, then preview the workbook for printing.

Master working with multiple worksheets in Excel by adding, moving, copying, renaming, color-coding, grouping, hiding, and un-hiding sheets, and learn safe delete practices.

Master 3D formulas to sum staff expenses across multiple worksheets efficiently, using 3D references, and copy across sheets, while noting identical structure and potential issues when moving sheets.

Link data across four workbooks to sum expenses and keep results updated automatically. Manage and troubleshoot workbook links, including arranging multiple workbooks on screen and handling broken links in Excel.

Consolidate by position across multiple workbooks using the data tools consolidate feature to sum ranges in each book without links.

Learn to consolidate data across multiple workbooks using the consolidate tool by reference, by position, or with left-column labels. Apply sum and average functions to summarize non-identical datasets efficiently.

Learn to combine full names and emails in Excel using concat and concatenate, plus the ampersand, a space in quotes, and copying the formula down.

Learn to convert text to upper, lower, and proper case in Excel using nested formulas with concat or ampersand. See how Excel evaluates inner functions first and handles brackets.

Learn to split a combined location column into building code and phone extension using left, mid, and right Excel functions, with a find helper to handle varying word lengths.

Combine left, right, and the find function to extract section information from variable-length text by locating the trailing space and calculating the number of characters to pull.

Learn date calculations in Excel using today, now, and yearfrac to display current date and time, measure years between dates, and forecast next review dates.

Master named ranges in Excel to simplify pension-contribution calculations by replacing absolute cell references with meaningful names, using the name box, and following naming rules for cleaner, faster formulas.

Explore creating named ranges in Excel using the name box, define name, and create from selection, with attention to scope and naming by column headings for efficient formulas.

Explore how to use the name manager to view, edit, and delete named ranges in Excel. Learn to create new ranges, adjust scope, and audit ranges from the formula tab.

master named ranges in formulas to compute totals and quick facts in Excel, using keyboard shortcuts like autocomplete, control+enter, and F3 to reference ranges for efficient calculations.

Learn to create, manage, and use named ranges in Excel, using create from selection, apply names, and base names to replace references in formulas and document them for others.

Master Excel count functions to tally shipped orders, total orders, and missing order priorities using named ranges, counting numeric values, non-empty cells, and blanks.

Learn to use countifs to count orders by state (M.H., RJ, WB), customer type, and quantity thresholds, and update a dashboard with the resulting counts.

Learn to summarize sales data in Excel using the sumifs function with one or multiple criteria, including handling absolute and relative references to drag formulas across a dashboard.

Convert the data to a table to make named ranges extend automatically as new entries, then customize with table styles, header options, and banded columns while preserving table functionality.

Learn how to work with tables in Excel, compare ranges vs tables, select data with table arrows, remove duplicates, and use sorting, filtering, and the total row.

Sort and filter Excel tables using the data or home tabs, access the total row for live summaries, and identify top earners or longer-than-average staff as you filter.

Explore how Excel tables auto extend to add rows and columns, carry formulas via structural references, and keep charts and named ranges in sync as you add staff data.

Convert the table to a range to use Excel's subtotal tool and create a department-level salary breakdown with subtotals for annual salary, pension, and package, plus a grand total.