
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