Share via

Preserve cell formatting when adding rows

Anonymous
2022-06-10T21:02:51+00:00

I keep several financial spreadsheets. I like my dollar values shown with a dollar sign and my negative values in parentheses (goddamnit...)

I will select an entire column by clicking on the top, change the Cell Formatting to be just as described above, go about my business and enter values. Perfect. The display is just the way I want it.

Three days later, more activity, so I have to add rows (between my bottom entry and my summation row below). I enter values again. What USED to be displayed as:

($398.64)

is now displayed (in the NEW row) as:

-398.64

What, oh WHAT, fixes this for good? You'd think formatting an entire column would do the trick. How do you tell Excel, "how about you follow the cell formatting parameters that *I* have set here instead of making up your own?"

Thanks for any aid.

Microsoft 365 and Office | Excel | For home | MacOS

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

6 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2022-06-12T16:25:59+00:00

    Appreciate the time and counsel of Waqas and all here. I have explored the Table concept more, and understand how it works. BUT... I am not interested in working in a table environment. I prefer the simplicity of the spreadsheet environment, which is where I've worked...forever. If I cannot globally format a column in a spreadsheet, and expect future inserted rows to follow that formatting, I will just shrug my shoulders and add it to the list of things that are head-scratchers about Office products, and hope that engineers will address in a future release.

    If I'm missing a method, within the spreadsheet environment, I'd be much obliged for direction.

    Thanks for checking in, Jim.

    10+ people found this answer helpful.
    0 comments
  2. Anonymous
    2022-06-11T04:21:10+00:00

    I believe, based on your reply, that what I have always done is as you describe in #2. But, what you describe (select column, format, proceed) does not work in the end. It works for THE CURRENT SESSION. But when you open the document a few days later, and add rows (as I described), the formatting defaults to Excel Fantasy Default Land (i.e., NOT the settings I established for the Column).

    As for your solution #1 FORMAT SHEET AS A TABLE...what IS this talk? For years I have dealt in Rows and Columns, never having to convert my workspace to a TABLE to make it behave to my preference. What IS a Table, and why can't I accomplish my core needs within the Rows and Columns space of a typical Excel spreadsheet?

    10+ people found this answer helpful.
    0 comments
  3. Jim G 134K Reputation points MVP Volunteer Moderator
    2022-06-12T15:46:14+00:00

    Were you and Waqas able to sort this out, or do you wish assistance from the rest of the community?

    3 people found this answer helpful.
    0 comments
  4. Anonymous
    2022-06-11T03:16:55+00:00

    Dear Garnish19,

    Good day.

    Thank you for posting in Microsoft Community.

    First, my pleasure to assist you.

    As per your description, please don’t worry, we can work together to narrow down and resolve the situation.

    First, for the situation you encountered, we do understand the inconvenience caused and apologize for it.

    May I know does format your sheet as a table.? It sounds like the cell range of column to which you are adding rows to is not actually formatted as a table.

    Option 1: Generally, we could format sheet as a table. Just highlight the area, then go to Insert >Select "Format as Table" on the Home tab. Once you do that, any new rows and columns will be formatted consistently.

    Option 2: To resolve this click on the header of a column then right click and choose "Format Cells" option and set the desired format. After this new row will use this format for selected column. You can specify the format for other / all columns like this.

    Your understanding and patience will be highly appreciated. I hope that you are keeping safe and well!

    Sincerely,

    Waqas Muhammad

    2 people found this answer helpful.
    0 comments
  5. Anonymous
    2022-06-11T06:40:41+00:00

    Hi Garnish19,

    Thanks for your valuable feedback and sharing updates with us and I really appreciate your effort and your precious time doing. 

    And I appreciate your kind words.

    I mean if I do "Format as Table" and added new rows will work as formatted. As per your description, it seems you tried that, but you still don't work in your Excel for Mac OS.

    I may need to check the elements with the data in your workbook sheet. If you don’t mind and if it is convenient for you, could you please send your file to me so that I can look from my side, and I will check this behaviour and help you to fix and verify the result with you.

    If there is confidential information and to protect your personal information, then you can send it to me in Private Message

    Your understanding and patience will be highly appreciated. I hope that you are keeping safe and well!

    Sincerely,

    Waqas Muhammad

    0 comments