Intermediate level Excel skills include options and methods to manage and work data in an efficient way..
Some helpful Intermediate level Functions to know are
SUMIF: Finds a specific piece of information by matching it against criteria.
VLOOKUP: Finds a value in a table and returns related information from another column. It's useful for combining data from different lists or comparing lists for missing or matching items.
INDEX-MATCH: Returns a value in a column to the left. It's an alternative to VLOOKUP, which returns an appraisal from a column to the right.
CONCATENATE: Joins information from different cells to make worksheets more dynamic. It's a powerful tool for financial analysts.
IFERROR: Catches and handles formula errors, returning a custom message if an error is detected.
COUNTIF: Counts cells within a specified range that meet a certain criterion.
IF AND: Creates a new field based on constraints from an existing field.
LEFT, MID, and RIGHT: Extracts parts of a text string.
OFFSET: Can be combined with other functions like SUM or AVERAGE to build complex formulas. For example, you can use OFFSET to shift the cell reference around.
Beginner level recap navigation & formatting of the worksheets, sorting, filtering, and basic calculation.
1 Beginner - Basic Workbook Skills
o Moving around a worksheet
o Navigating the workbook
o Creating a workbook
o Selecting cells and ranges
o Opening, closing, and saving workbooks
o Entering data into cells EX 9
o Entering values into ranges
o Entering multiple lines EX 11
o Entering Dates, numbers, %, etc EX 12
2 Creating Formulas EX 30
o Writing formulas in cells
o Precedence EX 31
o Using the Formula Bar
o AutoSum and other basic functions EX 34
o Automatic calculation EX 36
3 Working with Functions EX 127
o Inserting Functions EX 131
o Using the Paste Function tool
o Absolute and relative cell references EX 118-121
o Using range names in functions
o Creating nested functions
o Logical functions (SUMIF, COUNTIF) EX 151
o The IF function and nested IFs
o Using VLOOKUP
4 Working with Data
o Sorting Data
o Custom sorts
o The Advanced Filter tool
o Inserting Charts
o Using the Chart Recommendation Feature
o Editing Charts
o Using Chart Tools
o Using the Quick Analysis Tool
o Add and Format Objects
5 Collaboration Tools
o Protecting a workbook
o Protecting worksheets and cells
o Adding comments to cells
o Sharing workbooks
o Tracking changes