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. (Some of these answers may require you to have some basic, not advanced, knowledge of Excel.)

Changing sheet 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 a group of transaction cells to a specific location on your current sheet. This will work fine, but there are cautions.

First, you should paste only cell values to avoid affecting sheet formatting and formulas. Second, you should never cut-and-paste cell contents since this can cause reference errors. Instead, use copy-and-paste. You may subsequently erase the copied cell contents if desired.

If pasted cells extend beyond the last visible row of transactions, 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 are shown on the sheet, starting balances or balance rows may no longer appear to sum to zero because some users are hidden. You can correct the problem by adding 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 because it is using transactions you can’t see. The best way to fix this problem is to use the Edit Entries option to autocorrect formatting of entered transactions, which will clear any such invisible transactions from the sheet.

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.

Correcting formatting problems

Payments section cells are formatted to display entered amounts as currency, and permissions are set to prevent you from modifying that formatting. However, Excel has the unfortunate “feature” of altering currency formatting to conform to certain user inputs, despite those permission settings. This can result in Payments section cells failing to properly show entered values as dollars and cents. Selecting the Edit Entries option to autocorrect formatting of entered transactions will correct any such problems that may arise.

Another kind of formatting problem may be caused by the use of Excel’s Cut option, which may remove border lines that you are normally prevented from altering. This is another reason to never use cut-and-paste on a SquareUsUp spreadsheet. To correct problems resulting from the use of Cut, use Excel’s Undo feature – or just don’t save your changes.

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 starting 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 starting balances are linked to the ending balances of a sheet that no longer immediately precedes it.

Be aware that rearranging sheets and subsequently linking starting balances to the previous sheet may potentially create “circular reference” problems, where two sheets’ starting balances end up referring to each other, which is an illegal condition. Excel will display arrows to indicate the error. Again, starting 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.

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. Hiding rows and columns is controlled by SquareUsUp software to assure proper operation of the spreadsheet. You should never use Excel’s Hide and Unhide operations to manually hide and unhide columns and rows in the spreadsheet.

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 Change Users button does this for you, so there is never any need to manually hide and unhide columns. Since the users shown in the Payments and Balances sections must remain in sync, manually hiding and unhiding these columns can cause the SquareUsUp spreadsheet to behave improperly.

SquareUsUp also hides transaction rows to allow the Summary section to appear directly underneath the filled-in transactions, and to greatly ease your ability to use the sheet. 900 transaction rows are available per sheet, but by default, unused transaction rows are hidden. 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 unhides the specified number of rows (and may move existing cell contents down), while the Delete Rows button hides the specified number of rows (and may erase cell contents and/or move existing cell contents up). Any attempt to manually hide and unhide transaction rows to simulate these operations may result in improper operation of the SquareUsUp sheet.

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 starting 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 Starting Balances section and looking at the actual stored value of the cell in the Excel formula bar. If a starting 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. Starting 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 starting balances contain undisplayed fractions of cents, subsequent starting 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 starting 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.

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

Charging group members when no payment has been made

What if a group member wants to charge one or more group members for something, even though that charge doesn’t correspond to an actual payment having been made? For example, if a group member bought lunch for one or more others using a gift card, he or she may want to be compensated for that lunch despite having paid nothing for it. Or a group member may have performed a service in exchange for payment by other group members.

This kind of transaction is treated as if the person asking for money from others did actually make a payment. In this case, the payment represents the value of what the payer gave to the group rather than the amount of money that left the payer’s wallet.

So, if people got free lunches off of someone else’s gift card, the person with the gift card is recorded as having paid for the value of those lunches, while the recipients of the lunches are charged their appropriate shares of that amount. If a group member does something on behalf of the group and wants to be paid for that service, that person is recorded as “paying” the value of the service, and other group members each pay their share of that amount.

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.

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.

Owing individual people vs. owing the group

Some expense-sharing apps track 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. This approach needlessly complicates what should be a simple statement of each member’s balance within the group, and it significantly and unnecessarily increases the number of monetary transactions required to square everyone up.

SquareUsUp does not track who owes what to whom. When squaring up debts within a group, the goal is simply to get all group members back to even. No one should care where their debt payments go to or come from in their quest to owe nothing and be owed nothing.

People often have trouble accepting this concept. They may insist, for example, that they directly pay back the group member who covered an expense for them. But other group transactions may render this debt repayment unnecessary. SquareUsUp handles all these details for the group, so no one has to keep track of their personal debts and everyone can be assured that the debt repayment process is kept as simple as possible.

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. Group expense-sharing spreadsheets tend to be written primarily to demonstrate a technique for implementing a solution rather than to create a polished app with a clean user interface and a comprehensive set of features.

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 spreadsheet 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, starting 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 Microsoft Excel.