Do Math On The Fly In Paste Options

do math on the fly in Excel with paste options

One annoyance with using the Google Analytics API is that it doesn’t format percentages correctly. Instead of coming in as fractions that you just format as a percentage, they export as whole numbers.

Sure, you could create a new column next to your data where you divide the number by 100 and then format as a percentage. But then you have to copy and paste as values and paste back it into the cells where the original numbers lived.

Pfft. I don’t think so.

Excel gives you the ability to do math right on top of your data. I’ll demonstrate by taking a set of fictional values (non-union actors) and dividing them by 100.

Step 1: Enter 100 in a random cell.

Step 2: Copy it to the clipboard.

Step 3: Select your data range.

Step 4: Right-click and choose Paste Special.

Step 5: Choose divide from the Paste Special dialog.

doing math on the fly in Excel

Screenshot from Mac 2011 but PC dialog is very similar.

 

Step 6: Now you can format those values as percentages by pressing Ctrl/Command-1 and choosing it from the formatting options or using the number formatting icons under the Home tab.

Bug Warning: There’s a bug in Excel 2010 that you may run into. This post from the Microsoft Community addresses it.

Photo by wadem.