Course Overview
This course is a 5day intensive, high impact dive into excel Skills for Business at the first intermediate level. Spreadsheet software remains one of the most ubiquitous pieces of software used in workplaces across the world. Learning to confidently operate this software means adding a highly valuable asset to your employability portfolio. In Botswana, many job advertisements requiring Excel skills are posted every day. Research by Burning Glass Technologies and Capital One shows that digital skills lead to higher income and better employment opportunities at a time when digital skills jobs are growing much faster than non-digital jobs.
In this second course of the Excel specialization, Excel Skills for Business participants will build on the strong foundations of the Essentials course. Intermediate Skills I will expand participants’ Excel knowledge to new horizons. Participants will discover a whole range of skills and techniques that will become a standard constituent of their everyday use of Excel. In this course, participants will build a solid layer of more advanced skills so they can manage large datasets and create meaningful reports. These key techniques and tools will allow them to add a sophisticated layer of automation and efficiency to their everyday tasks in Excel.
Participant will learn how to work with multiple worksheets & workbooks to manage spreadsheets. They will understand how to combine data, manage datasets and perform calculations across multiple sources. As such they will become experts in dates and text functions as they will be able to extract information and manipulate data to fulfil specific business requirements. They will learn how to create, manage and apply Named Ranges to enhance their calculations.
Furthermore, participants of this course will understand data summarizing in excel as they will learn how one can use functions like COUNTIFS to extract information from data, as well as generate graphical representations of it. It starts by teaching participants how to create, format and manage tables and then move on to table sorting and filtering. This is followed by a deep dive into the popular (and very useful) pivot tables to teach participants how to create and modify them to solve a variety of business problems.
Practical examples and activities of real life excel scenarios shall be used throughout this course. This shall be facilitated by the employ of group activities, tests, scenario analyses and group discussions to help participant’s gain vigorous understanding of excel at this first intermediate level.
Target Audience
This course is suitable for the following personnel;
- Finance and accounting
- Human resource
- Management
- Sales and marketing
Learning Outcomes
By the end of the course, participants should be able to:
- Demonstrate understanding of working with multiple worksheets & Workbooks in Excel.
- Evaluate and apply the Text and Date Functions of excel.
- Create, manage and apply Named Ranges to enhance calculations in excel.
- Demonstrate understanding of data summarizing in excel.
- Demonstrate understanding of the various functions of tables in excel.
- Apply Pivot Tables, Charts and Slicers to solve a variety of business problems.
Content Covered
The following content will be covered in the course:
Working with Multiple Worksheets & Workbooks
- Introduction
- Multiple Worksheets
- 3D Formulas • Linking Workbooks
- Consolidating by Position
- Consolidating by Category
Text and Date Functions
- Introduction
- Combining Text
- Changing Text Case
- Extracting Text
- Finding Text
- Date Calculations
Named Ranges
- Introduction
- Introducing Named Ranges
- Creating Named Ranges
- Managing Named Ranges
- Named Ranges in Formulas
- Apply Names
Summarizing Data
- Introduction
- COUNT functions
- Counting with Criteria
- Adding with Criteria
- Sparkline
- Advanced Charting
- Trend lines
Tables
- Introduction
- Creating and Formatting Tables
- Working with Tables
- Sorting and Filtering in Tables
- Automation with Tables
- Converting to Range and Subtotaling
Pivot Tables, Charts and Slicers
- Introduction
- Creating and Modifying a Pivot Table
- Value Field Settings
- Sorting and Filtering a Pivot Table
- Reporting Filter Pages
- Pivoting Charts
- Pivoting Slicers
Assessment
A variety of assessment tools will be used throughout the course to gauge the level of understanding of the participants.
(i). Quizzes
After some of the topics have been covered, there will be mini quizzes that will be handed out and students must complete them based on the material they just covered.
(ii). Group Discussions
After some of the topics, group discussion activities will be initiated and the aim of these group discussions is for the participants to share their understanding of concepts covered and by sharing, they display their understanding of the content
(iii). Scenario Analysis
Scenario analysis will also be used as an assessment tool. Participants will be given a scenario pertaining to what has just been covered and then asked how they would navigate through the scenario. The answers they give would indicate how well they understood the material.
(iv) Final Written Test
At the end of the course there will be a final written test. The final written test takes all the content learned throughout the course and tests the participants’ knowledge all at once. The outcome of this final test is what will determine if participants pass or fail the course.