You have created a nice analysis in Excel, but there is one problem: Values are shown in thousands and you want to convert them to millions. Or the other way around. Or you want to convert monthly values to annual values, multiplying them by 12. So, the problem: How to multiply existing Excel formulas by a factor in bulk. That means, many different cells at the same time.
One more challenge: the factor you want to multiply (or divide) with is given in an Excel cell. The formulas in the end should not have fixed values in them but rather a link to this cell. Let’s take a look at the example, first.
Example
Let’s assume the following situation. You have finished the analysis as shown below. The numbers are shown in thousands. But instead of showing them in thousands, you want to show them in absolute numbers. So, the task would be to multiply everything by 1,000. Sounds simple, right? Not that fast… we have two conditions:
- The existing formulas should remain intact (in this case simple links to another worksheet, but could also be more complex).
- The factor (1,000) to multiply with is given in cell … . You later on want to be able to change this factor so that you have to link to this cell instead of hard-coding it into the formulas.
Let’s see how we can solve this!
Method 1: Multiply by fixed, hard-coded factor and not cell reference
Excel offers a simple, built-in method to multiply existing formulas with a value from another cell. Before we go into detail, please note: You cannot insert links to existing cells. You can only multiply by the value from this cell. To demonstrate this: Your existing formula is =A1. Your multiplier is 1,000, given in cell C1. That means, you want to create =A1*C1. But with this method you can only achieve =A1*1000. If that’s fine for you, let’s continue. Otherwise please proceed with the next method.
- Copy the cell containing the multiplier by pressing Ctrl + C on the keyboard.
- Select all target cells and press Ctrl + Alt + V in order to open the Paste Special window.
- In the Paste Special window, select multiply…
- … and “Values” (or “Formulas”) if you want to keep the target cell formats.
- Confirm with OK.
Method 2: Find & Replace as a workaround
This method only works if all your target cells that you want to multiply don’t have )*1 within their formula texts. Probability is low, but please make sure…
- We first create a closed bracket at the end of the formula by a multiplication by 1. In order to do this we do similar steps as in our method 1 above. Start by writing 1 into an empty cell outside your target cells. Copy this cell.
- Paste Special window.
- In the Paste Special window, select multiply…
- … and “Values” (or “Formulas”) if you want to keep the target cell formats.
- Confirm with OK.
Now, the formula in cell C9 (originally =Calc_Charts!C38) is =(Calc_Charts!C38)*1. The trick now is to replace )*1 by )*C6 because the multiplier is given in cell C6. For divisions, additions and subtraction it’s quite straight-forward, for multiplication we have to regard that the star character * means “all” and not literally a star. If we want to search (and replace) the actual star character, we have to write the tilde character in front of it.
- Press Ctrl + H on the keyboard to open the Find & Replace window.
- For “Find what”, type )~*1 – if your formulas have a bracket at the end, type only ) for the Find what value
- For Replace with, type )*C6
- Click on Replace All to start.
Method 3: A VBA macro to multiply or divide cells by a cell reference
One word of caution before we start with the VBA macro method: There is no undo – maybe it’s time to save a backup of your file now. Besides that – let’s get started.
- Select all your target cells that should be multiplied by the multiplier.
- Copy & paste the following code into a new module (here is how to do that).
- Adjust the macro slightly: Type the reference to the multiplier (instead of “C6” here). Also, if you want to use a different method of calculation (division, etc.) replace the star character.
- Press start to run the VBA macro.
Sub multiplySelectedCells()
Dim tempFormula As String
Dim multiplier As String
'Set your multiplier here:
'Can be the cell reference, the name of a cell or a value
multiplier = "C6"
For Each cell In Selection
tempFormula = cell.Formula
tempFormula = Right(tempFormula, Len(tempFormula) - 1)
'Instead of multiplication you can replace the star * character by your desired calculation method:
tempFormula = "=(" & tempFormula & ")*" & multiplier
cell.Formula = tempFormula
Next
End Sub
Do you want to boost your productivity in Excel?
Get the Professor Excel ribbon!
Add more than 120 great features to Excel!
Method 4: Use an Excel add-in in order to comfortably multiply existing cells with values from another cell
Probably the most convenient method: Use an Excel add-in to multiply existing cells by a reference to another cell. Our Excel add-in Professor Excel Tools can do that – besides many, many other things…!
- Select your target cells.
- Click on “Calculation Operation” on the Professor Excel ribbon.
- Select the operation method (here: multiplication).
- Type the cell reference (or named range or value) and press start.
This function is included in our Excel Add-In ‘Professor Excel Tools’
(No sign-up, download starts directly)
More than 35,000 users can’t be wrong.
Alternatives: Is it really necessary to do a calculation operation in place?
Just display values in thousands, millions or absolute numbers
Before we start one question: Do we really have to touch or formulas and cell contents. In this case, there is also an easier approach: Just display the values in thousands / absolute numbers. So, we just change the formatting. And actually, that is also quite easy. Because I have written a whole article about this, why don’t you click here and check it out?
Please note: This only works for thousands, millions, and so on. Other multiplications, for example x12 for months to year or x7 for day to week are not really possible like this.
Do you have to do it in place? Can you just link to your cell range?
Another alternative could be to leave all your initial calculations intact and just link to them. But instead of just linking, you could multiply or divide with the factor. In the end, you could just hide the original cells (recommended by grouping, not by using the hide function…).
Image by Falk Schirrmeister from Pixabay
