1. The Foundations & Concept of Data in Excel - our “3-2-1” Framework of Data

• Three Types of Data
• Dates
• The challenge of “looks like date but does not behave like date”
• What is a DATE?
• Recommended Date Formatting – dd-mmm-yyyy
• Extracting Month Name, Year, Day Name, Week Number, etc. from a date.
• Text
• The main challenges
• Cases
• Spaces
• Numbers
• The ideal and recommended formatting – Accounting
• Why Accounting is our recommendation
• Formatting large numbers as thousands ( 1,234,567 as 1,234 k)
• Formatting large numbers as millions with a decimal ( 1,234,567 as 1.2 M)
• Two Sources of Data
•  Raw Data
•  Calculated Data
• One Cell type – Range or Table

2. Data in “Excel Range” or in “Excel Tables” : The 1978 way of “Range” vs. 2020 way of “Table”

• Why copy formulas in the column after you have typed one?
• Why expand “range” after addition of new row or column to data.
• Use of Excel Tables – end of Excel “range” way.
• Understand the benefits of using an Excel table over a range of data
• Convert a data range into an Excel table
• Review of the Excel table contextual tab
• Quickly format a table with pre-defined table styles
• Filter data within a table
• Quickly add columns and rows into an existing table
• Add a Total row to a table for quick calculations
• Automatically add a function to all rows within a table
• Learn the benefits of using an Excel table to create a PivotTable
• Why name a Table with “tbl”?

3. Slicers for Data – avoid old FILTERS

Old Way of FILTERs vs NEW Way of SLICERS

• Concept of Horizontal Slicers
•  Best practices for Slicer position and size
•  Use of multiple columns
• Formatting Slicers using Styles
• Setting Default Slicer Style
• Deleting Slicers
• Glue your slicers so that even Excel can not move them
• Slicer Settings – best practices
• Slicers: One Pivot - Multiple Reports
• Super glue your slicers so that even Excel can not move them
• Positioning of Slicer with Pivot Tables
• To Print or Not to Print Slicers.
• Unlocking Slicers while Protecting a Sheet

4. Calculations Concept

• Relative and Absolute Reference
• Use of F4 Key
• Entering Formulas
• Using The Mouse to Enter Cell
• References in Formulas
• Editing Formulas – Using F2 Key
• The Order of Evaluation: BODMAS
• Using Parentheses to Change the Order
• Entering Functions
• The Structure of Functions
• The =SUM() Function
• Using the Mouse to Enter Range
• References
• Entering Functions in the Formula Bar
• Inserting Functions
• Applying AutoSum

5. Formatting Numbers, Text, Reports

• Number Formatting
• Dates Formatting
• Text Formatting
• Row and Column Formatting
• Additional Formatting Options: Styles
• Other Advanced Formatting
• Merging Cells
• Wrapping Text
• Transposing Data
• Using Paste Special Operations

6. Printing

• Preparing to Print
• Using the Spelling Checker
• Using Page Break Preview
• Page Setup Options
• Page Orientation
• Page Size
• Scaling
• Margins
• Custom Headers and Footers
• Print Titles: Rows or Column to Repeat
• Printing Worksheets

7. Power of IF and VLOOKUP – a Teaser

• =IF() Function
• =VLOOKUP() Function

8. Introduction to Pivot Tables

• Reports with ZERO Errors & ZERO Formulas
• Using Styles to Format Pivot Reports

9. Introduction to Charts

• Creating Charts with One Key Press!
• The FOUR Critical Steps
• Pie and Column Charts

1. The Foundations & Concept of Data in Excel - our “3-2-1” Framework of Data

Three Types of Data

• Dates
• The challenge of “looks like date but does not behave like date”
• What is a DATE?
• Recommended Date Formatting – dd-mmm-yyyy
• Extracting Month Name, Year, Day Name, Week Number, etc from a date.
• Text
• The main challenges
• Cases
• Spaces
• Numbers
• The ideal and recommended formatting – Accounting
• Why Accounting is our recommendation
• Formatting large numbers as thousands ( 1,234,567 as 1,234 k)
• Formatting large numbers as millions with a decimal ( 1,234,567 as 1.2 M)
• Two Sources of Data
•  Raw Data
•  Calculated Data
• One Cell type – Range or Table

