Mastering MS Excel 101 (with exercises and solution videos)

Learn how to enter text and numbers in Excel, format decimals and currencies, apply accounting and percentage formats, and avoid common pitfalls when representing data.

Learn how Excel treats dates as values and insert dates in year-month-day. Apply short, long, and custom date formats, and manage invalid dates and formulas.

Practice completing an Excel exercise by entering text and numbers, adjusting decimals, and applying currency and percentage formats. Format dates and days of week to match the sample.

Learn to select data and tables efficiently in Excel using mouse and keyboard tricks, including selecting cells, rows, columns, and entire tables, inserting columns or rows, and undoing mistakes.

Learn to use Excel's spell check to correct errors and manage dictionary words. Practice handling suggestions, changes, and ignores from the review tab to ensure accurate spreadsheets.

Learn to use Excel's freeze panes to keep headers and key columns visible as you scroll large tables, with top row, first column, or both fixed for easy data comparison.

Format text in Excel by changing font, size, bold, italics, underline, and alignment from the Home tab, including text orientation, to center, top, or left aligned cells.

apply text and cell color formatting to emphasize data, using red, blue, yellow; differentiate headings with white text on blue, and highlight negatives in red and positives in green.

Learn to create cleaner tables in Excel by merging cells to form a single heading and wrapping text to fit multilevel headings, improving readability and layout.

Discover how to apply and manage borders in excel using the borders dropdown, including outside borders, bottom and left borders, and all borders for selections.

Format borders in Excel using the Format Cells border tab, applying outside borders, color, custom styles, dotted lines, and diagonal cross for precise, professional tables.

Sort Excel tables by value or alphabetically, by text or dates, preserving data integrity with headers, using custom sort across the entire table and applying color-based options.

Master data management in Excel by applying sorting and advanced filtering across multiple criteria, using table headers and the data tab to hide or reveal rows without losing data.

Learn to quickly locate specific data in large Excel tables by using Ctrl+A to select the table, then data tab filters and dropdowns, or Ctrl+F search.

Learn to use find and replace in Excel to update colors, fix separators, and standardize data, with replace all, find next, and column-specific options.

Learn how to remove duplicate rows in Excel using the remove duplicates tool, with options for headings, selecting specific columns, or applying across all columns to keep unique rows.

explain how to use the text to columns feature in Excel's data tools to split a single column into multiple columns by delimited separators like forward slash, space, or comma.

Learn Excel through a chapter exercise and solution video that walks through removing duplicates by name and birth date, splitting names, handling multi-space data, color replacement, and answering data-driven questions.

Master basic Excel calculations using the formula bar and the equal sign. Learn addition, subtraction, multiplication, division, parentheses for order of operations, and power functions to compute quickly.

Learn how to reference cells in Excel to perform fast calculations by using formulas that reference B2 and B3, and perform add, subtract, multiply, and divide with automatic updates.

Learn to use the sum function to quickly add numbers across rows, columns, or ranges by referencing cells. See how Excel updates totals automatically when source cells change.

learn how to use excel's average and count formulas, built-in functions, by selecting cells and viewing results in the bottom right for quick insights.

Master Excel basics by using the max, min, and median formulas to find the largest, smallest, and middle values in a data range.

Explore common Excel formula errors, including hash tag error, divide by zero, non-applicable, hash tag name question mark, reference error, and hash tag value error, and learn their causes.

Practice completing Excel calculations using sum, average, max, min, count, and round up formulas; learn cell referencing, formula bar usage, and evaluating percentage contributions.

Copy and paste data in Excel across files or into PowerPoint and Microsoft Word using the menu, right-click, or keyboard shortcuts like Ctrl+C and Ctrl+V.

Learn how to paste with formatting or paste values only in Excel, with previews. See how dates may turn into numbers and how to apply or copy formatting.

Master Excel copying and pasting with formulas; use paste values only to show numbers, and beware losing formulas without undo or saving.

Master advanced copy and paste in Excel by transposing data, using paste special to paste values and formats, and dividing scores by 100 for percentage results.

Copy and paste formulas or drag the fill handle to apply them across rows and columns, using sum to compute totals, and verify accuracy by checking the formula bar.

Learn how to fix and manage cell references in Excel using the dollar sign to create absolute references, enabling accurate copying and percentage calculations across rows and columns.

Clean and standardize text by using TRIM to remove spaces and PROPER to apply proper case, then paste values to replace formulas with clean data.

Learn how to use the left, right, and mid functions to extract specific characters from text strings by specifying the start position and length.

Learn to use the concatenate function to join first and last names from separate cells, insert spaces or other text between fields, and create full names in Excel.

Explore the powerful if function, a logical formula in Excel that returns values based on conditions, uses operators like equal, greater than, not equal, and can output text.

Learn to apply and/or criteria in the Excel if function to test multiple conditions, returning 1 when met and 0 otherwise, with examples using scores, colors, and gender.

Learn how to use countif and sumif with criteria to count items and sum values in Excel, using ranges, sum ranges, and operators like greater than or equal to 10.

Master the vlookup vertical lookup to find a value in the first column and return data from the matching row. Build with lookup value, table, column index, and exact match.

Master the hlookup formula to perform horizontal lookups on a transposed table, using a table array, a fixed row index, and exact-match lookups to return seventh-row values.

Anchor a cell and use the offset function to move across rows and down columns, pulling diagonal values and commodity-to-commodity correlations for dynamic reports beyond vlookup.