Specialization in Excel Skills for Business

Knowing the basics
The first course, Excel Skills for Business: Essentials, provides a solid foundation of Excel knowledge, teaching the fundamental functions of the software and how to use them effectively. 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 – Perform 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 the critical formatting tools and their uses in Excel
– Editing spreadsheets with Excel styles and themes
– Explain the use of number formatting in Excel
– Formatting a raw data set using Excel’s formatting tools

Week 4-Working with Data:
– Manage rows, columns and worksheets
– Identify, retrieve and modify data in spreadsheets
– Explain how to use conditional formatting in Excel
– Use the conditional formatting tool to highlight specific data

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

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

Expand knowledge and skills
The second course, Excel Skills for Business: Intermediate I, builds on the fundamentals acquired in the previous period 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 the Date and Text functions in Excel
– Understanding how to work with nested functions
– Use Data functions to extract more information about the business
– Use Text functions to merge or split text strings

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

Week 4 – Summarize the data:
– Explain the syntax of more advanced formulas
– Use functions to extract summary information from data
– Generate graphical representations of data

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

Week 6 – Pivot tables, pivot charts and segments:
– Create pivot tables, pivot charts and segments
– Using 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 Objectives “Intermediate I” course:
– Manage large data sets efficiently
– Extracting meaningful information from large datasets
– Present data and extract information effectively
– Working with datasets from different sources

Further insights into advanced features
The third course, Excel Skills for Business: Intermediate II, delves into some of the more advanced features of Excel. 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
– Working with formulas in data validation
– Creating and using drop-down lists
– Create and apply custom conditional formats

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

Week 3 – Automated 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-Verification of formulas and protection:
– Configure formula calculation options
– Tracking precedents and employees
– Explain how to check for errors in a spreadsheet
– Protect workbooks and worksheets

Week 5 – Data models:
– Different Model scenarios based on inputs, assumptions, and outcomes
– Use Goal Seek and Solver to investigate which input parameters produce a desired outcome
– Using Data Tables and Scenario Management

Week 6 – Macro recorded:
– Identifying the uses of macros in Excel
– Create macros to automate repetitive tasks
– Modifying macros to extend their functionality
– Managing macros efficiently

Become an expert in Excel
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
– Creating robust and transparent calculations
– Create spreadsheets that document automatically
– Use formatting to improve functionality

Week 2 – Advanced formula techniques:
– Apply structured references in formulas
– Explain the use of matrix formulas
– Creating formulas with matrix functions
– Use matrix formulas in calculations

Week 3-Cleaning and preparing data:
– Correcting dates with the help of functions
– Replace blanks with repetitive values
– Remove unwanted spaces and characters from the data

Week 4 – Financial functions and working with dates:
– Apply formulas on dates in calculations
– Use financial functions in calculations
– Create an amortization schedule with financial functions
– Using depreciation functions

Week 5 – Advanced search functions:
– Explain the reference style R1C1 and A1
– Use the INDIRECT and ADDRESS functions.
– Create searches using the OFFSET function
– Use INDEX for complex searches

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