When working with price lists, statistics, or other numerical data, you often need a simple operation — multiplying an entire column or range by a specific number. This page explains the easiest ways to do it.
Multiply a column by a number using standard Excel tools
There are two main ways to multiply a column or row by a number in Excel without add-ins: using formulas or using the Paste Special feature.
Using formulas
You can use the PRODUCT function, but it’s usually easier to use the asterisk (*) multiplication operator. PRODUCT multiplies all numbers in a range, and the result can change if you insert a column or row inside the range. With the asterisk, cells are multiplied individually, so inserting new columns or rows won’t affect the result.
To multiply an entire column by a number, you can copy the formula down the entire adjacent column.
Using Paste Special
This is a quick built-in option. First, copy the cell containing the number you want to multiply by. Then select your target column or range, right-click, and choose Paste Special → Multiply. Here’s an example:
Multiply a column in place with !SEMTools
To avoid creating extra columns and formulas, the !SEMTools add-in includes several procedures for multiplying a range in place, quickly and easily.
Multiply a column by a single number (constant)
The simplest and most common option. You can either select a cell containing the number or just type the number directly.
Multiply a column by a random number within a range
Useful when you need to randomize a dataset. This is handy for generating test data.
Multiply a column by a percentage
Multiplying by a percentage works the same as multiplying by a regular number. For example, multiplying by 120% to include tax is the same as multiplying by 1.2.
Do you often face this task in Excel?
With !SEMTools, there’s no need to write formulas or add extra columns — handle it in just a couple of clicks!
This post is also available in RU.