Home > Format > Formatting Tips Excel Formatting Tips Unique ways to format worksheet cells to make text or fill color stand out in Microsoft Excel. Create a color spectrum across a range of cells. Show color palettes near cells

Open the Format Cells Dialog Box

Open the Format Cells with the keyboard or mouse, to change any of the formatting options for the selected cells

Open With a Keyboard Shortcut

1. Select the cells that you want to format
2. On the keyboard, press Ctrl + 1

Open With the Mouse

• Select the cells that you want to format
• Right-click on one of the selected cells
• Click Format Cells

The Format Cells dialog box opens, and you can click any of the tabs, to change the formatting options for the selected cells.

Show the Color Spectrum

To show an electromagnetic spectrum in a range of 6 cells, you can use a 2-color gradient fill in each cell.

Thanks to Robert Lepper, for sharing this tip. He used this technique to help with a homework assignment.

To show a color spectrum in an Excel spreadsheet, like the one above, each cell is filled with a 2-color gradient. Each adjacent cell starts with the finishing color of the cell to the left.

To fill each cell:

1. Right-click on a cell, and click Format Cells
2. In the Format Cells dialog box, click the Fill tab
3. Below the palette of background color options, click the Fill Effects button



4. Under Colors, the Two Colors option is automatically selected.
5. Select your starting and ending cell colors in the Color 1 and Color 2 drop downs
6. Under Shading Styles, select Vertical



7. Click OK, twice, to close the dialog boxes
8. Repeat these steps in each of the 6 cells, to create the full color spectrum.

Custom Colour Trick

If you're trying to select a custom colour, the colour palette that opens is very small, and it's hard to click on the colour you need.

Use this quick trick to make the job easier. When the palette opens, double-click the title bar, at the top of the palette window.

The colour palette will automatically expand in size, to fill the entire screen.

This very short video shows that trick.

Move Color and Border Palettes

While working on a worksheet, you might want to change a cell's fill color or font colour, or add borders. To do that, you can use the fill, font, and border commands on the toolbar.

Move the Formatting Palettes

If you have lots of formatting to do, it will be easier if the palettes are closer to the area where you're working.

In Excel 2007 and Later Versions

In Excel 2007 and later, right-click on the cell that you want to format, to see the popup menu. A mini toolbar also appears, with several formatting options, including fill, font and borders.

If the mini toolbar does not appear, go into Excel Options, in the General category, and add a check mark to "Show Mini Toolbar on selection".

In Excel 2003 and Earlier Versions

In Excel 2003 and earlier, you can detach the formatting palettes from the toolbar, and move them onto the worksheet. This video shows the steps, and the written instructions are below the video.

To move one of the “tear away” palettes (Font Color, Fill Color, Borders):

1. On the toolbar, click the dropdown arrow, to open the palette.
2. At the top of the palette, point to the thin grey bar.
3. When your pointer changes to a four-headed arrow, drag the palette down onto the worksheet.

When you've finished working with the palette, click the X at its top right, to close it.

Get the Sample File

Get the sample formatting workbook. The zipped file is in xlsx format, and does not contain macros.

More Tutorials

FAQs, Worksheet Functions and Formats

Data Entry - Tips

Conditional Formatting - Basics

Drop Down List in Cell - Basics