Let’s say you want to allocate a value across multiple months based on a start date and how many months you want to allocate. The monthly allocation will be averaged based on the number of months. The solution isn’t that hard. (more…)
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.
You want a sequential number in a column. The challenge is, it must display as sequential even if rows are hidden or filtered. Is this possible? (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…)
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…)