Hello, Excellers welcome back to more #FormulaFriday #Excel fun. Today let’s look at how to add decimal places to a dataset of existing numbers. Really quickly. (Without using a formula). Here is our dilemma. So, every week a data set is supplied from my data provider. This data set is good, but every week the data needed to be updated. Decimal places need to be added. A sample of the type of data can be seen below.
I need to be able to alter or add two decimal places to the data set all at the same time. I do not want to have to carry out calculations on each of the cells in the Excel data set. So, how on earth can we do this with no formulas? We can use the Paste Special feature. Really?. Yes!.
Paste Special Overview.
The paste special options available in Excel are quite detailed and very useful. To access the paste special options you can either (after you have selected cells you want to paste) use the shortcut
- Use the shortcut Alt + E + S + V
- Use the Home Tab | Clipboard Group | Paste Special
- Right Click and select Paste Special
All options will lead you to the Paste Special options box.
You can see there are two sections the Paste sections and the Operation section. The paste section (in the green box above). Use this to paste your data with some selected options. The full list of options available to you is in the green box above.
The second section is the Operation section. It is really useful. Use these options to perform the available operations (Add, Subtract, Multiply or Divide) without having to write a formula. So, this is what we use to add decimal places to the weekly data set.
Paste Special To Add Decimal Places.
Now we know where to look and what options are available, let’s go ahead and add our decimal places.
- Insert 100 into a cell somewhere in your Excel worksheet. Or another Excel worksheet.
- Copy that cell that contains the 100 value.
- Select your data set that requires decimal places.
- Right -Click | Paste Special | Divide.
So, that’s it. The data set now has decimal places.
So, no formula, no fuss just decimalized data.
What Next? Want More Tips?
So, if you want more top tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
Do You Need Help With An Excel Problem?.
Finally, I am pleased to announce I have teamed up with Excel Rescue, where you can get help FAST.