Module 1: Excel Basics
Navigating the Spreadsheet
Excel organises data in a grid of rows (numbered 1, 2, 3…) and columns (lettered A, B, C…). Each cell is identified by its column letter and row number.
| Cell | Meaning | Example Content |
|---|
| A1 | Column A, Row 1 — the first cell | Project Name |
| B3 | Column B, Row 3 | 500000 |
| D12 | Column D, Row 12 | =B12*C12 |
💡 Tip: Press Ctrl + End to jump to the last cell with data in your sheet.
Entering and Editing Data
- Click a cell and type to enter data. Press Enter to confirm and move down, or Tab to move right.
- Press F2 to edit a cell without overwriting it. Use arrow keys to position the cursor within the cell.
- Press Ctrl + Z to undo any change.
- Right-click a cell to access Format Cells, Insert, Delete, and other options.
Cell Formatting
Proper formatting makes financial data readable:
- Number format: Ctrl + 1 → Number → choose decimal places
- Currency: Select cells → Home → Accounting ($) format
- Percentage: Select cells → Home → % button (or Ctrl + Shift + %)
- Date: Enter as DD/MM/YYYY; Excel stores dates as serial numbers
💡 For climate data: Use the Number format with 2 decimal places for monetary values and 4 decimals for emissions factors (tCO₂/unit).
Ranges and Selection
A range is a group of cells, written as A1:C10 (top-left to bottom-right).
- Click and drag to select a range
- Ctrl + Click to select non-adjacent cells
- Ctrl + Shift + Arrow to extend selection to last non-empty cell
- Ctrl + A to select the entire sheet
Module 3: Lookup & Reference Functions
VLOOKUP
Searches for a value in the first column of a table and returns a value in the same row from another column.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
// Example: Find the emission factor for "Coal"
=VLOOKUP("Coal", $A$2:$C$20, 3, FALSE)
// ↑ ↑ ↑ ↑
// what where to look col exact match
💡 Always use FALSE for the last argument when you need an exact match (which is almost always in financial work).
XLOOKUP (Modern Excel)
The modern replacement for VLOOKUP — more flexible and can look in any direction.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])
// Find emission factor for a project type
=XLOOKUP(D2, $A$2:$A$20, $C$2:$C$20, "Not found")
// Returns "Not found" if the project type doesn't exist
INDEX MATCH
A more powerful two-formula combination that works in any direction and doesn't require the lookup column to be first.
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
// Return CO₂ factor where project name matches D2
=INDEX($C$2:$C$20, MATCH(D2, $A$2:$A$20, 0))
💡 Use INDEX MATCH when your lookup column is to the right of the return column — VLOOKUP can't do that.
Practical Example: Emissions Calculator
| A — Project | B — MWh | C — Emission Factor | D — tCO₂e |
|---|
| Solar Farm A | 12,000 | =VLOOKUP(A3,$F$2:$G$5,2,FALSE) | =B3*C3 |
| Wind Farm B | 8,500 | =VLOOKUP(A4,$F$2:$G$5,2,FALSE) | =B4*C4 |
| Total | =SUM(B3:B4) | | =SUM(D3:D4) |
Module 5: Pivot Tables
What is a Pivot Table?
A Pivot Table is an interactive summary tool that lets you quickly reorganise and analyse large datasets without writing formulas. Essential for analysing emissions data, project portfolios, and financial reports.
Creating a Pivot Table
- Ensure your data has column headers and no blank rows or columns.
- Click anywhere inside your data range.
- Go to Insert → PivotTable → choose "New Worksheet" → click OK.
- In the PivotTable Fields panel, drag fields to: Rows, Columns, Values, or Filters.
- Right-click any Value → Value Field Settings to change SUM to COUNT, AVERAGE, etc.
Climate Finance Pivot Example
Suppose you have a project database with columns: Sector, Country, Year, Investment_USD, tCO2_Avoided
- Rows: Sector → Values: Sum of Investment_USD → gives total investment by sector
- Rows: Country, Columns: Year → Values: Sum of tCO2_Avoided → gives avoided emissions by country over time
- Add Filters: Year to quickly filter the whole table by year
💡 Refresh data: After updating your source data, right-click the pivot → Refresh. The pivot does NOT update automatically.
Calculated Fields
Add custom formulas inside a pivot without touching the source data.
- Click anywhere in the PivotTable.
- Go to PivotTable Analyze (or Options) → Fields, Items & Sets → Calculated Field.
- Name the field (e.g., "Cost per tCO₂") and enter the formula:
=Investment_USD/tCO2_Avoided
- Click Add → OK. The new field appears in your PivotTable Fields list.
Module 6: Climate Finance Models in Excel
Carbon Cost Model
Calculate the total carbon cost for a project under a carbon pricing scheme:
// Setup:
A1: Project Name B1: Annual Output (units)
A2: Emission Factor B2: 2.5 // tCO₂ per unit
A3: Carbon Price B3: 50 // $/tonne
A4: Annual Output B4: 10000 // units/year
// Formula in B5:
=B2*B3*B4 // Annual carbon cost = $1,250,000
// 10-year projection in C1:L1:
=B5*(1+0.05)^(C1-1) // 5% annual price escalation
Green Bond Cash Flow Model
// A: Year | B: Coupon | C: Principal | D: Total CF | E: PV Factor | F: PV
A2: 1 B2: =B1*$D$1 C2: 0 D2: =B2+C2 E2: =1/(1+$E$1)^A2 F2: =D2*E2
A3: 2 B3: =B2 C3: 0 D3: =B3+C3 E3: =1/(1+$E$1)^A3 F3: =D3*E3
...
A7: 5 B7: =B2 C7: =$D$1 D7: =B7+C7 E7: =1/(1+$E$1)^A7 F7: =D7*E7
// Bond Value:
=SUM(F2:F7) // Present value of all cash flows
// Greenium test: compare with conventional bond at higher yield
Blended Finance Dashboard
// Input section:
B2: Total Project Cost = 10,000,000
B3: Concessional Finance = 2,000,000
B4: Private Finance = =B2-B3
B5: Leverage Ratio = =B2/B3 // 5x
// Return metrics:
B7: Annual Revenue = 1,500,000
B8: Annual Opex = 400,000
B9: EBITDA = =B7-B8
B10: DSCR = =B9/(B4*0.08) // 8% debt service rate
B11: IRR = =IRR(C2:C12) // range of annual cash flows
💡 Use data validation: Set dropdown lists (Data → Data Validation → List) for inputs like project type or country to avoid spelling errors that break VLOOKUP.
Emissions Tracking Dashboard
// Scope 1, 2, 3 tracker:
B2: Scope 1 (tCO₂e) = 1500
B3: Scope 2 (tCO₂e) = 850
B4: Scope 3 (tCO₂e) = 12000
B5: Total GHG = =SUM(B2:B4)
B6: Revenue ($M) = 45
B7: Intensity = =B5/B6 // tCO₂e per $M revenue
B8: Base Year Intensity= 350 // 2019 baseline
B9: % Reduction = =(B8-B7)/B8*100 // vs baseline
// Conditional formatting on B9:
// Green if > 0 (improvement), Red if < 0 (deterioration)