Quick Links
Excel is often seen as a complicated program for highly trained accountants, but this isn’t necessarily the case. I use Excel daily to keep track of my budgeting, choose the ideal vacation destination, track my team’s sports stats, and keep an eye on my weight-loss progress… and you may too!
1. Budgeting
With today’s never-ending cost-of-living increases, I use Excel to manage my budgeting and keep track of how much disposable income I have left over each month.
First, create an Excel table by typingType,Details,Cost,To Pay, andDatein cells A1 to E1.
Once you have input the data into columns A, B, C, and E,format your tableandgive your table a name. In my case, I’ve called it Budgeting.
Whenever you type a date in Excel, ensure thecell data type is set to “Date.“This will mean that Excel can use these dates to help you manage your budget automatically.
Now, in cell G1, typeToday’s Date, in cell G2, typeEnd of Month, and in cell G3, typeDays Left.
In cell H1, type the following formula to generate today’s date:
In cell H2, type the last date of the current month manually.
In cell H3, type the following formula to calculate the number of days remaining in the month:
We’re now ready to populate column D. In cell D2, type
This means that if the date of the payment item in row 2 is earlier than today’s date (in cell H1), we want Excel to mark it as Paid. If the date is later than today’s date, the total cost of that item will show in this cell. When you press enter after typing the formula, the rest of the column will populate automatically.
Finally, we can now use all this information to calculate our budget.
Underneath your formatted table, in column A, typeBank balance,Total expenditure,Total left to pay,Leftover, andDaily budgeton separate rows. Then, you will need to add totals or formulas to the corresponding cells in column B as follows:
Type your bank balance manually. You should update this cell each time you return to check your budget.
Then, in the cell next to Total expenditure, type
whereBudgetingis the name we assigned to our table, and[Cost]is the name of the column we’re totalling.
Now, in the cell next to Total left to pay, type
In the cell next to Leftover, type
whereB21is your manually-added bank balance, andB23is the cell containing the total left to pay.
In the cell next to Daily budget, type
whereB24is the total leftover, andH3is the number of days remaining in the month.
You now have a complete monthly budgeting spreadsheet. Simply duplicate the sheet each month to set up a new budget. Then, amend that month’s payment details, change the last day of the month in cell H2, and keep your bank balance updated.
2. Vacation Destinations With Different Criteria
With so many options for different vacation destinations, Excel can help you keep these in order. Yes, there are websites that can help you with some criteria, but how about those specific elements that matter to you, such as property with a fireplace, or a region with a dark sky for those of you who love stargazing? Whatever it is you’re looking for, use Excel to whittle down your options without having to pay that extra percentage for third-party booking commissions.
I love going on short stays around the UK, and this spreadsheet started off with a few notes about some of the properties that tickled my fancy. But as I stayed at more places, I started to realize what elements mattered to me the most, and my spreadsheet quickly expanded.
Here are some of the tools I benefit from the most when using my spreadsheet:
3. Tracking My Sports Team’s Stats
This is my favorite Excel workbook. Like with most spreadsheets, they started off really simple, and I’ve gradually added to them over time.
The best tools for creating these spreadsheets have been conditional formatting and charts.
As you can see in the screenshot above, I’ve usedconditional formattingto automatically apply color to cells based on their values (such as red for L, yellow for D, and green for W). To do this, I selected the whole of column D, opened “Conditional Formatting” in the Home tab, and clicked “New Rule.” I then clicked “Format Only Cells That Contain” and completed the details of the rule in the easy-to-follow dialog box.
On this sheet, I’ve used an in-built conditional formatting rule to create data bars. I selected column B, clicked “Conditional Formatting” in the Home tab, and hovered over “Data Bars.” I could then choose the data bar type that best aligned with my spreadsheet’s formatting. Then, I used the Format Painter tool to apply the same rules to the other columns, alternating between blue and green for clarity.
My sports team tracking workbook contains 12 tabs overall, so I created a dashboard sheet that pulls together the key information in chart form in one place. Click the “+” at the bottom of your workbook to add a new sheet, and double-click the new tab to rename itDashboard. Then, use the Charts group in the Insert tab on the ribbon tochoose different graphsthat will work well for your data. Finally, you canformat your data labelsand chart colors to make them look exactly how you want, andhide the gridlinesto add that extra presentational touch to your dashboard.
4. Tracking My Weight (I’m on a Diet!)
Who knew that Excel could be a weight-loss motivator?
Using Excel’s formatted table function and line chart, I created a spreadsheet to track my weight and show me how close to my target I am getting:
To add a new row for the next day’s data, right-click the bottom row of your table, hover over “Insert,” and click “Table Row Below.”
Once you start using Excel for your everyday dealings, you’ll undoubtedly find even more uses for this dynamic program, and usingthese little-known Excel functionscan help you to make your spreadsheets more adaptable to your needs.
