Course Overview

EXCEL COURSE

1. Introduction to Excel
• What is Excel & its complete history
• Basic terminology of Excel
• Spreadsheet environment & Object Model of Excel
• Different versions of Excel (97-2003,2007,2010, 2013,2016 &2019) and what is new in each version of it
• Different file formats – .xls,.xlsx,.xlsm,.xlsb,.xlam,.csv…etc., and when to use which format of Excel
• Excel user interface and customization of Quick Access Toolbar

Basic Excel

2. Clipboard Group
• Cut
• Copy
• Copy as Picture
• Different Types of Paste Special Options
• Paste vs Paste Special
• Format Painter

3. Font Group
• Font Style
• Font Size
• Border Styles
• Cell Colors
• Font Colors

4. Alignment Group
• Cell Alignment
• Orientation
• Indent Level
• Wrap Text
• Merge/Unmerge

5. Data Types in Excel
6. Number Group
• General
• Number
• Currency
• Accounting
• Short Date
• Long Date
• Time
• Percentage
• Fraction
• Scientific
• Text
• Custom number formatting
• How to use your own formatting
• Advance techniques in Number formatting
• How to change default numbersettings
• Comma Style
• Increase/Decrease Decimals

7. Conditional Formatting
• What is Conditional Formatting
• How to change existing formatting
• How to delete existing formatting
• Highlight cells Rules
• Top/Bottom Rules
• Data Bars
• Color Scales
• Icon Sets
• Writing Complex Conditional Formatting rules using formulas
Project1: 23 Interview tasks using conditional formatting

8. Format as Table (FT)
• What is FT
• Differences between Normal Table and Format as Table
• Advantages of FT
• How to create Dynamic Pivot Tables & Charts using FT
• How to create effective formulas using Format as Table
• Convert FT to Normal Range
• Create our own Table style

9. Cell Styles
10. Cells
• Insert Cells/Rows/Columns/Sheets
• Delete Cells/Rows/Columns/Sheets
• Hide Rows/Columns/Sheets
• Unhide Rows/Columns/Sheets
• Row Height/Column width
• Rename Sheet
• Change Sheet Tab Color
• Move/Copy Sheet

11. Fill Options
• Copy Down
• Copy Right
• Copy Up
• Copy Left
• Copy to Selected Range
• Fill Series by Column/Row
• Justify Fill

12. Clear Options
13. Find & Select
• Find
• Replace
• Go To
• Go To Special
• Select Objects
• Selection Pane

Advanced Excel

14. Formulas Introduction
• What is Formula
• How to start with formula
• What is Argument?
• How to give input to formulas
• What is Optional & Mandatory argument?
• How many ways we can give input to formulas
• Mathematical Operators
• Comparison Operators

15. Cell Reference Styles
• Relative Style
• Absolute Style
• Row Freeze
• Column Freeze

16. Text Functions
LEN, UPPER, LOWER, PROPER, FIND, SEARCH, LEFT RIGHT, MID, SUBSTITUTE, REPLACE, CONCATENATE, CONCAT, TEXTJOIN, CHAR, CODE, REPT, DOLLAR, FIXED EXACT etc.
Project2: 11 Interview tasks using Text Functions

17. Date & Time
TODAY, NOW, DAY, MONTH, YEAR, TIME, HOUR, MINUTE, SECOND, WEEKDAY, WEEKNUM, EDATE, YEARFRAC, DAYS360, DAYS, EOMONTH, DATEVALUE, TIMEVALUE, NETWORKDAYS, NETWORKDAYS.INTL, WORKDAY, WORKDAY.INTL, DATEDIF etc.
Project3: 7 Interview Based Tasks using Date & Time

18. Logical Functions
TRU, FALSE, AND, OR, NOT, IF, IFS, IFERROR
Project4: 9 Interview tasks using Logical Functions

19. Mathematical Functions
ABS, ROUND, ROUNDUP, ROUNDDOWN CEILING, FLOOR, MROUND RAND, RANDBETWEEN, MOD, QUOTIENT, FACT,LCM, GCD, PI, EXP, SQRT, PRODUCT, POWER, INT, EVEN, ODD, SUM, SUMIF, SUMIFS SUBTOTAL, SUMPRODUCT etc.
Project5: 7 Interview task using Mathematical functions

