Tip: Excel Data Point Labels

There are some things in Excel that make my life so much easier - formulas for one. Being able to auto-update data and graphs is a cool feature, and Excel spreadsheets are easier to share than R code with accompanying data files. However there are other things that drive me mad. For example, the insistence of cells to auto-format any text that looks like scientific notation into a number. Since I work with 4-letter PDB structures with names like 2E50, there are plenty of times when my cut-and-pasted or imported data ends up as 2.00E+50. Of course hat particular annoyance can be overcome by explicitly setting the format of the destination cells to 'Text,' as annoying as that is to remember.

One thing that annoys me to no end however is the inability to add labels to scatter plot points. If you have X and Y values your only choices for the labels are either the name of the series or the values themselves. Usually I got around this either by doing my graphs differently or using R to make these 'fancier' plots, but today I finally needed to make a chart like that in Excel, and it was Google (not Bing) time! Luckily someone, actually a lot of people, already had this issue and there was a MS knowledge base article on it. Apparently the 'easiest' solution (apart from setting each label by hand by clicking on it and filling in the formula) is to write a VB macro that adds the labels brute-force in a one-time run. Here is the kb article for your reference, the sample data and the graph it makes:

"How to use a macro to add labels to data points in an xy scatter chart or in a bubble chart in Excel"

If you have data like such:

Residue	Price	Effective Pixels (millions)
Canon EOS 1D Mark III	$4,160.00	10.1
Canon EOS 5D Mark II	$2,629.00	21
Canon EOS 10D	$324.00	6.3
Canon EOS 40D	$917.00	10.1
Canon EOS 50D	$1,213.00	15.1

You can get a chart like this:

Sure it's not the pretties chart in the world, but it does the job. And with that, my love/hate relationship with Excel continues...

data from dpreview.com