I wrote a blog post a few years back showing how to add up numbers formatted as text. If you have the subscription version of Excel you have another solution.
I was recently working with some data that had some issues with the sign on the quantities. The quantities should have had the same sign as the associated dollar amount, but they didn’t. Here is how I fixed it.
Power Query does not have an IFERROR function. It does however have another function that performs the same type of calculation.
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.
If you need to confirm a number is a whole number you can use a function with a short name.
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.
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.
I learned something new recently about underlines. They are not all created equal. The Single Accounting underline has some advantages.
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.
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?
If you need to refer to the first cell (top, left cell) in a range there is an easy way to do it.
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.
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.
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.
In a For Next loop you don’t have to include the variable in the Next statement. But ….
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.
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.
In this post I finish off the Calendar matrix by adding holidays.
The SEQUENCE function returns sequential numbers. Let’s see how we can use it to create a Calendar matrix.
The SEQUENCE function returns sequential numbers. Let’s see how we can use it with a list of dates.
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.
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.
I learned something new recently watching a recorded webinar by Jon Peltier. That’s not unusual, he is a charting legend.
The SUMPRODUCT function has been my favourite function for about 20 years. It is so flexible. Soon it will be redundant thanks to dynamic arrays.
Linking to a text box in a sheet is straightforward, unless you want to copy that linked text box to another sheet and retain the link. Here is how you do it.
To link to a text box you click the text box and then click in the Formula Bar and press = and then click the cell to link to and press Enter.
This works OK on the sheet but if you copy the text box to another sheet it links to the same cell in the other sheet. If that’s what you want, great. If it isn’t then you need to use this technique.
Text box copy technique
Click the text box click in the Formula Bar and press = then instead of clicking on the current sheet click on another sheet tab and click a cell in another sheet then return to the current sheet and then click the cell you actually want to link to and press Enter.
By doing it this way the sheet name is included in the link and that ensures the link is kept when you copy the text box to another sheet.
You could also manually type the sheet name into the Formula Bar, but using the mouse is much easier.
It is best practice to use grouping to hide and unhide rows in Excel. I recently saw a technique that also displays a message.
You can easily link a textbox to a cell. If you change the link you may have an issue with the text format used.
A few years ago I wrote an article on extracting the end of quarter date from a date. I recently had a query that was related and I tweaked the previous solution to solve it.
On a recent Webinar I was asked a question about an unusual date structure that was imported. The structure dd.mm.yy was not recognised by Excel as a date. Here is formula that fixes it.
Below is an example of the date issue.
The formula in cell B2 is
As you can see the dates in column A are left aligned. That is a clue that they are not recognised as dates in Excel. Dates are right aligned.
The SUBSTITUTE function replaces the full stop between the numerals with a / and makes it look like a date.
This isn’t sufficient as the SUBSTITUTE function will return text. The *1 at the end converts the text date in to a real date that Excel recognises.
Note: Power Query can also automatically fix dates like these when it imports data.
The Indian Financial Year start on 1 April. Like Australia its Financial Year month numbers can be painful. Here is a formula to sort them out.