How To Use Google Sheets To Create A Monthly Expenses Template & Income/Profit Tracker
If you need to create a small business spreadsheet for income and expenses and you want to do it without investing in a Microsoft Office subscription, look no further than Google Sheets! Google Sheets is basically a free online version of Microsoft Office’s Excel program. It does not support all of the same advanced functionality that Excel does but, for basic business purposes like tracking income and expenses, it’s a fantastic free tool!
If you run a multi-level business you likely need more advanced software, but if you are an entrepreneur with your own start-up, a blogger, or sell hand-made crafts, etc. then Google Sheets might be exactly the free solution you need to keep track of your financial ins and outs for tax purposes and business planning.
If you’ve never used Excel or Google Sheets before, it’s not as overwhelming as it may seem! My video tutorial below will help you learn some of the basics of how to start a new Google Sheets spreadsheet, format cells in it, and implement basic formulas. I will walk you through how to build a spreadsheet just like the one that I use to track my blogging income and expenses every month.
You NEED to track your business expenses and income. The information is essential for tax purposes. In addition, having a clear understanding of your business finances and your monthly ins and outs is also vital to growing your business. You need to be able to see how much profit you are bringing in and how your expenses are impacting your business to make smart strategic decisions.
Google Sheets is a great free tool to help you put together a clear tracking system! Watch below to learn how:
If you’re not a video person I’ve also spelled out step-by-step instructions below for how to create my basic income and expense tracking spreadsheet in Google Sheets. I HIGHLY recommend watching the video if you are not familiar with Google Sheets, but all of the details are also included in the written instructions.
Below is also a screenshot of what my basic spreadsheet for income and expense tracking looks like. It includes income and expense information for each month, monthly total profit calculations, and a year-t0-date profit total. It’s basic, but offers me a great overview of my earnings and monthly expenses so I can keep my business on track!
*All amounts and details below are just for example purposes and do not reflect any of my actual earnings or expenses.
To create your own income and expense tracking spreadsheet in Google Sheets:
- In the top row of your spreadsheet, starting in column B, type the name of each month (ex. in B1 type “Jan,” in C1 type “Feb,” in D1 type “Mar,” etc.)
- In cell A2 type in one of your sources of income. Type your second source of income in A3, third in A4, etc. List every source of income that you might have here.
- Fill in the total income that you earned for each source in the corresponding month column.
- Click and drag over all of your cells that contain dollar amounts. In the top white menu bar click on the “$” icon to convert the format of all of your numbers to currency.
- Click and hold on all of the cells that contain your month names. In the top white menu bar select the icon that looks like a little window. If you let your mouse hover over it it should say “Borders.” Click on the icon and then select the first option that shows you all sides of the cell in bold. This will add borders around all of your month names to set them apart from your data. You can also add color to any of your cells by selecting them and clicking on the paint bucket icon in the top white menu. Additionally, you can bold any of your headings or numbers by selecting the cells and clicking on the “B” icon in the top white menu bar.
- In the row below your last source of income (so if your income sources end in row 10, this is row 11) click the cell in column B and drag across to select the whole portion of the row that is under your month names. (Ex. if your income sources end in row 10 then you are selecting cells B11-M11.) Once you’ve selected all of these cells click on the border icon in the menu again and select the border option with a bold line on top of the box. This will add just a top line onto each of these cells to separate your totals from your breakdown per source.
- Under your new line, in the cell located under your “Jan” heading, click and type in a sum function. Type “sum=(” and then click and drag to select all of the cells above it under your “Jan” heading. When you’ve selected them all type “)” and then hit enter. You should see a calculation appear that totals all of the contents of the cells you selected. Select the cell that you just put the formula in and hit “Command C” on your keyboard for a Mac or “Ctrl C” for a PC. Then click and drag to select all of the other cells under the line you created in the same row. Once you have them all selected click “Command V” for a Mac or “Ctrl V” on a PC to paste your formula across so that it calculates for every month.
- Copy all of the content you created above by selecting all of the cells and hitting “Command C” or “Ctrl C” on your keyboard. Then skip one row below all of it and click in the first cell in column A. Hit “Command V” or “Ctrl V” to paste it all.
- Replace each of your income source names with the names of your expenses by clicking on them and typing the new expense name. It will automatically begin replacing the current contents in the cell.
- Click on each cell containing income source data and delete it’s contents. DO NOT delete the formulas under the bottom line.
- Delete any unnecessary rows after you’ve added all of your expense sources by right clicking on the row number on the far left of your screen. You will then see an option to “Delete row.” Click on this and the row will disappear. Your formulas will still correctly calculate all of the info above them. You can also add any additional rows by right clicking on the row number and selecting “Insert 1 below.” If you insert a row double click on your formula under the line and make sure it is still highlighting all of the rows above it. If it’s not correct highlight the info within the parenthesis and then click and drag over the correct cells before hitting enter. This will update your formula.
- Enter any of your expenses per source in the corresponding month columns.
- Skip a row under your expense totals and then in the first cell in column A after that row type “Grand Total.” (I also like to color this cell black and make the text bold and white using the paint bucket, “B”, and “A” icons in the white menu bar – but this is not necessary.)
- In the same row in column B click on the cell and type: =if(( and select the cell that shows your total income for Jan. Then type – and select the cell that shows your total expenses for Jan. Follow it with )<0,”-“,. Then select the cell that shows your total income again followed by – and select the cell that shows your total expenses again. At the end of it all type another ). For example, if your income totals are in cell B11 and your expense totals are in cell B20, your formula will be: =if((B11-B20)<0,”-“,B11-B20)
- Copy this formula by clicking on the cell containing it and then “Command C” or “Ctrl C.” Then select all of the other cells in the same row that fall under a month heading and click “Command V” or “Ctrl V.” This should copy your formula across every month to show your total profit after expenses for each month. You can format these cells by using the “B”, “A”, and paint bucket icons in the top white menu bar. I like to make them a bold color like yellow with bold text since these numbers are one of the most important values that I watch in my business.
- Skip a row again and in the cell below your skipped row in column A type “YTD.” Again, you can format this however you like. I like to make this cell black with bold white text to coordinate with the “Grand Total” cell.
- In the cell directly to the right of your “YTD” cell type: =sum( and select all of the cells that contain your total profit after expenses for each month. Then type ) and hit enter. This will calculate a total of all of your profits after expenses for the year so far. I like to format this cell in another bright color like green with a border all the way around and bold text.
And that’s it! Google Sheets automatically saves your spreadsheet as you go, so whenever you log back into your account it will be up to date.