Upgrading my accounting spreadsheet with Numbers’s latest update

I get that maybe most people don’t get as excited about an update to Apple’s spreadsheets app, but I am, frankly, a Numbers enthusiast.¹

So when I saw that this past week’s update to Numbers 14.4 added “over 30 new functions”, I was intrigued to see what was available²—and, more to the point, if there was anything that could fill in some gaps in functionality.

The newer functions like SORT, FILTER, and LET certainly won’t blow away any Excel users, who have had those—and more—for some time, but as a Numbers user, I’m just glad to see them join the party.

And, as it turns out, a couple of those do help me resolve some longstanding issues with one of my spreadsheets. Specifically, the freelance accounting sheet that I’ve been using for almost a decade.

The median is the message

One of the sheets in my Numbers file breaks down my income and expenses on a monthly basis, with some aggregate information like totals, minimums, maximums, averages, and medians. But because I create the table with the entire twelve months of the year, I have to make sure to discount some of the data or else it causes problems.

For example, telling me that my lowest monthly income in 2025 was the $0 I logged for October isn’t useful if it’s currently April. Likewise, having those zeroed out months can throw off the calculations for averages and medians.

Previously, for both the minimum and average I was able to use the MINIFS and AVERAGEIF functions respectively, keyed to a hidden field that said whether the month in question had passed or not.³ The MEDIAN ended up being more complicated, though, since there is no corresponding MEDIANIF function. I ended up just manually adjusting the cells it was looking at every month or two when I checked these tables. But because I had it on three separate tables (gross income, expenses, and net income), that was a bit of a pain.

Fortunately, the addition of the FILTER function solves this problem by letting me simply ignore months that haven’t happened yet, and instead just run my calculations on the subset of applicable values. I was not only able to create a median measurement I never have to update, but I could also remove that hidden month field and replace the AVERAGEIF function with a simpler filtered version too. All of that saves time and means less manual tweaking of the sheet.

I got receipts

Back when I updated my template in 2017 to add expense tracking, I mentioned that there was one feature I wanted to add but wasn’t able to at the time:

While Numbers still doesn’t have a way to link to a file, I set up an automatically-generated receipt ID, which I use as the file name for my receipts stored in Dropbox. (The ID is created when you check the related Receipt box.) It’s formatted as Year-Month-Day-First Five Characters of Expense Name-Amount in the hopes that I’ll never spend the exact same amount at the exact same vendor on the same day

It’s true that Numbers still doesn’t let you include a link to a file, even though it has long had a HYPERLINK function that can make text into a clickable link. That, however, seems to primarily be aimed at including a web URL or a mailto: link and it doesn’t let you use a file:/// URL.⁴

However, it occurred to me that I could use a URL scheme to run a shortcut. And surely I could create a shortcut that could simply open a specific file?

While I didn’t, strictly speaking, need any of Numbers’s new powers to do this, the LET function ended up being a timesaver, since it let me avoid having to do lengthy operations either in a separate, hidden field, or repeated within in a single cell.

I was thus able to drop my old naming formula into the new construction and use LET to define it as a variable called receiptname. Then I could not only display that value in the field itself, but also pass it to the shortcut I’d created, without having to write all those pesky functions a second time.

As for that shortcut, I actually made two versions: the first was a native one, but I discovered that every time I ran it for the first time on a specific file, it would pop up a privacy dialog box to approve the action. No thanks.

For my second crack at it, I switched to just using the Run Shell Script action to open the file via the command line, which bypasses the privacy dialog.⁵

I subsequently combined those two versions of the shortcut into one that detects which platform you’re on, using the shell script action if you’re on macOS while otherwise relying on native controls. That way it does at least work on iOS, even if you still have to contend with privacy warnings.⁶

Before I could declare victory, however, I discovered the need one final tweak: though the formula for my receipt file name already explicitly filters out spaces as well as the other characters caught by Numbers’s CLEAN function, I’ve had to replace ampersands with their URL-encoded equivalents.⁷ Since I’m using the URL scheme to pass the filename to Shortcuts, ampersands are a big no-no (they’re interpreted as part of the URL syntax) and I found that filenames that included them were routinely being truncated on their way into Shortcuts.

So, I’ve now fixed all the major problems with my accounting spreadsheet which means surely fame and fortune awaits! Any day now.