Forum Discussion

SkyTrader's avatar
SkyTrader
Copper Contributor
Apr 05, 2021

Stop First Row Being Numbered in Excel

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 w...
Excel on Mac
  • JMB17's avatar
    JMB17
    Apr 07, 2021

    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).

     

     

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

Lowellhawk's avatar
Lowellhawk
Copper Contributor

SkyTrader

There still is no way to stop the first row from being numbered, however if you want you could put the header and the first row of data both in row 1.

This is pretty unconventional and doesn’t even look much better but it still works completely fine.

To start, make an entirely new sheet where the properly listed data will be. In cell A1 enter the formula:

="[Insert Header for A1]"&CHAR(10)&
INDEX([Insert the original sheet name here]!A2)

So that the data from A2 on your original sheet is automatically combined with the header on A1. You will have to redo this for every column on your header row (so for example the next formula would be):

="[Insert Header for A2]"&CHAR(10)&
INDEX([Insert the original sheet name here]!B2)

 

Then for rows 2 and on just insert your data from rows 3 and on from your original sheet using the index function.

 

You should still be able to sort and filter data normally on your original data sheet, but also have another sheet to view your sorted list with correct numbering.

 

Its not a perfect solution; the new sheet will be a bit wonky and won’t be functional, but I think it’s still the best fix for this.

 

Let me know if it works for you or if you have any trouble with the formulas

 

Hope it helps,

 

Lowellhawk

Lowellhawk's avatar
Lowellhawk
Copper Contributor
Apr 23, 2023

Here is how it turned out for me: 

 

 

Resources