Advance Excel

01 Microsoft Excel Fundamentals

1. Launching Excel

2. Microsoft Excel Start-up Screen

3. Introduction to the Excel Interface

4. Customizing the Excel Quick Access Toolbar

5. More on the Excel Interface

6. Understanding the Structure of an Excel Worksheet

7. Saving an Excel Document

8. Opening an Existing Excel Document

9. Common Excel Shortcut Keys

10. Font formats, Alignment, Borders, etc

11. Basic conditional formatting

02 Entering and Editing Text and Formulas

1. Entering Text to Create Spreadsheet Titles

2. Working with Numeric Data in Excel

3. Entering Date Values in Excel

4. Working with Cell References

5. Creating Basic Formulas in Excel

6. Relative Versus Absolute Cell References in Formulas

7. Understanding the Order of Operation

03 Working with Basic Excel Functions

1. The structure of an Excel Function

2. Working with the SUM() Function

3. Working with the MIN() and MAX() Functions

4. Working with the AVERAGE() Function

5. Working with the COUNT() Function

6. Adjacent Cells Error in Excel Calculations

7. Using the AutoSum Command

8. Excel's AutoSum Shortcut Key

9. Using the AutoFill Command to Copy Formulas

04 Modifying an Excel Worksheet

1. Moving and Copying Data in an Excel Worksheet

2. Inserting and Deleting Rows and Columns

3. Changing the Width and Height of Cells

4. Hiding and Unhiding Excel Rows and Columns

5. Renaming an Excel Worksheet

6. Deleting an Excel Worksheet

7. Moving and Copying an Excel Worksheet

05 Formatting Data in an Excel Worksheet

1. Working with Font Formatting Commands

2. Changing the Background Colour of a Cell

3. Adding Borders to Cells

4. Formatting Data as Currency Values

5. Formatting Percentages

6. Using Excel's Format Painter

7. Creating Styles to Format Data

8. Merging and Centring Cells

9. Using Conditional Formatting

06 Protecting Excel

1. File Level Protection

2. Workbook, Worksheet Protection

3. Add New Range fro protection

07 Text Functions

1. Upper, Lower, Proper

2. Left, Right, Mid

3. Trim, Len, Exact

4. Find, Search, Text, Number, Code, Substitute

5. Concatenate, Replace, REPT

08 Mathematical Functions

1. Sum, Sumif, Sumifs

2. Average, Averageif, Averageifs

3. Count, Countif, Countifs,

4. Average, Averageif, Averageifs

5. Max, Min

6. If, ifs

7. and, or, not and with if, or with if, not with if

09 Date & Time Functions

1. Today, Now

2. Day, Month, Year, YearFrac

3. Date, Date if

4. EOMonth, EDate, Weekday, WeekNUM, Workday

5. Hour, Minute, Second

9-A Financial Functions in Excel

1. Future Value (FV)

2. FVSCHEDULE

3. Present Value (PV)

4. Net Present Value (NPV)

5. XNPV

6. PMT

7. PPMT

8. Internal Rate of Return (IRR)

9. Modified Internal Rate of Return (MIRR)

10. XIRR

11. NPER

12. RATE

13. EFFECT

14. NOMINAL

15. SLN

10 Advance Paste Special options

1. Paste Formulas, Paste Formats

2. Paste Validations

3. Transpose Table

4. Plan Paste

11 Slicer, Charts and Graphs

1. Working with Charts

2. Using SLICERS, Filter data with Slicers 

12 Pivot Tables

1. Creating Simple Pivot Tables 

2. Basic and Advanced Value Field Setting 

3. Classic Pivot table 

4. Choosing Field 

5. Filtering PivotTables 

6. Modifying PivotTable Data 

7. Grouping based on numbers and Dates 

8. Calculated Field & Calculated Items 

13 Printing Worksheet

1. using of Page Break Preview

2. Setting of Margin, Orientation & Page Size

3. Print Area, Page Break and Print Titles

4. Scale to Fit, Gridlines , Heading

14 Data

1. Import & Export Data

2. Sorting and Filtering

3. Text to Column, FlashFill, Remove Duplicate

4. Data Validation

5. Consolidate the Data

6. What-if-Analysis

7. Scenario Manager

8. Goel Seek

9. Data Table

10. Forecast Sheet

11. Outline

12. Group

13. UnGroup

14. Sub Total

15. Solver Add-in​

15 Lookup Functions

1. Vlookup / HLookup 

2. Index and Match 

3. Creating Smooth User Interface Using Lookup 

4. Nested Vlookup 

5. Reverse Lookup using Choose Function 

6. Worksheet linking using Indirect 

7. Vlookup with Helper Column

16 VBA Macro

1. What Is VBA?

2. What Can You Do with VBA?

3. Recording a Macro

4. Procedure and functions in VBA