SOLVED

Stop First Row Being Numbered in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-2254493%22%20slang%3D%22en-US%22%3EStop%20First%20Row%20Being%20Numbered%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2254493%22%20slang%3D%22en-US%22%3E%3CP%3EOnly%20Excel%20can%20make%20you%20spend%20an%20hour%20on%20Google%20trying%20to%20find%20the%20answer%20and%20figure%20out%20what%20should%20be%20the%20most%20ridiculously%20simple%20thing%20to%20do%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EStop%20the%20First%20Row%20Being%20Numbered.%3CBR%20%2F%3E%3CBR%20%2F%3ESeriously%20where%20is%20the%20simple%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2254493%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2254525%22%20slang%3D%22en-US%22%3ERe%3A%20Stop%20First%20Row%20Being%20Numbered%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2254525%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F696665%22%20target%3D%22_blank%22%3E%40SkyTrader%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDo%20you%20mean%20the%20row%20numbers%20on%20the%20left%20hand%20side%3F%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0266.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F269621i9A3AF7ABB49B1415%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22S0266.png%22%20alt%3D%22S0266.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIf%20so%2C%20there%20is%20no%20way%20to%20make%20them%20start%20on%20the%20second%20row.%3C%2FP%3E%0A%3CP%3EIf%20you%20meant%20something%20else%2C%20please%20explain.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2254676%22%20slang%3D%22en-US%22%3ERe%3A%20Stop%20First%20Row%20Being%20Numbered%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2254676%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F696665%22%20target%3D%22_blank%22%3E%40SkyTrader%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20your%20reference%20style%20is%20based%20on%20Table%20names%20and%20dynamic%20ranges%20then%20it%20is%20perfectly%20possible%20to%20hide%20the%20'town%20plan'%20style%20of%20sheet%20coordinates%20and%20create%20more%20meaningful%20array%20indices%20linked%20to%20data%20objects%20rather%20than%20the%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20679px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F269661iFAEAC94DEE230E3B%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2254894%22%20slang%3D%22en-US%22%3ERe%3A%20Stop%20First%20Row%20Being%20Numbered%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2254894%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3EHi%20Peter%2C%20thanks%20for%20the%20image%2C%20this%20is%20exactly%20what%20I%20want.%20A%20header%20with%20no%20zero%20and%20the%20rows%20starting%20with%201%2C2%2C%203%20etc%20which%20will%20match%20my%20VIX%20levels%20of%201%2C2%2C%20and%203%20etc.%20At%20the%20moment%20the%20VIX%20is%20%22out%20of%20sync%22%20with%20the%20Row%20numbers.%20Pls%20see%20image.%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-04-05%20at%2022.55.30.png%22%20style%3D%22width%3A%20926px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F269705i17E05B2F75CB8E6F%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-04-05%20at%2022.55.30.png%22%20alt%3D%22Screenshot%202021-04-05%20at%2022.55.30.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2255065%22%20slang%3D%22en-US%22%3ERe%3A%20Stop%20First%20Row%20Being%20Numbered%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2255065%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F696665%22%20target%3D%22_blank%22%3E%40SkyTrader%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20could%20hide%20excel's%20row%2Fcolumn%20headers.%20File%2FOptions%2FAdvanced%20and%20scroll%20down%20to%20the%20display%20options%20for%20this%20worksheet%20section.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22JMB17_0-1617667896271.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F269790i138EE9D47BAB0E49%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22JMB17_0-1617667896271.png%22%20alt%3D%22JMB17_0-1617667896271.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2255607%22%20slang%3D%22en-US%22%3ERe%3A%20Stop%20First%20Row%20Being%20Numbered%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2255607%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20but%20this%20is%20why%20I%20wasted%20an%20hour%20earlier%20today%20despite%20clarifying%20my%20system%20and%20version%20(Macbook%2FExcel%20v16%202019%20(and%20tried%202021))%2C%20none%20of%20the%20menus%20in%20the%20google%20search%20results%2Fvideos%20corresponded%20to%20my%20version....%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2255750%22%20slang%3D%22en-US%22%3ERe%3A%20Stop%20First%20Row%20Being%20Numbered%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2255750%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F696665%22%20target%3D%22_blank%22%3E%40SkyTrader%3C%2FA%3E%26nbsp%3BIf%20it's%20the%20%22Show%20row%20and%20column%20headers%22%20switch%20(as%20suggested%20by%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F675152%22%20target%3D%22_blank%22%3E%40JMB17%3C%2FA%3E%26nbsp%3B)%20that%20you%20are%20looking%20for%20in%20Excel%20for%20Mac%2C%20go%20to%20Excel%2C%20Preferences%2C%20View.%20Here%20you%20can%20switch%20off%20the%20row%20and%20column%20headers.%20It%20may%20look%20a%20bit%20different%20on%20your%20version%20but%20it%20should%20be%20very%20similar.%3C%2FP%3E%3CP%3E%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-04-06%20at%2006.57.01.png%22%20style%3D%22width%3A%20258px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F270269iD1844717549EB232%2Fimage-dimensions%2F258x180%3Fv%3Dv2%22%20width%3D%22258%22%20height%3D%22180%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-04-06%20at%2006.57.01.png%22%20alt%3D%22Screenshot%202021-04-06%20at%2006.57.01.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Only Excel can make you spend an hour on Google trying to find the answer and figure out what should be the most ridiculously simple thing to do:

 

