Excel for Beginners: Build a Multi-Currency Expense Tracker

To track expenses in different currencies, record each original amount and currency, then convert it to one reporting currency before calculating a total. Excel can do the calculation, but the result is reliable only if you also check the rate direction, imported data types and dates.

This guide builds a small tracker from a blank workbook. The example codes and rates are illustrative, not market data.

1. Open a workbook and learn the grid

Open Excel, choose Blank workbook and save the file. The desktop version is useful for this exercise; menu names and features can vary between versions.

A workbook is the whole file. A worksheet is a page inside it, selected using the tabs at the bottom. The ribbon at the top groups commands under tabs such as Home and Data.

The grid has lettered columns and numbered rows. Their intersections are cells: A2 is in column A, row 2. Click a cell to make it active, type an entry and press Enter. The formula bar shows the active cell's contents.

Excel distinguishes between text, numbers and dates. A currency code is text; an amount should be a number; a date should be a date value. An imported amount can look like a number while being stored as text, which can prevent calculations.

2. Enter expenses and a separate rate reference

Use AAA and BBB as illustrative original-currency codes and CCC as the reporting-currency code. Replace them with the appropriate standard currency codes in a real tracker.

Enter these headings in row 1:

  • A1: Date
  • B1: Description
  • C1: Original currency
  • D1: Original amount
  • E1: Rate to CCC
  • F1: Amount in CCC
  • G1: Rate source
  • H1: Rate effective date

Enter two practice expenses:

  • Row 2: an illustrative date, Travel, AAA, 100.
  • Row 3: another illustrative date, Supplies, BBB, 50.

Leave columns E–H blank for now. Enter dates in a form your Excel setup recognises, then check that Excel interpreted the intended day and month.

In an unused area of the same worksheet, make a rate reference:

  • K1: Pair; L1: Rate to CCC
  • K2: AAA/CCC; L2: 0.8
  • K3: BBB/CCC; L3: 1.2
  • K4: CCC/CCC; L4: 1

Use your setup's decimal separator when entering the rates. Each rate means units of CCC for one unit of the original currency. The last row lets an expense already in the reporting currency pass through unchanged.

For real work, check the pair's direction, the rate's source and its effective date. You cannot simply multiply the original amount by a rate quoted in the opposite direction.

3. Look up rates and calculate the total

Click E2 and enter:

=VLOOKUP(C2&"/CCC",$K$2:$L$4,2,FALSE)

This combines the original currency in C2 with /CCC, finds that pair in the first column of the reference range and returns its rate from the second column. FALSE asks for an exact match. If Excel rejects the formula, your setup may require semicolons instead of commas.

The dollar signs fix $K$2:$L$4 when you copy the formula. Drag the small square at the lower-right corner of E2 down to E3. The results should be 0.8 and 1.2. A #N/A result means the pair was not found: check the codes and the reference list rather than substituting a guessed rate.

In F2, enter =D2*E2, then copy it down to F3. The results should be 80 and 60. Every formula begins with =; * means multiply. Excel also uses +, - and / for addition, subtraction and division.

The references D2 and E2 are relative: when copied down, they become D3 and E3. To total the converted amounts, enter =SUM(F2:F3) in J2. The result should be 140. To calculate their average, use =AVERAGE(F2:F3) in another empty cell.

Do not total the original amounts as though they were one currency. The converted total is meaningful because both results use CCC.

Try an absolute reference

For a separate example, put an illustrative rate in unused cell N2 and enter =D2*$N$2 in N3. Copy the formula down: $N$2 stays fixed, while D2 changes.

Use this approach only for rows that genuinely share the same pair and rate. It would not be appropriate for both sample expenses above.

Preserve the rate used

The lookup is useful for learning and for estimates, but changing the reference rate changes every linked expense. For a reproducible historical record, copy each applicable rate and use Paste Special > Values in its rate cell. Record the source and effective date in columns G and H.

