A. Cells

·        A cell is the basic building block of a spreadsheet.

·        Each cell is identified by its column letter and row number.

·        You can click on a cell to select it.

B. Rows and Columns

·        Rows run horizontally from left to right and are numbered.

·        Columns run vertically from top to bottom and are labeled with letters.

C. Main Menu Items

1. File

The "File" tab in Google Sheets contains various options for managing your spreadsheet, including creating new spreadsheets, opening existing ones, saving, printing, and more. Let's break down each of the main items:

2. New

Meaning: This option allows you to create a new spreadsheet from scratch. You can choose from a blank spreadsheet, various templates, or even upload an existing file from your computer.

Usage: Clicking on "New" opens a dropdown menu where you can select the type of file you want to create (e.g., Blank spreadsheet, From a template, From a file).

Functions:

·       Blank spreadsheet: Opens a new, empty Google Sheets file.

·       From a template: Provides a variety of pre-designed spreadsheet templates for different purposes, such as budgets, schedules,  calendars, and more.

·       From a file: Allows you to upload a spreadsheet file from your computer to Google Sheets.

3.     Open

Meaning: This option lets you open an existing spreadsheet that is saved in your Google Drive or one that you've recently worked on.

Usage: Click on "Open" to see a list of your recent files. You can also click "Open from Google Drive" to browse your Drive for more files.

Functions:

·   Opens the selected spreadsheet for viewing and editing. 

4.     Import

Meaning: Import data from other sources into your current spreadsheet. This could be data from other spreadsheets, CSV files, or even  from the web.

Usage: Click on "Import" to see options such as importing from another spreadsheet, uploading a file, or using a URL.

Functions:

·       Import data: Brings data from another spreadsheet into your current sheet.

·       Upload: Allows you to upload a file (like a CSV or Excel file) from your computer.

·       From Drive: Imports data from a Google Drive file.

·       From URL: Fetches data from a web URL and imports it into your spreadsheet. 

 5.   Make a Copy

Meaning: Creates a duplicate copy of your current spreadsheet. This copy will be stored in your Google Drive.

Usage: Click on "Make a copy" to create a duplicate of the current spreadsheet you're working on.

Functions:

·       Creates an exact replica of the current sheet, including all data, formatting, and settings. 

  6.     Share

Meaning: Share your spreadsheet with others, allowing them to view, comment, or edit it based on the permissions you set.

Usage: Click on "Share" to open the sharing settings.

Functions:

·       Enter email addresses: Add the email addresses of the people you want to share the spreadsheet with.

·       Choose permissions: Select whether they can view, comment, or edit the spreadsheet.

·       Get shareable link: Obtain a link to the spreadsheet that you can share with others.

·       Set visibility options: Decide whether the spreadsheet can be viewed by anyone with the link or only specific people. 

7. Version history

Meaning: View and restore previous versions of your spreadsheet. This is useful for tracking changes and reverting to an older version if needed.

Usage: Click on "Version history" to see a list of saved versions.

Functions:

·       See version history: Shows a list of timestamps when changes were made, along with who made them.

·       Name current version: Allows you to label the current version for easier reference.

·       Restore this version: Reverts the spreadsheet to the selected version.

8. Download

Meaning: Download a copy of your spreadsheet in various formats, such as Excel (.xlsx), PDF, or CSV.

Usage: Click on "Download" to see options for different file formats.

Functions:

·       Microsoft Excel (.xlsx): Downloads a copy of the spreadsheet in Excel format.

·       PDF document: Saves the spreadsheet as a PDF file.

·       Web page (.html): Downloads a copy of the spreadsheet as an HTML file.

·       Comma-separated values (.csv, current sheet): Downloads the current sheet as a CSV file.

9. Email Collaborators

Meaning: Send an email to collaborators with a link to the spreadsheet. You can include a message in the email.

Usage: Click on "Email collaborators" to compose an email.

Functions:

·       Opens a new email window with a link to the spreadsheet.

·       Allows you to write a message to your collaborators. 

10. Print

Meaning: Print a physical copy of your spreadsheet or save it as a PDF.

Usage: Click on "Print" to see printing options.

Functions:

·       Opens the print dialog where you can select printing options such as paper size, orientation, and more.

·       You can also choose to save the spreadsheet as a PDF file. 

D. Edit

The "Edit" tab in Google Sheets contains various options for editing the content of your spreadsheet, including cut, copy, paste, find, and replace, and more. Let's break down each of the main items:

1. Undo

Meaning: This option allows you to undo your last action in the spreadsheet. You can keep clicking "Undo" to reverse multiple actions.

Usage: Click on "Undo" or use the keyboard shortcut Ctrl + Z (Windows/Linux) or Cmd + Z (Mac) to undo the last action.

Functions:

·       Reverts the last action performed in the spreadsheet. 

2. Redo

Meaning: This option allows you to redo an action that you have undone using the "Undo" option.

Usage: Click on "Redo" or use the keyboard shortcut Ctrl + Y (Windows/Linux) or Cmd + Y (Mac) to redo the last action.

Functions:

·       Reapplies the action that was undone using the "Undo" option. 

3. Cut

Meaning: Cut selected cells or data from the spreadsheet, removing them from their original location.

Usage: Select the cells or data you want to cut, then click on "Cut" or use the keyboard shortcut Ctrl + X (Windows/Linux) or Cmd + X (Mac).

Functions:

·       Removes the selected cells or data from the spreadsheet and places them in the clipboard for pasting elsewhere. 

4. Copy

Meaning: Copy selected cells or data from the spreadsheet, without removing them from their original location.

Usage: Select the cells or data you want to copy, then click on "Copy" or use the keyboard shortcut Ctrl + C (Windows/Linux) or Cmd +  C (Mac).

Functions:

·       Creates a duplicate of the selected cells or data in the clipboard for pasting elsewhere.

5. Paste

Meaning: Paste content from the clipboard into the spreadsheet.

Usage: Click on the cell where you want to paste the content, then click on "Paste" or use the keyboard shortcut Ctrl + V (Windows/Linux) or Cmd + V (Mac).

Functions:

