Excel Basics Cheat Sheet
This cheat sheet covers fundamental Microsoft Excel concepts, including definitions, operators, logical and mathematical functions, data manipulation, and flow control. It's designed for users of the Microsoft 365 version, noting slight differences in other versions.
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 testing conditions.
- Employ logical functions (IF, IFS, SWITCH, IFERROR) for conditional logic and error handling.
- Leverage data type functions (ISNUMBER, ISTEXT, ISLOGICAL) to check cell content.
- Use math functions (SUM, AVERAGE, MAX, MIN, ROUND, etc.) for statistical analysis.
- Manipulate text with functions like LEN, UPPER, LOWER, PROPER, MID, and TEXTSPLIT.
- Apply data manipulation functions (FILTER, SORT, UNIQUE, SEQUENCE) for organizing and extracting data.
- Understand cell referencing (relative, absolute) for formula flexibility.
Action Steps
- Open Microsoft Excel.
- Click the 'Help' menu.
- Use the search bar or browse topics for assistance.
- To add a comment: Right-click cell > 'New Comment', or Insert > 'New Comment'.
- Type your comment in the text box.
- Click the green arrow to save the comment.
Formulas
- Arithmetic: =A2+A3, =A4-B4, =A6*B1, =C3/B4, =C5%, =B1^C1
- Comparison: =A1=B1, =A2<>B2, =A3>B3, =A2<B2, =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)
- Switch: =SWITCH(value, choice1, return1, choice2, return2, ...)
- Count: =COUNT(range), =COUNTA(range), =COUNTBLANK(range)
- Sum: =SUM(range)
- Average: =AVERAGE(range)
- Max/Min: =MAX(range), =MIN(range)
- Text Length: =LEN(text)
- Text Extract: =MID(text, start, length)
- Text Case: =UPPER(text), =LOWER(text), =PROPER(text)
- Text Split: =TEXTSPLIT(text, delimiter)
- Data Manipulation: =FILTER(range, condition), =SORT(range, sort_column)
- Sequence: =SEQUENCE(rows, columns, start, step)
Key Terms
- Spreadsheet: An application like Excel for storing, calculating, and organizing data.
- Workbook: A file containing one or more worksheets.
- Worksheet: A single page in a workbook, structured as 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, values, and cell references.
- Cell Reference: The location of a cell (e.g., D7 for the 4th column, 7th row).
- Cell Range: A group of adjacent cells, specified by the top-left and bottom-right cells (e.g., A1:C3).
Real World Examples
- Calculating total sales from a list of transactions.: Use the SUM() function with a cell range like =SUM(B2:B10).
- Determining if a student passed based on a score.: Use the IF() function: =IF(A2>=60, "Pass", "Fail").
- Categorizing expenses based on amount.: Use the IFS() function: =IFS(B2<50, "Low", B2<200, "Medium", B2>=200, "High").
- Finding the average price of products in a specific category.: Use AVERAGEIF(): =AVERAGEIF(C2:C10, "Electronics", B2:B10).
Timeline
- 1/1/2023: Data entry for 'World' in cell D1.
- 1/2/2023: Data entry for 'Solar System' in cell D2.
- 1/3/2023: Data entry for 'Milky Way' in cell D3.
- 1/4/2023: Data entry for 'Local Group' in cell D4.
- 1/5/2023: Data entry for 'Laniakea Supercluster' in cell D5.
- 1/6/2023: Data entry for 'Universe' in cell D6.
People
- Microsoft Excel: Software application for spreadsheets.
Quiz
- What does the formula =SUM(A1:A5) do?: Adds all the numbers in the cells from A1 to A5
- Which operator tests for inequality?: <>
- What is the purpose of the IFERROR() function?: To return a specified value if a formula results in an error
More like this