MASTERING GOOGLE SHEETS FOR ANALYSIS,REPORTING AND MODELING
Live-Online Training: N250,000.00
Classroom Training: N 350,000.00
3 - 4 participants: 5% discount
5 or more participants: 10% discount
(Available also for Customised Training by Duration, Venue & Fee)
(PARTICIPANTS SHOULD BRING THEIR LAPTOP)
Course Contents
DAY ONE
Google Sheets Basics, Formulas & Best Practices
- Cells, Rows & Columns
- Productivity Tips & File Sharing
- Useful Menu & Toolbar Options
- Language & Spreadsheet Settings
- Version Recovery & Edit History
- Data Entry, Data Types & Autofill in Google Sheets
- Common Formulas in Google Sheets
- Important Functions in Google Sheets (Sum, Count, Counta, CountUnique)
- Relative Vs. Absolute Referencing
- Useful Features for Report Design
- Useful Shortcuts
- Conditional Formatting
- 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.
DAY TWO
Data Prep and Analysis Functions
- Sort Feature & What You Shouldn't Do
- Filter & Filter View in Sheets
- Calculations on Filtered Results – SubTotal
- Challenge: Delete Empty rows in Data
- Split Text, Remove Duplicates and Trim Spaces
- Find & Replace Values
- How to Properly Read & Understand Functions
- Unique & Sort Functions
- SUM(IFS) by Criteria (also COUNT, AVERAGE, MAX, MIN)
- Rounding Values
- Logical & Information Functions
- IF Function (IF, Then)
- Nested IF & IFS Functions
- Error Types & How to Handle Errors
- Named ranges.
DAY THREE
Visualizing Data with Microsoft Power BI
- Power BI Ecosystem
- Getting data from sources (CSV Files, Online, Excel, Sql Server, Text etc.)
- Extracting, Loading and Transforming Data for Analysis
- Profiling, Appending and Merging Data
- Introduction to Calculated Columns
- Introduction to Measures
- The Power BI Chart and Visualization Tools
- Reviewing Over Twenty Visualization Types
- Designing a Dashboard
- Publishing and Sharing a Dashboard Online
- Introduction to AI Powered Visuals
- Interpreting Data with AI Powered Visuals
- Using Third Party Visuals for Custom Visualizations.
DAY FOUR
Lookup, Date and Time 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
- Useful & Easy Text Functions
- Search & Substitute Text
- Split & Join Values to / from Multiple Columns
- Essential Date Functions
- Time Calculations (Calculating Hours worked)
Important Google Functions
- Detect Language and Translate Your Spreadsheets
- Image Function (Create QR Codes)
- Import Tables From the Web (IMPORTHTML)
- Import Any Data from the Web (IMPORTXML)
- Stock & Currency Data with GOOGLEFINANCE.
DAY FIVE
Charting and Pivoting Data
- 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
- Sparklines
- Sparklines for Stock & Currency Trends
- Explore Feature in Google Sheets
- Pivot Table Basics in Google Sheets
- Filters & Slicers for Dynamic Pivot Tables
- Grouping Items & Dates (by month, year)