Advanced Excel

Excel Shortcut Key to improve productivity.

Useful Excel Formulas with example:

• VLOOKUP

• HLOOKUP

• MATCH

• INDEX

• INDIRECT

• OFFSET

• CHOOSE

• ADDRESS

• FIND

• LEN

• MID

• SUBSTITUTE

• TEXT

• TEXTJOIN

• TRIM

• DATE

• DATEVALUE

• EOMONTH

• NETWORKDAYS

• NOW

• TODAY

• AVERAGEIF

• COUNTIF

• COUNTIFS

• SUMIF

• SUMIFS

• SUMPRODUCT

Click here to see more formulas

Basic Charts

• Line Chart

• Column Chart

• Bar Chart

• Stacked Column Chart

• Stacked Bar Chart

• Pie Chart

• Doughnut Chart

• Area Chart

• Combo Chart

Sort and Filter in 6 Chapters with step by step example:

Sort:

• Chapter-1: Data Sorting

• Chapter-2: Sort by Color

Filter:

• Chapter-3: Data Filter

• Chapter-4: Text, Number and Date Filter

• Chapter-5: Filter by color

• Chapter-6: Advanced Filter

Pivot table in 15 Chapters with step by step example:

1. Insert a Pivot Table

2. Data Sorting in Pivot Table

3. Filters in Pivot Table

4.  Value Field Settings in Pivot Table

5.  Columns in Pivot Table

6.  Groups/Ungroup in Pivot Table

7.  Report Layouts In Pivot Table

8.  Subtotals and Grand Totals

9.  Pivot Charts

10.  Slicers in Pivot Table

11.  Timeline in Pivot Table

12.  Refresh Pivot and Change Data Source

13.  Calculated Fields/Items in Pivot Table

14.  Show Details in Pivot table

15.  Pivot Tables, Slicers and Timeline Styles

Conditional Formatting in 11 Chapters with step by step example:

Preset Rules-

• Chapter – 1: Highlight Cells Rules

• Chapter – 2: Top Bottom Rules

• Chapter – 3: Data Bars

• Chapter – 4: Color Scales

• Chapter – 5: Icon Sets

New Rules-

• Chapter-6: Format all cells based on their values

• Chapter-7: Format only cells that contain

• Chapter-8: Format only top or bottom ranked values

• Chapter-9: Format only values that are above or below average

• Chapter-10: Format only unique or duplicate values

• Chapter-11: Use a formula to determine which cells to format

Manage Rules-

• Chapter-12: Manage and Clear Rules