OFFICE AUTOMATION – I EXCEL - BASICS 27. FORMATTING IN MICROSOFT EXCEL This lesson familiarizes you with the functionalities available in MS Excel that will help the user in designing and formatting a worksheet. This lesson will help you in making your own worksheet and formatting it using various options available.
27.0 Objectives On completion of this lesson you will be able to: • • •
Format a worksheet with colors and lines. Make and design tables. Handle columns and rows.
27.1 Introduction Once you are familiar with the MS Excel basic formatting patterns and data entry techniques, you should be able to create a worksheet. This lesson deals with the formatting patterns in MS Excel.
27.2 Cell Formatting: Border Tab Borders are applied to define boundary line of the data entered in the cells. Borders help in differentiating between more than one data groups and are mainly used while creating formats. Click on “Format” menu and click on “Cells” option or press “Ctrl + 1”. Activate the “Border” tab from the “Format Cells” dialog box. The dialog box will look like the one shown here.
To apply borders to one or more cells select the cells and 1. Select the color from “Color” drop down menu 2. Click on “Style” for the line.
“Presets” are used to easily apply boundaries outside or inside the select cells.
To apply borders to the cells click on the button at the left and bottom as shown below.
27.2.1 Alternative method for Applying Borders Using Tool Bar icons To apply borders to cells click on the “Borders” icon of the Formatting tool bar. Use dropdown list box to select the “Borders”. Click on the type of border you wish to apply to the cells.
Select “Draw Borders” option from drop down list, “Borders” tool bar appears. To select a color for line, click on “Line Color” button, and select the color for the line.
Click on “Line Style” icon and from “Line Style” drop down list select the style for your border. The line will appear as shown below.
27.3 Cell Formatting: Pattern If you want to fill color in the cells select color from the palette to fill in the cells.
27.3.1 Pattern You can select the pattern also from this drop down list of colors.
27.3.2 Alternative Method for Coloring a Cell Using Tool Bar Icon Select the cells and then select the color you want to fill in the cell from “Fill Color” dropdown list of colors.
27.4 Cell Formatting: Protection “Protection” is used to protect your worksheet from unauthorized use. “Locked” is used for locking the cells from any data entry and “Hidden” for hiding applied formulas.
Important: It is suggested not to protect a worksheet unless it is highly confidential because if you forget the password the data is lost and cannot be retrieved by any means.
27.5 Concept of Table / List or Format 27.5.1 Table / List A set of data which has headings at the first row or column is represented as table/list.
27.5.2 Format A format is set of cells which follow a particular pattern for the user to enter data.
Formatting of table/list is done with the help of following tool bar items: •
Change Font Type and Size
•
Columns Auto fit selection With the help of this feature the column width can be adjusted as per the maximum number of characters available in all the cells of the column, retaining all applied formats. For example Font Type and Size etc.
•
Text Color The color of the text can also be changed with the Font Color icon of the Formatting tool bar.
•
Cell Color The color to be filled in the cell can be selected by using the Fill Color icon of the Formatting Tool bar.
•
Cell Border The border for the cell(s) can be applied by using the “Borders” icon of the Formatting Tool Bar.
With the help of these tools, a table/list can be made more attractive and presentable.
27.6 Data Range and Selection A continuous group of cell addresses constitute a data range. For example like B5 to B10. Range in excel is represented as shown below
Syntax: First cell address: Last cell address Range can be of any number of rows or columns. The example of a range is shown in the figure.
27.6.1 How to select a Data Range Consider an example of selecting a range of B5:B10. To select a data range using mouse, click on the cell B5. Keep pressing the left mouse button and move the mouse to cell B10. Alternatively you can click on the cell B5 and press “Shift” key from keyboard and click on the cell B10. The other way to select a data range is to select the cell B5 by using arrow keys and use down arrow key to move down to select the range while pressing “Shift” key. You have now selected a range of cell B5:B10. 27.6.2 Shortcut Keys for selection of range Ctrl + Shift + Right Arrow Ctrl + Shift + Left Arrow Ctrl + Shift + Up Arrow Ctrl + Shift + Down Arrow Ctrl + A
To select all data at the right side of the first cell To select all data at the left side of the first cell To select all data above the first cell To select all data below the first cell To select entire worksheet
27.7 Table AutoFormat Create a table as shown here. 1 2 3 4 5
A S. No. 1 2 3 4
B Name a b d f
C Age 25 34 23 21
D Numbers 63 95 56 34
Once the table is ready select the table by selecting the range A1:D5 and select “Auto Format…” option from “”Format” menu.
Auto Format Dialog box appears having 17 patterns of table formats, with “Sample” pattern of the Table Format selected.
You can select any of the table formats, if you select “Classic 3” format. The selected table will appear as shown.
S. No.
1 2 3 4
Name
a b d f
Age
Numbers
25 34 23 21
63 95 56 34
If you click on “Options…” button on the dialog box. The dialog box appears with “Formats to apply” frame as shown.
In this frame Number, Font, Alignment, Border, Patterns and Width/Height check boxes are checked, which indicates that all these formats will be applied to the selected table from the pattern. If you do not want any of the formats from the pattern selected in the dialog box, then you can uncheck that option in “Formats to apply” frame.
Self-Check Questions 1. A set of data which has ___________________ at the first row or column is represented as table/list. 2. A continuous group of cell addresses is known as ___________________ 3. ____________________________________ key combination is used to select the data range below the current cell. 4. When you select A1:A10 cells the selected cells will be known as _________________________ 5. The color of the border of a cell can be changed by using the color option of ___________________ tab of “Format Cells” dialog box.
27.8 Working with Columns and Rows 27.8.1 Working with Columns All columns in a worksheet are represented by alphabets from A to IV i.e. 256. Following operations can be carried on columns: • • • • •
Insert Delete Column Width Hide/Unhide Auto fit Selection
Insert Column Click on “A” to select column “A”. Right Click on “A”. Options menu appears as shown below.
Delete a Column To delete a column select the column by clicking on it. Right click on the column and from the Options menu click on Delete option the selected column will be deleted. To Undo delete column click on Undo option from Edit menu or Press Ctrl + Z. Hide/Unhide a column To hide a column, selects the column by clicking on it. Right click on the selected column from the “Options” menu, select “Hide” option. The selected column will disappear from the screen. To unhide a column, select the column before and after the hidden column. Right click on the selected columns and from “Options” menu click on “Unhide” option. The hidden column will reappear on the screen. Cut, Copy and Paste a column Cut a column means the original column will be deleted from one location and can be pasted at other location. Copy a column means copying data of a column from one location and pasting it to another location, without deleting the data in the original location. Column Width To change the column width, select the column by clicking on it. Right click on the column and select “Column Width” option from the “Options” menu. Column Width dialog box appears.
Current column width is 10.14 characters which can be set in the range of 0255 characters. The default column width is 8.38 characters. Auto fit Selection Auto fit selection feature is used to set the column width as per the maximum number of characters available in the cells of the column, retaining the applied format.
All the above operations on a column discussed above can also be executed by using options from the “Format” menu.
Self-Check Questions 6. ___________ is the total number of columns available in one worksheet. 7. To undo deletion of a column you will use __________ shortcut key combination. 8. To change the width of the column you will select _____________________ option from the menu which appears when you right click on the column. 9. Auto fit selection feature is used to set the column width as per the ________________________ of characters available in the cells of the column
27.8.2 Working with Rows All rows in a worksheet are represented by numbers from 1 to 65536. Following operations can be carried on rows: • • • • •
Insert Delete Row Height Hide/Unhide Auto fit Selection
Insert Row Click on “1” to select row “1”.
Right click on row number 1; options will appear as shown in the figure.
Delete a row To delete a row select the row by clicking on it. Right click on the row number and from Options menu select the Delete option. The selected row will be deleted from the screen. Hide/Unhide a row To hide a row, select the row by clicking on the row number. Right click on the row and select “Hide” option from the options menu. The selected row will disappear from the screen.
To unhide a row, select the row above and below the hidden row(s) by clicking on it. Right click on the selected rows and from the options menu select “Unhide” option. The hidden rows will reappear on the screen Row Height To change the row height, select the row by clicking on it. Right click and select “Row Height” option from the options menu. “Row Height” dialog box appears.
Current row height is 30 points which can be set in the range of 0-409 points. The default value of row height is 12.75 points. “Auto fit” Selection feature sets the height of the row as per the maximum size (Font Size) of data available in the row, retaining the applied format. 27.8.3 Merge Rows/Columns Kindly refer Section 26.4.3 and Section 26.4.4 of lesson number 26, where Merge Cells has been discussed in detail. All the above operations on a row just discussed can also be executed by using the desired options from the “Format” menu.
27.9 General Calculations and Formulas Every formula in excel starts with “=“or “+” sign. 27.9.1 Addition Type “=2+3” in one cell and press enter the answer will be “5”.
Else: In cell A1, type “2”, in cell A2, type “3” in cell A3, type “= A1+A2” and press enter. Cell A3 will have “5” as answer. 27.9.2 Subtraction Type “=3-2” in one cell and press enter the answer will be “1”.
Else: In cell A1, type “3”, in cell A2, type “2”, in cell A3, type “= A1-A2” and press enter. Cell A3 will have “1” as answer. 27.9.3 Multiplication Type “=3*2” in one cell and press enter the answer will be “6”.
Else: In cell A1, type “3”, in cell A2, type “2”, in cell A3, type” = A1*A2” and press enter. Cell A3 will have “6” as answer.
27.9.4 Division Type “=6/2” in one cell and press enter the answer will be “3”.
Else: In cell A1, type “6”, in cell A2, type “2”, in cell A3, type “= A1/A2” and press enter. Cell A3 will have “3” as answer. 27.9.5 Use of Brackets The signs of brackets “( )”are printed on “0” and “9” keys of the keyboard. In cell A1, type “6”, in cell A2, type “2”, in cell A3, type “4”, in cell A4, type “5” and in cell A6, type “=(A1/A2)+(A3*A4)” and press enter. Cell A6 will have “23” as answer.
27.10 Insert Comments Inserting comments is like adding a remark to a cell. A cell which has a comment added to it is indicated with a red color anchor at the top right side corner. Right click on the cell where you want to add a comment and Select “Insert Comment”. Example of a cell with a comment is shown below. Click anywhere on the screen, this comment will disappear from screen leaving only red anchor at top right corner of the cell. Right click on the cell and select “Edit Comment” from Options menu to edit the comment.
Self-Check Questions Fill in the Blanks 10. __________________ is the range for row height 11. __________________ is the default row height 12. __________________ is selected from the menu in order to edit the comment
27.11 Summing Up In this lesson you have familiarized yourself with the following: •
• • • • •
Working with Worksheets o Cell Formatting: Border o Cell Formatting: Pattern o Cell Formatting: Protection Working with Worksheets o Insert, Delete, Move or Copy, Hide/Unhide Concept and Making of Table/List or Format, Data Range Working with Columns and Rows Introduction to formulas Entering a formula and Inset Comment
27.12 Answer to Self-Check Questions 1. Headings 2. Data range 3. Ctrl + Shift + Down Arrow 4. Range 5. Border 6. 256 7. Ctrl + Z 8. Column Width 9. Maximum Number 10. 0-409 Points 11. 12.75 12. Edit Comment
27.13 Terminal Questions 1. 2. 3. 4.
How do you insert a column or row? How do you delete a column or row? What is a comment and how do you insert a comment on a cell? What is a range?
27.14 Glossary • •
Boarders and Colors: Line and colors to be filled in the cells Rows and Columns: Horizontal and vertical line of cells are known as rows and columns.