Die Keyword-Liste steht, 241 Zeilen, nach Suchvolumen sortiert. Ganz oben „lederjacke“, dann „lederjacke damen“, dann „lederjacke herren“. Welche Farbe wird am meisten gesucht? Lohnt sich eine eigene Seite für Wildleder? Wie viel Nachfrage hängt an Pflegefragen? Aus einer sortierten Liste liest du das nicht ab. Du brauchst Summen je Merkmal, und dafür muss jedes Keyword seine Merkmale in eigenen Spalten tragen.
Kurz erklärt
Eine Pivot-Tabelle fasst eine lange Liste zusammen. Sie summiert zum Beispiel das Suchvolumen je Farbe oder zählt die Keywords je Kategorie. Du ziehst dafür nur Spalten in die Bereiche Zeilen, Spalten und Werte. Damit das klappt, braucht jedes Keyword seine Merkmale in eigenen Spalten, etwa Produkt, Farbe oder Absicht.
Warum sich das Sortieren lohnt
Sortierte Keywords beantworten die Fragen, die über Seiten und Budget entscheiden: welche Kategorie- und Filterseiten du brauchst, welche Ratgeber Nachfrage haben und welche Varianten im Sortiment fehlen. Weil die Zuordnung über Formeln läuft, sortieren sich neue Keywords später von selbst ein. Die Arbeit machst du einmal.
Zwei Wege: Merkmale oder Kategorie
| Weg A: Merkmale | Weg B: Kategorie | |
|---|---|---|
| Spalten | mehrere, eine je Merkmal wie Produkt, Farbe, Material, Absicht | eine |
| Zuordnung | ein Keyword kann viele Merkmale haben | jedes Keyword bekommt genau eine Kategorie |
| Aufwand | höher, mehrere Begriffslisten | gering, eine Liste |
| Stärke | genaue Filter und Kombinationen wie Farbe × Zielgruppe | schneller Überblick über das Potenzial |
| Gut für | Seitenstruktur, Filterseiten, Content-Planung | Priorisierung, Angebot, Forecast |
Beide Wege nutzen dieselbe Formel, du kannst also auch beide bauen. Im Marketing-Alltag ist Weg A der spannendere, weil er zeigt, was genau gesucht wird. Weg B ist schneller und die Grundlage für Teil 3: SEO-Forecast, wenn du ein Budget begründen musst.
Vorbereitung: Liste als Tabelle formatieren
- In die Keyword-Liste klicken und Strg + T drücken. Excel macht aus dem Bereich eine Tabelle.
- Unter Tabellenentwurf als Tabellenname „Keywords“ eintragen.
- Spalten: Keyword, Suchvolumen, CPC, Position, URL. Rechts daneben kommen die Merkmalsspalten.
Der Vorteil: Neue Zeilen gehören automatisch zur Tabelle, Formeln laufen nach unten mit, und Pivot-Tabellen erfassen neue Keywords beim Aktualisieren.
Weg A, Schritt 1: Begriffslisten anlegen
Für jedes Merkmal legst du eine kleine Tabelle mit zwei Spalten an: Begriff und Wert. Der Begriff ist das, was im Keyword steht. Der Wert ist das, was in der Merkmalsspalte erscheinen soll.
| Begriff | Wert |
|---|---|
| schwarz | Schwarz |
| braun | Braun |
| cognac | Cognac |
| weiß | Weiß |
| weiss | Weiß |
| rot | Rot |
Ein Begriff zählt, wenn ein Wort im Keyword damit beginnt. „schwarz“ findet also auch „schwarze lederjacke“, „rot“ aber nicht „karotte“. Schreibvarianten wie „weiß“ und „weiss“ bekommen einfach denselben Wert. Die Tabellen heißen Farben, Materialien, Zielgruppen und so weiter.
Weg A, Schritt 2: die Formel
In die Spalte Farbe der Keyword-Tabelle kommt diese Formel:
=WENNFEHLER(VERWEIS(2;1/((Farben[Begriff]<>"")*ISTZAHL(SUCHEN(" "&Farben[Begriff];" "&[@Keyword])));Farben[Wert]);"ohne")
Von innen nach außen gelesen:
" "&[@Keyword]setzt ein Leerzeichen vor das Keyword," "&Farben[Begriff]vor jeden Begriff. So findet SUCHEN nur Wortanfänge.- SUCHEN prüft jeden Begriff der Liste gegen das Keyword, ISTZAHL macht daraus WAHR oder FALSCH.
(Farben[Begriff]<>"")ignoriert leere Zeilen in der Liste.1/…macht aus jedem Treffer eine 1 und aus jedem Nicht-Treffer einen Fehler.- VERWEIS(2;…) sucht die 2, findet sie nie und nimmt deshalb den letzten Treffer. Der Trick funktioniert in jeder Excel-Version ohne Matrixformel.
- WENNFEHLER schreibt „ohne“, wenn kein Begriff passt.
Weil der letzte Treffer gewinnt, gilt eine einfache Regel: allgemeine Begriffe oben in die Liste, spezielle unten. Bei „biker lederjacke“ passen „lederjacke“ und „biker“. Steht „biker“ weiter unten, wird daraus Bikerjacke.
Für die übrigen Merkmale kopierst du die Formel und tauschst nur den Tabellennamen. In Excel 365 geht es auch mit XVERWEIS: =XVERWEIS(1;(Farben[Begriff]<>"")*ISTZAHL(SUCHEN(" "&Farben[Begriff];" "&[@Keyword]));Farben[Wert];"ohne";0;-1). Die −1 am Ende sucht von unten, damit gilt dieselbe Regel.
Weg A, Schritt 3: Suchabsicht als Merkmal
Die Absicht funktioniert genauso, nur mit Signalwörtern. Die Reihenfolge in der Liste: erst Informieren, dann Vergleichen, dann Kaufen. „lederjacke test kaufen“ landet so bei Kaufen. Ohne Signalwort schreibt die Formel „Produktsuche“, denn wer nur „lederjacke damen schwarz“ sucht, schaut in der Regel nach Produkten.
| Wert | Begriffe |
|---|---|
| Informieren | pflege, reinigen, waschen, imprägnieren, wie, was kostet, welche, größe, kombinieren, outfit |
| Vergleichen | test, vergleich, marken, beste, erfahrungen |
| Kaufen | kaufen, bestellen, günstig, sale, reduziert, online, preis |
Weg A, Schritt 4: Lücken schließen
- In der Keyword-Tabelle eine Merkmalsspalte nach „ohne“ filtern.
- Die Treffer durchsehen. Fehlt ein Begriff wie „grau“, trägst du ihn in die Liste ein. Die Formel ordnet sofort neu zu.
- Das für jede Spalte wiederholen.
„ohne“ ist dabei kein Fehler: „lederjacke damen“ hat eben keine Farbe. Fertig bist du, wenn unter „ohne“ nur noch Keywords stehen, die das Merkmal wirklich nicht haben.
Weg A, Schritt 5: Pivot-Tabelle anlegen
- In die Keyword-Tabelle klicken, dann Einfügen > PivotTable.
- Als Ziel Neues Arbeitsblatt wählen und mit OK bestätigen (Microsoft Support: PivotTable erstellen).
- Im Bereich PivotTable-Felder Produkt nach Zeilen ziehen, Zielgruppe nach Spalten und Suchvolumen nach Werte.
- Keyword zusätzlich nach Werte ziehen. Bei Text zählt Excel automatisch, du siehst also die Zahl der Keywords.
- CPC nach Werte ziehen, auf den Pfeil daneben klicken, Wertfeldeinstellungen wählen und unter Werte zusammenfassen nach den Mittelwert einstellen.
- Mit einem Rechtsklick in die Summenspalte nach Größe absteigend sortieren.
Der Mittelwert des CPC ist ungewichtet: Ein Keyword mit 10 Suchen zählt so viel wie eines mit 10.000. Für einen gewichteten Wert ergänzt du in der Keyword-Tabelle eine Spalte „Kosten“ mit Suchvolumen × CPC und teilst in der Auswertung die Summe der Kosten durch die Summe des Suchvolumens. Die Übersichten in der Vorlage rechnen so.
Weg A, Schritt 6: Datenschnitte zum Filtern
- In die Pivot-Tabelle klicken, Einfügen > Datenschnitt.
- Farbe, Material und Absicht ankreuzen und mit OK bestätigen (Microsoft Support: Datenschnitte verwenden).
- Jetzt filterst du per Klick. Mit gedrückter Strg-Taste wählst du mehrere Werte, etwa Kaufen und Produktsuche zusammen.
Auswertungen, die sich lohnen
| Zeilen | Spalten | Frage dahinter |
|---|---|---|
| Produkt | Zielgruppe | Welche Kategorieseiten brauche ich, getrennt nach Damen und Herren? |
| Farbe | Zielgruppe | Lohnt sich eine Filterseite „schwarze Lederjacken Damen“? |
| Material | Produkt | Welche Materialien werden gesucht, die ich nicht führe? |
| Absicht | Produkt | Wo brauche ich Ratgeber, wo Kategorieseiten? |
| Keyword, gefiltert auf die Top 10 | Absicht | Welche einzelnen Keywords tragen das Volumen? |
Im Beispiel der Vorlage liegt Schwarz mit 25.660 Suchen pro Monat klar vor Braun mit 13.990. Spannender sind die Kombinationen: Cognac wird fast fünfmal so oft mit „herren“ gesucht wie mit „damen“ (1.870 zu 390), bei Beige ist es umgekehrt (1.300 zu 130). Und Kunstleder kommt mit 15.910 Suchen auf mehr als Wildleder (10.030) oder Lammleder (7.860). Führt der Shop kein Kunstleder, ist das eine Frage fürs Sortiment. Alle Zahlen im Beispiel sind erfunden.
Weg B: eine Kategoriespalte für den schnellen Überblick
Wenn du zuerst wissen willst, wo das Potenzial liegt, reicht eine Spalte. Jedes Keyword bekommt genau eine Kategorie, etwa „Lederjacken Damen“, „Bikerjacken“ oder „Pflege und Reinigung“.
- Eine Tabelle „Kategorieliste“ mit den Spalten Begriff und Kategorie anlegen.
- Dieselbe Formel wie oben nutzen, mit Kategorieliste statt Farben und „Sonstige“ statt „ohne“.
- Die Reihenfolge festlegen: Was bei mehreren Treffern gewinnen soll, kommt nach unten. Im Beispiel steht „damen“ über „biker“, damit „bikerjacke damen“ bei Bikerjacken landet, und „pflege“ ganz unten, damit „lederjacke damen pflegen“ bei Pflege landet.
- Pivot-Tabelle mit Kategorie in den Zeilen, Suchvolumen und Anzahl in den Werten, absteigend sortiert.
- „Sonstige“ prüfen. Liegen dort mehr als etwa 5 % des Suchvolumens, fehlen Begriffe in der Liste.
| Kategorie | Keywords | Suchvolumen pro Monat | Anteil |
|---|---|---|---|
| Lederjacken allgemein | 44 | 95.780 | 29,8 % |
| Lederjacken Damen | 29 | 74.540 | 23,2 % |
| Lederjacken Herren | 31 | 51.770 | 16,1 % |
| Bikerjacken | 40 | 36.680 | 11,4 % |
| Ledermäntel | 37 | 28.580 | 8,9 % |
| Lederwesten | 34 | 14.170 | 4,4 % |
| Ratgeber | 15 | 4.790 | 1,5 % |
| Pflege und Reinigung | 7 | 8.950 | 2,8 % |
| Sonstige | 4 | 6.500 | 2 % |
| Summe | 241 | 321.760 | 100 % |
Nach wenigen Minuten ist klar: Die drei Lederjacken-Kategorien tragen fast 70 % der Nachfrage. Ratgeber und Pflege sind klein, „Sonstige“ liegt mit 2 % unter der 5-%-Grenze. Mit diesen Summen rechnest du im nächsten Schritt aus, was eine bessere Position wert wäre.
Pivot aktualisieren
Pivot-Tabellen rechnen nicht von selbst mit. Nach neuen Keywords oder geänderten Begriffslisten klickst du auf Daten > Alle aktualisieren. Weil die Quelle eine Tabelle ist, sind neue Zeilen automatisch dabei.
Typische Fehler
- Suchvolumen von Varianten doppelt zählen. Der Keyword-Planer zeigt für ähnliche Keywords oft das Volumen der ganzen Gruppe (SISTRIX: warum Suchvolumen vom Keyword-Planer abweichen). Summen sind deshalb eher eine Obergrenze.
- Kurze Begriffe ohne den Leerzeichen-Trick suchen. Dann findet „rot“ auch „karotte“ und „brot“.
- Den ungewichteten Mittelwert des CPC als Durchschnittspreis lesen.
- Zu viele Kategorien bei Weg B. Mehr als etwa 15 machen den Überblick wieder unübersichtlich.
Fazit
Mit Merkmalsspalten wird aus einer langen Liste eine Landkarte der Nachfrage. Du siehst, welche Produkte, Farben und Fragen gesucht werden, und filterst jede Kombination per Klick. Die Kategoriespalte zeigt dir in wenigen Minuten, wo das Potenzial liegt. Beide Wege laufen über dieselbe Formel, und neue Keywords sortieren sich von selbst ein. Wie du aus den Summen je Kategorie Klicks, Umsatz und ein Argument fürs Budget machst, zeigt Teil 3: SEO-Forecast.
Excel-Vorlage zur Reihe
Die Vorlage enthält alle Begriffslisten, die Formeln in der Keyword-Tabelle und zwei fertige Übersichten mit Filtern. Eine eigene Pivot-Tabelle legst du wie oben beschrieben in wenigen Klicks an. Alle Zahlen sind erfunden.
Vorlage herunterladen Excel-Datei, kostenlos, ohne Anmeldung
Teil 2 von 3 der Reihe „Keyword-Recherche in Excel“: Teil 1: Keyword-Recherche · Teil 2: Keywords sortieren · Teil 3: SEO-Forecast
Häufige Fragen
Wie ordne ich Keywords in Excel automatisch Kategorien zu?
Mit einer Begriffsliste und der Formel VERWEIS(2;1/ISTZAHL(SUCHEN(…))). Sie prüft jeden Begriff gegen das Keyword und gibt den Wert des letzten Treffers zurück.
Was ist der Unterschied zwischen Merkmalen und Kategorien?
Ein Keyword kann viele Merkmale haben, etwa Farbe, Material und Absicht. Eine Kategorie gibt es pro Keyword genau einmal. Merkmale eignen sich für genaue Filter, Kategorien für den schnellen Überblick.
Warum aktualisiert sich meine Pivot-Tabelle nicht?
Pivot-Tabellen rechnen nicht automatisch neu. Über Daten > Alle aktualisieren liest Excel die Quelle neu ein.
Welche Excel-Version brauche ich?
Die Formel mit VERWEIS und WENNFEHLER läuft ab Excel 2007. XVERWEIS und TEXTTEILEN gibt es nur in neueren Versionen wie Microsoft 365.
Wie viele Kategorien sind sinnvoll?
Als Richtwert fünf bis fünfzehn. Jede Kategorie sollte zu einer Seite oder einem Bereich passen, für den du eine eigene Entscheidung triffst.