Formatting Columns and Rows, Formatting Worksheets Using Cell Styles and Applying Styles, Format Painter

Get top class preparation for IMO-Level-2 Class-7 right from your home: fully solved questions with step-by-step explanation- practice your way to success.

Download PDF of This Page (Size: 201K)

Formatting Columns and Rows

Sometimes the contents of the columns do not fit in its width. So, to adjust them within the width of the column, we can use AutoFit feature. As you can observe, in the column C, the column header is not fully visible.

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.

  • Similarly you can apply 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.

  • Another way of automatically adjusting columns and rows is by way of best fit. To do this:

    • Place your pointer on or near the right edge of a column header of the column you wish to adjust. Notice that in this area your pointer changes to a double-headed arrow.

    • Double click your pointer, and the column to the left of it will automatically adjust to fit the data entries within it.

  • Similarly, pointing to a row header changes pointer to a double- headed arrow. Double clicking results in a best fit (taller or shorter rows).

Hide Selected Column(S) or Row(S)

Hide Selected Column(s) or Row(s)

Hide Selected Column(s) or Row(s)

  • Hide - Hides the selected column(s) or row(s), though the data is still there. Hiding column(s) or row(s) will help you to prevent unwanted changes. To hide a column, follow these steps:

  • Select the column you want to hide by clicking on the column header. Say you want to hide column C, i.e., column representing the marks in chemistry.

  • Right Click on the Column to hide and click on the Hide option.

  • See the following figure (Fig. 7.16). Column C is not visible.

Unhide Selected Column(S) or Row(S)

  • To unhide the column, follow these steps:

    • Select the visible range of columns that includes the hidden column(s).

    • Now Right Click on the selected Columns. Select Unhide from the pop-up menu.

    • You can observe, the Column C is visible again.

  • You can follow the same procedures to Hide and Unhide rows.

Formatting Worksheets Using Cell Styles and Applying Styles

  • Excel 2007 provides cell styles to quickly format a cell by choosing from predefined styles. Styles help to give a professional look to your worksheets. In Excel, all styles are cell styles. However, a defined style can be applied to an entire worksheet. Cell styles can include any of the formatting that can be applied to a cell using the options available. We can also define our own cell 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 - Adds commas to the number and two digits beyond a decimal point.

    • Comma [0] - Comma styles those rounds to a whole number.

    • Currency - Formats the number as currency with a dollar sign, commas, and two digits beyond the decimal point.

    • Currency [0] - Currency styles those rounds to a whole number.

    • Normal - Reverts any changes to general number format.

    • Percent - Changes the number to a percent and adds a percent sign.

Deleting Styles

  • Right click on the style (say if you want to remove Bad Style)

  • Choose delete

  • You can observe, the Bad style is deleted.

Format Painter

  • A handy feature on the standard toolbar for formatting text is the 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

  • Excel’s AutoFormat feature uses table styles, which are predefined collections of number formats, fonts, cell alignments, patterns, shading, column widths, and row heights to have a polished look of ranges of cells you specify. You can use these styles as-is or over rule some of their characteristics.

  • Excel has many preset table formatting options. Add these styles by following these steps:

  • 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.

  • See the effect of the formatting in the figure given below. Also, observe Table Tools Design tab with Ribbon showing multiple groups. You can change the style by clicking on the predefined table format in the Table Styles group

Hiding/Unhiding Worksheets

Hiding a Worksheet

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

  • Choose Hide option from the context menu, as shown below

  • 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

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

Protect and Unprotect Worksheets

Protect and Unprotect Worksheets

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

You can unprotect your worksheet to edit it.

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.

Developed by: