Complete Microsoft Office Basic to Advance Course

Explore the Excel interface, including tabbed groups, the home tab tools, the formula bar, and backstage view for saving, printing, sharing, and navigating cells and sheets.

Master creating a new Excel workbook from a blank file or templates, and save it using backstage view with save or save as, including file types and basic metadata.

Customize the quick access toolbar in Excel to access tools with a single click. Add commands from any tab, use Alt shortcuts, and reposition the toolbar for faster work.

Learn basic data entry in Excel by entering numeric, text, and date values, formatting with general, date, currency, and accounting formats, and performing calculations with formulas.

Learn to create a basic data table in Excel, open workbooks with ctrl+o, enter headers with tab, convert text to proper case, paste values, and manage rows efficiently.

Master efficient data movement and selection in Excel using keyboard shortcuts and mouse techniques, including Ctrl+Home, Ctrl arrow keys, End, and Ctrl+A to select tables, copy and paste.

Explore Excel formatting basics, including alignment options (left, center, right; top, middle, bottom), rotate text, wrap text, shrink to fit, auto fit, and merge and center.

Format tables in MS Office using keyboard shortcuts to adjust column width, apply borders, colors, heading styles, and sum calculations, then review with print preview and landscape layout.

Design an employee time sheet in Excel by building the table, applying borders and alignment, merging headings, copying formatting, and preparing print preview for practice.

Design advanced Excel forms with automatic calculations by merging cells, arranging layouts, using copy and paste tricks, borders, wrap text, and centering for professional forms.

Explore the three data types in excel: numeric values, text, and date and time values, and learn how to format, convert, and calculate with dates.

Master inserting special characters in Excel, using the insert tab and symbols dialog to insert copyright, trademark, currency, and other symbols, and apply them in data, labels, or formulas.

Enable autocorrect in Excel to automatically fix typing mistakes and capitalization, saving time and ensuring data accuracy. Customizing replacements through file options proofing autocorrect unlocks user defined shortcuts.

Master auto suggestion and auto fill in Excel for faster, error-free data entry using pick from drop down and alt down arrow.

Master the autofill feature in Excel to quickly generate series of numbers, dates, and text patterns, and extend data across thousands of rows using drag, double-click, and custom lists.

Explore Excel's flash fill, an autofill tool that auto extracts first names, last names, and initials from patterns; it's static data, not dynamic, and speeds data formatting.

Master inserting and deleting rows and columns in Excel using mouse or keyboard shortcuts, including right-click options, ctrl+shift+plus, ctrl-minus, and undo.

Learn to hide and unhide rows and columns in Excel using mouse actions, keyboard shortcuts such as Ctrl+space, Ctrl+0, and Ctrl+9, and the Home tab format options.

Manage worksheets in a workbook by inserting, renaming, deleting, and moving sheets. Learn to copy sheets, hide or unhide, apply tab colors, and save with Ctrl+W.

Learn to use find and replace in Excel with Ctrl F to locate text or numbers, search within a sheet or workbook, and replace with new values using advanced options.

Create named ranges in Excel, assigning names to a cell like F3 and ranges such as commission rate and sales data, then reference them in formulas using the Name Manager.

Define and manage named ranges with name manager, set scope to workbook, and use the North Sales name in formulas to simplify commission and sales calculations across the workbook.

Learn to retrieve a value from another workbook by using a defined name in Name Manager (eg. North sales and commission rate), then format the result as a percentage.

Define and manage named ranges with the name manager to resolve workbook and worksheet scope conflicts. Apply a wb total sales and ws total sales naming convention to prevent confusion.

Learn to create named ranges for monthly regional sales, use the name manager, and retrieve intersection values (month by region) with keyboard shortcuts for fast, accurate data in Excel.

Learn how to use Excel formulas and functions, apply operators like plus, minus, multiply, and divide, and create dynamic results by referencing cells with the equal sign.

Learn to use inbuilt Excel functions to calculate totals by multiplying quantity and rate, autofill formulas, and sum a range for dynamic, automatically updating results.

Master cell references in Excel, including relative, absolute, row absolute, and column absolute. Learn to lock references with F4 and apply them when copying formulas for consistent results.

