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?
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?
