reference to a full row or column doesn't seem to work anylonger

I haven't been using excel as much for quite a while and now suddenly my normal way of writing formulas doesn't seem to work anylonger. I have a habit to refer to cells as the whole row (e.g. 5:5) instead of C:5 (if the formula is in the C column). This in order to make it easy to quickly see that it is the row number that matters - the C is just a redundant piece of information when you try to understand a model built by someone else (or myself a while ago).

However, when I do this now I get either a blank value (if using several references in the same formula) or #SPILL! if I just use one reference of the kind I used to. Is there something that has changed in excel and does this mean I am forced to refer to both the column and the row going forward (this would be quite a large step backward in my opinion)?

If you're getting #SPILL! errors, you're NO LONGER using Excel/Office 2010, so your profile is out of date.

In that sense, yes, indeed, Excel has changed. In brief, you need to change your ways OR revert to an older version of Excel/Office. Those are your ONLY alternatives.

I can make many guesses about what's going wrong, but they all boil down to this: your use/reliance on whole row or whole column references used to work in older versions of Excel due to implicit range indexing. The latest Excel in Office 365 no longer works that way, and most formulas using whole row/column references are going to return IMMENSE array results. If you want to use the latest version of Excel/Office, you're going to have to accept that your preference for whole row/column references was always POOR PRACTICE (even if you believe otherwise), and it no longer works in the latest Excel.

If you want more specific advise, you need to provide more details, such as actual formulas which are now causing problems. Otherwise, expect only generalities as responses.

Thanks for a speedy reply which completely answers my question. I realise I have to adapt to what the excel developers have decided is standard practise.

Originally Posted by CFF

. . . I have to adapt to what the excel developers have decided is standard practise.

Not just Excel developers. Whole row/column references have always been bad ideas except as necessary components in generating dynamic ranges, e.g.,

INDEX($1:$1048576,a,b):INDEX($1:$1048576,c,d)

which would be a reference to a limited-size range impervious to row/column insertion/deletion. Whole row/column references are analogous to not using Option Explicit in VBA modules, and not declaring any variables with Dim statements, thus making ALL variables global Variants. One could do so, and one could get away with it for a while, but it would eventually cause far more problems than it may at first have seemed to solve.

Well, when e.g. modelling a P&L it was really nice to be able to e.g. name a row "NetSales" and another "GrossProfit" and thus be able to read the formula for the margin in "plain text" ("=GrossProfit/NetSales"). It was indeed a long time ago since I did financial modelling at GS, but at that time, at least in that company it was definitely considered as "Good Practise" to name column and rows in that way - in order to be as clear as possible for any outsider who was to use the model.

Anyway, not much I can do about it, but to accept

Thanks again for your help!

Thanks! It seems that it could definitely work, but now by chance I just realized that it seems as maybe the "at"-symbol has been added to give the same functionality i used to have.

So I can now name "='at'Sheet1!$A:$A" "Net_sales" and "='at'Sheet1!$A:$A" "Gross_profit" and then my formula for calculating the Gross margin be the same as before: "Gross_profit"/"Net_sales".

Does that sound correct?

As I, when trying to post this reply, got the error message "You are not allowed to post any kinds of links, images or videos until you post a few times.!" I replaced the symbol with the letters at