20. Statistical Formulas
COUNT, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS AVERAGE, AVERAGEA, AVERAGEIF, AVERAGEIFS MAX, MIN, MAXIFS, MINIFS, MAXA, MINA, LARGE, SMALL, RANK etc.
Project6: 7 Interview tasks using Statistical Functions

21. Information Formulas
ISTEXT, ISNONTEXT, ISNUMBER, ISNA, ISREF, ISERR, ISERROR, ISEVEN, ISODD, ISBLANK, ISLOGICAL, ISFORMULA, CELL, INFO, SHEET, SHEETS, N, NA, TYPE, ERROR.TYPE etc.

22. Lookup & Ref Formulas
VLOOKUP, MATCH, HLOOKUP, INDEX, OFFSET, CHOOSE, INDIRECT, ROW, ROWS, COLUMN, COLUMNS, TRANSPOSE, HYPERLINK, HLOOKUP etc.
Project7: 33 Interview tasks using Lookup & Ref Funcs

Report Designing Techniques
23. Dynamic conditional formatting using formulas
24. Illustrations
• Insert Pictures
• Insert online pictures
• Insert Shapes
• Insert Icons
• Insert 3D pictures
• Insert Smart Art
• Screenshots

25. Sparklines
• What is Sparkline?
• What is the difference between charts & Sparklines
• Line Sparkline
• Column Sparklines
• Win/Loss Sparklines

26. Filter Options
• What is Slicer?
• From which version this is available
• What is Timeline?
• From which version this is available
• Slicers vs Timeline

27. Insert Options
• Hyperlink
• Comments
• Text Box
• Header & Footer
• WordArt
• Signature Line
• Object
• Hyperlink vs Object
• Symbols
• Equations

Report Printing Techniques
28. Themes
• Change Document Styles using Themes
• Change Document Color
• Change Document fonts
• Change Document effects
• Create our own Theme

29. Page Setup
• Set Page Margins
• Change the orientation
• Set the paper size
• Set print area
• Clear Print area
• Insert/Remove Page breaks
• Insert picture as a background
• Print Titles

30. Scale to Fit
• Change scale to fit pages

31. Sheet Options
• Print Gridlines
• Print Headings

32. Arrange Objects
Formulas Execution Techniques

33. Define Names
• What is Define Name/Named Range?
• Create static Named Range
• Create dynamic Named Range
• Edit existing Named Range
• Delete Existing Named Range
• Use Named Ranges in Formulas
• Use Named Ranges in Charts
• Use Named Ranges in Dynamic Drop downs
• Use Named Ranges in Pivot Tables etc.

34. Formulas Auditing
• Trace Precedents
• Trace Dependants
• Show Formulas
• Error Checking
• Evaluate Formulas
• Watch Window

35. Calculation Options
• Automatic Calculations
• Automatic Calculations except for Data tables
• Manual Calculations using Calculate Now
• Manual Calculations using Calculate Sheet

Data Management Techniques
36. Connect with External Applications
• Create a connection with Database
• Create connection with Text/CSV file
• Create a connection with Website
• Extract data from external sources
• Delete existing connections
• Refresh connections
• Set time to refresh connections automatically

37. Sort & Filter
• Sort A-Z
• Sort Z-A
• Level by Sorting/Custom sorting
• Filter by Text
• Filter by Number
• Filter by Text Color
• Filter by font Color
• Filter by Icons
• Filter using search box
• Add selection to existing filters
• Advanced filters using conditions
• Paste unique filtered data in otherlocation
• Auto Filter vs Advanced Filter

38. Data Tools
• Split Data into multiple columns using Text to columns
• Flash Fill
• Remove Duplicates
• Consolidate data from multiple sheets

39. Data Validation
• What is Data Validation?
• Static Data Validation
• Create input message
• Customize Error alert
• Change the Error Style (Stop, Warning etc.)
• Dynamic Data Validation using formulas
• Static List by Selecting Range/ Manual input
• List by using Named Range
• Dependant drop downs using Named Range
• Highlight Invalid Data using Circles
• Clear validation circles
• Clear Data Validation

