Most Excel tutorials tell you to build a chart first, then right-click the axis and check 'Logarithmic scale'. That’s backwards — and dangerous. You’ll miss critical outliers, misalign trendlines, and break error bars before they’re even added. Log scaling belongs in your data prep or axis definition — not as an afterthought.
Quick Answer
To plot a log graph in Excel, either (1) transform your Y-values with =LOG10() before charting, or (2) right-click the vertical axis → Format Axis → check 'Logarithmic scale' and set Base = 10. Method 1 gives full control over zero/negative handling; Method 2 is faster but fails silently on non-positive values.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| LOG10() Data Prep | Enter =LOG10(B2) in C2, drag down; plot X vs C-column | Scientific reports, regression analysis, custom error bars | Requires manual handling of zeros/negatives (use IFERROR or add small offset) |
| Axis Log Scaling | Create scatter/line chart → right-click Y-axis → Format Axis → Log scale | Quick presentations, internal dashboards, teaching demos | Fails without warning if any Y ≤ 0; no control over base other than 10 |
| Natural Log + Custom Axis Labels | Use =LN(B2), plot, then manually relabel Y-axis ticks as powers of e | Engineering models requiring e-based scaling | Time-intensive; labels won’t auto-update if data changes |
| Power Law Fit + Log-Log Chart | Plot LOG10(X) vs LOG10(Y); add linear trendline; slope = exponent | Identifying power-law relationships (e.g., sales vs ad spend) | Only works when both axes need log scaling; requires clean positive data |
Method 1 Deep Dive
We’ll use real sensor data from Acme Corp’s air quality monitoring. Column A holds timestamps (A2:A11), column B holds PM2.5 concentration (µg/m³):
| A | B | C | D |
|---|---|---|---|
| Date | PM2.5 (µg/m³) | =LOG10(B2) | Label |
| 2024-03-15 | 12.4 | 1.093 | 10¹·¹ |
| 2024-03-16 | 198.7 | 2.298 | 10²·³ |
| 2024-03-17 | 0.8 | -0.097 | 10⁻⁰·¹ |
| 2024-03-18 | 1,240 | 3.093 | 10³·¹ |
| 2024-03-19 | 47.2 | 1.674 | 10¹·⁷ |
| 2024-03-20 | 0.0 | #NUM! | — |
| 2024-03-21 | 312.5 | 2.495 | 10²·⁵ |
Notice row 7: B7 is 0.0 → LOG10(0) is undefined. Don’t delete it. Instead, in C2 enter:=IF(B2<=0,"",LOG10(B2))
Then copy down to C11. This preserves row alignment for charting — critical when your X-axis is dates or categories.
Select A2:A11 and C2:C11. Insert → Scatter with Smooth Lines (Alt+N+S+P). Right-click chart area → Select Data → Edit Horizontal Axis Labels → point to A2:A11. Done. Your Y-axis now shows true log-spaced values. Trendline? Add → Linear → Display Equation. Slope = 0.37 means PM2.5 grows ~44% per day (10^0.37 ≈ 2.35, so daily multiplier).
Method 2 Deep Dive
This is faster — but riskier. Start with raw data in A1:B6:
| A | B |
|---|---|
| Company | Revenue (USD) |
| Nexus Labs | $45,200 |
| Stellar Dynamics | $892,000 |
| Vanta Systems | $3,150,000 |
| Orion Group | $12,400,000 |
Select A1:B6 → Insert → Clustered Column (Alt+N+C+L). Now: right-click the vertical axis → Format Axis. In the pane, check 'Logarithmic scale'. Set Base to 10. Done.
Here’s the counterintuitive tip: Excel will *not* warn you if your data contains zeros or negatives. It simply hides those bars — no error, no message. To catch this, press Alt+J+A+U to open the 'Select Data Source' dialog. Click 'Edit' under Legend Entries, then click 'OK' — if any series disappears, check for ≤0 values in your source.
You can also change the major unit. Under 'Units', set Major = 10. That forces tick marks at 10¹, 10², 10³… instead of Excel’s default auto-scaling.
Cheat Sheet
| Step | Action | Result | Shortcut |
|---|---|---|---|
| 1 | Add LOG10() column next to Y-data | Clean log-transformed values, blanks for ≤0 | Ctrl+Shift+" (to fill down) |
| 2 | Select X and LOG10(Y) columns | Data range ready for scatter chart | Ctrl+Shift+→ then Ctrl+Shift+↓ |
| 3 | Insert scatter chart | Raw log-scaled visualization | Alt+N+S+P |
| 4 | Right-click Y-axis → Format Axis | Axis options panel opens | Ctrl+1 (after right-click) |
| 5 | Check 'Logarithmic scale' + Base = 10 | Axis instantly rescales with log ticks | No shortcut — must click |
| 6 | Set Major Unit = 1 | Tick every power of 10 (10⁰, 10¹, 10²…) | Tab to field, type 1, Enter |
