SquareUsUp

User Guide


Despite this user guide’s rather daunting length, SquareUsUp is really very easy to understand and use. Once you’ve watched the videos on the Home page or 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 and the Advanced Topics page 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.

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 entered into the Payments section, and the automatically calculated running balances for each group member. Negative balances (displayed in red and in parentheses) indicate the person owes money, while positive balances (displayed in black) indicate the person is owed money.

The unshaded row directly under the names in the Balances section contains­­­ each group member’s starting balance. Here, everyone is starting out with a balance of zero, but if there are multiple Excel sheets, starting balances may be set to the previous sheet’s ending balances.

A transaction in the Payments section will contain 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 the expense but did not pay anything. When the expense is not shared equally, each 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.

Balance section rows are displayed with a gray background to indicate that these cells are computed automatically and cannot be typed into. Each row is computed by adding or subtracting from each person’s previous balance based on that row’s Payments section entry. The balance shows 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. Summary section contents are computed automatically and so are also displayed with a gray background. Group members’ sheet balances are calculated as their total charges accumulated on the sheet subtracted from their net payments made on the sheet (which are computed as the total amount paid minus total amount received). Ending balances are calculated as group members’ starting balances plus their sheet balances. Ending balances will always match the numbers in the sheet’s last Balances section row.

A bar chart beside the Summary section and below the Balances section provides an at-a-glance picture of each group member’s ending balance on the sheet. A blue bar going up represents how much money the group member is owed, a red bar going down represents how much money the group member owes.

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 row that’s empty or has an error – or to open up a new row below the last displayed row if no rows are available for entering 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.

As with the Grid Entry button, the new transaction will be written to the first row that’s empty or has an error – or to a new row below the last displayed row if no rows are available for a new transaction. The form tells you what row will be written to. The form title will be New Entry for a new transaction, or Edit Entry if an error is being corrected.

When OK is clicked, form entries will be checked for validity before creating a new transaction (or replacing a transaction with an error). After the transaction is written to the spreadsheet, the cursor is placed on that transaction.

In the example shown here, the last transaction of the tennis group 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. The entry form input fields will change to match the type of transaction specified by the option buttons.

Because a person cannot both make a payment and share or receive that payment, the Share or Recipient fields are disabled on the entry form for anyone making a payment on a shared or internal payment.

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 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 group members pay another group member. The amounts paid are entered under those who made the payments, and an R is entered under the recipient of the payment (turning that cell green). Internal payments are made to pay off debts within the group – or perhaps when money is being loaned.

3.    Unshared payment – One or more group members 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. Unshared payments of $0 are allowed to note a member’s participation in an event despite spending no money.

4.    SquareUsUp payment – All debts in the group are paid off to get all balances back to zero. As an alternative to entering a series of internal payments to record this, a SquareUsUp! payment is a single transaction with only a date and transaction name that resets all balances to zero, adjusting summary totals accordingly. While this reduces sheet complexity, this transaction cannot be relocated on the sheet since the amounts it computes to get balances to zero depend on the balances at the transaction’s location.

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 starting balances or Payments section cell contents will also turn red.

When the starting balances row is red, there’s either an invalid balance or the given balances don’t sum to zero. Adjusting starting 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 for that row and for all subsequent rows. An alternative to typing changes 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 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 spreadsheet rows. All three buttons prompt for Excel spreadsheet row numbers 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. Options are also available to insert rows at the end of the sheet or move rows to the end of the sheet.

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. To facilitate the grouping of similar transactions, you may want to prefix all transaction names with a letter or word that represents a category you want to sort them by. When multiple transactions have equal dates or transaction names, they maintain their relative list positions from prior to the sort.

When reordering rows, you cannot include a SquareUsUp! payment row that resets everyone’s balances to zero since the position of this type of row in the transaction list is important. Listing the individual internal payments that were made to square everyone up instead of using a SquareUsUp! payment transaction avoids this limitation.

Notice that SquareUsUp’s options to insert and delete rows are not the same as Excel’s options to do the same. Excel’s row insertion and deletion operations would damage SquareUsUp’s spreadsheet structure, which is why SquareUsUp spreadsheets prevent you from using these options. Through the Edit History buttons, SquareUsUp strategically hides, unhides, and moves spreadsheet rows to insert and delete rows without damaging the spreadsheet.


Editing customized entries

The Edit Entries 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 multiple times in the Summary section) can be set to describe the contents of the sheet (e.g., the time period the sheet covers or the category of transactions on the sheet).

