Specialization in Excel Skills for Business

Learn the basics
The first course, Excel Skills for Business: Essentials, provides a solid foundation in Excel, covering the software's fundamental functions and their practical use. Skills acquired in this course include creating and managing spreadsheets, using basic formulas and functions, and creating graphs and tables.

Quick overview of the"Essentials" course curriculum

Week 1 – Taking control of Excel:
– Describe the key components of the Excel user interface
– Use essential navigation controls
– Perform basic data entry operations in Excel
– Explain basic Excel terminology

Week 2 – Performing calculations:
– Explain the syntax of basic formulas and functions
– Use formulas and functions to perform simple calculations
– Describe the difference between relative and absolute cell references

Week 3 – Formatting:
– Provide examples of key formatting tools and their uses in Excel
– Modify spreadsheets with Excel styles and themes
– Explain the use of number formatting in Excel
– Format a set of raw data using Excel formatting tools

Week 4 – Working with data:
– Managing rows, columns and worksheets
– Identifying, retrieving and modifying data in spreadsheets
– Explaining how to use conditional formatting in Excel
– Using the conditional formatting tool to highlight specific data

Week 5 – Printing:
– Identify the main printing tools and options in Excel
– Optimise a spreadsheet for printing
– Create a spreadsheet for printing with repetitive elements

Week 6 – Charts:
– Describe the basic chart types in Excel
– Create basic charts in Excel
– Modify charts in Excel

Expanding knowledge and skills
The second course, Excel Skills for Business: Intermediate I, builds on the fundamentals acquired in the previous course and introduces new advanced skills and techniques. This course teaches students how to work with multiple worksheets, use text and date functions, manage named ranges, and create pivot tables and charts.

Quick overview of the"Intermediate I" course syllabus

Week 1 – Working with multiple worksheets and workbooks:
– Compare different methods of combining data from multiple sources
– Use a variety of techniques to perform calculations between workbooks/worksheets
– Manage data sets across multiple workbooks/worksheets

Week 2 – Text and date functions:
– Explain the use of Date and Text functions in Excel
– Understand how to work with nested functions
– Use Date functions to extract additional business information
– Use Text functions to join or split text strings

Week 3 – Named intervals:
– Describe the use of named intervals
– Use different methods to create named intervals
– Improve calculations through the use of named intervals

Week 4 – Summarising data:
– Explaining the syntax of more advanced formulas
– Using functions to extract summary information from data
– Generating graphical representations of data

Week 5 – Tables:
– Create and edit tables in Excel
– Apply formatting, sorting, and filtering to tables
– Add data to tables
– Explain specific terminology related to tables in Excel

Week 6 – Pivot tables, pivot charts and segments:
– Create pivot tables, pivot charts and segments
– Use pivot tables to extract meaning from data sets
– Create visual dashboards with segments and pivot charts
– Use segments to filter information from multiple sources

Summary of objectives of the Intermediate I course:
– Manage large data sets efficiently
– Extract meaningful information from large data sets
– Present data and extract information effectively
– Work with data sets from different sources

Further insights into advanced features
The third course, Excel Skills for Business: Intermediate II, explores some of Excel's more advanced features. Students learn how to set up data validation, use conditional logic in formulas, perform automatic searches with VLOOKUP, INDEX and MATCH, and work with protecting and controlling procedures in workbooks. This course also provides a thorough understanding of using macros and data models to automate and simplify processes within spreadsheets.

Quick overview of the"Intermediate II" course curriculum

Week 1 – Data validation:
– Set up and configure data validation
– Work with formulas in data validation
– Create and use drop-down lists
– Create and apply custom conditional formats

Week 2 – Conditional logic:
– Explain the concept of conditional logic in formulas
– Evaluate data in a cell using logical tests
– Use conditional operations in functions (IF, AND, OR)
– Evaluate data with nested IF functions

Week 3 – Automatic searches:
– Use the VLOOKUP function to find and display the contents of a cell
– Identify the use and requirements of the search function in the range
– Search for data using the INDEX and MATCH functions

Week 4 – Formula verification and protection:
– Configure formula calculation options
– Trace precedents and dependents
– Explain how to check for errors in a spreadsheet
– Protect workbooks and worksheets

Week 5 – Data models:
– Model different scenarios based on inputs, assumptions, and/or results
– Use Goal Seek and Solver to investigate which input parameters produce a desired result
– Use data tables and scenario management

Week 6 – Recorded macros:
– Identify uses for macros in Excel
– Create macros to automate repetitive tasks
– Modify macros to extend their functionality
– Manage macros efficiently

Becoming an Excel expert
The fourth and final course, Excel Skills for Business: Advanced, prepares students to become expert users of Excel. In this course, you will learn advanced formula techniques, such as matrix functions and structured references, and how to clean and prepare data for analysis. Students also learn how to work with financial and data functions, create advanced searches and build professional dashboards in Excel.

Quick overview of the"Advanced" course curriculum

Week 1 – Spreadsheet design and structure:
– Design flexible and verifiable spreadsheets
– Create robust and transparent calculations
– Create spreadsheets that document themselves automatically
– Use formatting to improve functionality

Week 2 – Advanced formula techniques:
– Applying structured references in formulas
– Explaining the use of array formulas
– Creating formulas with array functions
– Using array formulas in calculations

Week 3 – Data cleaning and preparation:
– Correct dates with the help of functions
– Replace gaps with repeating values
– Remove spaces and unwanted characters from data

Week 4 – Financial functions and working with dates:
– Applying date formulas in calculations
– Using financial functions in calculations
– Creating an amortisation schedule with financial functions
– Using amortisation functions

Week 5 – Advanced lookup functions:
– Explain the R1C1 and A1 reference styles
– Use the INDIRECT and ADDRESS functions
– Create lookups using the OFFSET function
– Use INDEX for complex lookups

Week 6 – Creating professional dashboards:
– Designing the visual representation of data in dashboards
– Preparing data for visual representation
– Creating interactive dashboard elements