Sort by value ignoring the sign
I recently received an unusual request about sorting. They wanted to sort in ascending order but they wanted to ignore the sign of the values. So -44 would be next to 44. (more…)
Navigation in the VBA Window
I recently found a useful keyboard shortcut for the VBA code window. I tried an Excel shortcut that helped in the VBA window. (more…)
Free Excel Webinar Recording – Excel Yourself 2018
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. CPD note - if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances. Download Webinar Materials https://vimeo.com/306102144 Includes extra content not included in the magazine. This year's smorgasbord webinar includes how to handle validations dynamically highlighting…
Open the Filter 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.
Counting Entries in Excel
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. (more…)
Distinct Count in Excel
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. (more…)
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.
(more…)Free Excel Webinar Recording – Mouse Shortcuts
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. CPD note - if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances. [button…
VBA to Find the Row and Column Number Limits of a Range
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.
(more…)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…
Excel VBA to Find the Last Used Row or Column
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.
(more…)Controlling a Conditional Format with a Checkbox
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. (more…)
Change in Ctrl Key Selections
Do you use the Ctrl key and the mouse to select multiple ranges? Well you may want to watch this short video. (more…)
Excel VBA to Remove Filters
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.
(more…)Missing Sheet Gridlines in Excel
Ever seen a sheet with what looks like missing gridlines? Wondered what happened? The issue is a format. (more…)
Free Excel Webinar Recording – Keyboard Shortcuts
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. CPD note - if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances. Download…
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.
Updates to Pivot Tables
If you have the latest Excel version or the subscription version, you may have noticed some refreshing improvements to PivotTables. (more…)
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?
Filling a large range in Excel with a series [Video]
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.
(more…)Running Total in a Formatted Table
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. (more…)
Advanced Filter Keyboard Shortcut
To open the Advanced Filter dialog use Alt A Q pressed in sequence, not held down.
Free Excel Webinar Recording – What If Techniques
My free Excel webinar for September 2018 covered What If Techniques. Download the materials using the button below and watch the video. CPD note - if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances. Download Webinar Materials https://vimeo.com/291843750 Content applies to Excel 2010 and later versions. You will need to install the Solver Add-in - instructions in the manual and video.…
Creating a Timeline Chart in Excel 2016
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. (more…)
Auto Format Formulas in Excel
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.
(more…)Horizontal or Vertical Progress Bar in Excel
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.
(more…)Range Names and Macros
When you record a macro that refers to a particular cell or range on a particular sheet in Excel the range reference is hard coded into VBA (macro) code. Unfortunately this means if rows or columns are inserted or deleted in the reference range the code is not updated. There is an easy way to get around this.
(more…)Related Tables and PivotTables
When you are building a PivotTable based on two related tables you may see unusual layouts that don’t make sense. Don’t worry, when you add values to the table all will be fixed. (more…)
Conditional Format Feedback
The Conditional Format webinars I ran in August were popular and well received- 94% feedback score. An email I received recently is shown below. To watch the recording and download the materials click here.
Power Query
Its always great to receive e-mails like this. Power Query is a game changer.