Productivity Tools Electronic Spreadsheets - Google Sheets

• Spreadsheet - An electronic document organized into rows, columns and cells that can store, organize and manipulate data.
• Workbook - The overarching spreadsheet file that can contain one or more worksheets. 
• Worksheet - A subset sheet within an Excel workbook that contains columns, rows and cells.
• Cell - The intersection between a row and column in a worksheet that stores data.
•  Range - A group of two or more cells that share a common link. 
• Formula - An expression that performs a calculation using values in cells.
• Function - A predefined formula in Excel that simplifies complex calculations. 
• Relative Reference - A cell reference that adjusts automatically when copied based on its location.
• Absolute Reference - A cell reference prefixed with $ to remain constant when copied. 
• formula - Perform calculations using data in cells
• formulas - start with = sign
• formula element 

  - cell references

     - Math operators

     - Functions

     - Values
• Cell referencing 

     - Relative and Absolute
• Math formulas

     - Addition, subtraction, multiplication, division

     - Exponentiation, modulus

     - Insert operators between cell references
• Joining text 

     - Use & or CONCATENATE function 
• Joining text 

     - Use & or CONCATENATE function 
• Comparisons

     - =, < >, >= <=
• Creating Basic Formulas

  • math formulas
  • joining text
  • comparisons
• Referencing cells

     - Insert cell reference in formula

     - Or click/select cell to add reference
• Reference types

     - Relative

     - Absolute

     - Mixed
•    - Relative: Adjusts when copied
• Absolute ($): Stays fixed when copied
• Mixed: Locks either column or row
• functions - Predefined formulas
• functions:

  •  Common categories: Math, text, date, lookup
• Syntax: 

     - Start with = 

     - Function name

     - Input arguments in ()
• Common Math Functions

  SUM - Sums a range

  AVERAGE - Calculates mean average

  MAX/MIN - Finds highest/lowest values

  COUNT/COUNTA - Counts cells

  INT - Rounds down to integer

  RAND/RANDBETWEEN - Random number generation
• Text Functions  

  LEN: Returns text length

  LEFT/RIGHT: Extract a substring 

  LOWER/UPPER: Change case  

  TRIM: Remove extra whitespace

  CONCAT: Joins text strings together
• Date and Time Functions

  - TODAY/NOW: Returns current date or date+time

  - DAY/MONTH/YEAR: Get part of a date 

  - WEEKDAY: Finds day of week

  - WORKDAY: Counts work days between dates

  - DATEDIF: Date difference in days, months, years
• Lookup Functions

  - VLOOKUP - Vertical lookup based on match

  - HLOOKUP - Horizontal lookup

  • INDEX/MATCH - Flexible lookup alternative
• Logical Functions

  • IF - Evaluates a true/false condition
  • AND/OR - Combine multiple logical tests
  • ISERROR/ISBLANK - Test for error or blank
  • IFS - Checks multiple IF conditions
• Collaborating and Sharing

  Syncing data across devices 

  Offline editing

  Sharing access 

      - View only versus edit access

  Version history
• Data Analysis  

  • Pivot tables

      - Rearranging, filtering data 

      - Summarizing values

  • Slicers
  • Data connections to other sources
• • Pivot tables

      - Rearranging, filtering data 

      - Summarizing values
• Common Math Functions

  SUM: Sums a range

  AVERAGE: Calculates mean average

  MAX/MIN: Finds highest/lowest values

  COUNT/COUNTA: Counts cells

  INT: Rounds down to integer

  • RAND/RANDBETWEEN: Random number generation
• Text Functions  

  LEN: Returns text length

  LEFT/RIGHT: Extract a substring 

  LOWER/UPPER: Change case  

  TRIM: Remove extra whitespace

  • CONCAT: Joins text strings together 
• Date and Time Functions

  TODAY/NOW: Returns current date or date+time

  DAY/MONTH/YEAR: Get part of a date 

  WEEKDAY: Finds day of week

  WORKDAY: Counts work days between dates  

  • DATEDIF: Date difference in days, months, years
• Lookup Functions

  - VLOOKUP: Vertical lookup based on match

  HLOOKUP: Horizontal lookup  

  • INDEX/MATCH: Flexible lookup alternative 
• Logical Functions

  - IF: Evaluates a true/false condition

  AND/OR: Combine multiple logical tests

  - ISERROR/ISBLANK: Test for error or blank

  • IFS: Checks multiple IF conditions