How to Set Custom Legend Ranges in an Excel Surface Chart
Question details
The user needs to define the specific number and values of legend intervals (color ranges) in a surface contour chart to maintain consistent color-coding.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating a speed profile across several intersections to identify traffic backups, requiring consistent legend parameters across different project charts.
- Observed behavior
- Excel automatically assigns colors to data ranges in a surface chart based on maximum data values, lacking a direct setting to input custom values for the legend intervals.
Select your existing surface chart and ensure the vertical (value) axis is visible, as the legend intervals are directly controlled by the axis bounds and major units.
Adjust Vertical Axis Major Units to Control Color Bands
Since there is no direct legend interval setting, you must adjust the vertical axis's major units to change the number and span of the color bands.
In Excel surface charts, the legend intervals are inextricably linked to the vertical (Z-value) axis. By default, Excel auto-generates these intervals based on your maximum and minimum data points. Modifying the major unit of this axis will force the legend to update its color ranges accordingly.
Right-click on the vertical (value) axis of your surface chart and select 'Format Axis' from the context menu.
In the Format Axis pane, locate the 'Axis Options' section. Under 'Units', manually input a smaller number in the 'Major' field to increase the number of legend ranges, or a larger number to decrease them.
To maintain consistency across different projects, manually set the 'Minimum' and 'Maximum' bounds in the same pane instead of leaving them on 'Auto'.
Customize Individual Legend Colors
Once you have the desired number of intervals, you can manually adjust the color of each specific data band.
Create and Customize Surface Charts in WPS Spreadsheet
WPS Spreadsheet provides powerful and intuitive charting tools, allowing you to easily adjust axis bounds, major units, and legend formatting to create standardized visual reports.
- 1. Insert Surface Chart: Highlight your dataset, go to the 'Insert' tab, click 'Chart', and select the 'Surface Chart' option.
- 2. Format Vertical Axis: Right-click the vertical axis, select 'Format Axis', and uncheck 'Auto' for Minimum, Maximum, and Major units to enter your own custom intervals.
- 3. Customize Colors: Double-click any color key in the legend to open the formatting pane and apply your specific project color codes.
Frequently Asked Questions
Can I set irregular or unequal legend intervals in an Excel surface chart?
No, Excel requires surface chart legend intervals to be evenly spaced because they are tied to the vertical axis major units. You cannot set irregular bands (e.g., 0-10, 10-50, 50-100) directly on a surface chart.
Why do my surface chart colors change when I update the data?
By default, the axis bounds are set to 'Auto' and will scale based on the new maximum or minimum values in your updated data. To fix the colors, right-click the vertical axis, select 'Format Axis', and manually type in fixed numbers for the Minimum and Maximum bounds.
How can I apply a smooth color gradient to a surface contour chart?
Excel surface charts use discrete color bands instead of continuous gradients. To mimic a gradient, you must significantly reduce the vertical axis Major Unit to create many thin bands, and then manually assign a progressively lighter or darker color to each legend entry.
