Advanced Excel course is designed for students/professionals who want to learn advanced features of Excel for generating smart reports with lots of time-saving which they can use it for other tasks. In addition, ADVANCED EXCEL course is also useful to people who want to clear the Microsoft certification for MS Excel (MOS) 2013/2016/2019. Learn how to set up an Excel sheet, perform calculations, use basic and advanced Excel formulas and create visual representations of data with charts and graphs. Learn how to quickly create different views of data with pivot tables and much more, directly from the Excel team at Microsoft.

Additional Excel training courses explore the more advanced statistics and data visualization features of the software. Learn about descriptive statistics, basic probability, random variables, Excel VBA, sampling and hypothesis testing. Perform data mashups and visualizations using data pulled from multiple sources. Some other elements you will learn on Microsoft Excel include conditional formatting, pivot tables, keyword shortcuts, and more. The advanced features courses are part of the Microsoft Professional Program Certificate in Data Science, a specialization designed to get learners on a path to a career in data science or related field. If you've been looking to learn Excel, you've come to the right place.

Objectives:

You will be able to:

• At the completion of the course, students will possess a solid comprehension of how to utilize the Office assistant feature in Excel.
• The course will cover the usage of formulas in Excel sheets and students will be able to derive knowledge on how to use them effectively.
• Students will learn how to use the date format with alpha and numeric options in Excel.
• The course will differentiate between the usage of functions and formulas in Excel sheets and will provide students with a clear understanding of their applications.
• Students will learn how to use macros to automate simple and complex tasks in Excel.
• The course will provide practical training to ensure that students gain hands-on experience in using Excel for various applications.
• The skills learned in this course will enable students to use Excel effectively to manage and analyze data in different contexts.
• Customize the Quick Access toolbar and use conditional formatting to customize the sheet after completing this course.
• Excel's efficiency may be improved by using sophisticated functions and formulae.
• To play with data, use the What if analysis command.
• To limit data entry, use data validation.
• To examine data, create Pivot tables and Pivot charts, and use a slicer and timeline to add extra interactivity.
• In Excel, use macros to automate repetitive activities.
• Passwords can be used to safeguard Excel files.

Course content

Microsoft Excel Basics

The Basics

• Creating a New Workbook
• Navigating in Excel
• Moving the Cell Pointer
• Using Excel Menus Using Excel Toolbars: Hiding, Displaying, and Moving Toolbars
• Entering Values in a Worksheet and Selecting a Cell Range
• Previewing and Printing a Worksheet
• Getting Help from the Office Assistant
• Saving a Workbook & Re-opening a saved workbook

Formatting a Worksheet

• Creating Headers, Footers, and Page Numbers
• Adjusting Page Margins and Orientation
• Adding Print Titles and Gridlines, rows to repeat at top of each page
• Formatting Fonts & Values
• Adjusting Row Height and Column Width
• Changing Cell Alignment
• Adding Borders
• Applying Colors and Patterns
• Using the Format Painter
• Using AutoFormat
• Merging Cells, Rotating Text, and using AutoFit
• Using AutoFill

Managing your workbooks

• Switching Between Sheets in a Workbook
• Inserting and Deleting Worksheets
• Renaming and Moving Worksheets
• Protecting a Workbook
• Hiding Columns, Rows and Sheets
• Splitting and Freezing a Window
• Inserting Page Breaks
• Advanced Printing Options

Editing a Workbook

• Entering Date Values and using AutoComplete
• Editing, Clearing, and Replacing Cell Contents
• Cutting, Copying, and Pasting Cells
• Moving and Copying Cells with Drag and Drop
• Collecting and Pasting Multiple Items
• Using the Paste Special Command
• Inserting and Deleting Cells, Rows, and Columns
• Using Undo, Redo, and Repeat
• Checking Your Spelling
• Finding and Replacing Information
• Inserting Cell Comments

Formulas

• Creating a basic Formula
• Calculating Value Totals with AutoSum
• Editing & Copying Formulas
• Fixing Errors in Your Formulas
• Formulas with Several Operators and Cell Ranges

Working with the Forms Menu

• Sorting, Subtotaling & Filtering Data
• Copy & Paste Filtered Records
• Using Data Validation

Creating & Working with Charts

• Creating a Chart
• Moving and Resizing a Chart
• Formatting and Editing Objects in a Chart
• Changing a Chart’s Source Data
• Changing a Chart Type and Working with Pie Charts
• Adding Titles, Gridlines, and a Data Table
• Formatting a Data Series and Chart Axis
• Annotating a Chart
• Working with 3-D Charts
• Selecting and Saving a Custom Chart
• Using Fill Effects
• Mapping Data
• Modifying a Map

Working with Other Programs

• Inserting an Excel Worksheet into a Word Document
• Modifying an Inserted Excel Worksheet
• Inserting a Linked Excel Chart in a Word Document
• Inserting a Graphic into a Worksheet

Microsoft Excel – Advanced

Excel Quick Overview

• Use of Excel, its boundaries & features

Data Formatting & Custom settings

• Number, Text, Date, Currency, Custom settings
• Data formatting & cleaning

Conditional Formatting

• Once defined, it will automatically change color of values e.g. up or down, high or low, pass or fail, profit or loss etc.

Filters, Queries & Data Sorting

