IT Skills

Google Sheets Basics




Google Sheets is a versatile and collaborative spreadsheet tool that’s ideal for data organization, analysis, and reporting. Google Sheets basics, practical examples, formulas, and specific scenarios to help you become proficient.

1. Basics of Google Sheets

A. Key Features of Google Sheets

  1. Cloud-Based Collaboration:
  2. Multiple users can edit the same sheet in real time.
  3. Spreadsheet Organization:
  4. Sheets consist of rows (numbers) and columns (letters) forming cells (e.g., A1).
  5. Tabs:
  6. Add multiple sheets within one file for better organization.
  7. Share and Permission Control:
  8. Grant specific users access to view, comment, or edit.
  9. Add-Ons:
  10. Extend functionality with tools like Google Analytics, Apps Script, and Supermetrics.

B. Basic Operations

  1. Entering Data:
  2. Click a cell, type text, numbers, or formulas, and press Enter.
  3. Formatting Data:
  4. Use the toolbar to bold text, adjust font sizes, or apply conditional formatting.
  5. Sorting and Filtering:
  6. Organize data by sorting (ascending/descending) or adding filters.
  7. Inserting Charts:
  8. Highlight data > Click Insert > Chart to create bar, line, or pie charts.

2. Examples of Google Sheets Usage

A. Simple Budget Tracker

  • Data Layout:
    | Category | Amount |
    |--------------|------------|
    | Rent | $1000 |
    | Groceries | $300 |
    | Entertainment| $150 |

  • Total Formula: =SUM(B2:B4)

  • Visualization: Insert a pie chart to visualize spending categories.

B. Task Management Sheet

  • Columns: Task Name, Priority, Due Date, Status

  • Example Data:
    | Task Name | Priority | Due Date | Status |
    |---------------|--------------|--------------|------------|
    | Design Website| High | 2025-01-30 | In Progress|

  • Conditional Formatting:

  • Highlight overdue tasks: Format cells in the Due Date column where the date is before today.
  • Formula: =TODAY()>C2

C. Team Attendance Tracker

  • Columns: Name, Date, Present (Y/N)
  • Example Formula:
  • Count Present Days: =COUNTIF(C2:C30, "Y")

3. Common Google Sheets Formulas

A. Basic Formulas

  1. SUM: Adds numbers in a range.
  2. Example: =SUM(A1:A10)
  3. AVERAGE: Finds the average of a range.
  4. Example: =AVERAGE(B2:B10)
  5. IF: Returns a value based on a condition.
  6. Example: =IF(B2>100, "Above Target", "Below Target")

B. Text Functions

  1. CONCAT: Combines text from multiple cells.
  2. Example: =CONCAT(A2, " ", B2)
  3. SPLIT: Splits text into columns.
  4. Example: =SPLIT(A2, ",")
  5. LEFT/RIGHT: Extracts part of a string.
  6. Example: =LEFT(A2, 5)

C. Lookup and Reference Functions

  1. VLOOKUP: Finds data in a table based on a value.
  2. Example: =VLOOKUP("Apple", A2:C10, 2, FALSE)
  3. HLOOKUP: Similar to VLOOKUP but searches horizontally.
  4. Example: =HLOOKUP("2025", A1:D5, 2, FALSE)
  5. INDEX-MATCH: A more flexible alternative to VLOOKUP.
  6. Example: =INDEX(B2:B10, MATCH("Apple", A2:A10, 0))

D. Date and Time Functions

  1. TODAY: Returns the current date.
  2. Example: =TODAY()
  3. DATEDIF: Calculates the difference between two dates.
  4. Example: =DATEDIF(A2, B2, "D")

E. Advanced Functions

  1. ARRAYFORMULA: Applies a formula to an entire range.
  2. Example: =ARRAYFORMULA(A2:A10*B2:B10)
  3. IMPORTRANGE: Imports data from another Google Sheet.
  4. Example: =IMPORTRANGE("sheet_url", "Sheet1!A1:B10")
  5. QUERY: Filters and sorts data like SQL.
  6. Example: =QUERY(A1:C10, "SELECT A, B WHERE C > 50")

4. Specific Scenarios for Google Sheets

Scenario 1: Analyzing Sales Data

Problem: Track sales performance across regions.
Solution:
1. Data Table:
| Region | Sales |
|------------|-----------|
| North | $5000 |
| South | $3000 |

  1. Formulas:
  2. Total Sales: =SUM(B2:B10)
  3. Sales by Region: Use a Pivot Table:
    • Select data > Insert > Pivot Table > Add Region and Sales columns.

Scenario 2: Monthly Expense Tracker

Problem: Organize and calculate monthly expenses.
Solution:
1. Data Layout:
| Date | Description | Category | Amount |
|----------|-----------------|--------------|------------|
| 01/05 | Rent | Housing | $1000 |
| 01/07 | Groceries | Food | $200 |

  1. Formulas:
  2. Monthly Total: =SUM(D2:D30)
  3. Category Total: Use =SUMIF(C2:C30, "Housing", D2:D30)

Scenario 3: Employee Performance Dashboard

Problem: Visualize employee performance.
Solution:
1. Columns: Employee Name, Sales, Rating
2. Steps:
- Add bar charts for sales performance.
- Use conditional formatting to highlight top performers.

Scenario 4: Project Management Tracker

Problem: Track task progress for a team project.
Solution:
1. Columns: Task, Assigned To, Deadline, Status, Progress
2. Formulas:
- Days Remaining: =DATEDIF(TODAY(), C2, "D")
- Completed Tasks: =COUNTIF(E2:E30, "Complete")

Scenario 5: Automating Data Import Across Sheets

Problem: Pull data from a master sheet into individual department sheets.
Solution:
1. Use IMPORTRANGE:
- Formula: =IMPORTRANGE("master_sheet_url", "Sheet1!A1:D50")
2. Combine with FILTER to show specific department data:
- Formula: =FILTER(A2:D50, C2:C50="Sales")

Scenario 6: Tracking Attendance

Problem: Monitor team attendance over a month.
Solution:
1. Data Layout:
| Name | 01/01 | 01/02 | 01/03 | |
|----------|-----------|-----------|-----------|-----|
| John | P | A | P | |

  1. Formulas:
  2. Count Present Days: =COUNTIF(B2:Z2, "P")
  3. Calculate Attendance %: =COUNTIF(B2:Z2, "P")/COLUMNS(B2:Z2)*100

5. Tips for Optimizing Google Sheets

  1. Freeze Rows/Columns:

  2. Keep headers visible while scrolling: View > Freeze > 1 Row/1 Column.

  3. Protect Ranges:

  4. Prevent edits to specific cells: Data > Protect Sheets and Ranges.

  5. Custom Data Validation:

  6. Restrict inputs to specific values (e.g., dropdowns).

  7. Data > Data Validation: Add criteria like a list or number range.

  8. Keyboard Shortcuts:

  9. Add a new row: Alt + Shift + = (Windows) or Option + Shift + = (Mac).

  10. Open filter view: Ctrl + Shift + L (Windows) or Cmd + Shift + L (Mac).

  11. Collaborative Comments:

  12. Right-click a cell > Comment to tag collaborators and assign tasks.

By mastering these basics, formulas, and specific workflows, you can use Google Sheets for everything from data analysis to project management.

❤ If you liked this, consider supporting us by checking out Tiny Skills - 250+ Top Work & Personal Skills Made Easy