Get Your Google Sheets Invoice Template Free for Accounting

Google sheets invoice template free for Accounting

in this video you're going to learn how to make this simple but awesome bookkeeping template in Google Sheets with this template you can create categories for both your income and expenses track all of your income and expense transactions in one place and view a summary of everything on this amazing Dynamic dashboard where you can select the year and month to update the information automatically as always you will find a link to the template in the description if you would rather buy it instead of build it and other than that let's get started beginning with a blank Google sheet spreadsheet we will start with the worksheet structure so select columns I through Z right click on the column headers and delete next click here in this box which is called the name box type in two in 1000 and press enter this action selects all of those rows and now right click on the row headers resize rows set the size to 30 and click okay select Row one next right click resize row set the size to 40 and click okay now with Row one still selected increase the font size to 14 bold the font change the font color to white click the fill color button and click the plus to create a custom color under hex enter 435 361 and click okay this creates a nice dark gray blue color that I like to work with now from here select column A rightclick resize column set the size to 50 and click okay finally click here to select all cells then change the horizontal alignment to left and change the vertical alignment to Middle then go to view show and uncheck grid lines the general worksheet structure is now complete so the next step is to create two copies of this worksheet just right click on the worksheet Tab and select duplicate then just repeat this process one more time once you have all three worksheets change the names to categories income and dashboard we will add the expenses worksheet in later for now on the dashboard worksheet select columns a through H right click and insert eight columns to the right because we will need these columns later on for now go ahead and move back to the categories worksheet on this worksheet click in the name box type in 16 colon 1000 and press enter then right click and delete now select column B hold the control key and also select column e then right click resize columns set the size to 175 and click okay select column d rightclick resize column set the size to 50 and click okay next select the range B3 through C13 hold control and also select E3 through f13 then click on the borders button change the color to Gray and click this button to apply all borders select select B3 through C3 hold control select E3 through F3 then bold the font and add a light gray fill color now select C4 through C13 hold control also select F4 through F14 change the font color to Blue and add a light gray fill color now these two tables give you enough space for up to 10 income and expense categories if you want to add more than 10 categories then you can add more space by selecting rows in the middle of the table like so rightclick and insert and you can repeat this process as many times as you need but for this video I will be sticking with the space for up to 10 categories like so that being said your next step is to select B3 through C3 click the borders button and for the color click the plus to create a custom color under hex enter 01b 8 AA and click okay then increase the Border thickness by one and apply a bottom border now select e 3 through F3 click the borders button and for the color click the plus and under hex enter fd6 25e and click okay then apply a bottom border awesome looking good now just select cell B1 and type in categories then in b 3 Type in income category then total and in E3 type in expense category and then total now select column C hold control and select column F then change the horizontal alignment to Center finally enter a few sample categories like so because this will help us validate our formulas later on and once you've done that select the full range of possible income categories like so then click in the name box type in income list with no spaces and press enter this creates a range name that we can use elsewhere to easily refer back to our income categories and now for the expense C categories select the expense range click in the name box enter expense list and press enter awesome that does it for the categories worksheet for now so on to the income worksheet next start by selecting cell B1 and type in income then select B3 through G8 click the the borders button change the color to gray change the Border thickness back to the top option and apply all borders then select the range B3 through G3 bold the font add a light gray fill color click the borders button change the color to the custom green color change the thickness back to the second option and apply the bottom border then select F4 through G8 change the font color to Blue and add a light gray fill color then select B3 and enter date then category description amount year and month select columns e through G change the horizontal alignment to Center select columns C and D rightclick resize columns change the size to 175 click okay select column B rightclick resize column change the size to 125 and click okay now let's create a drop- down menu for the category column to make it easy to select a specific income category so select C4 through C8 and go to data data validation click on ADD Rule and under criteria select drop- down from a range then in the box below type in equals income list which is the range name that we created earlier and press enter you should then see your samp Le income categories appear here now under Advanced options you can leave the display style set to chip if you want to personally I like The Arrow style so that is what I will select either way you can now click done close the panel on the right and easily assign categories while you're at it go ahead and type in some other sample data like so and and after you've done that select column e Click on the more formats button here and select number this will add two decimal places for your numbers and now let's update the format of the dates so select column B click on the more formats button and select custom date and time you can scroll to find a date format that works for you or you can build your own and to do that start by deleting everything in this box at the top and then you can use the down arrow to select the different components and build the date format however you want it for me I like to have the month abbreviation a space the day number a comma then a space and the full year and once you have the date format the way you want it you can click on done awesome next select cell F4 and enter the following formula equals IF function is blank function before close parentheses next argument quote- quote next argument year function B4 close parenthesis close parenthesis and enter this formula checks to see if the date cell is blank and if not it Returns the year of that date now in cell G4 enter this formula equals IF function is blank function B close parentheses next argument quote- quote next argument text function B4 next argument quote mmm quote close parentheses close parentheses and press enter this formula also checks to see if the date cell is blank and if not it Returns the abbreviated month of that date and with the two formulas done select F4 and G4 four and copy the formulas down to Row 8 using the fill handle fantastic to complete the worksheet select B8 through G8 and press control shift down then press contrl D this copies all of the formatting data validation and formulas down to the end of the worksheet and that does it for the income worksheet now now to save some time moving forward right click on the worksheet Tab and select duplicate then rename the copy to expenses and from here we just have to make a few changes instead of building the expenses worksheet all the way from scratch so the first thing we want to do is Select cell B1 and change income to expenses then change the amount to something different like [Music] so and after that go to data data validation in the panel on the right click on the data validation rule that you see there and change income list to expense list and then press enter you should now see the expense categories instead and you can click on done close the panel on the right and then change the categories of your sample data like so now for the Final Touch select B3 through G3 click the borders button change the color to the custom red color and apply the bottom border awesome you are now done with the expenses worksheet 2 onto the dashboard worksheet start by selecting cell B1 and type in dashboard then select column B hold control and select column G then right click resize columns set the size to 175 and click okay select column e hold control and select column J then right click resize columns set the size to 25 and click okay select column F hold control also select columns K and P then right click resize columns set the size to 50 and click okay now select B3 through B4 bold the font change the font color to white and add the gray blue custom fill color then select C3 through C4 bold the font add a light gray fill color and change the horizontal alignment to Center select G3 through j4 bold the font add a light gray fill color then click the borders button change the color to white and change the thickness to the top option and apply all borders then change the horizontal alignment to Center now select B6 through E6 bold the font add a light gray fill color click the borders button change the color to the custom green and apply the bottom border then select G6 through j6 bold the font add a light gray fill color click the borders button change the color to the custom red and apply the bottom border select B7 through e16 hold control select G7 through j16 and apply a light gray fill color now this is enough space to display the data for 10 categories if you added space for more categories at the beginning then you need to expand the gray ranges here to accommodate for those extra categories and now is the best time to do that and once you've done so select cellb 3 and type in select the year and then select the month then in G3 type in profit income and expenses while you're at it select I3 and j3 then click this button to merge the cells into one then select I4 through j4 and merge these cells also now select B6 and type in income then total and then chart and in G6 type in expenses total and chart nice for your next step select cells l 3 through O3 bold the font and add a light gray fill color then in L3 enter month then income expenses and profit and in L4 enter J N then use the fill handle to complete the list of months now for the Selections in C3 and C4 in cell C3 the user can simply type in a year value like so but for the month we want to create a drop-down menu so with C4 selected go to data data validation click on ADD Rule and under criteria choose drop- down from a range click the select data range button select L4 through l15 and click okay then under Advanced options select plain text click done and close the panel on the right you can then start typing in cell C4 in the drop-down list makes it very easy to pick a month to complete the formatting on this page select cell G4 and change the font color to Blue then select H4 and change the font color to the custom green then select I4 and change the font color to the custom red then select M4 through 015 and change the font color to Blue awesome we are now ready to focus on the formulas that power the dashboard in fact the first thing that we want to do is create two more range names that are going to help us write these formulas to do so select cell C3 click in the name box and type in dase and press enter then select C4 click in the name box type in- Monon and press enter once you've done that select cell M4 and enter the following formula equals sum ifs function function click on the income worksheet select E4 then add a colon and E then add dollar signs to make this an absolute reference move to the next argument select cell F4 add a colon and then F and then add dollar signs to make this an absolute reference next argument year next argument select cell G4 colon G and add dollar signs to make this an absolute reference next argument dollar sign L4 then close parentheses and press enter this formula uses the sum ifs function to add up all of the income transactions from January of the selected year now for your next Formula select cell N4 and enter the following equals sum ifs function click on the expenses tab select cell E4 colon e make this an absolute reference next argument F4 colon F make this an absolute reference next argument Das year next argument G4 colon G make this an absolute reference next argument dollar sign L4 close parenthesis and enter this formula uses the sum ifs function to add up all of the expense transactions from January of the selected year next select cell 04 and enter the formula equal M4 minus in4 and once you have all three formulas go ahead and select them then use the fill handle to copy them down to row 15 awesome next select cell G4 and enter the following formula equal H4 minus I4 then select H4 and enter the formula equals IF function is blank function- mon next argument sum function M4 through M15 close parentheses next argument X lookup function C4 next argument l 4 through l15 next argument M4 through M15 close parentheses close parentheses and enter this formula sums the income numbers if no month is selected otherwise it Returns the income of the selected month now for the next Formula select cell I4 and enter the following equals IF function is blank function- Monon close parentheses next argument sum function N4 through N15 close parentheses next argument X lookup function C4 next argument L4 through l15 next argument N4 through N15 close parentheses close parenthesis and press enter this formula sums the expense numbers if no month is selected otherwise it just Returns the expense of the selected month okay at this point we need to move back to the categories worksheet to add some formulas there and once you're there select cell C4 and enter the following formula equals if function is blank function dasm month close parentheses next argument sum ifs function income exclamation point dollar sign e dollar sign 4 colon dollar sign e next argument income exclamation Point dollar sign F dollar sign 4 colon dollar sign F next argument Das year next argument income exclamation point dollar sign C dollar sign 4 colon dollar sign C next argument B4 close parenthesis next argument sum ifs function income exclamation point dollar sign e dollar sign 4 colon dollar sign e next argument income exclamation point dollar sign F dollar sign 4 colon dollar sign F next argument Das year next argument income exclamation point dollar sign G dollar sign 4 colon dollar sign G next argument Dash month next argument income exclamation point dollar sign C dollar sign 4 colon dollar sign C next ARG ment B4 close parenthesis close parenthesis and press enter this formula checks to see if a month has been selected on the dashboard if not it calculates the total income for category 1 in the selected year otherwise it calculates the total income for category 1 in both the selected year and month and once you finish the formula use the fill handle to copy it down for all of the categories and after that select cell F4 and enter the following formula equals IF function is blank function Dash month close parentheses next argument sum ifs function expenses exclamation point dollar sign sign e dollar sign 4 colon dollar sign e next argument expenses exclamation point dollar sign F dollar sign 4 colon dollar sign F next argument Dash year next argument expenses exclamation point dollar sign C dollar sign 4 colon sign C next argument E4 close parentheses next argument sum ifs function expenses exclamation point dollar sign e dollar sign 4 colon dollar sign e next argument expenses exclamation point dollar sign F dollar sign 4 colon dollar sign F next argument dase next argument expenses exclamation point dollar sign G dollar sign 4 colon dollar sign G next argument Dash month next argument expenses exclamation point dollar sign C dollar sign 4 colon dollar sign C next argument E4 close parenthesis close parenthesis and press enter this formula checks to see if a month has been selected on the dashboard if not it calculates the total expenses for category a in the selected year otherwise it calculates the total expenses for category a in both the selected year and month and of course use the fil handle to copy the formula down for all of the categories awesome now move back to the dashboard worksheet select cell B7 and enter the following formula equals sort function then click on the categories tab select all of the income categories and totals next argument two next argument false close parentheses and press enter this formula sorts the income category data in descending order by the total amounts and places that information here on the dashboard next select cell G7 and enter the following formula equals sort function click on the categories tab select all of the expense categories and totals next next argument two next argument false close parentheses and press enter this formula sorts the expense category data in descending order by the total amounts and places that information here on the dashboard now for the data bars select cell D7 and enter the following formula equals IF function is blank function Dash year close parentheses next argument quote quote next argument sparkline function C7 next argument Open Bracket quote chart type quote comma quote bar quote semicolon quote Max quote comma Max function C7 through c16 and add dollar signs to make this an absolute reference then close parentheses semicolon quote color one quote comma quote 01 B 8 a a quote close bracket close parenthesis close parenthesis and press enter now if you live in a country where the argument separator is a semicolon rather than a comma and you would have noticed by now to get the other formulas to work then the Syntax for this formula in particular is going to be quite different for you it's going to look like this and instead so this might help if you're struggling to get it to work that being said once you've written the formula go ahead and use the fill handle to copy the formula down to cell D16 and after that select cell i7 and enter the following formula equals IF function is blank function Das year close parentheses next argument quote quote next argument sparkline function H7 next argument Open Bracket quote chart type quote comma quote bar quote semicolon quote Max quote comma Max function H7 through h16 and make this an absolute reference close parenthesis semicolon quote color one quote comma quote fd6 25e quote close bracket close parenthesis close parentheses and press enter and once again if you use semicolons instead of commas for the argument separator then your formula is going to look like this instead once you have it down go ahead and use the fill handle to copy the formula down to cell i16 awesome now select the range C7 through c16 hold control and also select H7 through h16 and G4 through j4 click the more formats button here and select number the dashboard is almost complete All That Remains now is the chart on the bottom so start by selecting the range L3 through o15 then go to insert chart in the panel on the right under setup change the chart type to combo then click on customize and under chart Style change the background color to light gray and set the chart border color To None under chart and axis titles remove the chart title under series select income set the type to columns change the fill color to the custom some green select expenses change the type to columns change the fill color to the custom red then select profit set the type to line change the line color to the custom dark blue gray change the line thickness to four pixels and set the point size to 10 pixels under Legend Chang the font size to 6 16 pixels bold the font under horizontal axis change the font size to 14 pixels under grid lines and ticks select vertical axis and uncheck major grid lines and then you can close the panel on the right and resize the chart to fill the space below the income and expense categories after that select columns l through o rightclick and hide Google Sheets will then ask you if you want to include the hidden data and you should click include data and just like that you are done with your simple bookkeeping template in Google Sheets leave me a comment if you have any questions and like this video if you learn something new and as always until next time I will see you again in the next spreadsheet life video a