SquareUsUp

Advanced Topics

In the interest of obsessive completeness, we’ll take a deep dive here into some areas of using a SquareUsUp project about which you may be confused or just curious. Hopefully, the information you’re looking for is here. (Some of these answers may require you to have some basic, not advanced, knowledge of Excel.)

Changing formatting

SquareUsUp spreadsheets are formatted in a way that should make them immediately usable, without modification, for most purposes. These sheets are largely protected from changes that could damage their integrity, but you are allowed to adjust column widths and row heights to better conform to your inputs. You may, for instance, want to expand the width of the Transaction column to accommodate longer descriptions, or the widths of the Payments or Balances columns to accommodate very large amounts of money.

The easiest way to alter columns widths and row heights in Excel is to place the cursor on the line to the right of the column letter or below the row number, then drag the line to adjust the width of the column or height of the row. To do this for a range of columns or rows, highlight a group of columns or rows (by dragging through a sequence of column letters or row numbers or by clicking on a column letter or row number, then pressing the Shift key while clicking on another column letter or row number to highlight a range of columns or rows), then dragging the line of any of the highlighted columns or rows to change them all equally.

In the Transaction column, long text automatically wraps to a new line, so rather than widening the entire column, you may instead choose to just expand the row height for any particularly long transaction entries. When adjusting Payments and Balances columns, it is recommended for appearance’s sake that any width expansion be applied to all columns equally.

You are allowed to adjust formatting (e.g., margins) on all reports via the Page Setup button.

Copying and pasting cell contents

You may want to copy Date, Transaction, and Payments cells from your current sheet or some other sheet in order to paste those cells into a specific location on the current sheet. This will work fine, but there are cautions.

If cells are pasted that extend beyond the last visible row of transaction entries, or if cells are pasted for more users than have been set up for the sheet, the paste operation succeeds but the results can be baffling. That’s because the sheet then bases its calculations on cell contents you cannot see.

If cells are pasted for more users than have been set up for the sheet, initial balances and/or balance rows may no longer appear to sum to zero because one or more users are there but hidden. To correct the problem, just add users to the current sheet to reveal the filled-in-but-hidden columns.

If cells are pasted into the cells below the last visible transaction rows, Summary section balances will not match the last Balances section row. This is a trickier problem to correct because the process of inserting rows clears out any contents of those rows. So, after inserting the required number of rows, you’ll need to repeat the copy-and-paste operation.

The moral of the story is to make sure you insert enough empty rows or add enough users to the current sheet before you paste transactions into the sheet.

Managing sheets

While you can create new sheets via SquareUsUp’s New Sheet button, other operations like rearranging and deleting sheets are not similarly provided via buttons. That’s because (a) those operations should be required infrequently and (b) it’s easier to do these things with Excel’s built-in functionality than by anything SquareUsUp could devise.

To delete a sheet, just right-click on the sheet’s tab, then select Delete. Keep in mind that if any sheet has links to this sheet’s ending balances, that will cause errors in that sheet that will require you to reset initial balances manually or via the Adjust Balances button.

To rearrange the order of the sheets, just drag a sheet’s tab to whatever new position you want it to have among the other tabs. This will not affect any sheet linked to the sheet you move, but it may be confusing when a sheet’s initial balances are linked to the ending balances of a sheet that no longer immediately precedes it.

Be aware that rearranging sheets and subsequently linking initial balances to the previous sheet may potentially create “circular reference” problems, where two sheet’s initial balances end up referring to each other, which is an illegal condition. Excel will display arrows to indicate the error. Again, initial balances will have to be reset manually or via the Adjust Balances button.

While sheets may be renamed via the Change Titles button, you may also change a sheet name by double-clicking the name on the sheet’s tab to type a new name. Using the Change Titles button will change both the sheet name and the spreadsheet titles, while changing the name on the sheet’s tab will not change spreadsheet titles to match (which is perfectly fine). Whichever method you use to change a sheet name, any links to the renamed sheet will automatically be changed to refer to the sheet’s new name.

Adding your own sheets

If you maintain non-SquareUsUp Excel spreadsheets for your project, you can add those sheets to your SquareUsUp file in order to consolidate all your project’s spreadsheets into a single file. To copy an existing spreadsheet into a SquareUsUp file, right-click the tab of the sheet to copy, then select Excel’s Move or Copy option and specify your open SquareUsUp file. (A SquareUsUp sheet shouldn’t be copied to a non-SquareUsUp file since a SquareUsUp sheet needs macros and hidden sheets in the SquareUsUp file.)

