Excel Tutorials | Create your Automated Business Tracker Profit and Loss Spreadsheet

1. Introduction: Hi, my name is George, and I'm here to teach you the skills to become much more proficient in using Excel. In this course, I'll be showing you how to create your very own business tracker, Profit and Loss spreadsheet to elevate your business and track your growth. C or income, expenses and profit and loss. Easily. Track your product inventory and have it update automatically. Finally, customized to fit your brand and business. But what's more important are the skills learned hair can be transferred to so many different tasks within Excel. The course is split into five sections and you will have a workbook provided to allow you to easily follow along. I'll be showing you how to create dashboards that are dynamic and change automatically depending on the days you enter. You will improve some fundamental Excel skills and learn functions such as using named ranges, Conditional Formatting, and tons of different formulas. What have wanted to showcase hair was hard to use many of the Excel functions that you have available into a truly useful product. After this course, not only will you have an easy-to-use, simple tool to track your business? But you will have also learned skills that many advanced XR uses are unaware of. And now let's begin. 2. Lesson 1: Categories: In this first video, we'll be creating our categories to use within the rest of the workbook. We will build it so that you can add up to 20 different revenue types, expense types, transaction types, and products. The really cool thing you'll learn today is how to create dynamic named ranges for all your categories. The reason we will be using these categories as drop-down lists in other tabs. And no one wants to see loads of blanks in the drop-down list. The skills learned heck, be transferable to many different scenarios. So first, open your business tracker workbook and go to the categories tab. So let's begin. First. Let's create our revenue types. To make things easier. I have highlighted cells in orange where you should enter data for revenue types will start him before. Will add to revenue types, and we'll call them product sales and affiliate marketing. Next, let's add some expense types. Starting in S4 will enter transaction fees, advertising phase, product costs, and shipping fees. Moving onto transaction types will enter some common ones, such as income, expense, balance and transfer. Now moving onto the products section, let's add some dummy product names and categories. As a startup will enter, not applicable in both the product name and product category. Has not every transaction will be related to a product and we want that flexibility. Next, we'll enter sofa, table, bed, wardrobes, and desk as the product names in column F. And finally, we'll create the following categories in column G. Now, as mentioned before, at the end of this course, you'll be able to enter more or I meant the categories we have entered to make this useful for you. However, for the course insure you keep to the same ones I have entered. Now comes the interesting bit, how to create dynamic named ranges will begin by creating a normal named range. Let's highlight so before to B203 and naming this range of cells income by going to the address box in the top left corner and entering income. Now the reason we are creating these named ranges is too easy. Refer to them in other worksheets in drop-down lists. I'll show you an example of a dropdown list and the benefits of a dynamic named range. For those of you who want an introduction into named ranges, please check out my Beginners Course. I have a section on it, the cell K4, and I'll highlight it in a different color quickly just to make it more visible. Will then go to data and data validation under Allow, select list. And then the source type. And ten equals income. You'll notice that the drop-down box has a lot of blank cells, which isn't great and can cause problems. So now let's create a dynamic named range so that only the non-blank cells will sharp using the offset function. Let me show you how this function works first, the offset function returns a cell or range of cells that is a specified number of rows and columns from a cell or range of cells. Start typing equal offset. Open parenthesis. The first argument is the starting cell we want to reference let Center before as an example. Then the next argument is the number of rows up or down from the first cell. We will enter 0 as we want to start from very, for. The next argument is the number of columns. Again, we will enter 0 as we want to reference column b. We will then enter the height, which we will make dynamic by using the count a formula in column B. Count a will count all the cells that contain data. And hence, as we add new categories, this will change. So enter count a, open parenthesis, and highlight Column B, and then close parenthesis. And since we have a title that will be counted, so we will enter minus1 after this. And finally, for the width, you'll enter one as we only want one column to be returned. Then close parenthesis. As you can see, the only categories we enter are displayed. As we add or remove a category, you'll see what is displayed gets automatically updated. Now let's update our named range with this formula. First, go to formulas and then name manager. Select the income named range, and go to the formula box at the bottom. Here, you will use the Offset command to create a dynamic named range. So type equal offset, open parenthesis, categories, exclamation mark. Before, as we want to reference the worksheet. And we will put an absolute reference around this into 0 for the row and column arguments. And to count a, open parenthesis, categories exclamation mark, column B with an absolute reference, then close parenthesis and a minus one for the height argument. And finally, one for the width argument. As a shortcut, you could have copied the formula we created an m3 and just paste it that as well. And finally click the green tick. Now looking back at the list, we can see it only contains the categories we defined. And if we add more, the list updates automatically. Let's now create the remaining named ranges starting with expense. Go back to formula's named manager. And let's copy the formula we used for the income range to speed this up. Then select me. Type expense in the income formula. Change the reference to C4. And they count a range to see and hit OK. We'll do the same for transaction types. Will name this T types. And we'll change the reference to D4 and the count a range to D. The same for product name, will name this proud name. And we'll change the reference to F4. And they count a range to F. And finally, the product category will name this proud cat and will change the reference to G4. And they count a range to G. And there you have it. We've created our categories to be used throughout the workbook using dynamic named ranges and the offset function in Excel. I've also introduce you to other functions such as data validation lists. These are just a few that you'll be using throughout this course. Keep moving forward. And by the end of this, you'll have a really great business, profit and loss tracking spreadsheet. 3. Lesson 2: Targets: In this next video, we will be creating the targets that will link through to our categories that we have defined in the previous video and allow us to set a specific revenue or expense target per each category. We will be using if statements quite extensively here. So it introduces those who have not used it to a new function. First, can you open the business, track a workbook, and go to the targets tab? Before we start, I've prepopulated some formulas as shown here. These will simply add up any data we enter to sum up the income and expense targets for each period. So let's begin by adding our categories. We want this to be dynamic and update from the categories tab. But we've done the hard work already and can refer to one of our named ranges we've created. So let's begin by going to sell BY 13 and type equal income. You can see that the income categories we've defined are now being displayed. Like in the previous videos. I have highlighted any cells in orange as a cell where the user enters free text or numbers. Let us say for product sales we have a target of 1000 per month. So we'll write a 1000 in d 13. And for affiliate marketing, we have a target of a 100 per month. So enter a 100 in d 14 will then use this to calculate the weekly, quarterly, yearly, and full history targets. First, let's use an if statement to calculate the weekly target, which is the monthly figure, divided by 4.3. As there are 4.3 weeks in a month. Go to c 13 and enter equals, if open parentheses. If d 13 equals blank, defined by these quotation marks, then blank to find by the quotation marks again. Otherwise d 13 divided by 4.3. And drag that down. Next, the quarterly. Go to E 13 and enter equal if open parenthesis, the 13 equals blank. Then blank. Otherwise d 13 times three. And the yearly go to F 13 and enter equal f, open parenthesis, d 13 equals blank, then blank. Otherwise d 13 times 12. And finally, the full history. Now this will be a more complicated formula. As your full history is not a constant. It could be three months or three years. So we need to be a bit clever hair to calculate the target for the same time period. We will need to find the number of days you have been trading. Divide that by 365, and then multiply that by your yearly income target. I'll walk you through that now. Start the same as before. Go to g 13 and enter equal f, open parenthesis, d 13 equals blank, then blank. Now in order to find the number of days you've been trading, we can use the min and max function on your transaction history. Type, max, open parenthesis, and then click on the transactions we're actually and reference column B. And then hit F4 once to put an absolute reference around this, so that when we copy this formula, it will still reference column B. Next, type subtract, and then MIN open parenthesis. And again reference column B and press F4 once to put an absolute reference. Put these formulas in brackets. Then divide by 365, and put these formulas in brackets again. And finally multiply by F 13 on the targets worksheet and close parenthesis. And just drag down there you have it. Once you populate the transactions data, your full history targets will update automatically. Let us now do the same for the expense categories. Type equal expense in IEEE 13 to reference the expense named range we have created in the previous video. Next, enter the monthly expense targets. We'll put some dummy numbers and make sure they are negative. So put minus 50 per month for transactions phase, minus 200 per month for advertising, phase, minus 500 per month for product, phase minus 100 per month for shipping fees. Now to enter the formulas, we can just copy the formulas we did for income. Select C 13 TC 30 to copy the cells and paste them in J 13. Now do the same for the quarterly, yearly and full history formulas. You can see for the full history, we're still referencing column B. If we didn't put an absolute reference and copy this across the columns would have changed. My beginner's course also covers absolute and relative references. So please check that out if you need an introduction. And finally, let's populate the annual revenue target and annual profit target. For annual revenue target go to F4 and type equals F 12. For the annual profit target, go to L4, type equal F2 plus m2. And that was the targets actually completed. In this video, you've seen how effective if statements can be to complete various tasks. Have also used some relatively complex formulas, especially when trying to calculate the full history targets. In the next session, we'll be going through the transactions were actually which we briefly touched on today. 4. Lesson 3: Transactions: Enter. If open parenthesis E5 equals blank with the quotation marks, then plank. Otherwise, some open parenthesis. Select K4, then I5, then minus J5. And close parenthesis. Copy this down to row a 100. This will sum up all your incoming and outgoing transactions to provide a total balance amount. Now instead of you having to enter transactions manually, I've created some dummy transactions that you can copy and paste values into this worksheet. For the transactions files saved in the project, and copy and paste values. All the transactions like psi. Just remember, do not paste over the product category or total balance formulas. And there you have it. In this video, we covered some more complex uses of data validation. If statements and V lookups. You also now have a working transaction C, that will be the main driver to the upcoming worksheets. In the next video, we will create our product inventory dashboard. 5. Lesson 4: Products: In this video, we will create your product inventory dashboard, will build it so that the worksheet will automatically populate as you add transactions. You don't need to worry about manually keeping track of your product list. It will be driven directly from your transactions. First opened the business track a workbook, and go to the product tab. In this worksheet will be using named ranges, SUMIFS, and is error functions to ensure the formulas cater for any scenario. And you may have noticed that the cells are highlighted in grey. This is following the same format as previous videos. Gray cells indicate that you'll enter a formula here. So let's begin with product name. Like in previous videos, we can pull through all the unique product names by referencing the named range. Go to B5 and enter equal proud name. For the product categories, we will enter if VLookup formula combination k2, C5, and enter equal f, open parenthesis. B5 is blank. Then blank. Otherwise VLookup, open parenthesis B5 for the lookup value, which is the product name, and categories, FTG for the table array. And pull three column two, and then go for an exact match. And then to close parenthesis. Now, drag this formula to the bottom. For the following columns, we will initially create a simpler version which will calculate figures based on the full transaction history. But in the next video, we'll make sure it is even more dynamic and get these figures to update based on the time period we select. For. Now, let's first calculate the total purchased. Go to D5 and enter equal f, open parenthesis. B5 is blank. Then blank. Otherwise we uses some formula which allows us to sum up data based on specific criteria. So let's enter the sum range as the Quantity column on the transactions worksheet. And the first criteria range is the product name column. And the criteria will be the product name on the product. We're actually the next criteria is the transaction type column on the transactions worksheet. And the criteria we will hard-code in the formula as expense. Remember to put in the double quotation marks. Now let's put some absolute and mix references on the cells to ensure they are locked in the correct place when we track or copy the formulas. Please refer to my lesson on cell references. If this is going too fast. And let's drag this down to the bottom. For the total products sold will copy the formula across As we only need to make one small change. Let's change expense to income as any cells will come in as income. And let's drag that down. For inventory held is simply the difference between your turtle purchased and your turtle sold. So CO2, F5, and enter equal f, open parenthesis. B5 is blank. Then blank. Otherwise, D5, total purchases minus E5, your turtle sold. And finally, for the products sold rank, we can use the rank function in Excel here. But one issue with the rank function is it doesn't cater for scenarios with the numbers are the same. So there are duplicates. Let me first show you how the rank function works. We'll go to cell G5 and into equal rank, open parenthesis. The first argument is the number we are trying to rank, which is the total sold for the first product. So select E5. The second argument is arranged. So select E5 to E3, E4, and put in absolute reference by hitting F4. The third argument is the order. So we want the largest number have rank one. So enter 0 here and drag this down to G ten. Can you see that the sofa and wardrobe had the same number of cells and the same rank. And we're also missing a rank five from the list. This highlights the issue where we are trying to rank duplicate numbers. We can get around this by making a small addition to the formula. Enter plus count if open parenthesis for your range into E5 to E5, but put in absolute reference around the first E5. For the criteria, select E5 again, close parenthesis. And then minus one close parenthesis. Drag that down to g ten. And as you can see, the sofa and wardrobe have a different rank even though they have the same number of sales. This is a very handy way to rank data with duplicates. We need to amend this formula just a little more to account for any blanks. So go to the start and create an if statement that states when the product name is blank, display a blank. Otherwise calculate the rank like say, and drag that down. And that is how you create a product inventory dashboard is driven from your transactions. You can go further and add calculations for average product cost, sales, profits, inventory values. To make this even more useful. In the next video, we will be creating the main dashboard. This is the final video in the course and will tie all the worksheets you have created so far together. This dashboard will contain charts for your revenue, expenses, and products. It will also be fully dynamic, allowing you to choose your time periods. And all the charts and figures will update accordingly. 6. Lesson 5: Main Dashboard: This is the final video that will bring everything you have done in the previous videos together. All the charts and data will be dynamic. And you'll have the option to change the time periods and have all the figures and charts update automatically. So let's begin. First, can you open the business tracker workbook and go to the main dashboard tab? This sheet is pre-populated with some simple data to get you started. But we'll do the bulk of the work together to get this dashboard up and running. First, let's start with the top-left. Like always, orange cells are cells. We enter text or numbers. Blue cells contain drop-down boxes where you will select data. And gray cells will be where you enter formulas. Let's go to cell D7 and enter the date of the first of January 2019. Then let's create the time period drop-down list. Select D8, K2, Data Validation, select list. And in the source we will manually enter our list options. For the source, we will enter weekly, comma, monthly, comma, yearly comma and full history, and select OK. As you can see, these are now available in the drop-down list. For the start date, we will need to enter an if statement to cater for the full history option. So if the time period selected equals full history, we want the start date to be the earliest transaction Day on the transactions page. Otherwise, we want the start date to be the one entered in D7. This can be done as follows. Enter equals, if open parentheses, D equals full history within the quotation marks. Then enter men. Open parenthesis. Go to the transactions worksheet and select column B. Otherwise, select D7, which is the start day. Let's also name this cell as date underscore begin, as it's much easier to reference a named range. For the end date, we will need multiple if statements to cater for every time period. So let's begin. If time period equals weekly. And the end date is the date begin plus six. Time period equals monthly. And we will want to use the date function. And the year will be the same as the date begin. The month would be the date begin plus one. And the day will be the date begin minus1. If the time period. We can copy the same formula we used for monthly, but just change the one month to 12 months. And finally, for full history, we will take the max state on the transaction worksheet. And there you have it. It was quite a long formula, but allows you to cater for all time periods. And as a final step, let's name this cell as date underscore. And I like to do one more small update that will introduce you to conditional formatting. Since the full history date range will not require you to enter a start day, it would be better to blank out what's feasible in cell D7 to avoid any confusion, this can be easily done with conditional formatting. Just highlight the cell D7. Go to the conditional formatting on the Home tab. Select new role. Go to use formula to determine which cells to format. Where it states format values where this formula is true. Enter D8 equals for history in the quotation marks. Then select Format and entered the fill and font color has the same color. We'll go with dark blue and then hit OK. Now you can see that when full history selected, the reports start date cell changes color and it is no longer visible. Next, let's populate some of the data at the bottom, starting with revenue. Go to cell C42 T1. And let's use our income named range to populate the income streams. For targets, we are going to need to pull through the targets for the correct revenue type based on the time periods selected. So we will use a combination of the index function and the match function to do that. Select the 4.2.1 type equal index, open parenthesis. Go to the targets worksheet. And the array will be b 11 to G, 32 on the target Sheet. And let's put in absolute reference around this. For the row number, we will need to look up the revenue type. So to do that, type, match open parenthesis for the lookup value back to the main dashboard and select the revenue type in C41. And we are looking this value up in column B on the target xi. So select b 11 to B32 as the lookup array and put an absolute reference around that. For the match type, we want an exact match. So put 0 and close parenthesis. And for the column number, we went to reference the column that matches the time period selected in the main dashboard. So enter match, open parenthesis. As the Lookup Value, select, deviate from the main dashboard and put in absolute reference around that. And for the lookup array, select B11 to G11 and put an absolute reference around that. For the match, we want an exact one. So put 0 and close parenthesis twice, and then drag that formula down. Now you'll notice we have a bunch of NAs that we want to get rid of. This can simply be done with an if statement. So let's update the formula. Select cell D 41, and at the start, right, if open parenthesis, select C 41, the revenue type equals blank, then blank and puts another close parenthesis at the end. Now if you drag this formula down, we get rid of the NA's. For the actual revenue we'll need to use the sum is formula to look up the transactions work she and sum up all the transactions based on the revenue type for the specific time period. This is a little more complicated, but I'll walk you through it. Select y4, z1, and type equals sumifs, open parenthesis. For the summer range, go to the transactions worksheet and select the Income column, column i, and put an absolute reference around this. For the criteria one range, select the transactions subtype on the transactions worksheet, column D, and then put an absolute reference. Four criteria. One, go back to the main dashboard and select the revenue type in C 414 criteria range to go to the transactions worksheet and select the date column, column B and F For, for an absolute reference. For our date criterias, we want to only sum the transactions that are between a start and end dates. So we need to split this into two separate criterias. For the criteria, we want to only some ANY days that are more than or equal to a start date. So we will need to enter quotation marks. The greater than sign. Equals quotation marks, the n symbol. And then we want to reference R star a. So type date underscore begin as this was the name of the start day we defined in the main dashboard. For criteria range three, select the date column, column b. Again, we want to only some, ANY days that are less than or equal to date. We will need to enter quotation marks. The less than symbol equals quotation marks and the n symbol. And we want to reference our end day. So type date underscore end, as this was the name of the end date we defined on the main dashboard. Now I know that was a lot, but we're almost there. To cater for scenarios where you may have negative transaction types for income, IE, returns, or refunds. We want to ensure these are also accounted for. So enter a minus sign and then copy the whole formula and paste it off to the minus sign. And all you need to change is the sum range from the incoming column, column i to the outgoings column, column j. Just like we did on the targets formula, we'll make a quick update so that no data is calculated when we do not have an income type. So select cell E4, t1, and at the star, right, if open parenthesis, select the revenue type in C41 equals blank, blank, and put another close parenthesis at the end. Now, drag this formula down. The differences formula is much simpler. It's just your actual income minus your target income. So select F4, t1 type equals, if open parentheses. Your income type equals blank. Blank. Otherwise your actual income minus your target. So E4, t1 minus t0 41. And drag that down. We'll do the same for expenses now. Go so H 41 and type equal expense to populate all your expense streams. For the expense targets, it's similar to the formula we did for income targets. Go to cell I4, T1, and enter equal f, open parenthesis, which 41 is blank, then blank. Otherwise we'll want to pull in the expense targets. So enter index, open parenthesis for the array. Go to the targets worksheet and select IL-1 to N32. Hit F4 for an absolute reference. For the row num into Match. Open parenthesis. For the Lookup Value, select age 41 on the main dashboard. The lookup array is IL-1 to either 32 on the targets worksheet. Hit F4 again for an absolute reference and a 0 for the match type. For the column NUM. Enter match, open parenthesis. For the Lookup Value, select D8 on the main dashboard. The lookup array is 1111 on the targets worksheet. And enter zeros for the match type. Drag this formula down. For the actual expense. We can simply copy the formula from the income table like so. And drag that down. And the difference can be copied and drag Dan as well. Lets now move onto the graphs. Will begin in the top right with a simple one. Right-click the box, have click Select Data, then select hey, H6 to j nine, and the chart will update. And click OK. Next, we'll create the graph for the income stream. And I'll show you how to make these charts dynamic so that it will ignore any blank cells. So I'll first show the problem. If we don't make the graph dynamic, right-click the box above the income table and click Select Data was holding Control. Select cells C4, T1 to E6, and then select C 39, t 39. And showed the legend entries state Target and actual. And the horizontal axis state product, sales, affiliate marketing. If they are reversed, just hit the switch row and columns button. Then HIT okay. You'll notice that the graph is not that useful because of all the blanks. And you don't want to have to update the ranges each time you add categories. So I'll show you how to make it dynamic. We need to use the offset function to define the series alongside named ranges. So let me show you the formula for the income target values. Pick any free cell. So let's pick D6, D2 and type equal offset open parenthesis. For the reference, select D 41 and hit F4 for an absolute reference. For the rows into 00 for the columns, for the height into account a. And select default t1, t2, d 60, and hit F4 for an absolute reference. And close parenthesis. Then minus count blank, open parenthesis, and select the 41 to D6 again and hit F4 and close parenthesis. So we are counting the number of cells that have data in it, including formulas minus the number of blanks, which result in the number of nonblank cells. For the width N21. Then finally close parenthesis. And as you can see, only the non-blank values for income targets are shown. And this is the formula we will use for the ink target named range. Let us quickly do the formula for the income actual named range. Copy the formula we just did for income targets and change the references from D to E psi. Now that you have the formulas, let's define a named ranges. Copy the income target formula, go to formulas, and then name manager. Select new for the name type ink. Target four refers to paste the formula. Heat. Okay. Now copy the income actual formula we did and create the ink actual name range, like say, the name ranges are created, we can update our graphs. Right-click and go select data, select the target series and click edit. And we need to do is replace the cell references at the end with ink target just after the exclamation mark. Do the same for the actual series and click Edit, replaced the cell references at the end with ink actual just after the exclamation mark. Once you hit okay, the charge now update. We'll need to do the same for expense target and expense actual. Copy the ink actual formula and change the reference from E to I like so to calculate a dynamic range for the expense target and repeat for the actual expense and change the reference from i to j. Next, we will create the name ranges for both the expense target and expense actual. Copy the formula for expense target. Then go to Name Manager, select new type exp target, and paste the formula. And do the same for actual like psi. We will name it. And finally, we will do the charts. First, right-click the box and select data. Let's select some data to get some information populated and make it much easier whilst holding Control. Select cells H 41 to j 60, and then select H 39 to J 39 ensured the Legend Entry state Target and actual and the horizontal axis state the expense types. If they are reversed. Just hit the switch row and columns button. Select the target series and click Edit. And all you need to do is replace the cell references at the end with XP target just after the exclamation mark. Do the same for the actual series and click Edit. Replace the cell references at the end with x Actual just after the exclamation mark. And now both your revenue and expense graphs are dynamic. So I have less the products section until last as we need to make an update to our formulas on the product C. So that updates based on the time period set. Go to the product worksheet and we will need to update the formulas for total purchase and total sold. Let's first go to cell D5 in the total purchase column and add conditions for the time period. For the third criteria range. Go to the transactions we're actually and select column B and then hit F4 for an absolute reference. The third criteria, type quotation marks greater than equals quotation mark. And then add the n symbol. And then reference the start date, which is the date begin. For the fourth criteria range, select column B again and then hit F4 for an absolute reference. And for the fourth criteria, type quotation marks, less than equals, quotation mark. And then the n symbol. And then reference the end date, which is called date. And the end. This will then only some the transactions within the time period selected on the main dashboard page. Let's drag that formula down. Now to speed up, updating the total sold, we can copy this part of the formula and paste the formula at the end, like so. And then drag down. Okay, great. Now that the products heat is updated, we can now complete the product data on the main dashboard. Go to the main dashboard worksheet and select cell and 41. And here we want to pull through the top-selling products. We can use the index and match functions to do this. So type equals index, open parenthesis for the array go to the product sheet and slept column B, which is the product name column, and hit F4 for an absolute reference. Therefore, the row number, we will look for one in the product, so rank column using the match function. So enter match, open parenthesis for the Lookup Value, select m for T1 on the main dashboard. Will also put a mixed reference where we want to look the columns so it's easy to copy by pressing F4 three times. For the lookup array, goto the product sheet and select column G and hit F4 once for an absolute reference. For the match type into 0 and then close parenthesis. And for the column NUM, you can enter 0 and close parenthesis. And then you can drag this formula down. You'll notice we get NAs as we have less than ten products. So we can fix this by using a combination of the if statement and is error function. Go back to n 41 and go to the beginning of the formula. And type f, open parenthesis is error, open parenthesis. And then go to the end and put a close parenthesis as a logical argument for the value if true, enter quotation marks like say and four, the value is false, copy the index formula and paste it here. What this means is if the index formula generates an error because the lookup value doesn't exist, then display a blank, otherwise, display the index result. Less. Drag that down. You can see the NAs disappear. Now, for the total purchased column, we can copy the formula for the product names. And we just need to change the array from column B and the product sheet to column D. Remember to change it twice. We change the array to column D as this was the total purchased column on the product sheet. Next, drag down. And then just quickly central line it. For the total soil column. We can copy the formula and change the array to column E. And then drag down. For the inventory held. We can copy the formula and change the array to column F, and then drag down. And finally, we will update the chart to show the top products sold will make these dynamic the same way we did for the income and expense grass. To speed this up, we will copy the offset formulas we did previously. And we will change the ranges like psi to reference the products sold data in column P. We'll then copy the formula, goto formulas, then name manager, and add a new name range for the name, call it product sold, and copy the formula in the refers to box. Then close the Name Manager books. We will then create the graphs. So right-click the box, click Select Data. Let's first select some data, self-control. Select n 41 to 50, and then select P 4150. Make sure the product names are in the horizontal box on the right hand side. For the series, click edit for the series name, right. Products sold. For the series values. Delete the cell references and replace with product sold, which is the name range. Just after the exclamation mark. We will just delete all those offset formulas we created as they're no longer needed. And that is, you have now built your business track. A spreadsheet, data is fully dynamic. You can change the time periods and you can change the start date and the graphs and data will update. Like say. I really do hope that you found this course useful. And the skills learned here will allow you to create all sorts of different spreadsheets. And so various problems within Excel. If you have any questions you want me to do, any other courses, please let me know.