158 useful answers · Page 1 of 7
Use formulas, clean imported data, combine tables, and understand calculation errors.
Search this topicPut =ARRAYFORMULA(B2:B20*C2:C20) in an empty result column to multiply each row's quantity by its price. Leave the output cells clear so the array can expand. For a longer working range, use an IF condition to suppress results for empty rows. Do not type separate values inside the area controlled by the formula.
Wrap its result in ARRAY_CONSTRAIN. For example, =ARRAY_CONSTRAIN(A2:D100,5,3) returns the first five rows and three columns from that range. It does not sort or choose the best records; it takes the leading portion. Apply any required filtering or sorting first, and keep enough empty cells for the result to expand.
Use =FILTER(A2:D100,B2:B100="Open",D2:D100>100) to return rows that are both open and above 100 in column D. The condition ranges must align with the source rows. Keep headers outside the formula's data range unless you deliberately handle them. If nothing matches, FILTER returns an error rather than an invented empty record.
Enter =SORT(A2:C20,2,TRUE) in an empty area to sort the source rows by their second column in ascending order. The source data stays in place, while the formula's output updates as values change. Keep the destination clear, exclude the header row, and use FALSE instead of TRUE for descending order.
If names and scores occupy A2:B20, use =SORTN(A2:B20,5,0,2,FALSE). It sorts by the second column descending and returns up to five rows. The zero specifies the basic tie mode, so this is not a rule to include every tied contestant. Decide how ties should be handled before using it for a ranking.
Use =UNIQUE(A2:A100) in an empty column. It returns one copy of each distinct value, preserving the order in which values first appear. Check for stray spaces when entries that look identical remain separate. For a multi-column range, uniqueness applies to the complete row, so matching names with different accompanying values can both remain.
Enter =SEQUENCE(10) to produce a vertical list from 1 through 10. To start at 100 and increase by five, use =SEQUENCE(10,1,100,5). Leave the cells below empty for the results. These are formula-generated values, so changing the formula changes the list; paste values if you need a fixed snapshot.
Use =TRANSPOSE(A1:D3) in a clear destination area. The three source rows become three output columns, and the four source columns become four output rows. Keep the source and destination separate so they do not overlap. Because this is a formula, the transposed result changes when the original cells change.
Use =SPLIT(A2," - ",FALSE) to split on the complete space-hyphen-space sequence. The FALSE argument prevents Sheets from treating each delimiter character as a separate splitting character. Leave cells to the right empty for the pieces. Decide whether empty pieces matter before changing the optional remove-empty-text argument.
Use =JOIN(", ",A2:A6) to combine those cells with a comma and space between entries. Choose a bounded range so unused rows do not add unwanted separators. The result is one text value, not several editable cells. Use TEXTJOIN instead when you specifically need an option to ignore empty cells.
Use =TEXTJOIN(" ",TRUE,A2:C2) to join nonempty parts with spaces. This is useful for names that may have a missing middle name. TRUE tells the function to ignore empty entries; it does not correct extra spaces already inside a value. Check the final text before exporting it to labels or another system.
Use =FLATTEN(A2:B4,D2:D4) in an empty column. It lists values from the first range row by row, then continues through the next range. Empty cells are not automatically discarded, so expect gaps if the inputs contain blanks. Use a separate filtering step when you need a compact list containing only populated values.
Use =TOCOL(A2:D10,1). The second argument tells Sheets to ignore blank cells while collecting the values into one column. By default, it reads across each row before moving down. Add TRUE as the third argument if you want to scan down each column instead. Leave enough room below the result cell.
Enter =TOROW(A2:C5,1) in a clear destination row. It lays the values out horizontally and ignores blank cells with that second argument. The default scan follows rows; the optional third argument changes the reading order. Check that the expanded row will not run into existing notes or formulas to its right.
Use =WRAPCOLS(A2:A13,5,""). It fills each output column with up to five items before starting the next column, padding the final short column with empty text. This is useful for arranging a list for display. The formula does not create a separate printable label layout or adjust your page margins.
Use =WRAPROWS(A2:A11,4,"") in an empty area. Each output row receives four items, with empty text padding any unused positions at the end. This changes the display shape rather than the source list. Leave a clear rectangular destination, and keep related fields together if the original data contains more than one column.
Use =CHOOSECOLS(A2:F20,1,4,6) to return the first, fourth, and sixth columns of that range in that order. The numbers refer to positions within the input range, not absolute worksheet column numbers. Keep the output area empty. This gives a linked view. Edit the source cells to change the data rather than trying to overwrite individual array results.
Use =CHOOSEROWS(A2:D20,1,3,5) to return the first, third, and fifth rows within that range. Because the range begins at row 2, its first row is worksheet row 2. Check those relative positions carefully. For records selected by a changing condition, FILTER is usually more appropriate than hard-coded row positions.
Use =HSTACK(A2:B10,D2:E10) to place the two ranges next to each other in one formula result. Align their row counts and record order before combining them. HSTACK does not match customers or IDs for you; it simply appends columns. Use a lookup or another matching method when corresponding records are in different orders.
Use =VSTACK(A2:C10,E2:G12) in a clear area to append the second range underneath the first. Both ranges should use the same column order and compatible widths. Include a header only once if you do not want it repeated in the middle. VSTACK combines the supplied rows; it does not automatically remove duplicates.
Use =IMPORTRANGE("spreadsheet URL","Sheet1!A1:C20") with the source file's URL and an exact range string. Sheets may ask you to connect the files and allow access. You need appropriate source access. Import only the range you need, and remember that sharing the destination can expose imported values to its viewers.
Use =IMPORTHTML("page URL","table",1), replacing the URL and table number with the intended page and table. The number identifies a table in the page's HTML, not necessarily the first visual block you notice. It may not work for tables built only after JavaScript runs or pages that require a login.
Use =IMPORTDATA("CSV URL") in a clear area, replacing the placeholder with a publicly accessible CSV or TSV address. The returned data expands into cells. A link to an ordinary webpage or a login screen is not the same as a direct data file. Check the resulting columns and dates before using them in calculations.
For an accessible HTML page, try =IMPORTXML("page URL","//h1"). The XPath expression selects h1 elements from the returned markup, so more than one heading may produce multiple results. It cannot reliably read content that requires a logged-in session or is created only by client-side scripts. Check that the returned text is the content you intended.
Source checks for these answers: September 21, 2026. Product instructions and local requirements can vary.