How to mass multiply or divide all values in a column by a number in Excel?
For a list of numbers, you may need to multiply by a certain number for each of them as below screenshot shown. Normally, you need to calculate them one by one with formulas. In this article, we will show you two methods to quickly multiply or divide all values in a column by a number at once in details.
Mass multiply or divide all values in a column by a number in Excel
You can multiply or divide all values in a column by a certain number as follows.
1. Enter the certain number in a blank cell (for example, you need to multiply or divide all values by number 10, then enter number 10 into the blank cell). Copy this cell with pressing the Ctrl + C keys simultaneously.
2. Select the number list you need to batch multiply, then click Home > Paste > Paste Special. See screenshot:
3. In the Paste Special dialog box, select the Multiply or Divide option in the Operation section as you need (here we select the Multiply option), and then click the OK button.
You can see the operation result as below screenshot shown.
Easily multiply or divide all values in a column by a number with Kutools for Excel
The Operation tool of Kutools for Excel will help you easily mass multiply or divide all values in a column or range by a certain number in Excel. Please browse for more details.
1. Select the column with values you need to batch multiply or divide by a number, then click Kutools > More > Operation. See screenshot:
2. In the Operation Tools dialog box, select the operation type you need in the Operation box (here we select Multiplication option), enter the certain number for the multiplication operand into the textbox, and then click the OK button.
Then all selected cells are multiplied with a certain number at once.
You can create formulas for the list with checking the Create formulas box.
If there already have formulas in the list, you can skip calculating these cells by checking the Skip formula cells box.
If you want to have a free trial ( 30-day) of this utility, please click to download it, and then go to apply the operation according above steps.
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
More than 300 powerful features. Supports Office/Excel
2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features
30-day free trial. 60-day money back guarantee.
I am trying to create a formula that will multiply certain numbers within a range (1-9) by specific numbers. For example if there is a 1 in a cell, I would like that to be multiplied by 24, if there is the number 2 in a cell I would like that to be multiplied by 13.5, if the number 3 is found in a cell I would like that to be multiplied by 10, the number 4 to be multiplied by 8.5, number 5 multiplied by 7.4, number 6 multiplied by 6.67, number 7 multiplied by 6.29, number 8 multiplied by 5.88, and number 9 multiplied by 5.56.
Essentially I am looking for this formula to convert numbers 1-9 to a scale of 50 and I am struggling with how to do that using conventional formulas found in excel or google sheets.
Anything you can do to help would be greatly appreciated. Thank you!