How to ensure that an Excel table is properly formatted, both in values and background?

[Mr. Snrub]

As I understand it, an Excel table can only be formatted at the time it's created. If you want to change the number format of a column, you have to convert it to a Range, and then convert it back to a table. The problem with this is once you turn it to a range, the background colors of your table get applied to the cells. Then when you convert it back to a table, you've got the formatting of the table, plus the background colors of the cells. This is unnecessary overhead, and also if you try to fill down any values in the cells, you'll get the same background color of the first cell you fill. This is bad! I feel like what I need to do to fix all this is the following:

1. Copy-and-paste the Values And Number Formatting of the entire table to an unused and unformatted area of the worksheet.
2. Ensure that all the recently-pasted cells in the new area are the correct number formats so that new rows will have the correct formats.
3. Select the cells of the new table and format it as a table. Keeping the default name Table1.
4. Rename the original excel table to a temp value, such as GarbageTable.
5. Rename the new excel table from Table1 to whatever the original correct table name is.
6. Delete the original GarbageTable's cells such that the new table occupies the same space as the original table did.

This will be very time-consuming for a workbook with multiple tables. Is there a better approach?

 

[Scott T]

You can select numbers in a excel table and format them, change the cell format from general to number. Are you talking about something else?

 

[Mr. Snrub]

If you create a new row in the excel table it will NOT adopt the number formatting of the last row. Instead it will adopt the formatting of whatever the last row's cells were when the table was created.

 

[Mr. Snrub]

More info:

https://superuser.com/questions/885...ws-appropriately-where-does-formatting-come-f

 

[Scott T]

I create this

Excel 2010

	A	B	C	D
1	Header1	Header2	Header3	Header4
2	DATA1	6919	1283	3179
3	DATA2	1586	6312	7777
4	DATA3	6719	3068	1137
5	DATA4	1656	1407	1400
6	DATA5	7422	7456	3364
7	DATA6	5172	7298	1510
8	DATA7	5343	33	314
9	DATA8	6378	129	5170

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

Sheet7​

After tuning it into a table I select B2:B9 and format as number with 2 decimal places and thousand separate. When I add another row and get the number format in that column.

Excel 2010

	A	B	C	D
1	Header1	Header2	Header3	Header4
2	DATA1	6,919.00	1283	3179
3	DATA2	1,586.00	6312	7777
4	DATA3	6,719.00	3068	1137
5	DATA4	1,656.00	1407	1400
6	DATA5	7,422.00	7456	3364
7	DATA6	5,172.00	7298	1510
8	DATA7	5,343.00	33	314
9	DATA8	6,378.00	129	5170
10	data9	66.00	5	9

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

Sheet7​

As you can see I get 66.00 instead of 66 since the cell is formatted as a number. I am unable to recreate the behavior you are seeing. Maybe someone else can.