And to do this for formatting, if you go to:
Format > Number > More Formats > Custom
And enter
[red]-0.00;0.00;-
It will invert the values around the way you want. This is only visual mind - but it’s useful for tables etc.
1 Like
Don’t be silly! I’m reasonably handy with Excel, but the two guys above are ninjas!
Don’t be put off, there’s always people willing to help.
3 Likes
I think if I were to rate how good I am at spreadsheets I’d give myself a #VALUE!
7 Likes
Oh don’t start this, spreadsheet jokes are where I excel!
5 Likes
Guys lets stop this. I don’t have time =now() to get into this.
2 Likes
But I do have a joke for you.
How many excel users does it take to correctly format a cell?
Sunday, 01 January 1900
7 Likes
Here’s what I do. It’s a bit convoluted, but simple enough.
First thing is to arrange both data sets so the columns match up and the column headers match (so ColA is the date in both etc). I do this with two queries. For the Monzo data set it was
=QUERY([Range],"select B,D,G,E,H,L")
Which gave me the columns I wanted (date, name, category etc) in the order I wanted them.
I made sure that the credit card column headings were the same as the Monzo ones and then did the same query for the credit card data, with each query on a different sheet.
Then the magic!
In a new sheet;
=query({Range1;Range2},"Select * where Col1 is not null Order by Col1",1)
So it makes an array from the two ranges, then selects all the columns, but not any blank rows, and sorts them by ColA (date column). The 1 at the end tells the query that there is a header row.
I then protected and hid the sheets, so that the only sheet I can input data is the original credit card sheet. Whenever I do this, or whenever Monzo update their data, the final query updates, and I then run other queries and pivot tables from this.
Hope that helps!
3 Likes
You can do calculated fields in Pivot Tables which would accomplish the same thing.
Edit; Sorry, you’ve already had an answer. 
Ahh gotchya - so your first Query filters your data in the way and order you want them.
Then number two selects for “anything” - * - but not blanks.
So the critical part of the 2nd query is that the arrays have to both be structured in the same order.
Theoretically then, you could use this to merge a Joint Monzo CSV export to the live Personal export.
Sure.
I’m not sure if you’d be able to tell which account the transactions came from (I don’t have a JA, so haven’t seen the output), if that mattered.
I don’t know if they ‘have’ to be, or what happens if they aren’t, but it seemed sensible that there was less opportunity for error if they were.
That’s a good point, there’s nothing in the raw export data that I can see that could be used to distinguish between personal and joint account data.
Hello - sorry if this is slightly off-topic but as this is an active thread, I thought it would be better to add in here rather than start a new thread for what is a pretty simple question that will probably only need one answer.
If I export to Google Sheets and then go back into the app and change the categories of a bunch of historic transactions (into new custom categories), will all of the historic data in Sheets update to reflect this? Or does the “live” data updates just incrementally add new transactions?
Essentially I’m trying to figure out if I ought to go through and do my category changes before I export?
EDIT for clarification and a bonus one for a typo
It will live update, it’s ever updating so once you’ve turned it on, whenever you make a change in the app, the sheet will reflect it within a few seconds.
1 Like
Ta.
1 Like
As @Revels said, it will update live, so as you change the categories in the app, the sheet will update.
A caveat, though. If you use custom categories, and rename the custom category, the sheet seems to reflect the old custom category.
1 Like
Very useful to know, thanks
Sorry, this is off topic again, but just looking at my data and these were my first two deposits back when Monzo was just a prepaid card 
No prizes for guessing what made me seek out a fintech bank!
7 Likes
Ooh I think I either missed or forgot this fact before. That’s a real shame.
Sorry if this may have already been covered, but is there a way to separate debits & credits?
For example, totalling the ‘general’ category doesn’t give me the correct spends for this month, because I also have some credits on the general category.