Excel Formula Spill

[Graemea]

Hi,

In A:B I have a Table in which I want to insert lookup formulae to extract data from I:J (a normal range).

I used the XLOOKUP function in column B but it spills the formula created in B2 down the column.

I would prefer it not to spill so can someone please tell me how I could amend the formula so that it doesn't spill?

I have posted this question on another forum a couple of days ago.

Thanks!

 

[Alex Blakenburg]

I think that "SPILL" might be the wrong terminology.
Spill implies that the one formula is producing an array of result and the results spill from the current cell to the rows below it and/or to the columns to the right of it.
You are using an Excel Table which automatically populates all the cells in the same column with any formula that is entered.
This normally desirable and one of the benefits of using a table. What did you want to happen ?

PS: If a formula in a table did try and SPILL you would get a #SPILL! Error

 

[Fluff]

I have posted this question on another forum a couple of days ago.

Thanks for letting us know, but can you please post a link to that question, as per the rules. Thanks

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

 

[Graemea]

Hi Alex.

Thanks for the reply.

You're correct, SPILL is the wrong term for what I'm describing.

I would like to have the benefits of the Table (e.g. structured references) but would prefer that the cells below the formula in the same column aren't automatically populated with the formula.

The reason for this is that I don't want to use the same formula in the whole column - in some cases the formula may refer to a different set of data elsewhere in the workbook that needs a formula with different syntax. I can obviously delete the unwanted formulae before inserting the correct ones but I'm just concerned that I could unwittingly get the wrong formula in a cell as I've not noticed that it's been autofilled.

If there's no way other way around this that's fine but I just thought I'd ask for some expert help.

Thanks again

 

[Anthony47]

If you first type a string (for example) in a different row of the column then your formula will not expand through the column. This will affect only the current table.

Or you can do it via Menu / File/ Options / Proofing tools (*), Autocorrect options (*), tab Autoformat when you type (*), uncheck the box "Create calculated column" (*)
* Not sure about how those fields are shown in your language
This will affect all the tables in your Excel

 

[Alex Blakenburg]

It is really bad practice to have inconsistent formula in the same column.
If you absolutely must have it then try this:
• select any cell in that column (except the heading)
• Ctrl+Spacebar (this will select the whole column)
• hit the delete key to delete any formulas already in the column
• select the cell you want the formula in and enter the formula
• immediatly hit Ctrl+Z (undo) (The initial autofill is a 2 step automated process ie 1) enter the formula, 2) autofill, the ctrl+z will reverse just the autofill step)
The column should now no longer autofill when you add more formulas in that column or when you add more rows.

 

[Graemea]

Thanks, that's really helpful!

 

[Alex Blakenburg]

You're welcome. Glad we could help.