• Drill down your data to your desired level.
• Sort data based on your choice e.g. like North, East, West, South rather than alphabetically.

Formula Writing & Fixing Errors

• Financial, Logical, Text, Day and Time, Statistical, Mathematical etc Sum, Average, Count, Minimum, Maximum, Absolute, Concatenate, Count, CountA, CountBlank, CountIf, Day, Today, Even, Exact, Exp, Find, Int, IsBlank, Left, Len, Lower, Upper, Proper, Now, Power, Rank, Right, Round, Trim etc.

If-Then-Else & Nested If commands

• Produce different set of results based on slabs, conditions.

VLookup / HLookup

• You have large amount of data placed at different locations & you want to merge it based on common values & it’s relevance.

Graphs & Charts

• In this workshop you will learn how to create, modify & update graphs / charts like Column, Line, Pie, Bar, Area, Scatter, 3D etc.

Hyper / Data Linking

• Hyper & data linking, within or outside Worksheet / Excel File is an important feature. This helps update data automatically.

Grouping – Ungrouping

• Sometime data needs to be grouped for summarized results & crisp view.
• Grouping & ungrouping feature will help you do so.

Data Analysis & Pivot Tables

• Summarize your data as per your desire.
• You need to select data in a range & produce consolidated results in user defined reporting formats.
• Creating a PivotTable
• Specifying the Data a PivotTable Analyzes
• Changing a PivotTable’s Calculation
• Selecting What Appears in a PivotTable
• Grouping Dates in a PivotTable
• Updating a PivotTable
• Formatting and Charting a PivotTable

Macros Recording, use, editing, linking:

• Record, edit & use Macros with little or no programming knowledge.

Security & Protection

• It will help you protect your cells, worksheet or an excel file from unauthorized use.

Data Validation

• Prevent wrong data entry in your specified format.

Microsoft Excel – Macros

Creating & Recording a Macro

• First step towards learning Macros programming is to learn how to create & record macros. This is the 1st step towards macro programming.

Executing Macros through shortcuts and command buttons

• This is using such features based on user requirements.

Editing recorded syntax

• How to edit or write your own Macro programs

Modules

• Learn about modules which is an excellent feature of VBA programming.

Procedures

• Write & practice programming procedures. Understand their flow.

Sub Procedures

• Write & practice programming procedures.

Functions

• There are certain inbuilt or user defined functions. Explore those functions

Objects and collections

• An object is a special type of variable that contains both data and codes. A collection is a group of objects of the same class. The most used Excel objects in VBA programming are Workbook, Worksheet, Sheet, and Range.

Workbook and workbook objects

• A workbook is the same as an Excel file. The Workbook collection contains all the workbooks that are currently opened. Inside of a workbook contains at least one worksheet.

Range object & cells property

• Range represents a cell, a row, a column, a selection of cells containing one or more contiguous blocks of cells, or a 3-D

Methods & properties

• Each object contains its own methods & properties. A Property represents a built-in or user-defined characteristic of object.

Object variables and arguments

• Sometime a method takes more than one argument. For example, the Open method for the Workbook object, takes 12 arguments. To open a workbook with password protection, you would need to write the code.

If Case, Loop Case & Select Case

• Looping, Sub looping, looping through a range, For, Next loop etc.

Excel VBA tutorial

• VBA tutorial will help you proceed in a step by step approach

Microsoft Visual Basic for Applications (VBA)

Your First Visual Basic Program

• Welcome To Visual Basic
• A Simple Project
• Using the Application Wizard

Adding Controls to Forms

• Visual Basic Controls
• Creating and Manipulating New Controls
• Changing Design Time Control Properties
• Visual Basic Built-In Controls
• Control Design Tips

Understanding Events

• Introducing Events
• Event Procedures
• The Form Load Event
• The Form Resize Event
• Command Button Click Event
• Text Box Change Event
• The Timer Control’s Timer Event
• GotFocus / LostFocus Events

Working with Forms and Controls

• Setting Properties at Run Time
• Setting Form and Control Properties
• Using ActiveX Controls
• Adding ActiveX Controls to a Project
• Using the Monthview ActiveX Control
• Extra ActiveX Samples

Creating and Using Menus

• Menus in Visual Basic
• Using the Menu Editor
• Working with Menus
• Manipulating Menus at Run Time
• Reusing Menus
• Creating Popup Menus

Compiling and Distributing Applications

• Finishing Touches
• Project Properties
• Native Code vs. p-code
• Creating Your Executable
• Using the Package and Deployment Wizard

Working with VBA

• Creating a Simple Procedure
• Variables and Parameters
• Data Types
• Using Constants
• Investigating Built-In Functions
• Branching Structures and Looping Structures

Handling Errors

• Handling Syntax Errors
• Handling Run-Time Errors
• The Error Handling Standard
• Taking a Closer Look
• Who Handles Errors?

Debugging Applications

• Handling Logic Errors
• Watch Expressions
• The Call Stack
• The Debugging Process

More VBA Issues, Form and Control Issues

• Scope, Lifetime, and Precedence
• Passing by Value and by Reference
• Arrays
• Optional Arguments
• Using Multiple Forms
• List Boxes Revisited
• Control Arrays

Adding Simple Database Support

• Support for Data Access in Visual Basic 6.0
• Using the ADO Data Control
• Binding Controls to Data
• Writing Code for the Data Control

To see the full course content Download now