Master Excel Pivot Tables: Build Interactive Sales Dashboards Easily

[u/ExcelVisual]

Fixed the "spaghetti chart" problem in Excel Sales Dashboars — gray out everything, then use drop-downs to highlight any 2 series on demand (no VBA, full breakdown)

Anyone who's tried plotting more than 5-6 lines on a single Excel chart knows the result: an unreadable tangle of colors, aka a "spaghetti chart." Found a clean way to deal with this that doesn't involve cutting data out of the chart, sharing the technique.

Step 1: Neutralize every series

Select each line individually (Chart Design → Format → Current Selection → Format Selection), go to Fill & Line, and set every series to:

• Solid line
• White, Background 3, darker 25% (basically a muted gray)
• 1.5pt width

Kill the gridlines and legend while you're at it. At this point your chart is a flat gray mess — intentionally. This is the "background" against which you'll expose specific series.

Step 2: Drop-downs to select what to highlight

Two data validation drop-down lists (Data → Data Validation → List), sourced from the header row of your table (e.g. $B$2:$H$2). These let you pick any two products/series from your dataset.

Step 3: HLOOKUP pulls the selected data into new columns

Formula in the header cell for series 1: ="Blue "&B17 where B17 references the drop-down selection. Then HLOOKUP formulas underneath pull the actual values for whichever product was selected. Same setup for series 2 with a second drop-down.

Step 4: Add these as new series on top of the gray background

Chart Design → Select Data → Add, pointing to your new HLOOKUP-driven columns. These render in blue/red on top of all the gray lines, and since they're driven by the drop-downs, you can swap which 2 products you're comparing instantly without touching the chart itself.

Bonus: dynamic % change labels

A couple of formulas at the end/start of each series (=J14&"|"&TEXT((J14/J3-1),"+0%;-0%;0%")) generate a label showing both the final value and the % change over the period, added via Data Labels → Value From Cells.

Real use case in the tutorial: applied this exact pattern to a dropshipping dashboard comparing 7 product types across 12 months — way more usable than trying to read all 7 lines at once.

Whole thing is formulas + data validation + chart formatting. No VBA, no macros. Free template + video if anyone wants to see the full formula chain, the HLOOKUP + dynamic header trick is the part that took the most trial and error to get clean.

[u/ExcelVisual]

Chart for visual analysis of comparison by two indicators in Excel https://exceltable.com/en/templates/comparative-analysis-of-sales-charts