While you may type directly into the title fields at the top of the spreadsheet, an alternative is to click the Edit Entries button, 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 box to autocorrect formatting of entered transactions before clicking OK will change transaction entries 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 extra or missing spaces). This option can improve the sheet’s readability and appearance. It can also correct errors caused by spaces inadvertently inserted within numbers, by inputs that may cause cells to display currency incorrectly, and by invalid entries made below the sheet’s last visible transaction row that can create erroneous summary results.


Creating a new sheet

There are a number of reasons why you might want to use multiple sheets for your project. Most commonly, a long-term project may be divided into multiple sheets (each sheet accessible via its Excel tab) to keep a single sheet from getting too long and unwieldy. For example, a new sheet may be created for each new year of a project, with each new sheet’s starting balances linked to the previous sheet’s ending balances.

Alternatively, a travel group may want to store all their trips in a single Excel file, using a new sheet for each trip. Or the travel group might want to use separate sheets to record different trip transaction categories (e.g., transportation, lodging, food, and activities) so that the sheets’ Summary sections provide a separate financial summary for each category.

Removing a group member may require the creation of a new sheet. After bringing the person to a balance of zero, creating a new sheet with starting balances linked to the previous sheet’s ending balances will leave him or her with no activity and a zero balance. This allows the group member to be removed from the new sheet, which is used to continue recording transactions.

The New Sheet button lets you create a new sheet. You will be prompted for the name to give the new sheet, which will be inserted just before (to the left of) the current sheet as an exact copy of that sheet, but with no transactions in it. Starting balances may either be linked to the current sheet’s ending balances, set to those ending balances without being linked, or all set to zero. The Adjust Balances button may be clicked at any time to modify these starting 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 starting 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. If you modify the user list on a sheet whose ending balances are being used to set another sheet’s starting balances, you may cause the other sheet’s starting balances to be changed improperly. If links on one sheet are adversely affected when you edit the user list on another sheet, you can use the Adjust Balances button (described next in Adjusting Starting Balances) to correct the problem on the affected sheet.

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 starting balances

Users’ starting balances may occasionally need to be adjusted. You may type changes directly into the spreadsheet (at the top of the sheet, on the unshaded row above the Balances rows) or you may click Adjust Balances to enter new starting balances via a form.

A starting 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 starting balance on this sheet.

A starting balance that is a link displays in the Excel formula bar above the spreadsheet 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’!C914 links a starting balance to the ending balance contained in the cell C914 on the sheet named 2023.

 

Use of the Adjust Starting Balances form allows you to view and enter links in a more natural way. You may type unlinked balances directly in the Starting Balance column; they will display with a white background. Alternatively, you may type a Sheet Link and Cell Link to give a sheet name and cell address to which this balance should be linked. The resulting starting balance displays with a blue background to show it’s linked.

Three option buttons are available. Link To Previous Sheet links all starting balances to the ending balances on the previous SquareUsUp sheet, assigning balances by list position (first user on this sheet linked to the first user on the previous sheet, and so on), with any user on this sheet beyond the number of users on the previous sheet being 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.) The other buttons set all balances to zero or reset starting balances to their current values.

Starting balances must always sum to zero. Note that negative balances must be typed using a minus sign, not parentheses, and that you cannot include commas in dollar amounts.


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 printing the report.

For example, you may choose to view or print a report in a landscape (horizontal) format rather than portrait (vertical). You may also set scaling and page margins as desired. All of these options may change report pagination to suit your needs. If, for example, you want to fit a report on a single page or force a page division at a desired place in the report, increasing the left and right margins may reduce the print size and thus accomplish these goals.

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 provided as an alternative to Excel’s Save and Save As menu options for convenience and to minimize the requirement for Excel expertise.

Clicking the Save button with Save Changes To Your Project File selected will save the workbook’s changes to the currently opened file. Using the other option to type a file name before clicking Save will save the workbook to the specified file name. All files will be saved into the current folder. You may move or copy the saved file to any folder after the save operation is complete. (To save directly to a different folder, you can use Excel’s Save As option.)

When saving a new project from the Create A Project window, you are required to give the file a new name before clicking Save to avoid overwriting the SquareUsUp 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. You can create a file for a new SquareUsUp project by starting with the SquareUsUp template file and customizing it for your project. You can get the template file from the Downloads page of the SquareUsUp website (https://www.squareusup.com), 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 developed by a software engineer who plays tennis in a group that was unsatisfied with every expense-sharing solution they found. The solutions from the big players in this field were gross overkill, while the homegrown spreadsheets out there just didn’t have all required functionality. The spreadsheet created for the group worked so well, they decided not to keep it to themselves.

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. The best way to do this is to give us a quick and anonymous rating on the Reviews page of the SquareUsUp website. It would be greatly appreciated!


Advanced topics

The Advanced Topics page explores some of the more advanced or esoteric aspects of SquareUsUp.