If you're looking for a productive way to spend a few hours with Excel this weekend, these three projects fit the bill.They're straightforward to build, but you'll still pick up useful skills along the way.So, let's get started.
Automate your invoice tracking to stop chasing overdue payments Know exactly who owes you money and when it's due If you regularly send invoices, keeping track of payments can quickly become difficult.This project introduces Excel tables, data validation, conditional formatting, and SUMIF formulas in a way that's approachable for beginners while producing a spreadsheet you'll genuinely use.Step 1: Set up the invoice table Start by creating a table that contains all the key details for each invoice: In row 5, enter the headers ID, Client, Issue, Due, Amount, Status, Overdue, and Notes.
Select cells A5:H6, press Ctrl+T, and check My table has headers.In the Table Design tab, choose a Table Style where only the header row is colored, and rename the table T_Invoices.In the Home tab, format the Issue and Due columns as Date.
Format the Amount column as Accounting.Enter a few sample invoices, but leave the Status and Overdue columns blank for now.Step 2: Add a status drop-down list A drop-down list makes it easier to update invoice statuses consistently: Select the Status column and open the Data tab.
Click the Data Validation icon.Choose List from the Allow menu.Type Paid, Unpaid into the Source field.
Click OK.Now, when you select a cell in the Status column, you can choose one of those two options.Step 3: Calculate overdue invoices automatically Next, you need to calculate how many days overdue each invoice is: Select the first cell in the Overdue column.
Enter the formula below.Press Enter to fill the formula down the table automatically.=IF([@Status]="Paid", 0, IF(TODAY()>[@Due], TODAY()-[@Due], 0)) Step 4: Highlight invoices that need attention Conditional formatting makes paid and overdue invoices easy to spot: Select all data rows in the table.
Go to Home > Conditional Formatting > New Rule.Choose Use a formula to determine which cells to format.Add the rule in the first row in the table below, then repeat the process for the rule in the second row.
Formula Formatting =$F6="Paid" Gray font =AND($F6="Unpaid", $G6>0) Red font Now, completed transactions are grayed out, overdue payments are in red, and all other upcoming payments are formatted normally.To add a new invoice later, start typing in the row directly beneath the table.Excel automatically expands the table and applies the existing formatting, formulas, and drop-down lists to the new row.
Step 5: Build a payment dashboard Finish the project by creating a simple summary section above the table: Enter Paid, Unpaid, and Overdue in cells A1:A3.Enter the following formulas in cells B1:B3.Format the results as Accounting.
Cell Formula B1 =SUMIF(T_Invoices[Status], A1, T_Invoices[Amount]) B2 =SUMIF(T_Invoices[Status], A2, T_Invoices[Amount]) B3 =SUMIF(T_Invoices[Overdue], ">0", T_Invoices[Amount]) With just a handful of formulas and formatting rules, you've created a spreadsheet that highlights overdue invoices and summarizes your payment status automatically.Microsoft 365 Personal OS Windows, macOS, iPhone, iPad, Android Free trial 1 month Microsoft 365 includes access to Office apps like Word, Excel, and PowerPoint on up to five devices, 1 TB of OneDrive storage, and more.$100 at Microsoft Expand Collapse Streamline your job hunt with a self-updating application log Keep track of opportunities without relying on memory When you're applying for multiple jobs, it's easy to lose track of who you've contacted, where you are in the hiring process, and when you should follow up.
This project uses tables, formulas, and conditional formatting to create a tracker that keeps everything organized in one place.Step 1: Create the application tracker Start by setting up a table that will store all your application details: In row 1, enter the headers Company, Role, Date Applied, Stage, Follow Up, Days Since Applied, and Notes.Select cells A1:G2, press Ctrl+T, and confirm that your dataset has headers.
Name the table T_JobApps and choose a light, unbanded table style.Format the Date Applied and Follow Up columns as Date.Your table is now ready, so you can enter a few sample applications, leaving the Follow Up and Days Since Applied columns blank for now.
For the Stage column, use Rejected, Applied, Interview, and Offer.Consider using data validation drop-down lists to standardize this column and speed up the entry process.Step 2: Add automatic follow-up formulas Next, add formulas that automatically schedule follow-ups for jobs you've applied for and calculate how long it's been since each active application was submitted: Column Formula Follow Up =IF([@Stage]="Applied",[@[Date Applied]]+7,"") Days Since Applied =IF([@Stage]<>"Rejected",TODAY()-[@[Date Applied]],"") Step 3: Color-code application stages Conditional formatting makes it much easier to scan your tracker and see where each application stands.
Select all the data rows in the table.Go to Home > Conditional Formatting > Manage Rules.For each of the following rules, click New Rule > Use a formula to determine which cells to format, paste the formula into the text box, and click Format to apply the formatting.
Formula Formatting =$D2="Rejected" Light orange fill =$D2="Applied" Light yellow fill =$D2="Interview" Light green fill =$D2="Offer" Purple fill With the formulas and formatting in place, your spreadsheet will automatically track follow-up dates, calculate how long applications have been active, and highlight each stage of the hiring process.Instead of digging through emails and job boards, you'll have a single place to manage your entire job search.Smarten up your shopping decisions with an automated comparison matrix Compare products side by side before spending your money When you're deciding between several products, comparing prices, features, and specifications can quickly become overwhelming.
This project uses tables, checkboxes, formulas, and filters to help you evaluate products objectively and narrow down your options.In this example, let's imagine you're shopping for a new laptop.You'll compare several models based on price and four features: a touchscreen, at least 16GB of RAM, a dedicated graphics card, and all-day battery life.
Step 1: Build the comparison table Start by creating a table that stores the products you're considering and the features you want to compare: In row 1, enter the headers Laptop, Price, Touch, 16GB+, GPU, Battery, Price Evaluation, and Feature Evaluation.Select cells A1:H2, press Ctrl+T, and confirm that the table has a header row.Name the table T_PriceComp.
Format the Price column as Accounting.Now, begin populating the table with several laptops and their prices.Step 2: Add feature checkboxes Next, add checkboxes so you can quickly indicate whether each laptop includes a particular feature: Select all the cells under the four feature columns.
Click the Checkbox icon in the Insert tab.Check some of the checkboxes so you can test the formulas you're about to enter.Step 3: Use formulas to evaluate prices and features The Price Evaluation formula uses the average price to determine whether a product is cheap, expensive, or reasonably priced, while the Feature Evaluation formula counts the number of checkboxes you check and returns a corresponding comment: Column Formula Price Evaluation =IFS([@Price] < AVERAGE([Price]) * 0.8, "Cheap", [@Price] > AVERAGE([Price]) * 1.2, "Expensive", TRUE, "Reasonable") Feature Evaluation =SWITCH(COUNTIF(T_PriceComp[@[Touch]:[Battery]], TRUE), 0,"Avoid", 1,"Poor option", 2,"Possible option", 3,"Good option", 4,"Excellent option") Step 4: Filter the results to find the best options Once you've entered several laptops, use the table filters to narrow down the list.
In the Price Evaluation filter menu, select only Cheap and Reasonable, and for Feature Evaluation, select only Good option and Excellent option.By combining formulas with Excel's built-in filtering tools, you can quickly identify laptops that strike the best balance between price and features.The same approach works for phones, TVs, appliances, cameras, and many other purchases where comparing several options can become difficult.
Just swap the feature column headings for the specifications you care about, and the spreadsheet will work in exactly the same way.Build confidence with Excel one project at a time These three projects prove that you don't need advanced formulas or years of spreadsheet experience to create something genuinely useful.Whether you're tracking invoices, organizing a job search, or comparing products before making a purchase, each setup helps you practice Excel fundamentals in a practical way.
Once you've worked through these projects, keep the momentum going with last weekend's personal library, home utility, and monthly budget trackers, which put many of the same Excel skills to work in different ways.
Read More
