Formatting worksheets

FORMATTING WORKSHEETS

You have a lot of control over the appearance of information that you enter into a cell, Changing the appearance of a cell's content is known as formatting. Formatting data in a range can make your worksheet more readable and presentable.

Adjusting Row Height and Column Width

You can specify a row height of 0 (zero) to 409. This value represents the height measurement in points (1 point equals approximately 1/72 inch). The default row height is 15 points.

You can specify a column width of 0 (zero) to 255. This value represents the number of characters that can be displayed in a cell that is formatted with the standard font (standard font: The default text font for worksheets. The standard font determines the default font for the Normal cell style.). The default column width is 8.43 characters.

There are two main methods to change the height of a row or width of a column:

(1) By dragging the boundary below the row heading until the row is at the height that you want.

To change the width of one column, drag the boundary on the right side of the column heading until the column is the width that you want

2. (a) Click Home tab→ Cells group → Format button. Following menu appears on the screen.

(b) Click Row Height... under Cell Size section (Column Width... for the column).

A dialog box named Row Height (Column Width for column) shown below appears on your screen.

Hide & Unhide Rows/Columns

Hidden rows and columns provide a means to temporarily remove rows or columns from view without deleting them or their contents. A row or column also become hidden if you change its row height or column width to 0.

1. Select the rows or columns that you want to hide

2. On the Home tab, click Format in the Cells group. Following menu is displayed on your screen:

3. Under Visibility section, click Hide & Unhide option. Following menu appears:

4. Select Hide Rows or Hide Columns for hiding rows or columns respectively.

You can alternatively hide a selected row or column by clicking Hide option from the shortcut menu which appears by right-clicking on the selection.

To display hidden rows, select the row above and below the rows that you want to display, Open the shortcut menu and select Unhide

To display hidden columns, select the columns adjacent to either side of the columns that you want to display. Open the shortcut menu and select Unhide.

Unhiding of a selected row or column can also be done by clicking Home tab→ Format in Cells group Hide & Unhide Unhide Rows or Unhide Columns.

Adding Borders to a Cell or a Range

People often use borders to group a range of similar cells. Borders provide a quick and effective way to emphasize and segregate data on a worksheet.

To add borders around a cell or range, follow these steps:

1. Select the cell or range.

2. Click the arrow on the Borders button() in the Font group of the Ribbon's Home tab Excel displays the Borders menu.

3. Choose a border style from the menu. Excel applies the border to your selection. In addition, the Borders button icon changes to reflect your border selection.

Aligning Cell Contents

Excel's default alignments are such that cell contents appear at the bottom, numbers are right-aligned, text is left-aligned, and logical values (True or False) are centered in cells. You can use Excel's alignment tools to change the default alignments that Excel applies to cell contents.

To apply a standard alignment option, select the cell or range that you want to align and then click on one of the options from the Alignment group on the Home tab. The Alignment group is shown in the following figure and discussed below.

Vertical alignment:

The options are Top, Center, and Bottom. Default vertical alignment in a cell is Bottom.

Horizontal alignment:

The options are Left, Center, and Right.

Indent:

The options are Increase Indent and Decrease Indent. In the following figure, Increase Indent button has been pressed twice in the active cell.

Orientation:

Click the Orientation button to display a menu of predefined orientations. Excel adjusts the row height to display the text. Following is the effect of Vertical Text orientation option.

Wrap Text:

Select this option when you want a long text entry to take up less horizontal area on the worksheet.

Merge and Center:

You can merge multiple cells into a single, larger cell. This feature enables you to have cells of unequal sizes. If you have a table that spans three columns, for example, you can merge three cells at the top to form a single larger cell for the table's title. The first row in the following figure has been merged to create the heading.

You can alternatively use all the above discussed alignment options by clicking the Dialog Box Launcher button of the Alignment group in the Home tab, This opens the Format Cells dialog box shown below:

Changing the Appearance of Cell Contents

By default, Excel formats the information you enter into a worksheet by using the 11 points Calibri font. However, if you want to emphasize various parts of your worksheet, such as the headers in a list, you can apply a different font style, size, color, background, or attribute to the header cells.

To apply a standard formatting option, select the cell or range that you want to format and then select one of the following options from the Format group on the Ribbon's Home tab.

The appearance of the selected cell contents can also be altered from the Font tab of the Format Cells dialog box shown below:

Adding a Background

You can add color and shading to selected cells to provide a solid background. You can also add preset patterns, either alone or in conjunction with a solid background for even more effect.

1. Select the area that you want to modify with a background.

2. Open the Format Cells dialog box. You can press CTRL+1 to do this.

3 Click the Fill tab in the dialog box and choose the color or pattern of your choice. The Fill tab in the Format Cells dialog box looks like the following figure:

Using Format Painter

The quickest way to copy the formats from one cell to another cell or range is to use the Format Painter button (7) in the Ribbon's Home tab. Follow these steps:

1. Select the cell or range with the formatting attributes that you want to copy.

2. Click the Format Painter button. Notice that the mouse pointer appears as a miniature paintbrush.

3. Select the cells to which you want to apply the formats.

4. Release the mouse button, and Excel copies the formats. You will notice that the miniature paintbrush pointer automatically disappears.

Note: Double-clicking the Format Painter button causes the mouse pointer to remain a paintbrush after you release the mouse button. This paintbrush enables you to paint other areas of the worksheet with the same formats. To exit paint mode, click the Format Painter button again (or press Esc).

Conditional Formatting of Cells

Using Excel conditional formatting feature, you can format one or more cells based on the values in the cells. You may, for example, want to visually identify all cells in a range that exceed a certain value. The steps involved are:

1. Select the cell or the relevant range of cells to be formatted.

2. Click Home tab Conditional Formatting in the Styles group. It expands to show the following menu:

3. Choose Highlight Cells Rules. Select the appropriate rule from the sub menu that appears.

4. Specify the condition for formatting the chosen cell or range of cells.

For e.g., to format the cells where the value lies between 60 and 80, we choose Between... and specify the value and the fill color. This is illustrated in the following figure. You will see the Live Preview while you specify the values in the respective text boxes.

SORTING OF DATA

Excel's sort feature is a quick and easy way to sort data in a spreadsheet. The options for sorting your data include:

Sort in ascending order - A to Z alphabetically or smallest to largest for number data.

Sort in descending order - Z to A alphabetically or largest to smallest for number data.

The steps involved in sorting are given below:

1 Select the range of data that you want to sort.

2 On the Home tab, in the Editing group, click Sort & Filter.

3. Choose Custom Sort... from the menu. It displays the following dialog box.

4. Under Column section, choose the column you wish to sort. Set all other parameters. A sample is shown in the above dialog box, where we want to sort the list on the basis of marks in English in ascending order.

5. Click OK.

Now, the list will be sorted on the basis of English marks in ascending order. This is displayed in the following figure.

PIVOT TABLE

A Pivot Table report (or simply a PivotTable) is a dynamic table that organizes and summarizes data that exists in a tabular form. This manipulation enables you to view relationships, make comparisons, detect patterns and analyze trends among some of or all fields in your raw data.

In contrast to a table, in which you present and analyze the details of your data, a PivotTable enables you to present and analyze your data in a summarized form. Moreover, a PivotTable enables you to view different dimensions of your summarized data in hierarchical levels of detail.

The steps for creating a pivot table

1. Create data in organized manner

2. Select a cell