Google Sheet
In this training program we start right from the fundamentals of Google Sheets (much of which translates to MS Excel!) and then gradually move on to the more valuable advanced formulas like VLOOKUP, INDEX(MATCH()MATCH()), and IMPORTRANGE.
Highlights of the course:
- Learn the basics like how to create a Google Account and a Google Spreadsheet
- Arithmetic Functions like SUM, COUNT, and AVERAGE
- Shortcuts like filling formulas across THOUSANDS of cells
- Advanced charts & Beautiful Visualizations
- PIVOT TABLES
- Advanced functions like INDEX MATCH MATCH and IMPORT RANGE
- QUERY
This Advanced Excel Course will empower the participants to be able to do the following:
- Performing complex calculations more efficiently, using various Excel functions.
- Organizing and analyzing large volumes of data.
- Creating MIS reports.
- Designing and using templates.
- Consolidating and managing data from multiple workbooks.
Basic For Google Sheet
- Cells
- Rows
- Columns
- Useful Menu & Toolbar Options
- Language & Spreadsheet Settings
-
Version Recovery & Edit History
Data Entry & Basic Formula
- Data Entry, Data Types & Autofill in Google Sheets
- Common Formulas in Google Sheets
-
Important Functions in Google Sheets (Sum, Count, Counta, Count Unique)
- Relative Vs. Absolute Referencing
Best Practice & Useful Features
- Useful Features for Report Design
- Conditional Formatting (Steer Attention to What Matters)
- Data Validation & Drop-down Lists
- Working with Excel & CSV Files (& Importing Data)
- Getting Data from Other Google Sheets (ImportRange)
- Best Practice in Designing Functional Spreadsheets
Shorting, Filtering and Cleaning Data
- Sort Feature & What You Shouldn’t Do
- Filter and Filter View in Sheets
- Calculations on Filtered Results – Subtotal
- Split Text, Remove Duplicates and Trim Spaces
- Find & Replace Values
Essential Sheets Functions to Analyze & Summarize
- How to Properly Read & Understand Functions
- Unique & Sort Functions
- SUM(IFS) by Criteria (also COUNT, AVERAGE, MAX, MIN)
- Logical & Information Functions (TRUE & FALSE)
- IF Function (IF, Then)
- Nested IF & IFS Functions
- Error Types & How to Handle Errors
- Named Ranges
Lookup & Filter Functions
- Overview of Helpful Lookup Functions
- VLOOKUP and HLOOKUP
- Approximate Match Lookup
- FILTER Function to Return Multiple Match Results
- Lookup Pictures & Images
- SortN for Ranking
Text, Date & Time Functions
- Useful & Easy Text Functions
- Search & Substitute Text
- Split & Join Values to / from Multiple Columns
- Essential Date Functions
- Time Calculations (Calculating Hours Worked)
Helpful Google Sheet Function
- Detect Language and Translate Your Spreadsheets
- Image Function
- Import Tables From the Web
- Import Any Data from the Web
- Stock & Currency Data with GOOGLEFINANCE
Collaborate, Publish & Protect
- Sharing Files & Collaboration Tools
- Publishing Sheet or Chart (& Embedding in a Web Page)
- Protection: Sheet & Cell Level
- Prepare for Print / PDF (& Page Breaks)
- Use Google Forms to Collect Data
Charts
- Chart Basics (Column & Line Charts)
- Aggregating Data in Charts(Bar & Pie Charts)
- Combo (Combination) Charts
- Scrolling Tables & Scorecards (Visualization Tools for Dashboards)
- Gauge Chart (Visualization Tools for Dashboards)
- Add Slicers to Charts
- Sparkline’s to Impress
- Sparkline’s for Stock & Currency Trends
Explore Your Data with Pivot Tables in Google Sheets
- Explore Feature in Google Sheets
- Pivot Table Basics in Google Sheets
- Filters & Slicers for Dynamic Pivot Tables
-
Grouping Items & Dates (by month, year)
-
Custom Formulas in Pivot Tables
- Master Pivot Tables in Google Sheets