ofCourse Consulting - Google Sheets

Google Sheets

Managing your money in Google Sheets

Introduction

“If You Don’t Control Your Money, It Will Control You” Budgets are for anyone seeking to stabilize their finances, avoid debt, and build savings.

If You Live Within Your Budget, You Will be Much More Likely to:

Avoid excessive debt.
Build emergency and long-term savings accounts.
Take care of financial priorities such as housing, food, and transportation, rather than disproportionate spending on entertainment and dining out.
Establish regular investing habits in preparation for retirement.
Feel in control of your finances.
Understand your personal spending habits and eliminate wasteful expenditures.
Be in a position to take advantage of financial opportunities, whether it’s for something on sale at the store or a chance to greatly improve your investment portfolio.
Prevent many of the arguments spouses have with regard to spending and finances.
Make more than monthly minimum payments to credit accounts, including credit cards, mortgages, and auto loans.

Getting Started in Google Sheets

Once you have successfully created a Google account, you can enter the Google Sheets application in different ways. First and foremost, you can use the 3x3 (9-dots) grid or "honeycomb" as shown in Figure 1. It may be challenging to locate initially, but once you have started using it, it will become second nature to you.

Figure 1 - Clickable Google Apps Grid

When you click on that 3x3 grid, you are presented with a lot (but not all of) the Google apps that are available to you and that you can run in your Chrome browser. Note that you can arrange these app icons any way that you would like and make the ones you use most frequently near the type. In this case, you will select the Google Calendar icon as shown in Figure 3.

You can also quickly bring up the Google Calendar application by typing in https://sheets.google.com or simply typing sheets.google.com in the browser's address bar. If you want to be really efficient, you can create a "bookmark" and place the Google Sheets application directly on your bookmark bar or place it within the bookmark hierarchical structure.

After selecting the Google Sheets icon, you will be running the Google Sheets application, and you can begin to manage your money by creating budgets, tracking and categorizing expenses, and controlling all of the tasks required to be financially secure.

Figure 4 - Google Sheets sample file

Figure 2 - Google Apps

Figure 3 - Google Sheets

Google Sheets Interface

Let's begin by examining the Google Sheets interface and it's main components. In Figure 5 you see what Google Sheets looks like when you open up a spreadsheet. The spreadsheet is comprised of the following elements, which are pointed out in red, from top to bottom within Google Sheets.

Spreadsheet Title - This is the name of your spreadsheet. Be sure to give it a descriptive name. Try to use keywords to describe its content for search.
Column Letters - The letters help you know where you are in the spreadsheet horizontally. Think of them as the x-axis for the grid. When referring to cells, you designate them by column letter and row number, such as A1 or B2.
Row Numbers - The numbers help you know where you are in the spreadsheet vertically. Think of them as the y-axis for the grid. When referring to cells, you designate them by column letter and row number, such as A1 or B2.
Sheets Tabs - A spreadsheet can consist of more than one sheet. Clicking on these tabs will change your view to another sheet. In this case, the sheets are named Sheet1 and Sheet2. Sometimes people describe Sheets as worksheets or tabs.
Active Cell - This is the cell that you are using. You can only use one cell at a time. The contents will show in the Formula Bar.

Figure 5 - Google Sheets

Menus and Toolbar

Now let's look at the Menu, Toolbar, and Context menu as shown in red in Figure 6 within Google Sheets.

Menu – The menu is where you will find options to work with your files, such as rename, share, and print, and the data inside it, such as formatting or inserting a pivot table.
Toolbar – The toolbar enables quick access to the most common settings, such as number formats and cell borders.
Context Menu – Right-clicking on a cell (control-click on a Mac) will bring up a context menu of actions for the active cell. In the image above, the context menu is for cell D1. You can tell this because Sheets outlined D1 with a blue border and the name manager (below and left of the toolbar) shows D1.

Figure 6 - Menus and Toolbar

Name Box, Formula Bar, and Explore button

Let's look at the Name Box, Formula Bar, and Explore button shown in red in Figure 7 within Google Sheets.

