excel tables column numbers

[sts8500man]

New to using Excel Tables. After converting data to a table, I noticed that not all the columns received sequential column numbers. I need to understand how that affects the efficacy of the data / worksheet. More specifically, table column number 1 is actually the fifth column in the table. Several next door columns were not given a column number. Table column number 2 is actually the 10th column. Also, two columns at the end of the table did not receive numbers. Everything seems to work OK. The VBA macros work correctly. The calculations from formulas seems to work OK. I guess I just do not understand the column numbering issue, why the columns are numbered, and what affect column numbers have on the worksheet. Would someone please enlighten me?

 

[Fluff]

The table header needs a "name", so if the cell has something in it, that is the "name", if a cell is empty then you will get column 1 for the 1st empty cell, column 2 for the 2nd, etc.

 

[sts8500man]

Thank you for responding. Based on your response, it sounds like I need to create an empty table and import the data into the table; would that work to give each column a sequential number starting from the first column as "number 1"?

 

[Fluff]

Does your data have a header row?
I prefer to avoid having columns labeled as Column1, Column2 etc. as they mean nothing, whereas having labels of Cost, Qty, Date etc. are a lot more meaningful

 

[sts8500man]

Yes sir. Every column in the spreadsheet has a header with names. The whole thing would not bother me if inconsistency were not present. For instance -- the first column that is given a column number is actually the fifth column of the table, but it, like the previous 4, has a column name. Then it skips columns 6 through 9 before providing the number 2 to the column which is actually the 10th column of the table. Column 10 has a name just as all the others have. From that point to the end of the columns, each one was assigned a number starting with 3 without skipping any more columns. Each of those also contained its own column name in the spreadsheet within which data was converted to a table. All columns contain numeric entries, either financial, numeric, or percentage from a formatting perspective. Everything in the table seems to work correctly in spite of the lack of consistency in the numbering of the columns. I could leave it as is, but I am trying to learn something about tables from this thread and I need to know how or if the lack of consistently numbered columns in the table will affect calculated results or the building of formulas or the writing of macros in VBA.

 

[Fluff]

Do you have any merged cells prior to converting the data to a table.

 

[sts8500man]

None inside the table itself, including the "header row". There are merged cells in the spreadsheet but not the table. And, thank you for your response and continued help.

 

[Fluff]

If there are no merged cells in the header, then the only reason you should get Column 1, Column 2 as a label is if the cell is blank.

 

[sts8500man]

Thank you again for responding. I just really don't understand this table column numbering issue in general. The cells in the column that excel called column 1 are blank but no other cells in the entire table are blank, including those it numbered column 2. I checked a different spreadsheet, one very similar to the one that generated this dialog, and it contains sequentially numbered columns starting from column 1 to the last column in the table. That table was built just like the one that generated this dialog, that is -- no merged cells, and containing header data. Two spreadsheets that are almost twins just containing data from different sources and different number of columns and rows and different calculations and charts. One has columns numbered sequentially from the first to the last, the other doesn't. Don't understand!

 

[Fluff]

If there are no blanks in the header & you select "My table has headers" you will not get Column 1 etc.
However if there are blanks they will get autonamed.

Before

County	District	Ward	DistrictCode	WardCode	Country
Hertfordshire	St Albans	Colney Heath	E07000240	E05009028	England
Hertfordshire	St Albans	Wheathampstead	E07000240	E05004804	England
Wiltshire	Wiltshire	Ethandune	E06000054	E05008362	England
Lancashire	Hyndburn	Barnfield	E07000120	E05005207	England
West Yorkshire	Kirklees	Cleckheaton	E08000034	E05001393	England
West Yorkshire	Bradford	Keighley Central	E08000032	E05001355	England
West Yorkshire	Bradford	Worth Valley	E08000032	E05001369	England
Greater Manchester	Bury	East	E08000002	E05000672	England
Somerset	Sedgemoor	Cheddar and Shipham	E07000188	E05008905	England
Cheshire	Cheshire West and Chester	Saughall and Mollington	E06000050	E05008692	England
County		Ward	DistrictCode		Country
Hertfordshire	St Albans	Colney Heath	E07000240	E05009028	England
Hertfordshire	St Albans	Wheathampstead	E07000240	E05004804	England
Wiltshire	Wiltshire	Ethandune	E06000054	E05008362	England
Lancashire	Hyndburn	Barnfield	E07000120	E05005207	England
West Yorkshire	Kirklees	Cleckheaton	E08000034	E05001393	England
West Yorkshire	Bradford	Keighley Central	E08000032	E05001355	England
West Yorkshire	Bradford	Worth Valley	E08000032	E05001369	England
Greater Manchester	Bury	East	E08000002	E05000672	England
Somerset	Sedgemoor	Cheddar and Shipham	E07000188	E05008905	England
Cheshire	Cheshire West and Chester	Saughall and Mollington	E06000050	E05008692	England

After

County	District	Ward	DistrictCode	WardCode	Country
Hertfordshire	St Albans	Colney Heath	E07000240	E05009028	England
Hertfordshire	St Albans	Wheathampstead	E07000240	E05004804	England
Wiltshire	Wiltshire	Ethandune	E06000054	E05008362	England
Lancashire	Hyndburn	Barnfield	E07000120	E05005207	England
West Yorkshire	Kirklees	Cleckheaton	E08000034	E05001393	England
West Yorkshire	Bradford	Keighley Central	E08000032	E05001355	England
West Yorkshire	Bradford	Worth Valley	E08000032	E05001369	England
Greater Manchester	Bury	East	E08000002	E05000672	England
Somerset	Sedgemoor	Cheddar and Shipham	E07000188	E05008905	England
Cheshire	Cheshire West and Chester	Saughall and Mollington	E06000050	E05008692	England
County	Column1	Ward	DistrictCode	Column2	Country
Hertfordshire	St Albans	Colney Heath	E07000240	E05009028	England
Hertfordshire	St Albans	Wheathampstead	E07000240	E05004804	England
Wiltshire	Wiltshire	Ethandune	E06000054	E05008362	England
Lancashire	Hyndburn	Barnfield	E07000120	E05005207	England
West Yorkshire	Kirklees	Cleckheaton	E08000034	E05001393	England
West Yorkshire	Bradford	Keighley Central	E08000032	E05001355	England
West Yorkshire	Bradford	Worth Valley	E08000032	E05001369	England
Greater Manchester	Bury	East	E08000002	E05000672	England
Somerset	Sedgemoor	Cheddar and Shipham	E07000188	E05008905	England
Cheshire	Cheshire West and Chester	Saughall and Mollington	E06000050	E05008692	England