·       Places the content from the clipboard into the selected cell or range of cells.

6. Paste Special

Meaning: Paste content from the clipboard using special paste options, such as values only, formatting only, formulas only, and more.

Usage: Click on "Paste special" to see a dropdown menu of paste options, then select the desired option.

Functions:

·       Paste values only: Pastes the values from the clipboard without any formatting or formulas.

·       Paste format only: Pastes the formatting from the clipboard without changing the values or formulas.

·       Paste formulas only: Pastes the formulas from the clipboard without the formatting or values.

·       Paste transpose: Swaps the rows and columns of the copied data when pasted.

·       Paste data validation only: Pastes the data validation rules from the clipboard.

7. Find and Replace

Meaning: Search for specific content within your spreadsheet and replace it with other content.

Usage: Click on "Find and replace" to open the search and replace dialog, then enter the search and replacement terms.

Functions:

·       Find: Locates instances of the specified content within the spreadsheet.

·       Replace: Replaces the found instances with the specified replacement content.

·       Replace all: Replaces all instances of the specified content with the replacement content. 

8. Delete

Meaning: Delete selected cells, rows, columns, or sheets from the spreadsheet.

Usage: Select the cells, rows, columns, or sheets you want to delete, then click on "Delete" or use the keyboard shortcut Ctrl + - (Windows/Linux) or Cmd + - (Mac).

Functions:

·       Delete cells: Removes the selected cells from the spreadsheet, shifting other cells accordingly.

·       Delete rows: Removes the selected rows from the spreadsheet.

·       Delete columns: Removes the selected columns from the spreadsheet.

·       Delete sheet: Deletes the entire sheet from the workbook.

E. View

The "View" tab in Google Sheets contains various options for changing the view and appearance of your spreadsheet, including zooming in or out, freezing rows or columns, and more. Let's break down each of the main items:

1. Show

Meaning: Toggle the visibility of certain elements in the spreadsheet, such as gridlines, row numbers, and formulas.

Usage: Click on "Show" to see options for displaying different elements.

Functions:

·       Gridlines: Show or hide the gridlines that separate cells.

·       Row numbers: Show or hide the numbers on the left side of the sheet that indicate row numbers.

·       Column letters: Show or hide the letters at the top of the sheet that indicate column letters.

·       Formula bar: Show or hide the formula bar at the top of the sheet where you can edit cell formulas.

·       Full formulas: Show all cell formulas instead of just the results.

·       Notes: Show or hide notes (comments) associated with cells.

2. Freeze

Meaning: Freeze rows or columns to keep them visible while scrolling through the rest of the spreadsheet.

Usage: Click on "Freeze" to see options for freezing rows or columns.

Functions:

·       Freeze 1 row: Keeps the top row visible while scrolling.

·       Freeze 2 rows: Keeps the top two rows visible while scrolling.

·       Freeze 1 column: Keeps the leftmost column visible while scrolling.

·       Freeze 2 columns: Keeps the left two columns visible while scrolling.

·       Custom: Allows you to specify a custom number of rows or columns to freeze.

3. Group

Meaning: Group rows or columns together for easier organization and collapsible sections.

Usage: Click on "Group" to create a new group or manage existing groups.

Functions:

·       Create a new group: Select the rows or columns you want to group, then click "Group."

·       Manage groups: Expand, collapse, or remove existing groups.

4. Comments

Meaning: Add comments to cells for additional information or collaboration.

Usage: Click on "Comments" to add a comment to the selected cell or view existing comments.

Functions:

·       Insert comment: Add a new comment to the selected cell.

·       View comments: See existing comments in the spreadsheet.

·       Resolve: Mark a comment as resolved to hide it from view.

5. Hidden sheets

Meaning: Manage hidden sheets within your spreadsheet.

Usage: Click on "Hidden sheets" to see a list of hidden sheets and unhide them if needed.

Functions:

·       Show all sheets: Unhide all hidden sheets in the spreadsheet.

·       Unhide sheet: Select a hidden sheet from the list to make it visible again.

6. Zoom

Meaning: Adjust the zoom level to change the size of the cells in the spreadsheet.

Usage: Click on "Zoom" to see options for zooming in or out.

Functions:

·       Zoom in: Increases the size of the cells, making them appear larger.

·       Zoom out: Decreases the size of the cells, making more content visible on the screen.

·       Reset zoom: Restores the default zoom level.

7. Full screen

Meaning: Enter full-screen mode to view the spreadsheet without the browser toolbar or other distractions.

Usage: Click on "Full screen" to expand the spreadsheet to fill the entire screen.

Functions:

·       Hides browser toolbars and menus for a distraction-free view of the spreadsheet. 

F. Insert

1. Cells, Rows, and Columns

Meaning: Allows you to insert additional cells, rows, or columns into your spreadsheet.

Usage: Use this to expand your spreadsheet, add space for more data, or reorganize existing information.

Functions: Provides flexibility in adjusting the layout and structure of your spreadsheet to accommodate new data or make it more readable.

2. Sheet

Meaning: Adds a new sheet to your workbook.

Usage: Use this to create additional tabs within your spreadsheet file for organizing different sets of data or calculations.

Functions: Helps in organizing and separating different types of information within a single workbook.

3. Chart

Meaning: Inserts a chart into your spreadsheet to visualize data.

Usage: Use this to create graphical representations such as bar charts, line graphs, pie charts, and more.

Functions: Helps in analyzing and presenting data visually, allowing customization of chart types, styles, and data ranges.

4. Pivot Table

Meaning: Inserts a pivot table into your spreadsheet.

Usage: Use this to summarize and analyze large datasets by reorganizing and summarizing the data.

Functions: Allows for dynamic analysis, filtering, and grouping of data to gain insights and trends.

5. Image

Meaning: Allows you to insert an image into your spreadsheet.

Usage: Useful for adding logos, pictures, icons, or any other images to your spreadsheet.

Functions: Enhances the visual appeal of your spreadsheet, making it more engaging and informative.

6. Drawing

Meaning: Opens the Google Sheets drawing editor to create and insert drawings or shapes.

Usage: Use this for custom diagrams, illustrations, or annotations within your spreadsheet.

