Google Sheets Data Analysis and Visualization (Live Online)

Overview

This intensive one-day Google Sheets Data Analysis course reveals the advanced capabilities of Google Sheets for complex data operations and analysis. While Google Sheets is accessible for basic tasks, it offers substantial power for advanced data manipulation and analysis. This course covers advanced features including conditional logic and conditional functions, text manipulation and processing, database lookup operations, date field handling, query construction, and much more. Discover the sophisticated features and functions that enable professional-level data analysis within Google Sheets.

Intended Audience:

This course is designed for Google Sheets power users seeking to leverage advanced functionality for complex data analysis and reporting tasks.

Prerequisites:

Experience creating and working with spreadsheets in Google Sheets is recommended. Participants who lack this background should consider completing ONLC's Introduction to Google Sheets course first.

Course Content:

Data Organization and Manipulation

Learn techniques for organizing and preparing data for analysis. Topics include sorting data by multiple criteria, applying filters and filter views, grouping related data, working with hyperlinks and embedded links, and implementing data validation rules to ensure accuracy.

Sorting Data and Multi-Level Sorting
Filtering Data and Using Filter Views
Grouping Related Data
Working with Links and Hyperlinks
Data Validation and Constraint Rules

Importing and Preparing Data

Bring data into Google Sheets from various sources and clean it for analysis. Learn to open Microsoft Excel workbooks, import tables and lists from web pages, import data from various sources, remove duplicate records, clean up whitespace and formatting, use automatic cleanup suggestions, calculate column statistics, split text into separate columns, and randomize data ranges for sampling.

Opening Microsoft Excel Workbooks
Importing Tables and Lists from Web Pages
Importing Data from External Sources
Removing Duplicate Records
Trimming Whitespace and Formatting
Using Cleanup Suggestions
Column Statistics and Analysis
Splitting Text into Columns
Randomizing Data Ranges

Pivot Tables and Advanced Analysis

Create powerful pivot tables for summarizing and analyzing data patterns. Learn pivot table fundamentals, use suggested pivot tables for quick analysis, create custom pivot tables, leverage the Explore feature for automated insights, implement VLOOKUP formulas to add data columns, customize pivot tables with the Pivot Table Editor, view detailed cell information, create calculated fields for derived metrics, use slicers for interactive filtering, and generate charts from pivot data.

Pivot Table Fundamentals and Benefits
Suggested Pivot Tables
Creating Custom Pivot Tables
Pivot Tables with the Explore Feature
VLOOKUP for Data Integration
Pivot Table Editor and Customization
Detailed Cell Information and Drill-Down
Calculated Fields and Derived Metrics
Slicers for Interactive Filtering
Creating Charts from Pivot Data

Formatting and Presentation

Apply professional formatting to enhance data presentation and analysis. Topics include custom number formats, conditional formatting rules, custom formulas for conditional formatting, alternating row colors, theme selection, and using Explore for automatic insights.

Custom Number Formats
Conditional Formatting Rules
Custom Formulas for Conditional Formatting
Alternating Row Colors
Theme Selection and Customization
Explore Feature for Data Insights

Collaboration and Distribution

Share your work effectively with others and protect sensitive information. Learn to share spreadsheets with specific people, create shareable links, publish spreadsheets to the web, protect sheets and specific ranges from modification, use comments for collaboration, download spreadsheets in alternative formats, and email files to recipients.

Sharing Spreadsheets and Access Control
Shareable Links
Publishing to the Web
Protecting Sheets and Ranges
Collaborative Comments
Downloading Alternative File Formats
Emailing Spreadsheet Files

Integration with Google Workspace

Connect Google Sheets with other Google applications for integrated workflows. Learn to embed Sheets ranges in Google Docs or Slides, embed charts in documents and presentations, work with linked objects that update automatically, create and modify charts within documents, embed Google Docs and Slides content in Sheets, create forms for data collection, and work with form responses.

Embedding Sheets Ranges in Documents
Embedding Charts in Presentations
Inserting Sheets Charts in Documents
Linked Objects and Auto-Update
Creating and Modifying Charts
Embedding Document and Presentation Content
Creating Forms for Data Collection
Working with Form Response Data

Advanced Charting Techniques

Create diverse chart types for effective data visualization and storytelling. Learn to work with specialized chart types and create Geo Charts for geographic data, Waterfall Charts for cumulative change, Histogram Charts for distribution analysis, Radar Charts for multi-dimensional comparison, Gauge Charts for performance indicators, Scorecard Charts for key metrics, Candlestick Charts for financial data, Organizational Charts for hierarchies, Tree Map Charts for hierarchical proportions, Timeline Charts for temporal data, Table Charts for formatted tables, Combo Charts combining multiple chart types, and Sparkline Charts for mini visualizations.