• No products in the cart.

Advanced Microsoft Excel Training

5( 8428 REVIEWS )
8428 STUDENTS

This certification is awarded by Berkeley – a US based corporate training body which is an established name in training and coaching especially in Accounting, Finance and Financial Reporting. This course covers how to work more efficiently with workbooks and worksheets, enhance the appearance of data and worksheets, perform analysis using functions and use the sorting and filtering features to manage and analyse data.

 

Learning outcomes:

» Discover methods to dramatically reduce the time you spend on spread sheets
» Develop practical solutions to your business problems through superior spread sheet design
» Upgrade your spreadsheet skills with advanced data management techniques
» Use macros more efficiently
» Consolidate worksheets from different sources into one workbook
» Develop efficient techniques for using charts

 

WHO CAN DO?

• The qualified finance, accounting and audit professionals in practice are eligible for this qualification.
CEO / Management / C- Level Staff / Accountants / Senior and junior accountants / Accounting and finance professionals / Chief accountants / Accounting managers and supervisors / Financial controllers / Accounting and finance professionals
• This course is intended for current Microsoft Excel users who wish to extend their knowledge and skills beyond building simple workbooks.
• Students should have already completed Excel Level 1 course or know how to create and save an excel workbook, enter simple formulas and print and chart data.

 

OBJECTIVES

Upon successful completion of this course, students will be able to:
• Use the fill operations available to fill a data series
• Understand and use formula cell referenc to create more complex formulas
• Use a range of logical functions
• Apply conditional formatting to ranges in a worksheet
• Create and use labels in a workbook
• Work with various elements of a worksheet
• Use a range of techniques to work with work sheets
• Use a range of find and replace techniques
• Apply a variety of page setup techniques
• Sort data in a list in a worksheet
• Filter data in a table
• Create effective charts in Microsoft Excel
• Use a range of techniques to enhance charts
• Apply formatting techniques to text on charts

 

WHAT IS THE COURSE OUTLINE?

Unit 1: Conditional and Formatting
• Formatting Cells Containing Values
• Clearing Conditional Formatting
• More Cell Formatting Options
• Top & Bottom Ten Items
• Working With Data Bars
• Working WithColour Scales
• Working With Icon Sets
Sparkline (New feature)
• Creating Sparklines
• Editing Sparklines

Unit 2: Sorting Data
• Performing An Alphabetical Sort
• Performing A Numerical Sort
• Sorting On More Than One Column
• Sorting By Vertically & Horizontally

Unit 3: Filtering Data
• Understanding Filtering
• Applying And Using A Filter
• Clearing A Filter
• Multiple Value Filters
• Using Wildcards

Unit 4: Filling Data
• Understanding Filling
• Filling A Series

Unit 5: PivotTables
• Understanding Pivot Tables
• Creating A PivotTable Shell
• Dropping Fields Into A PivotTable
• Filtering A PivotTable Report
• Clearing A Report Filter
• Calculation in pivot
• Formatting A PivotTable Report
• Dynamic range for pivot
New Feature
• Understanding Slicers
• Creating Slicers

 
Unit 6: Dynamic table (New feature)
• Converting data into table
• Automation calculation in table
• Converting table to normal range
• Using table for creating dynamic charts

Unit 7: Charting Techniques
• Adding A Chart Title
• Adding Axes Titles
• Positioning The Legend
• Showing Data Labels
• Showing A Data Table
• Modifying The Axes
• Showing Gridlines
• Creating combination chart
• Creating 2 Axis chart

Unit 8: Creating Charts
• Choosing The Chart Type
• Creating A New Chart
• Working With An Embedded Chart
• Resizing A Chart
• Changing The Chart Type

Unit 9: Advance Validation
• Creating drop down in cells
• Restriction values from list only
• Creating error message

Unit 10: Text options
• Text to Column
• SubTotal
• Sharing workbook
• Paste Special

Unit 11: Macros
• Recording & Running Macros
• Creating button to run the macro
• Assigning shortcut to macro
• Deleting Macros
• Relative reference in Macros

 
Unit 12: File / Data Security
• Open & Modify file password
• Sheet protection
• Protecting your sheet from deletion
• Protecting few cell, rows or cols
• Protecting your data from copying

Unit 13: Formulas
• Count, CountA, CountIF&CountBlank
• Sum, SumIF&SumIFs
• Networkdays
• Networkdays International (For ver 2010)
• Today & Now function
• Trim (Removing unwanted spaces)
• Concatenate (Combining columns)

Unit 14: Formula Referencing
• Absolute and Relative Referencing
• Problems with Relative Formulas
• Creating Absolute References

Unit 15: Logical Functions
• Understanding Logical Functions
• Using IF To Display Text
• Using IF To Calculate Values
• Nesting IF Functions

Unit 16: Lookup Functions
1) Normal Vlookup&Hlookup
2) Vlookup with IFError
3) Vlookup from Multiple Sheets using  IFERROR
4) Pulling two columns data in one col  (first & last name) with Vlookup & Concatenate
5) Using IF Condition in Vlookup
6) Using Multiple criteria in Vlookup

 

 

CHOOSE THE TRAINING OPTION:

LIVE ONLINE
TRAINING

Live online lectures where you interact with the instructor via webinar.

 

ON-CAMPUS
TRAINING

Live lectures at Berkeleyme training center where you interact with the instructor.

 

ONE TO ONE
TRAINING

Individually focused and highly interactive training with you and exclusive trainer for you.

 
 

Course Curriculum

Unit 1: Conditional Formatting 00:00:00
Unit 2: Sorting Data 00:00:00
Unit 3: Filtering Data 00:00:00
Unit 4: Filling Data 00:00:00
Unit 5: PivotTables 00:00:00
Unit 6: Dynamic table (New feature) 00:00:00
Unit 7: Charting Techniques 00:00:00
Unit 8: Creating Charts 00:00:00
Unit 9: Advance Validation 00:00:00
Unit 10: Text options 00:00:00
Unit 11: Macros 00:00:00
Unit 12: File / Data Security 00:00:00
Unit 13: Formulas 00:00:00
Unit 14: Formula Referencing 00:00:00
Unit 15: Logical Functions 00:00:00
Unit 16: Lookup Functions 00:00:00

Course Reviews

5

5
8428 ratings
  • 5 stars0
  • 4 stars0
  • 3 stars0
  • 2 stars0
  • 1 stars0

No Reviews found for this course.

TAKE THIS COURSE
  • Number of Units16
  • 0 Months

Instructors

8428 STUDENTS ENROLLED

    Currency Switcher

    YOU MAY INTEREST IN:

    MIGRATE TO CANADA:

    Read More..

    UNITED ARAB EMIRATES - VAT CONSULTANCY SERVICES:

    Read More..

    JOIN AS TRAINER:

    Apply Now

    Top Rated Courses:

    Copyright © 2020 - Berkeleyme School of Management & Sciences. All rights reserved.
    X