Power Query does not have an IFERROR function. It does however have another function that performs the same type of calculation.
Category Archives: Excel Blog
Prefix Numbers Conditionally in Excel
Conditional Format to the rescue
Let’s say we need to put a prefix in front of a number to identify the period being used. Whether that be year, month or week.
Validate a whole number in Excel
Very INTeresting
If you need to confirm a number is a whole number you can use a function with a short name.
Automating bullet points in Excel
Great for text boxes
Some people like to use bullet points in text boxes. Here is a simple technique to insert bullet points based on a list of entries in an Excel sheet.
The New LET Function in Excel
LET there be rock!
If you have the subscription version of Excel you may have the new LET function. This function lets you capture variables within a formula. Let’s see an example.
Single Accounting Underline
Great for headings
I learned something new recently about underlines. They are not all created equal. The Single Accounting underline has some advantages.
Use an Outlook template for an email
Why is it so hard to do?
Macros are designed to reduce keystrokes and mouse clicks. Here is a perfect example. It takes seven separate mouse clicks to use an Outlook email template as a new email. Here is a macro that does it in one.
Excel VBA to confirm a range is selected
You need to type TypeName
One of the most powerful statements in VBA is Selection. This holds whatever the user has selected just before they ran the macro. Normally it is a range, but what if it isn’t?
Excel VBA to select the first cell in a range
Top left cell
If you need to refer to the first cell (top, left cell) in a range there is an easy way to do it.
How to Create Linked Array Syntax in Excel
It is now possible
One of the frustrations with using array syntax is that you always have to type all the entries between the curly brackets. You couldn’t link to cells. Well that has all changed with dynamic arrays.
Creating a list of files in Excel
Power Query solution
Let’s say that you need to create a list of files in a folder. This is possible using VBA, but it is also possible in Power Query and it’s much easier to do in Power Query.
Excel Slicers Trick
Controlling multiple reports hack
Slicers can control multiple pivot table reports in Excel. The problem is that if you use a slicer on one sheet to filter a report on another sheet it is difficult to see that a filter is in place. This technique also comes with a warning – see bottom of post.
VBA tip – please don’t leave your Next blank
Include the variable
In a For Next loop you don’t have to include the variable in the Next statement. But ….
Text for a week in Excel
Time to TEXT
When working with weeks in Excel you may need to show the start and end date of the week in the same cell. Here’s how you can do that.
Percentage Area Chart in Excel
Conditional Format technique
You can use a pie chart to display a percentage, but it wastes a lot of space. An alternative that takes up less space is an area chart.
Using SEQUENCE with Dates in Excel Part 3
How to highlight specific dates
In this post I finish off the Calendar matrix by adding holidays.
Using SEQUENCE with Dates in Excel Part 2
Time to enter the matrix
The SEQUENCE function returns sequential numbers. Let’s see how we can use it to create a Calendar matrix.
Using SEQUENCE with Dates in Excel Part 1
Date listing
The SEQUENCE function returns sequential numbers. Let’s see how we can use it with a list of dates.
Time to Play with Dynamic Arrays
Let play time begin
Many things that were hard or complex are now much simpler. Creating dynamic drop down lists based on previous selections used to be tricky in Excel. Dynamic arrays make it straightforward.
Switching Rows and Columns is Now Easier
TRANSPOSE rules
With the introduction of Dynamic Arrays in all versions of Microsoft 365 (formerly Office 365) it is now a lot easier to use the TRANSPOSE function.