When creating ranges of formulas that you want to copy down, you sometimes have a trade off in the use of fixed and relative references. If you need to create a relative reference that acts like a fixed reference you can use a trick.
Tag Archives: fixed references
Reduce Excel Formula Length
Remove redundant references
When you create formulas that refer to other sheets Excel typically includes the name of the current sheet when you return to the current sheet and refer to a cell.
Totalling Tip For Excel
Make the most of SUMIF
I wanted to offer a solution to a common problem I see in Excel. It relates to creating totals in data that isn’t structured that well.
Towards a Shorter IF Function
Returning a range
Most people think that the IF function has to return a result. This leads to doing whole calculations in the true and false sections of the IF function. There is a way to create shorter functions.
Restricting the Scroll Area in Excel
Non-VBA and VBA solution
If you need to limit where a user can scroll to in a sheet you can change a setting in the VBA screen to restrict access to a specific range.
Handling Relative References in Excel [VIDEO]
When you copy a formula in Excel, any relative references (those without dollar signs) may change depending on where you paste the formula. If you would like to copy a formula and not have the relative references change you have two options.
Excel – Unbreakable Sheet Hyperlink [VIDEO]
Changing a sheet name and deleting the hyperlink cell are two processes that can break Excel sheet hyperlinks. The video is at the bottom of the page.
Excel Formula Dialog Frustration Solved
Make editing easy
In many Excel dialogs when you use the arrow keys to move around the formula you actually insert cell references from the active cell in the sheet below.
This is frustrating as you usually only want to move within the formula and you might have to exit out of the dialog and start again.
The Repeat key and $ signs
Do more with F4
The F4 function key in Excel has a dual personality.
When used normally it will repeat your last action. This is useful for formatting, deleting rows and other operations that require a few keystrokes. Pressing F4 can reduce the keystrokes. F4 can also repeat running a macro.