ost things that we construct follow some sort of modus operandi. Take an email for example, where its main purpose is usually encapsulated within the Subject line, then there’s usually a greeting to the recipient within the first line of the body, then the full reason for the email is explained further within the body after the greeting, and finally the email is concluded with some sort of departing statement. This general flow concurrently helps the recipient stay centered on the purpose of the message in an easy-to-follow manner, while keeping the sender organized and clear in their intended messaging.
Given how critical spreadsheet data is to most companies’ financial decisions, one would think that a baseline modus operandi would also exist for spreadsheets. Unfortunately, it doesn’t exist and too many companies suffer through preventable, business-critical mistakes as a result.
In the absence of a generally accepted spreadsheet standard, I’ve learned plenty from others’ mistakes and triumphs and developed some methods over the years that, while not perfect, are designed to both eliminate common mistakes and make spreadsheets more aesthetically palpable. The fundamentals are being applied to Google Sheets, however, they can just as easily be applied to Microsoft Excel (which is similar, but not the same as Google Sheets). I typically focus on the following when starting a new spreadsheet:
- Set up default Sheet formatting transferable to each tab.
- Create a spreadsheet design pattern across all data-containing tabs.
- Set up distinct formatting that features the most pertinent information.
Start with Sheet Design
Too often, I’ve seen spreadsheets sent internally or externally with a bunch of irrelevant noise. Unnecessary numbers, colors, formatting, gridlines, etc. within a spreadsheet are major causes for distraction, and mere seconds spent unnecessarily fishing for information within a noisy spreadsheet could cost a trader their next big trade, a sales rep their next big client call, or an accountant their next big…whatever they do that’s a big deal 😝. Spreadsheets should be designed with the end-user in mind, similar to how websites are generally designed, because ultimately we want the end-user to obtain as much information as possible while doing as little work as possible to obtain it. Establishing crisp and clean design patterns will not only aid in keeping the end-user focused on the most pertinent information, but it will also help the preparer build trust and stand out amongst their peers irregardless of their role.
When starting a brand new spreadsheet in Google Sheets, the first thing I typically do is remove the default gridlines so that the spreadsheet truly appears as a blank canvas.
Next, I add enough rows to the sheet to make the total equal 10,000 rows. Google Sheets starts with 1,000 rows by default, so that means that I add 9,000 additional rows within a brand new sheet (this step becomes important much later on).
I then click the blank square immediately to the left of the A column (and right above the 1st row) in order to select all rows and columns within the sheet. While all of the sheet’s rows and columns are selected, I change the sheet’s default font from Arial to Franklin Gothic by searching for it within the “More fonts” menu. Changing the font isn’t the most important thing in the world, I just prefer the look of Franklin Gothic over the default option.
Finally, while all of the cells within the sheet are still highlighted, I apply a particular number format that is commonly believed to only be available within Microsoft Excel using the “Comma Style” format. However, there’s a workaround that can be used within Google Sheets in order to get the same presentation results as Excel’s “Comma Style” formatting. It is as follows:
- Open up Microsoft Excel. Leave the active cell at Cell A1.
- Look for the “Comma Style” formatting within the “Home” ribbon and click it to apply the formatting to Cell A1.
- Go to your computer’s menu bar, click “Format”, then select “Cells”.
- Within “Cells”, you’ll be presented with 6 tabs full of options. Make sure you’re on the “Number” tab and select “Custom”. The “Custom” selection will show the number format code being used for the “Comma Style” formatting. Highlight and copy the number format code found within the “Type:” dialog box.
- Go back into your Google Sheets spreadsheet with all the rows and columns selected, click “Format” within the spreadsheet’s menu bar, go to “Number”, go to “More Formats” at the bottom, and finally select “Custom number format”.
- Within the “Custom number formats” dialog box, paste in the number format code that was copied from Microsoft Excel and click “Apply”.
- Now, instead of seeing “0” when there’s a 0-value, or “1000” when there’s a number in the thousands, you’ll see “-” in place of “0” and “1,000.00” in place of “1000”.
What if you would like to apply this initial setup across other sheets but don’t want to go through these repetitive steps over and over again? That problem is easily solved by recording a macro for the aforementioned steps in a new sheet. To record a macro, do the following in a new sheet within your Google Sheets document:
- Add a new sheet within the document by clicking the “+” sign in the bottom left corner next to the “Sheet1” tab.
- Within the spreadsheet’s menu bar, go to “Tools”, then go to “Macros”, then click “Record macro”.
- A dialog box will appear at the bottom of the active sheet indicating that a new macro is recording. Leave the default selection as “Use absolute references”.
- Repeat all of the initial steps in order (there should be 4 steps recorded within the macro), being careful not to do anything outside of those steps since every action is now being recorded.
- Once you’re done with the final step, you’ll click “Save” within the macro dialog box at the bottom of the page and you’ll be prompted to set up an optional shortcut to quickly access the macro. Regardless of whether or not you decide to do a shortcut, name the macro and accept it (assuming all of the steps were done correctly in order).
- Now the macro that you named is available for use either through the optional shortcut, or through the menu bar by going to “Tools”, then to “Macros”, then by clicking on the naming convention you chose for the newly created macro.
- What this now allows for, once all of the subsequent permissions prompts are accepted after clicking your newly created macro for the first time, is the ability to open up a new sheet (presumably Sheet 3 in this case) and apply all of the initial custom styling within one click or keyboard stroke starting from Cell A1. This is much more efficient than the multiple clicks that it took the first time around and it can be repeated across sheets within that particular spreadsheet file as many times as needed.
Once the sheet design basics are completed, I then move on to constructing the spreadsheet’s overall design pattern and how the data within the tabs will be presented to the end-user in an orderly fashion.
To be continued…
