Annielytics.com

I make data sexy

  • About
  • Resources
  • Services
  • Blog
  • Contact
  • Log In

Nov 20 2012

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.

Written by Annie · Categorized: Excel · Tagged: Excel Formatting, Google Analytics, Google Analytics API

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

CAN I HELP YOU FIND SOMETHING?

‘MAKING DATA SEXY’ IS LIVE ?

Want to learn how to create compelling, aesthetically pleasing charts in Excel? Learn how with Making Data Sexy.

Note: Also offered for Mac

STUFF I BLOG ABOUT

LEARN HOW TO USE ALL THE TOOLS

marketing strategy guide

DIY marketing strategy guide. This guide provides step-by-step instructions on how to perform 66 unique marketing tasks using 15 reputable marketing tools (both free and paid). Steal it for $295! Learn more.

LEARN TO DO A SITE AUDIT

site audit template

DIY site audit template. 20 sections, 215 checkpoints, 100+ explainer graphics, 218 pages, step-by-step instructions. Steal it for $295! Learn more.

LEARN TO DO AN ANALYTICS AUDIT

analytics audit template

DIY analytics audit template. 8 sections, 61 checkpoints, 100+ explainer graphics, 205 pages, step-by-step instructions. Steal it for $295! Learn more.

TO THIS DATA I DO THEE WED

dashboard course

Learn to build dynamic dashboards in Excel with Google Analytics data. 16 hours of video, 3 sample dashboards, 142-page workbook, practice Excel file, and more! Learn more.

FOLLOW ME ON TWITTER

SUBSCRIBE TO MY YOUTUBE CHANNEL

Privacy Policy
  • Email
  • LinkedIn
  • Twitter
  • YouTube

© 2022 annielytics.com