3 ms·

I can't agree with'always name a cell/range' and dont put numbers. This gets very confusing for a second person working on the workbook. Typically sheets get la

I can't agree with'always name a cell/range' and dont put numbers. This gets very confusing for a second person working on the workbook. Typically sheets get large and they get all these names building up over time. You end up with no idea with what someone is referring to. Also for 'paste special' I dint notice him mention Crt+Alt+V which is really shortcut vs using menu dropdown. Ctr+D was new to me. Thats a great one.

Tomte 10y ago
Re: seeing what it refers to: Do you know the trace to predecessor/successor feature in the Formula tab?
- Gustomaximus 10y ago
  Yeah I'm familiar. It can be useful. I just find it easier to move around workbooks/sheets via sheet/cell reference, especially with multiple users. I used to do the naming thing but I found it less efficient.
TeMPOraL 10y ago
I disagree with your disagreement. The principle of naming your cells/ranges is basically the exact same principle as "don't put magic numbers in code, use named constants".
- kilgor13 10y ago
  I mostly agree with you. Where the names are clear and obvious, naming is very helpful. However, when it is no longer obvious what the name refers to, there is no more benefit than the original cell reference. In fact it is even worse because it's not immediately apparent where the cell is now the reference isn't showing. I often run into this problem with very large spreadsheets where every cell is named. Our workplace has a strict "everything has to be named" in Excel and it really makes life much more difficult when working with extremely large calculations. Often I will want to know what a certain formula is doing. I will look at the formula. There are about 6 named cells that I have no idea what they refer to. I then have to use the drop down list (which includes about 500 names) to find the cell it refers to. Looking through this list is a lot slower than just reading the cell reference and looking at that cell. It's also quite easy to pick the wrong name in a list of 500, because there is likely to be a lot of similarly named cells.
  - aninhumer 10y ago
    >There are about 6 named cells that I have no idea what they refer to. Maybe I'm misunderstanding, but doesn't that mean they're just badly named?
- coroxout 10y ago
  I've thought that before but every time I've named important ranges I've come to regret it. Someone at some time will decide to nuke an entire worksheet and paste it back in without the named ranges, and then everything will look the same but various lookups and macros will break. (And they will somehow be able to do this even though you've password-protected everything in sight, too. Not sure how that happened.) Or someone will insert a row somewhere and the named ranges will be missing the last row of the data and any named ranges beneath them won't shift down. That kind of thing. Maybe I'm just missing some named range best practices.

Tomte 10y ago

Re: seeing what it refers to: Do you know the trace to predecessor/successor feature in the Formula tab?

Gustomaximus 10y ago
Yeah I'm familiar. It can be useful. I just find it easier to move around workbooks/sheets via sheet/cell reference, especially with multiple users. I used to do the naming thing but I found it less efficient.

TeMPOraL 10y ago

I disagree with your disagreement. The principle of naming your cells/ranges is basically the exact same principle as "don't put magic numbers in code, use named constants".

kilgor13 10y ago
I mostly agree with you. Where the names are clear and obvious, naming is very helpful. However, when it is no longer obvious what the name refers to, there is no more benefit than the original cell reference. In fact it is even worse because it's not immediately apparent where the cell is now the reference isn't showing. I often run into this problem with very large spreadsheets where every cell is named. Our workplace has a strict "everything has to be named" in Excel and it really makes life much more difficult when working with extremely large calculations. Often I will want to know what a certain formula is doing. I will look at the formula. There are about 6 named cells that I have no idea what they refer to. I then have to use the drop down list (which includes about 500 names) to find the cell it refers to. Looking through this list is a lot slower than just reading the cell reference and looking at that cell. It's also quite easy to pick the wrong name in a list of 500, because there is likely to be a lot of similarly named cells.
- aninhumer 10y ago
  >There are about 6 named cells that I have no idea what they refer to. Maybe I'm misunderstanding, but doesn't that mean they're just badly named?
coroxout 10y ago
I've thought that before but every time I've named important ranges I've come to regret it. Someone at some time will decide to nuke an entire worksheet and paste it back in without the named ranges, and then everything will look the same but various lookups and macros will break. (And they will somehow be able to do this even though you've password-protected everything in sight, too. Not sure how that happened.) Or someone will insert a row somewhere and the named ranges will be missing the last row of the data and any named ranges beneath them won't shift down. That kind of thing. Maybe I'm just missing some named range best practices.