For example, if you have a travel group, you may be keeping spreadsheets to track the trip’s itinerary or to list food and supplies group members are responsible for bringing. Rather than storing each spreadsheet in a separate file, you can include these sheets in your SquareUsUp project file to keep all trip-related spreadsheets together in one place.

Correcting Payments section formatting errors

SquareUsUp cells are formatted to display values and cell formats properly, and permissions are set to prevent you from modifying that formatting. However, Excel has the unfortunate “feature” of altering certain display formats to conform to user inputs or commands, even when they are protected from modification.

The most common problem that results from this buggy behavior is that Payments section cells may forget they’re supposed to show values as dollars and cents. If dollar amounts ever display improperly, just retype the amount as it’s supposed to look, i.e., with the dollar sign, the dollar amount, the period, and the two digits containing the cents to reset the formatting of the cell. Also, using Excel’s Cut option may remove border lines, which you are normally prevented from altering. To correct this, use Excel’s Undo feature – or just don’t save your changes.

Limitation on transactions per sheet

The design of a SquareUsUp sheet requires a limitation to be made on the number of transactions that can be entered on a single sheet. The limit that was chosen is 900 transactions. The first transaction on each sheet is on row 8 (leaving room for buttons and headings above the first transaction), and transactions cannot be entered beyond row 907 (with the Summary info section shown beneath that row).

The difficulty in navigating hundreds of rows of transactions makes it preferable to split long lists of transactions onto multiple separate sheets, as discussed previously. This means you should rarely if ever find the 900-row limitation to present a problem.

Note that using the Move Rows option to reorder rows in the list requires the creation of temporary transaction rows. The consequence is that you cannot move a group of rows if the number of rows being moved plus the number of currently displayed rows exceeds 900.

Limitation on users per sheet

SquareUsUp sheets, user interface forms, and reports all require that a limitation be made on the number of users whose expenses can be tracked. The limit that was chosen is 10 users.

The more users that are allowed, the more design challenges that have to be overcome. 10 users was chosen as a reasonable limit that should serve the needs of the vast majority of SquareUsUp customers while keeping screens and reports to relatively compact and usable sizes. Depending on customer feedback, allowing SquareUsUp to accommodate more than 10 users may be a priority for a future version of the system.

Hidden areas

SquareUsUp spreadsheets use hidden rows and columns to make the sheets easier to read and to use. For example, when you add or delete users, columns in the Payments and Balances sections are hidden or unhidden so unused columns are not shown. The Set Users button does this for you, so you should never need to manually hide and unhide columns.

SquareUsUp also hides transaction rows. 900 transaction rows are available per sheet, but by default, unused transaction rows are hidden, allowing the Summary section to appear directly underneath the last filled-in transaction row. The Grid Entry and Form Entry buttons create (unhide) a single empty row for entry of the next transaction if none is currently displayed. The Insert Rows button works by unhiding the specified number of rows (and possibly moving existing cell contents down), while the Delete Rows button works by hiding the specified number of rows (and possibly erasing cell contents and/or moving existing cell contents up).

Columns to the right of the grid are hidden to prevent you from trying to use the area of the sheet that houses its inner workings. While you are not prevented from unhiding these columns, the inner workings remain invisible and protected from modification, so there is really no reason to clutter the screen by unhiding this area.

Use without a mouse

Even if a keyboard is your only interface to your device, SquareUsUp spreadsheets maintain their full functionality. Buttons on all forms may be “clicked” by tabbing to the button and then tapping the Enter key. Checkboxes and radio buttons may be turned off and on via the space bar when they are highlighted. And the buttons at the top of each sheet may be “clicked” by holding down the Shift and Ctrl keys and then tapping the first letter of the button’s text.

Roundoffs

Unlike many expense-sharing templates and apps, SquareUsUp’s displayed balances will always total to exactly zero, even when normal methods of rounding values off to the nearest penny may cause discrepancies of a few pennies in balance totals. Importantly, SquareUsUp maintains full internal accuracy for all balances to prevent roundoff discrepancies from accumulating over time. This means that calculated balances will always represent the fairest possible way to distribute funds when debts are being squared up.

Payment amounts and initial balances may be entered with more than two decimal places, i.e., in fractions of a penny, both when typing directly into the spreadsheet and when using the Form Entry and Adjust Balances forms. Even though monetary amounts are always displayed with just two decimal places on SquareUsUp spreadsheets, forms, and reports, they are stored with full precision. This can be observed by clicking on a cell in the Payments section or in the Initial Balances section and looking at the actual stored value of the cell in the Excel formula bar. If an initial balance is a formula, you can copy the cell and use Excel’s Paste Values option to place the value into another cell to see the full value being used.

