I have a table filled with datas that comes from other tables. The formulas in the cells of my table are quite simple (reference to a cell in another worksheet).

Table example

In my table, I have "Title" rows (rows 57 and 64 in that case) that contains only one text value that I would like to overflow to adjacent cells since the adjacent cells in those rows will always be empty.

If the values in the table where entered manually, there would be no problem and the cell would looks like exactly like I want it as in the image below.

Table has it would be if data was entered manually and has I want it

The value of the first cell in row 57 does not overflow because the adjacent cell containt a formula (the formula is basically "=OtherCell" where OtherCell does not contain any value (more specifically, a value of ""))

It is important to note that the position of the "title" row changes and occurs in many rows in the table since the data are automatically extracted a database.

How can I achieve this...

  • Without using manual intervention (those are automated reports, I can't delete formulas because the next report generated will be erroneous)
  • VBA can be used, but not in a way that modify the table in an "irreversible" way (in a way that makes that the next time the sheet is calculated, the data is erroneous), for the same reason as manual intervention.

I use Excel 2013, but I believe this question applies to 2007+ versions of Excel.

  • You could set column A to wrap text? Sure, you'll get multiple lines, but it's a trade-off between readability and uniform display. – Andi Mohr May 23 '17 at 10:39
  • I often have long titles, which would make the table look quite strange, so it is not a very good option. – Gabriel Caron L'Écuyer May 23 '17 at 10:51
  • Maybe you could write a VBA macro that would delete all formulas that yield an empty cell. – Jérôme Radix Jul 4 '19 at 9:49

The short answer is: You can't.

The long answer is that only truly empty cells will allow text from the next cell to the left to spill over and display in that cell.

This is just how Excel works.

If you specifically exclude manual intervention, deleting formulas, as well as VBA, there's really nothing left other than accepting the defaults, which is that a formula in a cell to the right will prevent text from spilling over to the right.

Don't shoot the messenger.

| improve this answer | |
  • I know about Excel's behaviour with cell's overflow as very well explained by @DanHenderson in this post : link. My question is more if someone knows of a workaround since it is something that has bothered me for years. VBA can be used, but not to delete formulas. – Gabriel Caron L'Écuyer May 23 '17 at 10:43
  • Or, more specifically, VBA can be used, but not in a way that modify the table in an "irreversible" way (in a way that makes that the next time the sheet is calculated, the data is erroneous). – Gabriel Caron L'Écuyer May 23 '17 at 10:54
  • Well, no, not with these restrictions. Maybe you want to post a suggestion on Excel.Uservoice.com. If it gets a lot of votes, it may be one of the next features the Excel team will work on. – teylyn May 23 '17 at 11:08

Using VBA, you can void cells containing a formula that yield nothing :

Private Sub CommandButton21_Click()
    For Each c In Worksheets("yourworksheet").Range("yourrange")
        If c.Text = "" Then c.Select: Selection.ClearContents
End Sub

Thus, adjacent cells can extend over empty cells.

| improve this answer | |

If you reallllly wanted to do it, sort of,you could make a complicated formula for the leftmost cell that should handle it. It would not let the table's data be used directly in further calculations, and the second and third columns would not really be used which might disturb a user. But it'd work to show the data AND the full entry in the first column. One could remove them, which might make it more palatable to users.

Since there are explicity numerical entries and alpha entries in the first column, but NO alphanumeric entries, one could first test for that with ISTEXT(). If NOT, then one has the current formula for that column AND the formulas for the second and third columns. One would need toTEXT()` wrap each and add spaces as required for appearance, but the look could be achieved, though with a fair bit of work.

If the test succeeds, then the cell simply has the text result it now has with no reading of the second and third columns, as they have nothing, as I understand it.

If one used a given number of spaces between first/second and a different number between second/third columns, that could be a reliable basis for pulling the data from the table for use elsewhere.

However, if being used elsewhere, one could also simply use the formulas used to poipulate this table directly, not looking up something in the table, but rather doing the first level lookup in the source data. So a direct lookup rather than a lookup in already looked up material. A bit "cleaner" in a way.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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