Advance Excel

Personalising Excel

• Changing Excel’s Default Options Using AutoCorrect and Customizing It Customizing the Ribbon

Understanding and Using Basic Functions

Using Functions – Sum, Average, Max, Min, Count, Counta Absolute, Mixed, and Relative Referencing

Text Functions

• Upper, Lower, Proper
• Left, Mid, Right
• Trim, Len, Exact
• Concatenate
• Find, Substitute

Arithmetic Functions

• SumIf, SumIfs CountIf, CountIfs AverageIf, AverageIfs

Proofing and Formatting

• Formatting Cells with Number formats, Font formats, Alignment, Borders, etc
• Basic conditional formatting

Protecting Excel-

Excel Security  File Level Protection Workbook, Worksheet Protection

Printing Workbooks

1. Setting Up Print Area
2. Customizing Headers & Footers
3. Designing the structure of a template
4. Print Titles –Repeat Rows / Columns

Advance Paste Special Techniques

1. Paste Formulas, Paste Formats
2. Transpose Tables
3. Paste Validations

Time and Date Functions

1. Today, Now
2. Date, Date if, DateAdd
3. Day, Month, Year
4. Month, Weekday

New in Excel 2013 / 2016 & 365

1. New Charts – Tree map & Waterfall
2. Combo Charts – Secondary Axis
3. Sunburst, Box, and whisker Charts
4. Using Power Map and Power View
5. Adding Slicers Tool in Pivot & Tables
6. Sparklines -Line, Column & Win/ Loss
7. Forecast Sheet
8. Smart Lookup and manage Store
9. New Controls in Pivot Table – Field, Items, and Sets
10. Using 3-D Map
11. Auto complete a data range and list
12. Various Time Lines in Pivot Table
13. Quick Analysis Tool

1. Filtering and Sorting

   1. Filtering on Text, Numbers & Colors
   2. Sorting Options
   3. Advanced Filters on 15-20 different criteria(s)

Printing Workbooks

1. Setting Up Print Area
2. Print Titles –Repeat Rows / Columns
3. Designing the structure of a template
4. Customizing Headers & Footers