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. (more…)
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.
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.
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. (more…)
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.
(more…)
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.
(more…)
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.
(more…)
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. (more…)
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. (more…)