Effortlessly Manage Your Invoice Tracking Spreadsheet for Export

Invoice tracking spreadsheet for Export

hi there and welcome to my tutorial on how to build an invoice tracker in Microsoft Excel with this tracker you'll be able to store all of your invoice information in one place easily view the status of each individual invoice which updates automatically and see a quick summary of your invoice numbers right here at the top oky dokie if this looks like a template that you want to build then stick with me and if you'd rather just buy it instead I left a link just for you in the description that being said let's get to it starting with a blank Excel workbook let's begin by setting up the worksheet structure click here to select all cells change the horizontal alignment to left and change the vertical alignment to Middle right click on the row headers here select row height change the height to 25 and click okay right click on the column headers column width change the width to 20 and click okay now select row four rightclick row height change the height to 50 and click okay then select column a rightclick column width set the width to five click okay awesome now let's add in the titles and headers select cell B1 and type in invoice tracker then select B3 and type in overdue then unpaid partially paid and paid select H3 next type in still to be paid then total paid select B6 and type in invoice number then customer invoice date due date invoice amount paid amount balance and status fantastic now let's add in some formatting click here to select all cells and add a light gray fill color then select Row one bold the font increase the font size to 14 add a dark blue fill color and change the font color to white now select B3 through E4 change the horizontal alignment to Center then rightclick format cells click on the border tab change the color to this gray here click the outline and inside buttons to apply borders around all sides and click okay select B3 through E3 fold the font and add this slightly darker gray fill color then select B4 through E4 change the fill color to white and increase the font size to 24 nice now select B3 through C4 click the format painter tool right here and then select cell H3 this just copies the formatting straight to these cells and also saves us a whole lot of time so all you have to do now is Select H4 through I4 and change the font size to 20 oky doie now for the invoice table below start by selecting the range B6 through I 10 and go to insert table check the box next to my table has headers and click okay now this next part is very important under the table design tab up here in the ribbon under table name change the name to invoice and press enter this is very important for the formulas that we will write later and once you've done that make sure that the entire table is selected and go to home fill color no fill and now we can see the table a lot better the next step is to customize the table design to do this go to table design in the ribbon click the down arrow here and select new table style give the style a name of your choice then select the whole table element and click format under the Border tab change the color to this gray and click the outline button to apply the border around the outside of the table then change the color to this slightly lighter gray and click the inside button to apply this border on the inside of the table and finally click okay now select the header row element and click format under the Border tab select this gray click the outline and inside buttons move to the font tab bold the font move to the fill Tab and select this gray color here you can then click okay and then okay to apply the style to the table now click the down arrow here and select your custom style now you will notice that your table has filter buttons that you can use for each column you can leave them there if you want but but you can also hide them by unchecking the filter button option right here either way it's up to you and now that you have your table go ahead and enter a few pieces of sample data like so the sample data is going to help us validate our formulas here in a little bit and once you've entered some data the next step is to update the number formatting for each column in other words change how the information is displayed within the table so let's begin with the invoice number column depending on your specific situation you might want your invoice numbers to have a set number of leading zeros or a certain number of digits personally my invoice numbers are all four digits long so to customize this select the cells in the invoice number column rightclick and format cells move to the number tab and select custom then replace General with zeros like so if you want your numbers to be five digits long then type five zeros if you want your numbers to be three digits long then type three zeros the number of zeros represents the number of digits that you want and once you have it the way that you want click on okay now for the date columns select both columns rightclick and format cells select date and then make sure that your location is correct here then select a date format that works for you and click okay next select the amount and balance columns like so then rightclick and format cells you can select number currency or accounting it's completely up to you and then click okay finally select the status column and the column header as well then change the horizontal alignment to Center and then select the amount and balance headers and also change the horizontal alignment to Center all right we are now ready to add in some formulas let's start with calculating the balance column select cell H7 and enter the following equals select the invoice amount and you should see this entered into your formula this is called a structured reference and it shows up whenever you reference table cells in the same row like we are doing now after that add a minus sign select the paid amount and press enter you'll notice that the formula is copied down for all other rows in the table this is a feature of excel tables that happens automatically moving on now let's add the formula that will calculate the status for this formula in particular we're going to use the ifs function if your version of excel is older than Excel 2019 then this isn't going to work for you so instead use the alternate formula that I left for you in the description of the video otherwise let's write the formula select cell i7 and enter the following equals ifs function Open Bracket at symbol invoice amount close bracket equals 0 next argument quote- quote next argument and here you can press Alt Enter to add a new line just to make the formula easier to read then Open Bracket at symbol balance close bracket equals z next argument quote paid quote next argument Alt Enter today function be sure to close parentheses and then greater than Open Bracket at symbol due date close bracket next argument quote overdue quote next argument Alt Enter Open Bracket at symbol paid amount close bracket greater than zero next argument quote partially paid quote next argument Alt Enter one next argument quote unpaid quote close parentheses and press enter you should now get a status that is automatically calculated for each row awesome now for the numbers on top start by selecting cell B4 and enter the following formula equals count if function invoice Open Bracket status close bracket next argument B3 close parentheses and press enter this formula counts the number of invoices in the status column that contain the status matching the value in cell B3 which in this case is overdue and now all you have to do is copy cell B4 then select C4 and paste and then paste and paste nice two more formulas to go select cell H4 and enter the following equals sum function invoice Open Bracket balance close bracket close parentheses and press enter now select cell I4 and enter the formula equals Su function invoice Open Bracket paid amount close bracket close parenthesis and press enter then of course if you want to keep the number formatting like this with the dollar signs you can leave it that way or you can select the cells rightclick format cells select number and click okay either either way it's up to you all right now that we have all of the formulas in place all that's left is to add some final formatting start by selecting cell H4 and change the font color to dark red then select I4 and change the font color to dark green next select the balance column and change the font color to Blue I do this in particular because this helps remind me that these numbers are calculated by formula finally we will add in some conditional formatting that will change the color of the status cells based on the calculated status start by selecting the status column and then go to home conditional formatting highlight cell rules equal to in the blank box enter paid then in the next box select custom format Under The Fill tab select more colors click on custom and enter the hex code E6 FF then click okay click on the font tab fold the font change the color to dark green and click okay and then okay let's do the overdue status next select the status column and go to conditional formatting highlight cell rules equal to enter overdue select custom format Under The Fill tab select more colors click on custom and enter the hex code FF E6 E6 then click okay click on the font tab bold the font and change the color to dark red then click okay and okay all right now the partially paid status select the status column and go to conditional formatting highlight cell rules equal to enter partially paid then select custom format and under the The Fill tab select more colors click on custom and enter the hex code FF FF E6 then click okay click okay and then okay and just like that you're done now to expand the table just click on the bottom right corner here and drag down to add more rows this way you can add more information at any any time and as your table grows to keep the header rows visible at all times select cell A7 and go to view freeze pains freeze pains this will enable you to scroll the rest of the table while keeping everything else in view oky dokie that does it for this build so thank you so much for watching and be sure to leave a comment if you have any questions or if you just want to say hi also consider subscribing if you learn something new and I will see you again in the next spreadsheet life video you're like a circle that floats around me keeping me safe and sound and I you TI a r me you're blessing me