
Quick start: Enter 20, 35 and 45 in cells B2 through B4, then try =SUM(B2:B4). The result should be 100.
Related guide: How to Organize Files and Folders in Google Drive
An Excel formula starts with an equals sign (=). Cell references such as B2 point to values in the worksheet, so calculations update when the source data changes.
Create a Simple Practice Sheet
Enter item names in column A, quantities in column B and unit prices in column C. Add a heading in row 1 and a few sample rows underneath.
1. Multiply Quantity by Price
In D2 enter =B2*C2 to calculate the value of the first item. Copy the formula down the column for other items.
2. SUM: Add Values
Use =SUM(D2:D6) to add values from D2 through D6. SUM is more reliable than typing many plus signs.
3. AVERAGE: Find the Mean
Use =AVERAGE(D2:D6) to calculate the average numeric value in the range. Blank cells are generally ignored.
4. COUNT and COUNTA
=COUNT(B2:B6) counts numeric cells, while =COUNTA(A2:A6) counts non-empty cells. Choose the one that matches your question.
5. MIN and MAX
=MIN(D2:D6) returns the smallest numeric value; =MAX(D2:D6) returns the largest.
6. IF: Make a Simple Decision
Use =IF(B2<5,"Reorder","Enough") to label a stock quantity. The result depends on the logical test in the first argument.
7. SUMIF: Add Values That Match a Condition
If category names are in E2:E6, =SUMIF(E2:E6,"Stationery",D2:D6) adds values only for rows labeled Stationery.
8. Absolute References
Use $ signs to lock a reference when copying formulas. For example, =D2*$G$1 multiplies D2 by a rate stored in G1.
9. Common Formula Errors
Check for misspelled function names, text stored as numbers, missing parentheses and incorrect ranges. Regional settings may require semicolons instead of commas.
Practice Project: A Small Shop Stock Sheet
Create a table with item, category, quantity, unit price and stock value. Calculate totals, highlight low stock and filter by category. This is a useful exercise for students and small businesses.
Worked Example: A Stationery Stock Register
Create a practice worksheet for a small stationery shop. Put these headings in row 1: A: Item, B: Quantity, C: Unit Price, D: Stock Value, E: Category. Enter the following fictional records:
| Item | Quantity | Unit price (TZS) | Category |
|---|---|---|---|
| Exercise book | 20 | 1,500 | Stationery |
| Pen | 35 | 500 | Stationery |
| A4 paper ream | 8 | 12,000 | Stationery |
| USB drive | 5 | 18,000 | Accessories |
| Folder | 12 | 1,200 | Stationery |
In D2, type =B2*C2 and copy down through D6. Your stock values should be 30,000; 17,500; 96,000; 90,000; and 14,400 TZS. In D7, =SUM(D2:D6) gives 247,900 TZS. These are invented training values, not live market prices.
Then use =AVERAGE(C2:C6) to find the average unit price, =COUNT(B2:B6) to count numeric quantity entries, and =COUNTA(A2:A6) to count populated item names. All these formulas work because the relevant ranges contain the expected type of data.
Add Decisions With IF and SUMIF
Try =IF(B2<10,"Reorder","Enough stock") in an empty column. When copied down, the result changes by row: the A4 paper ream and USB drive records show Reorder using this demonstration threshold. In a real business, choose the reorder level for each item according to demand and delivery time rather than using one number for everything.
Next, enter =SUMIF(E2:E6,"Stationery",D2:D6) to calculate the total stock value for stationery items only. Using the sample above, the result is 157,900 TZS. If Excel displays an error, check the spelling of "Stationery" and ensure your criteria range and sum range have equal dimensions.
Why Cell References Matter
A formula such as =B2*C2 updates automatically when you correct the quantity or price. If you hard-code =20*1500, the value will not respond when the stock changes. Relative references normally adjust when copied down, so D3 becomes =B3*C3.
An absolute reference stays fixed. If cell G1 contains a practice tax or discount rate, =D2*$G$1 keeps the rate cell fixed when you copy the formula. The dollar signs lock the column and row; they do not mean U.S. dollars.
A Simple Accuracy Checklist
- Keep headings and number formats consistent; use numeric values, not text such as "1,500 TZS" inside calculation cells.
- Confirm that your ranges include every relevant row and do not include the grand-total row twice.
- Format money as currency or use a custom number format for TZS if appropriate.
- Save the workbook under a meaningful name and keep a backup copy.
- Compare calculated totals with a manual check on a few entries before relying on the sheet for decisions.
Frequently Asked Questions
What is the difference between COUNT and COUNTA? COUNT counts numeric cells; COUNTA counts cells that are not empty, including text.
Why do some formulas use semicolons? Excel's argument separator can depend on regional settings; the logic remains the same.
Can I use the same formulas in Google Sheets? Most of the basic formulas in this guide also work there, although menus and some advanced features differ.
Official reference: Microsoft Support: Adding values in Excel.
Build a small practice sheet
Enter five weekly expenses in one column and a category beside each. Calculate the total, then change one expense and see what updates. Copy a formula down a column and check whether its cell references moved as intended.
Conclusion
Learn a few formulas thoroughly before moving to advanced functions. Accurate data entry and clear worksheet organization are just as important as calculations.