Entropy Calculation in Excel

This repository provides an Excel LAMBDA function to calculate the Shannon entropy of a text string. I find it useful for quickly identifying high-entropy (potentially random or password-like) descriptions among user account descriptions exported from BloodHound (via Neo4j CSV export).

Background

In Active Directory enumeration (e.g., using BloodHound), user account descriptions can contain a variety of information, including notes, comments, or even embedded passwords. By computing the Shannon entropy of each description, we can flag unusually high-entropy strings that may warrant further investigation.

Low entropy (~1–2 bits/char): typical natural language.
Moderate–high entropy (~3–4 bits/char): mixed-case, alphanumeric strings.
Very high entropy (>4 bits/char): truly random or cryptographic strings (e.g., passwords).

Excel LAMBDA Function

Use the following LAMBDA in Excel (Belgian locale) to compute entropy:

=LAMBDA(txt;
  LET(
    n; LEN(txt);
    chars; MID(txt; SEQUENCE(n); 1);
    uniq; UNIQUE(chars);
    bins; SEQUENCE(ROWS(uniq));
    freq; FREQUENCY(MATCH(chars; uniq; 0); bins) / n;
    -SUM(IF(freq; freq * LOG(freq; 2); 0))
  )
)

Setup

In Excel, open Formulas → Name Manager → New….

Enter:
- Name: Entropy
- Refers to: (paste the LAMBDA above)
Click OK.

Example usage

Export user descriptions from BloodHound to CSV (via Neo4j) and import in Excel
Assume description is in column B (starting at B2). In C2, enter:
```
=IF(B2="null";0;ENTROPY(B2))
```
and fill down to compute entropy for each row.
To flag high-entropy descriptions (e.g., > 4 bits):
```
=IF(ENTROPY(B2) > 4; "⚠️ Investigate"; "")
```

Interpretation