INTERMEDIATE MICROSOFT EXCEL TRAINING

CLASSROOM FEE: ₦80,000 | ONLINE FEE: ₦60,000

SYNOPSIS: Microsoft Excel is one of the most powerful software used for analyzing data to make a better decision. It helps in data organizing, data analyzing, and data interpretation. Utilizing Microsoft Excel for inventory tracking is an easy way to cut costs, save time, and organize inventory. This course will take participants through intermediary excel tutorials, they will understand how Excel work. They will learn complex functions, nesting, data manipulation and automation using basic macros in excel. The course is intermediate excel and its dynamic application.

COURSE OUTLINE

Worksheet Management

• User Interface Navigation
• Keyboard shortcuts that facilitate quick and easy navigation within cells

Formula Review – Review various methods for completing calculations

Create and Save a Basic Workbook

Working with Text

• Splitting Text – Use Text to Columns to split text into multiple cells
• Joining Text – Join text from separate cells

Cell Ranges – Paste Special, apply formats and perform calculations on selected cells

Hardcode the answer to a formula or function

Named Ranges – Assign a name to a range of cells to make it easier to reference those ranges in calculations

Formatting a Worksheet

• Apply Text Formats
• Apply Number Formats
• Align Cell Contents
• Apply Styles and Themes
• Apply Basic Conditional Formatting
• Create and Use Templates

Printing Workbooks

• Preview and Print a Workbook
• Set Up the Page Layout
• Configure Headers and Footers

Database Functions

• VLOOKUP & XLOOKUP – Use VLOOKUP and XLOOKUP to find information in cell range and return information from another cell range
• Sort & Filter – Use Sort & Filter to find and organize data in large databases
• Pivot Tables – Create Pivot Tables to quickly summarize large databases
• Pivot Tables & Grouping Group within Pivot Tables – Create multiple Pivot Tables on a single worksheet

Logical Functions – IF statements – Used to return output based on the contents of another cell AND, OR Tests to see whether multiple conditions are true

Work with Date & Time Functions

Work with Text Functions

Math Functions – SUBTOTAL – Used to sum/average/count values based on what is not filtered

Statistical Functions –

• SUMIFS – Used to sum cells based on one or more conditions COUNTIFS Use
• COUNTIFS – Used to count cells based on one or more conditions

Improve Data Quality

• Data Validation – Restrict the type of data that can be allowed in a cell
• Remove Duplicates – Eliminate duplicate row data

Analyzing Data

• Create and Modify Tables
• Apply Intermediate Conditional Formatting
• Apply Advanced Conditional Formatting

Visualizing Data with Charts

• Create Charts
• Modify and Format Charts
• Use Advanced Chart Features
• Advanced Charts
• Combo Charts Combine two or more charts into a single chart, with the option of adding a secondary axis

More on PivotTables and PivotCharts

• Create a PivotTable
• Analyze PivotTable Data
• Present Data with PivotCharts
• Filter Data by Using Timelines and Slicers

Working with Multiple Worksheets and Workbooks

• Use Links and External References
• Use 3-D References
• Consolidate Data

Forecasting Data

• Determine Potential Outcomes Using Data Tables
• Determine Potential Outcomes Using Scenarios
• Use the Goal Seek Feature

Sharing and Protecting Workbooks

• Collaborate on a Workbook
• Protect Worksheets and Workbooks

TARGET AUDIENCE: Production Managers, Engineers, Office Managers, Administrators, Secretaries, Supervisors, Accountants, HR Managers, Sales Managers, Sales Executives and anyone wishing to up skill