Yes, you can plot points in Excel using a scatter chart. But if your X and Y columns aren’t contiguous, sorted, or clean of text labels in numeric cells, Excel will silently drop rows — and you won’t even know which ones.
The Setup
We’re working with field survey data from a solar panel installation project in Ningbo. A technician recorded GPS coordinates (latitude/longitude) and panel tilt angles for 9 sites. The raw data lives in A1:C10, with headers in row 1.
| Site ID | Latitude (°) | Tilt Angle (°) |
|---|---|---|
| NGB-01 | 30.2247 | 22.5 |
| NGB-02 | 30.2261 | 23.1 |
| NGB-03 | 30.2278 | 21.9 |
| NGB-04 | 30.2292 | 24.0 |
| NGB-05 | 30.2305 | 22.7 |
| NGB-06 | 30.2318 | 23.4 |
| NGB-07 | 30.2330 | 21.2 |
| NGB-08 | 30.2344 | 22.8 |
| NGB-09 | 30.2357 | 23.6 |
The Challenge
You want to plot latitude on the X-axis and tilt angle on the Y-axis — but Excel doesn’t ask what goes where. It assumes Column A = X, Column B = Y… unless you’ve selected three columns, in which case it treats A as labels, B as X, C as Y. And if there’s even one text cell buried in your Latitude column — say, a stray "N/A" in B7 — Excel drops that entire row without warning.
This is why people think their data “disappeared.” It didn’t. Excel just filtered it out during chart creation — and offered zero feedback. (Trust me, I learned this the hard way after missing two outlier points in a client report.)
Also: if your data spans non-adjacent columns — say, Latitude in column D and Tilt in column G — Excel won’t auto-detect them. You’ll need to build the series manually. That’s where most people give up and paste into Google Sheets instead.
Walking Through It
Let’s fix this step-by-step — starting from the raw table above in A1:C10.
Step 1: Clean & verify
Scan column B for non-numbers. Use Ctrl + F → search for "N/A", "-", or spaces. In our case, everything looks clean — but let’s double-check with a quick formula in column D. In D2, type:=ISNUMBER(B2)*ISNUMBER(C2)
Drag down to D10. All TRUE? Good. Any FALSE means that row won’t plot.
Step 2: Select only the numeric data
Highlight B1:C10 — not A1:C10. Why? Because Site ID is irrelevant for plotting coordinates vs. tilt. Including it forces Excel to treat the first column as labels — and then misassign axes. Just B1:C10 gives us clean X (Latitude) and Y (Tilt).
Step 3: Insert the chart
With B1:C10 selected, press Alt → N → S → C. That’s the keyboard shortcut for Insert → Scatter → Scatter with Only Markers. Done. No mouse needed.
Step 4: Fix axis labels (optional but critical)
Right-click the horizontal axis → Format Axis → check "Values in reverse order" — only if your latitudes increase northward and you want south-to-north reading. In our case, we leave it unchecked. Then double-click the chart title and rename it "Latitude vs. Panel Tilt (Ningbo Sites)".
Here’s what your selection looked like before:
| Latitude (°) | Tilt Angle (°) |
|---|---|
| 30.2247 | 22.5 |
| 30.2261 | 23.1 |
| 30.2278 | 21.9 |
| 30.2292 | 24.0 |
And here’s the exact same range after Excel plots it — now as chart points:
| X (Latitude) | Y (Tilt) | Plot Status |
|---|---|---|
| 30.2247 | 22.5 | ✓ Plotted |
| 30.2261 | 23.1 | ✓ Plotted |
| 30.2278 | 21.9 | ✓ Plotted |
| 30.2292 | 24.0 | ✓ Plotted |
| 30.2305 | 22.7 | ✓ Plotted |
The Result
After those four steps, you’ll see nine clean blue dots — each representing one site. Hover over any point: Excel shows tooltip values like "X=30.2357, Y=23.6". Right-click any dot → "Add Data Labels" → choose "Value From Cells" to pull in Site IDs from column A (you’ll need to re-select A2:A10 manually).
Final output table — the actual plotted dataset Excel used internally (yes, you can extract it):
| Point # | X (Latitude) | Y (Tilt) | Site ID |
|---|---|---|---|
| 1 | 30.2247 | 22.5 | NGB-01 |
| 2 | 30.2261 | 23.1 | NGB-02 |
| 3 | 30.2278 | 21.9 | NGB-03 |
| 4 | 30.2292 | 24.0 | NGB-04 |
| 5 | 30.2305 | 22.7 | NGB-05 |
| 6 | 30.2318 | 23.4 | NGB-06 |
| 7 | 30.2330 | 21.2 | NGB-07 |
| 8 | 30.2344 | 22.8 | NGB-08 |
| 9 | 30.2357 | 23.6 | NGB-09 |
What Could Go Wrong
Here are three mistakes I see weekly — with how to spot and fix each:
- Mistake #1: Mixed data types in X or Y column
You have "30.2247" in B2, but "30.2261 (est)" in B3. Excel reads that as text. The chart will show only 8 points — and no error. Solution: use=VALUE(SUBSTITUTE(B3," (est)",""))in a helper column, then plot that. - Mistake #2: Accidentally selecting headers + data across 3+ columns
Selecting A1:C10 triggers Excel to map A→labels, B→X, C→Y — but only if A contains text. If A has numbers (like ID 1, 2, 3), Excel treats A→X, B→Y, C→ignored. You’ll get nonsense. Fix: select only B1:C10, never more than two columns for basic scatter. - Mistake #3: Using Line chart instead of Scatter
Alt+N+L gives you Line — which connects points in row order, not coordinate space. Your "latitude vs tilt" becomes "row 1 → row 2 → row 3". Visually identical at first glance, but mathematically wrong. Always use Alt+N+S+C.
One last counterintuitive tip: If your points look bunched on the left, don’t widen the chart. Right-click the X-axis → Format Axis → set Minimum Bounds to 30.224 and Maximum to 30.236. That zooms the axis — not the canvas. You’ll see separation instantly.
Now you’re ready. Next time you open Excel and need to plot points, skip the ribbon hunt. Just remember: select two clean columns, Alt+N+S+C, then verify with =ISNUMBER().
| Task | Shortcut | Notes |
|---|---|---|
| Insert Scatter Chart | Alt → N → S → C | Fastest path — no mouse |
| Format Selected Axis | Ctrl+1 | Works on axis, plot area, or legend |
| Toggle Data Labels | Ctrl+1 → check "Data Labels" | Then right-click label → "Format Data Labels" |
| Select Non-Adjacent Ranges | Ctrl+Click on column headers | Useful when X/Y live in columns D & G |
