Spreadsheets

Show your latest 10 transactions automatically with TAKE and SORT

Build a recent activity box that shows your latest 10 transactions automatically with SORT and TAKE on a summary tab, plus an option for Excel before 365.

Editorial illustration for Show your latest 10 transactions automatically with TAKE and SORT

To show your latest 10 transactions automatically, sort the table by date with the newest first and then take the first ten rows: =TAKE(SORT(FILTER(A2:C500,A2:A500<>""),1,-1),10). Put it on a summary tab and it updates as you add rows. TAKE needs Microsoft 365; SORT and FILTER need Excel 2021 or later.

What are we building?

A small recent activity box for the top of a summary tab: the ten newest transactions, with the date, category and amount, so you can see what just happened without scrolling the whole sheet. Imagine your data sits on a sheet named Data with dates in A, categories in B and amounts in C, in rows 2 to 500.

The formula, one piece at a time

  • FILTER(Data!$A$2:$C$500,Data!$A$2:$A$500<>"") keeps only rows where the date is not empty, so blank rows at the bottom do not interfere.
  • SORT(that,1,-1) sorts by the first column, the date, and -1 means descending, so the newest comes first.
  • TAKE(that,10) keeps the first ten rows of the sorted result.

Put together in a cell on the summary tab:

=TAKE(SORT(FILTER(Data!$A$2:$C$500,Data!$A$2:$A$500<>""),1,-1),10)

The answer spills into ten rows by three columns. Type headings Date, Category and Amount above it by hand.

A worked example

Say the sheet has twelve transactions, with dates from 1 November to 12 November, one on each day. The newest is 12 November, so the box shows 12 November down to 3 November, ten rows in all. The entries for 1 and 2 November drop out. Add a transaction dated 13 November and the box shows 13 November down to 4 November, without you touching the formula.

Data on the sheetWhat the box shows
12 rows, 1 to 12 Nov12 Nov down to 3 Nov
13 rows, 1 to 13 Nov13 Nov down to 4 Nov
Only 6 rows so farShould return those 6; test it on your own sheet

What if two transactions share the same date?

SORT keeps tied rows in the order they appear in the data, so two payments on the same day stay in sheet order. If time of day matters, add a time column or enter the full date and time, and sort on that column instead.

How do I show it on a different tab?

Refer to the sheet by name, as in the formula above. If the sheet name has a space, put it in single quotes: 'My Data'!$A$2:$C$500. Format the date column of the box as a date, since spilled dates can appear as serial numbers until you do.

What can I use before Microsoft 365?

Excel 2019 and 2016 have no TAKE, SORT or FILTER, but LARGE and INDEX do the job with a helper column. In D2, make each date unique by adding a tiny row-based fraction: =A2+ROW()/100000, and fill down. Then on the summary tab, in G2 write =LARGE(Data!$D$2:$D$500,ROWS(G$2:G2)) and fill down ten rows, which gives the ten biggest keys, the newest first. In H2 write =INDEX(Data!$C$2:$C$500,MATCH(G2,Data!$D$2:$D$500,0)) to bring back the amount, and fill down. To show the plain date in another column, use =INT(G2). The fraction stops two same-day transactions from returning the same row twice. Empty rows have a date of zero, so they rank last.

Where this idea stops being enough

A recent activity box tells you what is new, not whether it is right. If you also want rows categorised, checked for duplicates and matched to receipts, you are doing the work a statement-importing tracker is built for. For a quick personal summary, the formula is neat.

Checklist

  1. Check that dates are real dates and amounts are numbers.
  2. Put the formula on the summary tab and leave the cells below it empty.
  3. Add headings by hand and format the date column.
  4. Add a test row with today's date and see that it appears at the top.
  5. If someone on older Excel will open the file, use the LARGE and INDEX version or send a values copy.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports