Excel · Power Query · IBAN-Test API

Validate IBANs in Excel with Power Query

Send an IBAN list from Excel to the IBAN-Test API and load validation results, bank names and BICs into a table—without VBA macros.

This guide targets Excel for Microsoft 365 on Windows. Set up a Power Query query once, then refresh results from the Data menu. You need an IBAN-Test account, an API token and sufficient request quota.

Download the Excel starter package

The ZIP includes an XLSX workbook with input and settings tables, the complete Power Query code and a quick-start file. The query is not embedded in the workbook: paste it into Advanced Editor once during setup.

Download starter package (ZIP)

Version 1.0 · 21 September 2026 · no macros

Example status: The XLSX structure and M syntax have been checked, and the API request has been compared with the published API specification. A complete test in Microsoft Excel with authenticated API calls is still pending. Start with a small set of test data.

Before you start

  • Excel for Microsoft 365 on Windows with Power Query. This guide does not establish compatibility with Excel for the web or Excel for Mac.
  • An API token: Sign in to IBAN-Test. See the API documentation for access details and pricing for request quotas.
  • Permission to process your data: IBANs are sent to IBAN-Test. Use only data that you are authorised to process this way.

1. Prepare the workbook and IBAN list

Extract the ZIP and open iban-test-excel-en.xlsx. Replace the sample rows on the Input sheet with your own list. Reference is your internal identifier; only the IBAN is sent to the API.

Keep the column names Reference and IBAN, and the table name IBAN_Input. Add new rows inside this Excel table. Store IBANs as text. The query removes whitespace and converts letters to uppercase.

On the Settings sheet, replace YOUR_API_TOKEN with your token. The table is named IBAN_Settings and its column is ApiToken. The token is saved in the file. Do not share this workbook with your token in it.

2. Add the Power Query code

  1. In Excel, select Data → Get Data → From Other Sources → Blank Query.
  2. In Power Query Editor, open Home → Advanced Editor.
  3. Open iban-validation.pq from the ZIP in a text editor. Copy its entire contents and replace the existing code in Advanced Editor.
  4. Select Done and name the query IBAN_Results.

The preview can already send API requests. The example initially allows 10 distinct non-empty IBANs per evaluation. This is not a guarantee of the number of HTTP requests per click: Power Query may evaluate a query more than once.

3. Configure API access and privacy

When Excel requests credentials for https://www.iban-test.eu, choose Anonymous. Your API token is still sent: the query explicitly sets the Authorization: Bearer … HTTP header. Do not choose the Web API credential type for this example.

The query sends a POST request to /api/v2/iban/validate. The Microsoft Web.Contents documentation states that POST requests in this workflow require the Anonymous data-source credential type.

Set workbook and web-source privacy levels according to your organisation's rules. A Formula.Firewall message concerns the combination of data sources. Review Power Query privacy levels; do not disable privacy protection globally.

4. Load and refresh the results

Select Close & Load To… → Table → New worksheet. Do not overwrite the input or settings table. For subsequent checks, edit the Input sheet and select Data → Refresh All.

ColumnMeaning
Reference / IBANYour original identifier and input.
IBAN_NormalizedUppercase IBAN with the specified whitespace removed.
StatusVALID: code 2100 with no API error. INVALID: business validation code 3100, 3101 or 3102. MISSING: empty input, no API request.
Code / MessageAPI response code and explanation.
Bank / BICBank name and BIC, where returned by the API.
CheckedAtUTCUTC time of query evaluation. Empty for missing inputs.

Identical normalised IBANs are deduplicated within an evaluation. Results are joined back to all matching input rows, retaining your input order.

Handle errors correctly

  • Token or credentials: Check your token and saved permissions in Data → Get Data → Data Source Settings. The API URL must use Anonymous credentials.
  • Quota, inactive access or server failure: The refresh stops. These operational problems must not produce an INVALID result.
  • Old results after a failed refresh: Excel may retain the previous successful table. Check refresh status and CheckedAtUTC before relying on results.
  • More than ten distinct IBANs: Check your remaining API quota before increasing MaxUniqueIbans in the query. Allow for preview requests and repeated evaluations.

What a valid IBAN does not prove

Validation checks the IBAN and provides available bank details. It does not confirm that a specific account exists, identify its owner, establish its balance or guarantee a successful payment. A valid checksum is not an account-holder verification.

Frequently asked questions

Can I validate an IBAN with an Excel formula?

Format and checksum checks can be implemented with formulas; checking the length alone is insufficient. This guide uses the API to retrieve available bank details and differentiated validation codes as well.

Is the Excel template free?

The download is free. API usage depends on your plan and available quota. Refreshes and previews can consume requests.

How do I share the results?

Copy only the required result values into a new file. Exclude tokens, queries and unnecessary IBANs. Hiding the Settings sheet does not protect your token.

View the complete Power Query code
let
    // Read only the two named tables supplied with the starter workbook.
    Settings = Excel.CurrentWorkbook(){[Name="IBAN_Settings"]}[Content],
    RawToken = Text.Trim(Text.From(Settings{0}[ApiToken])),
    ApiToken = if RawToken = "" or RawToken = "YOUR_API_TOKEN" then
        error Error.Record("Configuration", "Enter your API token in IBAN_Settings before refreshing.", null)
        else RawToken,
    Input = Excel.CurrentWorkbook(){[Name="IBAN_Input"]}[Content],
    AsText = Table.TransformColumnTypes(Input, {{"Reference", type text}, {"IBAN", type text}}),
    Normalized = Table.AddColumn(AsText, "IBAN_Normalized", each
        if [IBAN] = null then "" else Text.Upper(Text.Remove([IBAN], {" ", "#(tab)", "#(cr)", "#(lf)", Character.FromNumber(160)})), type text),
    UniqueIbans = List.Distinct(List.Select(Normalized[IBAN_Normalized], each _ <> "")),
    // Explicit cap for the starter example. Increase only after checking your quota.
    MaxUniqueIbans = 10,
    ToCheck = if List.Count(UniqueIbans) > MaxUniqueIbans then
        error Error.Record("Quota protection", "This example checks at most 10 distinct IBANs per refresh. Check your API quota before increasing MaxUniqueIbans.", null)
        else UniqueIbans,
    CheckIban = (iban as text) as record =>
        let
            Response = Web.Contents("https://www.iban-test.eu", [
                RelativePath = "api/v2/iban/validate",
                Headers = [Authorization = "Bearer " & ApiToken, #"Content-Type" = "application/json", Accept = "application/json"],
                Content = Json.FromValue([iban = iban]),
                Timeout = #duration(0, 0, 0, 30),
                ManualStatusHandling = {400, 404, 408, 422, 429, 500, 502, 503, 504, 509}
            ]),
            HttpStatus = Record.FieldOrDefault(Value.Metadata(Response), "Response.Status", 200),
            Body = if HttpStatus <> 200 then
                error Error.Record("API request failed", "HTTP " & Text.From(HttpStatus) & ": check authentication, quota or service availability. Refresh was stopped.", null)
                else Json.Document(Response),
            CheckedBody = if not Value.Is(Body, type record) then
                error Error.Record("Unexpected API response", "The response was not a JSON object.", null)
                else Body,
            Code = try Number.From(Record.FieldOrDefault(CheckedBody, "code", null)) otherwise null,
            IsValid = Code = 2100 and Record.FieldOrDefault(CheckedBody, "error", true) = false,
            IsInvalid = List.Contains({3100, 3101, 3102}, Code),
            Status = if IsValid then "VALID" else if IsInvalid then "INVALID" else
                error Error.Record("Validation unavailable", "API code " & (if Code = null then "missing" else Text.From(Code)) & ". Check your token, quota and API documentation. This is not an invalid-IBAN result.", null),
            RawDetails = Record.FieldOrDefault(CheckedBody, "details", []),
            Details = if Value.Is(RawDetails, type record) then RawDetails else [],
            // Force status evaluation before returning metadata.
            Result = if Status = "VALID" or Status = "INVALID" then [
                Status = Status,
                Code = Code,
                Message = Record.FieldOrDefault(CheckedBody, "message", ""),
                Bank = Record.FieldOrDefault(Details, "bankName", null),
                BIC = Record.FieldOrDefault(Details, "bic", null)
            ] else error "Unexpected validation status"
        in
            Result,
    // Evaluate distinct IBANs in sequence. An operational error aborts the refresh.
    CheckedRecords = List.Accumulate(ToCheck, {}, (state, iban) =>
        let
            PreviousReady = List.Count(state) = 0 or List.Last(state)[Status] <> "",
            Current = if PreviousReady then CheckIban(iban) else error "Previous check failed",
            Ready = Current[Status]
        in
            if Ready = "VALID" or Ready = "INVALID" then
                state & {Record.Combine({[IBAN_Normalized = iban], Current})}
            else error "Check failed"),
    Checked = Table.Buffer(Table.FromRecords(CheckedRecords,
        type table [IBAN_Normalized = text, Status = text, Code = nullable number, Message = nullable text, Bank = nullable text, BIC = nullable text])),
    Joined = Table.NestedJoin(Normalized, {"IBAN_Normalized"}, Checked, {"IBAN_Normalized"}, "Check", JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Joined, "Check", {"Status", "Code", "Message", "Bank", "BIC"}),
    MissingMarked = Table.ReplaceValue(Expanded, null, "MISSING", Replacer.ReplaceValue, {"Status"}),
    CheckedAt = Table.AddColumn(MissingMarked, "CheckedAtUTC", each
        if [Status] = "MISSING" then null else DateTimeZone.RemoveZone(DateTimeZone.FixedUtcNow()), type nullable datetime)
in
    CheckedAt

Connect your list to IBAN-Test

Choose a quota that fits your list size and refresh frequency. Refer to the API documentation for the full interface specification.

Set up API accessRead the API documentation

Bank data validation

Bank directory checks for 40 countries

IBAN-Test goes beyond checksum-only validation. For these countries, we check the bank identifier against available bank directories and return bank details such as institution, city and BIC when available. See how IBAN validation works.

ALAlbania ADAndorra ATAustria BEBelgium BGBulgaria HRCroatia CYCyprus CZCzech Republic DKDenmark EEEstonia FIFinland FRFrance DEGermany GIGibraltar GRGreece HUHungary ISIceland IEIreland ITItaly LVLatvia LILiechtenstein LTLithuania LULuxembourg MTMalta MDMoldova MEMontenegro NLNetherlands MKNorth Macedonia NONorway PLPoland PTPortugal RORomania SMSan Marino RSSerbia SKSlovakia SISlovenia ESSpain SESweden CHSwitzerland VAVatican City

IBAN syntax check

Format checks for 115 IBAN formats

Check IBANs for a valid structure, correct length and checksum. This helps you catch typos and transposed digits before a payment fails. Additional bank validation against real bank directories is available for the countries listed above.

MCP Server for AI Agents – Connect AI clients to IBAN validation tools – https://www.iban-test.eu/mcp – API documentation