Auto Table Column1 Column2

[bobaol]

Hello, is there a setting to make this NOT occur? Excel automatically creates tables for me based on my data. I do not want Excel to create automatically create tables for me. Also, how do I get rid of all these tables? Please see picture. Any help is appreciated. Thanks in advance.

 

[Alex Blakenburg]

Excel does not automatically create table. Something needs to be triggering that to happen.
Are you by any chance using either of these shortcut keys Ctrl+T or Ctrl+L ?

 

[ides315]

Wow, that looks pretty cursed. Usually, Excel will not automatically create Tables when you are just typing data in. A Table has to already exist.

However, once a Table does already exist: if you enter something in the row immediately below the Table, then Excel can attempt to expand it to include the new data. My guess is that what has happened here is a mix of (a) that, and (b) copy-pasting rows which, over time, has led to duplicate copies of one original Table being inadvertently created.

There are a few different things you can to to revert a Table back to regular old normal cells:

1. Whenever your active cell is inside a Table, you'll see a contextual tab called Table Design on your Ribbon toolbar, which isn't usually there. For example, in the screenshot you provided, if you were to move up 2 cells to AC2139, you'd see it appear. Click this tab, and click the Convert to Range button to get rid of the Table you're currently in.
2. You can also select all the data, Copy it, and Paste Values somewhere else (like on a fresh worksheet). Copy the data (i.e. to where you see the marching ants around what you copied), right-click where you want to paste, and choose Paste Special... from the context menu. Then choose Formulas and number formatsfrom the dialog, and click OK.
   • If you need to keep the yellow highlights too: just Copy all the data again, right-click in the same place again, choose Paste Special..., and this time choose Formats instead. This will paste the old formats over the new data, without copying the Tables.
3. You can also run the following VBA macro, which will convert every table on the sheet back to regular cells. While it's at it, it will also go ahead & remove all those extra {"Column 1", "Column 2", ...} header rows, and shift everything up into the space they leave behind.

Sub UnlistAllTables()
    Dim oTable As ListObject
    Dim oHeaders As Range
    For Each oTable In ActiveSheet.ListObjects
        Set oHeaders = oTable.HeaderRowRange
        oTable.Unlist
        If oHeaders.Row > 1 Then oHeaders.Delete Shift:=xlUp
    Next oTable
End Sub

Before trying to run the macro: make sure to save a backup copy of the workbook, in case it does something you don't want. There is no Undo on macros!​

​

To add the macro: open the View tab, then click the Macros button on the far right. Type something random like "MyMacro" into the "Macro name:" box, then click Create. This will open the VBA editor. Highlight everything from before where it says Sub MyMacro() all the way through to after End Sub, and paste the above code over it. After pasting, you can exit the VBA editor.​

​

To run the macro: first, make sure you have the tab active with the cursed data on it. Then click the same Macros button as before. If you pasted the code correctly, now you'll see UnlistAllTables in the list box. Click it, and click Run. It will do its thing.​

​

After running the macro: you don't need to keep it. Having it present will prevent you from saving the workbook as a .xlsx - you'd have to save as a .xlsm instead, and many companies don't allow .xlsm files to be e-mailed around for security reasons, which can be inconvenient. When you next Save the workbook, you'll see a popup saying something like "The following features cannot be saved in macro-free workbooks: • VBProject". Just click "Yes" when this happens, and Excel will automatically remove the macro.​

Hope this helps you un-curse your workbook!