SOLVED

multiply data in a block of cells by constant

%3CLINGO-SUB%20id%3D%22lingo-sub-1342003%22%20slang%3D%22en-US%22%3Emultiply%20data%20in%20a%20block%20of%20cells%20by%20constant%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1342003%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3EI%20need%20to%20update%20some%20prices%20in%20a%20pricelist%20I%20created%20in%20Excell%20some%20time%20ago.%26nbsp%3B%20Is%20there%20a%20way%20to%20select%20a%20block%20of%20cells%20and%20multiply%20the%20data%20that%20is%20currently%20in%20those%20cells%20by%20a%20constant%20that%20will%20repopulate%20those%20cells%20with%20the%20product%3F%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EI%20see%20that%20there%20is%20a%20way%20to%20do%20this%20by%20the%20column%2C%20but%20I'm%20not%20seeing%20a%20way%20to%20perform%20this%20simple%20function%20on%20a%20block%20of%20cells.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3ETIA%20for%20your%20help!%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EGary%20D%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1342003%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1342079%22%20slang%3D%22en-US%22%3ERe%3A%20multiply%20data%20in%20a%20block%20of%20cells%20by%20constant%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1342079%22%20slang%3D%22en-US%22%3EGary%2C%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20easiest%20way%20is%20to%20use%20a%20separate%20set%20of%20cells%20with%20a%20function%20call.%20For%20example%2C%20if%20your%20data%20is%20in%20A2%3AC10%20and%20you%20have%20a%20cell%20you%20want%20to%20multiply%20them%20all%20by%2C%20let's%20say%20in%20E2%2C%20in%20another%20block%20of%20cells%20which%20doesn't%20overlap%2C%20you%20would%20enter%20the%20following%20formula%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3DA2*%24E%242%3CBR%20%2F%3E%3CBR%20%2F%3EYou%20would%20then%20copy%2Fpaste%20this%20formula%20to%20a%20range%20of%20similar%20size%2Fdimension.%3CBR%20%2F%3E%3CBR%20%2F%3EYou%20could%20also%20use%20copy%2Fpaste%20special%2Fmultiply.%20To%20do%20this%2C%20have%20the%20number%20in%20a%20cell%20you%20want%20to%20multiply%20by%2C%20we'll%20say%20E2%2C%20same%20ranges%20as%20the%20above%20example%2C%20and%20copy%20the%20cell.%20Then%2C%20select%20your%20data%20range%20(i.e.%20A2%3AC10)%20and%20Paste%20(Home%20tab%2C%20drop%20down%20arrow)%20%26gt%3B%20Paste%20Special%20%26gt%3B%20Multiply.%20The%20keyboard%20shortcuts%20for%20these%20are%20CTRL%2BC%20for%20copy%2C%20and%20ALT%2BE%2C%20S%2C%20M%2C%20ENTER%20for%20paste%20special%20multiply.%20What%20this%20does%20is%20multiplies%20all%20values%20in%20your%20selection%20by%20the%20value%20you%20copied.%20If%20these%20are%20static%20values%20they%20will%20remain%20so.%20If%20these%20are%20formulas%20they%20will%20have%20the%20multiplication%20appended%20to%20the%20formulas.%20If%20the%20range%20is%20mixed%2C%20each%20changes%20respectively%20-%20static%20values%20stay%20static%2C%20formulas%20are%20appended.%3CBR%20%2F%3E%3CBR%20%2F%3EHTH%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1342112%22%20slang%3D%22en-US%22%3ERe%3A%20multiply%20data%20in%20a%20block%20of%20cells%20by%20constant%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1342112%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F10842%22%20target%3D%22_blank%22%3E%40Zack%20Barresse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%26nbsp%3B%20Just%20what%20I%20needed%20and%20much%20appreciated.%3C%2FP%3E%3CP%3E%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1342137%22%20slang%3D%22en-US%22%3ERe%3A%20multiply%20data%20in%20a%20block%20of%20cells%20by%20constant%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1342137%22%20slang%3D%22en-US%22%3E%3CP%3Eif%20you%20have%20a%20sample%20of%20date%20so%20i%20will%20give%20you%20solution%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1345061%22%20slang%3D%22en-US%22%3ERe%3A%20multiply%20data%20in%20a%20block%20of%20cells%20by%20constant%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1345061%22%20slang%3D%22en-US%22%3EThank%20you.%20I%20was%20able%20to%20use%20Zack's%20response%20to%20quickly%20resolve%20my%20issue.%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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

4 Replies
Highlighted
Best Response confirmed by gdawsondesigns (New Contributor)
Solution
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
Highlighted

@Zack Barresse 

Thank you!  Just what I needed and much appreciated.

Highlighted

if you have a sample of date so i will give you solution 

 

Highlighted
Thank you. I was able to use Zack's response to quickly resolve my issue.