Microsoft’s Money in Excel is worth looking at even if you don’t want to use it. There are some useful features and formula tricks you can use in your own workbooks.
Group Columns
Grouping columns or rows is normally for bringing together lists rows or columns. Grouping days into a week or weeks into a month with the option to sub-total.
On the Transactions tab, Microsoft uses Group in its unofficial role in tucking away extra columns. Instead of Hiding columns/rows, columns I to Q are grouped to keep them out of sight.
Click on the + icon to reveal the extra columns which have extra info and calculated columns (we’ll come back to them later).
Grouping columns makes no difference to the calculation. The cells can be referenced, just like Hidden columns/rows. All grouping does is keep the worksheet visually tidy and uncluttered.
IFERROR – error checking
In the rush to finish a workbook, it’s easy to miss important error checking. Money in Excel has many formula inside IFERROR() to stop nasty error messages appearing all over the workbook.
IFERROR() check the formula in the first parameter and if the formula returns an error message, the cell will show the second parameter instead of the error.
Index and Match
Take away the IFERROR element of the formula to see the common lookup formula that’s been around for many years — MATCH() combined with INDEX().
=INDEX(Table2[Category Type],MATCH([@Category],Table2[Category Name],0),0)
MATCH() looks up a value from a table or list and returns the relative position of the matched result in the table. In this case looking up the Category Name from the Category list (Table2)
(as opposed to Xlookup, Vlookup etc which return the value, Match returns a partial cell reference).
INDEX returns a cell value for a location in an array. Since Match() gave us the position in the table, INDEX will get the cell value for another column in the same array.
Why use Index() and Match()?
Why did Microsoft use Index/Match when there a better options available, especially in Excel 365?
INDEX and MATCH is a common but quite old-fashioned Excel trick. Money in Excel is for Excel 365 only, so it’s strange that Microsoft hasn’t used better and more up-to-date functions. XMATCH() is better and easier to use than MATCH().
For that matter, why not use XLOOKUP() to replace the entire formula with one straight-forward function? As you can see, XLOOKUP works perfectly well and includes an error trap too.
First day of the week
To calculate weekly summaries, you need to know what is the first day of the week. That’s what the ‘Beginning of Week’ formula does.
Strip out the IF and Iblank checks for a moment, the core formula is.
[@Date]-WEEKDAY([@Date],2)+1
Weekday() returns the day of week number for the date given.
The ‘2’ parameter tells Excel which day is the start of the week. ‘2’ means the week starts on a Monday = 1, Tuesday = 2.
Subtract the Weekday() value from the Date (which is an Excel serial date) to give the date for the start of that week.
All the transactions with the same ‘Beginning of Week’ value can be grouped together as happening in the same week.
Separating a date into day, month and year
Getting the month, year or day of an Excel date is easy, there are three functions to do just that.
DAY()
MONTH()
YEAR()
Money in Excel puts those values in three separate columns – M, N and O.
Strictly speaking those columns aren’t necessary because, as we’ll see, the simple formulas could be included in other formulas.
Having the values in separate columns can be done for readability and clarity. It’s also slightly more efficient if the same calculation is needed in many places.
Month of the transaction
Showing the Month and Year of a transaction (as an Excel serial value) is used to group transactions by month (similar to ‘Beginning of Week’ for weekly grouping).
The formula, without the error checking is …
DATE([@[Date_Year]],[@[Date_Month]])
Or the same formula with the original functions instead of intermediate columns.
DATE(YEAR([@Date]), MONTH([@Date]))
Date() returns the Excel serial date for a given year, month and day. The day isn’t given so ‘1’ is assumed. The column is formatted to show just the month and year.
Part Protected worksheet
Switching to the Categories tab, it’s an example of a partly protected sheet. The lock icon on the tab is the clue that the sheet is all or part protected from editing.
Categories are in two groups. The fixed in-built categories plus room for your own custom categories. Both are in the same table (Table2) but the protection for each section is different
The in-built categories have their cells locked – see Format Cells | Protection | Locked (the default).
For the custom categories the Protection is changed to not Locked (unchecked).
That means when Review | Protect Sheet is enabled the Locked cells becomes read-only and not changeable.
Single Categories list
Even though the in-built and custom categories are in a single table, there needs to be an ordered list of them combined.
Grouped off to the right of the categories tab are columns G to K which put all the categories into a single list. It’s not a separate table.
Tables not properly named
Money in Excel isn’t perfect, almost any worksheet can be criticized for the way it works or the formulas used. Some Excel worksheets aim for efficiency while others use longer methods that are easier for others to understand.
But there are elements of Money in Excel that are mystifying.
The two tables are left with their default names ‘Table1’ and Table2’ instead of renamed to ‘Transactions’ and ‘Categories’ which would be easier to understand. That makes it harder to read and understand the formulas … though maybe that’s the reason?
We’ve already mentioned using Index/Match instead of Xlookup. Given all the (justified) hoopla from Microsoft about Xlookup, why don’t their own Excel experts use it in an Excel 365 only application?
It would have been nice if Get & Transform / PowerQuery was used to import and manage the transaction list. But that would have exposed Microsoft’s continued refusal to put PowerQuery into Excel for Mac.
Exchange Rate support in Excel 365
