Keyword data arrives as thousands of rows of positions, impressions and clicks, and in that form it tells you almost nothing. Was that drop a real trend or normal fluctuation? Which topic clusters are gaining? Is a competitor taking your visibility or is seasonality at play? Excel remains one of the fastest tools for answering these questions, because pivot tables and charts let you aggregate and compare without waiting on a dashboard build. The key is preparing the data correctly, choosing the right chart type and inverting position axes so improvement reads as up.

How AAMAX.CO Turns Ranking Data Into Decisions

At AAMAX.CO we build reporting that drives action rather than filling slides. Our SEO services include cleaning and consolidating rank tracking, Search Console and analytics data, segmenting keywords into commercial clusters, building trend and share-of-voice reporting, and translating the movements into a prioritised action plan for content, technical and link work. As a full service digital marketing company delivering web development, digital marketing and SEO worldwide, we can also automate the pipeline so your reporting refreshes itself instead of consuming a day every month.

Export and Structure Your Data Correctly

Start with a clean source. Export Search Console performance data by query with date, or export rank tracker history. Aim for a long-format table with one row per keyword per date, containing columns for date, keyword, average position, impressions, clicks, click-through rate, page URL and any segment labels. Long format is essential because pivot tables handle it natively, whereas wide exports with a column per date are painful to extend. Format the date column as a true date, positions as numbers with one decimal, and remove thousands separators from numeric fields so Excel does not treat them as text.

Clean the Data Before You Chart It

Bad charts usually come from dirty data. Use TRIM and LOWER to normalise keyword text so casing and stray spaces do not split the same term into two rows. Remove duplicates with the Remove Duplicates tool on the combination of date and keyword. Decide how to treat missing positions: a keyword that dropped out of the tracked range should not be recorded as position zero, because that reverses the chart. Either leave it blank or assign a consistent floor value such as 101 and label it clearly. Add a helper column for the reporting month using TEXT with a year and month format so you can aggregate cleanly.

Add Segment Labels So Charts Mean Something

Charting a thousand individual keywords produces spaghetti. Add columns that group keywords into meaningful segments: topic cluster, funnel stage such as informational or transactional, brand versus non-brand, and target page. Use nested IF statements or SEARCH combined with IFERROR to tag terms automatically, for example flagging any query containing your brand name as branded, and any containing a price or cost term as commercial. Once segments exist, you can chart the average position of a cluster over time, which is a far more decision-ready view than individual keyword noise.

Calculate Change With the Right Formulas

Create explicit change columns rather than eyeballing charts. Use a lookup such as XLOOKUP, or INDEX with MATCH in older versions, to pull the same keyword's position from the previous period, then subtract. Remember that a negative difference in position is an improvement, so multiply by minus one to create an intuitive "positions gained" column. Add a percentage change column for impressions and clicks. Use a three-period moving average with AVERAGE over a rolling window to smooth daily volatility, and flag statistically meaningful moves by comparing the change against the keyword's own standard deviation using STDEV.

Build Pivot Tables to Aggregate

Insert a pivot table on your cleaned range or, better, on an Excel Table so new rows are picked up automatically. Put date or month in rows, segment in columns and average of position in values. Add a second pivot with sum of clicks and impressions. Use slicers for segment, device and country so a single report answers multiple questions. Set the position value field to average rather than sum, and be aware that averaging positions across keywords with wildly different impression volumes can mislead; a weighted average using SUMPRODUCT of position and impressions divided by total impressions is more honest for executive reporting.

Choose the Right Chart for Each Question

Match the chart to the question. Use a line chart for position trends over time, one line per segment, with the vertical axis reversed so that rising lines mean improving rankings. Use a clustered bar chart for period-over-period comparisons of top movers, sorted by positions gained. Use a stacked area or stacked column chart for ranking distribution, showing how many keywords sit in positions one to three, four to ten, eleven to twenty and beyond, which is the clearest single view of progress. Use a scatter chart to plot position against impressions and spot high-volume terms stuck on page two. Use a combo chart with clicks as columns and average position as a reversed-axis line to show the relationship between rankings and traffic.

Invert the Position Axis and Format for Clarity

This step is skipped constantly and it inverts the meaning of every ranking chart. Right-click the vertical axis, open Format Axis and select the values in reverse order option, then set the maximum bound to 1 and a sensible minimum such as 30 or 50. Set the horizontal axis to cross at the maximum value so labels do not sit over the chart. Beyond that, label axes explicitly, add data labels only to endpoints rather than every point, use a restrained colour palette with one accent colour for the segment you want to highlight, and add annotations for known events such as an algorithm update or a site migration so the reader interprets a drop correctly.

Add Conditional Formatting and Share of Voice

A sorted table with conditional formatting often communicates faster than a chart. Apply a colour scale to your positions gained column, and use icon sets for direction. Add data bars to the impressions column so scale is visible at a glance. For competitive reporting, build a share-of-voice view: assign each ranking position an estimated click-through weight, multiply by keyword volume, then sum per domain to show what proportion of the available clicks in your keyword set each competitor captures. Charting that as a stacked area over time is one of the most persuasive SEO visuals you can produce.

Automate the Refresh

Do not rebuild the workbook every month. Use Power Query to import and transform the export, applying your cleaning steps as repeatable transformations, then load into the data model. Build all charts on pivot tables sourced from that query. Next month you replace the source file and click Refresh All. Keep a raw data sheet that you never edit manually, a transformation sheet and a presentation sheet, so the workbook stays auditable and you can always trace a number back to its source.

Final Thoughts

Graphing keyword changes in Excel is straightforward once the data is long-format and clean, segments are labelled, position axes are reversed and each chart answers one specific question. The goal is not a prettier report but a faster decision about where to invest next. If you would rather have that analysis and the resulting action plan handled for you, our SEO and digital marketing teams can build the reporting and execute against it.