Data cleaning & shaping
Transpose, fill blanks, merge columns, split a cell into rows, auto-number, build sequences and frequency distributions — the data-prep toolkit.
17 pages · 17 how-to
How-to guides
How to merge two columns
Combine two columns into one with the & operator — perfect for joining first and last names into a full name.
How to create a sequential invoice number
Automatically generate sequential invoice numbers with a prefix and zero-padded counter using TEXT and ROW in Excel and Google Sheets.
How to number rows within each group
Assign sequential row numbers that restart for each group using COUNTIF with a mixed reference in Excel and Google Sheets.
How to remove blank rows
Delete empty rows quickly by filtering for blanks and deleting visible rows, or use Go To Special in Excel for a one-click approach.
How to transpose rows to columns
Flip rows and columns using the TRANSPOSE function or Paste Special to convert horizontal data to vertical and vice versa.
How to combine multiple columns into one
Merge values from several columns into one cell using the ampersand operator or CONCAT/TEXTJOIN for delimited output.
Excel ≠ SheetsHow to fill blank cells with a value
Replace empty cells with a default value using IF+ISBLANK, Go To Special fill-down, or a Power Query step for bulk data.
How to number rows automatically
Add sequential row numbers automatically using ROW() so numbers update if rows are inserted or deleted.
How to create a sequential list of numbers
Generate a list of sequential numbers using ROW, SEQUENCE (Sheets/365), or a simple fill handle drag for quick setup.
Excel ≠ SheetsHow to generate random numbers
Generate random integers or decimals using RAND and RANDBETWEEN — both recalculate on every sheet change unless pasted as values.
How to create a drop-down list
Add a validated drop-down list to any cell using Data Validation to restrict input to a predefined set of options.
How to reverse the order of a list
Flip a list upside-down using INDEX with ROW arithmetic in Excel, or SORT with descending order in Google Sheets.
Excel ≠ SheetsHow to split a cell into multiple rows
Break a delimited cell value into separate rows using Power Query in Excel or SPLIT combined with TRANSPOSE in Google Sheets.
Excel ≠ SheetsHow to auto-number rows that have data
Assign sequential numbers only to rows containing data using IF with COUNTA, skipping blank rows automatically.
How to create a dynamic range with OFFSET
Build a range that grows automatically as you add rows using OFFSET and COUNTA — the basis of a dynamic named range for charts and formulas.
How to distribute a total evenly across rows
Distribute a total evenly across a fixed number of rows, handling remainder cents using MOD and ROW to ensure the distributed values always sum exactly to the original total.
How to generate a series of dates
Generate a date sequence in Excel or Google Sheets with SEQUENCE — produce daily, weekly, monthly, or custom-step series from a start date in a single formula.