Tables should default to "My table has headers" being ticked
It's taken me 10 years to realise this but I've never "unchecked" My Table has headers, it's ALWAYS the other way round, forgetting to remember to tick it because Excel hasn't recognised the header row.
Let's default the tick to always on ?
And here I want the opposite - I NEVER sort by header rows. EVER. Excel usually knows this, but every now and then, using the SAME data sheet, it magically assumes that there IS a header row when there isn't Absolutely annoying. Many f-bombs are dropped when I yell at Excel. I want a settings feature that says "never assume row headers when sorting."
I also agree with Jan Karel that the dialog box should ask for a table name (but can default to Table1 etc just like PivotTables do).
Also, why not ask if my data already has a total row? (not as common as headers, but still useful to ask at this point).
No thanks. I have lots of data I export from a particular system which comes without headers (and the import process also does not require them as the format is fixed).
When I convert to a table it is very useful for it to see that I have no headers, and insert a row for me to put names into. Depending on what I am doing I sometimes don't need to rename them, I might just want convenient filters, or quick formulas that are copied down for me. I don't want to lose my first row of data by mistaken assumptions that it is a header.
This is more about understanding the existing criteria by which Excel "guesses" if you have a header or not. Some comments suggest using some basic heuristics, which Excel already does.
Some examples which recognise headers:
Top row is bold, italic or underlined* ("BIU" hereafter) and at least one other row is not (you can even have lots of bold or italic, even the majority, and headers are assumed, but not if all data is formatted the same)
Actually, one cell of the top row is BIU, but not the whole of that column is enough - so if just the top left cell of all your data is bold, Excel assumes headers.
Actually, pretty much any cell with formatting which does not match everything else will trigger headers. Number formats, cell colour, font name, size or colour all trigger it. (Borders have a special case). One cell anywhere which is red, or in currency format, will trigger headers.
Top row of a column contains text, at least one other cell in that column contains a number or date (very common).
Top row of a column contains a number, at least one other cell in that column has text or a date.
These last two seem to work regardless of number formatting, so if everything is "General" it's fine. If everything is "Text" it works too (ie even if the numbers are formatted as text)
Borders are very specific:
If the top left cell (R1C1) has a bottom border, or the one below that (R2C1) has a top border (same thing), or (weirdly) if R2C1 has a *bottom* border, Excel assumes headers. All other borders, even in the top row, are irrelevant as far as I can see.
A special case where Excel assumes no headers regardless of other 'triggers':
One or more cells of the top row is blank (with non-blank cells elsewhere in that column)
The single most common scenario I see where Excel does not identify a header row correctly is for a numeric column with a number or date as the heading. Most often a year as the heading but I could imagine dates too. For example, in a spreadsheet of actual sales, I may have columns for 2018, 2019, 2020. No more text needed in these labels as the whole context is "actual sales". Other columns are likely to be all text such as customer, product, region, etc. If everything is formatted as "general", no headers will be detected. Changing value cells to use number formatting rather than general would be enough to trigger headers (as would changing number of decimal places or changing to currency, for example).
So the simplest fix is to learn that the shortcut for converting to a table is "Ctrl+B, then Ctrl+T" (or "Ctrl+B, then Ctrl+L" if you have the old muscle memory for Lists like I do).
*PS: Only sociopaths use "underline" as formatting in spreadsheets, rather than cell borders.
yes it should and it should be easier to find to set up. Can we have an icon on the ribbon please thank you
I hate has header row, and have to change it back too often. Shouldn't a stick option keep everyone happy?
Simply make it a remembered per user option?
Matthew Cowan commented
Every time I create a table in Excel it has the "My table has headers" box checked as the default. Are you operating off of an older version?
Stephen Cunliffe commented
It would be very easy to apply some basic heuristics to at least guess if the table already has headers. (e.g. if the top cell is text, but the one's underneath are numbers or dates, or known string values (Jan/Feb/Mar...)
Wyn Hopkins commented
Great tip Jon, it appears to work if the top cell is formatted differently in any way (background, font etc).
Jon Acampora commented
So in your example, if the active cell is in the top row you can hit Ctrl+B then Ctrl+T to check the box. Fun workaround... :-)
Jon Acampora commented
Great suggestion! If the text in the header row is bold then the box will be checked by default.
Wyn Hopkins commented
Yes to the table name box and everything else Jan Karel said
Ingeborg Hawighorst commented
Jan Karel Pieterse commented
Fully agree. And lets have that table name box in that dialog too. Like in my TableTools utility. And while we're at it: if I rename a table, can you aslo rename the table in PowerQuery queries?
All of this is part of my TableTools add-in. https://jkp-ads.com/download.asp#TableTools