Custom Function to Increment Dates
Excel can easily add days to a date but months, quarters and years require a slightly different calculation. Here’s a single function that does all three.
(more…)Excel Custom Function for Common Dates
Excel has an EOMONTH function that returns the end of month. You can hack this function to return the start of the month as well. I thought I would make it easy to return the first, last or middle of the month in one simple function.
(more…)Sequential Rank Custom Function for Excel
Excel does not have a rank function that returns sequential numbers. All Excel’s ranking functions can return duplicated ranking numbers. Here is a solution to creating a sequential ranking system with no duplicates.
(more…)Formatted Table Running Total Custom Function
I have shared a running total formula for a formatted table many years ago. Here is a better solution that I have converted into a custom function.
(more…)Identifying Locked Cells in Excel
The default setting in Excel is for all cells to be locked. If you want users to input in a cell you must unlock that cell before you apply sheet protection. You can use a formula to identify locked and unlocked cells.
(more…)Add Hierarchy to Excel’s SORT Function
The SORT function allows you to specify which column to sort by. You can also include a hierarchy to sort by more than one column.
(more…)Webinar Feedback November 2024
Financial Functions Part Two "This accounting webinar series is one of the best for practical CPD hours. It focuses on things we actually use in our day-to-day work, like Excel models and useful tips, instead of theory or topics we may never touch depending on your area of expertise. It’s clear, easy to follow, and you come away with skills you can put into practice right away. Highly recommend it for anyone wanting CPD that’s actually helpful." 10/10C WalthewNovember 2024
Grouping Images for Effect in Excel
Using textboxes by themselves can be a good way to add extra content to a spreadsheet. Combining a text box, an icon and an arrow with some colour may make it even better.
(more…)Excel Formula Updating Technique [Video]
A recent project required editing many formulas to insert an IF function to display the NA error in certain circumstances. Here’s how I did it.
(more…)Are you Partial to Calculations?
Excel has re-badged one of the Calculation Options in the Formulas tab - see below. This is a change relating to the new Python capabilities. The middle option used to ignore Data Tables (a What If feature on the Data ribbon tab). The newly named Partial option also ignores Data Tables plus any Python calculations that may take a long time to calculate. Python calculations are done in the "cloud" and require an internet connection.
Excel YTD Reporting Solution
Having Actuals, Budget and Variances for each month going across the page is a very common reporting construct. Here is an easy way to sum the correct YTD values as the year progresses.
(more…)Creating Sentences in Excel
If you want to automate journal descriptions or create sentences the TEXTJOIN function is your friend. It can combine words and insert the spaces for you.
(more…)Excel Dynamic Arrays and Print Ranges
Print ranges can accept dynamic arrays. This means you can set up print ranges that automatically expand or contract based on dynamic array spill ranges.
(more…)Nice feedback
It is always nice to open my email in the morning and receive these types of emails. I have wished Peter well in his retirement.
Using a Prefix for Excel Table Names
I have been using the tbl prefix for my formatted table names in Excel for a long time. There are a few good reasons I use it.
(more…)Selecting a Formatted Table in a Formula
You can select a formatted table when you have a cell or range selected in the table by pressing Ctr + A. But that shortcut won’t work when creating a formula that refers to a formatted table. To select the table in a formula you must click a cell in the table and press Ctrl + Shift + Spacebar.
Excel Macro for Yesterday
I frequently use the shortcut Ctr + ; to enter today’s date in a cell. I thought it would be useful to be able to enter yesterday’s date in a cell. I wrote a one-line macro to do it for me.
(more…)Excel Hyperlink Formula Solution
Hyperlinks in Excel are a great way to navigate around a file, but they can be easily broken if the sheet name changes. Try these solutions using a formula to create hyperlinks that don’t break so easily. In the image below there is a formula in cell C3 that creates a hyperlink to cell A1. Here is the formula. =HYPERLINK("#"&CELL("address",A1),"<link text>") Simply change the cell reference from A1 to whatever cell you want to link to. This works for a…
Creating Full Names In Excel
When joining parts of names together, you may want vary the characters you use to separate the name. Depending on the effect you may be able to use array syntax to control the characters and their location.
(more…)Excel Auto-Adjusting Drop-Down List
If you need to have a drop-down list automatically adjust and remove items as they are selected, here’s one technique.
(more…)Array Syntax Custom Function
Array syntax is a powerful feature in Excel. Array syntax allows you to create a list within your formulas which makes them self-contained. Typing array syntax is difficult as you need to use a combination of quotation marks, commas or semi-colons plus braces (curly brackets).
(more…)Hyperlink Drop Down List
I recently saw a post on LinkedIn about making a drop-down list for a hyperlink interface. It was using a range name and the ADDRESS function. I thought I could streamline it with the CELL and INDIRECT function.
(more…)Counting the Number of Cells in an Excel Range
Excel has functions to count the number of rows and columns in a range. It doesn’t have a function to count the number of cells in a range. We can still perform the calculation with the COUNTA function.
(more…)Some nice feedback
It was a nice start to the day to receive this email this morning.
Excel’s Magic Percentage Symbol
I recently saw a post of LinkedIn (from Patryk Samborski) that used the percentage symbol with the SEQUENCE function to produce a list of decimals and I thought I would have a play with that idea.
(more…)Working Backwards in Excel
I recently received a request to help with a salary packaging calculation. I thought I would share the solution and explain the technique to solve it. This is a case where we have a value we need to equal but don’t know the components that make it up. We are in effect working backwards to find the missing value.
(more…)Olympic Average in Excel
Averages are affected by outliers. If Bill Gates walks into a room the average net worth per person jumps substantially. In the Olympics some sports deduct the top and bottom scores before calculating the average score. Here’s a formula to do that in Excel. You need the subscription version of Excel for this solution.
(more…)Double Click the Excel Icon
You can close Excel down (with multiple files open) by double clicking the green Excel icon - top left of screen. This works for the other Office apps too. If you haven't saved a file Excel will ask if you want to. To close a single file down use the X on the top right of screen.
Using Emojis in Excel Formulas
You can use conditional formatting to insert symbols in cells. You can also use formulas with emojis. using range names makes it even easier. To insert an emoji icon in a cell you can use press the Windows key and the full stop. This opens the Emojis dialog box. In this example we are going to insert three separate symbols in formulas. I have named each cell that has an emoji. A1 = Tick, A2 = Cross and A3 =…
Excel and Range References
The typical range reference looks something like A1:A10. You always refer to the top left cell followed by the colon followed by the bottom right cell of the range. Did you know Excel can handle you entering the last cell followed by the first and it corrects it for you?
(more…)