Turning a raw table of business data into answers, forecasts and automated reports with Microsoft Excel.

Excel is often treated as a place to store numbers, but its real strength is turning those numbers into decisions. This post walks through a complete mini-project covering the essential Excel skills: formulas, statistics, date calculations, loan repayments, PivotCharts, Goal Seek and macros. The example is a product development dataset, but the same steps apply to sales, inventory, projects or any other business data.

The Concept

Consider a company that develops several products each year. For every product, the start date, number of development days, cost, planned units, selling price and profit margin are recorded. The questions to be answered are:

• How much does a typical product cost to develop, and how much do costs vary?
• When will each product be delivered?
• What will the monthly repayment be if development is financed with a loan?
• Which categories and regions earn the most?
• What input value is needed to reach a given target?
• Can repetitive reporting work be automated?

Excel answers each of these with built-in tools, as the steps below show.

Step 1: Organise the Data as an Excel Table

Each record belongs in its own row and each attribute in its own column, with a single header row and no blank rows or columns. Selecting any cell in the range and pressing Ctrl + T converts it into an Excel Table.

A Table provides automatic filtering and sorting, banded rows, and formulas that fill down by themselves. It also expands when rows are added, so every formula, PivotTable and macro that points at it stays up to date.

Step 2: Apply Formulas and Statistical Functions

The first task is to add calculated columns, for example Revenue = Units × Price and Profit = Revenue × Margin − Cost. Statistical functions then summarise the data.

Question	Function
What is the typical value?	AVERAGE, MEDIAN
What are the extremes?	MIN, MAX, LARGE, SMALL
How spread out are the values?	STDEV, VAR, QUARTILE
How many items meet a condition?	COUNTIF, COUNTIFS
Totals and averages by group	SUMIF, SUMIFS, AVERAGEIF
Are two measures related?	CORREL, SLOPE, RSQ
Look up a record by its ID	INDEX with MATCH

Tip: Results should never be typed by hand. A formula keeps the answer current whenever the data changes.

Step 3: Calculate Delivery Dates

Adding days to a start date counts weekends, which does not reflect how real projects run. Excel has functions that respect working days:

• WORKDAY(start, days, holidays) returns the date after a number of working days, skipping weekends and any listed holidays.
• NETWORKDAYS(start, end, holidays) counts the working days between two dates.
• EDATE(date, months) moves a date forward by whole months, which suits warranty periods.
• EOMONTH(date, 0) returns the last day of the month.

Holiday dates are best kept in a separate list and referenced as a range, so that they are maintained in one place only.

Step 4: Work Out Loan Repayments

When a project is financed by a loan, the PMT function gives the regular payment:

=-PMT(annual_rate / payments_per_year, years * payments_per_year, loan_amount)

The loan amount, interest rate and term should sit in their own input cells so that they can be changed freely. A repayment schedule then follows, with one row per payment:

1. Interest = opening balance × rate per period
2. Principal = payment − interest
3. Closing balance = opening balance − principal

A good check is that the final closing balance comes to exactly zero.

Step 5: Summarise with a PivotTable and PivotChart

1. Click any cell inside the Table.
2. Go to Insert > PivotChart and place it on a new sheet.
3. Drag a category field to the Axis, a numeric field such as profit to Values, and a second field such as region to the Legend.
4. Add a status or year field to Filters, or insert a Slicer for one-click filtering.
5. Choose a chart type, then add a title and axis labels.

In a few clicks, the best-performing groups become visible with no formulas to write. A PivotTable does not update on its own, so Refresh should be pressed after the source data changes.

Step 6: Reach a Target with Goal Seek

Goal Seek works backwards: a desired result is chosen and Excel finds the input that produces it.

1. Go to Data > What-If Analysis > Goal Seek.
2. Set cell: the formula cell to be controlled (for example, the monthly payment).
3. To value: the target (for example, an affordable payment).
4. By changing cell: the input Excel may adjust (for example, the loan amount).

Typical uses include finding an affordable loan size, the interest rate that fits a budget, or the number of units needed to break even. The changing cell must hold a typed value, not a formula.

Step 7: Automate Repetitive Work with a Macro

A macro is a saved set of actions that Excel repeats with one click. The steps below record a macro that transfers selected records to another sheet:

1. Enable the Developer tab (File > Options > Customize Ribbon).
2. Click Record Macro, enter a name, and choose a shortcut key.
3. Filter the data to the required records, copy them, and paste the values into the destination sheet.
4. Clear the filter and click Stop Recording.
5. Save the file as an Excel Macro-Enabled Workbook (.xlsm).

A recorded macro repeats the exact clicks made during recording. A more robust approach is to write the same task in VBA. This example copies every row marked "Delivered" from a table to an archive sheet:

Sub TransferDelivered()
    Dim src As ListObject, arc As Worksheet
    Dim lr As ListRow, nextRow As Long

    Set src = ThisWorkbook.Worksheets("Data").ListObjects("tblProducts")
    Set arc = ThisWorkbook.Worksheets("Archive")

    arc.Range("A5:Q1000").ClearContents
    nextRow = 5

    For Each lr In src.ListRows
        If lr.Range.Cells(1, 13).Value = "Delivered" Then
            arc.Range(arc.Cells(nextRow, 1), arc.Cells(nextRow, 17)).Value = lr.Range.Value
            nextRow = nextRow + 1
        End If
    Next lr
End Sub

To run it, press Alt + F11, choose Insert > Module, paste the code, and run it from View > Macros.

Best Practices to Remember

• Inputs such as rates, dates and targets belong in clearly labelled cells that formulas refer to, rather than numbers typed inside formulas.
• Input cells are easier to spot when colour-coded differently from calculated cells.
• Results should be cross-checked, for example with a final loan balance of zero or a total that matches the PivotTable.
• Assumptions should be documented next to the data.
• Macros should be enabled only in files from a trusted source.

Conclusion

With one well-organised table, data can be described with statistics, work can be scheduled with date functions, financing can be planned with PMT, results can be summarised with a PivotChart, targets can be reached with Goal Seek, and routine tasks can be automated with a macro. These are the building blocks of practical data analysis in Excel, and they apply to almost any dataset.

Questions and favourite Excel tips are welcome in the comments below.