Excel automatically running absolute reference within same workbook

Copper Contributor

Hi, my excel is automatically running absolute references when I reference within the same workbook. For example, I am in sheet1 and I reference a cell in sheet2, excel will automatically do absolute reference. I want my excel to reference cells within same workbook as relative reference. Does anyone know how I can change that setting in excel? (I have excel on Mac) 

3 Replies

@mscnpeace Can't remember ever having seen a setting that puts Excel for Mac (or Windows) in absolute reference mode (i.e. entering $ signs to fix row numbers and column letters)  By default, references are relative (i.e. no $ signs). However, when working with pivot tables, charts, conditional formatting and data validation, references are often absolute by default. Perhaps there may be other instances as well.


Can you show what exactly you are doing when the references default to absolute?

Hi @Riny thanks for the reply! So I was having the issue I describe. Say I had two sheets: sheet1 and sheet 2. in sheet1 cell b4 I wanted to reference sheet2 cell c7, so I'd simply click on sheet1 b4, enter "=" then I click on sheet2 c7, and excel automatically would do this: "'Sheet2'!$c$7" which actually had never happened to me in excel. So I thought I had unintentionally done something (first time on Mac excel -- so feeling like a newbie entirely in excel) to change such settings which would have been a total pain in the butt going forward. But it corrected itself. What I am finding is that excel in Mac is a bit wacky so lots of little things like this. Thanks for answering.

I have another question: do you have a recommendation for Mac excel users -- trying to speed up my mental switch between the two :)


@mscnpeace I've never experienced the behaviour you describe on Excel for the Mac. So, I don't have any tips/recommendations to get used to it.