SquareUsUp
The ultimate Excel-based tool for tracking shared group expenses
SquareUsUp is a full-featured, easy-to-use tool that lets a group of people – a travel group, a lunch group, a sports group, club members, family members, roommates, etc. – track their shared expenses and know at all times what they owe or are owed within the group. This knowledge frees everyone from continually having to make monetary transactions to square up debts, allowing debt settlement to be done less frequently and when it’s convenient.
SquareUsUp in a nutshell
SquareUsUp was designed to be able to handle just about any group expense-sharing situation imaginable – and also to be a simple app that can be learned in minutes. Here is a brief list of its capabilities, many of which are not supported by other popular expense-sharing solutions:
Allows sharing of expenses that don’t involve all group members
Allows sharing of expenses unequally among group members
Allows the payer to assume no share of an expense
Allows recording of unshared expenses
Allows recording of payments made between group members
Supports ongoing changes to group membership
Eliminates roundoff discrepancies
Provides fully error-checked data entry and editing
Provides fill-in forms as an alternative to direct spreadsheet entry
Displays a summary of charges, payments, and balances for each group member
Produces personal transaction reports for each group member
Provides guidance for squaring up everyone in the group
You must have Microsoft Excel to implement a SquareUsUp project, but virtually no knowledge of Excel is necessary to use it. Unlike some of the big names in expense-sharing software, group members are not required to download and register their own copies of the app. A SquareUsUp spreadsheet can be maintained by a single person or it can be stored in a network or cloud location to allow group members to share its use.
Despite this user guide’s rather daunting length, SquareUsUp is really very easy to understand and use. Once you’ve read through the example in the following section, you’ll already have enough information to use the system productively. The remainder of the user guide can come in handy whenever you have a question or encounter a situation you’re not sure how to handle.
A SquareUsUp example
Each row of a SquareUsUp spreadsheet records a single monetary transaction. A transaction happens whenever anyone in the group spends money – either to pay an outside party for a product or service or to pay someone within the group.
SquareUsUp transactions are recorded using a simple notation that is best explained via an example. The sheet below tracks the expenses of a four-person tennis group.
Each transaction row contains a date, a transaction name, financial details of the transaction (in the Payments section), and the resulting balances for each group member (which are calculated automatically in the Balances section).
The shaded row directly under the names in the Balances section contains initial balances that were brought over from the ending balances on the previous sheet (here, the sheet for the year 2023). Amy’s negative balance (which is displayed in red and in parentheses) indicates she owed money, while everyone else (with positive balances shown in black) was owed money.
A transaction in the Payments section will show a dollar amount for anyone who spent money in that transaction. One S or more (always displayed with a blue background) in the same row indicates group members who shared responsibility for an expense but did not pay anything. When the expense is not shared equally, the S is followed by information that describes what portion of the expense each person is responsible for. When a row has an R (always displayed with a green background) instead of an S, the payment was not shared but was instead made directly to the group member designated by the R.
Shown below is a row-by-row explanation of the Payments section entries in the example.
Each row in the Balances section is computed automatically from that row’s Payments section entry, adding or subtracting from each person’s previous balance to show how much money is owed to the person by the group (positive numbers in black) or how much money the person owes to the group (negative numbers in red and parentheses). The balances in each row will always total to zero.
The Summary section appears below the list of transactions to provide a concise summary of the sheet’s transactions. Each person’s ending balance is calculated as the starting balance minus the total charges accumulated plus the net payments made (amount paid minus amount received). Ending balances will always match the numbers in the last Balances section row.
SquareUsUp includes utilities to ease transaction entry and editing, customize your project, change group membership, add new sheets, and view, print, and save reports. These utilities are accessed via the 11 buttons at the top of each SquareUsUp spreadsheet. Their use is pretty self-explanatory, but they are also fully documented in the sections that follow.
Getting started
Pictured below is what you see when you open the SquareUsUp project template file, which lets you create a new project.
When you load the project template file, the Create a Project form will pop up to guide you through project setup, where you give the project a title, customize other aspects of the sheet, identify the group members (users) who will be sharing expenses, and save your customized file as a new SquareUsUp project. You may change your project customizations at any time via the buttons that appear at the top of every SquareUsUp spreadsheet.
Once you’ve created a new SquareUsUp project, you’re ready to start entering transactions.
Entering transactions
Transactions may be typed directly into the spreadsheet. You can use the Grid Entry button to move the cursor to the first empty row – or to open up a new row below the last displayed row if no empty rows are available to type a new transaction.
As an alternative to typing directly into the spreadsheet, the Form Entry button can be clicked to pop up a form that allows entry of a new transaction via fill-in fields, option buttons, and checkboxes. The entry form input fields will change to match the type of transaction specified by the option buttons.
When OK is clicked, form entries will be checked for validity before creating a new transaction. If no errors are found, the new transaction will be written into the first empty row in the spreadsheet or, if there are no empty rows, into an automatically inserted row after the last displayed row.
Here, the last transaction of the tennis group example is being added to the spreadsheet as a new row (row 18). We can see that Dave paid $24 for tennis at Sherman Oaks Park, with Amy sharing equal responsibility for that payment. All other types of transactions can be entered using this same form by simply clicking the appropriate option button(s) at the right side of the form.
It’s a matter of personal preference whether you enter new transactions directly into the grid or via the input form. This same form may also be used to edit an existing transaction, as discussed in the Editing Transactions section.
Transaction types
Each time there is a monetary transaction within the group – whether someone pays an outside vendor or pays someone else in the group – it should be added as a new row in the spreadsheet. SquareUsUp recognizes four kinds of transactions:
1. Shared payment – One member of the group makes a payment for one or more others in the group. The amount paid is entered in the Payments section under the member who made the payment, and an S is entered on the same line for everyone responsible for a part of that payment (turning those cells blue). If the amount is to be shared equally by everyone (including the payer), only the S is typed; if unequal shares need to be assigned, each S should be followed by a qualifier (the same type of qualifier for each person) that defines the portion of the payment that person is responsible for:
a. Percent shares – The qualifier is a percentage ending with the % symbol. Percentages must all be greater than 0 and no more than 100. The payer’s share is the sum of all the percentages subtracted from 100%.
b. Fractional shares – The qualifier is a fraction, i.e., two numbers separated by a slash (/). Fractions must all be greater than 0 and no more than 1. The payer’s share is the sum of all the fractional shares subtracted from 1. This option is handy for shares that are hard to express as a decimal or percentage (e.g., 1/3).
c. Dollar shares – The qualifier is a dollar amount starting with the $ symbol. Dollar amounts must all be greater than 0 and no more than the amount paid. The payer’s share is the sum of all the dollar shares subtracted from the amount paid. This option makes sense to use when the exact amounts everyone owes are known (e.g., when paying a restaurant bill).
2. Internal payment – One or more members of the group makes a payment to another member of the group. The amounts paid are entered under the members who made the payments, and an R is entered under the recipient of the payment (turning that cell green). Internal payments are made in order to pay off debts within the group – or perhaps when money is being loaned.
3. Unshared payment – One or more members of the group make payments that are not shared by any other group members. While unshared payments have no effect on balances within the group and therefore could be ignored, recording them updates the charges and payments in the sheet summary section, allowing the sheet to contain a record of all group-related expenses, not just shared expenses.
4. SquareUsUp payment – All debts in the group are paid off to get all balances back to zero. As an alternative to recording a lengthy series of internal payments, a SquareUsUp payment is a single transaction with only a date and transaction name to document that everyone has squared up, and it adjusts summary totals accordingly. This transaction saves time and effort and eliminates unnecessary complexity on the sheet.
As new transactions are entered or existing ones are edited, the entire Balances section is instantly updated. If an invalid transaction is entered, the Balances section turns red on that row, and balances are not calculated for any subsequent rows. Invalid initial balances or Payments section cell contents will also turn red.
When the initial balances row is red, there’s either an invalid balance or the given balances don’t sum to zero. Adjusting initial balances via the Adjust Balances button can help you identify and fix the problem. An invalid transaction row can be diagnosed similarly by editing it via the Form Entry button. Transaction rows are simple enough that a problem should almost always be obvious. Some of the things to look for when diagnosing a problem include:
A payment that’s not a positive number
An entry that’s not a payment amount and doesn’t start with an R or S
A transaction with an S entry but no payment entry or multiple payment entries
A transaction with an R entry but no payment entry
A transaction with more than one cell containing an R
A transaction with both R and S cells
An entry with an R followed by anything else
An entry with an S followed by an invalid share value
A shared payment that mixes different types of shares
A shared payment whose values total to more than the value of the payment
Editing transactions
An existing transaction can be changed at any time. Changes will cause balances to be recomputed automatically in that row and in all subsequent rows. An alternative to typing changes directly into the spreadsheet is to highlight a transaction row by clicking on the row number – or by selecting at least the first two columns of the row – before clicking the Form Entry button. The highlighted row will be filled into the Edit Entry form (which is the same as the New Entry form), where it may be modified as desired.
The Edit History buttons at the top of the spreadsheet allow you to insert, delete, or reorder existing spreadsheet rows. These three buttons prompt for Excel spreadsheet row numbers to determine the rows to operate on. By placing the cursor on the row (or highlighting the range of rows) you want to operate on prior to clicking an Edit History button, those row numbers will be prefilled into the form, saving typing. To insert rows after the last displayed row on the sheet or to move rows there, you are allowed to enter a “before row” number that is one greater than the last displayed row number.
The Reorder Rows button allows you to not only move rows from one position to another, but also to sort the selected rows by date or by transaction name. This feature can be helpful if transactions were not entered in chronological order, or if you want to group like transactions together for easy inspection. When multiple transactions have equal dates or transaction names, they maintain their relative list positions from prior to the sort.
Notice that SquareUsUp’s options to insert and delete rows are not the same as Excel’s options to do the same. Using Excel operations to insert and delete rows would damage SquareUsUp’s spreadsheet structure, so these are among the operations you are prevented from performing on a SquareUsUp spreadsheet. Through the Edit History buttons, SquareUsUp strategically hides, unhides, and moves spreadsheet rows to insert and delete rows without damaging the spreadsheet.
Editing text
The Edit Text button allows you to change the three customizable titles on a SquareUsUp sheet and also to standardize the formatting of typed spreadsheet entries.
The default project title of SquareUsUp Payment Record can be replaced with a title that describes the project. The Transaction column heading can be modified to more accurately describe the nature of the transaction entries. And the sheet title (shown at the top of the grid and also in the Summary title box at the bottom of the grid) can be set to describe the contents of the sheet (most commonly, the time period the sheet covers).
While you may type directly into any of these titles in the spreadsheet (except for the sheet title in the Summary title box, which is automatically copied from the sheet title field at the top of the grid), an alternative is to click the Edit Text button at the top of the spreadsheet, which prompts for all three titles. Changing the sheet name here will change not just the displayed sheet title, but also the name of the Excel sheet itself. While it’s generally a good idea for the displayed sheet title to match the name of the sheet, this is not enforced. If you want to, you are allowed to type a new sheet title above the grid so that it is different from the Excel sheet name. And, as discussed in Advanced Topics, you may also change the Excel sheet name via Excel techniques, which will leave the sheet title unchanged.
Checking the Standardize Formatting of Transaction Entries checkbox before clicking OK will change transaction entries on the sheet as necessary to conform to SquareUsUp’s standard formatting style. This style is used when transactions are entered via Form Entry, but SquareUsUp accepts a wide variety of input styles on manually entered transactions (e.g., lower-case letters and extraneous or missing spaces). The Standardize option can improve the sheet’s readability and appearance without forcing you to tediously fix entries one at a time. It can also correct errors by removing spaces that are improperly inserted within numbers.
Creating a new sheet
If your project has a lot of activity over a long period of time, it is often useful to divide up the project among multiple spreadsheets (one spreadsheet per tab at the bottom of the Excel window) in your Excel file, which prevents a single sheet from getting too long and unwieldy. Most commonly, a separate sheet is created for each year of a project, each sheet being named for the year of the transactions it contains.
To create a new sheet, click the New Sheet button. You will be prompted for the name to give the new sheet. The sheet will be inserted just before (to the left of) the current sheet as an exact copy of the current sheet, but without any transactions in it and with initial balances set to the current sheet’s ending balances. Those initial balances will be linked to the current sheet if you checked that option (the default); otherwise, they will be set to the current sheet’s ending balances as fixed, unlinked values. The Adjust Balances button may be clicked at any time to modify these initial balances.
Changing users
Over time, people may enter or leave the group, so it’s important to be able to make changes to the user list. While a simple change of someone’s name can be made by just typing over the name in the Payments section, all user list changes – renaming, adding, removing, and reordering users – can be made via the Change Users button.
The Change Users button brings up the form that shows the current user list. Users with a non-zero balance and/or any transaction entries on this sheet show with their current column position in the spreadsheet and cannot be deleted because the sheet won’t balance if they’re removed. Other users are shown with a star instead of a number and can be deleted by simply deleting the user name in that row.
Type over any name to change the user’s name. Typing a name in an empty box will create a new user. New users will be assigned a default initial balance of $0.00, which may be changed later, if desired, via the Adjust Balances button. The user list may be rearranged via the up and down arrow buttons on each row, which move that user up or down in the list. The user order that is set here establishes the order of the user columns displayed in the spreadsheet. Blank rows in the submitted list will be ignored.
There is one caution about changing users on a project with multiple sheets. There should never be any problems modifying the user list on your most recent sheet. But if you modify the user list on a sheet whose ending balances are being used to set a newer sheet’s initial balances, you may cause the newer sheet to set improper initial balances. (Excel’s mechanism to automatically modify cell references doesn’t work here since SquareUsUp doesn’t use Excel’s standard cut-and-paste technique to rearrange columns.) If links on a more recent sheet are adversely affected when you edit the user list on an older sheet, you can use the Adjust Balances button (described next in Adjusting Initial Balances) to correct the problem.
Finally, because of the technique SquareUsUp uses to assure that row balances always sum to exactly zero despite potential roundoff issues, reordering users may cause user balances to change ever so slightly – usually by just a penny. This behavior is noted only as assurance that insignificant balance changes resulting from changes to user order is no cause for concern.
Adjusting initial balances
Users’ initial balances may occasionally need to be adjusted. You may type changes directly into the spreadsheet or you may click Adjust Balances enter new initial balances into a form.
An initial balance may be specified as a fixed amount, or, because your project may contain multiple sheets, it may be linked to another sheet’s ending balances. A link is an active connection to another sheet, meaning that any subsequent changes that alter the ending balance on the other sheet will automatically change the initial balance on this sheet.
An initial balance that is a link displays in Excel as a formula in the following format: equal sign, followed by the name of the sheet (often inside single quotes), followed by an exclamation point, followed by the Ending Balance section cell address (column letter followed by row number). For example, the formula =’2023’!C913 links an initial balance to the ending balance contained in the cell C913 on the sheet named 2023.
Use of the Adjust Initial Balances form allows you to view and enter links in a more natural way. You may type an unlinked balance directly in the Initial Balance column, or you may supply a sheet name and cell address to which this balance should be linked. A linked initial balance displays in blue alongside the sheet and cell to which the balance is linked.
Option buttons let you quickly reset initial balances to their current values or link all initial balances to the final balances on the previous SquareUsUp sheet. Links on the latter option are assigned by list position (first user on this sheet linked to the first user on the previous sheet, and so on). Any user on this sheet beyond the number of users on the previous sheet is given an unlinked balance of $0.00. If there is no previous sheet, or if the previous sheet has users with non-zero balances beyond the number of users on this sheet, this option will not be available.
Whether specified as numbers or formulas, initial balances must always sum to zero. Note that negative balances must be typed using a minus sign, not parentheses.
Producing reports
The Print Reports button can be clicked to view, print, or save various reports for the currently selected sheet. Reports are displayed via Excel’s Print Preview screen. From this screen you may click the Page Setup button to customize output appearance before clicking the Print button. The Print button allows you to specify the printer to use. If you select the Print To PDF printer (or a similar printer name), the report will not be printed but will instead be saved to a .PDF file whose name and location you specify.
The Balances Report lets you print the current sheet or save it as an image.
The Payments Report is the same as the Balances Report, but without the Balances sections of the sheet. This allows text to be displayed at a more easily readable size. Although running balances are missing, the Summary section at the bottom shows the final tallies.
The Summary Report lets you print or save the current sheet’s Summary section values. An example of this report, created for the Tennis Payment Record spreadsheet, is shown below.
The SquareUsUp Report lets you view, print, or save a list of users’ current balances. Additionally, it presents two suggestions for how to make a series of payments that will return everyone’s balance to $0.00. The first way is to simply have everyone who owes money pay what they owe to one person, who then pays everyone who is owed money. An alternative is to follow the report’s suggestion for squaring everyone up without the use of a designated person to collect and pay out the money.
A SquareUsUp report for the Tennis Payment Record is shown below.
The Transactions Report button lets you print, view, or save a separate list for each user, each list showing that user’s full set of transactions on this sheet. This allows each user to see only those transactions that he/she was involved in, as well as the effect of each transaction on his/her balance. Dave’s page from the Tennis Payment Record example is shown below.
Notice that for internal payments, the Transactions Report record will include the purpose of the payment if anything other than the default description of “Payment” was given. The purpose is extracted by ignoring the word “Payment” and a parenthesis, dash, colon, or the word “for” that follows it, as well as a parenthesis or period that ends the payment description.
Saving your work
The Save Work button can be used to save changes made to the Excel workbook. This button is redundant with (but simpler than) Excel’s Save and Save As menu options. It is provided for convenience and to minimize the requirement for Excel expertise.
Clicking the Save button on the Save Work form without filling in the file name will save the workbook’s changes to the currently opened file. Typing a file name will save the workbook to a new file name in the same folder. (To save to a different folder, it is best to use Excel’s Save As option – or to just copy the saved file to a different folder after the save operation is complete.) The file name should not include the Excel .xlsm extension, as that will be included automatically.
When saving a new project from a SquareUsUp template file, you are required to give the file a new name before clicking Save to avoid overwriting the template file.
When you try to close a SquareUsUp session, you may be asked if you want to save your changes – even if you’ve made no changes to your project since you loaded it. This is due to a quirk in Excel. As long as you’ve made no changes, it’s perfectly fine to leave without saving. If you’ve saved your changes in this session and have made no further changes to the project after saving, Excel will not ask you to save your changes when you close your session.
Installation and usage
SquareUsUp requires a supported version of Microsoft Excel running under a supported version of Microsoft Windows. You are welcome to try using it in other configurations, but you may encounter incompatibilities that make SquareUsUp spreadsheets difficult or impossible to use.
A SquareUsUp file is just an Excel file that’s been specially formatted and has lots of (mostly invisible) formulas and macros. The easiest way to create a file for a new SquareUsUp project is to start with the SquareUsUp template file and customize it for the project. You can get the template file from the SquareUsUp Download page, where the process to download the file to your computer is described in full.
While there are a number of steps involved in downloading the template file, each step is quite simple, and the whole process should only take a minute or two. The reason for all the steps is that both Windows and Excel keep an eagle eye on downloads that could house malicious code – like Excel files with macros. By following the documented steps, Windows and Excel are assured the template file is safe for use from this point forward.
After you’ve created a new SquareUsUp project file, you may want to open it via a desktop icon. To do this, right-click the SquareUsUp file in File Manager and select the Copy option. Then right-click on your Windows desktop and select Paste Shortcut to create an icon that is linked to the project file. You can then just double-click the icon to open the project in Excel.
About SquareUsUp
SquareUsUp was created by members of a tennis group (a self-admitted bunch of computer geeks) to relieve the tedium of paying court fees each week or trying to remember how much they owed when they didn’t keep up with their payments. Their solution worked so well and seemed to be so much better than other published solutions, they packaged it up for any other groups that have to track shared expenses within the group.
This is freeware. You are welcome to use it without obligation, and you are encouraged to spread the word if you find SquareUsUp is useful to you. If you have a few moments to share your comments and insights either at the SquareUsUp website or on the YouTube video that introduces people to SquareUsUp, it would be greatly appreciated!
Advanced topics
Click here to explore some of the more advanced or esoteric aspects of SquareUsUp.
Download SquareUsUp
Click here to download the SquareUsUp template file, from which you can create your own SquareUsUp projects.