Functions: Allows drawing shapes, lines, arrows, adding text boxes, etc., for explanations, highlighting, or personalizing data.

7. Function

Meaning: Inserts a function into a cell.

Usage: Use this to add mathematical calculations, logical operations, text manipulations, and more.

Functions: Enables various operations on data like summing values, finding averages, counting occurrences, and more.

8. Link

Meaning: Inserts a hyperlink into a cell.

Usage: Useful for linking to external websites, other sheets within the same workbook, or specific cells.

Functions: Creates interactive spreadsheets where users can click links to navigate to other resources or sections.

9. Checkbox

Meaning: Inserts a checkbox into a cell.

Usage: Useful for interactive to-do lists, checklists, or marking items as completed.

Functions: Helps track tasks or items in your spreadsheet, with checked or unchecked boxes changing cell values.

10. Dropdown

Meaning: Adds a dropdown list into a cell.

Usage: Use this for selecting options from a predefined list.

Functions: Provides a quick and structured way to input data, reducing errors and standardizing inputs.

11. Emoji

Meaning: Inserts an emoji into a cell.

Usage: Use this for adding visual cues or expressions to your data.

Functions: Enhances the visual appeal and communication of your spreadsheet.

12. Smart Chips

Meaning: Adds smart chips to your spreadsheet.

Usage: Use this to quickly reference people, files, or meetings from Google Calendar.

Functions: Allows for easy linking and referencing of relevant information within your spreadsheet.

13. Comment

Meaning: Adds a comment to the selected cell.

Usage: Use this to provide additional information, explanations, or notes about specific data points.

Functions: Enables collaborators to communicate within the spreadsheet for discussions, feedback, or clarifications.

14. Note

Meaning: Adds a cell note to the selected cell.

Usage: Use this for adding small notes that appear when someone hovers over the cell.

Functions: Provides quick additional information or context about a specific cell without cluttering the main data area.

G. Format

1. Theme

Meaning: Applies a predefined theme to your entire spreadsheet.

Usage: Quickly changes the overall appearance, including fonts and colors, to maintain a consistent look.

Functions: Creates a visually appealing and professional appearance across the entire spreadsheet.

2. Number

Meaning: Formats cells as numbers, including options for currency, date, time, percentage, or plain numbers.

Usage: Sets how numbers appear in cells, such as decimal places, thousand separators, and currency symbols.

Functions: Displays data in a readable and meaningful way without changing the actual values.

3. Text

Meaning: Formats the appearance of text within cells.

Usage: Changes font, style, size, and color of the text.

Functions: Improves readability and visual appeal, highlights important information, or matches a specific style.

4. Alignment

Meaning: Adjusts the horizontal and vertical alignment of cell contents.

Usage: Aligns text or data within cells to left, center, right, top, middle, or bottom.

Functions: Enhances the layout and presentation of data for better readability and organization.

5. Text Wrapping

Meaning: Controls how text wraps within cells.

Usage: Chooses whether text automatically wraps to fit the cell width or overflows into adjacent cells.

Functions: Adjusts cell sizes to display long text entries without cutting off or distorting data. 

6. Rotate Text

Meaning: Changes the orientation of text within cells.

Usage: Rotates text to vertical, diagonal, or custom angles.

Functions: Provides creative and space-saving layouts for text within cells.

7. Font Size

Meaning: Adjusts the size of the text within cells.

Usage: Changes the font size to make text larger or smaller.

Functions: Customizes the appearance of text, emphasizing certain information or improving readability.

8. Merge Cells

Meaning: Combines multiple cells into one larger cell.

Usage: Creates headers, labels, or improves the appearance of spreadsheet layout.

Functions: Organizes data and enhances the visual appeal of the spreadsheet.

9. Conditional Formatting

Meaning: Applies formatting rules based on cell values.

Usage: Automatically formats cells based on specific conditions, such as color scales, data bars, or custom formulas.

Functions: Highlights trends, patterns, or outliers in the data, making it easier to identify important information.

10. Alternating Colors

Meaning: Applies colors to alternate rows or columns.

Usage: Adds visual distinction between rows or columns to improve readability.

Functions: Enhances the organization of data and makes it easier to follow across long lists or tables.

11. Clear Formatting

Meaning: Removes all formatting from selected cells.

Usage: Resets cells to their default appearance, removing any applied formatting.

Functions: Cleans up and standardizes the appearance of the spreadsheet, especially when copying and pasting data.

F. Data

1. Sort Sheet

Meaning: Sorts the entire sheet by columns.

Usage: Orders rows based on the values in one or more columns across the entire sheet.

Functions: Helps in organizing the entire dataset in ascending or descending order for analysis or presentation.

2. Sort Range

Meaning: Sorts the selected range of cells.

Usage: Orders rows based on the values in one or more columns within the specified range.

Functions: Allows for sorting specific sections of data within the sheet without affecting the entire dataset.

3. Create a Filter

Meaning: Applies a filter to the selected range.

Usage: Enables filtering options to quickly sort and display specific data within the selected range.

Functions: Allows for easy exploration and analysis of data subsets without affecting the original dataset.

4. Filter Views

Meaning: Creates filtered views of the data.

Usage: Allows you to filter data without affecting the original dataset.

Functions: Enables focused analysis of specific subsets of data, useful for detailed examination or comparisons.

5. Data Validation

Meaning: Sets restrictions on what data can be entered into cells.

Usage: Ensures data integrity by controlling the type and range of valid inputs.

Functions: Prevents errors and inconsistencies by guiding users to input valid data.

6. Data Cleanup

Meaning: Removes duplicates and empty rows from the selected range.

Usage: Cleans up data by eliminating redundant entries and empty cells.

Functions: Improves data quality and accuracy by removing unwanted or irrelevant information.

7. Trim whitespace

Meaning: Removes leading, trailing, and excess spaces from cells.

Usage: Cleans up extra spaces in text data that might cause issues with analysis or presentation.

Functions: Improves data consistency and readability by standardizing text formatting.

8. Split text to Columns

Meaning: Splits text in a cell into separate columns.

