Formatting Issues

How to Change Color Shades in Excel Custom Number Formats

Kushani Nimanthika Sep 25, 2026 869 views

Question details

The user needs to apply specific shades of colors to data using custom number formats instead of relying on default named colors.

How to Change the Color Shade in an Excel Custom Number Format

Product: Microsoft Excel
Device & OS: not provided
Scenario: Formatting specific cells to display custom data patterns with distinct color shades depending on conditions or layout requirements.
Observed behavior: Excel custom number formats directly support basic named colors (like [Red] or [Green]) and a 56-color indexed palette, but they do not accept arbitrary RGB or Hex color codes within the format string.

Before you start

Ensure your Developer tab is enabled in the ribbon if you intend to use the VBA macro method to inspect your workbook's specific color palette.

Solution 1Recommended

Use the [ColorN] Syntax in Custom Format Codes

You can apply any of the 56 indexed colors stored in your workbook's palette by specifying its exact index number inside the format code.

Instead of typing standard color names, Excel allows you to use the [ColorN] syntax. Here, 'N' represents a whole number from 1 to 56 corresponding to the workbook's built-in color palette. This gives you more options for shading without relying on conditional formatting.

Select Target Cells

Highlight the cells or range where you want to apply the specific color shade.

Open Format Cells

Press Ctrl + 1 on your keyboard, or right-click the selection and choose 'Format Cells' from the context menu.

Choose Custom Category

Navigate to the 'Number' tab and click on 'Custom' at the bottom of the Category list.

Apply [ColorN] Format

In the 'Type' input box, enter your format string starting with [ColorN]. For example, enter [Color43]0.00 to display numbers with two decimal places in a specific green shade. Click OK to apply.

Use the [ColorN] Syntax in Custom Format Codes

Finding the Right Shade: For standard workbook palettes, common shades of green are often mapped to indexes 4, 10, 12, 35, 43, 50, and 51.

Solution 2

Identify Color Indexes Using a VBA Macro

Because it's difficult to guess which index number matches which shade, you can use a quick VBA script to map out all 56 available colors directly in your worksheet.

Customize formatting easily with WPS Spreadsheet

Easily Apply Custom Color Formats in WPS Spreadsheet

WPS Spreadsheet provides comprehensive support for customized data display, including full compatibility with Excel's 56-color indexed palette. You can seamlessly use the exact same [ColorN] formatting codes to manage your data aesthetics without any compatibility issues.

1. Open Your Spreadsheet: Launch WPS Spreadsheet and open the file containing the data you wish to format.
2. Select the Data: Highlight the specific cells, rows, or columns that require a custom color shade.
3. Access Format Cells: Right-click the selected area and choose 'Format Cells', or use the Ctrl+1 shortcut.
4. Apply Custom Format: Go to the Number tab, select 'Custom', and input your [ColorN] code (e.g., [Color10]General) in the Type field.
5. Confirm and Save: Click OK to immediately apply the chosen color shade to your data display.

Fully compatible with Microsoft Excel custom number formats and color indexes.Supports the execution of VBA macros to inspect and modify palette colors.Familiar user interface requiring no learning curve.Lightweight, fast, and free to use for daily data tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use arbitrary RGB or Hex color codes directly in a custom number format?

No. Excel custom number formats only support specific named colors (like [Red] or [Blue]) and the indexed color palette via the [ColorN] syntax. They do not support custom RGB or Hex string inputs.

Why does my [ColorN] code show a different color than I expected?

The [ColorN] syntax refers to the specific color palette saved within your individual workbook. If the workbook's theme or palette has been altered or customized previously, the colors linked to indexes 1 through 56 will change accordingly.

Are there other ways to apply specific RGB colors automatically?

Yes. If you need a highly specific RGB color that isn't in the 56-color palette, you should use Conditional Formatting instead of custom number formats. Conditional Formatting allows you to apply exact fill and font colors based on cell values.

What are the common index numbers for green shades?

Assuming your workbook uses the standard Excel palette, common green shades can usually be found at indexes 4, 10, 12, 35, 43, 50, and 51.

Document & File Issues