Collaborate on Excel Bill Tracker for Administration with Ease Using airSlate SignNow

Excel bill tracker for Administration

With the cost of living crisis affecting  everyone, it's essential to get control of   your finances so you don't leave yourself  short. I've put together this interactive   personal budget tracking spreadsheet designed  for use in future years. It's super quick   and easy to use for multiple bank accounts,  all in one place, and it gives you a handle   on your financial position at a glance. It  highlights problem areas, and you can drill   down to identify the cause and take corrective  action to get back on track. You can download   the template for free from the link in the video  description. Continue watching as I step through   how to use it and how it's constructed so that  you can customize it for your specific needs. Now, I know it might look complicated, but I  promise it's point-and-click easy to build. It   contains zero code and only four simple formulas.  I've done that because formulas are easy to break,   and the last thing you want is a personal budget  spreadsheet telling you you have more money than   you actually do. I've also included a bonus  savings tracker because you'll need to start   doing something with all your spare cash  once you get your finances under control. Now, I'm going to step through it at a reasonably  fast pace. If you find it's too fast, use the   video settings to change the playback speed.  The first thing we need to do is create a list   of income and expenses we expect to have. You can  see I've started a list of subcategories, which   I've then organized into larger groups to help  us analyze spending, and then a column to mark   them as expense or income. Before we add any more  items, I need to format this in an Excel table,   and I'll use the keyboard shortcut Ctrl + T. My  table has headers, so check the box and click   OK. This is just going to make it easy for Excel  to reference. Let's give the table a new name;   I'll call this table "Categories," and let's  change the color to keep in with the page theme. Now, this list is fully customizable, so let's  add a new item for motor vehicle fuel. Notice   when you enter it on the very next available row,  the table expands to include it in its range.   This means that anything referencing this table  will automatically be able to see the new item;   no further editing of formulas, etc., is required.  Here, I've used emojis in my category names to   help us more quickly identify them at a glance.  To insert an emoji, use the Windows key and the   semicolon, search for the emoji that suits  your needs—I'm going to go with the car   because this is for transport—and then into the  category type, this is an expense, obviously. Now, if you need to delete any items, simply  delete an entire row; that's the safest. Let   me quickly add the rest of my items. Now, we'll  be using the subcategories in other tables via   data validation drop-down lists to make them  easy to reference. I'm going to define them   as a name, so select them, and then via the  Formulas tab, I want to Define Name. Here,   I'm going to call them "Subcategories." Notice  it picks up the name of the table and column.   If you have an earlier version of Excel, you  might get an error when you click OK. If so,   there's a link in the video  description to work around. Now that we've set up the foundation of our  report, we're ready to prepare the budget. Because   you've already seen how to insert a table, to save  time, I've gone ahead and set up the budget table,   and we can see it's called "Budget." The  first column contains the year—remember,   this template is designed to be used for many  years, so you can just continue adding rows   to this table for future years. The  next column contains the subcategory,   and we simply select it from the drop-down list.  To set up the drop-down list, select the column,   and then on the Data tab of the ribbon, Data  Validation. Here, I want a list, and you can   see I've already entered my source, which is  the subcategories name we just set up. Now,   if you can't remember the name, press the F3 key;  it brings up the list of names, and you can simply   insert it. I won't do it again; I'll click OK,  and then your drop-down list will be available. Just note that income has been  entered as a positive value,   and expenses get entered as negatives.  Again, any new items get added on the   very next row under the table. If the  amount is the same for every month,   simply left-click and drag it to fill across;  otherwise, enter the amounts individually. So,   we've got 700 for our vacation in March and  800 in August. Okay, that's the budget done. Now, we're ready to record our actual spend  in the transactions table. You can copy and   paste the transactions in from a bank statement  for the date, description, debit, and credit   columns. Then, all you need to do is enter the  account that the transactions relate to, and this   allows you to record multiple accounts in the  one table. Finally, select the subcategory from   the list. Now, if you ever want to dive deeper  into your transactions, you can use the filter   buttons. Here, we can choose the account, or we  can filter by date, and there are lots of date   filters we can choose from here, or description,  right down to specific text filters, and so on. To make this even easier, I'm going to  add some slicers for this table. So,   with the table selected, on the Insert tab of  the ribbon, Slicer, and I want a slicer for   the account and the subcategory. I'll click  OK. Let's select them both, I'll bring them   roughly into place, let's color them in keeping  with the table, and I'll just quickly do some   formatting. I'll give this slicer four columns  and we'll just stretch it across there. And now,   with the slicer, I can filter for a specific  account, right down to the subcategory. And I   can continue filtering; say, for example,  I wanted to filter into a specific date,   I can also use the filter buttons. Let's click  the clear filters, and we're back to normal. Now, a side note: if you wanted, you could  add another column to the table for notes   for each transaction. So, to recap, you need  to decide what categories you need—that's a   one-time job. Once a year, set up your budget.  The first time you do this will be the hardest,   and then every year after that, you'll  have your actual spend nicely analyzed,   and you can simply copy that as a starting point  for next year's budget. And then, once a month,   copy your bank transactions into this table,  tag them with the account and the subcategory,   and then all you need to do is go to the report  sheet, and then on the Data tab of the ribbon,   click Refresh All, and then  your report will be ready. Next, we need to combine the three tables  into one dataset that we can easily summarize,   and the quickest and easiest way to do this is  with Power Query. Don't be put off by the name   of this tool; it is super easy to use, and the  nice thing is you only have to do this once. So,   I'm going to start by loading the category  table. We do that by the Data tab of the ribbon,   From Table/Range. This opens the Power Query  Editor window, where I can perform more data   cleaning tasks. Now, this data is already clean,  so I'm going to Close and Load To, and here,   I want Only Create a Connection. On the Data tab  of the ribbon, Queries and Connections opens the   Queries and Connections pane, and you can see  I've got one query for my categories table. Alright, let's go to the budget table, and  we'll do the same, From Table/Range. Now,   the budget table is in a great layout for us  to enter our budget data, but this layout,   with values split across many columns for the  months, is not easy for Excel to summarize. So,   let's fix that. The year and subcategory  columns are correct, so holding Shift,   I'll select them both, then right-click,  Unpivot Other Columns. And now we have   our month and our budget amounts in their  own columns, which is the correct layout. Next, I need to create a proper date column.  So, I'll select the month column, and then,   holding Ctrl, select the year column, and then  right-click, Merge Columns. Here, I'm going to use   a custom separator, which is a hyphen, and this  column will be called Date. Click OK, and there's   our date. Now, at the moment, it is still in a  text format, so clicking on the dropdown here, I   can change the data type to Date. Now, you can see  I have a proper date for the first of each month.   By the way, my dates are formatted day-month-year,  but Power Query will respect your locale and   format the dates based on your PC settings.  Lastly, let's rename this column "Budget";   just double-click to rename it. Notice in the  Applied Steps pane, Power Query has recorded   all the steps, much like recording a macro. And  this means that when I update my budget table,   Power Query can perform all these steps again  without me having to go through all this setup. Alright, let's close and load too. And again,  only create a connection. Then let's go to the   transactions table, and we'll load that in.  Now, you notice here the debit and credit   columns have null values where sales were empty.  And ordinarily, this would be okay, but I need   to perform a calculation on these columns in a  moment, and it will fail with null values. So,   with them selected, on the Transform tab, I'm  going to replace values. The value to find is   null. Now, Power Query is case sensitive, so  make sure you spell it with a lowercase 'n',   and I'm replacing it with a zero. That's that  done. Now, I'm going to select the credit column,   and then holding control, select the debit column,  and I'm going to add a column that subtracts one   from the other. It gives it the default name  'Subtraction', and I can either double click   here to change the name or I can change it up in  the formula bar. We'll call this 'Actual'. And   notice, we now have our income as positive and our  expenses as negative values. Now, I can select the   debit and credit columns and press the delete key.  I don't need them anymore. One important point is   Power Query doesn't alter the source data. It's  creating a separate view of the data for our   report. And while I could have created this new  column for 'Actual' with a formula, doing it in   Power Query means there's no formulas that I need  to maintain or that can accidentally get broken.   Plus, it's actually more efficient for Excel to do  these calculations in Power Query. Okay, now that   we have our tables cleaned and transformed, I can  append the budget and transactions together. So,   with the transaction table selected on the  Home tab of the ribbon, I'm going to append   queries as new. First table is my transactions,  and the second one is my budget. Click okay,   and now we have a new table with our transactions  and budget. Let's call this 'Data'. And let's fix   the date format, and just make it a date rather  than date-time as it currently is. The next thing   I want to do is bring the category and category  type fields into my data table. And I can do   this by merging tables. You can think of merging  like doing a VLOOKUP or XLOOKUP from one table to   another. So, from the data table on the Home tab,  I'm going to merge queries. The top table is my   data table, and the table I want to merge with  is my category table, and the column they have   in common is subcategory. So just select that in  both. That is, we're looking up the subcategory   in the data table, finding it in the categories  table, and bringing in the category and category   type. Now, the join defaults to left outer, which  is fine because it's going to keep everything in   my data table and only bring in matching rows from  the category table. So, I'll click okay, and now   I have a new column that contains the category  table. Clicking on the double arrow here, I can   select the columns that I want to bring in. Now, I  already have subcategory, so let's deselect that,   and I don't want to use the original column name  as a prefix. Click okay, and just like a lookup   formula, but easier, Power Query has looked up the  categories table and brought in the corresponding   category and category type for each subcategory  in this table. Next, I'm going to select the date   column, and then on the Add Column tab, under  Date, I'm going to add a column for the year,   and then repeat that and add a column for the  month. We need these fields for our charts. Now,   if you're familiar with pivot tables, you might be  thinking that I can just group these date fields   to get month and year, but as you'll see soon, I'm  going to build this profit and loss pivot table   with calculated fields, and I can't have grouped  fields in the same dataset. Alright, we're ready   to start creating the charts. So, I'm going to go  to the Home tab and close and load too. And here,   I'm just going to create a connection because I  haven't loaded my transactions or my data table   yet, and I don't want either of them loaded. In  order to use the data table in a pivot table,   I need to right-click and change the load-to  settings just for the data table. And here,   I'm going to choose PivotChart, going to put it on  an existing worksheet which I already have set up   called 'Analysis', and we'll pop it in this cell  here. Let's click okay, it inserts a pivot table   and pivot chart placeholder, and I'm ready to  build my Pivot chart. If you want to use Power   Query and other areas of your work to improve  productivity by automating boring and laborious   tasks like these, check out my course, linked to  in the video description. The first chart is going   to plot actual versus budget expenses by category.  So, I want category, actual, and budget. Now,   I need to filter this chart to only show expenses.  So, I'm going to insert a slicer on the Insert   tab, slicer for the category type. Let's bring  it over beside the pivot table, resize it just a   little bit, and choose 'Expense'. In the pivot  table, I'm going to right-click and sort the   values smallest to largest, and then I'm going to  format the numbers with a custom number format.   So, down to custom, and in here, I want the dollar  sign, hash, comma, hash, zero, underscore, close   parentheses, and then the semicolon separates the  formats. So, next is the negative value format, so   open parentheses, dollar sign, hash, comma, hash,  zero, close parentheses. And you get a preview of   what it's going to look like up there. I'll click  okay. In Australia and the UK, we call parentheses   brackets, and that reminds me of a mnemonic I used  to use when I'd hand out the monthly management   reports to non-finance people back when I was a  financial controller in London, which is 'brackets   are bad news'. And that will apply to your own  personal budget where negative values and negative   variances are undesirable. So, remember, brackets  are bad news. Right, let's repeat the formatting   for the budget. Go to custom, and scroll to the  end, and your new format is always at the bottom.   Click okay, and now those values are going to feed  through to the chart with the formatting. Right,   let's quickly do some formatting for the  chart. First of all, I'll go to the Design tab,   and I'm going to change the chart type because  this should be a bar chart. And then I'm going to   right-click on the field buttons and hide them all  because they take up way too much space. And then,   selecting the horizontal axis, Ctrl+1, and  then over here, I want values in reverse order,   and label position, I want high. It's going to  look all wrong for a moment, so just bear with me.   Next, I want the vertical axis, and again, this  is also going to be categories in reverse order.   Here, I want the label position also high. Okay,  that looks better. Let's do some formatting for   the colors. So, I'm going to go with this shade  of brown, and then select one of the bars, Ctrl+1   to open the formatting pane if it's not already  open. Mine's open, so I'm going to set the series   overlap to 100% and the gap width to 50. That's  just going to make the bars a bit wider. Next,   I'm going to select the budget columns, and in the  formatting pane, under fill, I want no fill, but I   want a border that's a solid line and a shade of  brown, and we'll make it a bit wider. Let's add a   chart title. This will be 'Expenses by Category'.  And let's just drag it over to the left,   and I'll move the legend to the top. Let's just  also position that on the same line as 'Expenses   by Category', that gives us a bit more room  for our bars. Okay, let's Ctrl+X to cut it out,   go to the report sheet, and paste it in. I'm just  going to hold down alt while I resize it a little   bit. Okay, the next chart shows your actual versus  budget expenses over time. So, to start off, I'm   going to copy and paste this pivot table, and then  switch out the category for the year and month. By   copying and pasting, I don't have to reapply the  number formats, and it's automatically connected   to the slicer. So, let's go and insert a column  chart. And I'll Ctrl+X to cut, and we're going to   go straight to the report sheet, and we'll format  it here, starting with right-clicking and hiding   all the field buttons. Then, I want to select the  vertical axis, and then in the formatting under   access options, I want values in reverse order,  that just brings the columns down to the bottom   of the chart. I'll select the columns, and we'll  set the series overlap to 100% and the gap width   to 50. Let's go up to the Design tab, and we'll  change the colors with the same palette. And then,   with just the budget selected, let's go into the  formatting, and I want no fill and a solid line.   We'll just make it wider. Okay, we need  to fix the horizontal axis because right   now it's overlapping the chart. So, let's go  and do that. Under labels, we want it high. Everything's back to front because we've flipped  the orientation because these are negative values. All right, let's add a chart  title. This is "expenses,"   and we'll just drag it across to the  left. We'll move the legend to the top,   and now we have a bit more space for our actual  columns. We'll make the chart a bit wider. Okay, the next chart is the "actual versus budget  income overtime" column chart. So, I'm going to   go back to the "Analysis" tab, and I'll start by  copying this pivot table, and I'll paste it here.   Now, I need to uncouple this pivot table from this  slicer. So, I'm going to right-click the slicer,   go into "report connections," and I'm going  to deselect "pivot table 3" (that was the   last one I added). Click okay, and now you  can see all the values have changed because   it's not filtered for expense. This one needs  its own slicer, so I'm going to insert a new   slicer for the category type, and this time it's  going to be income, and we'll resize it. Now,   I can insert a chart. Let's go again with  another column chart. Let's Ctrl+X to cut it out,   and we'll pop it in our report, and let me  resize it a little bit to take up the space. Okay, let's right-click, hide the field buttons,  change the color scheme on the design tab. Now,   instead of brown, I'm going to go with green.  This is income. Let's format the columns so   that they overlap 100%, and the gap width is  50. Then we'll just select the budget column,   let's change it to no fill, and again, we'll  add a solid border but this time in green. Okay, we'll add the chart title. This is  "income," and drag it across to the left   and move the legend to the top and across to the  right, and we'll make more space for our columns. All right, our last chart is the donut that  shows the proportion of income by source. So,   on the "Analysis" sheet, I'm going to copy this  pivot table because it's already connected to   my category slicer for income, and then we'll  go and edit it. So, instead of year and month,   and I also don't need budget, I just want my  actual by subcategory, and we can go ahead and   insert a donut chart. Let's Ctrl+X to cut  it out, and we'll place it in our report. Right, let's change the colors  for this one to green as well,   and I just need to go back to my pivot  table and let's sort this in descending   order or largest to smallest, and  then we'll go back to our donut. All right, let's get rid of these  fill buttons, and we'll also get   rid of the chart title and legend, and  instead, we're going to add data labels,   and I'm going to go into more options.  That's going to allow me to select what   I want to see. So, I want the category  name, the value, and not the leader lines. All right, let's also make the donut hole  smaller so we make the hole size only 50%,   and now I'll just left-click and  drag the labels up so they're close   to the item that they relate to but not on top of. All right, let's just make it a little  bit smaller. Okay, that's the last chart. Next is the personal profit and loss statement.  This is going to be one of your most valuable   tools enabling you to see which areas of  your income and expenses need attention. So,   I'm going to go to insert pivot table from  an external data source. This allows me to   choose the query that we built earlier for  the data table. I click open, and I want to   put this on this sheet and in the cell that's  currently selected, so I'll click okay. Let's   just move these charts across a little bit  so they're not overlapping the pivot table. Okay, here I want the category type and category  and the actual and the budget. And then on the   design tab, subtotals, I want to show all  subtotals at the bottom of the group. Next,   I need to go to the pivot table analyze  tab, and under Fields items and sets,   I want to add a calculated field, and  this is going to be for the variance,   and the formula is actual minus budget. And I'll  click add and okay, and now you can see I've got   a variance column added to my pivot table. Let's  rename the column headers, so this is going to be   my personal profit and loss. This is "actual,"  so just put a space at the front of actual,   and again for budget and variance, and this  just differentiates them from the field names. Now, I'm just going to left-click and drag  income to the top, and then on the design tab,   I'm going to add some blank rows after each  item. Now, I just need to add a calculated   item for the profit or loss. So, I need  to select one of the category type fields,   and then on the analyze tab, Fields items and  sets, calculated item, and here it's going to   be income surplus or deficit, and the formula  is simply income plus expense. Remember, our   expenses are negative values. Click add and okay,  and now we've got our new income surplus deficit. Click the minus button to collapse the  underlying accounts we don't need to see them,   and right-click and remove the grand total. Now,  on the pivot table analyze tab, I can turn off the   buttons, and then on the design tab, I'm going  to tone down the color scheme to make it a bit   more in keeping with my report. Right, we need to  format the numbers. So, let's go into the number   format custom, scroll to the bottom, select the  one we set up earlier, and rinse and repeat for   each column. Applying the formats to one field at  a time like this ensures that if the pivot table   grows, the formatting goes with it. Let's make  the profit and loss quicker to interpret with   some conditional formatting icons on the variance  column. So, with one of the cells selected on the   Home tab of the ribbon, conditional formatting  icon sets, I'm just going to go with this set   here and then click on this icon, and you want  all cells showing variance values for category. Now, I need to edit the rule, so back  in conditional formatting manage rules,   double click to edit it, and then down here,  here I need to change this value to zero and   the type is a number. So when the value is  greater than or equal to zero, it's positive,   and we change this to number as well. This  becomes zero, and in fact, I don't want an icon   for this level. So it's simply if it's greater  than or equal to zero, it gets a green tick,   and if it's less than zero, it gets a red  cross. Click okay and okay, and there we   go. Now currently our actuals are under budget  because we're looking at an entire year's worth   of budget but only up to October for our actual  values. So, we'll sort that out in a moment. Now it's a good idea to give each pivot table  a name, and you'll see why in a moment. So,   on the pivot table analyze tab  in the pivot table name box,   we're just going to rename this one  PT profit and loss. And then let's go   to our analysis tab and we'll quickly  rinse and repeat for the other ones. Okay, back on the report sheet. So  that you can filter the report for   different time periods, I'm going  to insert a timeline slicer. So,   I've got the profit and loss pivot table  selected on the insert tab. I'm going to   use timeline and I only have one field which is  date. Let's bring it up here into this space,   and I'll resize it slightly. Let's just change  the color quickly in keeping with our report. So now, I've got a slicer that I can filter  all the charts with but it's currently only   assigned to this pivot table. So, let's  right-click and go into report connections,   and here this is why naming the pivot tables was  so important. So, we want it to filter the profit   and loss and we want it to filter the expense  by category and the income donut. We don't want   the actual versus budget which are these two  column charts filtered because they already   show all the months. If you start filtering the  months, they kind of lose their context. So,   I always like to leave charts that show  data over time unfiltered. I'll click okay,   and now if I choose and only show up to  October, which is what we have actual data for,   and we look at the profit and loss, we can see  that we're under budget on our variable income.   We're over budget on discretionary spend and  transport, and overall we're $90 under budget. The last feature is the headline figures that give  you at a glance information about the health of   your finances. And these are just text boxes that  are linked to cells that I've formatted, and then   I've added an icon to make them easy to recognize.  You can add icons from the insert tab and then   icons. These are available with Microsoft 365, but  you could use any image and paste it into Excel. Right, let's go back to our file, and we'll  set them up. First on the analysis tab,   I've got a couple of rows here ready to go to  calculate my income surplus or deficit and my   actual versus budget variance. And these are  actually just going to link to the report page   and my profit and loss pivot table. Notice in  the formula bar, it inserts the get pivot data   formula for me. I don't have to do anything with  this, but this formula is super robust because   if the cell containing this value moves,  for example, if new categories get added,   this formula will follow the value wherever it  goes. So I'm happy to use these kinds of formulas. Let's press enter, and that's repeat and  go and grab the budget variance. All right,   we've got our two figures ready to work with.  Let's apply the formatting. Control 1, custom,   scroll to the bottom, and double-click. All right, by the way, you can use  these skills to wow your colleagues   at work with interactive reports. You can  get your skills up to speed quickly with   my Excel dashboard course linked  to in the video description. Next,   I want a message which states whether this is a  surplus or deficit. I'm just going to use an if   formula that says if this cell is greater than  zero, then return the text "income surplus,"   otherwise return the text "income deficit."  Close parenthesis, and we're in surplus. Let's do something similar for the actual  versus budget variance. So again, with if,   we're testing whether this cell is greater  than zero. If it is, then we're above budget,   otherwise, we're below budget. Close parentheses,  and that's done. Now technically, we could skip   these two formulas and directly reference the  profit and loss pivot table in this formula here,   but I just thought it would be easier for you  to follow if I put the values in here first. So back on the report sheet, I'm ready to insert  my text boxes. They're on the insert tab, shapes,   and they're on the basic shapes. Draw it in  roughly and with the outer edge of the text   box selected, type an equal sign in the  formula bar, and then go to the analysis   sheet and I'm selecting the cell that  has my IF formula in it. Press enter,   and now it comes through to the text  box. We'll just make it a bit bigger. Now let's quickly format it with no fill  and no outline so it disappears. All right,   I'm just going to hold down control and  copy that, and then we'll edit this,   so this one is going to reference the value on  the analysis sheet. Press enter there we go. All right, let's just format these both  centered, and next I just need an icon   that represents this. So, let's go insert  icons, and this is dealing with money,   so let's look for some money. All right,  let's use this one here. While I'm here,   I'm going to grab a couple more. So, I want  a target, and let's also look for a house,   this one. All right, so I've got three selected.  Let's insert them all and let's just resize them   so they're 1 cm by 1 cm, and I'll just move  them over there and this one up into position.   Let's format it in this dark brown, and I'm just  going to select the other two holding shift and   F4 to repeat the coloring. Okay, we can also  color this text in the same shade of brown,   and we might make it a little bit bigger. Okay,  that's our first headline figures done. I'm going   to fast forward while I insert the remaining  headline figures, and like magic, they're done. Now, I should point out that the expenses  text boxes are referencing the profit and   loss pivot table down here, and you'll notice I've  repositioned it slightly and reduced the size of   the donut chart. If you add more categories,  you'll need to update these references and,   of course, add another text box  and icon for the new category. Okay, now that your report is done,  let's run through how to update it   for next month's bank statement. I've got  some data here. Let's just pretend this is   next month. I'm just going to copy it,  and we'll go to the transactions sheet,   and remember we're putting it on the very next  line. So, I'm just going to Ctrl+V to paste it   in. Imagine you've already gone through and  assigned the subcategories. All I need to   do now is go to my report sheet, and on the  data tab of the ribbon, click refresh all. Now keep an eye out on the column charts here  because you'll see that the November data is   going to be populated, and we're done.  This updates all the queries and pivot   tables that feed the report, so you can  see it's super quick and easy to maintain. Banks often export transactions to CSV,  text, or Excel files, so if you'd like   to further automate the gathering of your  bank transactions, check out this video on   how to consolidate multiple files from a folder  with Power Query and bring them into Excel. And   if you'd like to see what else Power Query can  do, check out this video. Thanks for watching,   don't forget to like and subscribe,  and I'll see you in the next video.