Usage: Divides text based on a specified delimiter, such as a comma or space.

Functions: Helps in parsing and organizing data that is stored in a single column.

9. Protected Sheets and Ranges

Meaning: Restricts editing access to specific cells, ranges, or entire sheets.

Usage: Protects sensitive data or formulas from accidental changes.

Functions: Provides security and control over who can edit or modify certain parts of the spreadsheet.

10. Pivot Table

Meaning: Creates a pivot table from the selected data range.

Usage: Summarizes and analyzes large datasets by reorganizing and summarizing the data.

Functions: Allows for dynamic analysis, filtering, and grouping of data to gain insights and trends.

11. Pivot Table Editor

Meaning: Opens the pivot table editor for customization.

Usage: Use this to modify the settings and appearance of the pivot table.

Functions: Provides options for configuring the pivot table layout, data fields, and calculations.

12. Pivot Chart

Meaning: Creates a pivot chart based on a pivot table.

Usage: Visualizes the data from a pivot table in various chart types.

Functions: Provides graphical representations of pivot table data for easier understanding and analysis. 

13. Linked Form Responses

Meaning: Imports data from a connected Google Form.

Usage: Retrieves responses from a connected Google Form into the spreadsheet.

Functions: Automates the process of importing form responses for further analysis and reporting.

14. Data connectors

Meaning: Connects the spreadsheet to external data sources.

Usage: Imports data from external sources such as BigQuery, Google Analytics, or Google Cloud SQL.

Functions: Allows for real-time data updates and integration with other systems for analysis and reporting. 

G. Tools

1. Create a New Form

Meaning: Creates a new Google Form.

Usage: Use this to create surveys, quizzes, feedback forms, or any other types of forms.

Functions: Allows for easy collection of data from respondents, with options for various question types, customization, and data analysis. 

2. Spelling

Meaning: Checks spelling in the selected range of cells.

Usage: Identifies and highlights misspelled words within the spreadsheet.

Functions: Helps in maintaining accurate and error-free text data within the spreadsheet.

3. Autocomplete

Meaning: Autocompletes text based on existing entries.

Usage: Provides suggestions for completing text based on previously entered values.

Functions: Speeds up data entry by reducing the need to type repetitive information.

4. Notification rules

Meaning: Sets up email notifications for changes in the spreadsheet.

Usage: Get notified via email when specific changes are made to the spreadsheet.

Functions: Keeps users informed about updates or changes to important data, improving collaboration and communication.

5. Accessibility

Meaning: Provides options for accessibility settings.

Usage: Customize settings to improve accessibility for users with disabilities.

Functions: Ensures inclusivity by providing options for screen readers, keyboard navigation, and other accessibility features.

H. Extensions

1. Add-ons

Meaning: These are third-party tools and plugins that extend the functionality of Google Sheets.

Usage: Add-ons can be installed to add new features, automate tasks, or integrate with other services.

Functions: Enhance Google Sheets with features not available in the standard application, tailored to specific needs.

2. Macros

Meaning: Macros are scripts that automate repetitive tasks in Google Sheets.

Usage: Record a series of actions, then replay them with a single click.

Functions: Saves time and effort by automating tasks such as formatting, data entry, and calculations.

3. Apps Script

Meaning: Google Apps Script is a JavaScript-based language that lets you add functionality to Google Sheets.

Usage: Write custom functions, create advanced macros, build web apps, and interact with other Google services.

Functions: Allows for extensive customization and automation of Google Sheets, from simple tasks to complex workflows.

4. AppSheet

Meaning: AppSheet is a no-code platform to create mobile apps directly from your Google Sheets data.

Usage: Build custom mobile apps without writing any code, using your spreadsheet as the backend.

Functions: Enables users to create mobile apps for various purposes, such as data collection, inventory management, or customer tracking.

Undo

Meaning: Reverts the last action performed in the spreadsheet.

Usage: Quickly undo changes made to cells, formatting, or data.

Shortcut: Ctrl + Z (Windows/Linux) or Cmd + Z (Mac)

Redo

Meaning: Reapplies an action that was undone.

Usage: Restores changes that were previously undone.

Shortcut: Ctrl + Y (Windows/Linux) or Cmd + Y (Mac)

Print

Meaning: Opens the print settings to print the spreadsheet.

Usage: Customize print settings such as paper size, orientation, and margins.

Shortcut: Ctrl + P (Windows/Linux) or Cmd + P (Mac)

Paint format Icon (Format Painter)

Meaning: Copies the format from one cell to another.

Usage: Use the format painter to quickly apply formatting styles to other cells.

Shortcut: Click on the "Paint format" icon, then click on the cell(s) you want to apply the format to.

Zoom Icon

Meaning: Adjusts the zoom level of the spreadsheet.

Usage: Zoom in or out to adjust the size of the cells and text.

Shortcut: Click on the "Zoom" icon, then choose the desired zoom level.

Number Formatting (Currency, Percentage, Decimal Place)

Meaning: Formats the selected cells as currency, percentage, or with a specific number of decimal places.

Usage: Click on the "Number format" dropdown menu and select the desired format.

Functions: Displays numbers in a specific format without changing the actual values.

Change Font Text Size and Color

Meaning: Adjusts the font style, size, and color of the selected text.

Usage: Use the font dropdown menu, size dropdown menu, and color picker to make changes.

Functions: Customizes the appearance of text within cells.

Strike-through

Meaning: Adds a line through the selected text.

Usage: Click on the "Strike-through" icon to apply the formatting.

Functions: Highlights deleted or completed items in a list or document.

Merge Cells

Meaning: Combines multiple cells into a single cell.

Usage: Select the cells you want to merge, then click on the "Merge cells" icon.

Functions: Creates headers, titles, or improves the appearance of the spreadsheet layout.

Horizontal Line

Meaning: Inserts a horizontal line across the selected cells.

Usage: Click on the "Horizontal line" icon to insert the line.

Functions: Separates sections or provides visual division within the spreadsheet.

Vertical Line

Meaning: Inserts a vertical line in the selected cells.

Usage: Click on the "Vertical line" icon to insert the line.

Functions: Divides columns or creates visual separation within the spreadsheet.