2. Data in “Excel Range” or in “Excel Tables” : The 1978 way of “Range” vs 2020 way of “Table”

• Why copy formulas in the column after you have typed one?
• Why expand “range” after addition of new row or column to data.
• Use of Excel Tables – end of Excel “range” way.
• Understand the benefits of using an Excel table over a range of data
• Convert a data range into an Excel table
• Review of the Excel table contextual tab
• Quickly format a table with pre-defined table styles
• Filter data within a table
• Quickly add columns and rows into an existing table
• Add a Total row to a table for quick calculations
• Automatically add a function to all rows within a table
• Learn the benefits of using an Excel table to create a PivotTable
• Why name a Table with “tbl”?

3. Slicers for Data – avoid old FILTERS

Old Way of FILTERs vs NEW Way of SLICERS

• Concept of Horizontal Slicers
•  Best practices for Slicer position and size
•  Use of multiple columns
• Formatting Slicers using Styles
• Setting Default Slicer Style
• Deleting Slicers
• Glue your slicers so that even Excel can not move them
• Slicer Settings – best practices
• Slicers: One Pivot - Multiple Reports
• Super glue your slicers so that even Excel can not move them
• Positioning of Slicer with Pivot Tables
• To Print or Not to Print Slicers.
• Unlocking Slicers while Protecting a Sheet

4. Calculations Concept

• Relative and Absolute Reference
• Use of F4 Key
• Entering Formulas
• Using The Mouse to Enter Cell
• References in Formulas
• Editing Formulas – Using F2 Key
• The Order of Evaluation: BODMAS
• Using Parentheses to Change the Order
• Entering Functions
• The Structure of Functions
• The =SUM() Function
• Using the Mouse to Enter Range
• References
• Entering Functions in the Formula Bar
• Inserting Functions
• Applying AutoSum

5. Deep Dive – PivotTables – The Real Power of Data Analytics

• The NEW way of Pivot Tables: Create a Pivot Table that works for LIFE –on refresh, all new rows and columns of data should get included.
• The commonly faced challenges in a Pivot, and solutions for them.
• Challenge #1: There are lot of empty cells in the report – how to have “zero” value in all of them.
• The FIVE Critical Pivot Table Options
• Format Numbers (Not Cells)
• Report format: Style and Design Components to Format a Report
• The Report: Sum, Count
• The Report: Concept Of Data Cubes
• The Problem Of Pivot Report not taking New Data
• Analysis Escalator
• Historical or Descreptive Analysis
• Using Slicers instaed of Filetrs in Pivot Tables – Transition from 1978 to 2020 Way
• The 4 Quadrants of Data Analysis
• Understanding Dimensions 
• Time Dimension
• Product or Service Dimension
• Geography or Market  or Region Dimension 
• Customer or Prospect Dimension  
• Diagnostic Analysis 
• Show Amount as % Of Total
• % of Grand Total or % of Column Total – which is better?
• % Parent Row Totals
• % Parent Column Totals
• % Row Totals
• Variance Analysis – Difference in Value v/s Difference in %
• Running Totals in Value, %
• Ranking Largest to Smallest
• Index

6. The daily grind of data warriors - VLookup Functions 

• =VLOOKUP()
• How to lookup Vertically for data – data in other columns. 
• Eliminate the fixed range challenge created by “$A$1:$M$500” range way of VLOOKUP. Use TABLES
• Using VLOOKUP to find exact matches
• How to provide insurance against current and future “#N/A” errors
• Root Cause Analysis of why we get “#N/A” error and solutions to the root cause
• #N/A Reason: Unwanted Blank Spaces (bsp)
• #N/A Reason: Spaces that are NOT spaces in reality (Non Blank Spaces – nbsp)
• #N/A Reason: Transactional Data is text where as lookedup data is numbers.
• #N/A Reason: Transactional Data is number where as lookedup data is text
• #N/A Reason: Spelling Mistakes
• Looking across two different files without “$A$1:$M$500” range way. 
• Using VLOOKUP to Group number oriented data like Revenue, Stock, Accounts Receivables, Employee Tenure, etc using APPROXIMATE match component of VLOOKUP.
• Using MATCH and INDEX functions

