Data Entry: Formatting Worksheets: Introduction, using Formatting Toolbar and Formatting Cells Using Dialog Box

Get unlimited access to the best preparation resource for UGC Public-Administration: fully solved questions with step-by-step explanation- practice your way to success.


You can change the size, color and angle of fonts, add color to the borders and backgrounds of cells, and have the format of a cell change, based on its value. You will see that some of the formatting features in MS Excel are same, as you have used in MS Word.

Using Formatting Toolbar

  • Three locations where the Excel 2007 formatting tools are available:

  • In the home tab.

  • In the mini toolbar, that appears when you right click a range or a cell.

  • In the format cells dialog box.

In the Mini Toolbar

The Mini toolbar contains controls for common formatting:

  • Font type

  • Font Size

  • Decrease Font

  • Increase Font

  • Accounting Number Format

  • Comma Style

  • Font Color

  • Format Painter

  • Bold

  • Italic

  • Center

  • Percent Style

  • Borders

  • Merge and Center

  • Increase Decimal

  • Decrease Decimal

  • Fill Color

Using the Format Cells Dialog Box

  • Press the combination of Ctrl+1, i.e., Control key and numeric 1 key.

  • Click the dialog box launcher in Home → Font, Home → Alignment, or Home → Number. A small downward pointing arrow icon will be displayed to the right. In the Excel Ribbon, these arrow marks are known as dialog box launcher.

  • Choose Format Cells from the shortcut menu after you Right-click the selected cell or range.

  • Click the More command in some of the drop-down controls in the Ribbon. For example, the Home → Number→ General → More Number Formats… drop-down includes an item named More Number Formats, as shown below

  • There are six tabs in Format Cells dialog box: Number, Alignment, Font, Border, Patterns, and Protection.

Formatting Cells Using Dialog Box

  • Number tab

  • Alignment tab

  • Font tab

  • Border and Pattern tabs

  • Select Format→Cells to display the Format Cells dialog box.

  • Select the Border tab.

  • In the Presets area, choose None, Outline, or Inside to specify the location for the border.

  • Choose OK to apply the border and color.

Dates and Times

To change the date format, select the Number tab from the Format Cells window. Select “Date” from the Category box and choose the format for the date from the Type box. If the field is a time, select “Time” from the Category box and select the type in the right box. Date and time combinations are also listed. Press OK when finished.

Formatting Columns and Rows

Steps to AutoFit Columns

  • Click on Column Header/or any other cell in the column to change its width

  • Click Home tab

  • Select format from the cells group on the ribbon

  • Click on AutoFit Column Width

  • See the effect, the column C is showing full contents i.e. Chemistry.

AutoFit for row also

  • Click on Row Header/or any other cell in the Row to change its Height

  • Click Home tab

  • Select format from the cells group on the ribbon.

  • Click on AutoFit Row Height

  • See the effect, the Row 6 is showing full contents, i.e., Anurag.

Formatting Worksheets Using Cell Styles and Applying Styles

  • Select the cells to apply a style on.

  • Choose Home tab. From Styles group, Click on Cell Styles. Here we have chosen Heading1. See the effect.

A few of the effects are discussed below:

  • Comma

  • Comma [0]

  • Currency

  • Currency

  • Normal

  • Percent

Deleting Styles

  • Right click on the style

  • Choose delete

  • You can observe, the Bad style is deleted as shown in the following figure.

Image of deleting style

Image of Deleting Style

Loading image•••

Format Painter

  • If you have formatted a cell with a certain font style, date format, number format, border, and other formatting options, and want to format another cell or group of cells the same way, place the cursor within the cell containing the formatting you want to copy. Click the Format Painter button in the clipboard group of Home tab (notice that your pointer now has a paintbrush beside it). Highlight the cells you want to apply the same formatting. The formatting will change accordingly.

  • Also, to copy the formatting to many groups of cells, double-click the Format Painter button. The format painter remains active until you press the ESC key to turn it off.

Auto Format

  • Highlight the cells that will be formatted.

  • Select Home tab→Style group→Format as Table from the Ribbon. It will show many predefined Table formats.

  • Select any one format. Here we have selected fourth table format (with green background) from the Dark category of formats. It will show the following dialog box. Check My Table has headers check box and click OK.

Hiding/Unhiding Worksheets

  • Right click on the sheet tab which you want to hide.

  • Choose Hide option from the context menu

  • To unhide the hidden sheet, right click on the any of the visible sheets. Unhide Dialog will appear. Choose the sheet from the list to unhide. In our case only one sheet is displayed to unhide.

Protect and Unprotect Worksheets

To protect worksheet

To protect worksheet you can protect your worksheet against unauthorized editing. For this you can give password protection to your worksheet contents.

Steps to protect worksheet

  • Select Home tab.

  • Click Format in cells group.

  • Choose Protect sheet from Drop Down Menu. Protect sheet dialog box will appear. Enter password to protect sheet. Reenter same password in the confirm password dialog box.

  • Now if you try to make any change in the worksheet, following dialog box will appear.

To unprotect worksheet

Steps to unprotect worksheet

  • Select Home tab.

  • Click Format in cells group.

  • Choose Unprotect sheet from Drop Down Menu. Unprotect sheet dialog box will appear. Enter password to unprotect sheet. Password must be same as used for protecting the worksheet. Press OK.