Microsoft Excel
Intermediate
Overview
Whether you need to crunch numbers for sales, inventory, information technology, human resources, or other organizational purposes and departments, the ability to get the right information to the right people at the right time can create a powerful competitive advantage. After all, the world runs on data more than ever before and that’s a trend not likely to change, or even slow down, any time soon. But with so much data available and being created on a nearly constant basis, the ability to make sense of that data becomes more critical and challenging with every passing day. You already know how to get Excel to perform simple calculations and how to modify your workbooks and worksheets to make them easier to read, interpret, and present to others. But Excel can do so much more. To gain a truly competitive edge, you need to be able to extract actionable organizational intelligence from your raw data. In other words, when you have questions about your data, you need to know how to get Excel to provide the answers for you. And that’s exactly what this course aims to help you do.
Course Objective
Upon successful completion of this course, you will be able to leverage the power of data analysis and presentation to make informed, intelligent organisational decisions.
You will:
- Create advanced formulas.
- Analyse data by using functions and conditional formatting.
- Organise and analyse datasets and tables.
- Visualise data by using basic charts.
Who Should Attend
This course is designed for students who already have foundational knowledge and skills in Excel and who wish to begin taking advantage of some of the higher-level functionality in Excel to analyse and present data.
Prerequisites
The prerequisites for a Microsoft Excel Intermediate course are built upon the foundational knowledge and skills typically covered in a beginner-level Excel course.
Training Calendar
Intake
Duration
Program Fees
Module
Module 1 - Using Functions
Topic A: Creating and Using Functions
- Identifying the Parts of a Function
Topic B: Commonly Used Functions
- SUM
- AVERAGE
- COUNT
- MAX
- MIN
Topic C: Defining Relative, Mixed, and Absolute References
- Relative Reference
- Absolute References
- Mixed References
Topic D: Creating and Using Named Ranges
- Naming Ranges using Name Box
- Automatically Naming Ranges
- Navigating with Named Ranges
- Using Range Names in Formulas
- Pasting a List of Named Ranges
- Deleting and Editing Range Names
Topic E: Using the Logical IF Function
- Building an IF Formula
Topic F: Using Dates and Times
- Using the TODAY Function
- Using Date in Calculations
Topic G: Using Lookup Functions
- The VLOOKUP Function
- The HLOOKUP Function
- VLOOKUP Function for Approximate Match
- HLOOKUP Function for Exact Match
- VLOOKUP Function for Approximate Match
- HLOOKUP Function for Exact Match
Module 2 - Charting
Topic A: Using Charts
- Identifying chart types
Topic B: Working with Charts
- Inserting a chart
- Adding and removing chart elements
- Changing chart type, style, and chart sub-type
- Changing the location of a chart
Topic C: Changes to the Chart Source Data
- Update the data in existing Chart
- Add data series to a Chart
- Remove Data from a Chart
Module 3 - Advanced Formatting
Topic A: Conditional Formats • Format Cells by Using a Two-Color Scale
- Format Cells by Using Data Bars
- Format Cells by Using an Icon Set
- Format Only Cells That Contain Text, Number, or Date or Time Values
- Format Only Top/Bottom Ranked Values
- Format Only Values that are Above/Below Average
- Format Only Unique or Duplicate Values
- Use a Formula to Determine Which Cells to Format
- Clear Conditional Formats
Module 4 - Organizing and Analyzing Datasets
Topic A: Create and Modify Tables
- Tables
- Creating a Table
- Modify Tables
Topic B: Sort Data
- Sorting Data
- Quick Sorting
- Custom Sorts
Topic C: Filter Data
- Filtering Data in a List
- Other Types of Filtering
Topic C: Slicer
- Open Slicer
- Slice Data
- Slice by Text
- Slice by Number
- Slice by Date
FAQs
General Questions:
Q: What is a Microsoft Excel Intermediate course?
A: It’s a course that builds upon basic Excel skills, teaching more advanced techniques for data analysis, reporting, and automation. It bridges the gap between basic spreadsheet use and more complex Excel functionalities.
Q: Who is this course for?
A: This course is designed for users who are comfortable with the basics of Excel but want to expand their knowledge and become more proficient. It’s suitable for office workers, analysts, and anyone who uses Excel regularly and wants to improve their skills.
Q: What are the prerequisites for this course?
A: You should have a solid understanding of basic Excel operations, including entering and formatting data, working with formulas and functions (SUM, AVERAGE, etc.), and creating simple charts. Check the course description for specifics.
Q: What software do I need?
A: You will need Microsoft Excel installed on your computer. The specific version (e.g., 2013, 2016, 2019, 365) might be specified by the training provider.
Q: How is the course structured?
A: The course structure varies. It might involve in-person workshops, live online sessions, self-paced online modules, or a combination. Check the course details.
Q: How long is the course?
A: The duration varies depending on the scope and format. It can range from a few days to several weeks. Check the course details.
Q: Will I receive a certificate upon completion?
A: Yes.
Program Content & Skills:
Q: What specific topics are covered in the course?
A: Typical topics include advanced functions (lookup, logical, text, date & time), data analysis tools (PivotTables, conditional formatting, what-if analysis), advanced charting, working with multiple worksheets and workbooks, named ranges, and potentially an introduction to macros or VBA. Check the syllabus for a detailed list.
Q: Will I learn about PivotTables?
A: Yes, PivotTables are a core topic in intermediate Excel courses.
Q: Will I learn about advanced charting techniques?
A: Yes, intermediate courses typically cover more advanced chart formatting and customization.
Q: Will I learn about macros or VBA?
A: Some intermediate courses might introduce basic macros, but extensive VBA programming is usually covered in advanced Excel courses. Check the course description.
Q: Will I work on real-world examples and exercises?
A: A good intermediate course will include practical exercises and case studies to reinforce learning.
Submit your interest today !
