Life is hectic, and juggling dozens of apps for the "must-do" chores adds to the noise. Since I already work in Excel daily, I've expanded its use to automate three of my most boring tasks. These set-and-forget formulas make life easy, and they only take a few minutes to build.
Short on time? If you want to skip the build and start automating your life straight away, you can find a link to the completed Excel file at the bottom of this article.
The proportional bill splitter
In my spreadsheet below, when I enter a bill, the formula avoids awkward negotiations by generating a salary-weighted, self-adjusting split.
Accounting number format is applied to cells A2 and B2, as well as to the Total, Person A, and Person B columns in the table. The table is renamed "T_Expenses."
Achieving this requires only one formula to be typed into cell C5 and dragged into cell D5:
When I add an expense in the next row, because the data is formatted as an Excel table , all its formulas and formatting expand automatically.
Here's how the formula works.
Part 1: The proportion
The first part of the formula calculates person A's percentage of the total household income.
The dollar sign in Excel formulas defines whether a reference should change when you copy or drag a formula to a new cell. A$2 locks the row only, so when the formula is applied to rows further down the table, the salary row is always used. However, because there's no dollar sign in front of the A, the column is relative, so when you drag the formula from column C to column D, the A will correctly change to B. In the sum part, all rows and columns are locked because they don't need to change for any calculations.
While typing your formula, press F4 to cycle through the dollar sign combinations ( absolute, mixed, and relative references ).
Part 2: The split bill
The person's proportion is multiplied by the total bill to calculate the final amount.
This syntax ensures the reference to the Total column doesn't move when you drag the formula to the right. Here's a breakdown of what's happening:
-
The @ sign: The implicit intersection operator tells Excel to look only in the current row of the Total column.
-
The brackets and colon:This is the part that locks the reference to ensure it stays glued to the Total column.
-
The table name:As soon as you move from a simple structured reference to a more complex one, Excel needs you to add the table name.
Part 3: The other person
Finally, click and drag the fill handle in cell C5 into cell D5.
The smart shopping list
In this worksheet, as soon as I update a number in the Stock column, the Action column shows what to buy and how many.
Here's the formula I use to make this happen:
Yes, it looks complicated at first, but it's actually logical and intuitive.
Part 1: The inventory gap
The formula starts with a logic gate that decides if any action is needed at all. If your target is higher than your stock, you need to buy more. If it's not, the formula jumps right to the end and returns "-."
Part 2: The calculation
If you're under-stocked, Excel builds the first part of an instructional sentence. The math calculates the difference between what you want and what you have, then pairs it with your unit of measurement. The ampersands (&) act as a glue, stitching the numbers and words together into a single string of text.
Part 3: The grammar
This is the longest part of the formula. Its job is to decide whether to add a plural suffix to your unit (like turning "Box" into "Boxes") or to leave it alone. First, it checks if you're buying more than one. If you only need one, it adds an empty string ("") to keep the word singular. However, if you need two or more, it looks at the last letter of the words in the Unit column. If it ends in an o, s, or x, the formula chooses "es." Otherwise, it adds a simple "s."
This handles most plurals, though it may miss irregular ones. If high accuracy is needed, use a lookup table or add more rules to the formula.
Part 4: The final string
Now that the unit plurality is sorted, the final part adds the word "of" and pulls in the item name:
The subscription tracker
In this spreadsheet, once I've entered the subscription details, Excel tells me how many days remain until the next payment is due.
The formula is as follows:
Accounting number formatting is applied to the Cost column, Short Date formatting to the First column, and Number formatting (with zero decimal places) to the Days column. I also added conditional formatting drop-down options (Annual and Monthly) to the Period column.
Here's how it works.
Part 1: The toggle
The formula starts by identifying the billing cycle. If the period is monthly, Excel runs the first calculation. If it's anything else (yearly), it runs the second.
Part 2: The monthly engine
This calculates the next monthly anniversary. It calculates the number of months since your very first payment and adds an extra month if today's date has already passed your usual billing day. This ensures the date it finds is always in the future.
Part 3:The yearly engine
Similar to the monthly engine, this looks at the years passed. It creates a temporary date for this year's anniversary—if that date has already passed, it adds 12 months to find next year's date.
Part 4: The countdown
Once the formula has found the correct future renewal date, it subtracts the current date from it. This converts a calendar date into a number of days.
While crafting formulas yourself can be satisfying, to jump straight in, download a free copy of the completed worksheet . After you click the link, you'll find the download button in the top-right corner of your screen. When you open the file, you'll find each formula on a separate worksheet tab.