Text Wrapping Tool

Meaning: Adjusts how text wraps within cells.

Usage: Click on the "Text wrapping" icon and choose the desired option (wrap, overflow, clip).

Functions: Controls how text appears within cells, particularly useful for long text entries.

Hyperlink

Meaning: Inserts a hyperlink into the selected cell(s).

Usage: Click on the "Insert link" icon, then enter the URL or choose a cell reference.

Functions: Allows users to navigate to external websites, other sheets, or specific cells within the spreadsheet.

Comment

Meaning: Adds a comment to the selected cell(s).

Usage: Click on the "Insert comment" icon, then enter the comment text.

Functions: Provides a way to add notes, explanations, or feedback to cells without affecting the data.

Chart

Meaning: Inserts a chart based on the selected data.

Usage: Click on the "Chart" icon to open the chart editor and select the desired chart type.

Functions: Visualizes data with various chart types such as bar, line, pie, or scatter plots.

Filter

Meaning: Applies filter options to the selected range of cells.

Usage: Click on the "Filter" icon to enable filter options for sorting and viewing data.

Functions: Allows for easy exploration and analysis of specific subsets of data without altering the original dataset.

Function to a Cell

Meaning: Inserts a function into the selected cell.

Usage: Click on the desired function in the function menu, then enter the required arguments.

Functions: Performs calculations, data manipulation, or logical operations on the selected data.

A. Advanced Functions

1. SUM

Meaning: Adds together all the numbers in a range of cells.

Usage: =SUM(number1, [number2, ...]) or =SUM(range)

Example: =SUM(A1:A5) adds the values in cells A1 to A5.

2. AVERAGE

Meaning: Calculates the average of numbers in a range of cells.

Usage: =AVERAGE(number1, [number2, ...]) or =AVERAGE(range)

Example: =AVERAGE(A1:A5) calculates the average of the values in cells A1 to A5.

3. COUNT

Meaning: Counts the number of cells in a range that contain numbers.

Usage: =COUNT(value1, [value2, ...]) or =COUNT(range)

Example: =COUNT(A1:A5) counts the number of cells in cells A1 to A5 that contain numbers.

4. COUNTA

Meaning: Counts the number of cells in a range that are not empty.

Usage: =COUNTA(value1, [value2, ...]) or =COUNTA(range)

Example: =COUNTA(A1:A5) counts the number of non-empty cells in cells A1 to A5.

5. MAX

Meaning: Finds the maximum value in a range of cells.

Usage: =MAX(number1, [number2, ...]) or =MAX(range)

Example: =MAX(A1:A5) finds the largest value in cells A1 to A5.

6. MIN

Meaning: Finds the minimum value in a range of cells.

Usage: =MIN(number1, [number2, ...]) or =MIN(range)

Example: =MIN(A1:A5) finds the smallest value in cells A1 to A5.

7. IF

Meaning: Returns one value if a condition is true and another value if it's false.

Usage: =IF(logical_expression, value_if_true, value_if_false)

Example: =IF(A1>10, "Yes", "No") returns "Yes" if the value in A1 is greater than 10, otherwise returns "No".

8. VLOOKUP

Meaning: Searches for a value in the leftmost column of a table and returns a value in the same row from a specified column.

Usage: =VLOOKUP(search_key, range, index, [is_sorted])

Example: =VLOOKUP(A1, B1:C10, 2, FALSE) searches for the value in A1 in the range B1:C10 and returns the value in the second column of the matching row.

9. HLOOKUP

Meaning: Searches for a value in the top row of a table and returns a value in the same column from a specified row.

Usage: =HLOOKUP(search_key, range, index, [is_sorted])

Example: =HLOOKUP(A1, B1:F5, 3, FALSE) searches for the value in A1 in the range B1:F5 and returns the value in the third row of the matching column.

10. XLOOKUP

Meaning: XLOOKUP is a versatile lookup and reference function in Google Sheets. It searches a range or an array and returns an item corresponding to the first match it finds. It is an advanced version of the traditional VLOOKUP and HLOOKUP functions.

Usage:

=XLOOKUP(search_key, lookup_array, return_array, [if_not_found], [search_mode], [search_result])

search_key: The value to search for in the lookup_array.

lookup_array: The range or array to search for the search_key.

return_array: The range or array from which to return a value.

if_not_found (optional): The value to return if no match is found. If omitted, it defaults to #N/A.

search_mode (optional): Specifies the match type:

0 or FALSE (default): Exact match.

1 or TRUE: Approximate match (default for numbers).

-1: Exact match or next smaller item (default for strings).

search_result (optional): Controls whether to return the result if there are multiple matches.

1 (default): Returns the first match.

2: Returns an array of all matches.

Example:

=XLOOKUP("John", A1:A10, B1:B10)

This formula will search for "John" in the range A1:A10, and return his score from the corresponding cell in the range B1:B10.

=XLOOKUP("Apple", A1:A10, B1:B10, "Not Found")

This formula will search for "Apple" in the range A1:A10, and return "Not Found" if no match is found.

11. INDEX/MATCH (combined)

Meaning: Finds a value in a table based on a given row and column intersection using the INDEX and MATCH functions together.

Usage: =INDEX(range, MATCH(lookup_value, lookup_range, 0))

Example: =INDEX(B1:F10, MATCH(A1, A1:A10, 0), 3) finds the value at the intersection of the row where A1 is found and the third column in the range B1:F10.

12. CONCATENATE

Meaning: Combines multiple strings or values into one.

Usage: =CONCATENATE(string1, [string2, ...]) or =CONCATENATE(range)

Example: =CONCATENATE(A1, " ", B1) combines the values in cells A1 and B1 with a space in between.

13. TEXT

Meaning: Converts a value to text in a specific number format.

Usage: =TEXT(value, format_text)

Example: =TEXT(TODAY(), "yyyy-mm-dd") formats today's date as "yyyy-mm-dd".

14. TRIM

Meaning: Removes extra spaces from text, leaving only single spaces between words.

Usage: =TRIM(text)

Example: =TRIM(" Hello World ") returns "Hello World" by removing extra spaces.

