123 useful answers · Page 5 of 6
Work through formulas, formatting, sorting, and everyday data tasks.
Search this topicUse =SEQUENCE(4,3,1,1) to create four rows and three columns containing 1 through 12. The arguments are rows, columns, starting value and increment. Place it in a clear area so all twelve cells can spill. Changing the source formula changes the entire grid; edit the top-left formula cell rather than trying to modify one generated cell.
If your first record is in row 2, =ROW()-ROW($A$2)+1 returns 1 on that row, 2 on the next and so on. Copy it down through the data. This is positional numbering, so moving or inserting rows can change the numbers. Use stored IDs instead when each record must keep a stable identity regardless of sorting or layout changes.
Use =COLUMN(D5) to return 4, because D is the fourth worksheet column. With no argument, COLUMN returns the position of the formula's own column. This can support repeated layouts, but a worksheet column number is different from a position within a selected table. Subtract the table's starting column when you need a relative index.
Use =ROWS(A2:D50) to return 49. ROWS measures the shape of the range and includes blank rows; it does not count completed records. Use a value-counting function when occupancy matters. The distinction is useful when sizing an output: a four-column range can still have 49 rows regardless of how many cells contain numbers or text.
Use =COLUMNS(B2:F20), which returns 5. It counts the range's width, including empty columns, rather than populated headings. This can help size a formula result or check an imported table's structure. If you want to count completed header labels, use a nonblank count on the header row instead and check for formulas that only appear blank.
In Microsoft 365 or Excel 2024, use =TAKE(A2:C50,5). It returns the first five rows of the supplied range without changing the source. To show the five largest amounts, sort by the amount column before applying TAKE. A top-five position is not the same as a top-five value unless the input has the intended order.
Use =TAKE(A2:C50,-5) in Microsoft 365 or Excel 2024. A negative row count starts from the end of the supplied range. If A2:C50 includes unused blank rows, those blanks can be included in the result. Use a table or a filtered range that ends at real records before treating the last rows as your latest entries.
Use =DROP(A1:C20,1) in Microsoft 365 or Excel 2024 to return the supplied range without its first row. The original cells stay unchanged. It is useful when combining data sources whose headers have already been handled elsewhere. Verify that every source actually has the same number of introductory rows; blindly dropping one can remove real data from a headerless file.
Use =CHOOSECOLS(A2:F20,1,4,2) in Microsoft 365 or Excel 2024 to return source columns A, D and B in that order. The numbers are positions inside the supplied range. This creates a formula result rather than moving the original columns. Give the output matching headings, and recheck the positions if the source table's layout changes.
Use =CHOOSEROWS(A2:C20,1,3,5) in Microsoft 365 or Excel 2024 to return the first, third and fifth rows of that range. Those are relative positions, not worksheet row numbers. The result updates when source values change. If rows must be selected by an ID or status rather than position, use a lookup or filter instead.
Use =VSTACK(A2:C10,E2:G8) in Microsoft 365 or Excel 2024. The second block appears below the first. Make the columns represent the same fields in the same order, and keep repeated headers out of the input ranges. VSTACK combines positions; it does not automatically match columns by their names or remove duplicate records.
Use =HSTACK(A2:B10,D2:E10) in Microsoft 365 or Excel 2024. The resulting columns appear next to each other, aligned by row position. This is not a database join: unrelated records can be paired if their row order differs. Match records by a stable identifier first when the ranges describe separate lists, and check their heights before combining them.
Use =ISBLANK(A2). It returns TRUE only when the referenced cell is empty, so a formula returning an empty string is not considered blank. For a visual-empty test that includes those formulas, A2="" may better match your purpose. Spaces still count as content. Pick the test according to whether you care about stored contents or what readers see.
Use =ISNUMBER(A2). It returns TRUE for numeric values, including dates and times, but FALSE for a text string such as an imported "123". That distinction can explain why a calculation skips an apparently numeric entry. Do not automatically convert every FALSE result: postal codes, account references and other identifiers may intentionally be stored as text.
Use =ISTEXT(A2) to return TRUE for text values and FALSE for numeric values or genuinely empty cells. A formula returning an empty string counts as text, even if nothing appears on screen. This is useful for diagnosing mixed data types after an import. It does not tell you whether the text is meaningful or whether a required field is complete.
Use =ISERROR(A2) to check whether A2 evaluates to an error. You can use that test in a helper column or a conditional-formatting rule. It includes #N/A as well as other error types, so distinguish expected missing matches from broken calculations when reporting problems. Keep the original error visible during diagnosis instead of immediately replacing every flagged cell.
Use =FORMULATEXT(B2) to show the formula stored in B2 as text. This is useful for a workbook explanation or a side-by-side formula audit. If B2 does not contain a formula, the function can return #N/A. Displaying the formula is different from copying its result, and protected or unavailable source formulas may not be retrievable.
In Excel supporting LET, assign a name to a repeated intermediate value. For example, =LET(net,B2-C2,IF(net>0,net,0)) calculates the difference once and uses the name net in the final expression. Choose names that do not look like cell references. Test the original and rewritten formulas on the same inputs so a readability change does not alter the intended result.
Select the cell below the rows and to the right of the columns you want to keep visible, then choose View → Freeze Panes → Freeze Panes.
In “How to freeze rows and columns in Excel”
The selection determines the boundary. Unfreeze, choose the cell immediately below and to the right of the desired area, and try again.
In “How to freeze rows and columns in Excel”
No. Worksheet protection is a different feature.
Copy the data first. Select the full table, choose Data → Remove Duplicates, and select the columns that together identify a duplicate record.
In “How to remove duplicates in Excel without losing the wrong rows”
Yes. Conditional Formatting → Highlight Cells Rules → Duplicate Values can help inspect repeated values first; that is not the same as checking a multi-column record key.
In “How to remove duplicates in Excel without losing the wrong rows”
Check the selected columns and differences in displayed values, including spaces and formatting. Compare a specific pair before applying broader cleanup.
Source checks for these answers: September 21, 2026. Product instructions and local requirements can vary.