About

Advanced Excel

Excel Introduction

An overview of the screen, navigation and basic spreadsheet concepts.

Various selection techniques

Shortcut keys

Customizing Excel

Customizing the ribbon

Using and customizing autocorrect

Changing’s Excel’s Default options

Using Basic Functions

Functions like – Sum, Average, Max, Min, Count

Absolute, mixed and relative referencing

Format and proofing

Customizing the ribbon

Using and customizing autocorrect

Changing’s Excel’s Default options

Excel Introduction

Formatting cells with Number formats, Font formats, Alignment, Borders etc.

Basic Conditional formatting

Advance Conditional formatting

Formulas in Excel

Arithmetic Functions

Logical Functions

Date and Time Functions

Text Functions

Financial Functions

Statistical Functions

Lookup and Reference Functions

Formula Auditing

Text Functions in Excel

Left, Right and Mid Join Strings using ‘&’ Operator

Combine Strings using Concatenate Function

Separate Strings using Find and Search Function

Count length of string using Len Function

Remove Spaces using Trim or Substitute

Remove line breaks using Clean Function

Compare strings using Exact

Function or ‘=’ operator

Replace and Substitute Function

Lower, Upper & Proper Function

Conditional and Logical Functions in Excel

The IF Function

IF Function with comparison operators

Multiple IF Function within single IF Function

IF Function with AND Function

IF Function with OR Function

IF Function with NOT Function

IFs Function

Conditional Formatting in Excel

Lookup Functions in Excel

VLookup Function

HLookup Function

MATCH and INDEX Function

Reverse Lookup with CHOOSE function

Left Lookup

Reverse Lookup with MATCH and INDEX Functions

Double Lookup

Two column Lookup

Exact Match

Approximate Match

Lookup Tricks

Date and Time Functions in Excel

Today’s date

Current date with Time

Year, Month, Day with Date Function

Hour, Minute, Second with Time Function

Datedif function

Calculate Age in Excel

Prepare Time Sheet

Calculate Days until Birthday

Calculate networking days in a month

Calculate Weeks in year

Find Weekday, Weeknum and last day of month

Dynamic Date Vs Static Date

Quarterly Report generation

Mathematical & Statistical Functions in Excel

Arithmetic Operators in Excel

Calculate Total with one or many conditions

Calculate Average with one or many conditions

RoundUp( ) and RoundDown( ) Functions

FLOOR( ) and CEILING( ) Functions

Find Square Root in Excel

Find Factorial in Excel

Print Integer value using INT()

Print absolute value using ABS()

Count Blank or Non-Blank Cells

Count Characters and Words in a String

Count Cells having text

CountIF and CountIFs

SumIF and SumIFs

AverageIF and AverageIFS

Find Minimum and second minimum value using Small()

Find Maximum and second maximum value using Large()

Financial Functions in Excel

PMT Function in Excel

Calculate the Rate of Interest in Excel

Calculate present value & Future value

Calculate Investment and Annuity in Excel

Error Handling in Excel

Details about Excel errors

If Error() for error handling

Is Error() for error handling

Use of Aggregate Function for error handling

Circular reference

Formula Auditing

Protecting Excel

File level protection

Workbook, Worksheet Protection

Advanced Paste Special techniques

Paste Formats and images

Paste validation

Transpose Tables

Arrays Functions

Use of Arrays formulas

Basic examples of array

Arrays with if, len and midfuction formulas

Arrays with lookup functions

Advanced use of Formulas with Array

Charts & Slicers

Various charts i.e. Bar/Pie/Line

Using slicers, filter data with slicers

Manage Primary and Secondary Axis

Excel Dashboard

Planning a Dashboard

Adding Tables and charts to Dashboard

Adding Dynamic contents to Dashboard

Assignments & Projects in Excel

Calculate Electricity Bill

Data Cleaning

Prepare a Master Sheet with dashboard

Data Analysis in Excel

Sorting Data in Ascending or Descending order

Advanced Data Filtering Technique

Conditional Formatting

Data Validation in Excel

Data List creation in Excel

Dependent data list creation in Excel

Data Tables in Excel

Excel Scenarios

Goal Seek

Named Ranges in Excel

Dynamic Named Ranges

Create a Custom Name in Excel

Excel Pivot Tables

Excel Pivot Charts

Data Visualization using Charts

How to Sort Data in Excel Charts

Move and Resize your Chart

Chart Styles and Layouts

Chart Titles and Series Titles

Format your Axis Titles

How to Create an Excel Template

Data User Forms

Add your own Error Messages

Object Linking and Embedding

Insert Drawing Objects

Sorting & Filtering

Filtering on text, Numbers & Colors

Sorting options Advanced Filters on different criteria on Excel