Steve Scott, Author at Spreadsheet Planet

How to Insert Multiple Rows in Excel

Rows 6 to 8 selected with the row headers, three whole rows for three new rows

If you need to add ten or twenty blank rows to a sheet, inserting them one at a time is the slow way to do it. And most people get …

Ampersand formula joining the street, apartment, city and state into one full address with comma separators

If you want to pull the contents of a few cells into one cell, Excel’s Merge & Center button won’t do it. It squashes them into one big cell and …

The number 1000 typed in a helper cell and copied, showing the moving dashed border

If you want to divide numbers in Excel, you’ll go looking for a DIVIDE function and there isn’t one. And this is because Excel handles division with a plain forward …

Page Break Preview with a dashed automatic break and a solid manual break over the greyed layout

If you want to control exactly where your Excel sheet splits when you print it, you can’t just hit Enter and hope for the best. Excel decides where the pages …

=NOMINAL(B2,C2) in cell D2 converting Riverside Community Bank's 9.38% effective rate into a 9.00% nominal rate

If you know a loan’s true yearly cost but need the stated annual rate behind it, you’re converting an effective rate back to a nominal rate. When interest compounds more …

=SORTBY(A2:D11,RANDARRAY(ROWS(A2:D11))) in cell F2, spilling the whole list back in a random order

If you want to shuffle a list in Excel into a random order, you will notice there is no single “shuffle” button anywhere on the ribbon. That is because Excel …

Excel scatter plot showing a downward trend line with the equation y = -0.2776x + 9.2755 and R-squared value of 0.9804

If you have two columns of numbers and you want to know how strongly one relates to the other, linear regression is the tool for the job. It fits a …

VLOOKUP against the helper column returning 67000 in cell J2 for the Finance and Evening criteria in H2 and I2

If you’ve ever tried to look up a value that depends on more than one condition, you’ve hit VLOOKUP’s biggest limitation. It only matches one lookup value against one column. …

The COUNTIFS formula in cell F2 returning 0.3 for the 0-100 bin, with 0.4, 0.2 and 0.1 filled down the column

If you want to know what share of your data falls into each group, relative frequency is the number you’re after. It turns raw counts into proportions, so a busy …

CONCATENATE in cell E2 with every cell from A2 to A11 listed one by one, separated by a literal comma and space

If you have a column of values in Excel and you want to turn it into a single comma-separated list, there are a few quick ways to get there. Copying …

The finished amortization schedule with both summary figures sitting below the last payment

If you have taken out a car loan and want to see exactly where each monthly payment goes, an amortization schedule is what you need. It splits every payment into …

The Auto Fill Options button expanded, offering Fill Days, Fill Weekdays, Fill Months and Fill Years

If you’ve ever copied a formula into one cell and then retyped it row by row, the fill handle can do that in one drag. It’s the small square at …

How to Insert Multiple Rows in Excel

How to Combine Cells in Excel

How to Divide in Excel

How to Insert Page Breaks in Excel

How to Calculate Nominal Interest Rate in Excel

How to Randomize a List in Excel

How to Do Linear Regression in Excel

How to Use VLOOKUP with Multiple Criteria in Excel

How to Calculate Relative Frequency in Excel

How to Convert a Column to a Comma-Separated List in Excel

How to Build a Car Loan Amortization Schedule in Excel

How to Use the Fill Handle in Excel