MS EXCEL
Advanced Excel Course – Overview of the Basics of Excel
• Customizing common options in Excel
• Absolute and relative cells
• Protecting and un-protecting worksheets and cells
Advanced Excel Course – Working with Functions
• Writing conditional expressions (using IF)
• Using logical functions (AND, OR, NOT)
• Using lookup and reference functions (VLOOKUP, HLOOKUP, MATCH, INDEX)
• VlookUP with Exact Match, Approximate Match
• Nested VlookUP with Exact Match
• VlookUP with Tables, Dynamic Ranges
• Nested VlookUP with Exact Match
• Using VLookUP to consolidate Data from Multiple Sheets
Advanced Excel Course – Data Validations
• Specifying a valid range of values for a cell
• Specifying a list of valid values for a cell
• Specifying custom validations based on formula for a cell
Advanced Excel Course – Working with Templates
• Designing the structure of a template
• Using templates for standardization of worksheets
Advanced Excel Course – Sorting and Filtering Data
• Sorting tables
• Using multiple-level sorting
• Using custom sorting
• Filtering data for selected view (AutoFilter)
• Using advanced filter options
Advanced Excel Course – Working with Reports
• Creating subtotals
• Multiple-level subtotals
• Creating Pivot tables
• Formatting and customizing Pivot tables
• Using advanced options of Pivot tables
• Pivot charts
• Consolidating data from multiple sheets and files using Pivot tables
• Using external data sources
• Using data consolidation feature to consolidate data
• Show Value As ( % of Row, % of Column, Running Total, Compare with Specific Field)
• Viewing Subtotal under Pivot
• Creating Slicers
Advanced Excel Course – More Functions
• Date and time functions
• Text functions
• Database functions
• Power Functions (CountIf, CountIFS, SumIF, SumIfS)
Advanced Excel Course – Formatting
• Using auto formatting option for worksheets
• Using conditional formatting option for rows, columns and cells
Advanced Excel Course – Macros
• Relative & Absolute Macros
• Editing Macro’s
Advanced Excel Course – WhatIf Analysis
• Goal Seek
• Data Tables
• Scenario Manager
Advanced Excel Course – Charts
• Using Charts
• Formatting Charts
• Using 3D Graphs
• Using Bar and Line Chart together
• Using Secondary Axis in Graphs
• Sharing Charts with PowerPoint / MS Word, Dynamically
• (Data Modified in Excel, Chart would automatically get updated)
SQL SERVER
• Introduction to SQL
• Retrieving Data
• Updating Data
• Inserting Data
• Deleting Data
• Sorting and Filtering Data
• Advanced Filtering
• Summarizing Data
• Grouping Data
• Using Sub queries
• Joining Tables
• Managing Tables
• Using Views
• Stored Procedures
• Using Cursors
• Using Transactions
MS POWER BI
Power BI Desktop Tutorial
• Download and Install BI Desktop
• Add, Rename, Duplicate, and Delete Pages
• Connect to SQL Server
• Connect to Multiple Excel Sheets
• Get Data from Excel Files
• Get Data from Text Files
• Load Data from Multiple Data Sources
• Remove Unwanted Columns from Tables
Power BI Transformations
• Change the Data type of a Column
• Combine Multiple Tables
• Clusters
• Enter data or Copy & Paste data from Clipboard
• Format Dates
• Groups
• Hierarchies
• Joins
• Pivot Table
• Query Groups
• Reorder or Remove Columns
• Rename Column Names
• Rename Table Names
• Split Columns
• UnPivot Table
Power BI Charts Tutorial
• Area Chart
• Bar Chart
• Card
• Clustered Bar Chart
• Clustered Column Chart
• Column Chart
• Donut Chart
• Funnel Chart
• Heat Map
• Line Chart
• Clustered Column and Line Chart
• Line and Stacked Column Chart
• Matrix
• Multi-Row Card
• Pie Chart
• Ribbon Chart
• Stacked Area Chart
• Scatter Chart
• Stacked Bar Chart
• Stacked Column Chart
• Table
• Tree Map
• Waterfall Chart
• 100% Stacked Bar Chart
• 100% Stacked Column Chart
• Map
• Filled Map
• Format Area Chart
• Format Bar Chart
• Formatting Card
• Format Clustered Bar Chart
• Format Clustered Column Chart
• Formatting Column Chart
• Format Donut Chart
• Format Funnel Chart
• Formatting Line Chart
• Format Line & Clustered Column
• Format Line & Stacked Column
• Formatting Matrix
• Format Multi-Row Card
• Format Pie Chart
• Formatting Ribbon Chart
• Format Stacked Area Chart
• Formatting Scatter Chart
• Format Stacked Bar Chart
• Formatting Stacked Column Chart
• Format Table
• Format Tree Map
• Formatting Waterfall Chart
• R Script
• Format Map
• Format Filled Map
Power BI Filters Tutorial
• Slicer
• Basic Filters
• Advanced Filters
• Top N Filters
• Filters on Measures
• Page Level Filters
• Report Level Filters
• Drill through Filters
Power BI Tutorial on Calculated Fields
• Calculated Columns
• Conditional Columns
• Calculated Measures
• Calculated Tables
• Custom Columns
Power BI Tutorial on Dashboards
• Register to Pro Service
• Dashboard Introduction
• Connect Desktop with BI Service or Pro
• Publish Desktop Reports
• Create a Workspace
• Create a Dashboard
• Favourites
Working with Power BI Dashboards.
• Dashboard Actions
• Add Reports to a Dashboard
• Add Title to Dashboard
• How to Add Image to Dashboard
• Add Video to Dashboard
• Add Web Content to Dashboard
• Dashboard Settings
• Delete a Dashboard
• Pin Report to a Dashboard
Sharing Power BI Work
This Power Bi tutorial section covers the sharing of Work. I means, sharing reports, Dashboards, apps, Workspaces etc.
• Share a Dashboard
• Share a Report
• Sharing Workspace
• Publish App
• View Published App
Power BI DAX Tutorial
• Aggregate Functions
• Date Functions
• Logical Functions
• Math Functions
• String Functions
• Trigonometric Functions
VBA MACROS
Unit – 1 Sheet related Activities
• Simple data
• If statements
• Tool box controls
• Loops
• Multiple loops
• Data filtering & populating to listbox
• Data filtering & populating to another column in the same sheet
• Font related activities
• Font color activities
• Cells formatting activities
• Cells fill color activities
• Sheets renaming, protecting
• Sheets sorting
• Data sorting
• Blank rows, columns deleting
• Hide, unhide rows, cols
• Workbook save, delete activities
• Range, cells controlling
Unit – 2 Multiple sheets data activities
Unit – 3 Chart related activities
Unit – 4 Msgbox related activities
Unit – 5 Other applications related activities
Unit – 6 Transferring data from excel to word, access, and pdf files
Unit – 7 Administrator related activities
Unit – 8 Pivot Tables and Pivot Chart Related Programs
