Ask Your Question
0

Refer to rest of cells in a column after a cell

asked 2020-04-03 08:16:43 +0100

zf gravatar image

Is there a shorthand to refer to "all cells in a column after A3"? E.g. A:A represents all cells in a column--is there a shorthand for A3:A1048576 such that you don't have to type out the A1048576 part? It seems like A3:A doesn't work--I suppose I just have to memorize 1048576.

2 more vaguely related questions:

Would restricting conditional formatting to an expected number of cells you're dealing with as opposed to extending to the rest of the spreadsheet result in any noticeable performance improvement? I just prefer extending it for the sake of consistency and to be sure I will not encounter something unexpected if I ever do require more cells then I had originally formatted for.

Is it good practice to default to using relative reference and use absolute reference as necessary or the other way around for most typical workflows/data? Is it good practice to create simple test conditions like whether a date is entered (what's the best way to test this?) to ensure data entered to certain cells make sense or to only test for it in very specific conditions/context?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2020-04-03 09:04:37 +0100

keme gravatar image

updated 2020-04-03 09:08:49 +0100

> Is there a shorthand to refer to "all cells in a column after A3"?

No.

However, there are techniques for making dynamic references go "all the way down".

  • OFFSET() can take vertical and horizontal size parameters.
  • =ROWS(A:A) is perhaps easier to remember than 1048576, and will return max rows also in spreadsheet software with a different row limit.
  • Row number of a certain cell is returned by ROW(< *cell reference* >).

Together, the above functions enables creation of formulas with "variable scope".

> Would restricting conditional formatting to an expected number of cells you're dealing with as opposed to extending to the rest of the spreadsheet result in any noticeable performance improvement?

Not sure.

Recalculation should not happen for cells not impacted by a change, so after initial formatting it should not be noticeable when most of the formatted cells are empty. I have done this on a fairly large scale, but not with very complex format conditions, and I did not notice any performance impact.

Note that inserting cells or pasting from other cells will most likely override existing formatting, so trusting the "format all" practice for consistency is perhaps not the best strategy. For data integrity purposes, spreadsheet software is not the best choice.

> Is it good practice to default to using relative reference and use absolute reference as necessary or the other way around for most typical workflows/data?

No.

It is good practice to always understand what kind of data relationships you are working with, and address it accordingly.

  • When your formulas are in a table and reference other elements of the same table, relative references are usually fine.
  • When your formulas reference data from a table from outside the table, absolute references may be the way to go. Most likely you should use some kind of lookup to the table range, which should be referenced with absolute address. A named range is very useful.
  • When your formulas reference constants, absolute references are usually fine.
  • In a significant number of cases, mixed references will be required.
  • In many cases, in particular when data is routinely reorganized (sorting, filtering, pivot tables), you need to use lookup/redirection functions.

Those are "best fit" guides, as far as I can provide. There will be exceptions. There is no fixed and settled answer for every computation model. You need to think for yourself. No safe shortcuts. Sorry!

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-04-03 08:16:43 +0100

Seen: 44 times

Last updated: Apr 03 '20