When writing VBA code it is common to need to know the last used row and/or the last used column. These values provide the edges of the sheet contents.
Life is 10% what happens to me and 90% of how I react to it.
Bold Your Headings
Apparently this is not widely known, but you should always bold the headings in your tables.
Then when you use Format as Table (Ctrl + t) on the Home ribbon tab the header row will be correctly identified.
This also applies to the Ctrl + Shift + L shortcut to insert the filter drop downs.
It also applies to the ranges used for charts.
In general ALWAYS BOLD your headings – it is something Excel looks for.
Ctrl + b is the bold shortcut.
Atomic Habits (2018)
A new book that covers how you can make small incremental changes over time that lead to large results.
Atomic means small, as in atoms.
I have followed James’s Blog (link below) for a number of years and this book covers topics from the blog plus new material.
His back story is interesting and inspiring.
It is a practical book and he provides ideas to increase your good habits and reduce your bad habits – useful strategies to do both are included.
Habits are automatic and many times unconscious, so the more automated and intentional you make your habits the more time you have for other important things.
You may need to read it a couple of times to get the most out of all his suggestions.
Here is a technique that allows you to turn off and turn on the conditional format without actually removing the conditional format. You may want to do this to print a sheet without the conditional formats being applied.
Do you use the Ctrl key and the mouse to select multiple ranges? Well you may want to watch this short video.
When creating macros that work with filters it is a good idea to remove filters at the beginning of the macro code. Here is how you do that.
Ever seen a sheet with what looks like missing gridlines? Wondered what happened? The issue is a format.
When I ran some face to face training sessions recently I was reminded how much people LOVE keyboard shortcuts. So I decided to update my keyboard shortcut webinar.
In October 2018 I shared lots of keyboard shortcuts. Download the materials using the button below and watch the video.
Using the mouse isn’t always the quickest way to perform tasks in Excel. Keyboard shortcuts can speed up your work and save you hunting through screens and dialogs. Some of the keyboard shortcuts you will learn in this session are:
- wrap text and other useful formats
- applying row and column grouping
- selecting a table quickly
- copying visible cells only
- apply and remove Freeze Panes
- returning after following a hyperlink
- how to avoid an annoying feature of formula and reference dialogs when you press an arrow key to move around
- get the most out of the Tab key
There will be lots of other shortcuts as well. Even if you prefer using the mouse you might learn a few useful new techniques.
Delete a cell comment
From a question in a recent webinar I found a keyboard shortcut to delete a cell comment.
Alt r d pressed in sequence, not held down.
If you have the latest Excel version or the subscription version, you may have noticed some refreshing improvements to PivotTables.
Adding current date to a cell comment
You may know that Ctrl + ; (semi-colon) will insert the current date in a cell.
Did you know it also works in a cell comment?
Let’s assume you need to fill a vertical range with all the whole numbers from 1 to 100. There are lots of ways to do this, but I think the keyboard could be the quickest.
Formatted Tables allow you to create formulas that automatically copy down as the table expands. To create a running total in a column you have a couple of options.
Advanced Filter Keyboard Shortcut
To open the Advanced Filter dialog use Alt A Q pressed in sequence, not held down.
My free Excel webinar for September 2018 covered What If Techniques. Download the materials using the button below and watch the video.
Content applies to Excel 2010 and later versions. You will need to install the Solver Add-in – instructions in the manual and video.
- Goal Seek – simple what-if changes
- Solver – advanced what-if analysis
- Scenario Manager – handling different sets of inputs
- Data Tables – single and double variable sensitivity analysis
- NEW – Forecast sheet
Timeline charts are an effective way to display events over time. You can use a new Excel 2016 feature to easily create a timeline chart.
Would you like to change the format of all your formula cells so they have a different fill colour or font? There is a way in Excel 2013 onwards.
In a recent webinar on conditional formatting I was demonstrating how to create a horizontal progress bar using conditional formats when someone asked an interesting question about creating a vertical progress bar. It is possible and in this blog post I will explain both techniques.