Advanced Excel Student Portal
Advanced level knowledge of Excel means possessing the ability to use spreadsheets, graphing, tables, calculations, and automation efficiently to process large quantities of data relevant to business tasks.
Some helpful advanced 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.
Materials
MS Excel Manual (2010)
Intermediate Level Functions to know:
SUM: With this function, you can sum values from multiple cells, or you can also input values directly into the function.
SUMIF: With this function, you can add a criteria to sum values from multiple cells.
COUNT: This function returns the count of numeric values in a cell. You can refer to the cells where you have values or simply insert the values into it.
COUNTIF: This function allows you to add a criteria to returns the count of numeric values in a cell.
AVERAGE: It returns the average of numeric values. You can refer to the cells where you have the values or simply insert them into the function.
DAYS: Returns number of days between two dates
TIME: It returns a valid time serial number as per Excel’s time format. You need to specify hours, minutes, and seconds.
TODAY: Returns todays date each time the Workbook is opened.
NOW: It returns the current date and time (using your system’s settings) in the cell where you insert it.
LEFT: It extracts specific characters from a cell/string starting from the left (start). You need to specify the text and number of characters to extract.
RIGHT: It extracts specific characters from a string starting from the right (last). You need to specify the text and number of characters to extract.
VLOOKUP: It looks up for a value in a column and can return that value or a value from the corresponding columns using the same row number.
IF: The IF function (tests a condition) returns a value when the specific condition is TRUE and returns another value if that condition is FALSE.
