MS Office Excel 2016 (Basic + Advanced)

Course Code: IT 191

1106 Course Visits

Course Sector:

Information Technology

Request in house training Call Me Back

Course Dates and Locations

Choose a date and location to book your seat

No.

Date

Days

Location

Fees

Enrollment

No available dates now, will be added soon..

Introduction

Training course introducion / brief

Without any doubts, all companies and ministries in our current era are totally dependent on computers and systems to finish all their daily tasks which used to take hundreds of hours to be done manually. More specifically, when we talk about data management and reports, things become more and more complicated to avoid human mistakes which incur additional work hours.

With the presence of software like Microsoft Excel, things have become more and more easier, the workflow has become smoother, and the revenues generated are much higher. Using Microsoft Excel, you can now easily automate all your tasks, generate data reports automatically, derive daily and weekly budget tables within seconds. In this course, we will go through all the tweaks in Microsoft Excel to improve our productivity and accuracy at work.

Gain the necessary data management skills in Excel
Easily automate all daily and weekly reporting
Replicate all data with other departments at work
Create customized charting for reporting
Smoothly generate formulas for data management
Quickly backup and restore data and reports in case of any data loss

Cloud Computing Engineer
Computer Network Specialist
Computer Support Specialist
Database Administrator
Information Technology Analyst
Information Technology Leadership
Information Security Specialist
Software/Application Developer
Web Developer
Technology sales consultant

Introduction to Microsoft Excel 2016

Navigating the Excel User Interface
Spreadsheets, Worksheets and Workbook, Cells and Ranges and References, The Excel User Interface Inner and Outer Elements, Excel Window Commands, The Backstage View, Mouse Cursor Icons, Basic Data Entry
Using Excel Commands
The Ribbon, ScreenTips and Key Tips, The Quick Access Toolbar, The Mini Toolbar and Context Menus
Creating and Saving a Basic Workbook
The New Tab, The Save, Save As Commands, and Save As Screen, Compatibility Mode and Compatibility Checker, The Convert Option
Entering Cell Data
Data Types, The Cut, Copy, and Paste, Undo and Redo Commands, The AutoFill Feature and Options, Flash Fill

Performing Calculations

Creating Worksheet Formulas
Excel Formulas, The Formula Bar, A Basic Mathematical Formula, Elements of Excel Formulas, The Order of Operations, Intersection Operator
Insert Functions
Functions, The Function Library Group, The Insert Function Dialog Box, The Function Arguments Dialog Box, Graphical Cell and Range Reference Entry, The AutoSum Feature, Other Commonly Used Functions, Basic Function Syntax, The Formula AutoComplete Feature, The Arguments Tooltip
Reuse Formulas and Functions
The Paste Options, The Paste Special Dialog Box, Relative References, Absolute References, Mixed References, AutoFill and Formulas, Worksheet References, Excel Errors and Indicators

Modifying and Formatting a Worksheet

Insert, Delete, and Adjust Cells, Columns, and Rows
The Insert and Delete Options, Manual Width and Height Adjustments, Manual Fit, The AutoFit Feature, The Row Height and Column Width Dialog Boxes, The Hide and Unhide Commands
Search for and Replace Data
The Find Command, The Replace Command, The Go To Dialog Box, The Go To Special Dialog Box
Use Proofing and Research Tools
The Spelling Dialog Box, The Thesaurus Task Pane, The Insights Task Pane
Apply Text Formats
Fonts, The Font Group, The Format Cells Dialog Box, The Colors Dialog Box, Hyperlinks, The Insert/Edit Hyperlink Dialog Box, The Format Painter
Apply Number Formats
Number Formats, Number Format Tab, Custom Number Formats
Align Cell Contents
Alignment Options, The Indent Commands, The Wrap Text Command, Orientation Options, The Merge & Center Options
Apply Styles and Themes
Cell Styles, Galleries, The Style Dialog Box, The Merge Styles Dialog Box, Themes, Guidelines for Using Themes
Apply Basic Conditional Formatting
Conditional Formatting, The Conditional Formatting Dialog Boxes, The Highlight Cells Rules, Data Bars, Color Scales, Icon Sets
Create and Use Templates
Templates and the Backstage View

Printing and Managing Workbooks

