Monday, 27 January 2025

Excel Dynamic array functions

 Excel's dynamic array functions are powerful tools that allow you to work with arrays and spill results into multiple cells automatically. These functions are available in Excel 365 and Excel 2021. Below is a detailed explanation of the most commonly used dynamic array functions, along with 20 examples for each:

1. FILTER Function

The FILTER function filters a range or array based on specified criteria.

Examples:

  1. =FILTER(A1:A10, B1:B10="Apple")
    Filters rows where column B equals "Apple".

  2. =FILTER(A1:C10, (B1:B10="Apple") * (C1:C10>10))
    Filters rows where column B is "Apple" and column C is greater than 10.

  3. =FILTER(A1:A10, A1:A10<>"")
    Filters non-blank cells in column A.

  4. =FILTER(A1:A10, ISNUMBER(A1:A10))
    Filters numeric values in column A.

  5. =FILTER(A1:A10, MOD(A1:A10, 2)=0)
    Filters even numbers in column A.

  6. =FILTER(A1:A10, A1:A10>TODAY())
    Filters dates in column A that are after today.

  7. =FILTER(A1:A10, SEARCH("App", A1:A10))
    Filters cells in column A containing "App".

  8. =FILTER(A1:A10, LEN(A1:A10)>5)
    Filters cells in column A with more than 5 characters.

  9. =FILTER(A1:A10, ISERROR(A1:A10))
    Filters cells in column A that contain errors.

  10. =FILTER(A1:A10, NOT(ISERROR(A1:A10)))
    Filters cells in column A without errors.

  11. =FILTER(A1:A10, A1:A10=MAX(A1:A10))
    Filters the maximum value in column A.

  12. =FILTER(A1:A10, A1:A10=MIN(A1:A10))
    Filters the minimum value in column A.

  13. =FILTER(A1:A10, MONTH(A1:A10)=1)
    Filters dates in column A that are in January.

  14. =FILTER(A1:A10, YEAR(A1:A10)=2023)
    Filters dates in column A that are in 2023.

  15. =FILTER(A1:A10, WEEKDAY(A1:A10)=1)
    Filters dates in column A that are Sundays.

  16. =FILTER(A1:A10, A1:A10>TODAY()-7)
    Filters dates in column A from the last 7 days.

  17. =FILTER(A1:A10, A1:A10<DATE(2023,12,31))
    Filters dates in column A before December 31, 2023.

  18. =FILTER(A1:A10, ISBLANK(A1:A10))
    Filters blank cells in column A.

  19. =FILTER(A1:A10, A1:A10<>"#N/A")
    Filters cells in column A that are not #N/A.

  20. =FILTER(A1:A10, A1:A10<>"Error")
    Filters cells in column A that do not contain "Error".

2. SORT Function

The SORT function sorts a range or array in ascending or descending order.

Examples:

  1. =SORT(A1:A10)
    Sorts column A in ascending order.

  2. =SORT(A1:A10, , -1)
    Sorts column A in descending order.

  3. =SORT(A1:B10, 2, 1)
    Sorts rows based on column B in ascending order.

  4. =SORT(A1:B10, 2, -1)
    Sorts rows based on column B in descending order.

  5. =SORT(A1:C10, 3, 1)
    Sorts rows based on column C in ascending order.

  6. =SORT(A1:C10, 3, -1)
    Sorts rows based on column C in descending order.

  7. =SORT(A1:A10, , , TRUE)
    Sorts column A in ascending order, ignoring case.

  8. =SORT(A1:A10, , -1, TRUE)
    Sorts column A in descending order, ignoring case.

  9. =SORT(FILTER(A1:A10, B1:B10="Apple"))
    Filters and sorts rows where column B equals "Apple".

  10. =SORT(UNIQUE(A1:A10))
    Sorts unique values in column A.

  11. =SORT(A1:A10, , -1)
    Sorts column A in descending order.

  12. =SORT(A1:B10, 1, 1, , 2, -1)
    Sorts by column A ascending, then by column B descending.

  13. =SORT(A1:A10, , , FALSE)
    Sorts column A in ascending order, considering case.

  14. =SORT(A1:A10, , -1, FALSE)
    Sorts column A in descending order, considering case.

  15. =SORT(A1:A10, , 1, TRUE)
    Sorts column A in ascending order, ignoring case.

  16. =SORT(A1:A10, , -1, TRUE)
    Sorts column A in descending order, ignoring case.

  17. =SORT(A1:A10, , 1, FALSE)
    Sorts column A in ascending order, considering case.

  18. =SORT(A1:A10, , -1, FALSE)
    Sorts column A in descending order, considering case.

  19. =SORT(A1:A10, , 1, TRUE)
    Sorts column A in ascending order, ignoring case.

  20. =SORT(A1:A10, , -1, TRUE)
    Sorts column A in descending order, ignoring case.

3. UNIQUE Function

The UNIQUE function extracts unique values from a range or array.

