Dynamic array formulas
FILTER, SORT, UNIQUE and friends: one formula that returns a whole table. 61 answers, each checked by running it, with a live example and an exercise.
How to filter rows based on a condition with a formula in Excel
=FILTER(A2:C10,B2:B10=G1)Use =FILTER(table, condition). It returns every matching row and spills the result below the formula.
How to use FILTER with multiple criteria in Excel
=FILTER(A2:C10,(B2:B10=G1)*(C2:C10>=G2))Multiply the conditions for AND and add them for OR: =FILTER(table,(cond1)*(cond2)).
How to sort data with a formula in Excel
=SORT(A2:B7,2,-1)Use =SORT(range, sort_column, -1) for largest first, or 1 for smallest first. The sorted copy spills and updates automatically.
How to get a list of unique values in Excel
=SORT(UNIQUE(B2:B10))Use =UNIQUE(range) to list each value once, and wrap it in SORT for alphabetical order: =SORT(UNIQUE(range)).
How to get the top 3 rows by value in Excel
=TAKE(SORT(A2:B8,2,-1),3)Sort the table by the value column, largest first, and take the first rows: =TAKE(SORT(table,2,-1),3). In Google Sheets use QUERY with limit.
How to return multiple matching values in one cell in Excel
=TEXTJOIN(", ",TRUE,FILTER(B2:B8,A2:A8=E1))Filter the matches and join them: =TEXTJOIN(", ",TRUE,FILTER(results,lookup_range=value)).
How to filter rows between two dates in Excel
=FILTER(A2:E11,(B2:B11>=H1)*(B2:B11<=H2))Multiply two date tests inside FILTER:
=FILTER(A2:E11,(B2:B11>=H1)*(B2:B11<=H2))returns every row dated from the start date to the end date.How to filter rows that match one value or another in Excel
=FILTER(A2:E11,(D2:D11=H1)+(D2:D11=H2))Add the tests instead of multiplying them:
=FILTER(A2:E11,(D2:D11=H1)+(D2:D11=H2))keeps a row when either test is TRUE.How to filter by a list of values in Excel
=FILTER(A2:E11,COUNTIF(G2:G4,C2:C11))Use COUNTIF as the condition:
=FILTER(A2:E11,COUNTIF(G2:G4,C2:C11))keeps every row whose customer appears in the list inG2:G4.How to filter rows that contain specific text in Excel
=FILTER(A2:D10,ISNUMBER(SEARCH(G1,B2:B10)))Combine FILTER with SEARCH:
=FILTER(A2:D10,ISNUMBER(SEARCH(G1,B2:B10)))returns every row whose product name contains the text inG1.How to remove blank cells from a list with a formula in Excel
=FILTER(A2:A13,A2:A13<>"")Filter out the empty cells:
=FILTER(A2:A13,A2:A13<>"")returns the list without gaps, and it updates as names are added.How to return only certain columns with FILTER in Excel
=CHOOSECOLS(FILTER(A2:E11,D2:D11=H1),3,5)Wrap FILTER in CHOOSECOLS:
=CHOOSECOLS(FILTER(A2:E11,D2:D11=H1),3,5)returns just the customer and amount columns of the matching rows.How to fix the #CALC! error in FILTER in Excel
=FILTER(A2:E11,D2:D11=H1,"No matches")#CALC! means no row matched. Give FILTER its third argument:
=FILTER(A2:E11,D2:D11=H1,"No matches")shows a message instead.How to filter out rows with a certain value in Excel
=FILTER(A2:D9,C2:C9<>G1)Use the not-equal operator in FILTER:
=FILTER(A2:D9,C2:C9<>G1)returns every row except those whose status is inG1.How to filter data by month in Excel
=FILTER(A2:E11,(MONTH(B2:B11)=MONTH(H1))*(YEAR(B2:B11)=YEAR(H1)))Test the month and the year of each date:
=FILTER(A2:E11,(MONTH(B2:B11)=MONTH(H1))*(YEAR(B2:B11)=YEAR(H1)))returns the rows in the same month as the date inH1.How to show the last 5 entries in a list in Excel
=TAKE(FILTER(A2:C20,A2:A20<>""),-G1)Drop the empty rows with FILTER, then take from the bottom:
=TAKE(FILTER(A2:C20,A2:A20<>""),-H1). A negative number counts from the end.How to filter by a dropdown with an "All" option in Excel
=FILTER(A2:C9,((B2:B9=F1)+(F1="All"))*((C2:C9=F2)+(F2="All")))Let each test pass when the dropdown says All:
=FILTER(A2:C9,((B2:B9=F1)+(F1="All"))*((C2:C9=F2)+(F2="All"))).How to return columns by header name in Excel
=CHOOSECOLS(A2:F7,XMATCH(H1:I1,A1:F1))Find each header's position with XMATCH and pick those columns with CHOOSECOLS:
=CHOOSECOLS(A2:F7,XMATCH(H1:I1,A1:F1)).How to list duplicate values with a formula in Excel
=UNIQUE(FILTER(A2:A12,COUNTIF(A2:A12,A2:A12)>1))Keep the values that appear more than once, then list each one once:
=UNIQUE(FILTER(A2:A12,COUNTIF(A2:A12,A2:A12)>1)).How to sort by two columns with a formula in Excel
=SORT(A2:C9,{2,3},{1,-1})Give SORT arrays of columns and orders:
=SORT(A2:C9,{2,3},{1,-1})sorts by department A to Z, then by salary from highest within each department.How to sort a list by another column in Excel
=SORTBY(A2:A9,C2:C9,-1)Use SORTBY with the other column as the key:
=SORTBY(A2:A9,C2:C9,-1)returns the product names ordered by units sold, highest first.How to sort by a custom order with a formula in Excel
=SORTBY(A2:C10,XMATCH(B2:B10,F2:F4))Sort by each value's position in your own list:
=SORTBY(A2:C10,XMATCH(B2:B10,F2:F4))puts High first, then Medium, then Low.How to sort names by last name with a formula in Excel
=SORTBY(A2:A9,TEXTAFTER(A2:A9," ",-1))Sort by the text after the last space:
=SORTBY(A2:A9,TEXTAFTER(A2:A9," ",-1)). The -1 counts from the end, so middle names don't get in the way.How to sort columns left to right with a formula in Excel
=SORT(B1:F2,2,-1,TRUE)Set SORT's fourth argument, by_col, to TRUE:
=SORT(B1:F2,2,-1,TRUE)sorts the columns by the values in row 2, largest first.How to make a ranked leaderboard with a formula in Excel
=SORT(HSTACK(RANK.EQ(B2:B9,B2:B9),A2:B9),1)Add a rank column with HSTACK and sort by it:
=SORT(HSTACK(RANK.EQ(B2:B9,B2:B9),A2:B9),1). Tied scores share a rank.How to remove duplicate rows with a formula in Excel
=UNIQUE(A2:C11)Pass the whole table to UNIQUE:
=UNIQUE(A2:C11)keeps each distinct row once, comparing every column.How to get unique combinations of two columns in Excel
=UNIQUE(CHOOSECOLS(A2:D12,1,3))Pick the two columns with CHOOSECOLS and pass them to UNIQUE:
=UNIQUE(CHOOSECOLS(A2:D12,1,3))lists each region and product pair once.How to list values that appear only once in Excel
=UNIQUE(B2:B12,FALSE,TRUE)Set UNIQUE's third argument, exactly_once, to TRUE:
=UNIQUE(B2:B12,FALSE,TRUE)returns only the values with no repeats.Why does UNIQUE return 0 for blank cells in Excel
=UNIQUE(FILTER(A2:A15,A2:A15<>""))UNIQUE treats an empty cell as a value and shows it as 0. Remove the blanks first:
=UNIQUE(FILTER(A2:A15,A2:A15<>"")).How to find the most common values in a list in Excel
=TAKE(SORT(HSTACK(UNIQUE(B2:B15),COUNTIF(B2:B15,UNIQUE(B2:B15))),2,-1),3)Count each unique value, sort by the count and keep the top rows:
=TAKE(SORT(HSTACK(UNIQUE(B2:B15),COUNTIF(B2:B15,UNIQUE(B2:B15))),2,-1),3).How to get unique values from multiple columns in Excel
=SORT(UNIQUE(TOCOL(B2:D6,1)))Stack the columns into one with TOCOL, then remove the repeats:
=SORT(UNIQUE(TOCOL(B2:D6,1))).How to create a dependent dropdown list in Excel
=SORT(FILTER(B2:B12,A2:A12=E1))Build the second list with FILTER in a helper cell,
=SORT(FILTER(B2:B12,A2:A12=E1)), then point the second dropdown's Data Validation source at it with=$G$2#.How to get unique values based on a condition in Excel
=SORT(UNIQUE(FILTER(B2:B12,C2:C12=G1)))Filter the rows first, then remove the repeats:
=SORT(UNIQUE(FILTER(B2:B12,C2:C12=G1)))lists each person who logged time on one project.How to number rows automatically in Excel
=SEQUENCE(COUNTA(B2:B30))Let SEQUENCE count the filled rows:
=SEQUENCE(COUNTA(B2:B30))numbers every task from 1, and the numbers extend when you add a row.How to create a list of dates with a formula in Excel
=SEQUENCE(B2,1,B1,1)Use SEQUENCE with the start date as its start:
=SEQUENCE(B2,1,B1,1)listsB2consecutive dates from the date inB1. Format the cells as dates.How to list the months between two dates in Excel
=DATE(YEAR(B1),MONTH(B1)+SEQUENCE((YEAR(B2)-YEAR(B1))*12+MONTH(B2)-MONTH(B1)+1)-1,1)Count the months, then build the first of each with DATE:
=DATE(YEAR(B1),MONTH(B1)+SEQUENCE((YEAR(B2)-YEAR(B1))*12+MONTH(B2)-MONTH(B1)+1)-1,1).How to make a monthly calendar with a formula in Excel
=LET(s,B1-WEEKDAY(B1)+1,d,SEQUENCE(6,7,s),IF(MONTH(d)=MONTH(B1),d,""))Start the grid on the Sunday before the 1st and blank out other months:
=LET(s,B1-WEEKDAY(B1)+1,d,SEQUENCE(6,7,s),IF(MONTH(d)=MONTH(B1),d,"")), formatted as d.How to list only weekdays between two dates in Excel
=WORKDAY(B1-1,SEQUENCE(NETWORKDAYS(B1,B2)))Count the weekdays with NETWORKDAYS and step through them with WORKDAY:
=WORKDAY(B1-1,SEQUENCE(NETWORKDAYS(B1,B2))).How to generate a sequence of numbers in Excel
=SEQUENCE(B1,1,B2,B3)Use SEQUENCE(rows, columns, start, step):
=SEQUENCE(B1,1,B2,B3)returnsB1numbers starting atB2, counting up byB3.How to get every nth row in Excel
=FILTER(A2:B13,MOD(SEQUENCE(ROWS(A2:A13)),E1)=0)Keep the rows whose position divides by n:
=FILTER(A2:B13,MOD(SEQUENCE(ROWS(A2:A13)),E1)=0)returns every 3rd row whenE1is 3.How to reverse the order of a list in Excel
=SORTBY(A2:A9,SEQUENCE(ROWS(A2:A9)),-1)Sort by the row positions, largest first:
=SORTBY(A2:A9,SEQUENCE(ROWS(A2:A9)),-1)returns the list from bottom to top.How to make a multiplication table in Excel
=A3:A12*B2:K2Multiply a column by a row:
=A3:A12*B2:K2fills the whole 10 by 10 table from one cell.How to return the first 3 matches with FILTER in Excel
=TAKE(FILTER(A2:C12,B2:B12=F1),F2)Wrap FILTER in TAKE:
=TAKE(FILTER(A2:C12,B2:B12=F1),F2)returns only the firstF2matching rows.How to combine two lists without duplicates in Excel
=UNIQUE(VSTACK(A2:A7,C2:C8))Stack the lists with VSTACK and remove the repeats with UNIQUE:
=UNIQUE(VSTACK(A2:A7,C2:C8)).How to stack two tables on top of each other in Excel
=VSTACK(A2:C5,E2:G6)Use VSTACK with both ranges:
=VSTACK(A2:C5,E2:G6)returns the rows of the first table followed by the rows of the second.How to add headers to a FILTER result in Excel
=VSTACK(A1:C1,FILTER(A2:C10,B2:B10=F1))Stack the header row on top with VSTACK:
=VSTACK(A1:C1,FILTER(A2:C10,B2:B10=F1)).How to convert a table into one column in Excel
=TOCOL(B2:E4,1)Use TOCOL:
=TOCOL(B2:E4,1)reads the table row by row into a single column, and the 1 skips empty cells.How to transpose data with a formula in Excel
=TRANSPOSE(A1:D4)Use
=TRANSPOSE(A1:D4): rows become columns, and the result updates when the source changes.How to reference a spilled range in Excel
=SUMIFS(C2:C11,B2:B11,D2#)Put # after the formula cell:
D2#means the whole spill from D2, so=SUMIFS(C2:C11,B2:B11,D2#)totals every region in that list.How to look up a list of values at once in Excel
=XLOOKUP(E2:E6,A2:A9,C2:C9,"Not found")Give XLOOKUP the whole list as its lookup value:
=XLOOKUP(E2:E6,A2:A9,C2:C9,"Not found")returns one result per item and spills them down.How to sum by category with one formula in Excel
=HSTACK(UNIQUE(A2:A13),SUMIFS(C2:C13,A2:A13,UNIQUE(A2:A13)))Pair UNIQUE with SUMIFS:
=HSTACK(UNIQUE(A2:A13),SUMIFS(C2:C13,A2:A13,UNIQUE(A2:A13)))returns each category with its total.How to make a pivot table with formulas in Excel
=LET(r,SORT(UNIQUE(A2:A13)),c,TRANSPOSE(SORT(UNIQUE(B2:B13))),VSTACK(HSTACK(A1,c),HSTACK(r,SUMIFS(C2:C13,A2:A13,r,B2:B13,c))))Give SUMIFS the unique regions as a column and the unique products as a row:
=SUMIFS(C2:C13,A2:A13,SORT(UNIQUE(A2:A13)),B2:B13,TRANSPOSE(SORT(UNIQUE(B2:B13))))fills the whole grid of totals.How to find the highest value for each category in Excel
=HSTACK(UNIQUE(A2:A13),MAXIFS(C2:C13,A2:A13,UNIQUE(A2:A13)))List the categories with UNIQUE and get each maximum with MAXIFS:
=HSTACK(UNIQUE(A2:A13),MAXIFS(C2:C13,A2:A13,UNIQUE(A2:A13))).How to get the latest record for each customer in Excel
=FILTER(A2:C12,B2:B12=MAXIFS(B2:B12,A2:A12,A2:A12))Keep the rows whose date is the newest for that customer:
=FILTER(A2:C12,B2:B12=MAXIFS(B2:B12,A2:A12,A2:A12)).How to split comma-separated values into rows in Excel
=TEXTSPLIT(A2,,", ")Use TEXTSPLIT with the delimiter as its third argument, the row delimiter:
=TEXTSPLIT(A2,,", ").How to get the top 3 values for each group in Excel
=SORT(FILTER(A2:C14,COUNTIFS(A2:A14,A2:A14,C2:C14,">"&C2:C14)<F1),{1,3},{1,-1})Keep a row when fewer than N rows in its group beat it:
=SORT(FILTER(A2:C14,COUNTIFS(A2:A14,A2:A14,C2:C14,">"&C2:C14)<F1),{1,3},{1,-1}).How to make a search box in Excel
=FILTER(A2:D10,ISNUMBER(SEARCH(G1,A2:A10&" "&B2:B10&" "&C2:C10)),"No results")Search all the columns joined together:
=FILTER(A2:D10,ISNUMBER(SEARCH(G1,A2:A10&" "&B2:B10&" "&C2:C10)),"No results")returns every row that contains the text typed inG1.How to return the entire row of the max value in Excel
=FILTER(A2:D10,D2:D10=MAX(D2:D10))Filter for the rows that equal the maximum:
=FILTER(A2:D10,D2:D10=MAX(D2:D10)). Ties all come back.How to sort one column by another with a formula in Excel
=SORTBY(A2:A9,B2:B9,-1)Use SORTBY: =SORTBY(A2:A9,B2:B9,-1) returns the names in A ordered by the values in B, largest first. The sort column does not have to be in the output.
How to get the last N rows of a table in Excel
=TAKE(A2:C9,-3)Use TAKE with a negative number: =TAKE(A2:C9,-3) returns the last three rows. A positive number returns the first rows.
How to sum each row with one formula in Excel
=BYROW(B2:F6,LAMBDA(r,SUM(r)))Use BYROW with LAMBDA: =BYROW(B2:F6,LAMBDA(r,SUM(r))) returns one total per row and spills down, so there is nothing to fill.