際際滷

際際滷Share a Scribd company logo

Creating Dashboards in Excel - Courses in Advanced Excel Mumbai

Faiyaz Khairaz
Faiyaz Khairaz

This two-day Excel training covers a variety of essential Excel topics taught by trainer Faiyaz Khairaz. The training will cover basics of Excel including sheets, cells, and values; formulas with relative and absolute references; functions like IF, VLOOKUP, MATCH, and INDEX; text functions; conditional formatting; validation; pivot tables; charts; and protection. Real-world examples will be used to demonstrate topics like nested formulas, consolidation, subtotals, and charts with secondary axes.

1 of 21
Download to read offline
www.compufield.com / Trainer Faiyaz Khairaz ( +91 9819006132 ) 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 f
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) b) Instead or using =Sum(h5:h18) We would give a name to the Range of cells, for example sumSalary Sum(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 examples 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.ConsolidationWe 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 Formulas

More Related Content

Creating Dashboards in Excel - Courses in Advanced Excel Mumbai

  • 1. www.compufield.com / Trainer Faiyaz Khairaz ( +91 9819006132 ) Duration 2 Days By- Faiyaz M Khairaz
  • 2. 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
  • 3. www.compufield.com / Trainer Faiyaz Khairaz ( +91 9819006132 ) 2. Relative & Absolute Reference When would you use Relative / Absolute or Mixed Reference
  • 4. 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 f
  • 5. 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) b) Instead or using =Sum(h5:h18) We would give a name to the Range of cells, for example sumSalary Sum(sumSalary)
  • 6. www.compufield.com / Trainer Faiyaz Khairaz ( +91 9819006132 ) 3. If Condition Explanation If, And, Or, Compound, Nested IF
  • 7. www.compufield.com / Trainer Faiyaz Khairaz ( +91 9819006132 ) Assignment using If Condition & Reference Questions to Complete
  • 8. www.compufield.com / Trainer Faiyaz Khairaz ( +91 9819006132 ) 4. VlookUP (Exact)
  • 9. www.compufield.com / Trainer Faiyaz Khairaz ( +91 9819006132 ) VlookUP (Approx)
  • 10. 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
  • 11. 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 examples where the Left Column is not the LookUP Value and show examples for Match + Index
  • 12. www.compufield.com / Trainer Faiyaz Khairaz ( +91 9819006132 ) Match & Index Given the Social Security Number, Find the Name of the Person
  • 13. 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..
  • 14. 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
  • 15. www.compufield.com / Trainer Faiyaz Khairaz ( +91 9819006132 ) 7.Conditional Formatting
  • 16. www.compufield.com / Trainer Faiyaz Khairaz ( +91 9819006132 ) 8.ConsolidationWe have Data from multiple Sheets, and we need to Consolidate
  • 17. 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
  • 18. www.compufield.com / Trainer Faiyaz Khairaz ( +91 9819006132 ) 8.Subtotal
  • 19. www.compufield.com / Trainer Faiyaz Khairaz ( +91 9819006132 ) 10 Pivot Table
  • 20. 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)
  • 21. www.compufield.com / Trainer Faiyaz Khairaz ( +91 9819006132 ) 12. Protection How to Protect the Sheet How to Protect the Workbook Hide the Formulas