SquareUsUp

User Guide v3.0

While SquareUsUp was designed for simplicity, its features are nevertheless fully documented in this user guide. These materials can answer questions about SquareUsUp usage and clarify how to handle various expense-sharing situations. But once you grasp the basics of recording transactions, as described in detail in the example that begins the user guide, you should find SquareUsUp usage to be so intuitive that you will rarely need to refer to the guide at all.

A SquareUsUp example

Each row of a SquareUsUp spreadsheet records a single monetary transaction. A transaction happens whenever money changes hands – either between the group and an outside party or between members of the group.

Transactions are recorded using a simple notation that is best explained via an example. This sheet 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 running balance for each group member (automatically calculated in the Balances section). A negative balance (displayed in red and in parentheses) indicates the person owes money, while a positive balance (displayed in black) indicates the person is owed money.

A cell in the Payments section will contain a dollar amount for anyone who spent money in that transaction. S entries in the same row (always displayed with a blue background) indicate group members who shared responsibility for that 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. A negative payment amount can be used to indicate the receipt of a refund or credit, with S entries detailing the sharing of that money.

When a row has an R (always displayed with a green background) instead of an S, the dollar amount(s) in that row show money that was paid directly to the group member with the R.

Shown here is a row-by-row explanation of the Payments section entries in the example.

Balance section cells are computed automatically and cannot be typed into (and hence are shown with a gray background). 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 (a positive number) or how much money the person owes to the group (a negative number) as a result of that transaction. 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 for each group member, including each person’s net charges (total charged minus total credited), net payments (total paid minus total received), and sheet balance (net payments minus net charges). A bar chart beside the Summary section provides an at-a-glance picture of each group member’s ending balance on the sheet.

SquareUsUp includes utilities to customize your project, ease transaction entry and editing, 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 simple and straightforward, but they are also fully documented in this user guide.

Getting started

This is the SquareUsUp project template, from which you create a new project.

You may change your project customizations at any time via the Edit Sheet 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. The Grid Entry button can be used to move the cursor to the first empty or invalid row, or to open up a new row for transaction entry at the end of the transactions list.

Alternatively, the Form Entry button can be used to pop up a transaction entry form. The option buttons specify the type of transaction to be entered. Input is checked for validity before being written to the spreadsheet.

Here, the third transaction of the tennis group example is being created. Bill paid $46.34 for a case of tennis balls, with shares of that payment being specified in percentages. Note that for shared payments, the Charged column displays what the specified shares will cause each group member to be charged for that transaction. For credits, the amount paid would be a negative number, as would the charges.

Whether you enter new transactions directly into the grid or via the input form is a matter of personal preference.

This same transaction entry form may be used to edit an existing transaction, as discussed in Editing Transactions.

Transaction types

Each time there is a monetary transaction within the group – whether someone pays an outside vendor, receives a credit from a 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 – A 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). There are four kinds of shares:

a.    Equal shares – The S is entered by itself to indicate responsibility for paying the expense is to be shared equally among the payer and everyone with an S.

b.    Percent shares – The S is followed by a qualifier that is a percentage ending with the % symbol to indicate the percentage of the payment that person is responsible for. 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%.

c.     Fractional shares – The S is followed by a qualifier that is a fraction, i.e., two numbers separated by a slash (/) to indicate the fraction of the payment that person is responsible for. 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.

d.    Dollar shares – The S is followed by a qualifier that is a dollar amount starting with the $ symbol to indicate the amount of the payment that person is responsible for. 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.

A group credit or refund is indicated by entering a negative payment for the recipient of the credit. Sharing of the credit is done in the same way as the sharing of a payment.

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 aren’t 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’s 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. A negative payment amount can be used to record receipt of an unshared credit or refund.

4.    SquareUsUp! payment – When all debts in the group are paid off to get all balances back to zero, a SquareUsUp! payment may be entered as a single transaction that has only a date and transaction name to reset balances to zero and adjust summary totals accordingly. This reduces sheet complexity versus recording the full series of internal payments, but 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.

Whenever a new transaction is entered or an existing one is 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. Some of the things to look for when diagnosing a problem include:

• An entry that’s not a valid 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 two columns of the row, including the first – before clicking the Form Entry button. The highlighted row will populate the form (the same Form Entry form used for entering new transactions), where it may be modified as desired.

The Edit History buttons at the top of the spreadsheet allow you to insert, delete, or move spreadsheet rows. All three buttons prompt for the 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.

Insert Rows opens up the specified number of rows at the specified row number. Balances are not computed beyond the first empty transactions row. Delete Rows deletes the specified number of rows, starting with the specified row number.

Note that SquareUsUp’s options to insert and delete rows are not the same as Excel’s. 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 Insert Rows and Delete Rows buttons, SquareUsUp strategically hides, unhides, and moves spreadsheet rows to simulate row insertion and deletion without damaging the spreadsheet.