Name Box – The name box displays the name of the active data location. It could be a single cell reference, as is shown above, where the active cell is E7 or a range of cells. In addition to displaying cell references, you can use this box to create named ranges.
Formula Bar – This bar shows the content of the active cell. You can pull down the bottom of the bar to display multiple lines. Use this bar to get a better view of your formulas.
Explore – The Explore button, which also exists in Google Slides and Google Docs, allows users to ask questions about the data. You can type things such as “Sum of Units of Item Pencil.”

Figure 7 - Name Box, Formula Bar, and Explore Button

File Information

Let's look at how to retrieve information, move your file, and favorite your file from withing Google Sheets. These actions are executed but clicking on the buttons or hyperlinks as numbered in red on Figure 8 on Google Sheets.

1 – Star – The star is used to favorite an item. It makes the file show in the Starred section of Google Drive. Do this for your most important files.

2 – Move – The move icon allows you to change the spreadsheet’s location.

3 – Document Status – This icon indicates if Sheets saved the latest changes to your file. If you have internet connection problems, this icon shows issues with the file. You may want to use Google Docs Offline to continue working using the Google Chrome browser.

4 – Version History – If you click on the version history, you will see the previous saves of the file. From here, you can restore earlier versions if needed.

Figure 8 - File Information

Google Sheet Essentials

Creating Your First Spreadsheet

Table 1 Google Sheets Keyboard Shortcuts

Using the Google Supplied Monthly Budget template

Figure 9 - Selecting the Monthly Budget Template when creating a new Spreadsheet

Using the Managing your money Monthly Google Template

Up to this point, we have been working with the assumption that you are using a blank Google Worksheet as a starting point. In many cases, you are better off using a template, which is a spreadsheet predesigned with a specific purpose in mind and will have some information already in it before you begin putting your information into it. The Google Sheets Mothly budget template is one of those, and one that is developed and supported by Google.

The key to using Google Supplied Monthly budget template is to NOT input information into the non-shaded (white) cells. Begin by inputting your starting balance in cell L8 on Figure 10. Then you can begin to enter your planned monthly expenses and income into their respective cells. If you want to change the names that are used, feel free to do so. You can also enter "Custom categories" on both the expense and income side. Once you have completed your planned expenses and income for the month, you will enter your transactions on the Transactions tab as shown in Figure 11.

The sheet will provide you with a numeric summary, percentage, and graphical view of your savings (or decrease in balance) over a month. (See the Blue and Orange vertical bars) Additionally, you can see a set of dynamically updated views of your planned vs. actual expenses in the form of horizontally captioned bar charts or "sparklines."

Figure 10 - Google Sheets Budget Template Summary Sheet

Figure 10 - Google Sheets Budget Template Transactions Sheet

Using the Google Supplied Annual Budget template

Figure 11 - Selecting the Annual budget template when creating a new Spreadsheet

Using the Managing your money Annual Google Template

Like the monthly template, the annual template allows you to manage your expenses over an annual timeframe. However, the Annual budget tracker does not have a Planned vs Actual capability. It requires you to put in all of your expenses on a month-end basis. Consequently, a typical strategy is to use the Monthly template to track individual transactions on a fairly regular (daily?) basis and then at the end of every month, use the Annual budget tracker to input/transfer expenses and income from your monthly sheets.

You can do this by carefully reentering it or using cut-and-paste functionality into the Expenses and Income Sheets shown in Figures 13 and 14. Figure 15 is a summary sheet that is basically locked and you should not edit it for the most part. It sums up your income and spending by category on an annual basis.

Figure 12 - Annual Budget template setup sheet

Figure 13 - Annual Budget template Expenses sheet

Figure 14 - Annual Budget template Income sheet

Figure 15 - Annual Budget template Summary sheet

Please note: There are many free and reasonably priced budget templates available online that you can use to manage your money. I chose the templates provided by Google because although they may not be the flashiest, odds are they will provide stability and are supported by Google.

Page updated

Google Sites

Report abuse