Recently Liam Bastick (Excel MVP) wrote an article about using the OFFSET function to calculate depreciation in financial models. You can check out the full article here.
Category Archives: Excel Blog
Adding a Vertical Dotted Line to an Excel Line Chart
An error bar solution
When plotting Actuals and Forecasts on a single line chart you may want to use a vertical dotted line to identify where the Actuals finish and the Forecasts begin.
Copying Without Affecting Relative References
Works for Excel 2013 onwards
There is an easy technique to copy a single formula and paste it without affecting relative references but what if you wanted to paste lots of formulas?
Selecting All with Ctrl + A
Some other useful techniques
Many people know that you can select the whole sheet with Ctrl + A but there are lots of other selections it can perform.
An Easier Step Chart
A Line chart solution
Here’s another way to create a Step Chart. This one is quicker. I wrote previously about using a scatter plot and error bars but it required a lot of chart changes. This one hacks a line chart and requires no chart changes.
Sequential numbers in a filtered list
A formula solution
Let’s say you have a filtered list and in each of the filtered cells you want to enter a sequential number, but in the hidden rows you don’t want to enter anything. There is a way, but it takes a few steps.
Creating a Book Index in Excel
A macro to simplify and speed up the process
My book was published just over four years ago and part of the writing process was creating an Index. To make the task easier I wrote a macro to assist me. Adding an Index to a large document can improve its usefulness. I am sharing the file I used in this blog post.
Select Visible Cells Only Hack
Who knew what a filter could do?
When you only want to copy the visible cells Excel has a special option that allows you to select visible cells only before copying. But there a way to make that the default option.
Validating Checkbox Linked Cells
Data Validation and formula options
When you create a checkbox you need to link it to a cell on a sheet to be able to use its result. The user could overwrite that linked cell with a value or text and affect formulas that are using the checkbox linked cell. You can add a validation to make sure the linked cell only contains TRUE or FALSE.
Make Excel Grey Again
Grey isn't always a sign of aging
Have you tried using different line colours in Excel? I’ve been experimenting with using grey lines instead of the default thin black lines when formatting reports.
Vote No for Gauge Charts
They take up too much space
Gauge charts are not a standard chart in Excel and are quite complex to create. Gauge charts should be avoided for dashboards.
Fixing too many different cell formats
A VBA solution or two
In a recent webinar I was asked about the “Too many different cell formats” error. This tends to be an error in Excel 2010 and earlier versions. In many cases this error is caused by having too many custom Styles.
TreeMap a new chart in Excel 2016
A square pie chart
The TreeMap is like a square pie chart, but it has the added ability to show a hierarchy.
Suggested Excel Webinar Sequence
With over 20 free webinars, what order should you watch them?
Over the last five and a half years I have run more than 20 free Excel training webinars. The topics covered are varied and I have had requests to list the best order to watch the recordings.
Stopping the Clipboard from being cleared in Excel VBA
How to check if the clipboard is empty
It is common knowledge (or it should be) that running a macro clears the undo list. In general you can’t undo a macro. However some macros also clear the clipboard which can stop you copying and pasting. I have found a workaround for the clipboard problem.
Inserting a Blank Row Between Entries
Manual technique
Over the years I have had many requests to help people insert blank rows between entries is a list. Apparently it is for an input routine that requires blanks. My normal solution is a macro because it automates the process, but there is a manual technique that is quick and easy.
Step Charts in Excel
Hacking the error bars
The Step chart is not a standard Excel chart but it is a useful way to display values over time. You have probably seen a step chart but you may not have known what it was called. It sort of looks like the city skyline or something you would create on an Etch-a-Sketch.
Find the first and last dates in a filtered list
Super power to the rescue
Let’s assume you have a large table that you are filtering. Based on the current filter you want to work out the earliest date and the latest date. You may be surprised to learn the SUBTOTAL function can help you.
Pie Charts and Negatives – an Alternative
A waterfall solution
Pie charts have a lot of drawbacks and limitations. One major limitation is they can’t handle negatives. One of Excel’s new charts can help out.
Identifying Locked Cells in Excel
How do you know if a cell is locked in Excel? There is an easy was to find out. And it also makes it easier to lock or unlock the cell.