Outline:

Day 1

1. Introduction & Benefits of learning EXCEL
2. DATA ANALYSIS WITH PIVOT TABLE

• Creating Pivot Table
• Different types of calculations with “Value Field Setting”
• Pivot Charting
• Inserting Slicers

3. Lookup function (VLOOKUP)

• ROW, ROWS, COLUMN, COLUNMS
• VLOOKUP, HLOOKUP
• INDIRECT
• TRANSPOSE

4. Date & time functions

• NOW, TODAY
• DATE, DATEVALUE, DATEDIF
• EDATE, EMONTH
• TEXT

5. Finding & Managing Duplicates

Day 2

1. GRAPHS & CHARTS

• Line graph
• Bar graph
• Pie chart
• Combo graph

2. Mathematical functions

• FLOOR, CEILING, ROUND, ROUNDUP, ROUNDDOWN
• RAND, RANDBETWEEN
• SUBTOTAL
• SUM, SUMIF, SUMIFS

3. Text functions

• CONCATENATE and & (AMPERSAND)
• FIND
• LEFT, RIGHT, MID
• PROPER, LEN, REPT
• TRIM
• EXACT
• TEXTJOIN (Special)

4. Conditional Formatting (intermediate)

• Highlight Cell Rules
• Top/Bottom Rules
• Data Bars/ Color Scales/ Icon Set

Day 3

1. Statistical Functions

• AVERAGE, AVERAGEA, AVERAGEIF, AVERAGEIFS
• COUNT, COUNTA, COUNTIF, COUNTIFS, COUNTBLANK
• MAX, MIN

2. Conditional Formatting (advance)

• Creating rules for conditional Formatting
• Creating notifications or reminders in Excel

3. Creating drop down list
4. Logical functions

• AND, NOT, OR
• IFERROR, ISERROR
• IF, IFS

Day 4

1. Data Validation & Drop down list (advance)

• Prevent Duplicate Entries
• Creating dependent drop down list

2. Some Excel tips (Better to know)

• Showing all 255 character set in excel sheet
• Text alignment, wrap, merge & center option
• Showing all cells that contain numbers/text/formula/conditional formatting/data validation (Find & Select)
• Viewing all formula used in a sheet using shortcut (CTRL+~ tilde)
• Adding comments with image (with editing)
• Debugging formula by using “evaluate formula”
• Creating range names and its uses
• Hiding zero values/any text using “formatting cells” option
• Converting formulas to values & Why to paste as values
• Copying formulas only
• Freezing rows and or columns
• Hiding Formula Bar
• Hiding Grid Lines
• Quickly SUM a range of cells

3. Protecting Sheets & Workbooks
4. Preparing a complete Excel report/ presentation
5. Final words