7. Logical Functions

• =IF()
• =OR()
• =AND()
• =IF(IF(IF)) – Nested IF

8. Date Functions

• Is it a Date?
• Calculate the exact service length in YEARS, MONTHS, DAYS without dividing by 365! Use function DATEDIF().
• Find the number of days between two dates using =NETWORKDAYS.INTL()
• Calander days
• Working Days (without FRI-SAT as weekends)
• Working Days (without Public Holidays and FRI-SAT as weekends)

9. Conditional Formatting – Basics to Advanced

Bars

• Highlight Cell Rule
• Top-Bottom Rule
• Manual Rules
• Formula Based CF Rules 

10. Charts Basics to Creative Charts 

• Creating Charts with One Key Press!
• The FOUR Critical Steps
• Pie and Column Charts
• Creative Charts

11. Creating Interactive Dashboards Screenshots

• Dashboard planning
• Use of Filters inside Pivots.
• Using Pivot Charts
• Why keep formatting all Charts manually – let Excel do this work!
• Using Slicers
• Multiple Charts being controlled by several Slicers – the inter-activity
• Protecting your Dashboard

1. Advanced Functions

• =MATCH()
• =INDEX()
• =VLOOKUP() with nth Instance
• =SUMIF()
• =COUNTIF()
• =SUMIFS()
• =COUNTIFS()
• =RANK()
• =LARGE()
• =SMALL()
• =MAX(), MIN() with Array formuals
• =Array Formuals , the CSE Formulas
• =FORECAST()
• =User Defined Functions with a Macro
• =XLOOKUP()

2. Power Pivots – the Future Way – Alternative of VLOOKUP

• For Scalability and Usability
• Relationships
• STAR Schema – Data & Lookup Tables
• Data and diagram view
• Set up of Power Pivot
• Creating your first Power Pivot report
• Creating Reports Using PowerPivots

3. Power Query

• Import Data from Various Sources: Excel, Text, Web 
• Aggregate or summarize data 
• Append Query: Combine one below other 
• Combine Different files of similar format from a folder 
• Combine different sheets of similar format from a file 
• Creating calculating columns / fields which never existed in the source data. 
• Deleting / sorting rows, columns or blanks 
• Extract 
• Filtering out data you don't need 
• Find & replace text 
• Incorporating the next month (or period) data to an existing report 
• Merge Query: Combine side by side 
• Merging / consolidating / appending data from Text files, CSV files or database tables. 
• Perform same steps again when receive new file or data in next month 
• Pivot data 
• Unpivot Data for Pivot Tables 
• Removing spaces and special characters from your data. 
• Split Columns 
• Structured column 
• Transforming text, numeric, and date columns. 
• Transpose Data 

1. Deep Dive – PivotTables – The Real Power of 

     Data Analytics

• The NEW way of Pivot Tables: Create a Pivot Table that works for LIFE –on refresh, all new rows and columns of data should get included.
• The commonly faced challenges in a Pivot, and solutions for them.
• Challenge #1: There are lot of empty cells in the report – how to have “zero” value in all of them.
• The FIVE Critical Pivot Table Options
• Format Numbers (Not Cells)
• Report format: Style and Design Components to Format a Report
• The Report: Sum, Count
• The Report: Concept Of Data Cubes
• The Problem Of Pivot Report not taking New Data
• Analysis Escalator

• Historical or Descreptive Analysis
• Using Slicers instaed of Filetrs in Pivot Tables – Transition from 1978 to 2020 Way
• The 4 Quadrants of Data Analysis
• Understanding Dimensions 
• Time Dimension
• Product or Service Dimension
• Geography or Market  or Region Dimension 
• Customer or Prospect Dimension

• Diagnostic Analysis 
• Show Amount as % Of Total
• % of Grand Total or % of Column Total – which is better?
• % Parent Row Totals
• % Parent Column Totals
• % Row Totals
• Variance Analysis – Difference in Value v/s Difference in %
• Running Totals in Value, %
• Ranking Largest to Smallest
• Index

• Predective Analysis
• Using Forecast Function
• Using Forecast.LINEAR
• Trend Charting
• Forecast Sheet

