Format & Arrange:
Format Cells

Title: Jan's Illustrated Computer Literacy 101
Did you want Working with Numbers: 2007,2010,2013,2016  or españolIcon: Change web

Adding formatting to your spreadsheet not only makes it more attractive, it can also make it easier to read and use. The right font, the right font size, the right color, and the right background can combine to make the most important information pop right off the sheet. 

Handling the blank areas is also important. Without enough "white space" (blank areas), your data can be hard to read. Columns and rows need to have some breathing space so your eye will see a break between them.

Excel's sheets behave in many ways like Word's tables. There are some important differences, as you will see in this project.

Word tables and Excel sheets look similar
but do  not behave quite the same.

Where you are:
JegsWorks > Lessons > Numbers

Before you start...

Project 1: Excel IntroTo subtopics

Project 2: Excel BasicsTo subtopics    

Project 3: Format & Arrange
    Format Cells Open arrow to subtopics
    footprintApply Formatting
    footprintCopy Formatting
    footprintPartial, AutoFormat
    footprintStyle - Dialog
    footprintStyle - Style box
    Format ChartTo subtopics
    ArrangeTo subtopics
    Exercises To subtopics

Project 4: Groups & FormulasTo subtopics

Project 5: DesignTo subtopics


In this project you will apply formatting in several different ways. You will probably find that you prefer one approach, but you can not choose your favorite until you have seen them all! Below is a brief introduction to the formatting methods you will use in the Step-by-Step pages.

Format Cells dialog

The dialog Format Cells has six tabs, each with several characteristics that you can set.

The Number tab allows you to pick from a large list of number formats, or you can create a custom one. The Alignment tab includes both horizontal and vertical alignment choices. It also contains the Text control choices: Wrap text, Shrink to fit (the cell), and Merge cells, plus Orientation, which rotates text. The Font tab is nearly the same as the Font dialog in Word. The Border tab includes all the edge borders you used in Word plus diagonals. The Patterns tab is for background colors and patterns. The Protection tab allows you to keep certain cells from being changed by someone using your sheet and to hide parts of the sheet.

Dialog: Format Cells tabs

TipA format that is applied to a selected cell is applied to all of the characters in the cell. Some formats can be applied to just some of the characters when you are editing that particular cell.

Copy Formatting

To copy formatting you can use the Format Painter or the Paste Special command.

Format Painter:

The Format Painter button Button: Format Painter works much as it does in Word. The biggest difference is that Excel's Format Painter works only on the cell as a whole. You cannot use it on just part of the text in a cell. In fact, it is grayed out when you are in Edit mode. When you click the button, the pointer changes to Pointer: Format Painter its Format Painter shape. Click on a cell or drag across several cells to apply the copied formatting. The pointer then returns to Pointer: Select its Select shape.

To use Format Painter to format several cells that are not next to each other, double-click the Format Painter button. The pointer will remain as Pointer: Format Painter its Format Painter shape until you click the button again.

WarningIf a cell contains more than one format for text, such as different font sizes, Format Painter will copy only the first formatting that it finds.

Paste Special:

Dialog: Paste SpecialThe Paste Special dialog (  Edit  |  Paste Special… ) allows you to choose how much about a copied cell you wish to paste. You can choose All and paste the entire cell with all of its formatting.

Or choose one of the Paste options and paste just that characteristic. Of special interest for this project is the choice to paste just the Formats. You can even paste everything but the borders. This is very useful when some rows have borders and some don't.

If you choose one of the Operations, the operation is performed on the pasted contents when you paste.

Pasting multiple cells with Skip blanks checked leaves the previous data in place if the new data has a blank cell in that position.

Transpose will paste columns as rows and rows as columns, which can be quite useful and certainly saves a lot of time.


Dialog: AutoFormat - Simple styleAutoFormat ( Format  |  AutoFormat…) supplies complete table formats for you to choose. These designs include borders, colored fills, and different formatting for column and row labels. Word has a similar but longer set of formatting schemes in its Table AutoFormat dialog.

Clicking the Options button on the AutoFormat dialog reveals a list of characteristics that you can choose to apply. For example you could apply alternating colors or borders to long data rows by selecting the format List1 and checking only the Patterns checkbox.

dialog: AutoFormat in Excele 2000Icon: Excel 2000 Icon: Excel 2002Icon: Excel 2003 In Excel 2000/2002/2003 the dialog looks a bit different, but it has the same formatting schemes.

Warning Do not apply AutoFormat to a whole sheet. A complete sheet is very, very large. Your computer can take minutes, if not hours, to finish formatting a whole sheet. The program will not respond while it is so busy.

Icon: Trouble Excel freezes after you apply AutoFormat to a whole sheet: To break out of the process, you must shut down Excel. [CTRL + ALT + DELETE, select Microsoft Excel, and click the End Task button.] You will lose all unsaved work.

AutoFill Formats

You can use Excel's AutoFill feature to copy formatting into multiple cells, while leaving the data in the cell's alone.

If you drag the AutoFill handle with the right mouse button, a menu appears that lets you choose to fill just the formats:  Fill Formats .

Button: AutoFill Options for a seriesIcon: Excel 2002Icon: Excel 2003 In Excel 2002/2003, when you drag the AutoFill handle with the left mouse button, the AutoFill Options button appears. Clicking the arrow opens the menu of choices , including  Fill Formatting Only .

Clear Formats

After all this formatting, what do you do if you want to just start over? How can you get rid of all these formats, called clearing formats?

Menu: The menu  Edit  |  Clear |  Formats  returns all the fonts to the Normal font, removes fills and borders, and splits merged cells. It does not change row and column sizes. Data that was lost in a merge is not restored.

AutoFormat dialog: In the AutoFormat dialog the last table format in the list is None, which will also clear formats. It does not split merged cells, however.