Tricks inside Money in Excel

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

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.

image 62 194x296 - Tricks inside Money in Excel

Click on the + icon to reveal the extra columns which have extra info and calculated columns (we’ll come back to them later).

image 63 473x144 - Tricks inside Money in Excel

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.

image 64 473x195 - Tricks inside Money in Excel

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.

image 65 473x161 - Tricks inside Money in Excel

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.

image 66 473x245 - Tricks inside Money in Excel

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.

image 68 459x296 - Tricks inside Money in Excel

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).

image 69 473x135 - Tricks inside Money in Excel

For the custom categories the Protection is changed to not Locked (unchecked).

image 70 473x112 - Tricks inside Money in Excel

That means when Review | Protect Sheet is enabled the Locked cells becomes read-only and not changeable.

image 71 271x296 - Tricks inside Money in Excel

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.

image 72 461x296 - Tricks inside Money in Excel

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.

Money in Excel goes public

Getting rid of Money in Excel

Exchange Rate support in Excel 365

subs profile e1563205311409 - Tricks inside Money in Excel
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address