BENTECH

Excel for Small Business: Building a Daily Sales Tracker

Master spreadsheet basics by building an automated daily sales tracking sheet with arithmetic formulas, autofill, range summation, cell formatting, and currency settings.

This beginner-friendly course guides learners through creating a functional retail sales tracker in Microsoft Excel from scratch. Starting from navigating Windows to open Excel, learners structure data columns, write dynamic arithmetic formulas that combine quantities and prices, replicate formulas with the autofill handle, compute monthly totals with the SUM function, format numbers with local currency symbols, and preserve work safely.

Section 1: Getting Started with Spreadsheet Structure and Basic Formulas

Learn how to launch Excel, navigate the spreadsheet grid, and create initial addition formulas.

Lesson 1.1: Launching Excel and Setting Up Column Headers

  • Windows Start menu navigation
  • Spreadsheet grid layout
  • Zoom adjustment

Microsoft Excel is a spreadsheet application used widely for numerical data entry, inventory tracking, and financial analysis. In Windows 10, Excel can be opened by browsing the Start menu, navigating to 'All apps', and locating the Microsoft Office directory. Once opened, a blank worksheet displays a grid of columns identified by letters (A, B, C...) and rows identified by numbers (1, 2, 3...). The intersection of a row and column forms a cell (such as B4 or C4). To make the workspace easier to view, the zoom control at the bottom right of the Excel window can be increased.

Practical work

  1. Launch Microsoft Excel on your computer.
  2. Adjust the view zoom to at least 120% using the zoom slider.
  3. Enter product headers 'Salt', 'Sugar', and 'Total' in row 3 across columns B, C, and D.

Lesson 1.2: Writing Addition Formulas and Using the Fill Handle

  • Formula prefix (=)
  • Cell references in addition
  • Autofill handle replication

Calculations in Excel require typing an equal sign (=) at the beginning of the cell. Typing an equal sign tells Excel to interpret subsequent inputs as cell coordinates, numbers, and arithmetic operators rather than plain text. To add values from two cells, reference their locations directly (e.g., =C4+B4). When you press Enter, Excel calculates and displays the result. Instead of retyping formulas across subsequent rows, click the target cell and hover over its bottom-right corner until the cursor turns into a black plus icon (+). Clicking and dragging this handle downward copies the formula to adjacent rows while updating row coordinates automatically.

Practical work

  1. Enter 1800 into cell B4 and 4000 into cell C4.
  2. In cell D4, write the formula =B4+C4 and press Enter to verify the sum of 5800.
  3. Drag the bottom-right autofill handle down 5 rows to populate the formula downward.

Section 2: Automating Daily Sales and Summary Reporting

Expand the spreadsheet into a complete daily sales log with compound formulas, range summation, cell styling, and file saving.

Lesson 2.1: Multi-Product Daily Revenue Calculations

  • Inserting table columns
  • Multiplication operator (*)
  • Compound line total formulas

In retail environments, daily revenue depends on quantities sold multiplied by unit prices across multiple products. To accommodate this data, new columns can be inserted by right-clicking existing column headers and selecting 'Insert'. To calculate total revenue from multiple products on a given day, combine multiplication and addition into a single line formula. For example, if Salt Quantity is in B4, Salt Price in C4, Sugar Quantity in D4, and Sugar Price in E4, the daily total formula is =B4*C4+D4*E4. Excel evaluates multiplication prior to addition according to standard order of operations.

Practical work

  1. Insert columns to structure your table as Date (A), Salt QTY (B), Salt Price (C), Sugar QTY (D), Sugar Price (E), and Total (F).
  2. Enter test data: Date '21/05/2020', Salt QTY 5, Salt Price 1800, Sugar QTY 2, Sugar Price 4000.
  3. In cell F4, enter =B4*C4+D4*E4 and verify that the line total calculates to 17000.
  4. Drag the autofill handle down through row 10.

Lesson 2.2: Monthly SUM, Table Formatting, and Saving

  • =SUM() function
  • Cell borders and alignment
  • Currency formatting
  • Workbook saving

To calculate grand totals across a range of cells, use the =SUM() function rather than manually adding dozens of individual cells. For example, typing =SUM(F4:F20) calculates the sum of all values in cells F4 through F20. To make financial reports clear and professional, formatting tools should be applied. Cells can be highlighted and decorated with 'All Borders' from the font toolbar. Header cells can be merged across columns using 'Merge & Center' and styled with bold fonts. Values can also be formatted as Currency via the Number format dropdown or the Format Cells dialog, allowing you to choose local currency symbols (such as UGX) and adjust decimal precision. Finally, save your workbook using File > Save As, selecting a target directory such as the Desktop and assigning a descriptive filename.

Practical work

  1. Create a grand total cell and enter =SUM(F4:F20) to sum your daily totals.
  2. Select your data table and apply 'All Borders' from the Home ribbon.
  3. Merge header cells above the table, apply bold text, and label it 'Monthly Sales'.
  4. Format the total revenue cell with Currency formatting, setting 0 decimal places.
  5. Save the spreadsheet via File > Save As to your Desktop with the name 'My Business Database'.