Good habits before any formula
Marks in Excel assignments depend as much on structure as on correct answers. Five habits prevent most problems.
- Keep inputs separate. Put assumptions such as prices, rates and tax in labeled input cells, not inside formulas. Change an input and everything updates.
- Use cell references, not typed numbers. A formula such as =B2*1.08 hides a tax rate. Put 8 percent in a cell and use =B2*(1+$F$1).
- Be consistent. A column should contain the same formula in every row, so it can be filled down without errors.
- Label everything. Headings, units and a short notes area explain what the model does.
- Check as you go. Add a check cell, such as totals that must equal another total, so errors show up immediately.
To let a marker see your formulas, press Ctrl + ` (the backtick key) to toggle formula view, or add a column that shows them as text.
Relative, absolute and mixed references
This is the single most useful concept to master. When you copy a formula, relative references shift and absolute references stay fixed.
| Type | Looks like | When copied down or across | Use when |
|---|---|---|---|
| Relative | B2 | Changes row and column | Calculating row by row |
| Absolute | $B$2 | Never changes | Pointing at a single input, such as a tax rate |
| Mixed (column fixed) | $B2 | Row changes, column stays | Multiplying a column of values by a row of rates |
| Mixed (row fixed) | B$2 | Column changes, row stays | Multiplying a row by a column of values |
Press F4 while the cursor is in a reference to cycle through the four types. A classic error is copying =B5*F1 down a column, which makes the tax rate move to F2, F3 and so on. Write it as =B5*$F$1.
Core calculation and summary functions
| Task | Formula | Example result |
|---|---|---|
| Total | =SUM(E2:E6) | Adds a range |
| Average | =AVERAGE(E2:E6) | Mean of a range |
| Middle value | =MEDIAN(E2:E6) | Median |
| Count of numbers | =COUNT(E2:E6) | Counts numeric cells |
| Count of non-blank | =COUNTA(A2:A6) | Counts text or numbers |
| Highest or lowest | =MAX(E2:E6), =MIN(E2:E6) | Extremes |
| Round | =ROUND(E2,2) | To two decimals |
| Count with a condition | =COUNTIF(B2:B6,"North") | How many rows match |
| Sum with conditions | =SUMIFS(E2:E6,B2:B6,"North",A2:A6,"Widget") | Adds only matching rows |
| Average with a condition | =AVERAGEIFS(E2:E6,A2:A6,"Gadget") | Mean of matching rows |
| Sum of products | =SUMPRODUCT(C2:C6,D2:D6) | Units times price, summed |
Sample data and results
| Product | Region | Units | Price | Revenue (=C x D) |
|---|---|---|---|---|
| Widget | North | 120 | 25 | 3,000 |
| Widget | South | 80 | 25 | 2,000 |
| Gadget | North | 60 | 40 | 2,400 |
| Gadget | South | 90 | 40 | 3,600 |
| Widget | East | 50 | 25 | 1,250 |
- Total revenue =SUM(E2:E6) gives 12,250, and =SUMPRODUCT(C2:C6,D2:D6) gives the same result in one step.
- North revenue =SUMIFS(E2:E6,B2:B6,"North") gives 5,400.
- Widget revenue =SUMIFS(E2:E6,A2:A6,"Widget") gives 6,250.
- Number of North rows =COUNTIF(B2:B6,"North") gives 2.
- Average Gadget revenue =AVERAGEIFS(E2:E6,A2:A6,"Gadget") gives 3,000.
Logical functions
IF lets a spreadsheet make decisions. Its structure is =IF(test, value if true, value if false).
| Task | Formula |
|---|---|
| Simple test | =IF(E2>=3000,"High","Low") |
| Several conditions at once | =IF(AND(C2>=100,D2<=30),"Bulk","Standard") |
| Either condition | =IF(OR(B2="North",B2="East"),"Priority","Normal") |
| Multiple outcomes | =IFS(E2>=3000,"A",E2>=2000,"B",TRUE,"C") |
| Trap and replace errors | =IFERROR(E2/C2,0) |
IFS needs Excel 2019 or later. For older versions nest IF functions: =IF(E2>=3000,"A",IF(E2>=2000,"B","C")). Always test the boundaries, such as exactly 3,000, and decide whether the cut-off is greater than or greater than or equal to.
Lookup functions
Lookups pull a value from a table based on a key, such as a price from a price list. XLOOKUP is the modern choice, and VLOOKUP and INDEX with MATCH are widely taught.
| Function | Syntax | Notes |
|---|---|---|
| XLOOKUP | =XLOOKUP(lookup_value, lookup_range, return_range, "Not found") | Looks in any direction, exact match by default, Excel 365 and 2021 |
| VLOOKUP | =VLOOKUP(lookup_value, table, column_number, FALSE) | Key must be the left column; FALSE means exact match |
| INDEX and MATCH | =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)) | Flexible and works in all versions |
The most common VLOOKUP mistake is leaving off the final FALSE, which makes Excel use an approximate match and return wrong values without an error. Approximate matches are useful for tiered tables, such as tax bands or commission rates, where the table is sorted in ascending order.
A tiered commission (hypothetical)
Commission rates: 2 percent on sales from 0, 3 percent from 10,000 and 5 percent from 25,000. Put the thresholds (0, 10,000, 25,000) in one column and the rates beside them. For sales of 18,000 in B2:
=VLOOKUP(B2, $F$2:$G$4, 2, TRUE) returns 3% =XLOOKUP(B2, $F$2:$F$4, $G$2:$G$4, , -1) returns 3% (-1 means next smaller) Commission =B2 * rate = 18,000 x 3% = 540
The same result with nested IF is =IF(B2>=25000,0.05,IF(B2>=10000,0.03,0.02)). A lookup table is easier to update and to audit, which markers like.
Working on this assignment now? Get a price for help with your paper.
Get an instant quoteFinancial functions
| Task | Formula | Notes |
|---|---|---|
| Loan payment | =PMT(rate/12, years*12, loan) | Returns a negative number because it is a payment |
| Future value | =FV(rate, nper, pmt, pv) | Sign convention: outflows negative |
| Present value | =PV(rate, nper, pmt, fv) | |
| Number of periods | =NPER(rate, pmt, pv, fv) | |
| Interest rate | =RATE(nper, pmt, pv, fv) | |
| Net present value | =NPV(rate, future_cash_flows) + initial_outlay | NPV assumes the first cash flow is at period 1 |
| Internal rate of return | =IRR(all_cash_flows_including_time_0) | Needs at least one negative and one positive flow |
| Straight-line depreciation | =SLN(cost, salvage, life) |
See our guides on time value of money and capital budgeting for how these functions fit into problems.
Statistical functions
| Task | Formula |
|---|---|
| Sample standard deviation | =STDEV.S(range) |
| Sample variance | =VAR.S(range) |
| Correlation | =CORREL(x_range, y_range) |
| Regression slope and intercept | =SLOPE(y_range, x_range), =INTERCEPT(y_range, x_range) |
| R-squared | =RSQ(y_range, x_range) |
| Predicted value from a line | =FORECAST.LINEAR(x, y_range, x_range) |
| t-test p-value | =T.TEST(range1, range2, 2, 3) |
| Normal probability | =NORM.S.DIST(z, TRUE) or =NORM.DIST(x, mean, sd, TRUE) |
The Data Analysis ToolPak (switched on under Add-ins) produces descriptive statistics, regression tables, t-tests and ANOVA in one go. It gives static output, so rerun it if the data change.
Lay out a model so someone else can follow it
A tidy workbook earns marks. A common layout has three areas, either as separate sheets or clearly marked blocks.
| Area | Contents | Tips |
|---|---|---|
| Inputs and assumptions | Every number that could change, with units and a source note | Use one color for inputs, such as blue text, and keep them together |
| Calculations | Formulas that use the inputs, row by row | One formula per column, filled down, no typed numbers |
| Outputs | Summary tables, charts and the answer to the question | Link to calculations and label clearly |
| Checks and notes | Reconciliations, error flags and a short description | A check that shows OK or ERROR is easy to review |
- Freeze header rows View, Freeze Panes, so labels stay visible.
- Format numbers Currency, percentages and decimals matched to the data.
- Data validation Restrict inputs to sensible values using drop-down lists.
- Conditional formatting Highlight exceptions, such as negative cash balances.
- Name key ranges Named ranges such as TaxRate make formulas readable.
- Protect formulas if asked Lock the calculation cells and leave inputs editable.
Fixing common error messages
| Error | Meaning | Typical fix |
|---|---|---|
| #DIV/0! | Division by zero or an empty cell | Check the denominator, or wrap in IFERROR or IF |
| #N/A | A lookup found no match | Check spelling, spaces and data types, and use an exact match |
| #REF! | A reference was deleted | Undo the deletion or rebuild the formula |
| #VALUE! | Wrong kind of data, such as text in a calculation | Convert text to numbers; check for stray spaces |
| #NAME? | Excel does not recognize a name or function | Check spelling and whether the function exists in your version |
| ##### | The column is too narrow | Widen the column |
A circular reference warning means a formula refers to itself. Trace precedents with the Formula Auditing tools. Another frequent hidden problem is numbers stored as text, which look fine but do not add. Look for a small green triangle and convert them.
If you want help building or checking a model, you can order an Excel assignment and upload your instructions and any starting file.