15. SUMIF/SUMIFS

Meaning: Adds up values in a range that meet specific criteria.

Usage:

=SUMIF(range, criteria, [sum_range]) for one condition.

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2, ...]) for multiple conditions.

Example:

=SUMIF(A1:A5, ">10") adds values in A1:A5 that are greater than 10.

=SUMIFS(C1:C10, A1:A10, "Banana", B1:B10, "January") adds values in C1:C10 where A1:A10 is "Banana" and B1:B10 is "January".

16. AVERAGEIF/AVERAGEIFS

Meaning: Calculates the average of values in a range that meet specific criteria.

Usage:

=AVERAGEIF(range, criteria, [average_range]) for one condition.

=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2, ...]) for multiple conditions.

Example:

=AVERAGEIF(A1:A5, ">10") calculates the average of values in A1:A5 that are greater than 10.

=AVERAGEIFS(C1:C10, A1:A10, "Banana", B1:B10, "January") calculates the average of values in C1:C10 where A1:A10 is "Banana" and B1:B10 is "January".

17. FILTER

Meaning: Filters a range of data based on specific criteria.

Usage: =FILTER(range, condition1, [condition2, ...])

Example: `=FILTER(A1:A10, B1:B10

18. SORT

Meaning: Sorts the contents of a range or an array based on the values in one or more columns.

Usage: =SORT(range, [sort_column], [is_ascending], [sort_column2, is_ascending2, ...])

Example: =SORT(A2:C10, 2, FALSE)

·        This formula sorts the range A2:C10 based on the values in the second column (B), in descending order.

19. UNIQUE

Meaning: Creates a list of unique values from a range of cells, removing any duplicates.

Usage: =UNIQUE(range, [by_col], [occurs_once])

Example: =UNIQUE(A2:A10)

·        This formula will return a list of unique names from the range A2:A10.

Example with Multiple Columns: =UNIQUE(A2:C10)

·        This formula will return a list of unique combinations of values from the range A2:C10.

20. ABS

Meaning: ABS stands for "absolute value." This function returns the absolute value of a number, which is its distance from zero without regard to its sign. It converts negative numbers to positive and leaves positive numbers unchanged.

Usage:

=ABS(number)

number: The numeric value for which you want to find the absolute value.

Example:

=ABS(-5)

This formula will return 5 because the absolute value of -5 is 5.

=ABS(10)

This formula will return 10 because the absolute value of 10 is 10.

B. Conditional Formatting

Conditional formatting is a technique used to format cells, rows, or columns in a dataset based on specific conditions or criteria. Instead of manually changing the formatting of each cell, you can set rules that automatically apply formatting styles such as colors, icons, or data bars to cells that meet the criteria you specify.

Highlighting Data

One of the most common uses of conditional formatting is to highlight important data points. For example, you can set a rule to highlight all sales figures that are above a certain threshold in green and all figures below that threshold in red.

Identifying Trends

Conditional formatting can be used to identify trends or patterns in data. For instance, you can apply a color scale where lighter shades indicate lower values and darker shades indicate higher values. This makes it easy to spot trends at a glance.

Flagging Errors

Another useful application is error checking. You can create rules to highlight cells that contain errors or are outside of an expected range, helping you identify and correct issues in your data.

Comparing Data

You can compare data sets by applying conditional formatting to highlight the differences between them. For example, you might highlight cells in one dataset that are higher or lower than their corresponding cells in another dataset.

Creating Heatmaps

Heatmaps are a popular visualization tool that uses conditional formatting. By applying color scales to a grid of data, you can quickly visualize patterns and variations within the data.

How to Use Conditional Formatting

The exact steps to apply conditional formatting depend on the software you are using, but the general process is similar across most platforms:

Select Data Range

Choose the range of cells you want to apply conditional formatting to.

Define Rule

Specify the condition or criteria that the data must meet for the formatting to be applied. This could be based on values (e.g., greater than, less than), text (e.g., contains, does not contain), dates (e.g., before, after), or other logical conditions.

Choose Formatting Style

Select the formatting style you want to apply when the condition is met. This could be changing the font color, fill color, adding icons, applying data bars, etc.

Apply Rule

Once you have defined the rule and chosen the formatting style, apply the rule. The software will then automatically format the selected cells according to the specified conditions.

Manage Rules

You can usually manage and edit existing rules, as well as add new ones, to customize the conditional formatting to suit your needs.

Conditional formatting is a powerful tool for data visualization and analysis, making it easier to interpret and draw insights from large datasets. Its flexibility allows you to tailor the visualization to your specific requirements, highlighting the most important information and revealing hidden patterns within the data. 

A. Sorting and Filtering Data

Google Sheets offers robust tools for sorting and filtering data, facilitating efficient organization and analysis. In this guide, we'll explore how to leverage these features to streamline workflows and extract valuable insights.

Sort data in alphabetical or numerical order.

1.     On your computer, open a spreadsheet in Google Sheets.

2.     Highlight the group of cells you'd like to sort.

3.     If your sheet includes a header row, freeze the first row.

4.     Click Data > Sort range > Advanced range sorting options.

5.     If your columns have titles, click Data has header row.

6.     Select the column you'd like to be sorted first and choose a sorting order.

·   To add another sorting rule, click Add another sort column.

7.     Click Sort.

Sort an entire sheet

1. On your computer, open a spreadsheet in Google Sheets.

2. At the top, right-click the letter of the column you want to sort by.

3. Click Sort sheet A to Z or Sort sheet Z to A.

Sort by color

1. On your computer, open a spreadsheet in Google Sheets.

2. Select a range of cells.

3. Click Data > Create a filter.

4. To see filter options, go to the top of the range and click Filter .

a. Sort by color: Choose which text or fill color to filter or sort by. Cells with the color you choose to sort by will move to the top of the range. You can sort by conditional formatting colors, but not alternating colors.

5. To turn the filter off, click Data  Remove filter.

B. Pivot tables for data summarization

     A PivotTable is an interactive way to quickly summarize large amounts of data. You can use a PivotTable to analyze numerical data in detail and answer unanticipated questions about your data. A PivotTable is especially designed for:

·   Querying large amounts of data in many user-friendly ways.

·   Subtotaling and aggregating numeric data, summarizing data by categories and subcategories, and creating custom calculations and formulas.

·   Expanding and collapsing levels of data to focus your results and drilling down to details from the summary data for areas of interest to you.

·   Moving rows to columns or columns to rows (or "pivoting") to see different summaries of the source data.

·   Filtering, sorting, grouping, and conditionally formatting the most useful and interesting subset of data enabling you to focus on just the information you want.

·   Presenting concise, attractive, and annotated online or printed reports.

Steps on how to use Pivot Table to summarize data

1. Select Excel Table and Insert PivotTable

·       Within the worksheet, identify the Excel table containing the desired data.

·       Click on any cell within the Excel table to select it.

·       Go to the "Insert" tab in the ribbon at the top of the window.

2. Initiate Pivot Table Creation

·   On the "Insert" tab, locate the "Pivot Table" button in the "Tables" group.

·       Click on the "Pivot Table" button.

3. Specify PivotTable Options

·   A dialog box titled "PivotTable from table or range" will appear.

·   Confirm that the correct table is identified in the dialog box.

·   Choose whether to place the Pivot Table on a new worksheet or an existing one.

·   To create a new worksheet, ensure the "New Worksheet" option is selected.

·   To use an existing worksheet, choose the "Existing Worksheet" option and select a cell where the PivotTable will be placed.

4. Customize PivotTable Fields

·       After selecting the desired placement option, click "OK."

·       Excel will create a new worksheet and insert a blank PivotTable.

·       The "PivotTable Fields" task pane will appear on the right side of the window.

·       To customize the PivotTable:

o   Drag the desired fields from the "Fields" list in the task pane to different areas of the PivotTable layout (e.g., rows, columns, values).

o   Arrange the fields as needed to organize and summarize the data effectively.

5. Finalize PivotTable Setup

·       Once the fields are added and arranged, review the PivotTable layout to ensure it meets your analysis needs.

·       Make any necessary adjustments, such as formatting the values or modifying field settings.

·       Save your work.

C. Charts and graphs creation

Charts help you visualize your data in a way that creates maximum impact on your audience. Learn to create a chart and add a trendline. You can start your document from a recommended chart or choose one from our collection of pre-built chart templates.

Step-by-Step Guide to Inserting a Chart or Graph in Google Sheets:

1. Prepare Your Data

·   Enter the data you want to visualize into your Google Sheet.

·   Label your data with column and/or row headers for clarity.

2. Select Data for Chart

·   From your spreadsheet, select all the cells containing the data you want to include in the chart.

·   This selection can include column headers if desired.

3. Insert Chart

·   Once the data is selected, click on the "Insert Chart" icon located in the toolbar.

·   Google Sheets will automatically generate a chart based on its recommended settings.

·   The Chart Editor menu will appear on the right-hand panel for further customization.

4. Customize Chart

·   In the Chart Editor, you can adjust the range of cells used for the chart if needed.

·   Choose the type of chart or graph you prefer from the recommended options or scroll down to see all available types.

·   Customize chart elements such as title, axis labels, legend position, and formatting using the editing tools.

·   Resize and reposition the chart as desired by clicking and dragging its corners or selecting and moving it to a different location on the sheet.

5. Finalize Chart

·   Click somewhere else on the sheet to finalize the chart and make the editing tools disappear.

·   If further adjustments are needed, click on the chart to reveal the three dots in the upper right corner, offering quick actions such as editing, deleting, downloading, copying, moving, or adding alternate text to the chart.

6. Additional Editing Options

·   Double-click on specific chart elements to make quick edits, such as changing the color or text.

·   Utilize the customization options available to create a visually appealing chart or graph that effectively displays your data.

D. Tips for effective data presentation

In today's data-driven landscape, effective communication is paramount across all roles and industries. Whether you're a seasoned professional or just starting out, there's always room for improvement.

Led by Bill Shander, an expert with nearly 30 years of experience in data storytelling and visualization, this course aims to equip you with the strategies and tools needed to enhance your data presentations. From data literacy to visualization techniques, we'll cover essential topics to help you inform and inspire your audience.

Throughout the course, we'll focus on practical tips and best practices, ensuring a clear and concise learning experience. Let's dive in and elevate your data communication skills together.

1.     Plan

· Develop your data literacy

Effective data presentation begins with understanding your data, known as data literacy. Whether you're sharing basic findings with a general audience or complex insights with experts, your level of data literacy should match the importance of your work. From simple calculations for ice cream preferences to precise analysis for medical decisions, grasping the basics sets the stage for compelling storytelling.

· Find the “so what” in your data

Effective data presentation requires asking "So what?" This means critically assessing your data's significance and relevance to your goals. It's about asking the right questions to uncover meaningful insights that align with your objectives and ensuring your presentation stays focused and impactful.

· Make clear predictions and recommendations

When analyzing data, it's not just about understanding past trends; it's about guiding future decisions. There are three main types of data analysis:

a.      Descriptive analytics: Understanding past events and patterns.

b.     Predictive analytics: Making predictions about the future based on past data.

c.     Prescriptive analytics: Providing recommendations to improve future outcomes based on predictive analysis.

· Know your audience          

Consider language, age, interests, and knowledge. Identify primary, secondary, and tertiary audiences. Tailor presentation to drive specific actions. Balance inclusivity with relevance. Group audiences as needed. Clarify expected actions for each audience. Tailor content accordingly.

2. Put it in writing

·       Make your presentation flow logically

To ensure your presentation flows logically, create a condensed version of your story, summarizing key ideas with complete sentences and connector words. Practice saying it out loud to identify gaps, awkward transitions, or missing context. Adjust as needed until the narrative feels smooth and logical. This step is crucial for transforming your data story into an engaging and impactful presentation.

·       Make every presentation actionable

To ensure your presentation is actionable, go beyond simply sharing data and offer specific forecasts and recommendations. Provide clear suggestions for actions based on the data presented. Include prioritized recommendations, identify key stakeholders to engage with, and outline potential next steps, such as scheduling meetings and allocating resources. By bridging the gap between data analysis and business needs, you add significant value to your organization. Ultimately, aim to make the data fade into the background as your audience focuses on understanding insights and making informed decisions.

3. Make it visual

Selecting the right chart for your data is crucial for effective communication. Consider what you want your audience to understand or do with the data, such as trends over time, distribution, comparisons, or part-to-whole relationships. Use tools like Visual Vocabulary to match chart types to specific tasks. Avoid making poor choices, like using a pie chart for value comparisons. Test your visuals with others to ensure clarity and effectiveness. Ultimately, choose charts with intention, keeping your goals and desired actions in mind for optimal communication.

4. Pre-presentation

     Cognitive biases can affect data analysis and communication. Biases like the curse of knowledge and false consensus effect can hinder understanding and interpretation. To address these biases, test your presentation with colleagues or friends with different perspectives. Gather feedback to ensure clarity and adjust as needed to improve communication and avoid pitfalls in presenting data to leadership. 

A. Sharing and Permissions Management

Mastering the art of sharing and collaborating in Google Sheets empowers teams to seamlessly collaborate, ensuring effective communication, streamlined workflows, and optimal productivity. Here are the ways to have good sharing and permissions management:

1. Initiating Collaboration

·   As the owner of a Google Sheets spreadsheet, you have the authority to manage access and permissions.

·   Begin by clicking on the "Share" button to initiate the sharing process.

· Enter the email addresses or names of collaborators you wish to add, specifying their respective roles—editors, commenters, or viewers.

2. Assigning Roles and Permissions

·   Collaborators' roles determine their level of access to the spreadsheet.

·   Editors possess comprehensive access rights, including content modification, downloading, printing, and copying, but cannot transfer ownership or delete the file.

·   Commenters can view and add comments but cannot alter the spreadsheet.

·   Viewers are restricted to viewing privileges only.

·   Permissions can be adjusted at any time, allowing for fine-tuning of collaborative dynamics.

3. Enhancing Security and Control

·   Utilize advanced settings to limit editors' ability to share the spreadsheet and control download, print, and copy options for viewers and commenters.

·   Set expiration dates for collaborators to revoke access after a specified period, facilitating time-sensitive collaborations.

4. Sharing via Links

·   Google Sheets provides the option to share spreadsheets via unique links.

·   Link access can be restricted to collaborators or widened to include all users within the organization or even the general public.

·   Exercise caution when sharing via links, as recipients can redistribute them, potentially compromising security.

5. Ownership Transfer

·   Transfer ownership by adding the new owner as a collaborator with editor privileges, then selecting them as the new owner and initiating the transfer.

·   After ownership transfer, you retain editor status, but the new owner holds the authority to remove you as a collaborator.

6. Version History and Tracking Changes

·   Google Sheets maintains a comprehensive version history, documenting all edits made by collaborators.

·   Access the version history through the "File" menu, enabling you to track changes, identify contributors, and restore previous versions if necessary.

·   Named versions offer a convenient way to organize and reference significant milestones within the spreadsheet's development.

7. Downloading and Sharing Offline

·   Spreadsheets can be downloaded in various formats for offline editing or sharing with individuals outside the Google Sheets ecosystem.

·   Once downloaded, files are no longer subject to the permissions set within Google Sheets, allowing for broader distribution and accessibility.

B. Collaborative editing features

Google Sheets offers a plethora of collaboration options, empowering teams, clients, and partners to seamlessly work together regardless of their location. Let's explore some of the collaborative features available in Google Sheets:

1. Commenting and Discussion

·       Editors and commenters can engage in discussions directly within the spreadsheet using comments.

·       To add a comment, simply select a cell and choose "Comment" from the insert menu or click the insert comment toolbar icon.

·       Cells with comments are easily identified by a small amber triangle in the top right corner.

·       Reply to comments by hovering over the cell and typing a response in the comment card.

·       Mention specific individuals in comments using plus or at symbols before their name, allowing for targeted communication.

·       Grant access to individuals mentioned in comments by adjusting sharing permissions as prompted.

2. Action Items

·       Assign action items by mentioning individuals when creating a new comment.

·       Check the assigned person's checkbox to notify them and make them responsible for completing the action item.

·       Mark comments as resolved once action items are completed, streamlining workflow and organization.

3. Comment Management

·       Easily manage comments by resolving, editing, or deleting them from the comment card's more options menu.

·       Obtain direct links to specific comments for easy reference or sharing with collaborators.

·       Access all comments, including their history, by selecting "Open Comment History" next to the share button.

·       From the comment history, view, edit, delete, resolve, link to, and reopen comments, ensuring comprehensive comment management.

4. Notification Management

·       Customize notification preferences for comments within the spreadsheet.

·       Choose to receive notifications for all comments, only those directed at you, or opt for no notifications, tailoring the experience to individual preferences.

C. Version history and commenting

Google Sheets offers robust features for version history tracking and commenting, facilitating seamless collaboration and revision management. Let's delve into these essential functionalities:

1. Version History

·   Google Sheets automatically tracks all edits made to a spreadsheet, maintaining a comprehensive version history.

·   Access version history by selecting "File" and hovering over "Version history," then choosing "See version history."

·   The browser presents edits chronologically, allowing users to track changes from newest to oldest.

·   Each edit is represented by specific colors, enabling users to identify contributors and their respective modifications.

·   Selecting a specific edit reveals the changes made and the responsible editor, providing transparency and accountability.

·   Users can make copies of the spreadsheet at various points in time or create named versions to organize significant milestones.

2. Commenting

·   Collaborators can engage in discussions and provide feedback directly within the spreadsheet using comments.

·   To add a comment, select a cell and choose "Comment" from the insert menu or toolbar icon.

·   Comments facilitate communication and collaboration by allowing users to exchange thoughts, suggestions, and clarifications.

·   Mention specific individuals in comments to notify them and involve them in the discussion, enhancing targeted communication.

·   Assign action items by mentioning individuals and checking the assigned person's checkbox, streamlining task delegation and tracking.

·   Resolve comments once action items are completed, maintaining clarity and organization within the spreadsheet.