There was a time when I was the go to guy for anyone with Excel questions. I was using spreadsheets before they existed, and always kept up with the latest versions, and learned all the tips and tricks even if I didn’t need them. Later on, I started using other tools to do some of that work, notably r and other statistical packages, and about the same time I dropped Microsoft and switched to Linux, where I mainly used gnumeric if I needed a spreadsheet. Recently, I put Microsoft Office on my Mac and, knowing that of all the various products Microsoft has ever produced, Excel is probably the most impressive and useful and advanced, I’ve been using Excel now and then since.
And of course, I discovered that while I was not looking they broke Excel. There are two things that Excel does badly now, and I speak here of Excel for Mac 2011 14.4.1 specifically, because at least one of these problems is limited to that version.
One is annotating charts. You can’t any longer do so easily. At one time in the past you could draw shapes and lines and stuff on a chart you had made in your spreadsheet. My understanding is that the functionality is still there but to get access to it you have to use Visual Basic to fix a toolbar. Why? WHYYY!?!??
The other problem is that Excel now mistakes numbers that are formatted as text under conditions where it should figure out you are treating the number-as-text as a number. I’ve got kludges for both of those problems.
How do I annotate or add lines or graphic elements to an Excel chart?
If excel isn’t giving you the ability to annotate it’s charts, don’t fret. The way you would annotate a chart with excel already sucked anyway. Chances are if you want to draw arrows and stuff on an Excel chart, you’ve finished making the chart and are just fixing it up. So, to annotate it, cheat.
First, make the chart like you want it and enlarge it so it takes up a good part of your screen real estate. Then, do one of two things.
Option A: Take a screen shot, preferably the kind with crosshairs (on a Mac that’s with shift-command-4) so you don’t have to crop it.
Option B: Use Skitch to take a “crosshairs snapshot” of the chart.
Now, both of these methods are kludges, and probably only useful on a Mac which has the broken version of Excel where annotating a chart requires a degree in computer science. So, using the Mac, if you’ve done Option A above, then just open the image you grabbed in the built in “Preview” and annotated it there.
Yes, folks, Apple preview can annotate!
Here, I’ve taken a graph of global surface temperatures since 1880 and added an arrow and some text about a volcano:
If you use Skitch, which is part of the Evernote suite, you will get a different look and feel. Here is an annotation with Skitch in progress:
Of course, once you’ve decided to take the chart out of Excel and mess with it that way, there are myriad software choices including all your favorite graphics software. These two choices are just among the quickest and easiest.
Microsoft Excel Won’t Let Me Make A Chart From My Data …. also known as “My numbers are being treated like text and I can’t fix that without retyping all of it!”
This is a perennial issue. Some text in Excel is text, some is numbers, and they are obviously treated very differently. But sometimes, often after an import, stuff that looks like numbers and that is supposed to be numbers is really text. The current version of Excel for the Mac does not give you the old indicator of a single quote mark to tell you that the thing you think is a number is text. The function =VALUE(“text”) does not work on some of this number-as text, which is clearly a bug, and the old trick of placing a formula in the adjoining cell to multiply the number-as-text by the number “1″ (not the text “1″!) does not work either.
The problem here is that the number has gone into the cell with leading blanks and/or trailing blanks for some reason. Quite possibly you’ve used a fixed width import and the position of the breaks was not properly specified. There may be tabs involved.
So, here’s a quick fix using an external program. Copy the column of data with the text.
Paste it in a text editor.
When I said text editor just now, if you read it as “word processor such as Microsoft Word” then please note I mean text editor.
Then, you’ll see that your text has trailing or leading spaces, possibly quote marks, and quite possibly a tab or two.
Use the text editor’s search and replace to replace spaces with nothing, tabs with nothing, and quotes with nothing, as needed, to automatically turn the column of stuff that includes numbers into a column of good, clean, pure, numeral. Then copy that and paste it back into the column.
If you have multiple columns of text with this problem, you could fix it one column at a time, or you could copy and paste all of it at once and use your text editor to fix it, but that will take a bit of savvy on your part (but it is quite doable) to end up with, perhaps, a comma delimited file, or even a tab delimited file. That file can then be properly imported.
Or, of course, if the problem happened on import, you can always re-import. But if you’ve already fiddled with the data or the import is problematic, the out-to-text-editor-and-back kludge is fast.
Thank you that is all.