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.
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.
Import PDF Update
The Power Query engine keeps getting better.
Hopefully soon it will handle PDF documents.
If you want to watch the presentation mentioned above here is the link.
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?
Many people know that you can select the whole sheet with Ctrl + A but there are lots of other selections it can perform.
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.
If your sheet has a lot of colour and you want to print it on a black laser printer, one of the Page Setup options can improve the print.
On the Sheet tab of the Page Setup dialog there is a Black And White option – see image below.
This removes all the colour and prints in black only,
You can access the Page Setup dialog from the Page Layout tab. Click the small arrow on the bottom right of the Page Setup section.
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.
Data entry and calculating
In some large models Excel may calculate for a few seconds after you make an entry.
In most cases you don’t have to wait for Excel to finish calculating before you make your next entry.
Type your entries as fast as you like. Excel will finish calculating once you are done.
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.
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.
It is always good to receive emails like this.
You can read the article this relates to here.
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.
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.
Gauge charts are not a standard chart in Excel and are quite complex to create. Gauge charts should be avoided for dashboards.
My free Excel webinar for June 2018 covered Copy and Paste Tips and Tricks. Download the materials using the button below and watch the video.
The session focuses on the Paste Special dialog plus a little known pasting feature that is great for dashboards.
It covers the hows and whys of
- Paste Values, Paste Formulas, Paste Formats
- Converting negative to positives
- Fixing Text numbers in-situ
- Applying a Factor to a range
- Paste Link – how and why to use it
- Transpose (switching rows to columns and visa-versa)
- Paste Picture Link (great for dashboards)
The session includes lots of keyboard shortcuts. As always, I shared a few other tips during the session.
Thank you Michael Alexander
Recently I learned about the passing of Chip Pearson an Excel legend.
He was so generous with his content and I had thanked him for his contributions years back but it got me thinking about thanking others who had helped me along the way.
The seventh on my Thank You list is Michael Alexander.
In more recent years some of my favorite Excel books have been written or co-written by Michael Alexander.
I like his style of writing plus he knows so much about so many different areas of Excel. He also knows a lot about Access.
I own many of Michael’s books in paperback or kindle.
Thank you for sharing your Excel knowledge in such a practical and understandable way.
My Excel skills have improved from reading your books.
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.
The TreeMap is like a square pie chart, but it has the added ability to show a hierarchy.
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.