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…)