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

▶
by faiyaz m khairaz
SMART_READER_LITE
LIVE PREVIEW

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

Jan 16, 2023 520 likes •742 views

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

slide-1
SLIDE 1

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

Duration – 2 Days By- Faiyaz M Khairaz

slide-2
SLIDE 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

slide-3
SLIDE 3

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

  • 2. Relative & Absolute Reference

 When would you use Relative / Absolute or Mixed

Reference

slide-4
SLIDE 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 fil

slide-5
SLIDE 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)

slide-6
SLIDE 6

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

  • 3. If Condition

 Explanation  If, And, Or, Compound, Nested IF

slide-7
SLIDE 7

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

Assignment using If Condition & Reference

Questions to Complete

slide-8
SLIDE 8

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

  • 4. VlookUP (Exact)
slide-9
SLIDE 9

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

VlookUP (Approx)

slide-10
SLIDE 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

slide-11
SLIDE 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 example’s where the Left Column is not the LookUP Value and show examples for Match + Index

slide-12
SLIDE 12

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

Match & Index

Given the Social Security Number, Find the Name of the Person

slide-13
SLIDE 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..

slide-14
SLIDE 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

slide-15
SLIDE 15

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

7.Conditional Formatting

slide-16
SLIDE 16

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

8.Consolidation

We have Data from multiple Sheets, and we need to Consolidate

slide-17
SLIDE 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

slide-18
SLIDE 18

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

8.Subtotal

slide-19
SLIDE 19

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

10 Pivot Table

slide-20
SLIDE 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)

slide-21
SLIDE 21

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

  • 12. Protection

 How to Protect the Sheet  How to Protect the Workbook  Hide the Formula’s