Make Bill in Excel for Banking Effortlessly with airSlate SignNow

Make bill in excel for Banking

in this video I'm going to teach you how to build this personal finance dashboard from scratch including the essential steps you must take so you can update the dashboard with the click of a single button now if you're not interested in reporting on your personal finances you'll find there are a load of excel tricks and techniques in this dashboard that you can apply to any data for example waterfall charts trend lines automatic updates and more so be sure to stick around the Excel file is available to download from the link in the video description so you can use this file for your own reporting and if I go too fast for you you can click the Cog icon in the bottom right of the video and change the playback speed all right let's get started the first sheet in the file will contain my dashboard this is just a regular sheet and I've simply adjusted the row Heights in a few places to save me some time later but otherwise there's nothing fancy about this sheet you don't need an addin or anything like that so let me name this dashboard then on the transaction sheet I've got my bank transaction data now you might import this from your bank statement or enter it manually obviously there are a lot of different banking systems so the process will differ from one to the next and I can't cover them all but most will have an export to Excel or CSV or text file button enabling you to download the transactions and bring them into Excel now your bank should export the first five columns of data containing the account it came from the transaction date the description and the debit or credit amounts I've then added a formula for the amount and this simply takes the credits minus the debits to get a single column of values that stores expenses as negative values and income as positive values this column is simply required for the waterfall chart I've then added subcategories for each transaction via a data validation list if you're not familiar with how to insert a data validation list then you can click the link at the top of the video to see it tutorial the data validation list is stored on the data validation tab you can see it in this table here it's also got Columns of the category and category type now you can modify this table to suit your own needs adding and removing rows as required the next thing I need to do is populate the category and category type field and I'm going to use a lookup formula for this I'm using xlookup but if you have an earlier version of excel say 2019 or earlier then you can use V lookup or index and match so what I want to do is look up the subcategory on the datea validation list in the subcategory column and return the category close my parentheses and there's my category completed now it's copied down the entire table because my data is stored in a table and I'll mention more on that in a moment first let's do the category type I'm going to control R to copy the formula across and then all I need to do is replace the CATE with the category type and that's done now these fields enable me to group the transactions and understand where my money is going I've currently got data from January through to October in this table and at the end of the video I'm going to show you how you can update the dashboard with November's data with one click so be sure to watch to the end now to enable updating with one click it's essential that you format your data in an Excel table using the keyboard shortcut contrl t my data is already in a table and you can see here it's called transactions all right I'm ready to start analyzing and summarizing the data for my dashboard let's just take a quick look at it so we can familiarize oursel with what we need to build the first values I need are for the headline figures at the top of the dashboard now one thing I want to point out before we get started is that I've set this page to use a specific color palette in this case the paper color palette and this Alters the default colors to those that you see here and that's why you'll notice they appear different to the defaults you might be familiar with you can even create your own custom color theme and that will save you time when you're formatting your reports so let's go back into the file I'm working in you can see I've also selected the same color palette in this file here it's set to paper you get a little preview as you hover over the different color palettes you can play around with that in your own time now I'm going to be using pivot tables for my analysis because they're super F to build and when they're linked to data in an Excel table like I have here they automatically include any new data upon refresh so let's go ahead and insert a pivot table on a new worksheet I'll start by giving this pivot table a name we'll call it PT headline and this will help me identify it later when I connect the slices I'll drag the pivot table field list over closer to the pivot table so you can see it in context so here I want the category and and I want the debit amounts now I don't want the salary this is just looking at expenses so let's filter that out I'm going to sort values in descending order so largest to smallest let's go ahead and format the numbers I want them set to a currency data type with no decimal places this is going to feed through to my report now let's rename this sheet analysis now I'm going to put all my Pivot tables on this sheet but when you create your own dashboards I recommend you put each pivot table on its own sheet so that there's no risk of them overlapping one another because this will break your dashboard so we're ready to go to the dashboard sheet and bring in those numbers first of all I need some formatting now you can do this with cell fill colors but I want rounded corners on my background so I'm going to use a shape so insert shapes and it's this rounded rectangle here and let's just roughly draw it in I know the size that I want so on the format tab I'm going to type in the size size 1.93 by 4.72 okay let's go ahead and format the shape so I'm going to control one with the shape selected that opens the formatting pane we can drag it out and move it to somewhere more convenient so in my fill I want a gradient fill and down here I want an extra stop and we'll move it to the end and this one here let's just make this a bit bigger put that in the middle it's pretty much there anyway so going to start with the dark purple which it is then move through to this color and end again with the dark purple it's not going in the direction I want I actually want it to go horizontally so I want this and that's pretty good okay let's give it some text let's change the size of that font to 30 point now I need some icons for the different expense types to make the numbers quicker to interpret so on the insert tab we've got icons these are available in Excel 2019 onward so you may not have them in your version I want a house and shopping this will be for my discretionary expenditure and I want car for my transport expenses and dining for my restaurant expenses and I want one for charity and lastly one for my medical expenses so let's go with this one here you can see in the bottom corner I've got six selected so let's insert those with them all selected I'm going to change the size to 1.2 and I'm going to change the color to this pale purple that'll stand out better on the background so let's just roughly align them so I want one there charity and then we have medical transport discretionary I'm not too worried about their position cuz what I'm going to do is on the Home tab we're going to go into find and select and select objects then I'm just going to lasso them with them all selected I can now use the alignment tools so I'm going to align them to the top and then distribute them horizontally just spaces them evenly and then with my arrow keys I'm just going to nudge them up towards the top because I need space below the icons for my text all right the next thing to do is to link some text boxes with the values in my pivot table so we need to insert a text box and we'll just do the living expenses first so with the text box selected in the formula bar I'm going to equals go to my analysis sheet now if I select the pivot table I get the get pivot data function now a text box cannot be linked to a formula so I need to link it to the cell beside and then just change that to column B that just gets around the get pivot data formula and links it to the correct cell in the pivot table the press enter there's my value linked to the pivot table let's Center that vertically and we'll go ahead and get rid of the fill color and the outline on that shape and then we can format the font in the same light purple change the font size to 14 actually might make it 16 there we go all right so that's that one done the next thing I need is a text box for the labels so let's do that another text box underneath this is my living expenses and again we want to format the text box with no fill and no outline on the Home tab we'll format the font in the lilac color and we'll make it bigger which means my text box needs to be bigger as well okay so there's the first two text boxes done holding down shift to select them both and then holding control and shift I can left click and drag to duplicate them and I'm just going to do it for each of the categories and then we'll edit them so this one is going to pick up cell B5 and you'll notice the formatting gets lost when I make that change but I'll show you a shortcut on how to put that formatting back in a moment seven this is eight and nine so now with the formatting gone I'm just going to select the one that's still formatted correctly double click the format painter and now I can just simply tap it onto those text boxes that need formatting press escape to clear the format painter now I can just modify these discretionary this one is transport this one is dining out this one is charity and and this one is medical okay I won't spend too much time on formatting but let's just tidy those up a little bit all right using my lassu tool again let's select these ones and it hasn't picked up those two that's fine you have to lassu the entire text box for it to work so we'll move them over okay that's good now we just need one more text box to say spend year to date and we'll resize that and align it right okay let's take another look at the dashboard to see what's next so I need a pivot table for the donut chart let's tackle that one we go back into the file and we're going to insert a new pivot table onto the analysis sheet just going to pop it below this one here so I need account and the debit amounts let's form format the numbers currency with no decimal places let's give the pivot table a name PT donut and we're ready to insert the pivot chart so under Pi you'll find donuts I'm going to right click and hide all the fill buttons I don't want those and I'm going to move the legend to the bottom we're going to add labels but I want to go into more options and here I want percentage and no leader line and I wanted to wrap down onto a new line now let's make the donut hole smaller so instead of 75 we'll make it 50% and let's change the colors I prefer this color scheme we'll give it a new title total spend by account now let's cut crl X to cut and paste it into the dashboard so I need to resize it and we'll take it out to column J let's close the pivot chart field l so we got more room to work so what I want to do with this is also give it a fill color for the background so again gradient fill this time I only want two gradient stops so let's remove one and we'll move that to the top instead of the direction it's currently going in I want it to go vertically and now I need to recolor the text so that it's readable unfortunately I have to select them one by one and apply it all right let's just nudge this up here and we're going to send it to the back so it sits underneath the header I want to change the chart borders so let's scroll down and expose the Border options and at the very bottom I want rounded corners and that's just going to be in keeping with the rest of my rounded Corners it looks like this has a border on it so let's get rid of the outline on that one that's looks better probably nudge it up a little bit more okay so we'll move this down slightly and we'll make the donut hole or the donut bigger now one more thing I need is the total spin so let's insert a text box it's going to sit in the middle of my donut I'm going to link it using the formula to the grand total here remember I can't select the cell of the pivot table so I'm going to set select the one beside and type in B let's change this to have no outline and no fill go back here and we'll make the font White Center it and make it a bit bigger okay so there's my donut chart done we'll go back and check what the next chart is so this one is for all expenses and net income this is actually two charts formatted to look like one so what we need is two separate pivot tables let's go back and insert those on the transactions I want to insert a pivot table on my analysis sheet and to pop it in there so this pivot table is going to look at the category type in the filter we want the date now notice the dates are grouped we can tell they're groups because they got the plus beside the month name if I right click and group I can remove the grouping on days and just leave it grouped as months and then I want the sum of the debit amount let's give this pivot table a name so it's my pivot table for my expenses column chart let's format the numbers so they feed through to the Chart the way we want no decimal places and I'm ready to insert a cluster column chart that's the first one by default anyway so let's pop that in don't need my field list anymore so let's bring it up here I'm going to right click and hide all the fill buttons and then here I don't want the legend there's only one series I don't want the grid lines because I'm going to add data labels now notice the vertical axis starts at 2,800 it should always start at zero in a column chart so let's go and fix that in the axis options we're going to make the minimum zero and the maximum 3200 now I've done that I don't need to see the vertical axis because I've got my data labels let's make these columns a bit wider so we'll fix the Gap width to 50% and we'll just move the chart title to the left I'm going to leave it as is we'll see later on I'm going to create some Dynamic chart titles the last thing I need to do is I want to get rid of the shape fill and the shape outline because these charts overlap one another so let's cut it out and paste it in here and we can make it fill the space and we'll just bring it down to about row 14 okay that's better now let's get rid of the grid lines on the dashboard and it look more like a dashboard okay that one's done let's rinse and repeat for the net income chart so for that I'm just going to copy this pivot table and let's bring the field list back right click show fielded list and here I don't want the debit sum of debit instead I want the amount and I don't need the category type anymore let's change the grand total to net and we'll do the form formatting with comma separator no decimal places and we'll give the pivot table a name PT net column chart let's insert the column chart get rid of the field list okay so again right click hide all the field buttons we don't need the legend or the grid lines cuz we're going to have data labels we need to fix the vertical axis so that it matches that of the expenses column chart that was just make them more easily comparable so for that we want a maximum of 3200 and that changes the minimum so let's change that to zero all right now we've done that we don't actually need the vertical axis so let's turn that off we'll make the Gap width 50% like we did with the other chart let's change the fill color to this pink and let's also add a trend line and I'll just select that and make it Pink as well so it's consistent all right this is our net income and we'll get rid of the shape fill and outline and we'll crl X to cut it out and pop it on the dashboard so we'll make it the same width and then I can bring it up so that it sits closer bring that down so it's not overlapping and in fact I'll make the font smaller in that I'll make the font smaller there so they're consistent so there's our two charts now to make them look as though they're one we're just going to surround them in a rounded Corner rectangle we just draw it on and then we'll change the radius let's get rid of the shape fill and we'll give it this pale purple color let's take a look at the dashboard the next chart is this one down here the line chart by expense category so let's go ahead and build the pivot table for that I'm actually going to copy this one and paste it here so bring back the field list and we'll just put it on the left side so on the category type the date sum of debit but I want to see it by category going across the columns we'll get rid of the grand total right click remove grand total now I want to sort it in descending order based on the amount so in category more sort options I want to see in dend in order based on the debit amount so now we have living expenses down to salary in fact I want to make sure I'm only looking at expenses so let's filter it I don't need the grand total either so let's remove that all right we're ready to insert the line chart let's do it from here you can do it from the pivot table menu or from the insert tab I'll right click and remove all the fill buttons or hide them let's pop the legend at the top it's going to act like my chart title we'll just make the chart wider so that it's on one line okay now we can't see the medical expenses so I'm just going to select charity and then hold down control and up arrow and you can see now I've selected a series we can't actually see so let's control one to open the formatting we'll go into here and we'll add a marker one of the built-in markers just the dot the reason we don't see the medical expenses is because they're not in every month so there's no line for them but with the dot we can see when they occur all right let's select the chart crl X to cut we'll go into the dashboard and paste it in let's get rid of that field list because we don't need that anymore and we'll resize it now this chart we can actually set the chart border to have rounder Corners so in the formatting down the bottom we got rounded Corners let's just change the color so it's consistent with the others and that one's done okay let's go back to the dashboard and see what's next so the next chart is the tree map and tree Maps suitable for displaying hierarchical data let's go ahead and build that so I'm going to insert a pivot table on the analysis sheet and we'll just pop it beside now let me just check I didn't rename this pivot table so let's do that first this is PT line chart okay and while we're naming let's name this PT tree map so this pivot table is going to have the category type the category subcategory and the debit amount now I only want to see expenses and I don't want this layout I don't need the subtotals so right click and turn them off and then on the design tab we're going to change the layout to tabular I also don't want the grand total so right click and remove that so there's the data that I need for my tree map now if we try and insert a pivot chart you'll see that we can't create this chart type with the data inside a pivot table so we have to trick Excel into creating a tree map chart and referencing the pivot table so I'm going to copy the data and then just alt ESV to paste as values now that we have the data in the format we want that is not in a pivot table we can insert insert the tree map let's make it a bit bigger now all I need to do is right click the tree map select data and edit the cell references so instead of referencing that sum of debit I want it to reference that cell and this one here instead of those numbers I want it to reference the pivot table and we'll do the same for this one so instead of those labels we want these ones and now you can see my streap chart is referencing the pivot table I don't need this data anymore let's delete that so let's go ahead and do some formatting here I don't want the legend and the data labels I want to add the value so that's my tream app done I'm going to leave the chart title for now because like some of the other charts this one's also going to have a dynamic chart label which we'll do later so control X and crl + V to paste it in let's make it get a bit bigger and we also want to hide or turn off the outline now these tree map charts do not have the option to give the Border rounded Corners so let's hide the outline no outline and then we'll insert actually what I'll do is copy this shape holding down control and shift left click and drag to copy it across and then I'll just resize it to around the tree map so there's the tree map done let's go back to the dashboard and see what's next so last chart is the waterfall chart and this illustrates how you get from the starting point of your income to the amount you have left at the end of the period so let's go and insert a pivot table so insert pivot table on the analysis sheet and we'll just pop it underneath the tree map okay so this pivot table is going to have the date in the filters the category down the rows and the amount let's give this piver table a name PT waterfall and we'll format the numbers with no decimal places and the comma separator now ideally I'd have this sorted starting with salary and then the biggest expense going down but if I sort these amounts in descending order from largest to smallest you see it starts with the smallest negative value and actually what I want is living expenses up here if I try and manually rearrange that it won't let me so let's undo that I'm just going to manually sort this so that salaries at the top you can go ahead and manually sort the others if you want I'm going to leave them at that for now now just like the tree map the waterfall chart does not have an equivalent of a pivot chart and we can see that if we go in here we get the same message so just like the tree map we're going to copy the data and paste it as values and then we can insert a regular waterfall chart and then go about modifying it right click select the data and we'll edit where it's referencing so instead of there we're going to reference the pivot table and then likewise for the labels okay and okay let's change this to net instead of grand total let's make this chart bigger so we can see it more clearly all right now because we have labels we don't need the grid lines and we don't need our vertical axis we already know what the amounts are now with a waterfall chart you need to tell Excel which column is a total amount and that in this case is this net column here so selecting at once and then once again with the left click selects just that column right click set as total so we can see now that we came from 40,000 in salary and we've had all these expenses that leave us with a net of 9355 now I want to change the color style of this table so on the design tab we're going to change it to this fourth one here all right let's contrl X to cut and paste it in the dashboard now just like the tree map we do not have the luxury of being able to round the corners of this chart so I'm going to hide the chart border I'll just resize it first and then on the formatting tab we're going to get rid of the outline and then I'll just copy the shape from above down and resize that for this chart one more thing I want to do actually is put the legend at the bottom now this one is also going to have a dynamic chart label so I'm going to leave the chart title for now okay we've created all the charts the last visual we've got left let's just take a look at the completed dashboard is this table at the bottom with our top five categories so let's go ahead and build the pivot table required for them on the analysis sheet I'm going to go to the star and just copy the very first pivot table we built for the headline figures so in here I don't want the grand total let's remove that right click remove grand total and then we can filter this for our top well I want top five so let's go into top 10 and we'll change that to five top five items based on the sum of debit amount so there's our top five let's select those cells and control C to copy and then in the dashboard in cell J30 I'm going to paste links now these cells here I actually want to move move them across let's format them control1 to format them with currency formatting and no decimal places now I actually need these numbers again so I'm just going to reference them and contrl enter I'm not too concerned about the formatting here that's fine because what I actually want these for is to apply some conditional formatting data bars doesn't matter what color so select blue and then let's go in and manage rules double click on the rule here to edit it here I only want to see the bar in this column cuz I've already got the numbers here let's change the color to purple click okay and okay and there's our table let's give it a title top five categories in select a period and we just need an underline for that I'm going to use a shape just a line shape because I don't want the line to go all the way across so if I put a border on the cell it would go all all the way across and touch the charts on either side so I'm just holding down shift while I draw in the shape and then let's change it to this thicker one here we could probably nudge it across a little bit more so there we have all of our charts and our top five categories table we're ready to insert the slices which allow the user to interact and filter for different categories and months so on the analysis tab I'm just going to select this pivot table here which is the tree matap pivot table doesn't matter really which one you choose as long as it's one that you want to filter with the slicer and then on the pivot table analyze tab I'm going to insert slicer and here I want category and date so I want two slices holding down shift to select them both I'm going to format them purple and let's give the date slicer go to the slicer formatting we'll give it two columns and we need to do some more formatting in the settings so right click slice the settings at the very bottom and here I want the header to say month or MTH and I want to hide items with no data that's going to hide these four down here that are paler purple click okay those buttons will only come back if we have data for them this one we need to do some similar changes so go into the slice of settings here I want to hide items with no data that'll hide the salary item okay let's select them both holding down shift crl X to cut them out we'll paste them in the dashboard so this one's going to go on this side can be a bit narrower maybe not that narrow and this one's going to go on this side and we'll make it fit in the space all right so I'll just select them both and then we'll do some alignment we'll align them to the top we'll move them up and then let's make the buttons bit bigger so they take up all the space okay so there's our slices at the moment they they're only connected to the tree map chart so what I want to do is connect them to the other charts they need to be connected to so I'm going to right click on the slicer I'm going to report connections and this is where naming the pivot tables becomes really important because now I know which PIV table I want to connect to so this one for category I want to connect it to the expense column chart and you can see up there I've missed one of the pivot tables probably the last one I built and also the tream map which is so click okay let's go in and fix the name of this pivot table there it is PT top five and we'll go back and we'll finish with the month slicer connections right click report connections this one's going to be connected to my top five tree map and the waterfall so now when I select different periods you can see the top five categories the waterfall and the tree map update ing and likewise when I select different categories the tree map and this total expenses over here updates ingly now that we've got the slices set up I'd like to create custom chart labels that include information about what's selected and if we go back to the original dashboard you can see as I select items in the slicer the label up here here and here update to reflect what I'm selecting so let's go and set those up we go to the analysis sheet now the first thing I need is a pivot table that displays the items selected in the category slicer so let's insert a new pivot table insert pivot table onto our analysis sheet up here this is just going to contain the categories I don't need the grand total and let's add a filter in for the category type so I only want the expense categories all right so this is my slice of pivot table let's name it PT slicer and we can now connect it to the category Slicer in the report connections or check the box for PT slicer so now if I select only three categories we can see only three are selected here so this pivot table now tells me what I've selected in this slicer we can reference that with formulas to build Dynamic labels we need three separate labels we'll start with the label for the expense column chart so this is going to use the ifs function if you don't have ifs you can use a nested if formula works in the same way so first I want to check that this cell here is not blank this is the last category in my list so if this cell's not blank it means that all categories in the slicer are selected so if it's not blank then I want to return the text all expenses however if it is blank then I need the next result returns so we simply enter true here and then what I want to return is a list of all of the categories selected and I can use the text join function to do that now I should point out that the text join function is available in Excel 2019 onwards so if you don't have that you can't create this Dynamic label in the same way so the first argument is the delimiter well I just want a comma and a space between each piece of text I want to ignore empty cells so we'll tab to select true and then the text is just the text in these cells here close parentheses press enter So currently I have all expenses selected if we go and select just three you can see it now lists them so I want to link this cell to my chart title so selecting the chart title in the formula bar equals and then in the analysis sheet I simply want this cell here and now we have a dynamic chart label that updates as I select different items in my slicer okay the next label I want is my waterfall chart now it is only filtered by the month slicer so I only need a title that reflects what month is selected or months so let's go and build that we can grab that information from this cell here so in this cell here I'll write my Dynamic label I want to start with the text expense by category and I want to concat detate to that text some text that depends on what's selected in the slicer so we're going to use ifs again if you don't have ifs you can use nested if so if this cell here contains the text all then I want it to return the text year to date however if this cell here contains the text multiple items as it now does then I want it to return the text multiple periods however if it contains a specific month then I want it to Simply return the value in this cell here so my last logical test is true close parentheses and now we have expense by category multiple periods so we can link it to this chart now the waterfall and the treemap charts will not allow you to link the chart title to a cell if I select the chart title you can see well you can't probably can't see cuz I simp simply cannot click in the formula bar so what we need to do is insert text box over the top of the chart title and I'm going to just make it the width of the chart and then in the formula bar equals and we'll reference the cell here now text boxes can't have a formula entered in them they can only reference a cell that contains a formula that's why we write our label in a cell and then reference that cell in the text box so let's format the font in this chart title give it a slightly dark gray color and we want to get rid of the outline on that text box so no outline okay so there's the title for the waterfall chart we've got one more to do for the tree map and it's actually a combination of chart titles the tree map is filtered by both the category slicer and the month slicer so it needs to contain this text and what period is selected which we used in the waterfall so we'll start by saying include this information and we'll add a hyphen and some spaces and then I'm just going to leave that part there I want to go and copy this so and the ifs which Returns the period and we'll just add it to this formula so now we get and you can't quite see it there we get the categories selected and the period if we just go and I'll grab this text box paste it up here let's change the reference here from analysis 23 to this one and now you can see it's picking up the category names and the period That's selected let's format the font in this shade of gray now when we select items in both slices you can see all of the labels are updating ingly Okay so we've built our dasboard in it's interactive the next best thing to be able to do is update it really easily so on the November data sheet I've got my November data I've already classified it into its categories so it's ready to go obviously you're going to download your data from your bank and then go ahead and classify it but for now just to demonstrate I'm going to copy this data and then at the very bottom of the transactions table on the very next row I'm going to just control V to paste it in you see this blue marker here in the bottom right of the table will extend the table range down to include my new data so there's my new bottom of the table and that means all of the pivot tables here in my analysis sheet that are linked to that table will automatically pick up that new data so if we go to the dashboard and you keep an eye on these charts here which have the month listed on the data tab I can simply click refresh all so one button and my dashboard is now updated to include my November data I haven't had to do any more work now one thing I have just noticed that I forgot to do is set the fields for the waterfall chart to show items with no data we can see here with the chart filtered to show only June's data that there's no item for medical expenses so the next column has now moved across to the left and it's not displaying as a total column so let's go and fix that on the analysis tab all we need to do is select a cat agree field right click go into field settings and then on the layout and print tab we want to check the box show items with no data click okay you can see now we have the medical item even though it's empty and the net value is now in cell S35 we go back to the dashboard you can see our net column is now correctly displaying and if I select for example November's data which also doesn't have any medical expenses it's correct by click all the filters we can now see some medical expenses I hope you enjoyed this tutorial and found some techniques and ideas you can use to build your own dashboards you can download the file for this lesson containing the data and completed dashboard from the link here if you like this video please give it a thumbs up and subscribe to our channel for more and why not share it with your friends who might also find it useful thanks for [Music] watching