Learn common excel formula errors, such as division by zero, unrecognized names and custom names, unavailable data signals, null intersections, numb values, invalid references, and wrong type arguments.

Identify circular references where a formula depends on its own value, causing errors. Remove or adjust references, then save to obtain correct results.

Learn to paste values only in Excel by using paste special, selecting value (with source formatting or with number formatting) to paste as isolated values without formulas or cell references.

Excel stores dates as serial numbers starting from January 1, 1900, enabling easy date calculations and conversions between dates and numeric serials, with flexible date formats.

Explore the today function that returns the current date and updates dynamically, and use datediff to compute ages and deadlines for invoices and renewals, with if logic.

Create dynamic dates in excel using the date function by combining year, month, and day; explore last day of month and expiry date calculations from manufacturing dates.

Convert a non date string into a date in Excel by extracting year, month, and day with left, mid, and right, then assembling with the date function.

Compute the days between two dates in Excel using subtraction. Choose inclusive or exclusive counting based on purpose, such as project duration, event planning, or vacation days.

Learn to calculate days between dates in Excel using the days and days360 functions, understanding 365 actual days versus a 360-day year and the US and European methods.

Calculate the number of working days between two dates in Excel using the networkdays function, excluding weekends and optional holidays, for project scheduling, payroll, and invoices.

Calculate workdays between two dates using Excel's networkdays.intl function with a customizable weekend argument. Use it for project scheduling, payroll, or overdue payments, including holiday lists.

Learn how to use the Excel workday and workday intl functions to calculate an end date by adding a specified number of working days while excluding weekends and holidays.

Master the datediff function in Excel to compute date differences in years, months, or days, enabling age, tenure, and duration calculations with start date, end date, and unit arguments.

Calculate the days remaining in a year with date functions that auto update when the date changes, using year and today functions to fix December 31 as the last day.

Learn to determine if a cell contains text using istext and type in Excel, with cell references, copying formulas, and filtering results to identify text values.

Master two methods to test string equality in Excel: use the equal sign for value comparison (case-insensitive) and the exact function for case-sensitive results, with practical bank data examples.

Learn to join multiple text values in a single cell using ampersand, the concat function, and the legacy concatenate, including spaces and ranges, with real-life examples.

Learn to clean data in Excel using the trim and clean functions to remove leading and trailing spaces, collapse multiple internal spaces, and remove non-printing characters from imported data.

Learn how to count characters in a string using the Len function, including spaces, and compare results with trim and substitute to count with and without spaces in IBANs.

Learn to pad a number using the repeat function to create left or right asterisk padding, driven by dynamic length calculations with Len and optional concatenation.

Learn how to change text case in MS Office 2021/365 using proper, upper, and lower functions to convert text to proper case, uppercase, and lowercase.

Learn to extract characters from a string using left, right, and mid functions, and transform text with uppercase and lowercase, then combine results with concatenate.

Learn how to extract the first word of a string in Excel using the find function and left function, locating the space and calculating the character position from the left.

Explore the five inbuilt logical functions—and, or, not, iferror, and if—using logical tests to produce true or false, handle errors, and guide actions in Excel.

Explore how Excel handles time by converting times to serial numbers, applying time formats, and using now, today, time, and time value functions to display and compute time data.

Learn how to sum time values in Excel by converting numbers to time, applying a custom display format with hours, minutes, and optional seconds, and using autosum for weekly hours.

Learn to calculate working hours in Excel using subtraction for day shifts and the mod function for night shifts, with proper datetime formatting and copying formulas.

Convert integer values into time values in Excel by turning decimals into hours and minutes, minutes into hours and minutes, and seconds into a time format using a simple function.

Learn to round up time values in Excel by converting seconds to minutes or hours using rounding functions and 1440 and 24-based calculations to the nearest minute and hour.

Master the VLOOKUP function to retrieve a value from a table by matching a left-side value. Use four arguments and exact or approximate results.

Learn horizontal and vertical lookups with hlookup and vlookup, retrieving values from a table's rows or columns. Use if, len, range, and data validation for robust, approximate or exact matches.