There are times when pasting to the bottom of an existing large, formatted table can take a few minutes to update. There is a quicker way. When the formatted table is selected there is a Table Design (or Design) tab visible. On the far left-hand side the Resize Table icon allows you to easily extend the range of the formatted. You can amend the range and add sufficient rows to handle the new data in the dialog that opens. When…
My second article in Accountants Weekly was published 20 years ago today and it was Top 10 Functions for Accountants.
(more…)I received this email to day with feedback about last week's Intermediate Excel series.
You need the Developer ribbon tab visible to record a macro, or do you? This old-fashioned keyboard shortcut will open the record macro dialog. Pressed in sequence, not held down. Alt T M R You can also use the little icon next to the Ready at the bottom left corner of the screen. Once you start recording the small square icon to stop recording appears in the same spot.
I covered a solution to sorting and ignoring the sign a couple of years back, but it is time to revisit this thanks to dynamic arrays.
(more…)The IBM PC was launched 12 August 1981 – 40 years ago – wow! When I was looking at my first Excel articles I found this article from 20 years ago. Its amazing that PC's are roughly the same price these days. I remember using an IBM PC at college in the early 80’s for word processing. Instead of using a typewriter for your assignments you could use the PC and printer. You had to book times to use it…
My first article was published on 10 August 2001 in the Accountants Weekly magazine. I have scanned the original article and it is shown below. 20 years later I thought I would update the 10 points from the article. The right mouse button is still a time saver and now has even more options. You can now Filter and Sort using the right click. Try right clicking the navigation icons on the left of the sheet tabs for a complete…
Let’s say you have a validation check in your file and you want to display a message based on the validation status. Here is one way to do that.
(more…)