Only when creating a new sheet does SquareUsUp use a rounded value. Initial balances linked to or copied from another sheet are rounded to the two displayed decimal places shown on the other sheet. This is done to simplify SquareUsUp use. When initial balances contain undisplayed fractions of cents, subsequent initial balance edits can easily result in those balances being changed unintentionally, which may in turn cause transaction rows to fail to total to zero. Using rounded initial balances avoids these kinds of problems.

Squaring up strategies

When SquareUsUp is used for a defined timespan or for a specific event, e.g., to record shared expenses on a trip or for one season of a sports league, the group will probably want to square up everyone’s debts at the end of event. The SquareUsUp Report can suggest ways to do this, and a SquareUsUp payment transaction is the easiest way to record that this has been done.

When SquareUsUp is used on an ongoing basis without a defined end date, e.g., for roommates or a group that meets weekly, there may be little motivation to ever get everyone squared up since balances will not remain at zero for long anyway. Here, the goal will usually be simply to make sure no one strays too far from a balance of zero. This is accomplished by group members (presumably those who owe money) making occasional internal payments (presumably to those who are owed money). Keeping everyone’s balances reasonably close to zero, without worrying about getting everyone to exactly zero, is often the most practical way to use SquareUsUp for long-term activities.

Multiple people paying a shared expense

SquareUsUp requires a shared payment to have only one payer. This restriction simplifies system use and spreadsheet clarity, and it imposes no significant limitations because a multiple-payer scenario is still easily handled.

When more than one person contributes to the payment of an expense, it should be thought of as one or more people giving money to the designated payer, who then pays the full expense. This would then be recorded as an internal payment in which each co-payer pays the designated payer, plus a shared payment in which the designated payer pays the full expense, with everyone sharing the expense being identified in the normal way, i.e., as if no separate contributions have been made.

This way of recording a multiple-payer shared expense shows in a clear way what each person contributed and what each of their shares of the expense was. It also has the advantage of working fine even if the people contributing to the payment did not contribute exactly what they actually owed (which would be the typical case).

Dealing with non-group members

There may occasionally be a transaction that involves one or more people who are not defined group members on the SquareUsUp sheet. One way to deal with this situation is simply to add each new person as a project group member.

If a new person involved in a shared payment is not expected to participate much in future transactions and therefore doesn’t rate becoming a permanent group member, the group can simply square up with that person outside the visibility of the SquareUsUp project, then handle any remaining parts of the transaction on the SquareUsUp sheet as usual. For example, if a person is a one-time substitute in a tennis group, that person can simply pay his or her share of the expense to the person who paid the tennis fees, then the SquareUsUp transaction would record only the remaining part of the full payment.

100% share vs. internal payment

When one group member buys something to give to another group member, there are two different ways this transaction can be recorded. The transaction could be treated as a shared payment, where the recipient of the purchased item assumes a 100% share of the buyer’s payment. Alternatively, this could be treated as an internal payment, where the recipient of the purchased item is recorded as the recipient of the buyer’s payment – the payment in this case being the purchased item rather than money.

Either approach will result in the exact same user balances. The difference will only be seen in the recipient’s summary totals. The shared payment will show the recipient’s balance decreasing because total charges increase by the cost of the item; the internal payment will show the recipient’s balance decreasing because the received total increases by the cost of the item.

Which method you use to record this transaction really depends on whether the purchased item is considered to be an expense that relates to the whole group. In other words, if the recipient of the item would have recorded the purchase in SquareUsUp if he/she had made the purchase him/herself, the transaction should probably be recorded as a shared payment; if not, using an internal payment makes more sense.

Owing individual people vs. owing the group

Some expense-sharing apps track group debts on a person-by-person basis, where members’ financial statuses are defined by a matrix of what each person owes or is owed by each other person in the group.

Frankly, it’s unclear what advantage is gained by tracking debts in this way. It needlessly complicates what should be a simple statement of each person’s balance within the group, and it could grossly increase the number of monetary transactions required to square everyone up – for no good reason. Does it really make sense for a person to demand payment from someone who owes money to them, even when the person being asked to pay is owed money by the group? It would seem to make more sense for the person to demand payment from someone who owes money to the group.

