Excel, Adv. Excel & Analytics
  • Understand the Excel interface.
  • Learn basic navigation and data entry.
  • Excel Interface Overview
    • Workbook vs. Worksheet
    • Ribbon, Tabs, and Menus
    • Cells, Rows, Columns
    • Name Box and Formula Bar
  • Basic Navigation
    • Moving around the worksheet
    • Selecting cells, rows, and columns
    • Using the Go To command
  • Data Entry and Basic Formatting
    • Entering text, numbers, and dates
    • Editing cell contents
    • Basic formatting (font, size, color, cell alignment)
  • Saving and Managing Workbooks
    • Saving, opening, and closing workbookss
    • Save As options (different formats)
  • Summary Functions: SUM, AVERAGE, MIN, MAX, SUMIF, COUNTIF, AVERAGEIF, SUMIFS, COUNTIFS, AVERAGEIFS
  • Using AutoSum
  • Cell Referencing
    • Relative vs. Absolute References
    • Mixed References
  • Organize and manage data efficiently
  • Sorting and Filtering Data
    • Sorting by one or multiple columns
    • Applying filters to display specific data
    • Using Filters using effectively
  • Using Tables
    • Creating and formatting tables
    • Table styles and features (Total Row, Header Row)
  • Data Validation:
  • Creating drop-down lists
  • Setting data entry rules
  • Conditional formatting:
    • Conditional formatting using ifs function, greater than, lower than and equal between
    • Conditional formatting using colours
    • Conditional formatting using colour scales, data bars & Icon sets
  • Use advanced formulas and functions
  • Logical Functions
    • IF, AND, OR, NOT
  • Loopkup Functions
    • VLOOKUP, VLOOKUP WITH MATCH FUNCTION, VLOOKUP WITH COLUMN & ROW FUNCTION, VLOOKUP WITH SEQUENCE FUNCTION, VLOOKUP WITH CURLY PARANTHESIS FUNCTION, VLOOKUP WITH SUMIF, SUMIFS, COUNTIF & COUNTIFS FUNCTIONS, VLOOKUP WITH MATCH FUNCTION, INDEX & MATCH, XLOOKUP, XLOOKUP WITH CONCATENATE FUNCTIONS
  • Text Functions
    • CONCATENATE, LEFT, RIGHT, MID, LEN
  • Date and Time Functions
    • TODAY, NOW, DATE, TIME, YEAR, MONTH, DAY, EOMONTH
  • Office 365 function
    • Transpose
    • Textjoin
    • Sequence
    • Filter
    • Xlookup
    • Vstack
    • Hstack
    • Aggregate and many more
  • Analyze data using built-in Excel tools
  • Introduction to PivotTables
    • What is a PivotTable?
    • Benefits and uses of PivotTables
  • Creating a PivotTable
    • Preparing your data
    • Inserting a PivotTable from various data sources
  • Understanding PivotTable Components
    • Rows, Columns, Values, and Filters areas
    • Field List and Field Settings
  • Basic PivotTable Operations
    • Adding and arranging fields
    • Sorting and filtering data within PivotTables
    • Grouping data (by dates, numbers, etc.)
  • Customizing PivotTables
    • Changing the layout and style
    • Using different PivotTable layouts (Compact, Outline, Tabular)
    • Formatting numbers and cells
  • Calculations in PivotTables
    • Adding calculated fields and items
    • Using summary functions (SUM, COUNT, AVERAGE, etc.)
    • Showing values as percentages, differences, rankings
  • PivotTable Options and Settings
    • Understanding PivotTable options
    • Refreshing data in PivotTables
    • Changing data source
  • PivotCharts
    • Creating PivotCharts from PivotTables
    • Customizing and formatting PivotCharts
    • Understanding the relationship between PivotTables and PivotCharts
  • Advanced PivotTable Techniques
    • Creating dynamic PivotTables with named ranges
    • Using Power Pivot to manage large data sets
    • Connecting PivotTables to external data sources
  • Create and customize advanced charts and graphs
  • Creating Charts
    • Column, Bar, Line, Pie, and Combo charts
    • Customizing chart elements (titles, axes, legends)
  • Using Sparklines
    • Creating line, column, and win/loss sparklines
  • Advanced Chart Techniques
    • Dynamic charts with named ranges
    • Using secondary axes
  • Automate tasks using Macros and VBA
  • Introduction to Macros
    • Recording and running macros
    • Macro security settings
  • Basics of VBA (Recording):
    • Starting with the recording Macros
    • Recording of Macros
    • Saving and running of Macros
  • Creating Custom Functions
    • Writing and using custom functions in VBA
  • Use Excel’s collaborative and security features
  • Sharing and Collaboration
    • Sharing workbooks
    • Co-authoring and tracking changes
    • Comments and notes
  • Protecting Data
    • Worksheet and workbook protection
    • Setting permissions
  • Excel Online and Mobile
    • Using Excel in Office 365
  • Power Query
  • Introduction to Power Query
    • What is Power Query?
    • Importance and benefits of using Power Query
  • Getting Started with Power Query
    • Installing and accessing Power Query
    • Power Query interface and key components
  • Connecting to Data Sources
    • Types of data sources (Excel, databases, web, etc.)
    • How to connect to different data sources
  • Data Import and Shaping
    • Loading data into Power Query
    • Basic data transformation (filtering, sorting, removing duplicates)
  • Transforming Data
    • Using the Query Editor
    • Applying common transformations (merging, appending, splitting columns)
    • Pivoting and unpivoting columns
    • Grouping data
  • Data Cleaning
    • Handling missing values
    • Data type conversion
    • Text transformations (trim, clean, replace, etc.)
  • Working with Functions
    • Using built-in functions
    • Creating custom functions
    • Parameters in Power Query
  • Advanced Data Transformations
    • Merging queries
    • Append queries
    • Using conditional columns
    • Calculating and adding new columns
  • Scenario manager
  • Macro (record functionality)
  • Dashboards (Project)
    • What are dashboards
    • Getting data from differnt worksheets
    • Display data in various charts on same sheet etc.