welcome to this tutorial on how to create the ultimate personal budget in Excel a personal budget is one of the most fundamental and Powerful tools for actively taking control over your personal finances as it helps you to understand and to proactively plan where your money goes while many people already use Excel to build simply expense trackers for themselves I want to bring this to an entirely new level and show you how to build a full-blown personal budget management tool in Excel that lets you proactively plan your income allocation to custom expense and savings categories for every single month then lets you track and assign your actual money flows to these defined categories and based on that automatically generates a well-structured and interactive dashboard analysis with a detailed breakdown of your budget performance for each individual month and year let me quickly state that nothing said in this video is financial advice I'm not a financial advisor and this tutorial is solely about the creation of a powerful personal finance tool in Excel with that being said let's take a deeper look at the final Excel template that we're gonna build today the budget planning worksheet is where the journey starts it's designed for creating a so-called serial-based budget plan which essentially means that every dollar that comes in has to be allocated either to your expenses or to your savings so that the planned income minus the planned expenses and savings will equal zero with the template being fully empty at the beginning the first thing to start with is defining custom categories in the income expenses and saving section to add a new custom category in any of these sections you can simply unhide these prepared empty rows Define the category and it automatically gets added to this table and will now be considered in all further calculations and worksheets every additional Dollar in income that you plan for a month will increase the amount to be allocated in that same month just like in the income section you can add as many custom expense categories as you need by using those prepared empty rows the expense section should cover both your needs like housing utilities groceries and also something like minimum loan payments and your wants like entertainment fun vacation donations and many more every dollar allocated to an expenses category will decrease the amount left to be allocated so that after you have planned all your expenses you know exactly how much will be left for your savings the savings section can then be used for planning your contributions to both long-term savings Investments or extra payments on that but also short-term savings Ambitions in the form of sinking funds that help you to systematically save towards the future expense or down payment the goal should be to plan the contributions based on your saving priorities until the amount left to be allocated is exactly zero which means you now have a plan for every single dollar of disposable income and your budget plan for that month is completed but in real life not every month is the same and for many categories planning the exact same amount each month is not realistic this template does not force you to create one static budget plan that has to be used for every month but instead allows you to create a realistic multi-period budget plan on one single worksheet in basically no time let me show you how you can expand this budget plan for the whole year in case all income positions will stay the same in each month planning the remaining months is done in a second using the autocomplete handle but if you have a bit more variety in your monthly income so that for example you know there will be a level shift for your salary through a promotion then you have an income category that you expect to stay on the same level each month and a third income Source you know will be different every single month all of this can be accurately portrayed in your budget plan for the expenses you can plan regular expenses ahead just as easy in case you have an expense category that will be paid only once a year or once a quarter like insurances for example you don't have to break it down into 12 equal monthly amounts but you can budget the actual expense whenever it's expected to be paid obviously there might be regular categories like clothing or medicine that in reality are a bit more volatile than other regular categories like for example rent yet setting a fixed monthly budget for these positions will give you a good Benchmark for each month in case you cannot exactly predict the distributions over the year and finally you can also have mixed categories like fun and vacation for which you want to plan a base level each month for things like going out eating in a restaurant buying birthday gifts Etc but in specific months this amount will Peak when you plan to go on vacation or plan to spend more money for gifts and celebrations around Christmas every expected variation of expenses over the year can be accurately represented in this budget plan for the savings section you can now plan the allocation of the remaining amount available in each month here it makes sense to plan ahead with accordance with your personal priorities if for example your emergency fund is still 3 000 short of the amount that you feel safe with you can prioritize that and allocate a thousand for the first three months since the plant budget for this position goes to zero after that you will have more Capital available for other savings categories based on your priorities you can then go through all further positions and decide if you want to have a constant amount allocated each month if you want to increase the position once more money is available for saving or even start allocating to a new category once you have additional free resources to do so what's crucial in the end is that for each month you have a plan for every available dollar from your income streams so that the amount left to be allocated is exactly zero and the green check mark at the top is visible when we jump to the dashboard worksheet all the defined categories and planned budget amounts are instantly available on the left side you see a sorted breakdown of the categories that dynamically grows and shrinks as you add or remove categories in the budget planning sheet on the right side in the summary section the tracked versus budget chart beautifully visualizes our budget plan over time and even allows you to single out two or one specific types moreover using the drop down at the top you can easily switch between a total year View and monthly views of the budget plan in the budget tracking sheet you can then start tracking your actual money flows in this smart table for a new entry you simply need to enter the according date select the type as either income expenses or savings and then based on which type you have selected all the previously defined categories of that type will become selectable in that category drop down finally you can enter the amount as an absolute number and enter the details for that record whatever type you select the category drop down will always automatically adjust its list of values as you add records for different types you will notice that the amounts are automatically formatted to visually differentiate between income on the left and the allocation of income to either expenses or savings on the right when we add the dashboard breakdown in split view you can perfectly see how every new record instantly updates the respective tracked amount of that period in that breakdown the categories are automatically sorted by Tracked amount as the primary and budgeted amount as the secondary sorting Criterion and you always instantly know which percentage of the budgeted amount you have already tracked and how much of the budget is still remaining for each category and type the balance column will always keep track of all the tracked money flows up until the date of the respective record a positive or negative amount in their tracking balance column should be reflected in the main bank account through which your money flows from income to expenses and savings this balance is calculated in a way that is completely robust against any sorting or filtering in the table that means you can easily add a new record that chronologically should be placed right before these existing records the balance of this new record and all later records is still calculated correctly we can then bring this record to the chronologically correct position by sorting the date column from oldest to newest and anytime you want to take a closer look at certain transactions in a specific period for example the expenses only is sorted by amount you can easily do that and the Integrity of the balance calculation based on each individual date is still guaranteed the effective date column right next to that is responsible for another great optional feature that solves a real issue for many people when it comes to tracking their income because for many people it's the usual case to receive their income for example in the form of a paycheck rather towards the end of a month instead of the beginning of a month and if that's the case for you you might want to treat that income not as the income to make up for all the previously tracked expenses and Savings in that same month but rather in a forward-looking way as the disposable income for the next month so that you first earn it and then you spend it and save it and not the other way around if that is something you want to apply you can simply jump to the settings worksheet and activate the late monthly income option for which you can also specify the day in each month from which on tracked income is considered for the next month so with 20 in here every tracked income with a date of the 20th or later in a month is assigned to the next month just another great feature to better cope with the realities of life after entering all data to bring your budget planning and tracking up to date so that the lag between your latest record and the current date is as small as possible the budget dashboard is a great place to understand analyze and evaluate your personal budget performance with the current month as the selected period you can take a look at how much of that month has already passed in order to better evaluate the percentage of your budgeted amount that you have tracked so far for each type and category in addition the period tracking balance shows you exactly how much of your track disposable income is still left to be allocated in that month and down here that balance value is then perfectly broken down into the remaining budget amounts and the amounts that have exceeded the budget by type and category over here in this track versus budget chart your track budget performance for the income expenses and Savings in that current month is visualized and highlighted with an exact breakdown by category for the income the expenses and the Savings in these beautiful circular charts to analyze your budget performance for a completed month you can select that month's name in this period drop down and then conduct a conclusive evaluation of where you have under or over performed against the expectations from your budget plan on a higher level you can take a look at the total income expenses and savings numbers and then dive deeper to identify which categories have been the driving factors into one or the other direction if you want to analyze the individual transactions for a specific category in a given period to better understand the overall category performance you can display the budget tracking sheet in split view right next to the breakdown then filter down these records to the respective period and category and then simply sort by amount to identify the main contributors back in the dashboard you can also find the savings rate of that completed period up here and in addition you now have the opportunity to analyze the exact distribution of the track categories for the income expenses and savings now that you know that every transaction for that month has been included as we go through the different months you'll notice how the savings rate category distributions and the overall budgeted and tracked amounts can be highly volatile for that reason the total year view provides a way more evened out and thus significant representation of basically every Insight in this dashboard in this total year view any single category can now be evaluated against the period progress even better as the exact day of a single record within a month has way less impact on these percentage numbers when compared to a monthly View while in this total year view most visuals display aggregated numbers over all months the tracked versus budget chart still breaks down the track and budget amounts by month and lets you easily explore the development of income expenses and savings over time for comparing the development of only two types or exploring the development of one specific type this chart allows you to fully customize which types to display with a few simple clicks in addition for a pure focus on the tracked amounts only you can hide the light-colored budget values at any time with the tracked amounts for every category aggregated over the total year these category distribution charts now provide even more meaningful insights about the composition of your money flows since one time and periodical effects within single categories are getting leveled out over a longer period of time and eventually this totally a view is also the one view that gives you the most significant indication about your actual savings rate because while this savings rate might heavily fluctuate between single months the savings rate over the total year provides an accumulated and leveled out feedback about your long-term savings performance the whole template is just an amazing tool for setting up tracking and analyzing your personal budget it is created with a focus on lightweight design outstanding user experience and fast performance we're gonna build the whole ultimate Excel personal budget with all these amazing features step by step the final template file is available for download on Excel find.com link for that is in the description and now without further Ado let's get right into it as the initial step we're gonna build the budget planning worksheet in total the whole template will have six worksheets which we have already created as empty worksheets with header only besides the budget planning sheet there are two other main sheets budget tracking and budget dashboard that there are two supporting worksheets for calculations and drop-down data which will be hidden at the end and the settings worksheet which will allow the user to set the most important parameters for the template and this is where we're going to start off by creating an input field allowing the user to set the most crucial parameter of the template the starting year of your budget for this input field let's create a new input section by merging these six cells together for a section header aligning the text to the middle setting the fill to this dark blue and the text to White and bold this section will be for the general settings that concern the whole template and to set the section borders let's select the intended content range and click on more borders to open this format cells window we set the Border color to this mid light gray and add a left bottom and right border to the selected range as we want to use the left and right column as padding we're going to reduce their size and in return increase the size of the content columns the label of our only input field here will be starting here and we set it to bold and for the input field itself we set the fill color to this light blue reduce the font size and align the text to the middle and Center let's enter 2022 as the value for our budget starting year and make this Field's value globally accessible through the name starting underscore year for those of you unfamiliar with defining names in this upper left Name Bar every time we Define a name for a cell or range in this upper left Name Bar the cell or range will be referenceable in the whole workbook through that name and can always be checked edited or deleted in this name manager before we make use of that starting year value let's not forget to add a short description for this input field in column G which we will make a lot wider to fit in some text for this description let's reduce the font size to 9 set the font color to this dark gray and also make it italic the description text itself should be brief and on point letting you and potentially other users know what how and when to enter or change values here with this setup let's jump over to the budget planning sheet and create a header bar for the first year in this budget plan going from E5 to Q5 covering a total of 13 columns it's 13 columns because we need 12 for the months in a year and one for the total year let's merge these cells align the text to the middle make it bold and also adjust the height of that row as the value for this header bar we're going to reference the starting year that we have just defined in the settings sheet the two rows below that header bar is where we're gonna summarize the budget to make sure that the budgeted income is in balance with the budgeted expenses and savings let's open the advanced border options in the format cells window set the color to this mid light gray and this time we're going to add every horizontal and vertical border so that every individual cell is surrounded by a border as the next step we increase the size of column C which will be a super crucial column for the whole template as this column is where we will be able to Define our custom categories that then later will be made available in both the budget tracking and the budget dashboard worksheet before we start setting up the budget input section for the income let's quickly add a small reminder that this starting year is supposed to be defined in the settings worksheet for the income section now we start by entering income as the header in cell C9 and then in the rows below we add placeholder values like enter income category for all the potential custom income categories the exact number of placeholders here doesn't matter as later only those rows with entered categories will be considered below the placeholders we add one final row with the label total and then we're going to start formatting these values starting with adding an indent to all the values then selecting their header and total cell to make the text bold and eventually for the category cells in between we gonna reduce the font size to 10. after that we select the header row plus a number of category rows and insert a table the key idea of this table is that all the categories that are inside the table will be considered for the budget plan budget tracking and the dashboard and all other category values outside of that table will be ignored and held as reserved for adding more categories in the future so for those category cells inside a table let's enter some example income categories such as employment side Hustler and dividends from a stock portfolio since the default table design is not what we want to have let's create our own custom design by going to the table design tab open the full selection of available table designs and click on new table style at the bottom this opens this new table style window and as you see we're gonna start with a clean white design from scratch let's set the name to income table style and start by formatting the whole table element at first all we want to set on that level is the Border style and here again we go with this mid light gray as the Border color and add all horizontal and vertical borders so that every individual cell has a border around it the second element we want to format is the header row and for this header row we set the fill color to this green tone and the font color to white and bold and that's already it let's click OK and as you see the new custom table style is not automatically applied to our table instead it is now available in the selection of table designs and we have to manually select it eventually we also remove the filter button of that table as sorting these values is something that we don't want to allow even without the filter button it is still recognizable as a table through this small blue shape in the bottom right corner that lets you increase or decrease the table size to make the total row match the Border style of the table let's add a similar border to this one cell and now we can start setting up the budget plan input area for the budget values let's select the cells in this header row and add the same fill and text color as we already have for the table header in addition we reduce the font size to 10 align the text to the middle and center and finally make it bold in those header cells we want to see the respective period which is the month's name from January to December in the first 12 cells but instead of entering this as plain text we want to use this as lookup row to find the correct budget for each period in our dashboard later so what we're going to do is we insert an actual date consisting of the year in the upper header bar here we have to make sure that the row is fixed through a dollar sign for when we copy that header bar down later then for the month we're going to enter the respective month number which is a 1 for January in this First Column and for the day we simply take a one for the first day in that month and that's it now when we autofill this to the next cell you will see that the year reference is lost somehow and that is because we haven't fixed the column through a dollar sign and for a merged cell area you always have to reference the top left cell to get the value we haven't added this dollar side for a good reason as we're gonna add more years on the right at the later point so in this case we now need to manually readjust the reference to E5 and change the month number to 2 for February to make life easier let's just copy this formula and paste it for the remaining months so that we only have to adjust the month number and the reference to the year will always be correct once we have done that for all the 12 months the final column is for the total of that year and for this header cell we're going to reference the year only and also make sure the dollar sign is set only in front of the row the amazing advantage of having the months as full dates in here is that each cell now has two crucial information saved in it which are the month number and also in a dynamic way based on the selected starting year the Year number and both these together can be used later as lookup value to identify the correct budget plan column for the dashboard calculations at the same time we can display this date as its month's name only in a clean and pretty Way by opening the number format options go to custom and enter mmm as the type this will display this date as the three initial characters of the respective month and now it looks clean and beautiful for the value range we start by adding the same border design as we already had for the category table with this mid-like Ray and all the horizontal and vertical border elements and after that let's enter an example number value and select the whole range to adjust the font size and set the number value to the accounting format with no decimal places as the next step we gonna generate some Dynamic positional information about the elements of this table and the total row because for many calculations in this template we will need to know in which Row the table header is in which Row the first and last defined categories are and also in which Row the total for each period is calculated in this budget planning sheet specifically knowing these rows allows us to visually differentiate the rows with defined categories from the placeholder rows using conditional formatting and for the total calculations in each period it helps to ensure that only values are considered if the category label is part of the table let's start by selecting a cell in the table going to the formulas Tab and opening the name manager here you can see that the table that we have inserted got assigned a generic name table 1 and we want to change that name into income so that we can always access the table and its values using a name that makes sense once we have renamed this table we can now reference it with ease in any calculation using that name great and as already indicated there are four positional information we want to know for this category input section the first is the header row second is the row of the first element in the table which I call Monroe and the row of the last element in the table which I call Max row and eventually the total row calculating the header row is quite simple we're gonna use the row function and reference the cell of the table header and as you see Excel automatically fills in a reference to the table header instead of a simple cell reference so even if that table would move down in the worksheet the row will always be calculated correctly right now the row is 9. and for the row of the first and last element let me quickly demonstrate what happens if we apply the row function to the total table content range as you see this returns an array of the same size with the row function applied to each element in the table returning the correct row numbers of 10 to 12. when we enlarge the table to include another element the return array will include the row of that element as well and when we now feed this array into the Min function it will return us the smallest row and thus the row of the first table element the same logic can then be applied to dynamically calculate the row of the last element by using the max function instead so adding two additional elements here will change the max row to 14. and removing them back to 12. eventually for the total row we simply apply the row function to that specific cell and get returned the correct number of 20. as we want to use those four Dynamic row calculations multiple times in many other calculations in this template we gonna create so-called named calculations in the name manager so that we can always reference these calculated rows by a defined name as this is for the income input section we're going to call the header row calculation income header row we leave the scope at workbook so that it can be accessed from any worksheet in the workbook and then we create the row calculation just like I have done in the worksheet before applying the row function to the table header cell in a similar manner we then going to create the income Monroe calculation with the formula Min of row of income and the income Max row calculation with the formula Max of row of income and eventually the income total row calculated as the row of the total label cell great let's close the name manager and test if those named calculations work as intended the income header row is correctly calculated as 9. the income mineral S10 the income Max row as 12 and the income total row as 20. that looks perfect so we can make use of these row calculations the first time for setting up a dynamic conditional formatting that visually highlights those rows with a defined category in order to make all other rows visually move to the background let's set the default font color for all the category rows in that column and also the value input columns to this gray tone and now with the same range selected we can add a conditional formatting rule that uses a formula to determine which cells to format we want to format all the cells for which the row of the cell and here we need to reference the top left cell of the range and remove all the dollar signs to make it a relative reference is smaller equal the income Max row so right now as the income X row is 12 this rule will check for each cell of the range if the row of that cell is smaller or equal to 12 so this will return true for every cell in these first three rows and false for every cell in the rows below and the format we want to apply is a black font color instead of automatic and for the fill of those cells we take this light green let's confirm this and as you see the conditional formatting rule seems to work as it should let's insert some example budget values and as you can see the font color is set to gray once it has no defined category in the table and as soon as we add another income position though it gets automatically included into the table and the conditional formatting expands correctly in the same manner manually reducing the table to those top two elements works just as fine next let's take care of the total calculations in this total row we gonna calculate the total budgeted income per period while in this total column we're going to calculate the total budget over the year per income category as we want to make both stand out as totals let's set the font to be bold the total per category over the year can be calculated quite easily as the sum over those 12 cells in the same row with the range reference being Dynamic without any dollar signs we can autofill this formula to the other rows using the autofill handle for the income total per period however the calculation needs to be a bit more advanced as we want to only consider values from rows with a defined category that means instead of referencing the full range above the total sale in the sum calculation we now need to reference a dynamically calculated range that only covers those highlighted cells the solution to this is a combination of the address and the indirect function the address function takes a row number and a column number as input and returns a cell address in the form of a text the row of the first cell in our dynamic range is the income Min row and the column is the same as the column of the total cell from which this formula is called we can simply enter the column function with no arguments which then Returns the column number of the total cell itself let's hit enter and we see this returns E10 as an absolute cell address in the form of a plain text now to expand this one cell address text to be a range address text we need to add a colon as text and quotation marks and then add another address statement this time with the income Max row as the input as this is the row of the last category element in the table there we go now we have the full range address returned as text and when we expand the table the range address updates accordingly what we now need to transform this text range reference into an actual range reference is the indirect function so we pass the whole expression to the indirect function hit enter and you see it now dynamically references the relevant range and returns a dynamic array of all range values based on the table size to add all these signal values together we eventually feed the statement into the sum function and there we have the final result for the January income total let's autofill this formula to the other total cells in that row and then test all total calculations by expanding those sample values to all months and then adding a new income category as well works perfectly great so we can clean those sample inputs up for now and as the final step hide all those unused placeholder rows which gives us this beautiful condensed view that only covers that you find income categories with this initial section for the income we have created a great foundation for the other two budget plan sections that will be dedicated to expenses and savings for the expenses section let's copy down the income section with one Sparrow between obviously we need to do a few adjustments starting with renaming the table header to expenses also we're going to rename those three sample categories into expense categories like housing utilities and groceries then we change the placeholder value to enter expenses category for all those rows and next let's change the table design by duplicating the income table Style then renaming that duplicate table style to expenses table Style and the only adjustment we need to do is basically changing the fill color of the header row we're gonna go with this red tone as the color for expenses after defining this new table style we still need to manually assign it to this table and just like for the income table we gonna remove the filter button to avoid any Distortion with these category definitions for these period headers then we're gonna align the fill color with the table header color and that's it for the basic adjustments just like for the income section we want to generate those four crucial row calculations so that we always know the position of the header the min max and total row of this expenses section let's open the name manager and start by renaming this new table into expenses after that we can generate the equivalent named row calculations for the expenses section starting with the expenses header row as the row function applied to the table header cell then the expenses Monroe as the Min of the row function applied to the expenses table and in a similar manner the expenses Max row as the max of the row function applied to the expenses table eventually the expenses total row as the row of the total label cell great let's do a quick testing if all those named calculations work properly and as you see all of these return the correct row value now when we click into the category range and open the conditional formatting rules manager we see that the conditional formatting rule has been copied over and refers to the correct range of cells in this expenses section when we change this filter up there to show all the rules in this worksheet we can see that the original rule for the income section is still in place and obviously working correctly what we need to change for this new rule in the expenses section now is adjusting the formula to not compare the row of a cell against the income Max row but instead against the newly defined expenses Max row and of course we also need to adjust the fill color that goes along with this rule to be this light red instead of green let's apply these changes and it works perfectly so we can close that window and put it to the test by expanding the table everything works just fine and now the only thing left to be adjusted for this section is the calculation formula for the totals for those totals per category over the full year no adjustments are required as the relative range references have been transferred over correctly but for the calculation of the total expenses in each period we need to replace the income Min and Max row calculations with these new expensesmen and Max row calculations so that they now reference the correct elements let's autofill that adjusted formula to the other total cells in that row and do a similar check as before by entering some sample values for all the months and adding a new category as you see the calculation perfectly adjusts to always consider the correct range of values for the total calculation that's amazing and that's already it for this expenses section so let's remove those sample values and high dose unused placeholder rows to get the same condensed view as above we're now going to repeat this process one more time to create a saving section so let's copy over the expenses section change the table header to savings and the example categories to emergency fund retirement account and stock portfolio and in addition the placeholder value to enter savings category for all those rows below then we're going to create another duplicate table style that we name savings table Style and the only adjustments to do here is changing the header row fill to another color for the savings this beautiful blue will be the color we choose let's apply this new table Style and of course don't forget to remove the filter button from this table as well the period header will get the same blue fill color as the table header and then we have already reached a point where we're going to rename the table and Define the four named calculations for the key rows of that section in the name manager let's first change the table name to savings and then create a named calculation called savings header row as the row of the table header cell then the savings Min row as the Min value of the row function applied to the savings table and in a similar manner the savings Max Pro as the max of the row function applied to the savings table and eventually the savings total row as the row of the total label cell again a quick testing of all those named calculations is recommended before proceeding and since all four seem to work properly we can proceed with editing the conditional formatting rule for this section again it has been copied over correctly referring all the potential category rows so let's jump into the rules formula and replace the expenses Max row with the savings Max row and of course the fill color for this rule needs to be changed to the appropriate color tone which is a light blue in this case let's confirm and apply these changes and on first side it seems to perfectly work when we close that window and expand the table that first impression is confirmed amazing again for the calculation of totals the formula in the last column does not need any adjustments but the formula in this bottom total row needs to be slightly changed in the form of changing those row references to savings Monroe and savings Max row beautiful let's autofill this formula to the other total cells in that row enter some sample values and change the scope of the table as a quick test since everything works properly as expected we're going to clean this up and also hide those unused placeholder rows now let's take a look at the summary section at the top the way we want to approach budgeting here is called zero-based budgeting which means that every dollar that comes in through one of the income categories needs to be allocated to either expenses or savings so that you're basically gonna make a plan for every disposable Dollar in each period for that reason we want to keep track of the amount that still has to be allocated in each period while setting up our budget plan calculating this amount is as simple as taking the total planned income and subtracting the total plan expenses and savings so if we expect to have an income of 1000 for this first month then the amount to be allocated increases by that amount let's autofill this formula to all the other cells in a row decrease the font size to 9 just like we did for the input sections below and also make the total value for the whole year bold with those 1000 available to be allocated let's allocate half of that to expenses and the other half to a savings category as the whole amount of disposable income is allocated now the planned budget balance is back at zero which is the target state of our zero based budget plan in the upper row of those two we want to display the respective period for each column but it is slightly different way compared to the input sections below as we will not use this row as a lookup row later we don't need to put actual dates in here but instead we can use it to display if we have reached the target state with our budget plan so what I want to do is inserting an if statement that checks if the amount left to be allocated equals zero and if that's the case I want to display the month's name with a check mark as a Unicode character which I gonna put into the description of this tutorial otherwise I simply want a month's name to be displayed without any additional character let's reduce the font size align the text to the middle and center and make it bold just like we did before for the other period headers then we're going to fill this formula to all the other cells in this row but of course as we have hard-coded the month's name here we need to manually adjust it for the other 11 months and the year's total as well that will take a bit of effort I know but it will definitely be worth the result for the year's total header then we're not going to reference the year number as this is already displayed directly one cell above instead here we're going to call this total to not be totally redundant to unleash the full potential of these summary rows let's add a few conditional formatting rules that will visually help us to understand in which state the budget plan for each period is let's start with the case that not a single amount has been entered in any of these three sections for a specific period so the income total the expenses total and the savings total all are zero make sure that each reference has a dollar sign in front of the row but not the column in that case even though the amount left to be allocated is zero the budget plan for that period has not been touched yet and for that reason we want to set the font color to a default mid light gray once we apply that rule we see that only those columns are shown in color once they have underlying values in any of these three input sections the second rule we're going to create covers the case that there is more allocated to expenses and savings than we have in disposable income which means the amount to be allocated is smaller than zero in this case we want to change the font color for both rows to be a warning red let's click OK here and here and now in the rules manager we see that this new rule has been placed at the top but since we want that rule to be checked and applied after the other rule we're gonna move it down and also set a check mark for the stop if true option which makes sure that the rule after that first rule is only checked if that first rule proves to not be applicable eventually with the third rule we want to cover the target state which is that we have values entered in either of these three sections and the amount left to be allocated equals zero since this initial rule with the end statement only lets through those columns for which at least one of the input sections has entered values we only need to ensure that the amount to be allocated equals zero so let's just duplicate that second rule move it right below the initial rule change the formula to E7 equals zero and change the font color to this stunning green tone now as soon as we click apply we see that both the January and the total value are colored in green as they fulfill all the target State criteria of a zero-based budget plan and to improve the performance let's also set the check mark at stop if true for the second rule because there is no need to check the last rule in case the second rule is true with this we have now completed the setup of the budget planning section for this first year let's quickly test the setup by adding 1 000 of disposable income to all the other months as you see all the months are now in color but do not display any check mark as the available amount still needs to be allocated to expenses and savings once we allocate the full amount the color changes to green and the check mark becomes visible indicating that the planned budget is in perfect balance now if we allocate more than available however the amount allocated turns negative and the color changes to Red indicating that this is not a sustainable budget plan for that period let's quickly bring all the periods into perfect balance and just as intended all the periods are now displayed with a check mark and in green color in the summary section beautiful now we can remove all the input values and change to a more macro level view because we don't want to use this personal budget template for only one year we want to make this a multi-year template and thus we need to find a way to display the budget plan for one year in a compressed way and then add similar budget planning sections for the subsequent years to display the budget plan for this year in a compressed way we can select all the month columns then go to the data Tab and click on that group button this will allow us to collapse all the monthly budget plan information while the yearly totals are still visible let's export it again and decrease the size of column r as we want to use this as a separator column between this year's and the next year section then we're going to select column e to R and use the autofill handle to add another section for the next year instead of referencing the starting year in this top header cell we now going to reference the previous year's value and add 1 to it that not only updates the year number in the top header cell but also the underlying year values in all those period headers of the input sections when we open the conditional formatting rules manager and make it display all the rules in this worksheet we can make another great discovery because by using this autofill option to create the second year the conditional formatting rules have not been duplicated but instead the new ranges have been automatically added to the already existing rules which heavily improves the worksheet performance and the manageability of those rules in general based on the second here section we can now add as many additional years as we wish for this template we're going to go with a total number of 10 years so with 2022 set as the initial year the last year should be 2031. perfect of course we don't want to have all these years expanded so let's collapse all the years by selecting all these columns going to the data Tab and clicking on that hide details button which collapses all the groups finally to ensure that the categories are always visible even when we scroll to the right with multiple sections expanded let's select column e go to the view Tab and use the freeze panes option which makes every column to the left of the selected column always stay in place when we scroll to the right awesome that's the complete setup for this budget planning worksheet so all that's left to do here is entering some generic sample data for this first year so that we have something to work with especially in the budget dashboard later for the income section we're gonna go with these three already defined categories for the employment category let's assume at the start of the year the net salary from our job after taxes is 3500. but we already know that a few months into the year we'll get a raise so that we end up with a thousand more in net disposable income from that job changes in salary or income are obviously part of real life and that's the great thing about the way we have set up this budget plan it allows you to accurately represent each month individually for the side hustle let's say we expect to earn about 1 000 each month in net income after putting away payable taxes and eventually for the dividend payments from our stock portfolio we can look up which company plans to pay what dividend in which month of the year so here we're going to plan the income for each month individually and for Simplicity let's assume the dividend payment amounts repeat every three months once we have entered the expected income for all the defined categories the total row at the bottom accurately sums up those relevant positions for each period while the total year column on the right provides a perfect overview over all months per category as we have not allocated any of the income to expenses or savings so far the amount will be allocated in each month up here is exactly equal to the budgeted income let's start planning the expenses for the year and at first we need a few more expenses categories so let's unhide these available placeholder rows the expenses section is probably the one section that in general requires the most number of different categories so let me show you how you can easily add more potential input rows to a section simply select a number of unused category rows right click on the row number and click on insert this will insert new empty rows on top of the selection and by selecting the row above and dragging down the autofill handle you can add the placeholder values and the total formula in the year total column to these new rows just make sure that these newly inserted rows are somewhere in between the other rows so that they are automatically included in the conditional formatting rules range in addition to the already defined expense categories housing utilities and groceries let's also add a few more the level of granularity you choose for these expenses categories is of course totally up to you still I want to recommend to rather use higher level categories and not to get too granular as this could negatively impact the interpretability of your data in the budget dashboard later as a quick side note for those of you who want to consider loan payments in their budget as a rule of thumb I think it makes sense to include your minimum loan payments in the expense section because this is something you have to pay to not get into trouble with the lender and anything beyond those minimum payments like for example extra payments would rather go into the savings section as those are the amounts that heavily contribute to what's actually improving your net worth with that being said let's hide those unused rows and set up the budget numbers for these expense categories for the first four categories let's plan a consistent amount so we can simply autofill these numbers to all the periods for the insurances however we're gonna assume that here we have to do quarterly payments that means there are always two months with no payment at all and one month that covers the whole quarter that's another great example how this budget plan right here enables you to set the right expectations in each month because with a simple budget that's reused every month we would need to budget in monthly average of 50 each month and then when tracking the actual payments we would fall below that amount in two of the months and exceed that amount massively in the month of the actual payment now for the next three categories we again want to plan a constant amount each month which we can easily do by autofilling these numbers to all subsequent periods and the last category then combines two types of expenses fun which refers to more regular expenses and vacation which is a rather irregular expense that means we're gonna plan a base level amount for each month in which we don't plan to be on vacation and then for April for example that budgeted amount increases for the first vacation putting the total budgeted expenses in that month to a way higher level than usual then another three months where we're gonna plan the base amount and then in August we plan a second even larger vacation pushing the total budgeted amount in that month to over four thousand and eventually after another three months of base level for December we budget twice the base level to account for everything that is related to Christmas celebrations this yields as a plan for our expenses for every month individually based on how we actually plan and expect our life to be throughout the year for every month we are able to set the expectations as realistic as possible and more importantly the top section up here now tells us exactly how much we will be able to save based on the planned income and expenses in each individual period and for that reason enables us to set up a sustainable and realistic allocation of the remaining money to our custom savings categories we have already set up a few important savings categories with emergency fund retirement account and stock portfolio but now let's also add a sinking fund for a future down payment so this is an actually pure savings position where we're gonna save towards a specific future investment or transaction and also a sinking fund for other future purchases it's important to understand that these syncing funds don't necessarily have to be tied to individual savings accounts it's also possible that they only exist on paper or in this case on the spreadsheet and actually are located in one mutual savings account for planning your allocations to the different savings categories you should strictly plan according to your priorities let's assume those savings categories are already ordered by priority and we still need three thousand dollars to reach the target value for our emergency fund then we can take a look at the available amounts for the first months see that we can afford to allocate 1000 each month to the emergency fund for the first three months and after that we can budget that position as zero for the rest of the year next on the list is the retirement account and here we probably prefer a constant monthly contribution and an amount that can easily be covered each month would be for example four hundred dollars so let's set that amount for each month of the year next we also want to plan regular contributions to our stock portfolio but the amount left to be allocated for those three months is pretty low so we decide to allocate the minimum amount of 150 in those first three months which makes us hit the budgeting Target of zero for March and as we want to start saving towards the down payment not before we can make significant contributions to that position we gonna allocate the remaining amounts to the sinking fund rest for these periods due to the increase in salary and the no longer required contributions towards the emergency fund starting in April the amount left to be allocated for those three savings categories has taken a huge and sustainable jump that gives us more room to increase the monthly amount that goes into our stock portfolio and as we have at least 1 000 available in each of these remaining months we can easily cover an amount of 750 for this and all subsequent periods and for the remaining amount we decide to put the full amount minus 100 into the sinking fund for the down payment so that we have one hundred dollars to allocate to the other sinking fund for all these months as I already mentioned this is just a random example but the important aspect here is that this budget plan now perfectly fulfills the requirements of a zero-based budget as the budgeted income versus the budgeted expenses and savings are perfectly balanced and we have a plan for every disposable dollar as the next step we're going to set up the budget tracking worksheet and this is where we're going to keep track of our actual income expenses and savings allocations and the budget planning worksheet that we have just set up provides a crucial input for this budget tracking sheet in the form of the defined custom categories which we now going to use to accurately categorize each of the actual transactions and we're gonna track these actual transactions in an input table so let's start by defining the table column headers as date type category amount and details then we select those five cells press Ctrl T to insert a table and set the check mark for my table has headers let's enlarge this table to get a better view of the overall table design and also increase the size of those columns as the current Table style doesn't meet our expectations let's again create a custom table Style and we're going to start with the duplicate of the savings table Style for the name we're gonna go with tracking table Style and for the whole table element we then adjust the Border setup by removing those In Between Borders only for the header row we're going to add all horizontal and vertical borders in there and for the fill we select this dark blue for this table style in particular we want to add one additional element which is the first row stripe for which we set the fill color to this light gray and as you see the first and second row stripe basically allow you to Define alternating fill colors for the table content rows and for a transaction tracking table like this this seems to be an appropriate design let's apply this new table Style and then take care of the general text formatting in the table for the header bar we want a text to be aligned to the middle and the left but with a small indent and for the table entries we're going to decrease the general font size to 10. align it to the left and also add a small indent after that let's start setting up the input columns starting with the date column technically you can enter dates in any form you want so for example in this clean and readable format but also month slash day slash year and whenever you enter a value into the cell right below the table the table will automatically be expanded to now included row and applies the defined text formatting as we want to have all dates being displayed in a uniform format independently from which format they have been entered in we're gonna Define a custom and clean date format like this in addition we want to add a data validation to this column to ensure that only date values will be accepted as input for the validation criteria we can select to only allow dates and as we want to allow basically any date let's set this to greater than the smallest available date in Excel which is the 1st of January 1900. in case a value is entered that doesn't match this validation Criterion we want to show a custom error alert that simply tells you what you have done wrong let's confirm this and put it to the test when we enter a validate everything is fine and when we enter a non-accepted value like a text for example Excel will not accept that value and shows our custom error alert perfect the type column is where we're going to choose if the entry is of the type income expenses or savings for the best user experience let's create a drop down list that contains those three options so with the table column selected we open the data validation window and to get a drop down list we have to allow a list of values and you see by default the in-cell drop down option is already activated then in the source field we can type in our three items separated by a comma and as you might see the values we type in exactly match the names of the three category tables that we have defined in the budget planning sheet and that is crucial even with this drop down option activated you can still simply type in values into those cells and when someone types in a value that is not in the list we want to show a custom error alert that points to the fact that only values from the list are allowed let's click ok and test it for the first record as you see we have all three options available and when we select the value it will be written directly into the cell and whenever we type in a value into the cell that is in the list it will be accepted just as fine but as soon as we type in something slightly different that's not defined in the list the error alert is triggered and the input value gets rejected the category input column is where we now gonna dynamically inject the custom categories that we have defined in our budget planning worksheet in this column we want to have a drop down list that depending on which type has been selected in the type column offers all the custom categories of that type that we have previously defined now in the source field we're gonna insert a formula that will make this a dependent drop down list since the values that we can select in the type column perfectly equal the table names that we have set up for the three category tables in the budget planning worksheet we can now apply the indirect function to convert this simple text value from the type column into an actual reference to the respective table content to make this work row by row we have to make sure that this is a relative cell reference without any dollar signs and in case a value is manually typed into the cell that is not contained in the respective category table we also want to show a custom alert similar to the one that we have defined for the type column great let's take a look at the first row and as you see since we have selected income for the type the category drop down automatically contains all the defined categories from the income category table let's select a value and increase the size of that column so that all the categories will be displayed in full length with expenses selected as type the category drop down now contains all the defined expense categories and eventually for the savings type we get all the defined categories from the savings category table that is already pretty amazing and it ensures that late in the dashboard the budget plan and track values can be perfectly matched together one drawback of drop-down lists in Excel is that once a value has passed the data validation and is written into the cell the data validation will not be repeated anymore when the underlying list of allowed values changes if we for example change the type to expenses for this first record this updates the available drop-down list to now show all the expenses categories but it doesn't affect the value that is already written into the cell as long as we don't actively override it with a value from the updated list the same is the case if we would delete or rename a category in the budget planning sheet there is no automatic indication that the value that is already written in there might be outdated and needs to be replaced so to solve this issue I want to add a conditional formatting rule to this category column that checks if we get an N A error which basically means no such value found when we try to look up the category value in the category table that has the same name as the selected type for the lookup we use the match function and provide the category value as the lookup value then a reference to the relevant category table as the lookup array and as the final argument is zero which means it has to be an exact match now if that category value written into this cell right now is not found in the category table of the selected type the match function will return an N A error and thus the isna function would return true in that case we want to set the font color to a warning red indicating that the category value is no longer part of the relevant category list and probably needs to be replaced with another category and as you see the first records category is now colored in red as employment is not found in the list of defined expense categories however once we change the type back to income the red color disappears as now both the type and category fit together again this is just a clean and user-friendly way to ensure the consistency of type and category in this worksheet at any time the next column is the amount column where we're going to input the actual value of the transaction so let's start by entering some example values so that we can properly set up the number formatting and as the general number format we're going to apply the accounting format but with the zero decimal places of course you're always free to display the two decimal places if you prefer that way but without the decimal places the focus is more on the actually relevant digits before the comma just like for the other columns we also want to apply data validation to ensure that only valid values in this case only numbers can be entered into this column the only suitable option from this allow section would be decimals but since we don't want to specify any further conditions for the value range we're going to choose custom instead and enter the is number formula and inside that function we're going to reference the first cell as a relative reference without any dollar signs this will absolutely do the job for us and in case this formula returns false for an input value because it is not a number we want the error alert to show an appropriate custom message once we confirm this data validation setup you see that entering valid numbers is accepted while entering something else like text for example will trigger this custom error message and reject the entered value correctly for an improved user experience we also want to visually differentiate between incoming amounts from income and allocated amounts to other expenses or savings for the allocated amounts the default number formatting aligned to the right is absolutely fine but for the incoming amounts we want the value to be aligned to the left and colored in green and the way we're going to do this you've guessed it right is by adding another conditional formatting rule to this column which checks if the type value in the same row equals income again make sure to make the cell reference relative without any dollar signs so that the rule is correctly applied in each row and if that condition is true let's set the font to a green color and a bold Style and then change the number format to the number category and make it look exactly like the custom accounting format that we have defined as the default so with zero decimal places a comma as a thousand separator and brackets instead of minus for negative numbers once we confirm we see how this impacts the values in the amount column just as intended the reason why the values are aligned to different sides is that the general number format follows the general left alignment setting that we have applied to the whole table in the beginning while the accounting format is always automatically aligned to the right the last input column is the details column where we can enter any relevant details for each record let's increase the size of that column and format the potential text input to have a dark gray color while some records are already pretty self-explanatory a good example for a record that would definitely benefit from providing additional details is expenses for clothing here it would make sense to specify what did you buy and where so that at a later point of time you are still able to understand what that track transaction was about for the further development of this template however it is not necessary to have any data in the details column so we're gonna leave it empty during development when applied for tracking your own budget though I definitely recommend to provide details for all the records if possible after we have successfully set up the five input columns let's add an additional column to automatically calculate the balance at each point of time as we want to make this calculation fully robust against any sorting or filtering in that table we not going to rely on the order of these records but instead gonna sum up over all the records of the same or an earlier date no matter where they are located in the table the target value column over which we want to sum up of course is the amount column and for summing up values from a value column that meets certain conditions in other columns a common function to use is the sum product function in which we can simply pass the target value column as the first array and then provide conditional arrays based on other columns to filter out only the relevant amounts for the first conditional array let's open a bracket and check for each row in the date column which of those dates are smaller than or equal to the date value of the record for which you want to calculate the balance you can perfectly see that from within the table you can reference whole columns as the column name in squared brackets and the record of a column in the same row with an additional add symbol this conditional statement will return an array with either a true or false value for every row of the table and is then multiplied with the amount array and summed up through the sum product function however when we now close the statement and hit enter we see that the resulting sum for every row is zero so it seems like that conditional statement doesn't work properly right now the reason for that is simple because within the sum product function if you put in an array that returns Boolean true or false values you have to either multiply it with one so that you get ones and zeros and now the sum is correctly built as you see or alternatively you can tell the sum product function that this is a conditional array that returns Boolean values by adding two dashes as a prefix that has the exact same effect right now this statement isn't fully complete as we are simply adding up the amounts from dates equal or smaller than the date of the record no matter if they are Income which would positively impact the balance or an allocation to expenses and savings which actually should negatively impact the balance as we prefer to not be forced to negate the values when we enter them into the amounts column we somehow need to handle that in the balance formula and the way we can do this is by adding another conditional array to the sum product statement what we want to generate is an array that returns a minus one in case the record is not of the type income and the one in case it is of the type income so let's open a bracket and start by checking for each row if the type column value is unequal income so it's either of the type expenses or savings this will return true only for the expenses and savings rows and therefore we can multiply that with -1 to get an array that has either a zero for income entries and a minus one for expenses and savings entries we still need to turn at zero for income entries into a one because otherwise all the income entries would be ignored and for that we can simply add up this first part with the second statement that checks if the type equals income which then will return a 1 for income entries and a zero for all the other types so both these statements added up together produces an array of either once for income and minus ones for expenses and savings once we hit enter you'll see that the balance is now correctly calculated as income values positively contribute to the balance while allocations to expenses or savings make the balance go down eventually let's apply the same number format to the column as for the amount column and to show you that this balance calculation is fully robust against any sorting or filtering in the table let's filter down to the expenses only and Resort from highest to lowest date as you see the balance for each record stays the same which is pretty amazing in addition to the balance column we gonna add one additional calculated column that we're gonna call effective date let's quickly give both these columns a slightly different design to make them visually differentiate from the input columns a light gray background for the header and a dark gray font color for the whole column seem to be a good choice here now the effective date column will allow us to implement a feature that solves a big real-life issue for many people when tracking their budget and that is late monthly income because for many people it is the reality that they get their paycheck or earnings towards the end of the month and instead of considering that as income for that month which would then make up for all the previous expenses and savings of that same month it makes far more sense to consider this as the disposable income for the next month because logically you want to First earn the money before you spend it or save it and not the other way around now with the current setup if you track income with a date towards the end of a month later in the dashboard it still would be considered as the disposable income for that month in which you have received it instead of the next month which would make far more sense I know as a workaround you could decide to artificially enter the first day of the next month as a date even though that is not the actual date on which you have received that income but why not simply building a feature that does this automatically for you if you activate it to let you decide if you want to activate that feature and on which day of the month you want to start assigning received income to the next month let's jump to the settings worksheet and create a new section we're going to call that section budget tracking and dashboard as the settings are only specifically for those two worksheets and then we're going to insert two rows in here so that we have some space to create a title for that feature that we call late monthly income the first element here will be a drop down field to decide if we want to activate or deactivate that shift of the late income to the next month and as this drop down field is one of multiple sub elements below that title we gonna add a small indent to the label to create a drop down list let's select the input field go to the data Tab and open the data validation window we gonna allow a list that has two values which are active and inactive let's select active for now and then we're going to expand the whole section area to get more space for the settings of this and other features by selecting a range containing the bottom row and multiple rows below then we open the advanced border settings set the Border color to this mid light gray and to remove the current bottom border all we need to do is Click twice on this middle horizontal border until no border is visible and then add a new bottom border and the right and left borders for the whole selected range great for the second input field let's copy down this label and slightly reduce the size of the separator row in this input field we want to set the day in a month starting from which we want to consider received income as the disposable income for the next month let's resize this column to make the label fit in and then we can leave the input field as is and only going to insert a value that somehow makes sense like day 20 in a month to access the value of these settings Fields let's define the global name for that first cell as shift late income status and the second field as shift late income starting day Also let's not forget to add a small description for those settings for that we can merge these three cells together align the text to the top and left and then enter the description text to make the text better fit in we gonna resize the column and also activate the wrap text option for automatic line breaks once we have finished the description there is only one thing I want to do here to improve the general user experience and that is graying out the second field whenever the shift late income field is set to inactive because that then indicates that you first need to activate that option before the value of the second field will have any impact setting this up is quite simple with the second field selected we only need to create a new conditional formatting rule that checks if the shift late income status equals inactive and if that is the case we set the font color to this mid light gray and the cell fill to the slide Gray let's confirm that and now you see once we set this feature to inactive the second field is grayed out and we as a user immediately understand we first need to activate that feature before the value of the second field will have any impact with those settings set up let's jump over to the budget tracking sheet again and create a formula for the effective date column the shift late income feature should be applied if multiple conditions are met which are the type has to be income the shift late income status has to be active and finally of course the day in month of that record state has to be greater equal the defined shift late income starting date if all those conditions are fulfilled then the effective date is set to a new date for which the month is the month of the date Plus 1. and the day is the first of that month and if any of those conditions are not fulfilled then the effective date will simply equal the date of that record let's set the number format for the cell to the custom date format we have already used for the normal date column and since the shift late income feature is currently active and we have set the starting date for that feature to the 20th of each month this income record right here should keep its effective date as the 1st of January even if that income is actually received a few days earlier let's say on the 27th of the previous month as you see the effective date column automatically shifts the date of that income to the next month but only as long as the day in month is at least 20. so if we change that to 19 for example you see the effective date is no longer shifted to the next month just as intended and a day in month greater than 20 again will make the feature go back to work as this feature if activated is expected to be applied only to a small portion of all the records and all other effective dates basically are equal to the untouched date and for that reason provide no new information let's set the default font color to this lighter gray and only make those effective dates stand out for which the date has actually been shifted to the next month to do this we're gonna add a conditional formatting rule to this column that checks for exactly those three conditions so let's copy them from the formula select the whole column open the conditional formatting rules manager and create a new rule for the formula we're gonna paste this and statement that we have just copied and since conditional formatting rules cannot handle those table specific references we are simply going to replace that with a normal relative cell reference to the type value for this first condition and the same for the date value in this third condition and the rest can stay as is now whenever those conditions are fulfilled which means the date is shifted to the first day of the next month we want to set the font color to this dark gray and for the date format we're going to do something really cool we Define the same custom date format as we have already done before but to also indicate that this date has been shifted we can insert a Unicode symbol like this Arrow as a prefix Please be aware that this symbol is not part of the cell value so the cell value itself is still just a date and that is pretty amazing if you ask me because now you can see within a second to which records this feature has been applied let's quickly deactivate that feature as you see now the effect of date column is just a perfect replica of the date column itself and when we activate it again it works beautifully with this we have finished the work on their tracking table now we also want to access the information in their table from the outside and for that we first need to make sure that the table content is accessible through an appropriate name reference so let's go to the formulas Tab and open the name manager when we scroll down here we see the table as table 4 at the bottom and we're going to change that name to tracking now when we select the whole table content you can see the name of the table up there in the name bar the first occasion for which you want to access that table is the creation of some informational tiles on top of the table that display the current date the date of the last record the number of records and the total tracking balance based on the latest record to generate those information let's jump over to the calculations worksheet in which we're going to put in all the background calculation for this template so also the calculations for the dashboard later for a clean setup let's create a simple Header by merging these four cells setting the fill to this dark blue and the text font to White size 9 and a line to the left and middle with a small indent and then we're gonna specify that all the calculations below are for the budget tracking worksheet as the first value we want to calculate today so the current date which technically is quite easy using the today function but as the current date will be used in many other formulas in this template and during the development of the template we want to be able to simulate the current date to be a specific date in the year let's create a new name current date that we can set to whatever date we like during development like for example the 15th of March of 2022 and once we have finished the development and go into the production mode which means we start actually using the template we can replace this with the today function to make it fully dynamic so from now on be aware to always use this current date name instead of the today function whenever required in a formula as these calculations for the budget tracking sheet are for the informational tiles and we will link these values from the calculation sheet into some text boxes it is crucial that we format those values here in the cell exactly how we want them to be displayed in those tiles so for this value we apply our usual custom date format then as the next value we want to calculate the date of the last record in our tracking table which can be calculated as the maximum of the tracking table State column let's copy over the date format from the cell above and based on these two dates we now want to calculate how many days are between the current date and the date of the last record for that we use the days function which expects two inputs first the end date for which we're going to pass the today value and the start date for which we pass the last record value this returns 70 days as we want to display this piece of information as a short text in Brackets behind the date of the last record let's add an if statement to first check if the last record equals zero which would be the case if the tracking table has no records at all and in that case we simply return an empty text string and in case the date of the last record is not zero then we're going to return the calculated value in Brackets supplemented with the words days ago so we get this composed text component for the next tile we want to apply the number of Records in the table which can be easily done by applying the count function to almost any column in the table we're going to take the date column here this will give us four and as an additional information in that tile we also want to display the number of Records in the current year because assuming that you're going to use the template over multiple years that is a valuable information in our sample data for example one of those records is from last year so this calculation is expected to return 3 for this specific setup of Records here again we can make use of the sum product function and this time we only pass one conditional array that returns one for all the rows for which the year of the date in the date column equals the year of the current date and a zero for the others at least if we mark this as a conditional array so those values in The Returned array summed up will yield the value 3 just as expected and just like for the Delta last record we're gonna transform this into a full text string with this information in Brackets and supplement it with the words this year great for the last tile in the budget tracking worksheet we want to display the total tracking balance which basically is the balance value of the record with the latest date as we have already calculated the date of the latest record we can create an index match statement to look up the respective balance value for those of you unfamiliar with index match the index function Returns the value of an array at a given position so since we want to return a value from the balance column of the tracking table let's select this as our return array and then to figure out the correct row number within this array we use the match function which looks up a value in our case the date of the last record in a lookup array in our case the date column of the tracking table as an exact match and then Returns the row number of the match which we can then use as the row number in the index function and as you see it has returned the correct balance of the latest record in case the tracking table has no records this match statement would cause an N A error so no worries we're going to catch that error using the if and a function and return a simple Dash in that case and eventually let's format this balance value as an accounting number with zero decimal places and as the final calculation for this last tile we want to generate a short balance analysis text that depending on whether the balance is positive or negative displays a different content so if the balance value is greater equals zero we want to display behind the balance value of tracked income left to be allocated and in case the balance value is negative we want to display that amount X that has been allocated is not covered by income let's adjust the column size to make this fit in and since now all the calculations for the tiles are prepared let's jump over to the budget tracking worksheet and create those tile elements to create a clean and good looking tile let's insert a rounded rectangle like this slightly adjust the size and then position it in a way that it is aligned with the left border of the tracking table then we right click on it and open the format shape area in which we can now set the fill color to white and the line to this lighter gray and a width of 0.5 we can play around with the roundness as well and for those tiles in particular I prefer a bit of roundness but not too much to make the tile better stand out from the white background we're gonna add some light Shadow with a high level of transparency like 85 percent A reduced size of 95 percent a strong blur of 65 PT then we leave the angle at 90 degree and increase the distance to 10 PT that results in this beautiful tile background that is not super dominant but stands out through its neat design of course this first time needs some content which will be the date of today so let's start with the title for which we insert a text box and enter the tile's name by default a text box has a fill and border and we want to have neither of them so let's add this to no fill and no line in addition we're going to make this text bold and then move it to a good position in the upper left corner of the tile for the date value we can just duplicate that text box and since we now want to dynamically reference the calculation we have set up in the calculations worksheet we go to the formula bar with the text box selected and enter equals and then jump over to the calculation sheet and select the correct cell once we had entered this value from the cell is now visible in the text box and will update anytime the calculation result in the background changes what you need to know about linking text boxes to cells whenever you create or update such a cell reference for a text box Excel for some reason resets the text formatting and in this case the Bold style has been removed so keep that in mind for this value text now we want to increase the font size to 12 and also make it bold after that let's align the title and value text box to the left and do some final adjustments to the positions of both these text boxes until it looks good to us once we are happy with the result let's select all those elements go to the size and properties tab on the right and under properties select the option don't move or size with cells so that whenever any of those columns are resized the proportions of those tile elements stay unaffected great with these tile elements selected let's create a copy and start building the second tile for the date of the last record for this type we need to adjust the size of the text box to make it fit in here and since the content will also be a bit larger let's resize the tile size as well since we know that in the calculation sheet the date of the last record is calculated in the cell right below the current date we can just change that row number in the cell reference to 9. and there we have the calculated date but again as I just told you the text format is reset and we have to change it back to a font size of 12 and a bold Style since we want to add the additional information of the Delta between the current date and the last record let's make this text box a bit smaller and then duplicate it the Delta text content is calculated in one cell below the date of the last record so let's simply change the row to 10 and there we have the dynamic text statement for all secondary information in a tile we want to make the phone size a bit smaller and for that reason we set it to 10.5 and then we're going to position it right behind the date and do some final adjustments to the tile size wonderful we can now use this as the blueprint for the third tile that will display the number of records and will also have one main value text box and a text box with some secondary information let's call this tile number of tracked records change the reference to E11 which then returns a number of four total records in the tracking table again we set the font size to 12 and make it bold for the second text box we change the reference to E12 which then Returns the text statement on how many records are in this year and here we readjust the font size back to 10.5 as the number of Records won't take as much space as the date we can reduce the size of this text box and move that one over to the left maybe bake this one even smaller and it looks like the secondary information text box should be moved down a tiny bit for a better alignment so let's move this one down a tiny bit as well and finally we're gonna adjust the size of the tile and select all those tile elements to create another copy for the last tile in this worksheet this last tile will be displaying the total tracking balance which is found in cell e13 of the calculations worksheet let's set this text form to 12 and bold again and for the secondary information text box which probably will require a bit more space than in the previous tiles we're gonna start by moving it to the right change the cell reference to e14 then readjust the font size to 10.5 and that looks pretty good to me let's quickly test if the size and position of both these text boxes is still appropriate once the balance changes for a smaller balance it still looks okay for a bigger negative balance however the value text box seems to be too small so let's make it a bit larger and maybe also move that secondary text box a tiny bit to the right and adjust the size of the tile perfect that's already it for the setup of those single tiles now we only need to get those four tile components perfectly aligned and to do this let's transform each of them into a group element and select all four groups go to the shape format tab and then align them to the middle to unify the spacings between the tiles let's move the second tile and also the last tile a tiny bit to the right and with this we have completed a setup of this budget tracking worksheet and can now move on to bring it all together in the budget dashboard for the dashboard we're going to start with the initial dashboard setup and for this initial setup we start by creating the header bars for the breakdown and chart section so let's select range C11 to X11 and those first columns from column C to column X will be used as in sheet calculation columns right in front of the visible budget breakdown section so those columns will be hidden later but nonetheless they will get a clean blue header bar and more importantly a light gray sub header bar right below where we're going to insert the individual calculation column names and then the visible budget breakdown section starts in column y and covers a total of six columns as for the section header we want to have one centered section title let's merge these cells together align the text to the middle set the fill to this dark blue and eventually the text font to White size 10 and bold let's insert breakdown as the section title and for now we can hide those in-sheet calculation columns to further set up the visible part of the worksheet for the chart section header let's select the range from af11 to ar11 merge those cells together and apply the same formatting as for the breakdown section header and as the section title we're gonna insert summary one fundamental key of this dashboard will be the option to select exactly which year and which period within that year you want to see as a user and for that we're going to need two drop down lists that we're gonna set up in the top right corner for the drop down header let's merge these two cells together align the text to the middle and make it bold and then we open the Border settings select this mid light gray as color and create a slight border around this range to visually separate it from the surrounding area the title of this first drop down selection will be simply year and then we select the two cells right below merge them together as well align the text to the middle reduce the text size to 10 and set the fill color to the slide green this is where we want to put in the drop down selection for the year and to make this fully obvious let's add a small descriptive text to this three cell range on the left which we also merge then insert the instructional text and to make this visually less dominant we gonna reduce the font size to 9 change the font color to this darker gray and in order to position it a bit closer to the drop down field let's align it to the left and add a small indent and finally we're gonna add an italic Style for the period drop down selection we can then just copy over this range change the title to period and adjust the instructional text great as the next step let's build up the lists for those drop down selections and as these lists will have a lot of potential values that are partly Dynamic let's set them up on a separate worksheet for those drop down data for the year drop down let's create a header with a bold font style a dark blue fill and a white font color and right below we're gonna Define all potential values for the drop down selection for which we slightly decrease the font size and also make them aligned to the middle and Center the first selectable value in that list is not a year number directly but instead a text that says current year and the idea behind this is that whenever this value is selected this will be translated into the current year number in the background calculations which we're going to cover in a minute then all the subsequent values are the 10 year numbers dynamically dependent on the starting date that is defined in the settings so just like in the budget planning sheet the first year is the starting year and all the subsequent years are the previous year plus one for a clean design let's add some borders around that list by opening the advanced Border menu selecting that mid light gray as color and adding a bottom left and right border to that range that looks great so let's just copy over the header for the period drop down data and for these values we also want to slightly reduce the font size and align them to the middle and Center the period drop down Works under the assumption that the specific year is selected in the year drop down and now we need to decide whether we want to take a look at all the months of that year by selecting total year or a specific single month as a specific month you can either select the current month which just like in the current year option will be dynamically translated into the current month number in the background or directly one of the 12 months in a year to save us a few steps we're not going to type in all the 12 months as text here instead we simply Define them as full dates for which the day and year basically don't matter so we just set him to the first day in the month and oo as the year and now what we can do for all those 12 dates is Define a custom date format that simply is M four times this date format displays these dates as the full month name and nothing else and what is amazing about this setup is that we can Now display these values as the full month name in the drop down selection and at the same time we can extract the month number from the selected value simply by applying the month function so let's add some borders around that range just like we did for the other list and now all we need to do on the dashboard sheet is to select the drop down cell open the data validation window select list and reference the defined list in the source field so now we have all those year values available in this drop down selection and then we're going to do the same for the period drop down select list as the data validation method reference the defined period drop down list and now all these values are available in this drop down selection the only adjustment that we need to do is defining the same date format for that cell so that once selected the underlying date is not displayed as this big number but as the respective month with its full name that works perfectly and just as a quick preview to always have the latest month selected at any point of time the dynamic combination of current year and current month will do the job for you because that is the logic that we're going to implement right now to make sense out of and interpret the selected drop down values for all other upcoming formulas that we're going to build in this template let's create a new section for the budget dashboard worksheet and Define two new calculations that we call selected here and selected period for the selected year we want to return a year number for every drop down selection while for the selected period we want to either return the total year expression or a month number let's start with the selected year calculation for which we're going to take a look at what has been selected in this drop down cell since this is a merged range it gives us the whole range but we only need the first of these cells and we first check if that value equals current year because if that's the case we want to return the year of the current date for all other potential values from this drop down list we know that these are year numbers so if the value is not current here we just pass through the cell value to make this interpreted variant of the selected year easily accessible for all the future formulas in this template let's give the cell the global name selected year in the dashboard sheet we can then do a quick test by referencing that name next to the drop down field with the current year selected 2022 is the correctly derived year value and for all the explicit year values the value is just passed through as intended perfect two also derive the correct values from the period drop down selection we first check if the selected value equals total year because that is the only value from that drop down that we basically gonna leave as it is in case the selected value equals current month we gonna return the month number of the current date using the month function and since we know that all the other potential values from that drop down list are explicit dates from which we want to extract the month number we're going to apply the month function to the selected value directly in case the first two conditions are not met let's close that statement and make that calculation result available as selected period just like for the year we can test the functionality of that one by referencing that name next to the period drop down field with the current month selected it correctly returns three as we have set the current date to the 15th of March when we change that value to Total year total year is just passed through to the selected period value and for all explicitly defined months the corresponding month number is perfectly extracted from the underlying dates that is great as these two calculations are you know sort of special as they are the direct interpreters for the drop down Selections in the dashboard let's delimit them from all the following calculations by adding a bottom border in this mid light gray with a dotted Style now whenever we want to display the selected year and period somewhere in the dashboard for example the section headers we can display the selected year value directly but for the selected period it would be prettier to display the month's name instead of the extracted month's number whenever current month or a specific month is selected so let's create a version of that selected period value that is more suitable for presentation in headers and titles that we're going to call selected period display and in the formula we first check if selected period equals total year in which case we again want to just use that value here as well and in all other cases we know that selected period is a month number so we're going to transform that month number into the corresponding month name as a text string by using the text function putting in a compose date for which the year and the day both really don't matter it's only important that the selected period value is used as the month input because we want to print out that full month's name using the mmm date format let's hit enter and you see the month number from selected period is now available as an easy to read text string version which we gonna make accessible as selected period display as the final step for this initial dashboard setup let's make the selected year and period part of these two section headers so we're gonna make this a composed text string starting with breakdown and a Dash and then in case the selected period equals total year we gonna print out the selected year and then the total year in Brackets otherwise we first print out the selected period display value which then has to be a month's name and then after adding in a space the selected year so with the current year and current month selected the section header now correctly displays March 2022. when we select an explicit month the month and the year are displayed in the same way and whenever we select total year the year is printed with total year following in Brackets and of course When selecting another year up there the section header updates as intended as well eventually let's copy that formula over to the summary section header we only need to replace the breakdown with summary and that's it at this point we have completed the initial dashboard setup and can now move on to build the smart breakdown section for the budget breakdown section we're gonna start by unhiding those hidden columns as we need to apply some powerful pre-calculations those pre-calculations have the goal to First retrieve all relevant data which are the relevant items with their respective budgeted and tracked amounts for the selected period and then to automatically sort the category items within each individual type section first by a tracked amount and second by budgeted amount as the first step let's format this gray subheader row where we're going to put in the column names for these pre-calculations and for these column names we want to have a font size of 8 and a text alignment to the center and middle those first 10 columns from column C to L will cover the dynamic retrieval of only the relevant header category and total items from the income expenses and savings type of the budget planning sheet while ignoring the unused placeholder category rows in between and then also the dynamic calculation of the budgeted and tracked amounts for each item based on the selected year and period while the remaining columns m2x are then required to build up an automatic sorting mechanism that sorts the category items within their individual type section first by tractor Mount and second by budgeted amount for a better focus on these first 10 pre-calculation columns let's temporarily hide those remaining calculation columns as well as the visible breakdown section and also adjust the size of the separator column between the sections that way we can now build up those 10 pre-calculation columns while having an easy access to the two drop down selections for year and period before we start let's enter the column names and also visually structure those 10 columns the First Column will be called row ID and this is what we're going to bring in the row number of each relevant item from the budget planning sheet then to always know where the relevant type section starts in the budget planning sheet the second column will make the relevant type header row ID available in each row in here the next four columns will then be binary classification columns to figure out if the respective Row from the budget planning sheet is either a header row a category row a total row or an empty row and based on these information we can then dynamically retrieve the type so either income expenses or savings for each item and of course the item name itself for each category and total item we then gonna calculate the tracked and budgeted amounts based on the selected year and period let's add some borders to visually separate those ID binary classification and actual content columns from each other we will add all column calculations up to row 50 on this sheet however if you need more room as you plan to define a significantly larger number of custom categories you can either cover more rows now or later expand the calculation range using the autofill functionality to add those borders let's open the advanced border options select this mid light gray as the Border color and add a left and right border to each individually selected range as the final preparation step let's define dynamic name references for each column so that all the column formulas can reference the same row value from another column simply by its column name let's select the first cell in the row ID column go to the formulas tab and click on Define name the name for that reference is already proposed as row underscore ID just like in the column header then we want to limit the scope to this budget dashboard worksheet only and for the reference we're gonna leave the dollar sign in front of the column character to have it fixed to this column but make the row relative by removing the dollar sign so that the row always adjusts to the row from which that name is referenced so when we now reference row ID from another column in this first row the row ID value in the same row is referenced just like when we call it from another row or column as you can imagine using this technique of dynamic name references will make all our formulas that somehow rely on other column values way easier to build and understand so let's take the time to set up equivalent Dynamic name references for the other nine columns here as well for the header row ID let's again select the first cell in that column click on Define name the name is already proposed correctly the scope is the budget dashboard worksheet again and then in the reference field we remove the dollar sign in front of the row number then the same for the is header column D is cat column the is total column and the is empty column just as a quick side note if the name is not proposed correctly by Excel which happens from time to time just enter it manually by yourself eventually we also cover the type column the item column the track column and the budget column after taking this effort each of these column values is easily accessible from any other column and with this we have done the best possible preparation to Now quickly build our column calculations we gonna start with the row ID column and in this column we want to dynamically bring in all the relevant row numbers from the items in the budget planning sheet starting with the row number of the income header which is 9 then all the defined income categories which currently are 10 to 12 then skip all the row numbers of those unused placeholder rows and jump straight to the row number of the income total row which at the moment is 20. then with one empty Row in between we want to have the row number of the expenses header as the next row ID then all the row numbers of the expense category items then again skip all the unused placeholder rows and jump straight to the row number of the expense total row and then of course the relevant row numbers for the savings section using the same approach for a better understanding of the formula that we are about to build up in the row ID column let's start by demonstrating how the item column would look like if we brought in all the items using a direct reference to the items in the budget planning sheet in this simple scenario we would directly reference the income header cell and then just use the autofill handle to bring in all the subsequent items from that column as you see that would bring in all the category placeholders as well which we actually don't want to have in here and for that reason we want to find a good way of dynamically cutting them out as a first step let me show you how we can bring in those exact same item values using a row ID as an intermediary because we can easily bring in the corresponding row numbers from the item values into the row ID column by referencing the income header row as a starting point and then for all subsequent values simply take the previous Value Plus 1. that produces this incremental series of row numbers in the row ID column and allows us to return the exact same item values in the item column using the index function for which we only have to provide this column C from the budget planning sheet as a return array and then simply enter the row ID as row number from which the value in this column shall be returned simple as that to catch any potential error let's wrap this into an if Arrow function that returns an empty string if any error occurs and when we hit enter and update all the cells in this column by double clicking the autofill handle you see that this short formula now produces the exact same result but is now fully based and dependent on the values in the row ID column and that means if we now find a robust way of not printing all the row numbers but only the relevant ones in a consecutive way into this row ID column then suddenly only the relevant items will be visible in the item column so let's do this the first row ID will always be the income header row as this defines the Anchor Point for our row ID calculation what we're going to change though is the formula for all the subsequent cells because obviously we will have to make this a bit more sophisticated than this formula in here however what we're going to do in a similar way is that for every cell in here we're gonna take a look at the previous row ID in the cell above and based on what that previous row ID in the seller buff is we then gonna decide what is the next relevant row ID to be displayed in this cell as this formula will be a bit larger let's increase the size of the formula bar and then we start the formula with the first if statement that checks if the previous row ID equals -1 as minus 1 is the row ID that we're going to print if there is no more relevant row number to show and thus If the previous one is -1 the next one will automatically be -1 as well okay if the previous row ID is not -1 then we're gonna go from top to bottom through all the relevant item row numbers in the budget planning sheet that we know as we go from top to bottom the first relevant items after this initial cell with income header row are the first to the last income category item with the first item's row number being equal to the income header row plus one and now to make sure that we always gonna print out the row numbers of all the defined income categories no matter how many there are we can say that as long as the previous row ID is smaller than the income Max row which we have dynamically defined as the row number of the last element in the income category table the row ID of this cell equals the previous row ID Plus 1. and as soon as the previous row ID equals the income Max row we know okay so we have printed all the row numbers for the income category items in the sales Above So as we want to skip the row numbers of the placeholder rows now in this cell we obviously have to return the income total row let's take a quick break here by saying for all the other cases we simply going to return a minus one and update all the subsequent cells with this new formula the magic you can see now is that by dynamically printing only the relevant header category and total row IDs for the income type using those predefined positional row information we have produced a condensed output in the item column that only shows the corresponding relevant items and skips all those empty placeholder rows in between let's continue with this formula and start covering the expanses section as well to separate the income and expenses section from each other we want to make sure that there is always exactly one separator row so when the previous row ID equals the income total row we know that as this row ID we want to have a row ID that represents a separator now intuitively we could just return a -1 but the biggest problem here is that in the next cell below we would not be able to figure out what the next logical row ID is based on the previous value so we need something unique that tells us that this is the separator row between the income and expenses section so let's make that a text string like slash one and then in the next if statement we can refer to that and check if the previous row ID is that slash 1 separator row because then the next logical row ID is obviously the expenses header row as we know that the expenses category items are coming right after we know that as long as the previous row ID is smaller than the expenses Max row we have to Simply return the previous row ID plus one just like we did for the income category items and as soon as the previous row ID is no longer smaller but equals the expenses Max row we want to skip all the placeholder row numbers and directly return the expenses total row let's again set all further row IDs to -1 for now and close the brackets for all those additional if statements to take a quick look at the intermediate results when we add that updated formula to all the subsequent cells in this column it seems to perfectly work starting with a separator row which by the way is an intended result of the if error statement because a row ID like slash 1 obviously cannot be interpreted as an actual row number and then we have the expenses header all the defined category items and finally the total item just as we have envisioned to complete this formula let's also include the saving section with its relevant row IDs and again we start with a separator row between the expenses and saving section which is The Logical next row If the previous row ID equals the expenses total row using the same notation as above we're going to return a unique text string with Slash 2 for the second separator row and then in the next cell we know if the previous row ID equals that slash 2. then this cell needs to have the savings header row as its row ID simple as that then as long as the previous row ID is smaller than the savings Max row we gonna return the previous row ID plus one and as the final part of this formula once the previous row ID equals the savings Max row we print a savings total row as the last relevant row ID and all further cells in this column will be just -1 let's close all these additional if statements hit enter and update all the subsequent cells in this column with this final formula and once we got that huge formula bar out of the way we can see how that setup perfectly plays out and displays exactly the relevant items in the items column I assume you would love to see how this works when we add another category to one of these sections so let's do that and add another income category and as you see it is automatically included in the item column as the row ID formula has now returned the row numbers for four instead of only three categories and all the subsequent row IDs are just printed one cell lower than before that is pretty amazing okay so let's remove that category again and continue with our calculation columns in the budget dashboard sheet one information we want to make available in each row is the row ID of the header of the section to which each respective item belongs that means for all the income items we want a row ID of the income header which is 9 in this case printed in this header row ID column in a similar manner we want to make available the row ID of the expenses header which is 22 right now for all the expense items and of course the same for the saving section now having the respective header row ID available in each row makes sense for two good reasons first we will know for each individual item to which type so other income expenses or savings it belongs simply by looking at the header row ID without leaving the row and second it is an important foundation for calculating the new sorted position of the category items which will be covered in those currently hidden columns in order to create this header row ID formula we first need to generate some more information about the individual items based on the row ID by classifying them into either header category total or empty item in these four columns we want to either have a one for True or a zero for false for each individual row and what's important here is those four columns are mutually exclusive so for each row we want to have exactly one true value in either of these four columns figuring out if a row ID belongs to a header or not is pretty easy because for an item to be a header its row ID has to either equal the income header row the expenses header row or the savings had a row so if any of these three conditions is fulfilled the or statement will return true and to transform this into number we simply multiply it with 1. pushing this formula to all the cells in the column shows us that we get a one exactly where the header items are perfect then figuring out if an item is a category or not based on the row ID alone would need a way longer formula so let's skip that for now and take a look at the is total column first because for this one we can apply the same logic as for the is header column by creating a similar or statement that checks if the row ID either equals the income total row the expenses total row or the savings total row and if any of these conditions is true the statement will return a 1 otherwise a zero and this works just as fine displaying a one for all the total items D is empty formula is slightly different because for this one we're not going to compare the row ID against any predefined names but instead we simply check if the row ID equals either slash 1 Slash 2 R minus 1 because that perfectly identifies all the possible empty rows so we want to return a 1 in case either the row ID equals slash 1. R equals slash two or minus 1. let's add this formula to the whole column and as expected we see a one for both the separator rows and all the rows that go beyond our relevant sections eventually we can now determine the category rows simply by looking at the other three classifications because if an item is not a header not a total and not empty it then has to be a category so for the formula all we need to do is apply the not function to negate an or statement that checks if that item is either a header total or empty only if all these are zero which is the equivalent for false the or statement will also return false and the overall statement will be true and then return a 1. when we push this formula to the whole column we get a one wherever a category item is that's pretty amazing and as a final check we can quickly calculate a check sum over these four columns and this proves that each row has exactly one true value in either of those four columns with these new information generated we can now set up the header row ID formula and we start with an if statement that checks if the current item is a header and if that is true we gonna return the row ID as this is the row ID we are interested in here for this and all the other income items in case it is not a header it is either empty a category or a total item and in case it is a category or total item so not empty then we just grab and return the value of the previous header row ID cell and in case it is empty then we just return a -1 and that's already it let's add that formula to all the cells in that column and as you see now we have the row ID of the respective types header item available for every single item of that type and we can directly make use of that information for adding the correct type name to each relevant item and for this let's just copy the formula from the item column and now instead of returning the item from the row number that is saved as row ID we're going to return the item from the row number saved as header row ID when we add that formula to all the cells of that type column each item now has the name of its corresponding type available in its respective row with all these information available we now want to dynamically calculate the track and budgeted amounts for all the income expenses and savings categories and totals for the tracked amounts we will dynamically extract and filter the data available in the budget tracking worksheet while for the budgeted amounts we will look up the values for the respective items in the budget planning worksheet of course for both these calculations the consideration of the selected year and period are crucial let's start with the tracked amounts and for this we're going to jump over to the budget tracking sheet to get a better understanding on how to approach the filtering and summing up of the relevant amounts for each item from this table so for that let's create some example calculations here right next to the table our Target column of interest is of course the amount column and we can reference all column values by entering equals and then selecting the whole column as you see when we reference a table column from the outside of the table the notation is simply the table name with the column name in squared brackets only referencing this column will return an array containing all the values and if we now want to filter those values for example by a certain category we could either use the sum product function like we did for the balance calculation however I prefer to use the sum product function mainly for in-table calculations or for applying more complex conditional arrays like we did here but in our case now we only going to use Simple conditional arrays for filtering that only check which row of the column equal a certain value like for example which rows of the tables type column equal income this returns a same-sized array with a Boolean true or false value for each row and if we now multiply both these two arrays in a new formula which means the tracking amount column times in Brackets type column equals income this returns a filter version of the amount array with only the income amount values and all the other values are set to zero so calculating the sum over this array Returns the total income when we now change that filter to expenses it will return the total expenses now what you see in this formula is the exact notation we need to apply in our track column so building the sum over the amount column multiplied with one simple conditional array for every filter Criterion we remove this example calculations and start creating the base formula that contains the two filters which are the bare minimum required to be applied in any case and these are filtering down to the respective type enter selected year any more specific filtering to a specific category or a specific month will then later be added as additional filters to this base formula so let's create a base formula as the sum over the tracking amount column and only consider those amounts for which the value in the tracking type column equals the type available in this row and the year of the value and the effective date column so not a date column but the effective date column equals the selected year and then we close that statement so when we add this to all the cells in this column it will now return the total income expenses and savings for the whole selected year in every row of that type as the next step let's make sure that we only return a value for the category and total items and for the header and empty items we actually want to have an empty cell instead for that we add if the item either is a header or empty then return an empty string and only otherwise return that formula that we have just built let's update the whole column and now we only see calculation results for the category and total items based on these amounts we can already see that the filtering to the respective type seems to work but let's also quickly test the selected year filter by selecting the next year as you see for that year nothing is returned as no values have been tracked with an effective date in 2023 but when we adjust one of these records in the tracking table to have an effective date in 2023 then this formula returns nothing for the 2022 income and it is now correctly visible when we select 2023 so that filter works as intended perfect now I have called this formula in here the base formula which is required in any case because technically we have four different cases to cover and thus four different formulas to build that will all contain That Base formula the four different cases result from having two options for the item classification because either it is a total item which then requires no additional filtering condition or it is a specific category which then requires an additional filtering condition and then we have two options for the selected period because either we have selected the total year which again would not require any additional filtering condition or we have selected a specific month which then requires an additional filtering condition so two times two options results in four different cases and to accurately build up the decision structure to figure out which of these cases has to be applied in which row let's temporarily save that base formula in another cell and remove it from in here so that we can build up this structure from scratch as nested if statements with placeholder values for the actual calculations now to build up this decision structure first we're going to check if the item in that row is a total and if that is true we also need to know if the selected period equals total year if both these are true then we need to return the total for the respective type for the total year if the selected period does not equal total year then it is a specific month and we need to calculate the total for the respective type in that specific month let's close that inner if statement and now consider the case that is total is not true then as all the other classifications are basically ruled out the item has to be a category item and we're going to apply the same inner if statement to check what the selected period is in case it equals total year we now have to return the tracked amount for that category over the total year and in the other case the tracked amount for that category in a specific month and that's it let's close that upper level if statement and push that formula to the whole column now we see for every item which calculation would be applied right now with the total year selected the category items all have the category and totally a case returned while the total items return the total and total year calculation when we select a specific month instead we jump to the corresponding month cases for both the category and total items and that means that we have now ensured that always the correct case is applied for every item and period before we now insert the actual calculations for all those four cases let us quickly improve the sample database in the tracking table by adding additional records for a month that is different from January because with only one month in 2022 being represented in the data we would not be able to tell the difference between the results returned for total year and a specific month let's decide for February to be the second month in our sample data for which we're gonna add some income then two records for expenses and one record for savings great now with this additional data added for February let's jump over to the dashboard sheet and replace these placeholders for the four different cases with the actual calculations for that let's copy the base formula and for the first case where we calculated total value for the total year the base formula is all we need since except from these two base filters for type and selected year this case requires no additional filter for either a specific category or a specific month for the second case however the selected period is not total year but a specific month so here we need to calculate the total for a specific month for which we're going to add an additional filter to the base formula we multiply the existing expression by a new conditional array that checks for every Row in the tracking table if the month of the effective date equals the selected period remember that both the result of the month function and the selected period value are month numbers here before we also insert the calculations for the category items let's quickly hit enter push this updated formula to all the column cells and we see that now we have calculated values returned for the totals right now for the total year and by selecting a specific month in this drop down selection this value can be now broken down into the totals for January and February while all these subsequent months display a zero as for these we have no underlying tracked data for the category calculations we also have these cases for either the total year or a specific month for the total year let's again paste this base formula and while the selected year filter is already sufficient for covering the total year selection so we don't need to add an additional filter for the month here we instead need to insert an additional filter for the category so let's add that filter right behind the type filter and the notation is equally simple we're gonna check for every Row in the tracking table if the category value equals the item name available in the respective Row in this sheet and eventually for this last case which filters down to both a specific category and a specific month we're gonna need both additional filters added to the base formula so let's insert the category filter and the month filter to finalize this formula once we hit enter and push the final formula to all the cells in this column we get this beautiful result where we now have the tracked amounts for the individual categories which are then summed up in this total positions for the income expenses and saving section and when we change that selected period from Total year to a single specific month then these amounts are broken down even more specifically amazing let's remove this temporarily stored base formula reduce the size of the formula bar and continue with the budget calculation retrieving the budgeted amounts for each item from the budget planning worksheet is much simpler as here we have only one value for each item in each period that we just need to look up and to look up the correct budget value all we need to know is the row of the respective item which we have already available as row ID and also the correct column based on the selected year and selected period as we have built the specific month headers as actual dates that contain the corresponding year and month number and the total year is available as a single year number we can use our selected year and selected period to build a dynamic lookup value and identify the correct column by using this row as a lookup row let me show you how that works just like for the track column we're going to conduct this priority check if the item is a header or empty and in that case we simply return an empty string and if it passes that test we're going to use the index function to return a value from a given array at a certain position for the return array we select all Columns of that value input range so starting at column e and ending at the last column which is em in our case and since this is a two-dimensional so a multi-row and multi-column array we now need to provide both a row number and a column number to identify the position of a specific cell you got to be aware that the row number and the column number you provide are always measured relatively against the starting row and starting column of the selected return array since our array covers the whole columns and thus starts at the very first row of that sheet we can simply provide the row ID as the row number for the column number though our array starts at column e which means to get the value for January 2022 we need to enter a 1 as this is the first column in the array that we have selected let's close that statement for now and update all the cells in the budget column with that new formula as you see returning the correct January budget for each individual item simply based on the row ID works perfectly but of course we actually want to return the values for the period that is selected up there which is totally you right now and the correct column number to show the totally a budget for each item would be 13 as this is the 13th column of the provided return array for now let's just manually update that number and push it to all the cells in the column and as expected we now see the budget values for the total year but of course changing that column number manually each time is not what we want to do instead we want to take a look at which year and period are currently selected and then use that information to dynamically determine which column represents the selected year and period combination for that let us replace this hard-coded number with the match function which expects a lookup value as the first argument we know that in our budget planning worksheet the total year columns have the year number only as their header value so in case the selected period equals total year we're going to use the selected year as the lookup value otherwise we know that the selected period is a specific month number and we also know that in the planning budget sheet those month headers have full dates with the respective year and month number as their underlying value so in that case we're gonna construct a lookup value as a date using the selected year as year the selected period as month and for they we just provide a one so the first day of that month to match the pattern that we have used in those period headers on the budget planning sheet and with this lookup value being defined we're gonna jump over to the budget planning sheet and select this period header row starting at the same column as the return array we have selected earlier and going all the way to the last column so in our case from E9 to e M9 let's make that an absolute range reference and as we are looking for an exact match let's enter a zero for the last argument of the match function and that is it we only need to close that match statement and hit enter and as soon as this updated formula is added to all the cells in the budget column the budget calculation is now dynamically linked to whatever we have selected as year and period these values are exactly the budget we have defined for January as we can see on the budget planning sheet let's take a look at another example with these numbers budgeted for April for example and these are also perfectly retrieved here of course the track column is all zeros as we haven't tracked any money flows in our tracking table for that month but yeah both the tracked and budget column now perfectly work as they should so the only thing left to do here is formatting them uniformly as accounting numbers with the zero decimal places and at that point we have retrieved all the required data for this breakdown section in a condensed and fully Dynamic way still what's left to do in order to bring this breakdown section to the ultimate level is automatically sorting those category items within each type section by Tracked amount as the first sorting Criterion and budgeted amount as the second sorting Criterion that means for each section we only take a look at the category items and then find a way to reorder them based on the tracked amount and for equal tracked amounts the budgeted amount I know sorting a subset of items within each section while everything else around stays at its place seems like an impossible task at first but once I've showed you how that can be achieved you will be amazed at what is possible in Excel let's unhide those remaining columns and as you can guess from the numbers of columns implementing this in section sorting mechanism is a multi-step process the current order of category items within each section is determined by the order by which we have defined them in the budget planning sheet and this order is then transmitted into this dashboard worksheet through the row ID so what we need to do to reorder them is in a first step ranking the category items within their respective section and then use those ranks to compute the row ID each category item should actually have in a sorted state all the remaining pre-calculation columns here except the last one will be used for the in section ranking and then in the last column we're going to compute the sorted row ID based on which we then print out the sorted category items in the visible breakdown section as a fundamental step for the ranking we need to know for each category item what is the range of items within this item will be ranked and sorted and what is important to mention before we start so far for the whole process of getting these items and data into this dashboard worksheet we were super focused on the row numbers from the budget planning sheet which we have referenced as the row ID in this dashboard sheet but now that we have those sorted data in here and try to rank them we will have to take a look at the row numbers in this sheet to identify the relevant ranking ranges so when we for example take a look at the income range it is an important information for every income category item that it will be sorted within the range going from the row 14 to 16 of this sheet for the expenses section the relevant sorting range currently goes from row 20 to 28 of the sheet and eventually for the savings section the relevant sorting range currently goes from row 32 to 36 in this sheet so to make this information available in every category item Row the first two columns will be sort Min row and sort Max row let's adjust the column size to make these headers fit in add a gray border to visually separate it from what's coming next and finally create Dynamic name references for both columns so that we can instantly use these values in the subsequent calculations so first for the sort Min row scope is still this worksheet only and the row of that reference needs to be related without a dollar sign and then the same for sword Max row perfect to get the row number of the first income category item in this sheet we can still make use of the row ID column as it allows us to apply the match function for looking up the income Min row which equals 10 at the moment in that column as an exact match and that match function then Returns the row number at which that lookup value has been found which is 14 in that case pushing this first draft of the formula to all the cells in this column shows us that this calculation so far is totally independent from the row it is applied in so we need to add a bit of conditional structure around it to make sure it is only used in the rows where it makes sense that means we have to limit it to categories only in a first if statement and since this specific match statement is meant to be for the income category items only let's add a second level if statement that checks if the header row ID equals the income header row which is true for all the items of the income section and in any other case we gonna return an empty string for now when we now update all the cells in this column with this improved formula this information is only returned where it's supposed to be and to also cover the category items of the expenses and saving section let's expand that formula by adding a similar if statement for the expenses section so in case the header row ID is not equal the income header row we then check if it equals the expenses header row in that case we would return this match statement looking for the expenses Monroe and otherwise if that condition is also not fulfilled we eventually cover all category items belonging to the savings section and returning their minimum row number of the relevant sorting range and in all other cases we simply return an empty text string let's update the formula for all the cells in this column and now each category item has that section related information available for the sword Max row we can then simply copy this formula and replace Min by Max in those match statements and there we go now we are able to determine the correct sorting range for any category item without looking at another row let's instantly make use of that row information for ranking the category items by their tracked and budgeted amounts within their respective sections and we're gonna make that ranking a two-step process as for ranking an item we need to provide a range within which we want to rank it that range is obviously different for each section and therefore we need to dynamically compose that range address first using the sort Min and sort Max row and then dynamically pass it to the rank function in the second step thus we're gonna have a tracked range and tracked rank column and in the same manner a butted range and a budget rank column before we insert the required formulas let's add another border separator after these four columns and also Define dynamic name references for the tracked range the tracked rank the budget range and the budget rank column great for composing the range address of the relevant tracked amounts we can use the address function for which we only have to provide the sort Min and sort Max values and the correct column so let's start the formula by making sure that the values are only returned for category items and then build that range address using the address function to First calculate the starting cell address based on the sort mineral and the column number of the tracked name reference which correctly returns k14 for the income category items K20 for the expenses category items and k31 for the savings category items and then we make this arrange address by adding a colon as text and then another address statement this time providing the sword Max row and the column number of the track name reference let's push that updated formula to all the cells in that column and there we have all the relevant range addresses for ranking the tracked amounts in the next column two now rank the amounts in their respective section from highest to lowest we start by focusing on category items only and then use the rank function for which we're gonna provide the tracked value of the respective row as the value to be ranked and the tracked range address transformed into an actual range reference using the indirect function as the range to rank the value in for the last optional argument we're going to provide a zero as you want to rank the values in a descending order and eventually in case the item is not a category item we simply return an empty string and that way we now have dynamically ranked track category amounts within each section for the income section those items are already listed from highest to lowest while for the expense section for example the first rank is found for housing at the top while the item with the second highest amount is not at the correct position just like the third and fourth highest amounts what's important to know is that the rank function will return the same rank for equal amounts as you see here for all those items with rank 5. down there in the savings section we only have two items with a value greater than zero which are adequately ranked first and second for the ranking by budget we can use the exact same formulas and only need to replace the tracked reference by the budget reference so for the budget range let's copy this over and adjust the column to be the budget column now that Returns the correct ranges like l14 to l16 or l20 to l28 and for the budget rank column we again start by focusing on the category items only and then gonna rank the budget value of the respective row against all the values from the range defined as budget range the order will be descending and the default value for all the non-category items is an empty string again once this formula is available in all cells of that column we see that also for the budget the income category items are already listed from highest to lowest while for the expense category items we get a whole nother picture with the highest budget item being listed at the top but the item of the second highest budget currently listed at the bottom and where the first three ranks are all unique you can see how the fourth rank is returned twice for those equivalent budgets and here's another interesting thing to learn about this rank function because it returned Rank 4 twice it does not assign rank 5 to any item but instead assigns rank 6 to the next item to always realign the ranks with the number of ranked items we can make the same observation for rank 6 which is also assigned twice and therefore there is no rank 7. now we only need to combine those two rankings into one combined rank that considers the tracked rank first and for two equal track ranks then considers the budget rank for that we first gonna simply add them together then with the next two columns normalize them and in a last step make sure that every combined rank is unique again we add a separating border to the right side of the section and then Define a dynamic name references for each individual column starting with the column comp rank and remember to set the scope to this sheet only then the comp rank range the comp rank Norm the comp rank Norm run range which is an extremely long name I know but I'm gonna explain that in a minute and then eventually the comp rank unique the combined rank is as simple as concatenating both these ranks together as a text string and transforming them into a number again we focus on category items only and then we use the number value function to transform the text string consisting of the tracked rank as the first part and the budget rank as the second part and then return an empty string for all the non-category items by simply adding the two ranks together with the tracked rank as the first part when we now simply rank these combined numbers to normalize them they will be first ranked by the leading digit representing the tracked Rank and for two items with the same track rank like the 5 for example for these items with a zero track value it will then be ranked by their last digit which represents the budget rank so let's normalize them by first constructing the respective rank range For Those comp rank values in this formula again we just need to replace the value within the column functions and then for all category items we apply the rank function to rank that composed comp rank number within its relevant range but this time since we are basically normalizing descending rank numbers we need to sort from lowest to highest so in an ascending order let's add the default empty string for the other item classes hit enter and push that formula to all the cells in that column and that looks pretty good we now have normalized ranks that cover both the tracked and budget rank in one number and the only thing left to optimize about this combined and normalized rank is we need to make those ranks fully unique to resolve a situation like you see here where the tracked and budgeted value are fully equal and for that reason we get the same combined normalized rank that means for these items we basically need a third ranking Criterion that uniquely differentiates them from each other so what we decide to do is that we're gonna go through these ranks from top to bottom and once a rank appears a second time then we just increment it by one because as we know that subsequent rank number has been automatically skipped by the rank function then in case it appears a third time we increment that rank number by 2 for the fourth time by three and so on that means when looking at the rank in here and Counting how often it already has appeared we are now only interested in this row and the rows above so we're gonna compose what I call a run range that starts only covering the first category items row and increases as we move down so in this Row The Run range will be only this one cell in this Row The Run range will be these two cells and for this last item we finally take a look at the hold range let's copy over this range composition formula first adjust the column references to now reference the comp rank Norm column and then when looking at the row number in this first address statement we can leave it as is as each individual range should start in the first category items row for the second address statement though we gonna make that row number Dynamic by returning the row number of the row that we are currently in and that produces exactly these running range addresses as intended which always reference a range covering the row itself and all the previous category rows of that section and with that being set up we can then create fully unique ranks for the category items by returning the normalized combined rank plus a count of statement that takes a look at this run range and counts how often the rank of the current item appears and since the appearance of that rank in the row itself is counted as well we need to subtract 1 which then means whenever a rank appears the first time this second part of the formula will be 1 minus 1 which equals zero so the original rank stays the same well for the second appearance of the rank the second part will be 2 minus 1 which equals one for the third appearance it will be three minus one which equals two and so on let's add the default empty string for non-category items close that statement and add it to the whole column and as you see for these two appearances of the rank 8 for example the first one only counts the number of appearances in this range which equals 1 and then 1 minus 1 equals 0 and the rank stays as is while for the second appearance this range is considered containing the rank 8 2 times and thus 2 minus 1 equals one and the rank is incremented by one and that way we now have fully unique ranks in each individual section amazing this unique rank is the final result of that whole ranking process so we can temporarily hide all those columns with intermediate ranking steps as mentioned earlier the final column of that pre-calculation section will now be used to compute the sorted row ID which basically is the row ID that each item actually should have in a sorted state and we will use this column as a lookup column so that we can then go through every row take its row ID look it up in the sorted row ID column and then return the respective item which actually should be at that position from the row for which we have find the lookup match let's not forget to add a border to visually separate this column from the upcoming columns and as this column will be used as lookup column only this is the only column in this whole pre-calculation section for which we don't need to define a dynamic name reference calculating the sorted row ID is probably one of the easiest calculations in this whole pre-calculation section because if the item is a category all we need to do is adding up the header row ID and this calculated rank which will cover the exact same range of values as the original category row IDs in each section but in a sorted order and then for all the other non-category items we simply pass through the row ID so that they stay at the exact same position adding this formula to all the cells in that column will give us the sorted row IDs which we can now use to print out all those items in a sorted order in this First Column of the visible breakdown section so the formula here will be if the row is empty just return an empty string otherwise we use an index match statement for which we're going to provide this item column J as the return array and to figure out the row of the item we want to return we use the match function to look up the row ID of the current Row in that sorted row ID lookup column with the letter X and in whatever row we find a match that's the row from which we want to return the item let's close that statement auto fill it to all the cells in that column and now you can see the first result of that multi-step ranking and sorting effort because now every section is printed out but with the category sorted according to the ranks we have calculated take the expenses section for example here housing is the first rank category as it has the highest tracked amount and the second rank goes to the groceries category which is now accordingly visible at the second position so this has been successfully repositioned and again the way we got that value into this row was we first took a look at the default row ID in that row then looked it up in that sorted row ID column to find the row of the item that should be at that position and then return that item name from column J that's the whole formula Magic and of course we now want to return the according tract and budget amounts next to the sorted items so let's jump into the next column and restart that formula in a similar Way by first checking if that row is an empty Row in which case we're going to return an empty string but then instead of directly returning the according tracked amounts we first need to artificially add a header in case the row is a header row because if you look at the raw tracked and budget data over here there is no header value available here unlike for the item values so let's first check if that row is a header row and return an appropriate header like tracked in that case and only otherwise we're gonna return the actual data from the track column using the index match statement this time with the tracked column K as the return array and to identify the correct row we again look up the row ID in the sorted row ID lookup column as an exact match and then close that statement and autofill it to the whole column there we have those tracked amounts with a header and perfectly sorted within each section and a quick comparison of category name and track value with the original data confirms that this is perfectly legit amazing adding the sorted budget amounts in the subsequent column will now be a no-brainer as we can just copy over and reuse the formula from the tracked output column all we need to adjust is the header name and of course the return column in that index match statement to the budget column l once that formula is pushed to the whole column we can perfectly see how for equal track values the items will then get sorted by the budget values as the second Criterion and yeah that is pretty fantastic and now that we have brought all the available data into the visible breakdown section we are at a point where we can just hide those pre-calculation columns and continue on the actually visible part of that worksheet based on these visible tracked and budget columns we now want to calculate the most important kpi values in those remaining Columns of the breakdown section which is which percentage of the defined budget has already been tracked what amount is remaining from the defined budget and finally in case the budget is already exceeded by how much let's start by defining Dynamic name references for both this tracked and budget column and to differentiate them from the original data in the pre-calculation section we're gonna assign the prefix out for output underscore tract set the scope to this worksheet only again and as always make the row relative and then the same for the budget column we're going to call it out underscore budget set the scope to this worksheet and make the row relative as well for the percentage column we start the formula with the same pattern that we have already used for the tracked and budget columns with an if statement to check if the row is an empty row and returning an empty string in that case and then if the row is a header row we gonna return a custom header name let's take percentage completed but in a shortened form to make it fit into the column and eventually calculate the percentage value in case the row is a category or total row as out underscore tract divided by out underscore budget and since we cannot guarantee that the budget is not zero we need to wrap this inside an if Arrow function to catch potential division by zero errors in which case you want to return a simple Dash let's close that statement hit enter and autofill the formula to the whole column and of course we need to change the number format into percentage and there we go right now we are comparing the tracked values for January and February with the budget for the total year so obviously these percentage values are quite low let's select January as the period and there we see not only how the sections automatically get resorted but also that we have already reached 100 for some of those categories in that specific month when we jump to February the percentage completed for income and expenses is much lower while we seem to already have exceeded our budgeted allocation to our stock portfolio by a good amount before we take care of the next column let's also Define a dynamic name reference for this column as we're gonna need this for the conditional formatting rules that we want to add to this section after setting up all the columns we call this out underscore percentage underscore completed again we set the scope to this worksheet and remove the dollar sign to make the row reference relative perfect the next column will calculate the remaining amount of the budget that has not been tracked so far and again we start by setting empty rows to an empty string in this first if statement and then the header rows to a custom header called remaining in the second if statement and for category and total items we then do the actual calculation in which we first gonna check if the budget is still greater than the track value and only if that is the case we're going to return the difference between the budget and track value in case we have exceeded that budget this columns value will be set to zero because any excess of a budget value will be covered in the next column let's close these if statements hit enter and autofill the formula to all the cells in that column and there we have all the remaining amounts as the Delta between the budget and the track values and for the column that calculates the excess of the budget values we can simply refactor this formula in the remaining column by changing the header name to excess then reverse this check to identify cases in which the track value exceeds the budget and in those cases we're gonna return a reverse version of that statement so the tract minus the budget amount to get the excess as an absolute value with the selected period currently being set to the total year it makes total sense that we haven't exceeded any of those budget values at that point when we select January we see that we have a few positions for which we exactly hit the 100 percent and thus both the remaining and excess amount are both zero but for February we have one position down in the savings for which we have tracked a higher amount in our sample data than originally budgeted and consequentially we have the success value displayed as everything works perfectly from a calculation point of view we can now focus on the visual design and formatting of that breakdown section the first change we want to do is slightly reducing the font size for the whole section which leads to a more spaced and straightened out look and in addition let's set the vertical text alignment to the middle then for these five value columns let's set the default horizontal text alignment to Center which is primarily relevant for the headers and the percentage column because for all the absolute amounts in the tract the budget the remaining and the excess column we gonna apply a standardized and clean accounting number format with zero decimal places anyway and as the last easy formatting step we gonna adjust the column sizes with a special focus on applying a uniform size for those four columns containing these absolute amounts and once all that is applied the section already looks so much cleaner to bring this design to a whole nother level let's dynamically format the header and total rows using conditional formatting rules for that let's select the whole section then open the conditional formatting rules manager and then create three individual rules for the income the expenses and the savings header for identifying the income header we're gonna apply the same technique as we have done previously for other calculations so we're gonna apply this rule to the row for which the row ID equals the income header row and the style we want to apply is this beautiful green fill and for the text we're gonna make it bold and give it a white Style just like this let's apply that first rule and that income header appears in an entirely new light let's create the rules for the other two headers simply by duplicating that first rule then adjusting the formula to Now look for the expenses header row and of course change the fill color to this beautiful red tone and there we go eventually let's jump into the last rule in which we're going to look for the savings header row and as the color of choice we again go with this beautiful blue let's confirm and apply those changes and there we have our dynamically formatted header rows for the income expenses and savings section let's set this check mark at stop if true to avoid unnecessary calculations and improve the overall performance of that sheet and now the only thing missing on this full section level is the formatting of the totals and here we only gonna Define one single rule for all the three total rows to identify those total rows we can use the is total information and for the formatting we're gonna set a bold font style and an automatically colored upper border simple as that let's confirm these choices and apply the rule to complement the existing design Simply Beautiful let's move that rule down and also set the check mark here we before we confirm and close that window as now we are done with the general section design the last feature we want to add to this breakdown section are progress bars and the dynamic coloring in the percentage completed column for that let's select the percentage completed column only and open the conditional formatting rules manager here we still see the four rules that we have just defined for the whole section but now with only the percentage completed column selected we can add dedicated data bars for those percentage values and for that let's create a new rule this time leave the rule type selected as this upper type called format old cells based on their values and down below in the format style drop down we can select data bar and for a data bar we now have to define a minimum and maximum value let's go with number here to keep it simple and leave that value at zero and for the maximum we also select a type as number and enter a 1 which is equal to one hundred percent as the value eventually we need to define the fill color of the bar and here we go with this lighter gray so that there is still enough contrast between the percentage number and the data bar let's click OK and apply this newly defined rule currently you see that this data bar rule is applied for both the categories and totals we're going to move it down in the rule hierarchy and Please be aware that with the rule above for these total rows having that stop if true option checked this data bar rule will no longer be applied for the total rows you know I personally prefer that way as it visually differentiates the total Row from the category rows however in case you wish to have these data bars also being displayed for the totals simply remove that check mark from the stop if true option up there with that being said let's close that window and we now perfectly see those data bars which are quite tiny right now as we have selected a total year as our period to view but when we change that to one of the two months for which we have some tracked data we see a different picture with those data bars now being much larger covering up to 100 of the cell you might remember that for February we had that one stock portfolio position for which the tracked amount exceeds the budget and that perfectly shows you that the data bar is limited to 100 even if that percentage value itself goes much higher now to visually highlight the case of reaching or exceeding the 100 percent we're gonna introduce one additional coloring rule for each individual section so let's select this column and open the conditional formatting rules manager and last time for this breakdown section and then start adding the first rule that will be applied when an income item has reached or exceeded the 100 percent for this multiple conditions have to be fulfilled so we start with an end statement that checks if the item is either a category or at least if you wish to also cover those a total then also make sure that the budget is greater than zero then of course the percentage completed needs to be greater equal 1. and finally to limit this rule to the income section only the header row ID of the item needs to equal the income header row if all those four conditions are met then we want to color the cell in that mid light green now when we apply that rule directly without having to stop if true box checked the data bar rule will still be applied and just prints the data bar on top so to fix this let's first move that rule down right above the data bar Rule and more importantly check that box to stop the data bar rule from being applied in that case and after that only this beautiful green fill is visible for this 100 position amazing for the expenses and saving section we can again just duplicate this rule two times move those duplicates down and then adjust the second one to be only valid for items of the expenses section and to color the respective cells in this mid light red tone looks good to me and eventually for the third rule we're gonna adjust the formula to only be valid for items in the savings section and set the fill color for that rule to this mid light blue before we close that window just as a quick reminder in case you wish to have these coloring rules and the data bar rule applied to the total items as well make sure that you uncheck the stop if true option for the formatting rule above that is applied to all the total rows I'm Gonna Leave it checked for now and exclude the total rows from these rules as I think it's a bit cleaner but you know it's totally up to you let's click on OK to close that window what a beautiful result and even better we can now watch The Life impact of new records into this budget tracking sheet by displaying the tracking table and this budget breakdown side by side for that we only need to go to the view tab then open a new window that automatically references the same live file and in there we can then navigate to The Butcher tracking sheet for such a new window our current View and zoom settings are not retained that's a bit unfortunate so we need to First manually set a view settings to no grid lines and potentially adjust the zoom back to our default value but then we can leave the full screen for this window move that window to the right and latch it until only the breakdown section is visible on the left and eventually I recommend to also leave full screen mode for that primary window and make sure it's positioned on the left in the butcher tracking sheet let's make sure that we can see all the columns of that table so we would need to reduce the size of that details column but before we do that let's check if those grouped tile elements will not be affected by changes in the underlying column sizes for that we have to make sure that for all these grouped tile elements these properties are set to don't move or size with cells that way the tiles will stay at their exact position totally independent from changes in the column sizes and we can also scroll a tiny bit to the right so now that looks pretty good and we are ready to add some new records to provide a solid data foundation for creating the dynamic charts in our summary section of the dashboard in the next steps we're gonna enter some records for all the defined categories over multiple months and as we have hard-coded the current date to be the 15th of March it makes sense to enter sample tracking data up until this point as we currently have a few records with an effective date in January and February and we want to start entering data with a focus on January let's filter the effective date column to just display the existing records for January before we start adding new records here for entering those sample records you can just follow me along or if you prefer add whatever sample records you would like to have just make sure that you pretty much cover every defined category and also have some variation in how you under or over perform certain category or total budgets between the different months as we enter these new records into this table we can now instantly see the impact on the percentage completion and the remaining amounts in the breakdown section which is pretty cool and helpful especially when you think about how you can use that later for tracking your real budgets for the current month and then can use that as a monitoring tool to always know how much is there left to spend in each expense category and how much you are still supposed to earn and allocate to your savings once you're done with this first month the breakdown section serves as your final performance evaluation against your originally set up budget now that we want to also add some additional tracking sample data for February let's scroll over in the dashboard sheet to change the selected period to February and in a similar way in the budget tracking sheet we're going to change the effective date filter to now only display the corresponding February records only now these existing records for February don't cover the first few days of the month so why not add a few records right at the beginning of the month as we can just easily bring them to the correct chronological position through sorting the date column at any time and just like we did in January we're gonna provide a paycheck position received at the end of the previous month that will be automatically effective and thus disposable in this month one thing I also want to show you is the fact that even though we have implemented these data validations as drop-down lists you can still type in the values directly obviously only values from the drop down list will be accepted and this Auto completion is only available not before you have already entered the respective value before in that column I think maybe for some of you entering the data directly without leaving the keyboard might be the preferable way from time to time so yeah just do as you prefer now after entering the records that are chronologically before the existing records let's quickly sort this column from oldest to newest and then we can just continue adding the remaining sample data for this month the sample data that we have entered for February here is a good example of how only a small relative deviation from budgeted income and expenses has a tremendous impact on your savings potential not only into the negative Direction like in this case but of course also into the positive direction let's change the selected period in our dashboard to March now for which we have not a single record in our tracking table yet and for that reason right now it is not possible to filter the effective date column to match records only because we first need to provide at least one record for March before we can do that so let's enter the paycheck from employment record here that's effective for March and now we can filter down to that month to get this clean and focused view for our data entry as already mentioned before with our current date set to the 15th of March we only want to enter sample tracking data up until this point you can perfectly observe how the difference in days between the date of today and the date of the last record steadily shrinks as we enter new records later when you track your actual money flows it should be the goal to constantly keep that tracking lag in days as low as possible with the last record having a date that is the same as our current date that tracking lag is now down to zero days so technically these sample tracking data are now perfectly up to date and we can just close that second window and maximize the primary window to full screen mode again before we start with the creation of the charts for the summary section in the dashboard let's quickly jump over to the budget tracking sheet which we have just edited in the other window as you see everything is just as we have left it before we close that second window and what I want to do now is removing that filter and the effective date column then bringing all the records from all the months into chronological order by sorting the date column from oldest to newest and since the overall table has grown a lot and we cannot see all the records at once anymore it makes sense to select the first row here go to the view tab and click that freeze paints option for a smooth scrolling experience with a fixed header wonderful now let's move on to the creation of those beautiful category distribution charts and here we're going to create the chart and chart area for the income type first and then use that as a blueprint for the expenses and savings type on this dashboard sheet we start outlining the chart area that we want to cover six columns and 12 rows for the outline we're gonna open the advanced border options select this mid light gray as the Border color and then add a border at the top bottom left and right of the selected area in addition that chart area also needs a title for which you're gonna insert a text box and then type in income categories and in Brackets tracked as these distribution charts will be focused on the tracked data only let's format a text field by setting the font style to bold then right click on the text box and open the format shape settings to remove both the fill and the line around that box then we make it a bit smaller and highlight the income part of that title to make it stand out through a different font color like this beautiful green eventually let's position the text box within that area somewhere in the top left with some distance to both those Border Lines and then close the settings area on the right and focus on the creation of the actual chart itself and for that we jump over to the calculation sheet make sure that we leave a little bit of space for the calculations that we're gonna put in here later for the dashboard kpi tiles so down here in row 32 we gonna create a section bar for these chart calculations by adding a gray top and bottom borderline and then noting down that this section will contain the chart calculations for the category distribution charts let's add a little bit of formatting by changing the font color and making that first part bold and with one empty row between we can then start building up the chart data to create a dynamic data foundation for such a circular category distribution chart we need to Define three columns as we want to display the track categories sorted from highest to lowest amount the First Column will contain the ranked numbers within their type in this case the type is income then the second column will contain the respective category names and the third column will contain the amounts tracked for these categories let's adjust the size of that amount column and then quickly format these column headers by setting the font style to bold and adding a simple bottom border since we want to display a maximum of six positions in the chart with the last position being a collective position that covers all the categories that haven't made it into the top five we gonna add in rank numbers from 1 to 6 in that first column and then with this last row selected open the advanced border options and add a double lined border at the bottom to separated chart data from the totals calculation and the dotted line at the top to separate its Collective position from the single categories that are ranked top 5. below that doubled line we then add the label total and make both these cells textile bold Also let's select this whole section and set the text alignment to Center and that is it for this General formatting perfect for the initial chart setup we're going to work with dummy data as this will allow us to ensure that every data point will be displayed in the chart with a big enough size and thus can be formatted adequately as dummy data for the category labels we insert income 1 to 5. and for the last one other and for the amounts we add the same amount of 1000 for each position so that every data point will be displayed in the chart with a big enough size for the total amount we then simply add 6 000 for now and eventually set the number format for those values to accounting with zero decimal places great as the data foundation for the expenses and savings chart requires a similar setup let's quickly copy this over here with one empty column in between then we're gonna copy those three columns but only to apply the same column sizes through choosing the paste special option and then selecting column widths the only values that we're going to change here are this First Column header to expenses and the category dummy labels which we can replace all at once by selecting them then using the keyboard shortcut Ctrl H and then entering income as the value to find and expands as the value to replace it with click replace all perfect and one last thing that we're going to change is one of these dummy amounts to another number this serves as a simple validation measure for us so that later we can automatically differentiate by the numbers which range of amounts we have currently referenced and consequently let's also adjust the total amount finally let's copy over that expenses chart data section in order to also set up the data foundation for the savings chart again we copy those columns to then paste special the column sizes then just like before replace the First Column header with savings now then change the dummy category labels to now contain saving instead of expense and as the last step change one of these six amounts and the total amount to make that set of amounts uniquely differentiable when we reference it with this data sections set up let's create the first chart for the income section for that we select the category and the mount values of those six positions then go to the insert tab and right there we select the donut chart type when hovering over the single data points we see that the first data point starts at 12 o'clock and all the subsequent data points then just follow in a clockwise Direction let's remove all the other chart elements and also format the chart area to have no fill and no borderline and then we're going to color these individual elements in different shades of green for these income categories for the first and highest rank category we're going to choose the dark shade of that green like this and then for all the subsequent data points we gradually assign lighter shades of green so this one for the second rank category than this mid light green the even lighter green and for rank 5 the lighter screen that we have in that color palette and for the last position which is the collective position named other we're going to choose a light gray tone to visually differentiate it from all these single positions and that's it for the design of that chart so now we can take it and move it over to the dashboard worksheet by cutting and pasting it in here our first task is to make it fit in there by sizing it down and positioning it approximately where we want to have it in the end however we probably gonna slightly readjust that position once we have also added the dynamic chart Legend to the section as preparation for this Dynamic chart Legend we now need to adjust the column sizes for these six columns covering this chart section to make sure that the title and chart elements are not moved or resized when we change the sizes of the underlying columns let's select both then right click and click on format object to open the settings area on the right and here we go to the size and properties tab in order to set the properties to this time move but don't size with cells the reason we leave that move option activated is because once we have set up this income chart section we want to duplicate and move it down with all its elements to set up the expenses and savings chart sections using the exact same design okay for the column sizes let's start with the separator column and make it a tiny bit wider and then we select the first two columns that are covered by the category distribution chart and increase their size while for the third column we're going to need to drastically reduce the size as this column will contain the colors in the legend then the subsequent column will be used to display the respective category labels for that reason let's increase the size to make the majority of the labels fit in here and eventually the column right next to it will contain the tractor mounts assigned to each category and for that purpose the column size is almost perfect and only needs a slight adjustment the last column will not contain any content and will serve as padding to keep enough space between the content and the right border and then column Al will be used as separator column between this and the following chart section and thus only requires little space great for a uniform appearance for all these chart sections we gonna copy those six columns and then over here right click and paste special the column widths only once that is done we can start building up the dynamic chart Legend and as already mentioned before this tight column right here will contain the colors this wider column will contain the category labels and then this third column will contain the respective tracked amounts and the good news are we have both the labels and the corresponding amounts already set up in the calculation sheet so we can just reference the first category label cell as a relative reference with no dollar signs because this will allow us to add the references to all the other labels and amounts by using this autofill handle with all these cells selected let's reduce their font size to 9. and then for the category labels we're gonna add a little intent to create some spacing between the labels and the colors that we're going to add on the left in a second then for the tracked amounts we set the number format to the usual accounting format with zero decimal places and finally to make the total row stand out we set the font style to bold and also add a dotted upper borderline that looks pretty good so let's continue with the Latin colors in these cells that will correspond to the colors that we have used in the donut chart as we only want to display a Latin color for those positions that are visible in the chart so for those categories with a tracked amount greater than zero We're Not Gonna directly set the fill of that cell but instead make it conditionally based on if the category label right next to it is empty or not because later when we replace those dummy values with actual calculations only the categories with a tracked amount greater than zero will be displayed as non-empty cells here so with this first color cell selected we're gonna create a new conditional formatting rule that will only be applied in case the category label right next to it is non-empty please make sure that the cell reference here is a relative cell reference with no dollar signs if that category label cell is not empty we then want the fill color for this first Legend position to be the same color that we have used for this first rank category in the donut chart which was this darker green and there it is just to show you if we had no income category with a tracked amount greater than zero then even this first position would be empty and in that case the corresponding Legend color would disappear to generate a conditional formatting rules for the other five rows we can now select those six cells then open the conditional formatting rules manager and duplicate this initial rule five times after this we have six rules that currently all apply to this first cell that has the address ah16 to now make the second rule apply to the second cell in row 17 in order to display the color of the second data point let's adjust the row number for this applies to value and since we have defined that cell reference in the rule itself as a relative reference this will automatically adjust to now also reference row 17 once we open it so there is nothing to adjust in that formula and we can directly jump into the format settings to change the fill color to this shade of green that we have used for the second data point in the donor chart perfect we can then repeat that process for the other four rules setting the row number to 18 opening the formatting rule window to set the color to this mid light green and then pressing the apply button for the fourth position the relevant row is 19 and the corresponding fill color is this even lighter green then row 20 with this super light green and finally row 21 that relates to this Collective position named other and for that reason we'll have this light gray as the corresponding fill color once we apply all these defined rules all the legend colors are now available and look super clean to give you a little preview on how it would look with the actual income categories in here as we only have three defined categories with tracked amounts there would only be three positions with non-empty category label cells and thus only the first three Legend colors would be displayed great now with this Dynamic chart Legend setup let's readjust the position of the donut chart to align it with the top edge of the legend and then we can move it horizontally until we find the position we like as I mentioned before this initial setup of the income category distribution chart section will serve as the blueprint for the other two chart sections for expenses and savings so to create the expenses category distribution chart section let's select this whole range and copy it down here with one separator Row in between as you see the reference has got a bit messed up but we're going to fix that in a second first we start by adjusting the title to now contain expenses in a red font color next we're going to change the references for both the chart and the legend area to the expenses data table in the calculation sheet but one thing I recommend to do before you change the underlying data reference of any chart is to open your general Excel options then in the advanced tab scroll down to the Chart section and in there you will find this option called properties follow chart data point for current workbook and this checkbox needs to be unchecked so deactivated as with this option activated Excel resets the complete formatting of a chart whenever it is connected to your new data source with that being said let's change the underlying data for this chart to no longer reference these income positions but these expense positions instead since we have slightly mutated the amounts with the other position being set to 2000 we can instantly verify in our chart that the connection to the new data source was a success and similarly we gonna update the initial cell in this Legend area to now refer to this first ranked expenses category label as a relative reference so we can use the autofill handle again to update all the cell references for the category labels and amounts to restore the original formatting let's just select the respective range in the income chart section activate the format painter tool and apply it to the section down here amazing now the only thing left to do is changing the chart and Legend colors to the usual red color palette for expenses for the chart that means clicking onto every single element and changing the fill color to the corresponding red shade so this darker red for the first data point this main red tone for the second data point then this mid light red the even lighter red and the super light red for the last individual category and we're gonna leave this light gray for the other position just as is then for these latching color cells we select all six cells and open the conditional formatting rules manager since the cell references in the applies to field and the formula itself have been automatically adjusted correctly all we need to update is the fill color for those first five rules to their respective red counterpart so for this first rule again this darker red which once applied is instantly visible in the worksheet then this main rare tone for the second rule this mid light red for the next rule then this even lighter red and finally the slightest of those red tones for the fifth rule just like we did for the chart we're going to leave this light gray color in the last rule as is and that's it for this expenses category distribution chart section now we're going to repeat that whole process one more time for the savings category distribution chart starting with the copy and pasting of the expenses section right over here than adjusting the title to now contain savings with a blue font color then updating the references for the chart and Legend area so for the chart area we're going to change the reference to these savings positions for which we again can verify the correct update of the data source through this mutated amount of 3000 which as you see is now correctly represented in the chart and for the dynamic chart Legend we only need to update this initial cell reference for the first rank category and then can just autofill all the other values and copy over the formatting from one of the other sections eventually we update the chart color palette to these blue tones going from a darker blue to this super light blue with the light gray of the last data point staying as is and similarly for the legend colors we select all six of these cells open the conditional formatting rules manager and since those cell references all have been automatically adjusted again we only need to update the individual fill colors of those rules to their blue counterparts what a beautiful result now that we have set up all three of these sections you might want to re-evaluate and adjust the position of all these donut charts within their respective section and if that's the case you can just select all three of them and use your arrow keys to apply uniform positional adjustments while these dummy data were perfect for setting up the design and formatting of these charts and the dynamic chart Legends we should now move on to replace them with actual data so let's jump over to the calculation sheet select all dummy data in every data table and delete it then we're going to start by setting up the formula for the amounts first as the amounts that we want to bring in here are those tracked in the currently selected period it makes sense to select the period in the dashboard that has tracked amounts available for all three sections so let's change the selected period to February for example and as we want to display the categories in the same sorted order that we have already applied in the breakdown section we can leverage synergies by making use of the already existing pre-calculations in those hidden columns as a lookup column we're going to need a sorted row ID column again and we can hide all these intermediate calculations this column is built on and to bring in the tracked amounts in the correct order this time we will not make use of the row ID as the lookup value but instead build the lookup value based on values that we have available in the data table in the calculation sheet one number that we have available across all worksheets is the header row number of the respective section which is also referenced here and has the value 9 for the income section and this number added together with the ranks from 1 to 5 that we have defined in the data table in the calculation sheet will produce us lookup values that allow us to look up the corresponding Row in this lookup column and then return the tractor Mount from that row so for example nine plus one looked up in here will return that Row from which we then return the tracked amount then 9 plus 2 looked up in here will return the row of the second highest tracked amount and so on so let's jump over and build this index match lookup we start with the index function for which we need to select the return array as the first argument since this formula is for the tracked amounts we jump over to the budget dashboard and select column K that contains all those tracked amounts as an absolute reference with dollar signs to figure out the row from which we want to return the track value we insert a match function for which we then construct the lookup value as the income header row plus whatever rank is specified for that respective position in the data table so for this first position here for example the composed lookup value is nine plus one so 10. and then by looking up that value in this sorted row ID lookup column with the letter x as an exact match we will get the value from the respective row returned and when we autofill that formula to all the five category positions you see that we get a value for the first three positions while the last two produce an N A error as we only have three income categories defined to avoided error in case the number of defined categories is smaller than the ranked number in that First Column let's extend that formula by putting the main statement into an IF function and that if function makes sure that in case that rank number is greater than the number of elements in the table that has the name income then we avoid that n a error by simply returning an empty text string and only if that is not the case we then return the main formula great so that solves this issue now there is one additional requirement that we want the category to fulfill in order to become visible in this Dynamic chart Legend and that is that the track value for that category has to be greater than zero because with the current setup a category would still be displayed in this chart Legend even though nothing has been tracked for that category at all so to exclude our zero value categories as well let's add another if statement inside here that first checks if that return value from the index match formula equals zero in which case we also want to return an empty text string and only if that is not the case we're going to return the actual returned value with that we have completed the formula for returning the greatest five track category amounts and then for cases where we have more than five categories defined the tracked amounts for any category that didn't make it into the top 5 is then collected in this last position instead of directly calculating that Collective position with a complex formula let's make life easy and retrieve the total value first because that value is already available in the pre-calculations of our dashboard worksheet and can be identified by looking up the income total Row in this lookup column and just to remember the income total row name reference is nothing more but a name reference that Returns the row number of their total item here in this initial budget planning sheet so we can basically use the same index match lookup statement that we have used for the top five category amounts and just replace the lookup value with this income total Row in order to get the total track value for all the income categories and that works perfectly having both the total amount and the amounts of the top 5 categories available in here we can now easily calculate the amount of all the other categories by taking this total amount and subtracting the sum of those top five amounts so that value minus the sum of those five now since we have only three income categories defined that other amount is obviously zero but when we're going to transfer these calculations to the expenses section later you will see that the other amount Position will come into effect in cases like here with this position equaling 0 we also want to hide it by returning an empty string instead and only in case it is not a zero we want to return the actual amount that is everything we need for these amount values and now we also want to display the corresponding category labels for these top five and then also the label other for the collective position for retrieving the correct category labels that correspond to these highest tracked amounts we can use the same index match statement as in the amount formula the only thing we need to adjust is the column in the dashboard worksheet from which we're going to return the value and as you see that perfectly Returns the corresponding category labels for that displayed amount the great thing is for this category column we don't need to implement the logical structure for when to display or hide a value as we can just take a look at the corresponding amount value and in case that one is empty we simply return an empty string for the category label as well and only if a tracked amount is returned in that column we know okay so obviously we have a category defined for that Rank and the tracked amount has to be greater than zero so we can return the respective category label using that index match statement without any worries let's add this formula to all the top 5 positions and for the last Collective position down here we gonna apply the same check if that Collective amount is an empty string in which case we're also going to return an empty string and only if that amount cell is not empty we're gonna display the label other amazing that was the whole process for linking this underlying chart data table to the actual tracked data and now we can just copy those seven rows to apply the same formula logic to the expenses chart table as you see those relative cell references inside this worksheet have been perfectly transferred and now reference the correct cells in this expenses data table the only adjustments we need to implement are first in the amount formula replacing this reference to the income table with the expenses table and changing both these name references to expenses header row then in the category formula also changing this name reference to expenses header row and finally after adding that updated formula to the top 5 rows we take a look at the total amount formula for which we also in order to calculate the correct value for this Collective position gonna change this name reference to expenses total row that looks pretty good let's just take a look at the breakdown section in the dashboard to validate if those top five category labels and the amount for all the remaining categories of currently 460 are legit and as you see those top five positions are exactly the same as listed in this breakdown and the tracked amount for all the remaining categories sums up to 460. perfect that means the dynamic data retrieval into this chart data table works perfectly fine and we can continue copying these formulas over to the savings chart data table here of course we're going to implement the same adjustments first in the amount formula updating this table reference to savings and those two name references to savings header row then in the category formula also changing this name reference to savings header row and finally after adding this updated formula to the top 5 rows we adjust the total amount formula to Now look up the savings total Row for retrieving the savings total tracked amount with this updated total value this Collective position here has now correctly vanished and let's quickly validate if those saving amounts match what we can find in the breakdown section and that looks great seems like everything in those chart data tables is now working perfectly fine so we can hide those pre-calculation columns again and take a look at the final state of those category distribution charts these charts now perfectly visualize the actual track data for the selected year and period so when we change the selected period to January the charts update correctly and the same for March in that case of March we have nothing tracked for the savings so this chart area down there is empty as expected and when we select total year we get the most representative visualization of the category distributions as the monthly deviations are getting more balanced out beautiful we have come a long way to create the current state of this interactive budget dashboard which is already super powerful and exciting but let me tell you this final chart that we're going to build next to display the track versus budget amounts over time is probably one of the coolest and most flexible Excel charts I have built so far so let's get straight into it for this chart we start by creating a similar chart section that is separated from its surroundings through some mid-cray Border Lines at the top bottom left and right and of course we also need a chart section title so let's just copy over this text box and replace the current text with tracked versus budget and the budget part in Brackets as we want to be able to completely hide the budget values with one click in this chart now to bring this title into the exact same relative position as the other titles within their sections let's position this text box a bit lower than this text box and a bit further to the right when compared to this text box because that allows us to at first select both these then go to the shape format Tab and click on the Align top option with multiple elements selected this will align all the elements to the vertical position of the highest positioned element and then we select these two and click on the Align left option in order to align the new title text box to the horizontal position of the savings chart title that way this new text box is now placed at the exact same relative position within its section as the other titles and with that initial chart section setup let's jump over to the calculations worksheet and start building the chart calculation section for this final track versus budget chart just like for the other chart calculations we create a similar header with the title chart calculations tracked versus budget chart and below that section header we leave some space of six empty rows that we're gonna need for setting up the interactive checkboxes later and then in row 54 we start creating the header row of the chart data table let's change the text style for this row to bold and centered and then we're going to define a lot of column headers which will explain themselves during the chart data setup the First Column is called month the second column type then we have another column for the month number then some binary columns for if the data row is in focus if the type of the data row is visible or hidden and if the remaining budget in that data row is visible or hidden then of course we need to bring in the tractor Mount and budget value and calculate the Delta between them let's scroll a bit up to optimize some of these column sizes and all the columns that we have defined so far are basically the input columns for the actual data Columns of the chart area those actual data columns will be three data columns for the selected periods that we want to put into Focus and those columns are named in budget remaining and excess and then we're gonna have the same three data columns for those periods that are not in focus let's transfer over the column sizes rename this header to periods not in focus and change the fill color to Gray to visually separate this header from the data let's eventually add a simple bottom border here and in addition since we're only going to need those two and the chart data columns for the initial chart setup with some dummy data let's temporarily hide those in between input columns before we start working on the data since we want to have the data displayed for every month of the selected year let's start by creating the first month as a full date using the date formula for which we passed the selected year as year then one for January as the month and one as the day for each month we're gonna need four rows of which the first three rows will be for the income expenses and savings data bar and row number four will be an empty row that will serve as a visual separator in our chart so let's merge those four cells together in order to display the month for all four rows at once align it to the middle and eventually format this full date to only display the first three letters of this month's name then as already indicated we're gonna add income expenses and savings to specify the type of each row and then we leave the last row empty two separated data for each month from each other let's also add a bottom border below these first four rows and also let's set the general text alignment to Center for setting up the actual chart we again want to make sure that each element is visible in the chart with a big enough size so that we can format it adequately and for that reason we set each cell in those six columns and three rows to the same value of 1000 for example let's change the number format to our usual accounting format with zero decimal places and after that we select all those four rows including this empty separator row and autofill these values to all other months simply by double clicking onto the autofill handle that's the initial data setup that we need for inserting the chart before we insert that chart let's make sure that we have the full range selected then we go to the insert tab open this bar chart section and in there we choose the Stacked column chart that looks like this as a quick explanation in this chart each stacked bar with its six elements represents one row in our data that means this lowest level element in each bar comes from this First Column the second lowest element from the second column and so on so the three elements at the lower part represent the period in Focus data while the upper three elements represent the data for when the period is not in focus the first crucial adjustment we want to make in this chart is heavily reducing the gap between those individual bars which already makes this much more pleasant to look at and instantly makes us as the viewer understand which bars probably belong to the same period as the next step we're gonna get rid of all the unnecessary chart elements like this title which we have already covered in the chart area itself than the child Legend which we also not need once we have applied our super intuitive and self-explanatory coloring and finally the horizontal grid lines in order to reduce the general visual complexity after that we want to bring in the months names to the x-axis with each of these names only displayed once for each group of bars just like we have already set it up in this column but before we add this column to the Chart data I recommend to make sure that the access type is set to text axis because even though these month labels are fundamentally set up as dates we don't want them to be interpreted as dates as this messes up the whole chart so make sure that the access type is set to text axis and after that you can go to the Chart design tab select data and then added these horizontal axis labels by inserting the whole month column you can perfectly see how these values are now taken at their face value exactly how we have formatted them and since in this merged areas in the month column the visible value is only stored in the top cell we can see that each month's name only appears once and has three subsequent empty labels after it let's hit enter and the result speaks for itself this is the exact axis labeling that we intended to create with one label for each respective group of bars let's continue with the coloring of those data elements and this is the first time that we need to talk in detail about the content and the general concept of this chart and especially the meaning of each element Within These different stack bars for each month we have three stack bars the first one represents the income the second one the expenses and the third one the savings in each of these bars we now want to show how big is the amount that we have tracked that is still within our budget then if the tracked amount is still lower than the budget how much of that budget is remaining or alternatively in case the tracked amount exceeds the budget by how much is the budget exceeded depending on whether the month is part of the selected period or not we either going to make use of the lower three elements in that stack bar which will be in color to visually represent the focus or the upper three elements to make them Fade Into the background so you gotta understand that once we link this chart to the actual data each stack bar will only display two of those six elements at the very most well let's start coloring the upper three elements in each buff first as each of these three upper elements represents the data of the periods that are not in focus and for that reason should visually Fade Into the background the best colors to achieve that effect is using different Shades of Gray all of them being relatively light but still distinguishable from each other and what's also important with these passive gray tones we won't visually differentiate between income expenses or savings as there would be no additional value we start with the color for this first element that belongs to the in budget column in our data and as a starting point let's select that lighter gray but we're gonna adjust it a tiny bit to make it even lighter something like this then the element representing the remaining budget should be way lighter so let's jump in here and use the gray tone that we have just customized as a starting point as this allows us now to click on more colors and in this Advanced color window we can now manually modify the color and see exactly how it differentiates from the starting point color reference I think this gray tone sets itself apart sufficiently from the in-budget gray tone so we can click OK and continue with the element that represents the excess of the budget and again we start by setting it to the in budget gray and then customize it to now be darker instead of lighter that looks good to me so we confirm and now we are halfway through this coloring job you might think but unfortunately like the French would say oh contraire because coloring the lower three elements for the periods and focus will take a bit more effort but I guarantee the end result will be totally worth it we start by coloring the lower three elements of all stack bars using the green income color palette so for the in-budget elements let's choose this main green tone then for the remaining elements we again choose a super light shade of the color and eventually for the excess element we go with a darker green tone of course only the first of these three bars in each month actually represents the income while the second bar always represents the expenses and for that reason should actually be using the red color palette and a third bar represents the savings and should actually be using the blue color palette but since in Excel when setting the color for a data series we need to decide between either formatting the whole data series at once or instead only single data points we have now at least set the coloring for all the income bars in each month and only need to individually change the colors for the expenses and savings elements let me show you the correctly colored Target design for each month using this first month as a blueprint for the second bar we set the in budget element to this main red then the remaining element to the slide red and the excess element to this darker red and the same for the savings first this made blue for the in budget element then this light blue for the remaining amount and it is darker blue for the excess amount and this is the exact target design that we want to apply for all the months now so we have to change the colors of six individual elements for each month my personal recommendation for the most efficient procedure here start coloring the same element in all months at first as this makes both the selection of the elements and the intuitive choice of the correct color way simpler so we start with this in budget expenses element and set it to this main rare tone for all the periods then we continue with the remaining expenses element and set the color to the slide red for all the periods after that we finalized the expenses bar by setting the color for the excess element to this darker red for all the periods and then we repeat that process for the savings bars starting with the in budget element for which you set the color to this main blue then the remaining element with this light blue and finally the excess element with this dark blue now that has been a lot of manual work but we have now successfully created the visual foundation for this chart and can now continue with dynamically connecting it to the actual data for this let's delete all those dummy data and just to give you a quick preview of how beautiful this chart will look like when we connect it to real data let's just enter an in budget and the remaining value for income expenses and savings and that gives us this stunning and intuitive visualization of how tracked amounts with some remaining budget will look like and when we assume that this period is not in Focus then the same data would just appear over here instead fade it into the background through these smooth Shades of Gray okay let's delete this again and unhide those columns in between to build up the calculations that produce these results for the actual data as the initial step we will enter calculations into the columns from month number to Delta and as we're gonna use the autofill functionality a lot I recommend to first check in one of the columns if the bottom border of the header row is copied over together with the value which is something we actually don't want to have if you don't experience that behavior you can just move on but in case you face the same issue this is probably caused by the way that this simple bottom border option we used is implemented and we can avoid this Behavior by removing the Border again and then setting it via the advanced border settings it seems this way it is only defined as the bottom border for this row and not automatically as an upper border for the subsequent row as well okay perfect as mentioned before all those columns from the type column to this Delta column will be used for generating the input for the calculations in our six chart data columns for that reason I recommend taking the time to add Dynamic name references for each of these columns as this will make the final formulas in the chart data columns super intuitive and easy to understand so let's do this and start with the type column and to differentiate it from the other name references that we have already defined in our main sheets we add the prefix cc to the name which stands for chart calculation and then underscore type then the scope to this worksheet only and finally make the row reference relative by removing the dollar sign in front of the row number next we Define the name CC month number set a scope to this worksheet and make the row reference relative by removing the dollar sign then we continue with CC in focus for the in Focus column then CC show type for the show type column and CC show remaining budget CC Direct CC budget and eventually CC Delta once we are done every relevant column is accessible in the same row via this Dynamic name references and that will make life a lot easier for the final calculations in those six chart data columns great to prepare us for the retrieval of the correct tracked and budget data for each month we need to extract the month number from the underlying date in the month column and as you might remember for this merged cell range the visible value is always only stored in the top cell so in that first row for the income we can just reference the cell in the same row for the other two rows for expenses and savings flow we need to manually adjust that reference to also refer to the top cell in that merged cell area after setting this up for the first month however we can just select those four cells and double click the autofill handle to automatically generate the month numbers for all the other months when we move to the next column the in Focus state of a month we'll decide about whether that month will be displayed in color or visually move to the background by being displayed in Gray a month should be said to be in Focus whenever either the selected period is total year so every month of that year should be displayed in color or if the selected period is that specific month so let's set up the formula for this as if selected period equals total year then return a 1 no matter what otherwise the selected period is a specific month and we need to check if it equals the month number if that is the case we also going to return a 1 and only if both these conditions are false then we're going to return a zero let's add this to the other two rows and then with all four cells selected let's fill that formula to all other months as the selected period is currently set to be total year this value is 1 for every month when we change the selected period to a specific month however only that month is still set to 1 while all the other months now display is zero so later in our chart data columns we can use this in Focus value as a switch to either display our data in the first three columns or the last three columns the next column that is called show type will also act as a switch in the chart data column calculations to either display or completely hide the data of any of the three types we want to make the visibility of each type controllable via check boxes and to set up these check boxes let's create some labels first up here that say show income show expenses and show savings we format the text to be bold and a bit smaller and we're gonna add a Boolean value like true for each of them also with a slightly reduced font size and all we need to do now in order to convert these Boolean values up there into a switch for each data row that is related to the respective type is starting the formula with one times that respective Boolean value here the relevant Boolean value is to show income one we also make sure that the reference is an absolute reference with dollar signs and that's it we only need to create a similar formula for the other two types so let's enter one times the show expenses Boolean value as an absolute reference and finally for the savings type 1 times the show savings Boolean value as an absolute reference and once this is set up for the first month we can again just select those four cells and push this formula to all the other months and now when we set one of these Boolean values to false the show type value for that type is set to zero for all the months to now bring in some actual check boxes to directly control those Boolean values up there let's go to the developer tab which you can enable in the Excel options in case it is not visible and in there we can click on insert and in the form control section select the checkbox element which we then insert right here let's change the label to income and then right click on it and open the format control window which now allows us to connect this checkbox to this Boolean value when we hit enter the Boolean value is instantly dependent on the checkbox date which means we now have full control over the income rows in that show type column let's duplicate that checkbox twice to create an expenses checkbox that controls the show expenses Boolean value and similarly the savings checkbox to control the show savings Boolean value perfect let's position these check boxes right above the show type column for now and then we can continue with the show remaining budget column the value in this column will also serve as a switch this time to either hide or display the chart data columns for the remaining budget for this let's create another Boolean value up there that has the label show remaining budget that we initially set to true and as this switch will not make any difference between the individual types or months and is the same for all the data rows we can just enter one times that Boolean value make it an absolute reference then autofill it to the other two types and eventually with these four cells selected push it to all the other months and to also make that switch controllable via a checkbox we duplicate one of the existing checkboxes change the label to budget and Link it to the respective Boolean value perfect now the state of the checkbox is instantly available in every data row to retrieve the tracked and butcher data per type for each individual month we're going to take some inspiration from the approach that we have used in this hidden pre-calculation section of the budget dashboard let's first take a look at the formula that we have used to calculate the track values for the category and total items in here we have differentiated between four possible cases all of which use a different combination of conditional array filters applied to The Columns of this tracking table that way we were able to sum up only the relevant amounts in this nested if statement we first checked if the respective item is a total or category item and then in the second level if statement if the selected period is total year or alternatively a specific month for the track value in our track versus budget chart now we are only interested in the total amounts for each type which direct our Focus to these two upper cases and we are not interested in the amount for the total year but instead want to calculate the tracked amount for each respective month that means the second case formula right here is the closest to what we need for the track values in our chart data we only need to adjust some of the filter values so let's copy this over insert it into this first cell of that column and then we go through the different parts of that formula step by step in here we also want to build the sum over the values in the amount column of the tracking table so we leave that first part as is then for filtering down to the correct type we now want to do this based on the type that we have defined in the type column here then as we are interested in the months of the selected year we can leave the next part as is as well and only need to adjust this final month filtering to not be based on the selected period but instead be based on the respective month number available in each row as CC month number and that's it when we add this formula to the expenses and savings rows you see the amounts automatically adjust as each of them is filtered down to the relevant type of each row and in the same way when we now select these four cells and push that formula to all the other months the amounts are filtered down to the respective month numbers and accurately represent the total tracked amounts per type and month for retrieving the budget values per type and month let's also take a look at what we have done for retrieving these values in the pre-calculation section of the dashboard worksheet in the main formula here we use the index function to return a value from a multi-row and multi-column array defined as the columns e to em in the budget planning sheet so basically all these columns to identify the exact cell in that array from which we want to return the value we needed to provide a row and column number for the row number we have provided a row ID which basically represents all the row numbers of the relevant header category and total items in the budget planning sheet for our chart now we are only interested in the total items so here we could easily just provide either the income total Row the expenses total row or the savings total row and to identify the correct column number what we have done in this formula was using the match function to look up either the selected year number if the selected period is total here or otherwise a composed date with the selected year and selected period since these month headers have full dates as their underlying values the lookup row is E9 to em9 and we want to find an exact match now when we think about our chart we are only interested in looking up the column number of each respective month for which we have the month number available and thus can dynamically compose the respective full date that is required that means in this match statement we could now directly use a compose date like this as the lookup value and can basically throw out this whole if statement part before so let's copy over this formula into the first cell of that budget column then we bring this all into the same line and now for the row number argument we can directly reference the total row number of the respective type so the income total Row for this first row and then to identify the correct column we can remove the if statement and directly use the compose date as lookup value to look up the column of the relevant month in that compose State we keep the selected year but then for the month argument we provide the respective month number representing the month in each row when we hit enter this Returns the total income budget value for January which can be confirmed after taking a quick look at the budget planning sheet when we add this formula to the rows for expenses and savings the value stays the same as we haven't adjusted the name reference in the row argument to get the expenses total budget we need to change this to expenses total row and for the savings total budget we need to change this to savings total row and that is it we can just select those four cells and with a double click onto the autofill handle the formulas are successfully pushed to all the other rows and now return the total income expenses and savings budget amount in each individual month that is amazing and allows us to now calculate the Delta between these two values as CC budget minus CC tract let's push this formula to all the data rows and as a final step let's set these columns to have the same accounting number format with zero decimal places and also jump over to the budget dashboard worksheet to hide those pre-calculation columns again wonderful we are now well prepared to build up the calculations in our six chart data columns based on all these available input values so let's get right into it in the in budget column we basically want to display the tracked amount but with the limitation that this value can only get as high as the budgeted amount and whenever the budget is exceeded the excess amount then goes into the access column so to display the tracked amount with the budget amount as the upper limit we can simply return the minimum of CC tract and CC budget because in case the tracked amount is still below the budget then this track amount is returned and in case the tracked amount exceeds the budget then the budget amount is returned as the upper limit so you can see for the total income in January the track value exceeds the budget and for that reason the return value is limited to the budget amount while for the expenses for example the track value is still below the budget amount and thus the track value itself is returned and eventually for the savings the track value exceeds the budget again and thus the return value is limited to the budget amount whenever the track value is below the budget amount which means the Delta value is positive then we want to return the remaining Delta in this remaining column so let's set up an if statement that checks if CC Delta is greater than zero like it is the case for the expenses in January for example and if that's true then we want to return that CC Delta value as the remaining budget amount and otherwise we simply set it to zero as you see this produces a zero for both the income and savings as there is no remaining budget here but for the expenses we have a remaining budget amount of 100 that is now also visible here in the chart in this light red color in a similar way we want to return the delta in the excess column whenever the track value exceeds the budget amount that means if CC Delta is negative so smaller than zero we want to return the absolute value of that CC Delta as the excess amount and otherwise zero this gives us an excess value of 200 for the income and 300 for the savings which are instantly visible in these darker shades of their respective colors in the chart and I don't know how you feel about it but I think this is just a beautiful visualization of so many information at once and if our goal would be a static non-interactive chart we could now just push these formulas to all the other data rows and call it a day as we have the track and budgeted values beautifully visualized for every single type in every month of the selected year but since we want to also build in our Dynamic switch values to make both the coloring Dynamic based on the selected period and also control the visibility of the individual chart elements through our checkboxes let's go one step back and make our existing formulas dependent on these values called in Focus show type and show remaining budget let's quickly talk about the desired effect we want each of these switches to have we want all of those first three column values only be displayed if in focus in that respective row equals one otherwise we want all the values in the first three columns to be zero so all we need to do is multiplying all three formulas by this in Focus value in a similar way we want all column values only be displayed if show type in that respective row equals 1. otherwise we want all the values in all Columns of that row to be zero so again we need to multiply all these formulas by the show type as well and finally we want the remaining column value only be displayed in case show remaining budget in that respective row equals 1. otherwise we want that remaining value to be zero in order to hide the budget so only for the remaining column formula we're going to add in the show remaining budget value as a third factor in front of the existing formula let's start by multiplying the in budget formula by CC in focus NCC show type to only display the resulting value in case both the switch values are set to 1. then we update the other two rows with this adjusted formula let's copy those two factors to also add them into the formula of the remaining column but here we extend that by adding CC show remaining budget as a third factor that also needs to be set to 1 in order to show that value then we add that updated formula to the other two rows as well and finally let's add this initial two factors CC in focus and CC show type to the exact calculation as well and update the other two rows now we can push these upgraded formulas to all the other periods and directly test the functionality of those checkboxes to hide and display the expenses savings or income for each month or generally hiding all the remaining budget values to put a focus on the tracked amounts only that works perfectly and now as the final step to also visualize the periods not in Focus let's just copy over these three formulas from the first row and then simply negate the CC and focus value in all these formulas by putting it into a not function that means whenever a period is not part of the selected period And Thus not in Focus this will now be true right now this whole expression returns 0 for all the rows in all the months as the selected period is total year and for that reason all the periods are in Focus but once we jump over to the budget dashboard worksheet and select a specific month in that period drop down only that specific month has its values returned in the period in Focus columns and is displayed in color while the values of all the other months are now displayed in the period not in Focus columns and for that reason are visualized in these Shades of Gray that is pure Excel chart magic and of course all these formulas still work perfectly hand in hand with those check boxes and we can hide and display elements just like before great as this chart is now fully set up let's move it over to the budget dashboard worksheet and make it fit into this prepared chart section for that let's first right click on it and open the format chart area section on the right in order to set the fill to no fill and the border to no line then we reduce the size and position it somewhere inside this chart section for these y-axis labels I recommend to slightly reduce the font size to something like 7 and set the font color to a darker gray and for these x-axis labels we apply the same font size and color but also make them bold after that we just play around with the position and size of that chart until we are fully satisfied and then we can do the first test of the interplay between this period drop down and the dynamic chart coloring seems like it works perfectly with the totally you selected all the periods are visualized in color while for the current month which is March only the March values are colorized and the same when we switch to any other month amazing the only components we still need to bring in here are the check boxes so let's jump back to the calculation sheet select all of them then cut them here and paste them over here unfortunately the link to the underlying Boolean value in the calculation sheet somehow gets messed up when moving them to another sheet we can easily see that by checking and unchecking them with no impact on our chart at all we can figure out what the problem is when we right click on one of these check boxes and open the format control window as you see the selling is still e49 but it doesn't show any worksheet specification so now this checkbox is just linked to the cell e49 on this dashboard worksheet which obviously makes no sense at all we need to fix this for all the four check Boxes by manually redefining the link to now reference the correct cell in the calculations worksheet again same for the expenses checkbox the savings checkbox and the budget checkbox and with this adjustment we gain back the control over the visibility of the chart elements through these checkboxes as the final step to finish this whole chart section let's move these check boxes close to the Chart into the top right corner of the chart section and to visually separate them from the rest of the chart let's insert a normal rectangle for which we're going to remove the fill and set a line to have a mid light gray color and a width of 0.5 then we adjust the size of the rectangle and also its position until we are fully satisfied and after that let's select all those four checkbox elements set them to a standardized height and width and move them into the rectangle making sure that all of them fit in there then I recommend to open the selection pane to make the selection of those three a bit easier in order to align them to the left and distribute them vertically maybe we should move them a little bit more to the left and finally let's select the budget checkbox and the income checkbox to align them to the top and at that point we have completed this whole track versus budget chart section with these check boxes placed that close to the actual chart hiding and displaying the individual components becomes an even more intuitive experience and allows us to view the tracked and butcher data over time from multiple angles and at different degrees of visual complexity in addition when going through the individual months of the selected year this chart beautifully highlights the relevant insights for that month only and enables us to better understand how the respective month's data can be compared within the bigger context after completing the breakdown and summary section of this dashboard let's add the last missing pieces which are the performance kpi tiles right at the top the goals for these tiles is to provide contextual information and summarizing insights that let us better evaluate and understand our performance in the selected year and period let's build these tile by tile and in the first tile we want to display the selected year and period just like we already do in the section header but visually way more prominent as we have the selected year and selected period values already available as referenceable names we can directly start with creating that tile by copying over one of the existing tiles from the budget tracking sheet and then positioning it right aligned to the left edge of our breakdown section to make the transition between these two worksheets as visually smooth as possible let's try to bring this to the exact same position as this first tile in the budget tracking sheet for that we switch back and forth and slightly adjust the tile position each time until we have achieved the perfect transition without any shifting after that let's decompose this tile to modify its individual components at first we make this tile background element a bit wider like this as we're going to need a bit more space in here then we also increase the size of this text box and change this title to selected year and period instead of displaying the year and period value in a simple text box let's remove this and insert a rounded rectangle shape instead for this rectangle we set the roundness of the corners to the maximum and then right click on it in order to open the format shape section on the right we set the fill to White and the borderline to this dark blue and then close that section again to adjust the size and proportions of the shape let's jump up here and play around with different values for the height and width and I think for displaying the selected year this is an appropriate size so let's link that shape's text content to the selected year value in the formula bar there it is it still looks a bit off so let's align this text to the center and middle reduce the font size to 10. and make it bold now it looks pretty clean to me so let's find a good position for that shape within the tile and right after that we're gonna duplicate the shape to also display the selected period right next to the year let's align both shapes to the top then increase the size of the second shape as the selected period text will probably take a little bit more space and eventually change the value reference to selected period display due to this updated reference we need to readjust the font size and the Bold text Style and that's it for this tile let's see if everything works fine by changing the selected year and period and here we can see that one major advantage of this tile is that even when we have selected those dynamically calculated current month and current year options in the drop down Fields this new tile prominently tells us what the current year and current month actually are of course this tile also updates correctly for any other selected year or selected month and what is also important even those longer period names like September or November perfectly fit into this period shape that means we are ready to regroup those tile elements and move on with the creation of the second tile which will display how much the selected period has already progressed in time so how much of the period is already over that will allow us to better evaluate the percentage completion for each individual position in our breakdown section as this can now be set into perspective before we create that tile let's first set up the underlying calculations to determine the selected periods progress in the calculations worksheet and before we get into that let's quickly straighten up the alignment of that selected period value okay and now down here to calculate Which percentage of the selected period is already over based on the current date we first need to calculate the number of days that exist in that period then the days that have passed in that period and then by combining both we will get the percentage value for the calculation of total days in the selected period we need to differentiate between the calculation of Base in the total year and days in a specific month if the selected period is total year then we can determine the number of days in the total selected year by composing a date for the last day of the selected year which is the 31st of December and then subtracting the date representing the last day in the year before the selected year so the selected year -1 that way the number of days in the selected year is always correctly calculated even if that year is a so-called leap year that has 366 days alternatively in case the selected period is not total year but a specific month instead then we can calculate the total days of that month by making use of the EO month function which stands for end of month that function Returns the date value of the last day in a month before or after a specific number of months that means it expects us to put in a start date value for which we provide a composed date with the selected year as year and the selected period as the month and a random day number like one for the day and then we could potentially Define an offset in months that we want to apply to that date but since we want to stay in this exact month we simply enter an offset of zero and finally since this end of month function returns a date value we still need to extract the day number from that date by wrapping the whole expression into the day function let's close that if statement hit enter and as you see with the selected period being set to Total year the total number of days for the selected year 2022 is returned as 365 which is correct when we jump over to the dashboard worksheet and change the selected year to 2024 which is a leap year the formula correctly returns 366 this time that's amazing and when we change the period to the current month for example which is March the formula correctly returns a total number of days of 31 while for February in 2022 it also Returns the correct value of 28 total days so as these days in Period calculation seems to be legit let's set the selected period back to Total year and continue with the days passed in Period calculation as we're going to need to differentiate between a lot of different cases in that calculation let's add a bit more space in the formula bar and start the formula off by checking if the selected year is the year of the current date that is an important first check because only if that is the case we actually need to calculate something that is different from either 0 or the total number of days in that period so in case the selected year is the current year then we need to check if the selected period is total year because in that case we need to return the total days that have already passed in this year so far and this number can be calculated by simply taking the current date and subtracting the date of the last day in the previous year alternatively in case the selected period is not total year but a specific month then we need to figure out if that selected period is the current month so the month of the current date in that case the days passed in this current month are calculated as the day number of the current date otherwise if the selected period is a month before the current month then that month has already fully passed and thus the return value equals the total number of days in that period in case the selected period is not smaller but greater than the current month then this period obviously hasn't started yet and the days passed in that period are zero with this we can now close the three inner if statements and now can focus on the value if false calculation for the initial if statement if the condition of that initial if statement returns false that means that the selected year is either smaller or greater than the year of the current date in case it is smaller than the current year then we follow the same logic as we have already done for the month and return the total days of that period as all of the days have already passed otherwise in case the selected year is greater than the current year that year hasn't started yet and the days passed in that year are zero we can now finally close that whole statement I know these were a lot of nested if statements but this construct was necessary to cover all the potential cases with the current date being set to the 15th of March the days passed in the total year of 2022 are now correctly returned as 74. when we jump over to the dashboard sheet and change the selected year to a year after the current year the formula correctly returns zero when we for example select a month that is before the current month than the days passed are correctly set to the total days in that month when we set it to the current month it correctly Returns the current day in the month and finally when we select the month after the current month then the formula correctly returns zero as this month technically hasn't started yet so this days passed in Period formula also works perfectly fine which now allows us to calculate the percentage of days passed in the period as the days passed in Period divided by the total days of that period and of course this is supposed to be formatted as a percentage value great with this calculation setup we can now jump over to the dashboard worksheet and create that second tile and as a blueprint we're going to take that first tile from the budget tracking worksheet again as this comes closest to the tile that we want to build for aligning it to the position of the selected year and period tile let's just place it at the lower position then select both and in the shape format tab click on align top it makes sense to First ungroup all the elements before we modify them one by one starting with the size of the tile background which we're gonna slightly reduce then we change the title to a shortened form of period completed and then we change the reference the value of that value text box down there to the percentage value that we have just calculated back in the calculation sheet let's readjust the text style to be size 12 and bold and also align it to the Center for this tile and then we're going to reduce the size of the text box and re-center it within the tile shape by selecting both elements and in the shape format tab clicking on the Align Center object to finalize this tile we want to add a progress bar just like we have done for the percentage completion values in the breakdown section for this one however we can't make use of the conditional formatting data bar option as we want to place this on the foreground of the tile shape instead we need to create an actual bar chart based on the percentage value in the calculation sheet for this let's select a random empty cell in the sheet so we can insert an empty horizontal clustered bar chart with no data in it and then we go to the Chart design tab click on select data and then select the single percentage value which produces this simple bar chart to normalize the proportions of the bar let's manually set the x-axis maximum to 1 which equals 100 percent and after that we select the whole chart area to remove the fill and borderline and then also remove any other element in this chart area except the bar itself of course we still need to adjust the fill color of that bar to match the lighter gray that we have used for the percentage completed data bars in the breakdown section and finally to make this bar fill out most of this chart area shape let's reduce the Gap to zero percent when we then also reduce the height that leaves us with a shape that comes pretty close to the cell based data bars in the breakdown section so let's move that over to the dashboard worksheet and adjust its size and position within the tile to be perfectly centered around the percentage value when fully filled to do this in the most efficient way let's put the value text box in front of the chart by opening the selection pane and dragging it on top of the chart element and then we can reposition the chart adjust its size to better match the proportions of the data bars in the breakdown section and once we have aligned it to the text box vertically we select both the chart and the tile shape to also Center it horizontally within the tile perfect let's select all elements of the tile and group them together and as this value is supposed to serve as a comparison value for the percentage completed values in the breakdown section let's align the horizontal tile position to these data bars by moving it down then making sure that the center of the progress bar in the tile matches the center of this percentage completed cells and then just realign it vertically by also selecting the other tile and making use of the Align top option that's it for the setup of the second period completion tile and you can instantly see the main value of that tile is that we now have a comparison value for all those percentage values in the breakdown section and that gives us a good approximation for every position if it is behind or ahead of the Curve let's continue with the next tile that will display the selected periods tracking balance so the Delta between the tracked income and tracked allocations to either expenses or savings but only those effective in the selected period let's start by calculating the period tracking balance in the calculation worksheet first and at first you might think this is a complicated thing to do for the selected period only but the good news are that we have all the values we need for that calculation already available because they are in the chart data tables for the category distribution charts in these data tables we have already dynamically calculated the total tracked income the total track expenses and the total track savings effective in the selected period so this calculation is as easy as taking this total income value and subtracting the sum of this total expenses value and this total savings value there we go as the selected period is totally here right now it is no surprise that this period tracking balance equals the total tracking balance up there and as we also want to have a similar analysis text in this new tile let's just copy down these two cells and as the cell reference has been set up relatively it automatically references the correct cells with the period tracking balance value above so it either returns this or this text based on the balances sign that's all we need let's jump over to the budget tracking sheet and simply copy over this tile for the total tracking balance as for this one we only need to change the title and the references in the value text boxes to turn it into the period tracking balance tile on this dashboard worksheet for vertically aligning it to the other two tiles let's just place it a little below them then also select one of the other two tiles and then align both to the top this time we won't even need to ungroup these elements and can directly start with changing this title to period tracking balance then changing the reference for the balance value to this newly calculated value in the calculation sheet let's restore the font size of 12 and the Bold textile and after that we also update the reference for this analysis text to now refer to the one that is actually related to this period tracking balance here we also need to restore the intended font size of 10.5 maybe we can even slightly reduce the size of this text box and of the tile background shape and that is it for this third tile let's see how this title value behaves if we change the selected period to January for that one it returns an evened out period tracking balance of zero as obviously the income tracked for this period perfectly equals the tracked allocated amounts to expenses and savings the same is the case for February and as in our sample data setup we are only halfway through the current month of March here we still have a lot of tracked income that is left to be allocated wonderful that means we can now move on to the creation of the fourth and final tile which is also the final component of this whole Excel template in this final tile we want to display one of the most crucial kpis in personal finance which is the savings rate just like for the other tiles we're going to calculate the savings rate for the selected period and technically there are two fundamentally different ways to calculate the savings rate in this template and I think it makes sense to enable whoever used this template to decide which way of calculation is a better fit maybe also depending on the situation so let's jump over to the settings worksheet and create a new title in here for decisions about the ways kpis are calculated in the dashboard and the label for the decision field will be savings rate as percentage of income because that is how the savings rate is always measured as a percentage of the disposable income that is saved within a period let's quickly copy down this drop down field and change the names of the available options while I'm going to explain the two different ways of calculating the savings rate as the savings rate is calculated based on the amounts that we actually track during the course of a period it will make a fundamental difference if we either calculate a savings rate actively as the percentage of the disposable income that we have actually allocated to the savings categories are passively as the percentage of disposable income that we have not allocated to expenses assuming we have a certain amount of disposable income at the beginning of a period Then calculating the savings rate as the percentage of disposable income that is actually allocated to savings would make the savings rate start at zero percent at the beginning of the period and then gradually increase towards its final State at the end of the period while when calculating the savings rate as the percentage of disposable income that has not been allocated to expenses so far this is switched around and we start with a savings rate of 100 which then slowly decreases over the course of the period until it reaches its final State at the end of the period so you see depending on the situation and the way your actual money flows look like either one or the other way of calculation might be better and to make the selection you make here accessible in all other sheets let's assign the cell the global name savings rate calculation type and also add a short description that explains the decision to take here for that description let's insert another row so that we have three rows to merge into one coherent description area for which we then set the text alignment to the left and top reduce the font size change the font color to this dark gray and finally make the font style italic then we enter a quick description tags that explains how the savings rate will be calculated if we either select the active or the passive option and then we jump over to the calculation sheet and start building that savings rate formula in that formula we first check if the savings rate calculation type equals percentage allocated to savings in that case we calculated as a total track savings amount divided by the total tracked income amount that are effective in the selected period and in case the other savings rate calculation type has been chosen we calculate the savings rate passively as the difference between the total income and the total tracked expenses in Brackets divided by the total tracked income effective in the selected period And since both these calculations include a division by income and this tracked income can be zero let's wrap this whole statement in an if error function to catch potential division by zero errors and in case of an error we simply return a dash great at the moment the selected period is total year and for this year we have tracked 13 600 in disposable income of which we so far have only allocated 3090 to savings accordingly with the savings rate calculation type currently set to the active option the savings rate here is 22.7 percent when we now change that savings rate calculation type to percentage not allocated to expenses however not only the savings position but also the amount left to be allocated that is visible here as the period tracking balance is taken into account for that reason the savings rate is much higher here at the moment but keep in mind that at the end of a period the savings rate will be more or less the same with both ways of calculating it given that the period tracking balance comes close to zero as I prefer the active way of calculating it let's put that in place and then with the savings rate value now available in the calculation sheet let's create the respective tile using the previous tile as a blueprint again we position that new tile at a lower position and then also select one of the others to align the new tile to the same top Edge now that we have the total number of tiles available in here for the first time let's quickly make sure that we have a consistent spacing between each of them that is similar to the spacing in between the budget tracking tiles so maybe we move this one a bit to the right then move those two together a bit more to the left and eventually slightly increase the size of the tile background shape of that initial tile that looks pretty clean to me so we can continue with setting up the individual elements of that last tile for the savings rate this will be a lot easier if we ungroup the elements first then we start the modification with changing the title to period savings rate as the next step we're gonna remove the reference in the formula bar from this descriptive text box as in this type we want to have a static text for which we're going to restore the font size to be 10.5 and the static text will be actually divided into a front part before the savings rate for which we enter you are saving and then we just make this descriptive text box and the value text box switch places after that we're gonna change the reference for this value text box to now reference our calculated savings rate in the calculation sheet and then we not only restore the font size of 12 and the Bold text Style but we also align this value to the center and this time also change the font color to be this beautiful blue that we associate with Savings in this template maybe let's slightly adjust the size of that value text box and move it a little closer to this front text and finally we duplicate that front text box to make the sentence complete first we align that second text snippet to the same vertical position and then replace the text with off your income so that the full sentence reads as you are saving X percentage of your income and after a few final positional adjustments to those text boxes and a slight reduction of the tile background size we can recruit all these tile elements quickly test how this new tiles content adjusts when we go through the few periods for which we have some track sample data that looks good for January and February for March we obviously have no track savings yet so zero percent makes absolute sense and finally the total year again which provides us the most evened out insight for this kpi and at that point we have completed the creation of this ultimate Excel personal budget template there are only two finishing touches you need to put to this template before actually taking it into production and applying it for managing your personal budget and this is at first changing the value of the current date name from this currently hard-coded date we see right here and which made absolutely sense during the development of the template to the dynamic today formula which always will return the actual current date and as a final obvious step we're gonna hide those two supporting worksheets for the drop down data and the calculations and that is it for this tutorial if you want to download the ultimate Excel personal budget template it is available on Excel find.com the link for that is in the description I hope you enjoyed this tutorial and I'd be more than curious to learn what additional features you would love to see in this personal budget template let me know in the comments any constructive feedback is appreciated and with that being said I wish you a beautiful day and see you next time cheers