By- Faiyaz M Khairaz www.compufield.com / Trainer Faiyaz Khairaz ( - PowerPoint PPT Presentation

Duration – 2 Days By- Faiyaz M Khairaz www.compufield.com / Trainer Faiyaz Khairaz ( +91 9819006132 )

1. Overview of Basics  What is Excel  What is the Structure of Excel  How to enter values in a call, sheet and access those values from a different sheet & workbook www.compufield.com / Trainer Faiyaz Khairaz ( +91 9819006132 )

2. Relative & Absolute Reference  When would you use Relative / Absolute or Mixed Reference www.compufield.com / Trainer Faiyaz Khairaz ( +91 9819006132 )

2. Relative & Absolute Reference  When would you use Relative / Absolute or Mixed Reference In this example, we use the previous sheet, but this time, we change the figures of HRA , DA & TA on a different sheet, and the changes here would automatically change the master fil www.compufield.com / Trainer Faiyaz Khairaz ( +91 9819006132 )

Using Name Range  When we need to access a Range of Cells repeatedly, instead of selecting it again & again, we use a NAME Range a) We need the minimum, maximum, average of the Range of Cells (which are in total) Sum(sumSalary) b) Instead or using =Sum(h5:h18) We would give a name to the Range of cells, for example sumSalary www.compufield.com / Trainer Faiyaz Khairaz ( +91 9819006132 )

3. If Condition  Explanation  If, And, Or, Compound, Nested IF www.compufield.com / Trainer Faiyaz Khairaz ( +91 9819006132 )

Assignment using If Condition & Reference Questions to Complete www.compufield.com / Trainer Faiyaz Khairaz ( +91 9819006132 )

4. VlookUP (Exact) www.compufield.com / Trainer Faiyaz Khairaz ( +91 9819006132 )

VlookUP (Approx) www.compufield.com / Trainer Faiyaz Khairaz ( +91 9819006132 )

Vlookup (Nested)  First, Given the State, we need to find the Region  After Finding the region, depending on the number of dependents, we need to search the TAX Rate www.compufield.com / Trainer Faiyaz Khairaz ( +91 9819006132 )

Match & Index  Limitation of VLookUP, the left most column needs to be the LookUP Value. Solution, Match + Index. We take multiple example’s where the Left Column is not the LookUP Value and show examples for Match + Index www.compufield.com / Trainer Faiyaz Khairaz ( +91 9819006132 )

Match & Index Given the Social Security Number, Find the Name of the Person www.compufield.com / Trainer Faiyaz Khairaz ( +91 9819006132 )

5. Text Functions  Where we cover the most frequently used TEXT Functions like  =concatenate  =left  =rigt  =mid  =isblank  =iserror and more.. www.compufield.com / Trainer Faiyaz Khairaz ( +91 9819006132 )

6. Power Functions  CountIF  SumIF  CountIFS  SumIFS For Example How many People are working in the South Region who have made more than 150 Units of Sales www.compufield.com / Trainer Faiyaz Khairaz ( +91 9819006132 )

7.Conditional Formatting www.compufield.com / Trainer Faiyaz Khairaz ( +91 9819006132 )

8.Consolidation We have Data from multiple Sheets, and we need to Consolidate www.compufield.com / Trainer Faiyaz Khairaz ( +91 9819006132 )

9. Validation How to Validate Cells, so that we can restrict the type of data is entered in the cell www.compufield.com / Trainer Faiyaz Khairaz ( +91 9819006132 )

8.Subtotal www.compufield.com / Trainer Faiyaz Khairaz ( +91 9819006132 )

10 Pivot Table www.compufield.com / Trainer Faiyaz Khairaz ( +91 9819006132 )

11. Charts  Different Types of Charts  Charts with Secondary Axes  Multiple Tyle Charts  (Bar and Line in the same Chart) www.compufield.com / Trainer Faiyaz Khairaz ( +91 9819006132 )

12. Protection  How to Protect the Sheet  How to Protect the Workbook  Hide the Formula’s www.compufield.com / Trainer Faiyaz Khairaz ( +91 9819006132 )