Preview and Print a Workbook
The Print Tab, Print Settings, Page Orientation, Margins, Print Preview
Set Up the Page Layout
The Page Setup Dialog Box, The Print Area, The Print Titles Command, Page Breaks, Workbook Views, Page Break Preview View
Configure Headers and Footers
Headers and Footers, Page Layout View, Contextual Tabs, The Header & Footer Tools Contextual Tab, The Header and Footer Dialog Boxes
Manage Worksheets
Tab Formatting Options, Drag to Reposition Worksheets, The Move or Copy Dialog Box, Methods of Inserting and Deleting Worksheets, The Hide and Unhide Worksheet Options
Manage Workbook and Worksheet Views
Custom Views, The Add View Dialog Box, The Split Command, The Freeze Panes Options, The Arrange All Command, The Arrange Windows Dialog Box, The View Side by Side Command, The Switch Windows Command, The New Window Command

Understanding the Advanced part of Microsoft Excel

Using the IF condition
IFERROR and when to use it
Using SUMIFS, AverageIFS and how does it differ from the AverageIF only?
What is the usage of the COUNTIFS? How to make our report and the result dynamic
How to use the various text functions in Microsoft Excel and how to apply the Left, Right, Mid, Len, and Find functions
How to use the Date functions to easily calculate the number of working days without holidays for a certain report

LOOKUP, VLOOKUP AND HLOOKUP

Use and advantages of LOOKUP, VLOOKUP
What is the difference between VLOOKUP and HLOOKUP?
Match and Index functions?
Scenarios in which we are obliged to use the Match and Index instead of VLookup
How to combine both Match and Index in one cell instead of having them in two different cells
How to create Data Tables to easily fill data for various cases and scenarios

Data Validation

How to apply proper data validation
How to validate data for a specific range number and how to prevent the user from entering invalid data
How to setup data validation errors
How to create data validation rule and take the input from other cells
How to create a drop-down list menu and take the values from other sheets and columns

Proper structuring of organized data

How to apply the basic and advanced sorting of data
What are the various ways to sort data?
What are the mistakes that are usually done in sorting which mess the whole data and how to prevent them?
How to apply the basic and advanced filtering
How to use advanced criteria to filter data
How to create tables to easily apply formatting and calculations
How to enable the structured references in tables, and how to allow the auto formula fill for new rows

Exporting and Importing Data From/To Excel

How to remove duplicates
How to highlight duplicates
How to convert text to various columns in one click
How to get external data and link them automatically to excel
How to extract data from a web page and add them to excel
How to use the Goal Seek functionality to find our goal
How to use various Scenarios in excel to better represent and visualize our data

Understanding Advanced Charts

How to change the Chart Scale
How to modify the data shown on the X axis and the Y axis
How to format the data points and modify the increments
How to use the combination charts (Combo Chart) and add two charts at the same time
How to add trendlines in our charts

Pivot Charts

How to add a pivot chart and how to add their various fields
How to insert slicers to filter data
How to group data in a pivot table and how to move files
How to refresh pivot table data
How to insert calculated fields in a picot table
How to properly format a pivot table
How to change the PivotTable field settings and how to add a currency
How to add Pivot Charts and how to move them from one sheet to another and keep them linked and synchronized and updated

Boost certificate of completion

BOOST's Professional Attendance Certificate “BPAC” is always given to the delegates after completing the training course, and depends on their attendance of the program at a rate of no less than 80%, besides their active participation and engagement during the program sessions.

ENDORSED EDUCATION PROVIDER

Flexible deadlines

Customized dates accordance to your schedule

Shareable Certificate

Earn certificate upon completion

COURSE METHODOLOGY

Our Training programs are implemented by combining the participants' academic knowledge and practical practice (30% theoretical / 70% practical activities).

At The end of the training program, Participants are involved in practical workshop to show their skills in applying what they were trained for. A detailed report is submitted to each participant and the training department in the organization on the results of the participant's performance and the return on training. Our programs focus on exercises, case studies, and individual and group presentations.

Download Company profile

Download Calendar

Trending Courses

The most bespoke and flexible training courses

Open courses table

Jan

- 05 -
Days

Leading and Building a Positive, Motivated, and Empowered Teams

Online, Virtual

TRAINING COURSE: MS Office Excel 2016 (Basic + Advanced)