MS Excel Basic and Advance

MS Excel Basic and Advance

MS Excel Basic and Advance

Version 2007 onwards
Part 1  - Understanding Excel      

What is a Spreadsheet?
1 .Excel Rows and Columns
2 .Enter text and numbers in a Cell
3 .How to Edit text in a Cell
4. How to Centre text and numbers
5 .Font Formatting in Excel
6 .How to change the colour of a Cell
7 .How to Save your work in Excel
8 .Currency Symbols in Excel
9. How to Merge Cells

Part 2 - Complex  Spreadsheet  

1. How to use AutoFill in Excel
2. Entering Simple Addition Formula
3. The SUM Function in Excel
4. The SUM Function Continued
5. Copy and Paste
6. How to use Paste Special
7.How to Multiply in Excel
8. Finishing your spreadsheet for this section
9. How to Add a Comment to a Cell                                                                                                                                                                  
Part 3  - Microsoft Excel Charts                                                                                                                                                                               
1. How to Sort Data in Excel
2 .Create an Excel Chart
3 .Move and Resize your Chart
4 .Chart Styles and Layouts
5 .Chart Titles and Series Titles
6 .Chart Layout Panel in Excel
7. The Format Chart Panel
8 .Create a Pie Chart in Excel
9 .Add Labels to a Pie Chart
10. Format Pie Chart Segments
11.Create a 2D Line Chart in Excel
12.Format your Axis Titles
13 .Predict the future with a Trendline Chart
14 .Sparkline Charts                                                                                                                                                                                             
Part 4  - Formulas in Excel                                                                                                                                                                                    
1.The SUM Function
2.How to Multiply in Excel
3.Subtract and Divide
4. Combining the Arithmetic Operators
5. A Budget Spreadsheet                                                                                                                                                                                   
Part 5  - Functions in Excel                                                                                                                                                                                
1. The Average Function
2. The Date Function
3. Time Functions in Excel
4. A TimeTable Project
5. Financial Functions

Part 6  - Conditional Logic in Excel                                                                                                                                                                      
1 The IF Function
2 Conditional Formatting in Excel
3 CountIF
4 SumIF

Part 7 - Processing Data in Excel                                                                                                                                                                         
1.Data Tables in Excel
2.A Second Data Table
3.Excel Scenarios
4.Absolute Cell References
5.Name  Ranges in Excel
6.Create a Custom Name in Excel
7. Excel Pivot Tables
8. The LOOKUP Function
9. The VLOOKUP Function in Excel
10. Searching with MATCH and INDEX
11.Create a Excel Business Invoice

Part 8 - Advanced Excel                                                                                                                                                                                          
1. How to Create an Excel Template
2.Data Forms in Excel
3. Drop Down Lists in Excel
4. Add your own Error Messages
5. Array Formulas Intermediate Excel
6. Frequency Distribution Intermediate Excel
7. Excel and Web Integration
8. Hyperlinks in Excel
9. Object Linking and Embedding
10. Insert Drawing Objects

11. Customizing common options in Excel
12. Absolute and relative cells
13. Protecting and un-protecting worksheets and cells- Using logical functions (AND, OR, NOT)
       VlookUP       with Exact Match, Approximate Match
14. Nested VlookUP with Exact Match
15. VlookUP with Tables, Dynamic Ranges
16. Nested VlookUP with Exact Match
17. Using VLookUP to consolidate Data from Multiple Sheets ~ Specifying a valid range of values for a cell
18. Specifying a list of valid values for a cell
19. Specifying custom validations based on formula for a cell
        -Designing the structure of a template
20. Using templates for standardization of worksheets~ Sorting tables
21. Using multiple-level sorting
22. Using custom sorting     
23. Filtering data for selected view (AutoFilter)
24. Using advanced filter options

Part 9 - Working with Reports  

1.    Creating subtotals
2.    Multiple-level subtotals
3.    Creating Pivot tables
4.    Formatting and customizing Pivot tables
5.    Using advanced options of Pivot tables
6.    Pivot charts
7.    Consolidating data from multiple sheets and files using Pivot tables
8.    Using external data sources
9.    Using data consolidation feature to consolidate data
10.    Viewing Subtotal under Pivot                                                                                                                                                                     Using auto formatting option for worksheets
11.    Using conditional formatting option for rows, columns and cells  

Part 10 - Charts

1.    Using Charts
2.    Formatting Charts
3.    Using 3D Graphs
4.    Using Bar and Line Chart together
5.    Using Secondary Axis in Graphs
6.    Sharing Charts with PowerPoint / MS Word, Dynamically
7.    (Data Modified in Excel, Chart would automatically get updated) 

Part 11 - WhatIf Analysis

1.    Goal Seek
2.    Data Tables
3.    Scenario Manager 

Part 12 - New Features Of Excel

1.  Sparklines, Inline Charts, data Charts
2.  Overview of all the new features


 

 

 

  • Start: Aug. 24, 2020
  • Package Duration: 20 Days
  • Seats Available: 80
  • Total Classes: 20
  • Time: 6:30 p.m. - 7:30 p.m.