40. Forecast
• What if Analysis – Scenario Manager
• Goal Seek
• Data Table

41. Outline
Group
Ungroup
Sub-Totals

Data Protection Techniques
42. Spell Checking Options
• Spelling
• Thesaurus
• Translate
• Auto Correct Options

43. Protection Options
• Worksheet Protection (Restrict user to enter or modify data)
• Workbook Protection (Restrict user to Insert/ Delete Sheet, Move Sheet, Copy Sheet, Change Tab Color etc) – Structure Protection
• How to protect specific range
• Allowing users to edit the protected range
• Lock cells / Hide formulas
• Workbook Encryption

44. Comments
• Insert Comment
• Show/Hide Comments
• Modify Comments
• Delete Comment

45. Notes
• Insert Note
• Show/Hide Notes
• Modify Notes
• Delete Notes

Data Viewing Techniques
46. Workbook Views
• Normal View
• Page Break Preview
• Page Layout View
• Custom Views

47. Show/Hide
• Gridlines
• Formula Bar
• Headings

48. Zoom Options
49. Create new window for workbook
50. Arrange Workbooks
51. Split worksheet screen
52. View workbook side by side for comparison
53. Switch from one workbook to another

Data Analysis & Visualization
54. Pivot Table (PT) & Pivot Chart
• What is Pivot Table
• How to insert Pivot Table
• Pivot Table Fields Section
• Pivot Table Areas Section (Rows, Columns, Values & Filter)
• How to make your PT dynamic without changing the data source every time
• How to insert Slicers in Pivot Table
• Insert Timeline in Pivot Table
• Difference between Slicer and Report Filter
• How to Connect Multiple Pivot Tables using Slicers
• How to insert Calculated Field/Formulas in PT
• Pivot Table Value field settings
• Grouping/Ungrouping of fields
• Creating Pivot Chart
• Sub Total settings
• Grand Totals settings
• Report Layout Options
• Pivot Table Style Options etc.

55. Charts
• What is Chart?
• Charts Terminology
• Types of Charts – Column, Line, Bar, Pie, Area, Combo charts etc.
• Creating Static Chart
• Chart Design
• Dynamic Chart using formulas
• Dynamic chart using Named Range

56. Dashboards
• What is Dashboard?
• Creating Dashboard using Pivot Tables and Charts
Project8: Creating Dashboard using real time data
Project9: 25 complex tasks using advanced features of Excel

Best Excel Course at Air Inv IQ technologies

We offer the best Excel courses for beginners and advanced learners. Our comprehensive Excel courses are designed to equip you with the skills needed for data analysis, financial modeling, and more.

Why Choose Our Excel Courses?
• Comprehensive Curriculum: Our Excel courses cover both basic and advanced topics, including data entry, formulae, pivot tables, and advanced data analysis techniques.
• Expert Trainers: Learn from industry professionals with extensive experience in Excel, ensuring you gain valuable insights and hands-on experience.
• Flexible Learning Options: We offer both online and offline courses, making it convenient for you to find the best Excel courses for beginners and advanced learners near you.

Course Highlights
• Basic Excel Course: Start with the fundamentals of Excel, perfect for beginners who want to build a solid foundation.
• Advanced Excel Course Bangalore: Dive deeper into complex functions, data analysis, and financial modeling with our advanced Excel course in Bangalore.
• Microsoft Excel Advanced Course: Master advanced Excel techniques and tools that are essential for professional data analysis and business applications.
• Best Excel Courses for Data Analysis: Learn how to use Excel for comprehensive data analysis, enhancing your skills and career opportunities.
• Online Excel Courses for Beginners: Flexible online learning options to fit your schedule, providing access to top-quality training from anywhere.

Best Excel Courses for Beginners and Advanced Learners
Air Inv IQ technologies is recognized as the leading provider of Excel training in Bangalore. Whether you’re a beginner or looking to enhance your data analysis skills, our Excel courses are tailored to meet your needs.

Enroll Today
Join us for the best Excel courses in Bangalore. Visit our course page to learn more and enroll today. Experience exceptional Excel training and achieve your career goals with us.