Microsoft Excel isn’t just for work. In fact, by leveraging just three single, powerful formulas, I use the popular spreadsheet software at home to track warranties, manage my budget, and ensure I never forget a birthday. Thanks to Excel, I’ve ditched my chaotic life for one that’s highly organized. There’s nothing more frustrating than realizing a key household item is failing, only to later find out you missed the warranty expiration by a few days. To avoid this, I use the FILTER function to track when product warranties expire, so I can check the condition of my products well in advance. In my spreadsheet below, when I enter a month and year into cells G2 and G3, respectively, the formula checks the Warranty column of the T_Receipts table for matching dates and automatically populates the lookup table with a spilled dynamic array. Achieving this requires only one formula, which I entered into cell F6: =FILTER(T_Receipts,(MONTH(T_Receipts[Warranty])=MONTH(DATEVALUE(G2&”1″)))*(YEAR(T_Receipts[Warranty])=G3),”No items”) Here’s how the formula works. Part 1: Initiating the FILTER function The FILTER function filters an array based on certain criteria. In this case, the array is a table named T_Receipts: FILTER(T_Receipts Part 2: Specifying the filtering criteria The second part of the formula is the inclusion criteria, and since I need it to consider the month and the year, it consists of two segments. The first segment looks for dates in the Warranty column whose month matches the month in cell G2. However, because the full month name is used in cell G2, it needs to be converted into a month number. First, the month in cell G2 is concatenated with 1 to create a date string (such as January 1). Then, the DATEVALUE function converts the string to a numeric value, which the MONTH function extracts from the date. Then, it compares that target month number to the month number of every warranty date in the Warranty column of the T_Receipts table: (MONTH(T_Receipts[Warranty])=MONTH(DATEVALUE(G2&”1″))) The second segment extracts the year from every date in the Warranty column of the T_Receipts table and checks if it’s equal to the year in cell G3: (YEAR(T_Receipts[Warranty])=G3) Use data validation drop-down lists to ensure the dates match the format the FILTER function expects. Also, because Excel treats dates as serial numbers, be sure to select the date column in the lookup table and change the number formatting to “Date.” Multiplying these two segments together forces Excel to treat them as a single logical test. Only when both segments return TRUE (represented as 1 in Excel’s logic) will the multiplication equal 1. Then, any rows where 1 is the result are extracted into the filtered list. Categorize and track a budget Managing my personal finances used to mean manually comparing my spending against my budget for various categories, but this was unreliable and time-consuming. Now, I use a single Excel formula to automate this comparison and give me instant feedback on my financial health. As you can see in the screenshot below, this formula is invaluable because it tells me if I’m within, near, or over my budget for each category in a given month. Here’s the formula I entered into cell G2, which I then expanded to cell G8 using autofill: =LET(Spend, SUMIF(T_Budget[Category], E2, T_Budget[Cost]), IFS(Spend>F2,”Over budget”, Spend=F2,”Budget hit”, Spend>(F2*0. 9),”Near budget”, TRUE,”Within budget”)) Let’s break the formula down to understand what’s going on. Part 1: Defining the Spend variable The LET function is one of Excel’s most versatile functions, as it lets me name a result and refer to it multiple times. This makes the final formula easier to parse and faster to execute. In this case, I’m calling the result Spend: =LET(Spend Now, I need to define what the Spend variable represents. To do this, I’ll use the SUMIF function to calculate the total cost for the specific budget category in question: =LET(Spend, SUMIF(T_Budget[Category], E2, T_Budget[Cost]) T_Budget[Category]: This is the range to check. E2: This is the criterion. It checks if the category in the table matches the category name in cell E2. T_Budget[Cost]: This is the range to sum. If the categories match, the corresponding value in the Cost column is added. Because I used the LET function, the Spend variable holds the total amount I’ve spent for the category listed in cell E2. Part 2: Determining the budget status The final part of the LET function uses the calculated Spend variable inside the IFS function to determine the status. The IFS function evaluates multiple conditions from left to right until one returns TRUE. Then, it returns the corresponding value: IFS(Spend>F2,”Over budget”, Spend=F2,”Budget hit”, Spend>(F2*0. 9),”Near budget”, TRUE,”Within budget”) Spend>F2,”Over budget”: This is the first test. If the total spend is greater than the budget limit in cell F2, the formula stops and returns “Over budget.” Spend=F2,”Budget hit”: If the first test isn’t met, Excel moves onto the second test. In this case, it checks whether the spending exactly matches the limit in cell F2 and, if so, returns “Budget hit.” Spend>(F2*0. 9),”Near budget”: If neither of the first two tests is met, I want Excel to check whether the spending for a category exceeds 90% of the budget in cell F2. If this is the case, it returns “Near budget.” TRUE,”Within budget”: The final test is the catch-all-if my spending hasn’t triggered any of the other three tests, the formula returns “Within budget.” Work out the next time it’s someone’s birthday If, like me, you’re terrible at tracking birthdays, Excel can help you out. This table tells me how many days are left until each person’s birthday: This is the formula I constructed in cell C2. Because I entered it into an Excel table, it duplicated down the Days column automatically when I pressed Enter: =IF(DATE(YEAR(TODAY), MONTH([@Birthday]), DAY([@Birthday]))https://www.howtogeek.com/microsoft-excel-formulas-organize-my-daily-life/