IT Skills

Microsoft Excel Basics




1. Basics of Microsoft Excel

Key Features:

  • Workbook Structure:
  • An Excel file is called a workbook; each workbook contains worksheets (tabs at the bottom).

  • Columns are labeled alphabetically (A, B, C), and rows are numbered (1, 2, 3).

  • Navigation:

  • Use the arrow keys or click cells to navigate.

  • Select multiple cells by dragging the cursor or using Shift + Arrow Keys.

  • Common Functions:

  • Data Entry: Click a cell and type. Press Enter to move to the next cell.
  • Formatting: Found in the "Home" tab (font, colors, alignment, borders).

  • Basic Calculations: Use = to start a formula (e.g., =A1+A2 for addition).

  • Saving Files:

  • Save as .xlsx for full functionality.
  • Export as .csv for simple, text-only data.

Keyboard Shortcuts:

  • Copy/Paste: Ctrl+C / Ctrl+V.
  • Save: Ctrl+S.
  • Auto-Sum: Alt+=.
  • Insert Current Date: Ctrl+;.
  • Delete Selected Cells: Ctrl+Minus (-).

2. Examples of Excel in Action

Use Cases:

  1. Budget Planning:
  2. Create columns for categories (e.g., Rent, Groceries, Utilities) and rows for months.

  3. Use formulas to calculate totals and remaining balance.

  4. Data Analysis:

  5. Organize sales data into rows and columns.

  6. Use PivotTables to summarize data by categories.

  7. Inventory Management:

  8. Track product names, quantities, and restock levels.

  9. Add conditional formatting to highlight low-stock items.

  10. Attendance Tracking:

  11. Create a table with employee names in rows and dates in columns.
  12. Use checkmarks or P for present and A for absent.

3. Essential Excel Formulas

Basic Formulas:

  1. Addition: =A1+A2
  2. Subtraction: =A1-A2
  3. Multiplication: =A1*A2
  4. Division: =A1/A2
  5. AutoSum: =SUM(A1:A10)

Text Functions:

  1. Concatenate: =CONCAT(A1, " ", B1) (joins text from cells).
  2. Length of Text: =LEN(A1) (returns the number of characters in a cell).
  3. Uppercase: =UPPER(A1); Lowercase: =LOWER(A1).

Date and Time Functions:

  1. Today’s Date: =TODAY()
  2. Current Time: =NOW()
  3. Days Between Dates: =DATEDIF(A1, B1, "d") (where A1 and B1 are dates).

Logical Functions:

  1. IF Statement: =IF(A1>10, "Yes", "No") (checks if a value meets a condition).
  2. AND: =AND(A1>10, B1<20) (returns TRUE if both conditions are met).
  3. OR: =OR(A1>10, B1<20) (returns TRUE if at least one condition is met).

Lookup Functions:

  1. VLOOKUP: =VLOOKUP(lookup_value, table_array, col_index, range_lookup)
  2. Example: Find a product price by searching for its ID.
  3. HLOOKUP: Similar to VLOOKUP but searches horizontally.
  4. INDEX-MATCH Combo: More flexible than VLOOKUP:
  5. =INDEX(column_to_return, MATCH(lookup_value, lookup_column, 0)).

Statistical Functions:

  1. Average: =AVERAGE(A1:A10)
  2. Maximum Value: =MAX(A1:A10)
  3. Minimum Value: =MIN(A1:A10)

4. Specific Scenarios and Tips

Scenario 1: Creating a Monthly Budget Sheet

  1. Layout:
  2. Column A: Expense categories (e.g., Rent, Utilities).
  3. Column B: Budgeted Amount.
  4. Column C: Actual Spending.

  5. Column D: Difference (=B2-C2).

  6. Add Total Row:

  7. Use =SUM(B2:B10) to calculate the total budget.
  8. Apply conditional formatting to highlight overspending in red.

Scenario 2: Sales Data Analysis

  1. Organize Data:
  2. Column A: Product Names.
  3. Column B: Units Sold.
  4. Column C: Unit Price.

  5. Column D: Revenue (=B2*C2).

  6. Create a PivotTable:

  7. Select data Insert PivotTable.

  8. Drag "Product Names" to Rows and "Revenue" to Values.

  9. Visualize with Charts:

  10. Insert a bar chart or pie chart from Insert Charts.

Scenario 3: Employee Attendance Tracker

  1. Table Layout:
  2. Column A: Employee Names.

  3. Columns B onward: Dates (e.g., 01/01/2025).

  4. Mark Attendance:

  5. Use P for Present and A for Absent.

  6. Count Attendance:

  7. Use =COUNTIF(B2:Z2, "P") to count presents for an employee.
  8. Add conditional formatting to highlight absentees.

Scenario 4: Automating Invoice Calculations

  1. Invoice Structure:
  2. Column A: Item Description.
  3. Column B: Quantity.
  4. Column C: Unit Price.

  5. Column D: Total (=B2*C2).

  6. Add Tax:

  7. Set a tax rate in a separate cell (e.g., 10% in F1).

  8. Calculate tax: =SUM(D2:D10)*F1.

  9. Final Total:

  10. Add subtotal and tax: =SUM(D2:D10) + F1.

Scenario 5: Gantt Chart for Project Tracking

  1. Data Layout:
  2. Column A: Task Names.
  3. Column B: Start Date.
  4. Column C: End Date.

  5. Column D: Duration (=C2-B2).

  6. Create a Bar Chart:

  7. Highlight data Insert Bar Chart Stacked Bar.
  8. Adjust colors and axis labels for clarity.

5. Advanced Excel Tips

  1. Freeze Panes: Keep headers visible while scrolling:

  2. Go to View Freeze Panes Freeze Top Row.

  3. Data Validation: Restrict input with dropdowns:

  4. Select cells Data Data Validation List Add options.

  5. Conditional Formatting: Highlight cells based on criteria:

  6. Home Conditional Formatting Choose a rule (e.g., highlight values > 100).

  7. Protect Sheets: Prevent accidental edits:

  8. Review Protect Sheet Set a password.

  9. Split Text: Split first and last names:

  10. Use =LEFT(A1, FIND(" ", A1)-1) for the first name.
  11. Use =RIGHT(A1, LEN(A1)-FIND(" ", A1)) for the last name.

6. Resources for Templates

  1. Excel Built-In Templates:

  2. Go to File New and search for templates like budgets, invoices, or schedules.

  3. Microsoft Office Templates Online:

  4. (Microsoft Templates) for free downloads.

  5. Third-Party Sites:

  6. Vertex42: Free, high-quality templates (vertex42.com).
  7. Spreadsheet123: Ready-to-use Excel tools (spreadsheet123.com).
❤ If you liked this, consider supporting us by checking out Tiny Skills - 250+ Top Work & Personal Skills Made Easy