It is easy to hide multiple sheets in Excel. Unfortunately, it now just as easy to unhide those sheets. You can hide sheets and make it harder to unhide them. You can use a setting called xlVeryHidden (no kidding) that won’t display the sheet name if you right click a sheet tab and choose unhide sheets.
(more…)Woohoo, I don’t know when this happened, but you can now get Excel to extend your ordinals when you drag with the Fill Handle and use things like 1st, 2nd, 3rd and 4th etc. Type 1st January in a cell and drag the cell down. It seems to work with ordinals at the start rather than at the end of a text string. So January 1st doesn't work. 1st by itself does work.
Excel’s HYPERLINK function is not easy to use. You need to know a few of its secrets to get it to do what you want. Unfortunately, you can’t replicate the HYPERLINK function in a custom function, so we can’t improve it. But the next best thing is to simplify creating the reference you need as the first argument in the HYPERLINK function.
(more…)
Sometimes Excel refers to columns using numbers. If you need to identify the column letter you can use a few functions in combination. Thanks to Marcus Small for the formula which is shorter and more elegant than my original one. You can also create a custom function.
(more…)
It is common in Excel to calculate the percentage movement or difference between two values. This may be between this year and last year or between actual and budget. There are two common issues you will face when doing this calculation. The first is handling zeros and the second is handling negatives.
(more…)