Confirm which rate and method your recipient requires. For financial reporting decisions in your circumstances, consult a qualified professional.

4. Format dates and amounts, then make an Excel Table

Select the date cells. On the Home tab, open the number-format dialog, choose Custom, enter yyyy-mm-dd and select OK. A recognised date can now display as, for example, 2026-04-15.

Formatting changes the display, not the underlying date. If Excel interpreted the day and month incorrectly, correct or reimport the value before applying the format.

Format the amount and rate cells as numbers using the Home tab. Keep original currency codes visible beside original amounts and the reporting currency in the converted-amount heading. A currency format changes the display; it does not convert an amount. One currency symbol applied to every original amount would be misleading.

Select A1:H3 and choose Insert > Table. Confirm the range and select My table has headers. The table adds filters and makes the expense list easier to extend.

Now replace the formula in F2 with a structured reference:

=[@[Original amount]]*[@[Rate to CCC]]

Instead of naming cells by address, this formula names the two columns in the current row. Excel can fill it through the table as a calculated column. Check that the results remain 80 and 60.

Try filtering Original currency, then clear the filter. Keep one expense per row and avoid merged cells or blank rows inside the data.

5. Import numbers and dates using the source convention

Regional settings affect decimal separators, date interpretation and formula argument separators. For example, 1,234 could represent a decimal value or a whole number with a grouping separator. If the source is unclear, ask the sender which convention applies.

A CSV is a text file whose fields are separated by a character, often a comma or semicolon. Keep the original unchanged and work on a copy.

If a row appears in one column:

  1. Select the column and choose Data > Text to Columns.
  2. Select Delimited, choose the source separator and inspect the preview.
  3. Select identifier columns in the preview and set them to Text to preserve leading zeros.
  4. Check the destination: splitting can overwrite cells to the right. Finish the wizard.

If an amount column is text, select it and run Data > Text to Columns again. Choose Delimited, proceed to the final step, select the amount column in the preview and open Advanced. Set the decimal and thousands separators to match the source, then finish. For example, for source text 1.234,50, set the decimal separator to a comma and the thousands separator to a period. Test the result with a calculation such as =D2*1.

For text dates, use Text to Columns on the date column, choose Date in the final step and select the source order, such as DMY or MDY. Then apply the yyyy-mm-dd display format. If dates were already converted incorrectly, return to the original text and reimport it; formatting cannot recover the intended date.

Formula separators are separate from CSV delimiters. =SUM(D2,D3) may need to be entered as =SUM(D2;D3) on another setup. A colon in D2:D3 specifies a range instead.

6. Decide whether to retrieve exchange rates

Excel's Currencies data type can retrieve rates for a pair written as From Currency / To Currency using standard currency codes. Select the pair and choose Data > Currencies, then insert the rate field. Availability depends on your Excel version and account; see Microsoft's instructions.

Check the direction and timing before using a retrieved rate. A current rate is not automatically appropriate for an older expense. When you need a reproducible record, store the rate used as a value with its source and effective date.

7. Share less data and investigate errors

Expense descriptions, names and receipts can reveal personal information. Decide what the recipient needs: if totals by category are enough, share those rather than the underlying records. Use approved storage and sharing methods, restrict access and agree when the data should be reviewed or deleted.

Replacing names with IDs reduces exposure but does not necessarily make records anonymous. A separate worksheet in the same workbook is not an access barrier. Create a separate, minimised file when someone should not receive the original records, and check local rules and your organisation's policies.

If a formula shows #VALUE!, check whether an amount or rate is text. For #REF!, inspect the formula for a deleted or moved reference. Do not hide errors with guessed values.

Next, practise filters, IF and XLOOKUP. Power Query helps when you repeatedly import files that need the same cleaning. For financial growth calculations, see personal finance maths; for querying larger record sets, try SQL for beginners.

The members' bonus contains a pseudonymisation exercise and a reusable rate-snapshot macro.