• Prescreptive Analysis
• What if and Scenario Analysis – The Practical Approach
• Goal Seek Scenario Analysis

• Pivot Tables - Calculated Fields
• Pivot Tables - Calculated Items
• Grouping
• Aging Analysis

• Creating PNL Using Pivot Tables

2. Journey of Excel Functions for Fundamental Data Analysis

• Text Functions 
• =SEARCH()
• =SUBSTITUTE()
• =LEN()
• =LEFT()
• =RIGHT()
• =MID()
• =TEXT()
• =PROPER()
• =TRIM()
• =CONCATENATE() (or “&”)

• Count functions
• =SUMIF()
• =COUNTIF()
• =SUMIFS()
• =COUNTIFS()
• =RANK()
• =LARGE()
• =SMALL()

• Intro Statistical functions
• =MAX(), MIN() with Array formuals
• =Array Formuals , the CSE Formulas

3. Data Analysis Using Statistical Analysis Functions

• =AVERAGE() – The MEAN
• =VAR.S() – The Concept of Sample
• =VAR.P() – The Concept of Population
• =STDDEV.S() - The Concept of Sample SD
• =STDDEV.P() - The Concept of Population SD
• =MEDIAN()
• =MODE()
• =QUARTILE.INC()
• =CORREL – The Correlation

4. DATA MODEL - Power Pivots and Power Query

• Power Pivots – An Alternative for VLOOKUP
• Understanding DATA Model
• Relationships
• Power Pivot

5. Power Query – ETL (Extract, Transform and Load)

• Loading Multiple Data Sources
• Clean and transform
• Append Queries

1. My Data in Excel

2. Dashboard on my Mobile Device Using Microsoft Power BI – the Journey 

3. Power BI 

• On my Desktop (to use Excel files) On my Web Browser 
• On my Mobile (to show Dashboard) How to get it and install? 

4. Power BI on Desktop 

• Bringing Excel Files to Power BI Getting Excel Data into Power BI 
• Excel Data - Dos and Don’ts / Best Practices Naming of Table 
• Naming of columns 
• Date format only - No columns for Month / Qtr./ Year 
• Data type: 
• Using multiple sheets or multiple files for data. 

5. Making Charts on Desktop Power BI 

• Type of Visualizations Default visuals 
• Custom visuals: The recommendations- Sparkline, Card with State 
• Location Download Process 
• Import into Power BI Desktop Import into Power BI web 
• Suggested visualizations for Finance Data Maps: Filled maps vs Maps 
• Slicers Combo Charts Bar Charts 
• Single Number Card Tile Gauge Chart 
• Matrix 
• Pie and Doughnut Charts Line Charts 
• Scatter & Bubble charts 

6. Frequently Used Formatting in Visuals 

• Data Labels Title 
• Font size 
• Showing in Millions or Thousands - how to Seeing data not visualized - hovering - tool tip Using your Corporate Colors 

7. Interactivity in Power BI Reports / Interactivity amongst Charts 

• Use of Slicers - Page Level only Use of Filters 
• Chart Level Page Level Report level 

8. Combination of Charts Makes a Report

• Saving your Report - the file type ".PBIX" 
• Publishing your Report to Power BI Web - need for login 

9. Power BI Web 

• Use Power BI web on Browser Need for login 
• Power BI Desktop to Power BI Web - the Reports to Dashboard 
• Pinning Charts from Reports to create Tiles in a Dashboard 
• Playing around with Tiles - arrangement, serial order, size - for WEB and for MOBILE. 
• Back to WYSIWYG - See "as on mobile" on the web. 
• Modifying report, dashboard till you get what you want; Adding new Chart 

10. Power BI Web to Power BI APP on your Mobile 

• Log in to Power BI app – the settings 
• Getting used to interface of Power BI with sample dashboards 
• Reports and Dashboard tabs Preview Dashboard on your Mobile 
• Interactivity in Power BI Dashboard -Deep dive for full chart. 
• While Using a Dashboard: Write Comments on a chart and share via WhatsApp 

11. Sharing the Dashboard 

• Within same organization Across organizations

Coming Soon

Coming Soon

Coming Soon