Table in state where new rows do not get the formula or formatting from the rest of the rows.

Anonymous
2020-12-27T23:49:31+00:00

With Tables, normally when you add a new row, it will fill down the formula  from the same column of previous rows of the table (if they are all the same formula).  Also, it will normally fill the formatting for that column into the new row.

I have FREQUENTLY encountered 'bugs' in this, where the table loses track of the formula, the formatting, or both. Dealing with it at the moment in a particularly onerous situation.

Is there any way to tell the table to "reset" and use that formula in all new rows for that column, and use that same formatting in all new rows for that column?  have asked before and never gotten a satisfactory answer.

NOT ACCEPTABLE as a workaround is to "convert to range" and then "recreate it as a table".  This changes all the formulas that point to that table to A1C1 notation instead of table notation, and so it would create a HUGE task to go back and manually fix all the formulas that point into the table back to table style notation.

Is there any OTHER workaround for this Microsoft Excel bug behavior?

Microsoft either needs to fix these bugs or [preferably] provide us with a UI to "set column format" and "set column formula", say.  The "Tables" feature is not all that new and should not be this buggy.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

12 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  2. OssieMac 47,631 Reputation points
    2021-01-07T10:30:13+00:00

    Hi Tom-Con,

    Progress report.

    I have not forgotten you. I was ready to post my latest when I now see that you edited your most recent reply. I have only just seen it because we don't get an email advice with edits so I was not previously aware of the continuing problem.

    Anyway I am going to delay posting my solution. One of the problems I had with the formulas was that it was not recognizing the change for added rows until I made a change where I copied the formula to a variable and then cleared the entire column and then pasted back the formula to the entire column and that appeared to work.

    Now I am going to try to do the same with formats. However, it is a bit more complex because we have to handle the banding but I have done some testing and I think I can achieve it (I know. Famous last words but nothing ventured; nothing gained).

    See the following re your statement/question. *"*If you want to provide a way I can send you this example that is not cured by your macro." 

    • Guidelines to upload a workbook on OneDrive. (If you already use OneDrive and your process for saving to it is different then you can probably start at step 8 to get the link but please zip the file before uploading.)
    • Sharing links to business OneDrive often does not work because the business has applied security measures that prevent this. Some people take a copy of the workbook home and upload from their private OneDrive.
    1. Zip your workbooks. Do not just save an unzipped workbook to OneDrive because the workbooks open with On-Line Excel and the limited functionality with the On-Line version causes problems.
    2. To Zip a file: In Windows Explorer Right click on the selected file and select Send to -> Compressed (zipped) folder). By holding the Ctrl key and left click once on each file, you can select multiple workbooks before right clicking over one of the selections to send to a compressed file and they will all be included into the one Zip file.
    3. Do not use 3rd party compression applications because I cannot unzip them. I do not clog up my computer with 3rd party apps when there are perfectly good apps supplied with windows.
    4. Go to this link.  https://onedrive.live.com
    5. Use the same login Id and Password that you use for this forum.
    6. Select Upload under the blue bar across the top and browse to the zipped folder to be uploaded.
    7. Select Open (or just double click). (Be patient and give it time to display the file after initially seeing the popup indicating it is done.)
    8. Right click the file name in OneDrive.
    9. Select Share.
    10. Click the link icon (Looks like chain links) at the bottom left of the dialog (Just above "Copy link").
    11. Click Copy button.
    12. Change back to this forum and click the "Insert Hyperlink" icon at top of the posting editor (Icon looks like chain links).
    13. Right click in the Web address field and right click and paste (or just Ctrl V to paste).
    14. Click "Insert" Button.

    Was this answer helpful?

    0 comments