👩‍💻👩💻 Spreadsheet Banking | Basic Functions

Working with Spreadsheet Banking in Excel

How to:

• Reference a table

• Fetch a single table column

• Do calculations on a table

• Do complex filtering with tables and live-sync

If there are other functions that you use and would like to share, please feel free to contribute to the page here or pop us a mail, and we'll add it. ❤️

An intro to working with Excel tables and formulas in Spreadsheet Banking.

How to reference a table

✨ Reference your accounts: tblAccounts

✨ Reference your transactions: tblTransactions

Fetching a single table column

Functions for Accounts/Transactions

Requirement: I want to reference my ID for all my transactions.

Function: =tblTransactions[ID]

=tblTransactions[ID]
=tblTransactions[AccountNumber]
=tblTransactions[Date]
=tblTransactions[Description]
=tblTransactions[Debit]
=tblTransactions[Credit]
=tblTransactions[Balance]

*Sub in Accounts to see account information

Transforms on functions

Requirement: I want to show you the first two letters of my transaction description.

Function: =left(tblTransactions[Description],2)

Doing calculations on a table

Calculations on functions

Requirement: I want to see a sum of all my transactions coming off my account.

Function: =sum(tblTransactions[Debit])

Fetching table headers

Requirement: I want to see what the headers of my table are.

Function:=tblTransactions[#Headers]

Complex filtering with tables and live-sync

Filtering within your transactions/Accounts

Requirement: I want to filter the description of all my transactions only displaying the first two letters.

Function: =(left(tblTransactions[Description],2)

**Requirement: `**I want to filter the description of all my transactions only displaying the first two letters for a specific account.

Function: =(left(tblTransactions[Description],2), tblTransactions[Account Number]=Playground!B4

Requirement: I want to filter the of all my transactions greater than R5000 for a specific account.

Function: filter(tblTransactions[Debit]),(tblTransactions[Account Number]=Playground!B4)*(tblTransactions[Debit]>5000)

Search Term

Requirement: I want to filter all my transactions for my account that contain the search term ‘Uber’ (Uber in cell D17) using the first 4 letters of the description.

Function: tblTransactions[[Description]:[Debit], left(tblTransactions[Description],4)=D17