Microsoft Excel - Complete Beginner to Pro Guide

Master formatting in Excel by changing font style, size, and color, adjusting column widths, bolding titles, and applying cell and text colors for clearer data.

learn how to change text alignment and apply borders to cells, making clear grids in Excel, and explore font effects such as italic, underline, strikethrough, superscript, and subscript.

Master the format painter to copy cell formats across worksheets in Excel, applying title styling, alignment, and colors without duplicating data.

Master how to clear formats and contents in a worksheet, undo changes with ctrl z, and save or save as a separate file.

Wrap text in cells to keep long content within a column, avoiding oversized columns, and apply alignment and formatting to present a tidy worksheet.

Rotate and align text in a cell or column using Excel's orientation options, including counterclockwise, clockwise, vertical text, and custom angles like 60 degrees.

Master number formatting in excel by applying currency formats, separators, and decimals, and format phone numbers; learn to move, fill, and manage grid lines for cleaner worksheets.

Learn to hide and unhide sheets in a workbook, manage visibility of sheets like number one through three, and select multiple sheets using shift or ctrl before hiding.

Master inserting and deleting rows and columns in Excel by selecting the target, using right-click insert or delete, and using the Home tab options to manage worksheet structure.

Master how to merge and unmerge a range of cells in Excel, then format merged cells with color, font, and borders, and restore grid lines.

Master freezing panes in Excel to lock the first row or first column, so headers stay visible as you scroll; learn freeze and unfreeze steps using data like student IDs.

Split a worksheet to compare first three and last three records by scrolling side-by-side; you can also split by columns for horizontal navigation, then revert to the original view.

Learn to enforce precise data entry in Excel by creating drop-down lists with data validation, defining sources for names, positions, IDs, and statuses, and extending validation to additional rows.

Learn to enforce project ID rules in Excel with data validation for numeric input between 1000 and 7000, use messages and error prompts, and apply conditional formatting by status.

Add, reorder by dragging, and delete items in an Excel drop-down list. Protect the sheet and workbook to prevent tampering with the list values.

Customize the Quick Access Toolbar to pin autosave, save, undo, and redo, and learn to add, remove, and separate commands for faster sorting and filtering in Excel.

Compare records across worksheets side by side by duplicating a window from the View tab, enabling view side by side, and using arrange all for horizontal or vertical layouts.

Compare records across two open workbooks side by side using the view side by side feature, align them, and scroll in sync or enable asynchronous scrolling for efficient cross-workbook review.

Master basic Excel formulas to perform addition, subtraction, multiplication, and division by starting with the equals sign and using cell references for practical budgeting and payroll calculations.

Compute the amount by line of business from Q1 to Q4 using SUM and relative referencing. Drag to fill and double-click to view formula range, showing how relative references adapt.

Learn to compute each line of business as a percentage of the total using absolute cell references, fix the column with dollar signs, drag to fill, and format as percent.

Learn how to use the average function in Excel: enter =average, select the salary range, and see that the result is the sum divided by the count of salaries.

Use the min and max functions to identify the lowest and highest salaries in a range. Learn how to select a range and apply max to extract the maximum salary.

The count function counts only numeric cells in a range, while the COUNTA function counts non-empty cells including text, illustrating salary counts and total employees.

Explore the if function in Excel to test a condition and return true or false values using its three parameters: condition, value_if_true, and value_if_false, including writing text in quotes.

Use nested if statements in Excel to grade scores with the 90-100 A, 80-89 B, 70-79 C, 60-69 D, and below 60 F, by combining multiple if functions.

Compare the IFS function with the IF function in Excel to simplify multi-condition logic into a single expression. Learn how IFS improves readability and reduces repeated syntax for key thresholds.

Use the countif function to count cells meeting a criterion, such as Amsterdam customers, by selecting a range and a criterion, with dynamic updates from a cell reference.

Learn to use the countifs function to count customers by multiple criteria, using city and age ranges as criteria, with Amsterdam and Toronto examples including ages 31 and 39.

Master using the sumif function to total sales by region with range, criteria, and sum_range, copy the formula across regions, and apply absolute references for accuracy.

Learn how to use the sumifs function to total sales by region and quarter, including setting sum and criteria ranges, choosing criteria values, and applying absolute references.

Learn how the average if function computes the average of cells that meet a condition, mirroring sum if but returning an average for each region using a filter handle.

Learn to use the averageifs function to compute the average of cells that meet multiple conditions, such as region and quarter, producing a sales average by quarter.

Learn how the Excel and function tests multiple conditions like total sales ≥ 15000 and accounts ≥ 10, and how to nest if with and to calculate commissions.

Learn how to use the or function in Excel to test whether total sales reach 15,000 or at least 10 accounts open, and calculate the commission with an if function.

Apply the upper function to convert lowercase text to uppercase, extend columns, paste as values to remove the formula, and delete the first three columns to keep only uppercase text.

Apply the lower function to convert uppercase text to lowercase across a range. Copy the results, paste as values, and delete the temporary columns to finalize the transformed text.

Apply the proper function to capitalize the first letter of each word and lowercase the rest in column a, then copy and paste results and delete columns a and b.

Master the concatenate function to join last names and first names with a comma and space, then copy the formula and paste values to produce a full name.

Learn how to use left, mid, and right functions in Excel to extract area code, town, and line number from telephone numbers, using the formula editor and fx wizard.

Explore how the LEN function counts characters in text, including spaces, and identify the longest and shortest usernames and passwords in a worksheet.

Master the trim function to remove leading and trailing spaces from text, cleaning usernames and passwords and preventing login errors by trimming inputs on the back end.

Apply the substitute function in Excel to replace specific characters or text, control which occurrence to replace using the optional number, and remove dashes while noting its case sensitivity.

Compare text using the exact and equal functions in Excel. Exact is case sensitive, while equal is not, and leading spaces can cause false results.

Use vlookup to look up data organized vertically in a table, using a unique id in the first column to retrieve salaries or departments.

Use Vlookup to fetch emails and telephone numbers for multiple customers from two workbooks by aligning the leftmost user name column, using exact matches, and specifying the correct column indices.

Use HLOOKUP to retrieve sales data organized horizontally by date. Create a dropdown with data validation and use exact-match HLOOKUP to find sneakers sold on March 3 and March 6.

Learn to use XLOOKUP to retrieve emails and telephone numbers by username from the customer records sheet, handling duplicates and using exact-match retrieval.

Explore rotating data in Excel from vertical to horizontal using two methods: paste special transpose for static results and the transpose function for dynamic, date formatting, and proper number formats.