I have spent my days stringing and unstringing my instrument, while the songs I came to sing remain unsung.
Rabindranath Tagore (1864-1941)
Excel Formula Week Starting with a Monday
The WEEKDAY function has options
Let’s say you have a weekly roster. You have a date and you need to identify the Monday start date for the week that contains that date. The formula is fairly easy.
Make a Dent
Do you use Indenting in reports? Example below.
If you do, you make like this keyboard shortcut.
To increase Indenting press Alt H 6 pressed in sequence, not held down.
To decrease Indenting press Alt H 5 (is that an Outdent?)
Related Posts
Plan for the future because that’s where you are going to spend the rest of your life.
Mark Twain (1835-1910)
Reduce typing with variables in Excel VBA
Use a Constant variable
Sometimes when writing code you use the same text string in multiple places in your code. To make things easier you should capture that text string in a variable.
Extracting entries from a slicer
A simple hack
There is a complicated way to extract the entries from a slicer but there is also an easy way to do it.
Be curious, not judgmental.
Walt Whitman
Sort by value ignoring the sign
An absolutely simple function to the rescue
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.
If you done it, it ain’t bragging.
Walt Whitman
Navigation in the VBA Window
An Excel shortcut
I recently found a useful keyboard shortcut for the VBA code window. I tried an Excel shortcut that helped in the VBA window.
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.
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.
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
Counting Entries in Excel
SUMPRODUCT solution
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.
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.
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.
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.