Unfortunately, SquareUsUp cannot prevent you from using Excel’s Cut operation, which will also damage the spreadsheet, so we can only ask you to refrain from using the Cut operation on any portion of a SquareUsUp spreadsheet. The Move Rows button will let you do any cut-and-paste operations you may want to perform via its options to move, copy, and sort transaction rows.

The first two options on the Move Rows form allow you to move the specified rows from their current position to another place on the sheet. The third option allows you to copy the specified rows to the end of the sheet while leaving those rows in their original position, thus duplicating those rows. The fourth and fifth options allow you to move or copy the specified rows to the end of a different sheet in the SquareUsUp file.

The last three options on the Move Rows form allow transactions rows to be sorted. Specified rows can be sorted by date or by transaction name. A third sort option provides the ability to group transactions by category. If you type a fixed-
length category name to start each transaction name, the last sort option can be used to sort specified transactions by category while maintaining the relative list positions of transactions that have the same dates or transaction names.

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.

Editing customized entries

The Edit Entries button allows you to change the three customizable titles on a SquareUsUp sheet, and also to clean up transaction text and adjust column widths if necessary.

While you may type directly into the title fields at the top of the spreadsheet, you may prefer using Edit Entries to make title changes. 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 name (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 sheet’s time period or transaction category).

Changing the sheet name on the Edit Entries form 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 that is different from the Excel sheet name. And, as discussed in Frequently Asked Questions, you may also change the Excel sheet name via Excel techniques, which will leave the sheet title unchanged.

Consistent formatting of entered transactions cannot be enforced when transactions are typed directly into the spreadsheet. Checking the box to clean up entered transactions can repair the sheet in several ways: it applies consistent capitalization and spacing to improve the sheet’s readability and even correct errors; it fixes fixing currency formatting that can get corrupted; and it removes transaction entries that may have been improperly pasted below the sheet’s last visible transaction row, which can produce perplexing Summary section balances that are partially based on invisible transactions.

If column widths on the spreadsheet are too narrow, entries may get truncated or numbers may get replaced by asterisks. Checking the box to adjust column widths will set the widths of both Payments and Balances section columns as necessary to fit all entries. While column widths may be adjusted manually using standard Excel techniques, the use of this option is recommended to assure all columns in each section are the same width (required for proper display of the bar chart) and the optimal width.

Creating a new sheet

There are a number of reasons 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 to keep all trip records together in one file while giving each trip its own financial summary. 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. A group member with any activity or a non-zero balance on a sheet can’t be removed since the sheet would not balance properly. After bringing the group member to a balance of zero, creating a new sheet with starting balances linked to the previous sheet’s ending balances will leave that person with no activity and a zero balance, allowing him or her to be removed from the new sheet. The new sheet can then be used to continue recording group transactions.

The New Sheet button lets you create a new sheet that is an exact copy of the current sheet, but without any transactions. You will be prompted for the name to give the new sheet, which will be inserted as a new tab just before (to the left of) the current sheet tab. Starting balances may either be linked to the current sheet’s ending balances (so that subsequent changes to the current sheet’s ending balances will be automatically be reflected in the new sheet’s starting 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), but it’s generally easier to 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 =’2024’!C916 links a starting balance to the ending balance contained in the cell C916 on the sheet named 2024.

Use of the Adjust Starting Balances form makes it easier to view and enter starting balances and links. You may type unlinked balances directly in the Starting Balance column; they will display with a white background. Or you may type the name of a linked sheet, then provide a user column letter (C-Q) to specify the ending balance on that sheet the starting balance should be linked to. When a starting balance is linked, it displays with a blue background.

Four 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.) Unlink All Balances preserves the displayed balances but unlinks them from the linked sheet. The last two buttons set all balances to zero or reset starting balances to their current values on the spreadsheet.

Starting balances must always sum to zero. Negative balances must be typed using a minus sign, not parentheses, and 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 Print Preview, you may click the Page Setup button to customize output appearance before printing the report. Customization options include choosing to view or print a report in a landscape (horizontal) format rather than portrait (vertical), changing font scaling for readability, and changing page margins to, say, force a page division at a desired place in the report.

The Print Preview screen’s 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 section 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 values in the current sheet’s Summary section. An example of this report, created for the Tennis Payment Record spreadsheet, is shown here.

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 here.

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 here.

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. SquareUsUp does its best to extract the purpose to look natural in the Transaction Report. You may want to experiment with different transaction text to see what works best on this report.

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 the first option selected will save the workbook’s changes to the currently opened file. Using the second option will save the workbook to the specified file name. While SquareUsUp will only let you save files to the current folder, you may use Windows to move or copy saved files to any folder you want after the save operation is complete.

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 unfortunate Excel behavior caused by some SquareUsUp coding techniques. 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 and were awkward to use. The spreadsheet created for the group worked so well, they decided not to keep it to themselves.

This is freeware. SquareUsUp was not written with a profit motive in mind, and you are welcome to use it without obligation. We encourage you to spread the word if you find SquareUsUp is useful to you, and we’d be extremely grateful if you would please give SquareUsUp a quick and anonymous star rating on the SquareUsUp website. Thank you!