Excel formulas become easier when they are grouped by the job they solve. This cheat sheet covers calculations, conditional totals, error handling, lookups, text, dates and dynamic arrays. The examples use English function names and commas as argument separators. A local Excel installation may use a different separator, while the calculation logic remains the same.
Contents:
How to read an Excel formula
Every formula begins with an equals sign. Cell references tell Excel where to take a value from, and operators describe the calculation. For example, =B2*C2 multiplies quantity by rate.
Relative references move when a formula is copied. Absolute references stay fixed: $A$1 locks both the column and row, $A1 locks the column, and A$1 locks the row. This distinction matters when one tax rate, markup or lookup range is reused across many rows.
Arithmetic and totals
Basic arithmetic covers most small estimates and expense trackers. If quantity is in B2 and rate is in C2, the line amount is =B2*C2. Addition, subtraction and division use +, - and /.
- =SUM(D2:D200) adds a range.
- =AVERAGE(C2:C200) calculates the average rate.
- =MIN(C2:C200) and =MAX(C2:C200) show the lower and upper values.
- =COUNT(D2:D200) counts numeric cells.
- =COUNTA(D2:D200) counts non-empty cells.
- =ROUND(D2,2) rounds a value to two decimal places.
Choose the rounding point deliberately. Rounding every row may produce a different result from rounding the final total.
Conditional calculations
Conditional formulas are useful when a table contains work categories, projects, contractors or cost items.
- =SUMIF(A:A,"Materials",D:D) totals rows marked Materials.
- =COUNTIF(A:A,"Labour") counts labour rows.
- =AVERAGEIF(A:A,"Delivery",C:C) calculates the average delivery rate.
- =SUMIFS(D:D,A:A,"Materials",B:B,"Project 12") totals rows that meet two conditions.
- =COUNTIFS(A:A,"Materials",E:E,">0") counts rows that meet two conditions.
Logic and error handling
Logic functions turn operating rules into repeatable calculations. They can keep a row blank until quantity is entered, check several conditions or replace a formula error with a clear result.
- =IF(B2="","",B2*C2) calculates only when quantity is present.
- =AND(A2<>"",C2>0) checks two conditions together.
- =OR(A2="Materials",A2="Labour") accepts either category.
- =IFERROR(expression,"") returns a blank cell when the expression fails.
Long chains of nested IF functions are difficult to audit. Intermediate columns and a clean reference table usually make the workbook safer.
Lookups and reference tables
A reference table can hold item codes, work descriptions, units and standard rates. The working sheet then stores an item code and retrieves the matching data.
- =VLOOKUP(A2,Rates!A:D,4,FALSE) looks in the first column and returns a value from column four.
- =INDEX(Rates!D:D,MATCH(A2,Rates!A:A,0)) combines a flexible return range with an exact match.
- =XLOOKUP(A2,Rates!A:A,Rates!D:D,"Not found") returns a match without a hard-coded column number.
XLOOKUP is available in current Microsoft 365 and recent Excel versions. INDEX with MATCH remains useful for workbooks that must support older versions.
Text, dates and dynamic arrays
Text functions help clean imported data. Date functions support reporting periods and planned completion dates.
- =LEN(A2) returns the number of characters.
- =LEFT(A2,5), =RIGHT(A2,5) and =MID(A2,3,4) extract part of a string.
- =TRIM(A2) removes extra spaces.
- =TEXT(A2,"dd-mm-yyyy") formats a date as text.
- =TODAY() returns the current date, and =NOW() returns date and time.
- =DATE(2026,8,17) builds a date from year, month and day.
Current Excel versions also support dynamic arrays. =FILTER(A2:D200,A2:A200="Materials","") returns matching rows, =SORT(A2:D200,4,-1) sorts by the fourth column, and =UNIQUE(A2:A200) returns distinct values.
A small estimate in Excel
- Create columns for category, description, unit, quantity, rate, amount and notes.
- Enter =D2*E2 in the amount column and copy it down.
- Add =SUM(F2:F200) for the overall total.
- Use SUMIF to calculate totals by category.
- Use XLOOKUP or INDEX with MATCH to retrieve rates from a reference sheet.
- Add IFERROR only where missing values or division can produce an expected error.
As the number of workbooks grows, version control and formula integrity become harder to manage. A shared construction workspace can keep estimates, actual expenses, approvals and project records together. The English 101 platform includes estimate management and project planning tools at 101-app.com/en.