Step-by-step builds you can reuse at work: budgets, invoices, trackers, schedules and simple reports.
14 articles
To build a gradebook in Excel, list students in column A and one assignment per column. AVERAGE gives each student's mean score, or SUMPRODUCT with a row of weights gives a weighted mark. IFS turns the mark into a letter grade, for example 90 or more is an A. AVERAGE, MIN and MAX under each column summarise the class.
To build a monthly budget in Excel, list your income and total it with SUM. List each spending category with a Budget column and an Actual column, then subtract to get the difference. Total both columns, and subtract total spending from total income to see what is left. SUMIF can fill the Actual column from a list of purchases.
To build a commission calculator in Excel, put each rep's sales in a column and the rate in a cell, then multiply. For tiered rates, either use IFS with the highest tier first, or list the tiers in a sorted table and use VLOOKUP with TRUE to find each rate. Multiply sales by the rate, and use MIN to apply any cap.
A KPI summary sheet is a small block of formulas that reads a data table and shows the few numbers your manager asks about. Use SUMIFS for totals, COUNTIFS for counts, a ratio for rates and (new - old) / old for percent change. Put the month in one input cell so every KPI updates when you change it.
To build a sales report in Excel, start from one row per order and add a Revenue column (units times price). Then build a summary next to it: SUM for total revenue, SUMIFS for revenue by region, rep or month, MAX for the biggest order and a share-of-total column. A pivot table gives the same totals with no formulas.
An employee lookup sheet has a staff table and a small lookup card. You type an employee ID into one cell, and XLOOKUP formulas fill in that person's name, department and other details from the table. Add "Not found" as XLOOKUP's fourth argument so a wrong ID shows a clear message instead of #N/A.
To calculate overtime in Excel, first get hours worked: =(End-Start)*24 turns a time difference into decimal hours. Total the week with SUM, then use =MAX(0,Total-40) or =IF(Total>40,Total-40,0) for overtime hours. Pay is regular hours times the rate plus overtime hours times the rate times 1.5, rounded to cents with ROUND.
To create an invoice in Excel with formulas, keep a price list on its own sheet and use VLOOKUP to bring each item's description and price into the invoice from its code. Multiply quantity by price for each line, add the lines with SUM, work out tax with ROUND so it shows exact cents, and add a due date by adding the payment terms to the invoice date.
To make a project timeline in Excel, list each task with a start date and a length in working days. WORKDAY works out the end date and skips weekends and holidays, and the next task starts one working day later. Then add date columns across the top and a conditional formatting rule that colors the cells between each task's start and end.
A weekly shift schedule in Excel is a grid with one row per person and one column per day, filled with short shift codes such as E (early), L (late) and OFF. Type the week's start date once and build the other dates with +1. COUNTIF totals each person's shifts, and COUNTIFS checks that every day has enough people and a supervisor.
To make an attendance tracker in Excel, put names in column A and dates across row 1, then type a code in each cell: P for present, A for absent, L for late. COUNTIF counts each code per person, and dividing by the days recorded gives an attendance rate. Conditional formatting colors absences and low rates automatically.
To make an inventory tracker in Excel, keep two tables: a product list with each item's opening stock and reorder level, and a log with one row for every delivery or sale. SUMIF adds the stock in and out for each product, so current stock is opening plus in minus out. An IF formula and conditional formatting flag items to reorder.
A price list in Excel needs three formulas per product. Sale price is =ROUND(B2*(1-C2),2), the list price less its discount. Tax is =ROUND(D2*$I$1,2), with the tax rate in one cell locked with $. Final price is sale price plus tax. Keep the rates in their own cells so one change updates every row.
To make an expense tracker in Excel, log one expense per row with its date, description, category and amount. Then total each category with SUMIF, for example =SUMIF(C:C,"Travel",E:E), and add a month condition with SUMIFS. A pivot table gives the same category and month totals in a few clicks, and both update when you add expenses.