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

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

3. Enter:
   • Name: Entropy
   • Refers to: (paste the LAMBDA above)
4. Click OK.

Example usage

1. Export user descriptions from BloodHound to CSV (via Neo4j) and import in Excel

2. 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.

3. To flag high-entropy descriptions (e.g., > 4 bits):

   =IF(ENTROPY(B2) > 4; "⚠️ Investigate"; "")
   

Interpretation

• Sort or filter rows by entropy to find accounts with unusual or random-looking descriptions.
• Typical natural-language descriptions will have entropy around 1.5–2.5 bits/char.
• Strings containing embedded secrets (e.g., passwords, keys) often exceed 3.5–4 bits/char.