Setting cells to default to mixed/absolute referencing

Copper Contributor

I googled around, but couldn't find an answer to how to do this (but, at the same time, I can't believe that this hasn't been requested enough that Microsoft hasn't put it into Excel, yet).

 

I make a lot of tables where the main area of the table is calculated from the values of the row/column headers (for example, imagine row headers being the date and column headers being the hour of the day, and the main table would be, say, the position of the sun at that day/time). Because of this, I would make my top-left calculated cell (B2) to refer to $A2 for the row header and B$1 for the column header. Then, I can just drag-fill the rest of the table. However, for complicated formulae, managing these mixed references is time-consuming and error-prone.

 

What I'd really love is the ability to mark a row of cells to default to row-absolute referencing and a column of cells to default to column-absolute referencing. That way, when I merely click on something from the headers, the cell reference already would have the desired reference type. Is there a way to do this in Excel?

2 Replies
There isn't a way currently I'm afraid, other than the F4 key to toggle the $$. But I think this is an interesting suggestion. Suggestions can be sent to the Excel team in two ways:

1. From Excel click Help, Feedback, I have a suggestion.
2. Go to https://feedbackportal.microsoft.com/feedback/forum/c23f3b77-f01b-ec11-b6e7-0022481f8472 and add a new suggestion. Ask your peers to vote up your suggestion to increase the chance of getting this implemented!

@jemenake You can also use dynamic arrays, like shown in the attached Excel file, which contains no more than 3 formulas!

DynamicTable.gif