Workshop Excel Advance

Microsoft Office Excel Advance Course Content

Level

Topic

Sub topic

DURATION

Day

100+ Advance formulas

Cell reference

a) Relative cell reference
b) Absolute cell reference

15 mins

Day 1

Essential logical functions

a)IF function
b)And function
c)OR function

30 mins

Day 1

Essential Lookup functions

a)Vlookup function
b)Hlookup function

c) Index and match function

60 mins

Day 1

Text functions

a)Join two or more cells with Concatenate function b)Use Len function to determine the number of characters in cell
c)Upper, Lower and Proper functions
d) Right, left, mid functions

e) Type, columns, rows functions

f) Find, search, exact, text and value

120 mins

Day 1

Statistical functions

a) Learn function which has inbuilt conditions Sumif, countif, Averageif,Minif, Max if, count, counta, coutblanks and etc

60 mins

Day 1

Date and Time functions

a)Understanding the various Date and time formats
b)Get current date and time with Todays, Date, Now, time, weekday, network days, week number, workday and workday.intl functions
c)Calculating the number of days between two dates and other date and time functions

60 mins

Day1

Advanced: Logical functions

a) Nested IF functions
b) IF Error function
c) IF NA function

120 mins

Day 1

Financial function


a) PMT, IPMT, RATE, NPER, PV and other function

60 mins

Day 1

Advance Keyboard shortcuts (will be covered in the above topics)

 

 

 

 

 

 

 

Level

Topic

Sub topic

DURATION

Day

Advance features

Pivot tables, Charts, Slicers and Timeline

About pivot tables?
a) Creating pivot table for the data set
b) Understanding the 5 fields in the pivot table
c) Calculation field area
d) Adding Pivot slicer and timeline

120 mins

Day 2

 Master Data validation

 Data validation settings, input message and error alert

 

30 mins

Day 2

Name ranges and dynamic name ranges

a) Create a name range and make it dynamic by using the Tables and Offset function

60 mins

Day 2

Data filter & Data sorting

a) Filter the data with the use of Auto filter
b) Custom filter based on the type of cell data

c) Sort data from ascending order to descending and custom sort

30 mins

Day 2

Remove extra spaces from Text or number

Remove all three types of spaces

a) Extra spaces

b) Non printable spaces

c) Non breaking spaces

60 mins

Day 2

Data splitting and removing duplicates

a) Text to columns
b) Identify the duplicates and identifying the duplicates for removal

30 mins

Day 2

Master Lookup functions

Advanced Nested Vlookup functions

60 mins

Day 2

Protecting the file

a) Protect the entire workbook, worksheet
b) Protect specific cells in a worksheet

c) Allow formula protection

 

30 mins

Day 2

Conditional formatting

Understanding how to format cells

a) Highlight cell rules b) Top and bottom c) Data bars d) Icon sets e) Colour scales f) New rule

60 mins

Day 2

Recovering unsaved files

Recover the files which are not saved at all and the last autosaved or saved files

30 mins

Day 2

Charts

Column, Bar, Line and Pie, XY, Radar, Surface, Bubble and Stock charts

60 mins

Day 2

Statistical analysis

a) Regression, forecast
b) What if Analysis: Solver, Goal seek, Scenario Manager

60 mins

Day 2

 

Instructor-led live online classes

OFFER ! Limited Period Offer! Pay now!

Early Bird Offer

  • 16

    Aug

    Sat-Sun (16 Hours)

    09:00AM-6:00PM

    • 73% off

      9,260

    2,501
73% Off till 12-Aug

Live Classes

On-time scheduled virtual classes with limited trainer-student ratio.

Expert Trainers

Best industry experts with years of online training experience.

Tailored Course

Based on your expertise and goals we design course for you.

Flexible Schedule

Learn at your schedule , time zone and convenience.

Get Certified

Receive a certificate to confirm the level of your skills in a professional context.

×

Get A Quote

Please fill your details. Fields marked with mandatory are mandatory

(Allowed types : PDF, DOC, XLS, JPEG, GIF, PNG )

Privacy Policy : We hate SPAM and promise to keep your email address safe.