MASTERING ADVANCED EXCEL FUNCTIONS
Advanced Functions:
SUBTOTAL:
Performs a specified function (e.g., SUM, AVERAGE) on filtered data.
Formula: =SUBTOTAL(function_num, ref1, [ref2], …)
Explanation: The SUBTOTAL function calculates a specified function (such as SUM, AVERAGE, MAX, MIN, etc.) on a range of data. It differs from regular functions like SUM or AVERAGE in that it ignores any cells that are hidden or filtered out. The function_num argument specifies which function to apply (e.g., 1 for AVERAGE, 9 for SUM, etc.), and ref1, [ref2], etc., are references to the cells or ranges where the calculation should be performed.
Example: =SUBTOTAL(9, B2:B10) calculates the sum of values in cells B2 to B10, ignoring any hidden or filtered out cells.
Video Link: (456) Using the Excel SUBTOTAL Function – YouTube
TODAY:
Inserts the current date into a cell.
Formula: =TODAY()
Explanation: The TODAY function returns the current date as a serial number. When used without any arguments, it automatically updates to display the current date whenever the worksheet is opened or recalculated.
Example: =TODAY() inserts the current date into the cell where the formula is entered.
Video Link: Using the Excel Today Function to Set Target Dates (youtube.com)
LEFT, RIGHT AND CONCATENATE:
Left:
- Extracts a specific number of characters from the left side of a text string.
- Syntax: =LEFT(text, num_chars)
Right:
- Extracts a specific number of characters from the right side of a text string.
- Syntax: =RIGHT(text, num_chars)
- Example: =RIGHT(“Apple”, 2) returns “le”
Video Link: Using LEFT RIGHT & MID Functions in Excel (youtube.com)
Concatenate:
- Joins multiple text strings or cell references into a single string.
- Syntax: =CONCATENATE(text1, text2, …) (can use cell references instead of text)
- Example: =CONCATENATE(“Hello “, A1, “!”) (if A1 contains “World”, returns “Hello World!”)
Video Link: How to Concatenate a Date with Text in Excel 2016 (youtube.com)
Data Validation:
Purpose:
Restrict the type of data (numbers, text, dates) allowed in a cell to ensure accuracy and consistency.
How-to:
Select cells > Data tab > Data Validation. Set criteria (e.g., numbers between 1-100).
Benefits:
Prevents typos, promotes consistency, safeguards formulas.
Data Protection:
Purpose:
Lock cells to prevent accidental edits and protect formulas.
How-to:
Select cells > Format Cells > Protection tab (uncheck “Locked”). Apply protection to the sheet.
Benefits:
Maintains data integrity, secures sensitive information.
Conditional Formatting:
Purpose:
Apply formatting (color, font) to cells based on specific conditions (e.g., highlight negative values).
How-to:
Select cells > Conditional Formatting. Choose a rule type (e.g., Highlight Cells Rules) and set conditions.
Benefits:
Provides visual cues, identifies important data trends.
Sorting:
Purpose:
Reorganize data in ascendaing (A to Z) or descending (Z to A) order based on a specific column.
How-to:
Select data range > Data tab > Sort. Choose sort column and order.
Benefits:
Quickly find specific data, analyze trends more easily.
