SUMIFS can use wildcard characters, but the wildcards only work on text-based codes.
Category Archives: Tips & Tricks
Clearing All Formats in Excel
Sometimes with Excel formatting you just want to clear everything and start again from scratch. You can clear just the formats, and there is an icon you can add to the Quick Access Toolbar to make clearing all the formats earlier.
Changing row and column grouping in Excel
Excel allows you to easily hide and unhide rows and columns using a feature called grouping. There are two keyboard shortcuts that allow you to apply and remove grouping. These shortcuts can also be used to amend existing groupings.
Paste Special Values Shortcut
Excel has a new keyboard shortcut for Paste Special Values.
Ctrl + Shift + V
Formats Affect Excel’s Fill Handle
Applying a date format before you drag with the Fill Handle may save you some time. See why.
Drop Down Selection update
Woohoo!
It has taken a decade or so but Excel finally has an in-cell drop down that you can type a letter and reduce the entries listed – see screen shot below.
Excel Has Ordinals
Woohoo, I don’t know when this happened, but you can now get Excel to extend your ordinals when you drag with the Fill Handle and use things like 1st, 2nd, 3rd and 4th etc.
Type 1st January in a cell and drag the cell down.
It seems to work with ordinals at the start rather than at the end of a text string. So January 1st doesn’t work. 1st by itself does work.
Restarting Excel and Windows
Even now sometimes a full restart is a possible solution.
I recently had a macro returning a weird error message about an action taking too long. Restarting Excel did not fix it.
So, I restarted Windows and the macro worked as expected.
These days with so many apps running in the background, sometimes the only solution is a restart of Excel and Windows.
Turning it off and turning it on can still solve issues.
Extract Text Before a Number in Excel
Excel’s new TEXTBEFORE function simplifies extracting text from the left. In this example I share how to extract all the text before a number in a code.
Forcing a Spill with the Magic Plus Sign
I posted recently about a technique to force a function to spill if it didn’t spill automatically. I have since learned of a much easier way.
Excel VBA to Get to the Top Left of the Screen
Sometimes when you are using Excel VBA you need the screen to always display at the top, left of the sheet. Here is how you do it.
Rounding to the Nearest 9 Cents in Excel
A client recently requested a formula to round to the nearest 9 cents. This avoids getting to a price point. This is a common requirement in retail businesses. The solution was simpler than I thought it would be.
Open Power Query Shortcut
Woohoo ! A new shortcut to open the Power Query window.
Works in the latest subscription version of Excel.
Alt + F12
VBA Window Split
I just found out you can split the VBA code window. See images below.
Use the small icon above the right side scroll bar.
This can be useful if you have a long block of code and need to look at separate parts together.
Thanks to Bob Umlas the Excel MVP for sharing.
You can double click the split bar to remove it.
Excel’s New Navigation Pane
If you have the subscription version of Excel then you have access to the Navigation Pane. This allows you to navigate between sheets and see the structure of the sheets in your file.
One Minute to Excel #30 – Extract Data Based on Sheet Name
Shortcut to speed up name creation
Here’s a technique I use a lot to speed up report development.
Sheet names have to be unique, so they can’t be duplicated. This makes them great for department names or states.
This short video combines a few techniques to extract from a data set based on the sheet name.
All in less than a minute.
Excel Constant not a Constant
In Excel you can use the Go To Special dialog to find constants. These are cells that won’t change. Constants are things like labels, entered text, numbers, or dates. But there are cells that won’t change that Go To Special won’t identify as a constant.
Task Manager
Wow – if you right click the Taskbar (bottom of the Windows screen) you can access the Task Manager.
No more Ctrl + Alt + Del – woohoo!
Related Posts
Browser Mouse Tip
In a browser you can open a link in a new tab by middle clicking – (mouse wheel click) – the link.
Woohoo!
Date Alignment Trick in Excel
Text alignment in Excel is versatile. If the column isn’t wide enough to display the text, it will display over the next cell. Date and number alignments are not so forgiving. If the column isn’t wide enough the cell with display the ### symbols or the scientific format. Here is a function technique to get around the limitation.
Circular Reference in Excel but the Circular Reference Button is Greyed Out
Sometimes Excel is not very helpful when it tells you that there is a circular reference in your file. If that is the case here is a technique to help you find where the circular reference is.
Array Syntax in Excel Formulas
With dynamic arrays making array calculations more accessible and easier to use here is a hack for using array syntax in Excel formulas.
Formatted Table – Change Default Colour
The standard colour for a formatted table is blue.
If you use Ctrl + T to create a table, that’s the colour Excel uses.
You can change the default.
On the Home ribbon tab, click the Format as Table drop-down and right click the colour scheme you want to set as the default.
Select Set As Default from the menu.
Done!
Related Posts
Selecting a Column Range within a Merged Cell in Excel [video]
I am not a fan of the merged cell format. It causes more problems that it solves. One issue you will face is trying to select a single column range within a range that has a merged cell. Here is how you handle it.
This post is a video post as it easier to show the problem and the solution in a video.
One Minute to Excel #29 – Nine Names in a Minute
Shortcut to speed up name creation
In this short video I demonstrate how to create range names quickly based on labels. Range names are a powerful formula feature. I also demonstrate their use.
General Format
The General format is Excel’s default format.
It doesn’t really do much. It can be handy to use though if Excel applies a format you don’t want, like a date or a percentage.
The keyboard shortcut to apply the General format is
Ctrl + Shift + ~
Be careful if you forget the Shift key as it will display formulas. If that happens, press it again (without the Shift key) and it will revert back to normal.
One Minute to Excel #28 – Eight More Charts in a Minute
Fast mouse work to the rescue
OK in the last video I cheated and used Sparkline charts to create 8 charts in a minute.
This time I set myself a real challenge to create 8 real charts in a minute.
Its close – check out the video and learn a few useful techniques.
Navigation Columns in Excel
Great for long reports
When working in long spreadsheets it can be a good idea to use a navigation column to make it easier to move between the different sections.
One Minute to Excel #27 – Eight Charts in a Minute
Sparklines to the rescue
Let’s see if I can create 8 charts in a minute – one for each state and territory in Australia
I cheat a little bit and use Sparkline charts. If you haven’t seen Sparklines before check out the video.
One Minute to Excel #26 – Fix AutoSum’s Weakness
Get the right range every time
AutoSum’s cryptonite is a blank cell – it stops AutoSum in its tracks every time.
Here’s how you can avoid AutoSum’s blind spot.