I show a number of Excel Productivity to my customers every single day. One of the task includes getting some random number to show some charts or some formulae functions. I have used the RANDBETWEEN() function in the below example as an dataset. Assume we have a this range of numbers and now we want to copy ONLY the values from this range?
If you try to do anything with this Excel sheet, the random number is automagically generated. Not something we wanted :). Now the task is to copy ONLY the values into a different column.
First step is to Select the range of cells we want to copy. This can be any range in your workbook to start with. In our example we are using B2 to B6 as shown in figure below.
With the range selected. Move your mouse to the left side of the box. Do you see the “Cross Arrow” in the above figure? That is the trick.
Once you see the Cross Arrow, with your mouse in that over the box –> Right Click and Drag your mouse. You will see a range now where the values will land. In the below figure you see the D2:D6 for example. Now is the time to release the mouse.
Voila, now you will get an awesome popup inside Excel to play with. This gives us interesting options. Feel free to play with them as you wish. For our example, we wanted ONLY values, so I choose the 3rd option.
Now that lands the values, without the formulae into these cells. As you can see the values are copied, since we used the RANDBETWEEN() function – the values in our old cells are now regenerated. This is by design.
Our final output looks like above. You can use this technique in ANY range that contains range with formula. Do let me know if you used this trick anywhere in your Excel explorations.