0

The screenshot should say it all really. Excel syntax bubble obscures column headers

I keep getting frustrated with the Excel syntax bubble obscuring the column headers. My headers are often narrow and my formulas often refer to whole columns, but I can't simply click them because they are obscured. Naturally, the bubble appears where it does because I disabled "Allow Editing Directly in Cells", but I am not prepared to put it back on (because editing in cell obstructs adjacent cells). The worst thing is that the bubble always shows over the column headers, even if the formula bar has empty rows. Finally, I acknowledge it is still possible to click over the tiny strip between the column headers and formula bar (highlighted green in the screenshot). BUT if any cell in the column selected is part of a merged cell spanning more than one column, then the range Excel enters into the formula spans all the columns involved, even if I only click on the single one I need, and it is impossible to see which column I actually need. So I always have to accept the formula, often with errors, and then go back and edit manually to narrow the range down to the single column I need. (NB The screenshot is taken with print screen rather than the snipping tool, as I needed to be able to show how excel uses green highlight to show where I'm hovering - I don't know how to achieve this with the snipping tool).

I can see a few ways this could be resolved:

  1. the bubble could appear below the cursor so stretching/scrolling the formula bar would indirectly control where the bubble shows
  2. the bubble could become transparent when I hover over it with the cursor
  3. the bubble could be toggled off, perhaps with a keyboard shortcut.

Does anyone know how to achieve any of the above, or are there any alternative solutions? Or at least please tell me it's not just me suffering from this? I got zero results from my google searches so far!

0

The only possible option from your list is to switch off pop-ups, however that's need to be done from options and can't be done during editing the formula.

Other option could be

  • select just one cell from the column you want to use then press CTRL+Space, that selects the whole column.
  • you can also scroll the sheet during editing the formula, so make the headers you need visible and selectable (it will not make the bubble disappear, but the column headers will move from behind it to be visible).
  • of course you can also type the address of the column (e.g. E:E)
  • Thanks for a quick response, but none of your suggestions seem to work. The link in your primary suggestion is for 2003/2007, and when I google for this, I don't get any results for 2016, and can't see it in 2016 options. Ctrl+Space does the same as clicking the header - if any cells in the column are merged across multiple columns, excel selects all columns involved. Finally, scrolling doesn't make the bubble disappear in Excel 2016. I appreciate your input though. Am I missing something? – pateksan May 15 '18 at 9:31
  • I've updated my answer – Máté Juhász May 15 '18 at 9:36
  • Thanks for your update - for those who haven't followed this thread, the addition was to type the address of the column (e.g. E:E). You see, this is the issue precisely - I can't see the heading. At the moment I am trying to memorize the headings I need before I start typing the function, but normally I fail miserably and end up counting from the last visible heading. Which is not very "superuser" if you know what I mean. – pateksan May 21 '18 at 14:37

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.