Text Analytics Toolbox: Modeling guide

Design corpus tables, configure stopword rules and sentiment lexicons, tune topic models, and scale text mining workflows in Excel.

← Text Analytics Toolbox overview · Getting started · Methods guide · Examples · Reference

Overview

The Text Analytics Toolbox models text corpora using a modular spreadsheet architecture. Rather than hardcoding text preprocessing parameters or dictionary lists into Python code, all corpus records, filtering thresholds, affective lexicons, and search queries reside directly in worksheet tables.

This guide details: - Designing and formatting corpus tables on Corpus. - Configuring rule types and scopes on Stopwords & Rules. - Exploring the four catalog model families. - Sizing, scaling, and memory guidelines for Excel workbooks. - Adding custom corpora and registering new analytical models.

Corpus table design & schema

Text documents are stored on the Corpus worksheet (Corpus!A5:D200). Each row represents an individual document, review, customer support ticket, abstract, or article.

Column schema

Column Header Type Required Description Example
A Model Text / String Yes Foreign key linking the document to a model registered in Start!D5:H9. Only rows matching Start!B6 are ingested during recalculation. customer_reviews_sentiment
B DocID Integer / String Yes Unique identifier for the document within the model’s dataset. Passed through to output tables for record tracking. 1, TICK-104, REV-89
C Category Text / String Optional Domain label, product classification, ticket category, or genre. Carried into results for grouping and validation. Reviews, Billing, Hardware
D Text Text / String Yes Unstructured textual content to analyze. Can contain punctuation, mixed case, sentences, and paragraphs up to 32,767 Excel characters. “Excellent product, superior build quality…”

Data formatting best practices

  1. Avoid Empty Text Cells: Blank or whitespace-only cells in the Text column are coerced to empty strings, which can distort average document length and topic distributions. Ensure all records contain substantive text.
  2. Text Normalization: Text can contain line breaks (CHAR(10) or Alt+Enter), contractions (don't, can't), and common punctuation. The engine automatically handles punctuation stripping and contraction splitting during tokenization.
  3. Partitioning by Model: Multiple distinct corpora can co-exist within the same Corpus table by tagging column A with different Model keys. Switching Start!B6 filters the table dynamically.

Preprocessing configuration & rule types

Natural language processing pipelines depend heavily on feature engineering. The Stopwords & Rules worksheet (Stopwords & Rules!A5:D50) provides a declarative configuration table:

[Stopwords & Rules!A5:D12]
Model                       | RuleType      | Value                                                | Description
----------------------------|---------------|------------------------------------------------------|------------------------------------------
ALL                         | Stopword      | the, and, of, to, in, a, is, for, on, it, with...    | Standard English function stop words
ALL                         | MinLength     | 3                                                    | Minimum token length filter
customer_reviews_sentiment  | PositiveWords | durable, ergonomic, sleek, flawless, intuitive...   | Custom domain positive affective lexicon
customer_reviews_sentiment  | NegativeWords | fragile, clumsy, sluggish, broken, unusable...       | Custom domain negative affective lexicon
keyword_tfidf_extraction   | NgramMax      | 2                                                    | Maximum n-gram order (1=unigram, 2=bigram)
document_similarity_search  | SearchQuery   | multi-factor authentication SSO login security       | Active search query for cosine similarity
support_tickets_topics      | Stopword      | ticket, issue, error, please, thanks, help           | Domain-specific stop words for tickets

Rule types reference

RuleType Allowed Models Value Data Type Default Description and Algorithmic Impact
Stopword ALL or Specific Model Comma-separated strings Built-in 318 English words Words removed during vectorization. Tokens are trimmed and case-matched according to the Case fold control. Model-scoped stopwords are unioned with ALL stopwords.
MinLength ALL or Specific Model Integer (\(\ge 1\)) 3 Filters out tokens with character lengths strictly less than this threshold via regular expression (?u)\b\w{min_length,}\b. Useful for stripping noise, single letters, and abbreviations.
PositiveWords Sentiment models Comma-separated strings Standard positive lexicon Domain-specific positive affective tokens. Matched against corpus words with contextual negation lookback.
NegativeWords Sentiment models Comma-separated strings Standard negative lexicon Domain-specific negative affective tokens. Matched against corpus words with contextual negation lookback.
NgramMax TF-IDF / Keyword models Integer (\(1, 2, 3\)) 1 Controls n-gram feature range (1, NgramMax). A value of 1 extracts unigrams only; 2 extracts both unigrams and adjacent word bigrams ("quantum computing", "solar cells").
SearchQuery Cosine / Similarity models Text / String Empty The active multi-word query string transformed into the document TF-IDF vector space for cosine similarity scoring.

Scoping rules: ALL vs Model-Specific

  • Model = "ALL": Applies universally across every task. Use this for general function words (the, of, and, in) and baseline minimum token length (MinLength = 3).
  • Model = "<TaskID>": Applies exclusively when the corresponding task is selected. For example, the token "ticket" is a critical domain term in customer service, but removing it in support_tickets_topics prevents it from dominating all three topic clusters.

