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.
It is common to have a Factor in a cell or cells in a budget to allow you to easily tweak the numbers by a percentage. If you want to add a Factor to an existing budget model here is how you can do it.
There are times when estimating numbers that an average is a good message to use.
Excel has a right click Filter option that speeds up filtering by a single value. You can hack that shortcut to do a little bit more.
Do you hate using the keyboard for formulas? Does having to peck the = or + key to create a formula annoy you? Well there is an answer.
With introduction of Dynamic Arrays in Office 365 Excel has one new formula symbol and another that was previously only use in formatted tables.
I typically turn gridlines off on my sheets and then use borders for the lines. I have added icons to my Quick Access Toolbar to speed up the process.
Seeing we may be stuck at home for a while here is a suggestion regarding reading books online. If you are a member of CPA Australia you can read certain books online for free.
Dynamic arrays have the potential to change the way Excel spreadsheets are created. They were released in the January 2020 wave of updates to the Office 365 subscription version.
I wrote about a formula to extract the sheet name many years ago, but I just found an issue with it. Click here to see older blog post.
It is possible, but it involves a setting change that comes with a warning.
In Excel we tend to work in years, months or days. There may be times when you want to work in weeks.
Being able to use wildcard characters like * and ? in searches is something we are used to having. Now XLOOKUP allows you to do those types of searches in Excel.
Did you know that VLOOKUP actually looks down? That’s right, it finds the first entry from the top down. The default XLOOKUP also looks down, but can start from the bottom and look up.
The INDEX and OFFSET functions can return a reference to a cell, something VLOOKUP can’t do. XLOOKUP can return a cell reference, let’s see one way to use it.
It’s finally here, well it is if you have the monthly update cycle of the subscription version of Excel.
Recently I found an interesting way to handle plurals in Jordan Goldmeier’s book on dashboards.
In my previous post I showed how to have a flexible data validation and how to validate it. This post will look at adding conditional formats to inputs and validations.
The most common type of Data Validation in Excel is a drop down list. In the example below I allow the user to select a year, then a month (using a drop down) and then enter a valid day in the month.
You can create a hyperlink to another Excel file in Excel but you can’t control what sheet or cell will be active. Well you can actually, but you need to know how.