SquareUsUp does not track who owes what to whom, thus eliminating a complexity that is unnecessary to deal with in just about any real-life situation. When squaring up debts within the group, people just want to get back to even. Where their money goes to or comes from to achieve this goal really shouldn’t matter to anyone.

Using a pot

Some groups maintain a pot of money which group members contribute to and from which group expenses are paid. SquareUsUp can easily handle this situation.

From SquareUsUp’s viewpoint, a pot is simply another group member, so “Pot” should be added as a user. Whenever anyone contributes money to the pot, that is recorded as a payment in which the person paying into the pot is the payer and the pot is the recipient. When an expense is paid from the pot, that is recorded as a shared payment in which the pot is the payer and group members responsible for the payment share 100% of that payment (since the pot will obviously never assume any share of the responsibility for any payment) in whatever proportions you want.

The pot will always have a negative or zero balance that indicates how much money it contains. (The pot can never spend money it doesn’t have, so it will never have a positive balance.) When squaring everyone up, the pot’s funds will be figured in with everyone else’s balances to get everyone (including the pot) squared up.

Sharing the spreadsheet

Since SquareUsUp spreadsheets track the shared expenses of a group of people, the whole group will likely want to have access to that information. There are several ways to accomplish that goal.

If the spreadsheet is being stored on the personal device of the person who is maintaining it, that person can periodically produce SquareUsUp reports saved as .PDF files and send those to the group.

If the SquareUsUp file is being stored on a network or in a cloud location accessible to group members, it can be maintained and/or viewed by group members at any time.

If Excel is used in co-authoring mode, the SquareUsUp file can be accessed and even edited simultaneously by group members.

SquareUsUp vs. other Excel-based solutions

Scores of Excel-based expense-sharing spreadsheets have been published on the internet. Virtually all of these solutions are most properly categorized as templates, not apps. Templates provide a framework that can be used to solve the problem at hand or demonstrate an approach to solving the problem, but they are not polished applications. SquareUsUp was designed to be an application, not a template.

While SquareUsUp is not a tool for teaching Excel techniques, it is a comprehensive and fully documented expense-sharing solution, it provides user-friendly data entry and editing mechanisms, it is robust in its ability to detect and respond in a friendly way to invalid inputs, and it insulates and protects the user from the complexities of the Excel environment. We are unaware of any other Excel-based solution that has all of these attributes

SquareUsUp vs. other expense-sharing apps

Your group may require an app that’s designed for use on cell phone screens, does currency conversions, provides messaging services between group members, facilitates payments between group members, and maybe even scans receipts for transaction entry. If features like these are important to your group, you’ll probably want to use something other than SquareUsUp.

On the other hand, SquareUsUp offers a method for tracking shared group expenses that lacks the complexity all those bells and whistles bring with them. Not to mention that it’s totally free.

SquareUsUp use in nonnative environments

SquareUsUp was designed specifically as a Windows/Excel-based tool that will work on all supported versions of both. Although it would be great to assure it works on other operating systems or with other spreadsheet tools, this would have been a colossal effort outside the scope of the developer’s requirements.

When running a SquareUsUp spreadsheet on Microsoft Excel in a non-Windows environment (e.g., on an Apple computer), the spreadsheet should still work well, as should most of the buttons. But the buttons for printing reports and saving your work may not work as designed. For instance, Apple computers may print reports immediately without showing a print preview window that allows on-screen viewing or editing of report appearance.

When running a SquareUsUp spreadsheet on an app other than genuine Microsoft Excel, the spreadsheet software will probably do a reasonable job of replicating an Excel experience, with some likely differences in screen appearance and behavior. But because non-Microsoft spreadsheets may not fully support the VBA programming language, a SquareUsUp sheet’s buttons may not function properly – or at all.

Without the buttons, all input to the sheet (titles, user names, initial balances, and transactions) must be done directly on the sheet. You will not be able to run reports, but all other button functionality can be performed manually, albeit with considerably more effort than required by the buttons. Inserting, deleting, and moving rows is performed by hiding and unhiding rows, and by copying and pasting transaction cells. (You will probably want to unhide a block of empty transaction rows at the bottom of a sheet to minimize how often you have to do this.) Adding and deleting users is similarly done by hiding and unhiding columns. A new sheet can be added by copying an existing sheet, pasting it as a new sheet, and then editing the new sheet. Printing a sheet and saving your work can be done via native spreadsheet functions.

Bottom line: For an optimal SquareUsUp experience, use Microsoft Windows and Excel.