Stop the First Row Being Numbered.

Seriously where is the simple check/uncheck option to do this?

Thanks.

13 Replies

@SkyTrader 

Do you mean the row numbers on the left hand side?

S0266.png

If so, there is no way to make them start on the second row.

If you meant something else, please explain.

@SkyTrader 

If your reference style is based on Table names and dynamic ranges then it is perfectly possible to hide the 'town plan' style of sheet coordinates and create more meaningful array indices linked to data objects rather than the sheet.

 

image.png

@Peter Bartholomew 
Hi Peter, thanks for the image, this is exactly what I want. A header with no zero and the rows starting with 1,2, 3 etc which will match my VIX levels of 1,2, and 3 etc. At the moment the VIX is "out of sync" with the Row numbers. Pls see image. Screenshot 2021-04-05 at 22.55.30.png

@SkyTrader 

 

You could hide excel's row/column headers. File/Options/Advanced and scroll down to the display options for this worksheet section.

 

JMB17_0-1617667896271.png

Thanks but this is why I wasted an hour earlier today despite clarifying my system and version (Macbook/Excel v16 2019 (and tried 2021)), none of the menus in the google search results/videos corresponded to my version....


@SkyTrader If it's the "Show row and column headers" switch (as suggested by @JMB17 ) that you are looking for in Excel for Mac, go to Excel, Preferences, View. Here you can switch off the row and column headers. It may look a bit different on your version but it should be very similar.

 Screenshot 2021-04-06 at 06.57.01.png

@SkyTrader 

I used the Excel 365 SEQUENCE function to generate fake row and column headers that matched the dimensions of the data ranges on the sheet (the sheet-based values being hidden as discussed subsequently).  You already have the 'VIX Reading's for that.  Formatting and sheet splits can be applied to taste.

 

Hiding the grid and sheet headings only makes sense if you adopt a referencing style that is entirely based on Names and Structured References.

Thanks @Peter Bartholomew,
How would I go about doing that step by step please, (instead of losing the far left column numbers via unticking Row/Col Headers - of which I might need to reference row/col values later)?
Cheers.

@SkyTrader 

Sorry but it is the unchecking of the headings that is a common feature of workbooks I deliver (though I do occasionally leave the headings and the grid showing as a 'comfort blanket' for users that would feel insecure without them).  As you point out, the row numbers systematically fail to index the content and I would suggest that the idea of denoting the columns by letters is ridiculous.  Whilst developing the workbook, the headings do provide controls, allowing rows/columns to be inserted or resized.  

 

Your record numbers in the 'VIX Reading' can be useful in formulas but, personally, I have little use for the values shown in the built-in headings.

 

Seriously, can anyone tell me how to do this or not??

Thanks.

@SkyTrader 

@Riny_van_Eekelen showed you how to switch of the built-in display of row and column headings.  You can then use worksheet cells to build index arrays to match your actual data by formula.

If you insist on using the built-in heading, but wish to show custom values, then the answer is "Seriously, no one can tell you how to do this" because it probably implies rewriting Excel to allow zero and negative row numbers.

best response confirmed by SkyTrader (Occasional Contributor)
Solution

@SkyTrader 

Based on my experience and everything I've ever read, there is no way to change excel's row/column labels via excel's menu or vba, which is what it appears you are asking.

 

You can hide or unhide the row/column labels, and while you might be able to use a formula to visually replicate row/column label(s), either the custom row or column label won't agree to the actual cell references in your existing formulas, which comes back to Peter's comment regarding it would work better in a structured named environment.

 

You could do this (replicating excel's column letters), for example, but your data table row 1 will actually be Excel's row 3 (still mismatched, but it wouldn't be as obvious).

 

JMB17_2-1617770542786.png

 

Seriously, I don't see any available options that I think you will like based on what you've said thus far.

Thanks for the quick response @JMB17.
Seems like Excel could easily add such a feature and it surprises me it's not available based on the large number of queries on google.

Thanks to everyone else for your input.