• 3.5 (3k reviews)

₹ 4999

Get Course Get Course

• Level Beginner
• Duration 1.5 Month

Course Content

1. Logical Functions
   1. Advanced use of IF, AND, OR, and NOT functions
   2. Nested IF statements for multiple conditions
   3. Using the SWITCH and IFS functions for more complex logic
2. Lookup and Reference Functions
   1. Advanced VLOOKUP and HLOOKUP with approximate and exact matches
   2. INDEX and MATCH for more flexible lookups
   3. Using OFFSET for dynamic ranges
   4. Using INDIRECT to reference other workbooks
3. Text Functions
   1. Text manipulation using LEFT, RIGHT, MID, LEN, FIND, and REPLACE
   2. Combining and splitting text with CONCATENATE, TEXTJOIN, and TEXTSPLIT
   3. Using the TRIM, UPPER, LOWER, PROPER functions for text formatting
4. Date and Time Functions
   1. Using TODAY(), NOW(), DATE(), YEAR(), MONTH(), DAY(), WEEKDAY(), and DATEDIF
   2. Using NETWORKDAYS, NETWORKDAYS.INTL and WORKDAY, WORKDAY.INTL
   3. Time functions: HOUR(), MINUTE(), SECOND(), TIME(), and TEXT
5. Math and Statistical Functions
   1. SUMIFS, COUNTIFS, AVERAGEIFS for multiple criteria
   2. Using the RANK, PERCENTILE, MEDIAN, and MODE functions
   3. Advanced rounding functions: ROUNDUP, ROUNDDOWN, CEILING, FLOOR
6. Data Consolidation
   1. Using Consolidate to combine data from multiple worksheets
   2. Understanding 3D referencing for combining data
7. PivotTables and PivotCharts
   1. Advanced PivotTable techniques: grouping data, multiple consolidation ranges
   2. Using calculated fields and calculated items
   3. Creating and modifying PivotCharts
   4. Using slicers and timelines for data filtering in PivotTables
   5. Dynamic PivotTable reports using the GETPIVOTDATA function
8. What-If Analysis
   1. Scenario Manager: creating and comparing multiple scenarios
   2. Goal Seek for finding target values in formulas
   3. Data Tables: single and two-variable data tables for sensitivity analysis
   4. Using Solver for optimization problems
9. Advanced Charting Techniques
   1. Creating combination charts (e.g., line and column charts together)
   2. Using secondary axes for displaying multiple data types
   3. Customizing chart elements (titles, data labels, legends)
   4. Creating dynamic charts using named ranges and formulas
   5. Using sparklines to show trends in data
10. Data Validation
    1. Creating complex validation rules using custom formulas
    2. Using dependent drop-down lists for dynamic data entry
    3. Creating error messages and input messages for better user input
11. Named Ranges and Dynamic Ranges
    1. Using named ranges to simplify formulas and improve workbook navigation
    2. Creating dynamic named ranges using OFFSET and COUNTA
12. Keyboard Shortcuts and Time-Saving Techniques
    1. Key Excel shortcuts for power users
    2. Efficient ways to navigate large data sets
    3. Quick access toolbar and custom ribbon creation
13. Working with Large Data Sets
    1. Sorting and filtering large datasets efficiently
    2. Removing duplicates and managing data consistency
    3. Using Advanced Filter to extract data based on complex criteria
    4. Using Text-to-Columns for splitting data in cells
    5. Handling and working with external data sources (importing CSV, text, and database data)
14. Introduction to VBA
    1. What is VBA and why use it?
    2. The VBA editor: The Interface, Modules, and Macros
    3. Writing basic VBA macros for automating repetitive tasks
15. Using VBA to Automate Excel
    1. Recording and editing macros
    2. Writing custom VBA code to automate data entry, formatting, and calculations
    3. Debugging VBA code and error handling
16. Advanced VBA Techniques
    1. Working with loops, conditional statements, and functions
    2. Creating custom Excel forms (UserForms)
17. Collaborating on Workbooks
    1. Sharing workbooks for collaboration in real-time (OneDrive, SharePoint)
    2. Protecting and securing Excel workbooks and worksheets with passwords
    3. Tracking and reviewing changes using Track Changes and comments
    4. Using Excel’s “Protect Sheet” and “Allow Edit Ranges” features for controlled collaboration
18. Version Control and Document Recovery
    1. Using Excel’s version history to restore previous versions of workbooks
    2. Creating backups and managing file versions effectively
19. Budgeting and Forecasting
    1. Creating and managing financial budgets in Excel
    2. Using Excel for cash flow projections and forecasting future trends
20. Advanced Formatting
    1. Custom number formats
    2. Formatting large data sets efficiently
    3. Advanced cell referencing (absolute, relative, mixed)
21. Various types of Reports
    1. Attendance Sheet
    2. Salary slip
    3. Report card
    4. Stock Management
    5. Profit & Loss Report
    6. Bank Statement