Forum Discussion
Solved
multiply data in a block of cells by constant
Hi All,
I need to update some prices in a pricelist I created in Excell some time ago. Is there a way to select a block of cells and multiply the data that is currently in those cells by a constant that will repopulate those cells with the product?
I see that there is a way to do this by the column, but I'm not seeing a way to perform this simple function on a block of cells.
TIA for your help!
Gary D
- Gary,
The easiest way is to use a separate set of cells with a function call. For example, if your data is in A2:C10 and you have a cell you want to multiply them all by, let's say in E2, in another block of cells which doesn't overlap, you would enter the following formula:
=A2*$E$2
You would then copy/paste this formula to a range of similar size/dimension.
You could also use copy/paste special/multiply. To do this, have the number in a cell you want to multiply by, we'll say E2, same ranges as the above example, and copy the cell. Then, select your data range (i.e. A2:C10) and Paste (Home tab, drop down arrow) > Paste Special > Multiply. The keyboard shortcuts for these are CTRL+C for copy, and ALT+E, S, M, ENTER for paste special multiply. What this does is multiplies all values in your selection by the value you copied. If these are static values they will remain so. If these are formulas they will have the multiplication appended to the formulas. If the range is mixed, each changes respectively - static values stay static, formulas are appended.
HTH
4 Replies
if you have a sample of date so i will give you solution
- Thank you. I was able to use Zack's response to quickly resolve my issue.
- Gary,
The easiest way is to use a separate set of cells with a function call. For example, if your data is in A2:C10 and you have a cell you want to multiply them all by, let's say in E2, in another block of cells which doesn't overlap, you would enter the following formula:
=A2*$E$2
You would then copy/paste this formula to a range of similar size/dimension.
You could also use copy/paste special/multiply. To do this, have the number in a cell you want to multiply by, we'll say E2, same ranges as the above example, and copy the cell. Then, select your data range (i.e. A2:C10) and Paste (Home tab, drop down arrow) > Paste Special > Multiply. The keyboard shortcuts for these are CTRL+C for copy, and ALT+E, S, M, ENTER for paste special multiply. What this does is multiplies all values in your selection by the value you copied. If these are static values they will remain so. If these are formulas they will have the multiplication appended to the formulas. If the range is mixed, each changes respectively - static values stay static, formulas are appended.
HTH
