Budget Planning Basics in Microsoft Excel
Please Comment and Share
Getting Started Planning Your Budget
Before you go any further, I have to tell you the I really love Excel and all that it can do. I consider myself a complete novice because it can do some truly amazing things. I am always happy to share my knowledge with you. You can always send me a message if you need some help, and I will do my best to put together a how-to quick guide for you.
If you are uncomfortable with computers or have no idea how to use Excel, this may sound daunting at first and a paper and pencil method may seem easier, and I completely understand that. I have helped many people who had never used Excel before do this and once they did it for their first time, they were amazed at how simple it really is.
A good way to examine your budget is to download your actual transaction history from your online banking profile. After logging into your banking profile, you should be able to select an account and have the option to download the information. You can select the type of file format you want to save the information in. If Excel is not an option, you can select .csv and Excel will extrapolate the data into the correct format for you.
Disclosure - this article may contain affiliate links for which I may receive compensation for their use. See full disclosure/disclaimer here: Disclaimer/Disclosure – Stylin Spirit (stylin-spirit.com)
Here is a sample account. While looking at expenditures for two months gives you an idea of how to plan your budget, looking at an entire year provides insights that are extremely valuable and may give insight into spending categories you may not remember.
Somethings that may not reveal themselves when only looking at two months of your expenses include medical deductible costs, prescription costs, bank fees, membership fees, interest income, returned bills, subscription fees, natural fluctuations in utility costs due to seasonal change and average increases the companies apply that aren’t based upon consumption. These budget components are essential if cash flow, the flow of income compared to the flow of expenditures, are an issue.
A tool in excel called “Pivot table” is a quick and automatic way to summarize your detail data. The first step is to click insert and select Pivot Table from the options.
This pop-up will appear on the screen. You will want to make sure that all your columns and rows are included in the Table Range. If you don’t have blank lines in your detailed data excel will make the correct selection automatically. Then click OK.
This is the next screen that will appear. This is where you may design how the summary report will look. If you have multiple accounts or want to look at specific date ranges, you can add those to the Filters or Columns section. (For more guidance on using excel, please check out my other blog post).
Here is how the Pivot Table report will look. You have a lot of options here. For more detailed guides on using Pivot Tables please check out my other blogs.
If you want to change how the numbers look you can simply click on the Sum of Amount button and go to the Value Field Settings.
Here you can see that you have many choices. If you wanted to see how many times you made a payment on a particular line item you could select Count. Since this example is only looking at the total amounts spent, I am going to select Number Format.
I prefer to look at things rounded to the nearest dollar so I selected accounting and reduced the decimal places down to zero. It added the dollar sign automatically. I really enjoy working in excel!
I am not concerned with my opening balance and don’t want it to get in my way, I have clicked on the drop down arrow on the Row Labels and unchecked that box. I don’t want to overwhelm you with too much customization at this point, and I hope you can see how much you can customize what you see and how you see it.
Now every single transaction that went through your bank account is summarized for you! Way to go! I hope it didn’t take too long for you. Please check out my other blogs for next steps and Excel How To Quick Guides! Happy budgeting… Flare your budget!