You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Numbers Column Letters Are Messed Up. How Can I Fix That?

I use a desktop Mac 27" (which isn't listed with my choice of devices) with Ventura. I'm working on a Numbers spreadsheet with columns A thru K and rows 1 thru 42.


Somehow, the A-B-C ... identifiers at the top of the columns got messed up. Columns are now labeled A-B-C-E-F-G etc. -- in other words, no Column D. This apparently is the cause of Numbers telling me I can't "Sum" the numbers in Column I because the app identifies it as Column J, which has text. (Note: There is a double vertical line dividing C and E, and the tiny space between those lines is probably column D, but there is not enough space to let my cursor in to move the dividing lines apart to fix it.)


Sums appear at the bottom of other columns of numbers, but not Column I (a.k.a. J). How can I fix this so I can total the numbers in Column I? Thanks.

Fran

iMac (24-inch, M1, 2021, 2 ports)

Posted on Feb 25, 2023 1:17 PM

Reply
Question marked as Top-ranking reply

Posted on Feb 25, 2023 7:27 PM

I suspect you have accidentally hidden column D by selecting the column or selecting a cell in the column, then engaging the Hide column menu item in the local menu (or in the Table menu).

Here's an example where I've done that with column D. Note that the formula shown here was entered into a footer row cell (A11), then filled right to K11.


Sums in the columns shown should decrease by 1 per column, due to the deleted values in the columns.


Select the whole table then use "Unhide all columns" in the Table menu to bring column D back into view.


Regards,

Barry

6 replies
Question marked as Top-ranking reply

Feb 25, 2023 7:27 PM in response to Kentucky Fran

I suspect you have accidentally hidden column D by selecting the column or selecting a cell in the column, then engaging the Hide column menu item in the local menu (or in the Table menu).

Here's an example where I've done that with column D. Note that the formula shown here was entered into a footer row cell (A11), then filled right to K11.


Sums in the columns shown should decrease by 1 per column, due to the deleted values in the columns.


Select the whole table then use "Unhide all columns" in the Table menu to bring column D back into view.


Regards,

Barry

Feb 26, 2023 2:54 PM in response to Barry

This is what I see when I click on J42, which holds the total of all numbers in the "J" column. I have no idea what the additional "+" factors are or how they got there, but the first "+" is K38, so that's definitely the issue here. Is it OK to delete everything in that formula except "Sum" and "J2:J41"? If so, do I click the red-circled "X" and start over with just "Sum"? Thanks.

Feb 26, 2023 3:54 PM in response to Kentucky Fran

Either would be OK.


SUM(J2:J41) will sum the values of all cells in rows 2 to 41 of column J. If the values in the other four cells should not be included in the sum, they should not be in the formula, and can be deleted.


If this formula (when corrected) is to be placed in cell J42, and row 42 is the last row of the table, you can simplify the formula by making Row 1 a Header Row (if row 1 has the grey fill shown in my examples above, and had that fill colour when the table was created, it is already a header row.) Footer rows are not included by default in tables created from one of the general purpose templates, but you can specify the number of Header rows and Footer rows using the Table menu.


Placing Header Row(s) at the top of a table and Footer Row(s) at the bottom of the table makes summing the individual columns a very simple formula, as shown below:

If you need the formula in more than one column, you can, after clicking the checkmark to confirm the formula and close the editor, drag to fill it into adjacent cells. In the example, where the formula was entered into J42, hover the pointer near the left edge of the cell. When a small yellow filled circle appears, grab it with the pointer and drag left into the rest of the cells that should contain the same formula (adjusted automatically to sum the column they are filled in to).


One other note: The header row at the top of the table is 'frozen'. This keeps that row on screen while you scroll down the table, moving it up the screen and sliding it behind the 'frozen' header row.


Regards,

Barry

Feb 26, 2023 12:53 PM in response to Barry

Thank you, Barry! I now can see Columns A through L.


Ironically, I still get a message at the bottom of what is now Column J that says cell K38 contains a string, not numbers. The message disappears when I remove the text from cell K38. I don't know why that happens, but when I move the text from K38 to L38, all is well. Problem solved, even if it makes no sense!


I'm fairly new to spreadsheets, and I'm learning by doing. Thanks for responding to my plight.

Fran


Feb 26, 2023 2:37 PM in response to Kentucky Fran

HI Fran,


Is there a formula in the cell at the bottom of 'what is now column J'?

Does that formula contain a reference to column K or to cell K38?

Would that formula be one that expects cell K38 to contain a number value?


If so, the error message is correct, and you would need to replace the text in K8 with an entry that Numbers recognized as a numeric value, or revise the formula to one that does not require a numeric value in K38.


Removing the content of K38 leaves an empty cell. If the data format setting for that cell is Automatic, numbers may interpret an empty cell as a zero, and use that value in its formula.You can check the data format setting of a cell by selecting the cell, clicking the format brush (upper right corner of the window), and clicking Cell in the sidebar that opens.


Regards,

Barry



Feb 26, 2023 3:05 PM in response to Kentucky Fran

Hello again, Barry. After I sent the previous response, I came across "show formula as text." Once I did that, I could easily delete everything that didn't belong. Finally, all is well. I hope you see this before devoting more time to my situation. I appreciate your solution and the way you guided me through it. You are an excellent teacher/trainer!

Fran


Numbers Column Letters Are Messed Up. How Can I Fix That?

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.