Examples:

  1. =UNIQUE(A1:A10)
    Extracts unique values from column A.

  2. =UNIQUE(A1:B10)
    Extracts unique rows from columns A and B.

  3. =UNIQUE(A1:A10, TRUE)
    Extracts unique values from column A, considering case.

  4. =UNIQUE(A1:A10, FALSE)
    Extracts unique values from column A, ignoring case.

  5. =UNIQUE(FILTER(A1:A10, B1:B10="Apple"))
    Extracts unique values from column A where column B equals "Apple".

  6. =UNIQUE(SORT(A1:A10))
    Sorts and extracts unique values from column A.

  7. =UNIQUE(A1:A10, , TRUE)
    Extracts unique values from column A, considering case.

  8. =UNIQUE(A1:A10, , FALSE)
    Extracts unique values from column A, ignoring case.

  9. =UNIQUE(A1:B10, TRUE)
    Extracts unique rows from columns A and B, considering case.

  10. =UNIQUE(A1:B10, FALSE)
    Extracts unique rows from columns A and B, ignoring case.

  11. =UNIQUE(FILTER(A1:A10, B1:B10="Apple"), TRUE)
    Extracts unique values from column A where column B equals "Apple", considering case.

  12. =UNIQUE(FILTER(A1:A10, B1:B10="Apple"), FALSE)
    Extracts unique values from column A where column B equals "Apple", ignoring case.

  13. =UNIQUE(SORT(A1:A10), TRUE)
    Sorts and extracts unique values from column A, considering case.

  14. =UNIQUE(SORT(A1:A10), FALSE)
    Sorts and extracts unique values from column A, ignoring case.

  15. =UNIQUE(A1:A10, TRUE, TRUE)
    Extracts unique values from column A, considering case and blanks.

  16. =UNIQUE(A1:A10, FALSE, TRUE)
    Extracts unique values from column A, ignoring case and blanks.

  17. =UNIQUE(A1:B10, TRUE, TRUE)
    Extracts unique rows from columns A and B, considering case and blanks.

  18. =UNIQUE(A1:B10, FALSE, TRUE)
    Extracts unique rows from columns A and B, ignoring case and blanks.

  19. =UNIQUE(FILTER(A1:A10, B1:B10="Apple"), TRUE, TRUE)
    Extracts unique values from column A where column B equals "Apple", considering case and blanks.

  20. =UNIQUE(FILTER(A1:A10, B1:B10="Apple"), FALSE, TRUE)
    Extracts unique values from column A where column B equals "Apple", ignoring case and blanks.

4. SEQUENCE Function

The SEQUENCE function generates a sequence of numbers.

Examples:

  1. =SEQUENCE(10)
    Generates numbers from 1 to 10.

  2. =SEQUENCE(5, 5)
    Generates a 5x5 grid of numbers from 1 to 25.

  3. =SEQUENCE(10, 1, 100)
    Generates numbers from 100 to 109.

  4. =SEQUENCE(5, 5, 10, 10)
    Generates a 5x5 grid starting at 10, incrementing by 10.

  5. =SEQUENCE(10, 1, 0, -1)
    Generates numbers from 0 to -9.

  6. =SEQUENCE(10, 1, 100, -10)
    Generates numbers from 100 to 10, decrementing by 10.

  7. =SEQUENCE(10, 1, 0, 0.1)
    Generates numbers from 0 to 0.9, incrementing by 0.1.

  8. =SEQUENCE(10, 1, 1, 2)
    Generates odd numbers from 1 to 19.

  9. =SEQUENCE(10, 1, 2, 2)
    Generates even numbers from 2 to 20.

  10. =SEQUENCE(10, 1, 100, -5)
    Generates numbers from 100 to 55, decrementing by 5.

  11. =SEQUENCE(10, 1, 0, 0.5)
    Generates numbers from 0 to 4.5, incrementing by 0.5.

  12. =SEQUENCE(10, 1, 1, 1)
    Generates numbers from 1 to 10.

  13. =SEQUENCE(10, 1, 10, -1)
    Generates numbers from 10 to 1.

  14. =SEQUENCE(10, 1, 100, -10)
    Generates numbers from 100 to 10, decrementing by 10.

  15. =SEQUENCE(10, 1, 0, 0.1)
    Generates numbers from 0 to 0.9, incrementing by 0.1.

  16. =SEQUENCE(10, 1, 1, 2)
    Generates odd numbers from 1 to 19.

  17. =SEQUENCE(10, 1, 2, 2)
    Generates even numbers from 2 to 20.

  18. =SEQUENCE(10, 1, 100, -5)
    Generates numbers from 100 to 55, decrementing by 5.

  19. =SEQUENCE(10, 1, 0, 0.5)
    Generates numbers from 0 to 4.5, incrementing by 0.5.

  20. =SEQUENCE(10, 1, 1, 1)
    Generates numbers from 1 to 10.

on

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)