I maintain a spreadsheet table ordered by date from newest to oldest. I use a hidden formatting row (3) below the header to support inserting new rows at the top. There are a number of currency rows formatted as Currency. Lately, when I insert a new row at the top or anywhere below there, two columns (F and G) in the new row default to the Accounting format, forcing me to manually fix those two cells. This formatting causes the cell to override the default format an display the data centered horizontally and the $ sign is spaced further left of the currency value. Earlier, the file cache has been emptied and Office 365 has been reset. Neither procedure fixed the issue. What is causing this and how do I fix it? See attachment.

Attached Files

EdJonesInvestment-25 - Copy.xlsx (20.5 KB, 4 views)

Is your table a regular one, or a Defined Table?

Why is there the need for formatting row, what it contains, and how it affects added new rows?

It is a normal table. See attachment. Without a hidden dedicated "formatting" row next to the header, new rows added below the header pick up the formatting of the header cells which has nothing to do with the data rows.

It is a Defined Table! When designed properly, all formulas, formats, or whatever, are applied automatically to new rows. The only condition for this is, that every column can in entire datarange contain only single formula, single format, or whatever.

Some of your Table columns contain 2 different formulas or formats! So it is for Excel to decide - and who knows what the decision will be!

Actually, only column H has both formula and a fixed value. That cell is in the bottom row which is carrying over a percentage out of context. And that column does not have the problem. Only columns F and G have the issue and their formulas are consistent down each column. So, I am not sure what you are referring to. The insert Accounting format is a phantom formatting that Excel pulls out of thin air, as it were. it is nowhere in the table formatting design.

Added is an example I'd use.

The Table can contain data from several years. NB! Real entries for entry dates, not totals as currently for 29. December 2024!

The order of entries in table does'nt affect formulas. To make this possible, I added an Entry ID column, which allows to register the entry with unique order number only, where the new entriy ID will be previous one +1. To see how, look at sheet Hidden (which you can hide afterwards )

Above of Table is Totals row, where you can select a year the totals there are calculated. To make it possible to use SUMIFS() to calculate totals, I added a Year column, which you can hide. Another way is to use SUMPRODUCT() to calculate totals - then you can skip the Year column. NB! It is a bad idea to have some info immediately above Table, as sometimes Excel my decide to include them. To avoid this, I left between totals and Table header an empty row and did hide it.
To select the year for Totals, on Hidden sheet is the Table where the years are listed. Based on this table is defined a Named Range which is used as source for Data Validation List in Totals row.

You can use the Autofilter on Date column (or on any other columns) to determine, how many and which rows are displayed (so the number of displayed entries is reasonable). as the formulas in Table are not depending on placement of references, you can add new rows at any place tou fancy. But I'll advice you don't add new entries immediately below header, as somehow Excel then takes the height of row and formats for some cells from header row! (Probably a problem with some of latest updates, as I haven't had this problem earlier!)

I had to replace column formats in Table with my local ones, as otherwise Excel messed up with them (and with Table Style too).
Also I had to edit some column headers (removed spaces or replaced them with underscore), as otherwise there were some problems (again a new ones for me) with references in Table Formulas.

Attached Files

EdJonesInvestment-25 - Copy.xlsx (26.9 KB, 4 views)