Search Contextures Sites
Custom Search

Excel Formatting Tips

Format the cells on a worksheet, to make the text or fill color stand out from the rest of the worksheet contents. Open the Format Cells dialog box with a keyboard shortcut, or with the mouse. See how to create a color spectrum across a range of cells, and move the color palettes in older versions of Excel.

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.

Format Cells dialog box

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.

fill effects gradient for color spectrum

To show a color spectrum, 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. On the Fill tab, click Fill Effects
  3. fill effects button

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

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

fill effects gradient for color spectrum

Move the Color and Borders Palettes

Note: This tip works in Excel 2003 and earlier versions only. The palettes on the newer Excel Ribbon cannot be moved.

While working on a worksheet, you might want to colour a few of the cells, change the font colour for the text, or add borders to a range of cells. To add colour, you select a cell, click on the dropdown arrow at the right side of the colour button on the toolbar, and click on a colour.

That's simple enough, but all those trips up and down from the toolbar can get a bit tiring, and take extra time.

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. You can detach the palettes from the toolbar, and move them onto the worksheet.

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.

Watch the Steps on Video

And here's my short video, if you'd like to see the steps performed on screen:

Download the Sample File

Download 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

Get All the Excel News

For regular Excel news, tips and videos, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.

Search Contextures

Search Contextures Sites

 

.

More Tutorials

FAQs, Worksheet Functions and Formats

Data Entry - Tips

Conditional Formatting - Basics

Drop Down List in Cell - Basics

.

.

.

.

.

.

.

.

.

.

 

.

.

.

 

Privacy Policy

 

Contextures Inc., Copyright ©2016
All rights reserved.