Excel Basics Cheat Sheet

This cheat sheet covers fundamental Excel concepts, including definitions, operators, logical and mathematical functions, data types, cell referencing, and data manipulation techniques. It's designed to help users quickly understand and apply core Excel functionalities.

Core Principles

• Understand basic Excel terminology: Spreadsheet, Workbook, Worksheet, Cell, Formula, Cell Reference, Cell Range.
• Master arithmetic operators (+, -, *, /, %, ^) for calculations.
• Utilize numeric comparison operators (=, <>, >, >=, <, <=) for data evaluation.
• Leverage logical functions (IF, IFS, IFERROR, SWITCH) for conditional outcomes.
• Employ text functions (LEN, REPT, TEXTSPLIT, MID, UPPER, LOWER, PROPER) for string manipulation.
• Apply math functions (SUM, AVERAGE, MAX, MIN, MEDIAN, ROUND, etc.) for numerical analysis.
• Understand data types and how to check them (ISNUMBER, ISTEXT, ISLOGICAL).
• Learn cell referencing (relative, absolute with $) and range specification (start:end).
• Use counting functions (COUNT, COUNTA, COUNTBLANK) to analyze data quantity.
• Explore data manipulation functions (FILTER, SORT, SORTBY, UNIQUE, SEQUENCE) for data organization.

Action Steps

• Open Microsoft Excel.
• Click 'Help' menu for assistance.
• Use the search bar in Help for specific topics.
• Click a cell to select it.
• Right-click and choose 'New Comment' to add notes.
• Type your comment in the text box.
• Click the green arrow to save the comment.
• Enter formulas starting with '='.
• Use cell references (e.g., A1, B2) in formulas.
• Specify cell ranges using the start:end format (e.g., B2:B5).
• Apply absolute references ($B$2) to prevent changes when copying formulas.

Formulas

• Arithmetic: =A2+A3, =A4-B4, =A6*B1, =C3/B4, =C5%, =B1^C1
• Numeric Comparison: =A2=B2, =A3>B3, =A1<B1, =A1<>B1, =A3>=B3, =A2<=B2
• Logical IF: =IF(cond, return_if_true, return_if_false)
• Logical IFS: =IFS(cond1, return1, cond2, return2, ...)
• Error Handling: =IFERROR(value, value_if_error)
• Conditional Logic: =SWITCH(value, choice1, return1, choice2, return2, ...)
• Counting: =COUNT(A5:E5), =COUNTA(A5:E5), =COUNTBLANK(A5:E5)
• Math: =SUM(A1:A6), =AVERAGE(A1:A6), =MAX(A1:A6), =MIN(A1:A6), =MEDIAN(A1:A6)
• Rounding: =ROUND(PI(), 2), =CEILING(PI(), 0.1), =FLOOR(PI(), 0.1)
• Text Length: =LEN(D5)
• Combine Text: ="Hello " & D1 & "!"
• Repeat Text: =REPT(D6, 3)
• Split Text: =TEXTSPLIT(D4, "o")
• Extract Substring: =MID(text, start, length)
• Case Conversion: =UPPER(text), =LOWER(text), =PROPER(text)
• Data Manipulation: =FILTER(range, condition), =SORT(range, sort_column)
• Unique Values: =UNIQUE(range)
• Sequence Generation: =SEQUENCE(rows, columns, start, step)

Key Terms

• Spreadsheet: An application like Excel for storing, calculating, and organizing data.
• Workbook: An Excel file containing one or more worksheets.
• Worksheet: A single page within a workbook, consisting of a grid of cells.
• Cell: A rectangular box in a worksheet that holds data, formulas, or content.
• Formula: A calculation starting with '=' containing functions, operators, and cell references.
• Cell Reference: The address of a cell (e.g., D7, indicating column D, row 7).
• Cell Range: A group of adjacent cells, specified by its top-left and bottom-right cells (e.g., A1:C3).

Pro Tips

• Use `IFS()` for multiple conditions instead of nested `IF()` statements.
• Employ `IFERROR()` to gracefully handle potential calculation errors.
• `SWITCH()` is efficient for multiple exact matches.
• Absolute references (`$B$2`) are crucial when copying formulas that must always point to the same cell.
• Use `COUNTA()` to quickly check for non-empty cells.
• `TEXTSPLIT()` is powerful for parsing delimited text data.
• `FILTER()`, `SORT()`, and `UNIQUE()` are dynamic array functions that can revolutionize data analysis.
• The `SEQUENCE()` function simplifies generating lists of numbers for various purposes.

Pitfalls to Avoid

• Forgetting to start formulas with '='.
• Using relative references when absolute references are needed, leading to incorrect results after copying.
• Confusing `COUNT` (numbers only) with `COUNTA` (non-empty cells).
• Incorrectly applying logical operators in `AND()` or `OR()` functions.
• Not understanding the difference between `SUMIF` (single criterion) and `SUMIFS` (multiple criteria).
• Assuming text that looks like numbers will be treated as numbers by all functions.
• Over-reliance on nested `IF` statements, which can become unmanageable.

Myth vs Reality

• Excel only works with numbers.: Excel handles various data types, including text, dates, times, and logical values.
• All formulas are complex.: Many basic formulas use simple operators like '+' or '-' and are easy to understand.
• Cell references change automatically and always correctly.: Relative references change when copied, but absolute references (` ) must be used intentionally to fix them."}}]} ) must be used intentionally to fix them.

Real World Examples

• Calculating total sales from a list of transactions.: Use the `SUM()` function on a range of sales figures.
• Determining if a student passed based on a score.: Use the `IF()` function: `=IF(ScoreCell>=60, "Pass", "Fail")`.
• Counting how many employees are in a specific department.: Use `COUNTIF()`: `=COUNTIF(DepartmentRange, "Sales")`.
• Finding the highest temperature recorded in a week.: Use the `MAX()` function on a range of temperature data.
• Formatting a product name to appear as 'Product Name'.: Use the `PROPER()` function: `=PROPER(ProductNameCell)`.

People

• Microsoft Excel: Software application for spreadsheets