Google Sheets - Advanced

Import worksheets from an Excel workbook into Google Sheets by selecting insert new sheets, choosing import data, and optionally keeping or skipping the theme, then organize the newly imported data.

Link values from item list, fruit list, beverage list, and tea list into a summary sheet using formulas, and watch totals update automatically when source values change.

Learn how to link data from another Google Sheet using the importrange function, ensure files are Google Sheet format, and specify the URL, sheet name, and cell reference.

Protect sheets and ranges in Google Sheets by using the data tab to add sheets or ranges and set permissions, such as only me or specific individuals, with edit warnings.

Learn to use Google Sheets version history to view, name, restore, and copy prior versions, whether auto-generated or manually saved, using File > Version history.

Enable and review accessibility features in Google Sheets to support screen magnifier, screen reader, Braille support, and collaborator announcements for users with assistive technologies.

Learn named ranges in Google Sheets, assign names to cell ranges, and use them in formulas for readability and navigation, with syntax rules like starting with a letter or underscore.

Create named ranges in Google Sheets for the 2017, 2018, and 2019 sales data. Use the data menu to name, navigate, and edit these ranges for formulas.

Use named ranges in Google Sheets formulas to sum and average across years 2017, 2018, and 2019, format results as currency, and improve formula clarity.

Explore how logical functions compare values with operators equal to, greater than, and not equal to, returning true or false. Build and apply if statements to define conditions and outcomes.

Demonstrates building an if statement in Google Sheets to calculate commissions: use a logical test for greater than or equal to 2 million to apply 3%, else 2%, with autofill.

Learn to use and and or with if in Google Sheets to test conditions. Nest functions to decide city bonuses based on sales and CEU thresholds, producing yes or no.

Learn how to build nested if statements and use the ifs function in google sheets to implement tiered commissions with descending thresholds, using classic and ifs approaches.

Explore how sumif, countifs, averageif, maxifs, and minifs compute totals, counts, and averages by line criteria in Google Sheets, with named ranges and optional pivot tables.

Learn to use the iferror function in Google Sheets to clean up errors from division by zero or missing lookups by wrapping formulas and displaying blank or zero values.

Explore how Google Sheets lookup functions use a unique identifier to retrieve information from a larger data set, returning related data such as commission rates via horizontal and vertical lookups.

Explore how vlookup performs vertical lookups from left to right, using an employee data range and exact-match criteria to return fields like first name, last name, and department.

Explore hlookup for horizontal lookups, where headings run in the first row and a value is returned from a row on a named range Health Data using an exact match.

Use index and match to retrieve the item name by product ID when vlookup can't look left, with match providing the row position and index returning the intersecting value.

Use vlookup with iferror and isna to compare product ids against the sales list, returning yes for sold and no for not sold.

Explore three text-joining functions in Google Sheets: concat, concatenate, and textjoin. Learn how each differs, including delimiters and ignoring blanks, to combine names and data efficiently.

Learn to split a column into multiple columns in Google Sheets using split text to columns, with a dash delimiter to separate category code, manufacturing part number, and product code.

Learn to separate text with left, right, and mid in google sheets, extracting department, division, and extension from an employee code, and generate emails via concatenate.

Learn to standardize text in Google Sheets using upper, lower, and proper to fix inconsistent casing in names and emails, and clean imported data.

Discover how the Len function returns the number of characters in a text string and supports data validation and length checks. See examples counting characters in employee codes and emails.

Clean data in Google Sheets by removing duplicates and trimming whitespace. Highlight the range, remove duplicates by employee name and shirt size, and trim whitespace to ensure 17 unique values.

Master date and time functions in Google Sheets, including today and now, and calculate days between dates, days remaining in the year, and days since the last review.

Learn to calculate tenure and remaining workdays in Google Sheets using year frac and net work days, accounting for holidays and absolute references, and compare calendar days to work days.

Create named functions in Google Sheets to reuse custom calculations, such as power ranking, by defining a function with arguments and placeholders, then call it as a named function.

Import a named function from another Google Sheets using the data menu, named functions, and import function, then choose the source sheet and import one or multiple functions.

Apply data validation to cells with drop-downs for division, department, and health plan. Enforce date of hire on or after today and extension 56000-56999.

Learn to apply conditional formatting in google sheets, using a single color, color scales, and custom formulas to highlight low stock, out-of-stock items, and total thresholds.

Master grouping and ungrouping in Google Sheets to collapse or expand columns and rows, keeping related data together and making totals easier to read.

Learn how pivot tables in Google Sheets query, organize, and summarize repetitive data to reveal metrics like average call time by agent and product line. Cover essential data preparation rules.

Create a pivot table from a beverage sales range, drag fields into rows, columns, and values, and use filters to explore totals and groupings.

Explore pivot table techniques in Google Sheets by filtering data by category and date, viewing per-customer totals, and expanding details to reveal itemized records.

Create a chart from a pivot table, adjust the data range to exclude the grand total, customize chart type and appearance, and observe how pivot table changes reflect in the chart.

Add a slicer to your pivot table in Google Sheets as a visual filter, created via data > add a slicer, then filter by customer.

Discover how macros automate repetitive tasks in Google Sheets by recording actions and replaying them, then learn where to find and use the recorder.

Record and save a macro in Google Sheets to apply uniform formatting using absolute references, then replay it on weeks 23, 24, and 25.

Explore managing Google Sheets macros by editing scripts, renaming, and assigning keyboard shortcuts. Learn to edit Apps Script, update ranges like A1 to J1, and apply font and background changes.