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:

Microsoft Excel chart, screen grabbed and annotated with Apple Preview.

Microsoft Excel chart, screen grabbed and annotated with Apple Preview.

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:

Microsoft Excel chart being annotated with Evernote Skitch.

Microsoft Excel chart being annotated with Evernote Skitch.

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.

Comments

  1. #1 Carl Feagans
    United States
    June 14, 2014

    I’ve been known to copy a chart then paste as a Windows Metafile (when I’m forced to use Windows). Then I can paste into Word and annotate using Word’s shape tools & text boxes. When I don’t want anyone screwing with my annotations, I’ve enlarged to maximum, taken a screenshot with ALT-Printscreen, then pasted back into Word one more time. Crop the extraneous stuff down to the graphic and you have a single image. I’m often pressed for time and this is the down-and-dirty methods I use given the meager application set my employer provides.

  2. #2 Richard Chapman
    June 14, 2014

    The Jekyll and Hyde nature of Excel has much to do with the internal organization of Microsoft. The interface between departments and divisions isn’t always smooth. In fact there’s kind of turf war mentality going on.

    Your search for an intelligent spreadsheet would not be complete if you did not try Libre Office from time to time. Yes, it does lag behind Excel in features, for now. But at some time in the future Libre Office will leave Microsoft Office in the dust. It’s a simple matter of the World developing an office suite vs a very large company doing it. In the end the World will win out no matter how large the company’s lead was. The World’s office suite will have more features, will be more stable and it’s calculated results more trustworthy. Speaking of trustworthy has Excel fixed their notorious date problem? If not date arithmetic is useless in Excel.

  3. #3 Greg Laden
    June 14, 2014

    I use Libra Office/OpenOffice Calc all the time. It does lag behind Excel in certain features. Gnumeric, by the way, has (or at least had) many features not found in Excel, mainly in the functions.

    I remember the date problem. That must be fixed by now, right? I do not actually know, though.

  4. #4 Paul Foord
    June 14, 2014

    re numbers as text – if the problem is “spaces” – select the relevant cells and search and replace spaces with nothing. I have a problem importing numbered text where there is a “/” in the number, eg., 10/10. I don’t want Oct 10, I want 10/10. There I resort to a text editor to break it apart,

  5. #5 Greg Laden
    June 14, 2014

    Paul, yes, one can try that. However, under the quirky conditions referred to here, that does not work either. If that worked some of these other things that don’t work might also work.

  6. #6 Peter Smith
    June 15, 2014

    I stopped using Excel so long ago that I can no longer comment about it. Libre Office works for me. The key difference between proprietary and open source software seems to be direct access to the developers, in the case of open source, and consequently their greater responsiveness. I found this when I had problems with the JFS and NILFS file systems, developed by IBM and NEC, respectively. I was able to interact with the lead designers and they took me seriously. By contrast, the designers at MS are hidden behind an opaque veil. You have zero chance of influencing them.

    After a career in corporate management I understand the reasons all too well. One becomes deeply concerned about presenting a uniform and consistent public relations message. It is better to hide the developers away and not disturb the well thought out message we put out for public consumption! Who knows what those loose cannons might say! Then there is the all important matter of staff meetings, project coordination meetings, motivation meetings, management meetings, planning meetings, budget meetings and review meetings. The business would grind to a stop if we failed to perform these necessary administrative procedures, not to mention the performance reviews, disciplinary processes and salary reviews. No, the customer is a distraction from the real work and in any case we know what we want and customers are just plain confused. You can’t get any useful direction from them.

    No, I am not kidding. This is my real life experience of working for a very large international group on three continents. It was no accident they standardized on the use of MS products, given the comfortable similarity of approach to business.

  7. #7 David
    June 15, 2014

    =value(trim(A1)) removes the spaces and converts to numeric

    Copying an image of a graph and working with it in a different program creates another problem: if expanded, the fonts and lines develop jagged edges, and if expanded or contracted in one direction only (for instance, to fit in available space in a report, or for printing on a specialized paper size) the “aspect ratio” of text changes in unpleasant ways. Copy the graph object, and use powerpoint “paste special,” selecting one of the metafile formats (or any other software that allows you to specify the paste format as a metafile). Then annotate in powerpoint.

  8. #8 Gary S
    SoCal
    June 15, 2014

    I sometimes annotate in Excel on Windows, but other times I just paste into MS Paint – simple but effective. My Windows “text editor” of choice is Notepad – it really results in just text. But for changing all spaces to nothing, I use MS Word since I can “Change all” in one operation. Then just make sure you save as text only.!

  9. #9 Greg Laden
    June 15, 2014

    David, the circumstance I’m related to here is special because those things don’t work. Due to a bug. Also, one might prefer to use the external editor for other reasons.

    The method you suggest for graphics is certainly doable, though requiring the use of PowerPoint is problematic if one prefers not to use that software. But a metafile can be manipulated in other ways. None of which are quick and dirty. Clearly if the fast fix suggested here produces bad results than one is stuck not being able to use it.

  10. #10 Bftcpa
    June 15, 2014

    Excel 2014 is coming… And if it includes half off the power bi stuff that’s in the Windows version, it’s going to be amazing.

  11. #11 dean
    June 15, 2014

    Have they fixed the problems with the statistics portion?

  12. #12 GregH
    June 19, 2014

    Here’s a handy AutoHotKey macro that addresses a couple of these problems, at least in Windows:

    =================
    #c::
    Clipboard = %clipboard%
    MsgBox %Clipboard%

    #c up::Send {Escape}

    #v::
    run notepad.exe
    WinWaitActive Untitled – Notepad
    send ^v ;`%clipboard`%
    return
    =================

    1. Download & install Autohotkey
    2. Create a new AHK macro by pasting the text between the double lines into a text file named “ClipMSG.ahk” (or whatever).

    When running, pressing the Windowskey + C opens a message box showing the contents of the clipboard, trimmed left and right. Letting go the C key closes the message box.

    Pressing Windowskey + V pastes the trimmed clipboard text into a new notepad file.

    I know this isn’t much good for multiple operations, but I use it all the time for general stuff at work.

    There are sample image viewers for AHK that do similar things for image files.

  13. #13 Greg Laden
    June 19, 2014

    Nice

  14. #14 Jon Peltier
    United States
    June 20, 2014

    Excel on the Mac is less powerful, less useful, and less easy-to-use than the Windows version, and MS doesn’t always seem to give it their full attention. But I couldn’t imagine you couldn’t draw shapes on a chart.

    In fact, you can, you just have to know where they hid the shapes. Select the chart, then on the regular menu at the top of the window, click Insert > Picture > Shape…, and the dialog with all the shapes appears.

    I don’t know why you have to go through a step called “Picture”, which probably kept many users from finding Shapes. I also don’t know why there isn’t anything on the ribbon to handle this.

    In Windows Excel, there’s a tab on the ribbon called Insert, which includes shapes, charts, and other objects. Much easier to find what you want.