Catalog model families

The toolbox organizes text mining into four distinct task families:

flowchart TD
    subgraph Catalog["Text Analytics Model Families"]
        Sentiment["Sentiment Family<br/>(Lexicon Scoring)"]
        Topics["Topics Family<br/>(Latent Dirichlet Allocation)"]
        Keywords["Keywords Family<br/>(TF-IDF N-grams)"]
        Similarity["Similarity Family<br/>(Cosine Vector Search)"]
    end

    Sentiment --> CR["customer_reviews_sentiment<br/>Polarity [-1, +1] & Negation"]
    Topics --> ST["support_tickets_topics<br/>Multinomial Dirichlet Clusters"]
    Keywords --> KW["keyword_tfidf_extraction<br/>Sublinear TF & Bigrams"]
    Similarity --> DS["document_similarity_search<br/>Normalized Query Vector Matching"]

1. Sentiment family (Sentiment)

  • Primary Algorithm: Rule-based lexicon dictionary matching with clause-aware negation inversion.
  • Output Metrics: Continuous sentiment score \(s \in [-1.0, 1.0]\), categorical polarity (Positive, Negative, Neutral), and dominant topic tag.
  • When to Use: Voice-of-customer surveys, Net Promoter Score (NPS) verbatim feedback, product reviews, and brand social media monitoring.

2. Topics family (Topics)

  • Primary Algorithm: Latent Dirichlet Allocation (LDA) fitted via variational Bayes on discrete word count matrices.
  • Output Metrics: Topic probability distributions across \(K\) topics, document-level dominant topic assignment, topic confidence score, and top topic terms.
  • When to Use: Unsupervised discovery of recurring themes, root-cause categorization of customer support tickets, warranty incident logs, and open-ended survey clustering.

3. Keywords family (Keywords)

  • Primary Algorithm: Term Frequency-Inverse Document Frequency (TF-IDF) with sublinear logarithmic scaling (\(1 + \ln(\text{tf})\)) and smooth IDF.
  • Output Metrics: Highest-ranking discriminatory unigrams and bigrams per document, and corpus-wide key terms.
  • When to Use: Indexing academic abstracts, extracting patent keywords, contract clause summarization, and tag generation.

4. Similarity family (Similarity)

  • Primary Algorithm: Cosine similarity \(\cos(\mathbf{q}, \mathbf{d})\) in an \(L_2\)-normalized TF-IDF vector space.
  • Output Metrics: Similarity score \(\in [0.0, 1.0]\), relevance tier (High (≥0.4), Moderate (0.2–0.4), Low (<0.2)), and matched query terms.
  • When to Use: Knowledge base retrieval, finding relevant technical articles, deduplicating incoming customer tickets against resolved issues, and regulatory document search.

Sizing and scaling guidelines

The Text Analytics Toolbox is engineered for decision-scale text collections running interactively inside Microsoft Excel:

Dimension Tested Baseline Recommended Limit Operational Considerations
Document Count (\(n\)) 8 – 12 docs Up to 1,000 docs Excel recalculation takes < 1 second for 500 documents. Above 1,000 docs, vectorization and LDA convergence time increases linearly.
Document Length 10 – 30 tokens Up to 500 tokens Short to medium texts (reviews, tweets, tickets, abstracts) perform best. For lengthy documents (> 1,000 words), split into paragraph rows.
Vocabulary Size (\(V\)) 60 – 280 terms Up to 5,000 terms Managed automatically via min_df and stopword filtering.
Topic Count (\(K\)) 3 topics 2 – 10 topics Controlled via Start!B7. For \(n < 50\) documents, keep \(K \le 5\) to avoid empty or degenerate topics.
Top Terms Count 8 terms 3 – 20 terms Controlled via Start!B8. Determines the depth of the keyword output block on Vocabulary & Topics.
Matrix Sparsity 85% – 92% Up to 99.0% Handled efficiently using SciPy sparse csr_matrix structures under the hood.

Authoring custom corpora and adding new models

To analyze your own dataset:

Step 1: Register the new model on Start

  1. Go to Start!D5:H9.
  2. Add a new row in the model library table:
    • Model: employee_survey_2026
    • Label: Employee Survey — Topics
    • Family: Topics
    • Algorithm: LDA
    • Description: Quarterly employee engagement survey feedback topic modeling.

Step 2: Populate the Corpus table

  1. Go to the Corpus sheet.
  2. Append your survey rows:
    • Set Model = employee_survey_2026
    • Set DocID = 1, 2, 3...
    • Set Category = Engineering, Sales, Marketing...
    • Paste the verbatim responses into Text.

Step 3: Add custom rules (optional)

  1. Go to Stopwords & Rules.
  2. Add survey-specific stopwords:
    • Model = employee_survey_2026, RuleType = Stopword, Value = company, work, feel, year.

Step 4: Run the analysis

  1. Return to Start.
  2. Set cell B6 (Active task) to employee_survey_2026.
  3. The toolbox automatically ingests the new records, computes topic distributions, and publishes results to Results, Vocabulary & Topics, and Diagnostics.