Excel has a few counting functions. But when it comes to counting entries in a cell it can be difficult if you are using formulas that return a blank cell. This is where the SUMPRODUCT function can come to the rescue.
Free Excel Webinar Recording – Excel Yourself 2018
Feedback score 89%
In December 2018 I reviewed four of my articles from the INTHEBLACK magazine from 2018. Download the materials using the button below and watch the video.
Includes extra content not included in the magazine.
This year’s smorgasbord webinar includes
- how to handle validations
- dynamically highlighting the current row in a table (uses a macro)
- centralising logic – how and why to do it – alternatives to the IF function
- creating a slope chart (includes a macro to speed up the process)
As always there will be a few more tips and tricks shared in the session.
Open Filter the Drop Down
If you have filters turned on and you are in the heading row of the table you can press Alt + down arrow to open the filter drop down.
You can then use the arrow keys to move up and down.
Related Posts
Everything has beauty, but not everyone can see.
Confucius
Nothing will work unless you do.
Maya Angelou
Distinct Count in Excel
The Data Model to the rescue
Counting is the poor cousin to summing in Excel. Not many people count things, but everyone adds up things. There is a special sort of count that can be useful. A distinct count counts unique entries and is hard to do with a formula. If you have Excel 2013 or a later version you can use a PivotTable to perform a distinct count.
The most difficult thing is the decision to act, the rest is merely tenacity.
Amelia Earhart
First and Last Cell Address in a Range
Following on from my last two VBA posts here is how you can identify the cell addresses of the first and last cells in a range.
Strive not to be a success, but rather to be of value.
Albert Einstein
Free Excel Webinar Recording – Mouse Shortcuts
Feedback score 93%
When I ran some face to face training sessions recently I was reminded how much people LOVE shortcuts. I did a keyboard shortcut webinar session in October 2018.
In November 2018 I shared lots of mouse shortcuts. Download the materials using the button below and watch the video.
Some of the topics and techniques you will learn include:
- the various cursor shapes and how to use them correctly
- right clicking options you may not know exist
- copying anything quickly with the mouse, even sheets
- drawing perfect circles and squares
- lining up charts and other shapes perfectly
- cool techniques with the Fill Handle
- something you may never have tried with the right mouse button
- using the the Office clipboard
- a trick to use the Series dialog
This session will demonstrate lots of techniques using the mouse and also using the mouse with the keyboard.
Even if you prefer using the keyboard you might learn a few useful new techniques.
VBA to Find the Row and Column Number Limits of a Range
First and last row and column numbers
In my last blog post I found the last used row and column numbers on the active sheet. This post lets us find the row and column extremities of a specific range.
Life is 10% what happens to me and 90% of how I react to it.
Charles Swindoll
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.
Excel VBA to Find the Last Used Row or Column
UsedRange used wisely
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.
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.
Highly recommend.
Controlling a Conditional Format with a Checkbox
Turn it off and on
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.
Change in Ctrl Key Selections
Latest versions
Do you use the Ctrl key and the mouse to select multiple ranges? Well you may want to watch this short video.
Excel VBA to Remove Filters
Its a one liner
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.
Missing Sheet Gridlines in Excel
Its a format
Ever seen a sheet with what looks like missing gridlines? Wondered what happened? The issue is a format.
Free Excel Webinar Recording – Keyboard Shortcuts
Feedback score 93%
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.