ADVANCED EXCEL

Introduction to Microsoft Excel & its properties

• Workbook properties
• creating a workbook, saving a workbook, renaming a workbook
• Worksheet properties, add, delete
• Name a worksheet, color a worksheet tab
• move & copy worksheet, select all

Concept of Row & Column

• Insert row & column, row height, column width
• Rows, Column, Columns, Hide, unhide/ Cut
• Copy / Delete, Clear content / Paste, Paste Special

Properties of Cells

• workbooks
• worksheets
• Paste special
• Paste Special Transpose

Formatting cells

• Number, Alignment, Font formats
• Border, Fill, Protection / Use of format painter
• Conditional formatting/ Advanced Conditional Formatting
• Print worksheet / Printing properties

Cell Reference

Data Sorting and Filtering

Date Function

• Now/Extract Date, year, Month, Week no, Week day from given date
• Text Function-Left, Right, Mid / Len / Proper, Upper,
• Lower /Concatenate / Find, Search, Substitute, Replace, Round, RoundDown, Roundup

Use Of Formula, Lock the required cells

Count

• CountA, CountBlank, CountIf, CountIfs
• Min, Max / Sum, Sumif, Sumifs
• Subtotal / Average, Averagea, AverageIf, AverageIfs

Double Freeze, Chart validation, Pivot Table, Pivot Chart

Drop down creation

VLOOKUP & HLOOKUP

Index and Match Linking Cells & Sheets

Slicer| Name Manager | What If Analysis | Consolidation

Preparing data for Analysis | Report Visualisation Techniques | Advanced Filter | Record Macro