OFFICE AUTOMATION – I EXCEL - BASICS 26. WORKING WITH MICROSOFT EXCEL This lesson familiarizes you with the basic features of MS Excel. This will help you begin working with MS Excel worksheet.
26.0 Objectives On completion of this lesson you will be able to do the following: • •
Navigate around in a worksheet Formatting of worksheet o Number Tab o Alignment Tab o Font Tab
26.1 Introduction In the last lesson you were introduced to MS Excel. This lesson is a logical move forward and deals with the data entry and basics of MS Excel. Formatting – A type of style to be applied to cells Alignment – Manage data in a way that it looks presentable.
26.2 Navigation around the Worksheet Navigating around a worksheet involves the selection of a cell or placing the cursor on a particular cell. For Example if your cursor is at the A100 cell and you want to click on B60 cell, first go to the column B and then go to row number 60 by using scroll bar. Under column B at the row number 60, you can click to select the cell. If you are at the cell address AI200 and you want to go to the A1 cell (the first cell) of the worksheet press Ctrl + Home keys. If you are at the cell address A1 and you want to go to the last cell of worksheet up to which you have entered data in the worksheet, press Ctrl + End keys to go to the last cell.
As shown in the figure you have entered data in the cells from A1 to A14. Now if you press Ctrl + End key, cell A14 will appear as the current cell.
If you have entered data in 5 columns of a row, you can select the first cell by pressing Home key, and by pressing the End key, you can select the last cell of the row. Press Ctrl + Home To select the first cell of the worksheet Press Ctrl + End To select the last cell of the worksheet (the cell of last data typed) Press Home To select first cell of the row Press End To select last cell of the row You can also use arrow keys to navigate through the worksheet. Up arrow Down arrow Left arrow Right arrow Page Up Page Down
To select one cell above the current cell To select one cell below the current cell To select one cell at the left of the current cell To select one cell at the right of the current cell To move one screen above the current cell To move one screen below the current cell
Shift + arrow keys are used to highlight/select the current cell and adjacent cell(s) in the direction of arrow keys. Ctrl + arrow keys are used to move to the last filled cell of the row or column in the direction of arrow keys.
Ctrl + Shift + Arrow keys are used to highlight/select the filled cells (the cells which have some data) of the worksheet. These keys are mostly used to select number of cells. Selected cells are known as range.
26.3 Cell Formatting To format cells click on “Cells” option from the “Format” menu. “Format Cells” dialog box appears with “Number” tab activated. 26.3.1 Number Tab: General General format keeps the data as entered by you without any change in appearance of data in the cell.
26.3.2 Number Tab: Number In “Number” format the decimal places for the data can be defined. For example, data with 2 decimal places looks like as shown below.
26.3.3 Number Tab: Currency In “Currency” format the data with 2 decimal places appears as follows:
26.3.4 Number Tab: Accounting In accounting format the data entered with 2 decimal place, appears as follows:
26.3.5 Number Tab: Date In “Date” format the data looks like, as defined in “Type” and you can select any type to represent date in the cell.
26.3.6 Number Tab: Time In “Time” format the data looks like as defined in “type” and you can select any type to represent time in the cell.
26.3.7 Number Tab: Percentage In “Percentage” format the data with 2 decimal places looks like as shown in the figure:
26.3.8 Number Tab: Custom In “Custom” format you can format data in several ways, for example, Date and Time, Decimal and Percentage and many more. You can also define your own format, by typing the format you want in place of the word “General” and press “Enter”.
In “Type:” box, type the format in the way you want your data to appear on the worksheet, for example 0.00(for a number) or hh:mm (for time) or dd/mm/yyyy (for date).
26.4 Cell Formatting: Alignment Tab 26.4.1 Alignment Tab “Alignment” tab aligns the text in a worksheet in many ways.
26.4.2 Text Alignment “Text alignment” can be either horizontal or vertical. Horizontal text alignment means left to right alignment, either left, centre, right or justified. Vertical text alignment means top to bottom alignment, top, centre or bottom.
As shown in the picture here, you can select the required alignment settings from the drop down menus of “Horizontal” and “Vertical” options.
Vertical Alignment is useful when the size of the text available in cell is less than the height of the cell/row. 26.4.3 Text Control “Text control” options are used to manage the text as per the requirements and the constraints of the cell or worksheet. “Wrap Text” option is used to display the text completely in the cell while broken into various lines. “Shrink to fit” option is used to arrange the text as per the cell dimension.
“Merge Cells” option is used to show multiple cells as one cell. 26.4.4 Merge Cells: Alternative Method using Toolbar Icon This feature is used to combine many cells so that they appear to be one cell. Click on “Merge and Center” button to merge the cells.
26.4.5 Orientation In the “Orientation” box, the text can be presented vertically or inclined to any angle in the cell. Select the cell and from “Format Cells” dialog box click on “Alignment” tab, click on the red mark of the orientation frame and drag it on the D shape in the box, to assign the angle to the text. Alternatively you can enter number of degrees in “Degrees” box to have text inclined to the degree of angle given in the box. You can also click on the box having text written vertically. Your text on the screen will appear vertically in the selected cell(s). 26.4.6 Format Cells: Left, Center and Right alignment using toolbar icons To make the text left aligned click on “Align Left” icon. To make the text center aligned click on “Center” icon. To make the text Right aligned click on “Align Right” icon.
26.5 Cell Formatting: Font Tab This “Font” tab is used to change font type, size, style and color of the text.
26.5.1 Font Select the font type from the Font list box. For example the selected font is “Verdana” as shown below.
26.5.2 Font Style and Size Select the “Font style” to make it Bold, Italic or Underline. Change style from the list. For example the selected style is “Regular”. In the same way font size can also be selected. For example selected font size is “10”.
26.5.3 Underline Select “Underline” either single or double from underline list.
26.5.4 Effects Click and check the boxes against the effect name to apply them to the text.
26.5.5 Colour To apply the colors to the text click on the “Color” drop down menu.
26.5.6 Preview “Preview” box shows text as per the options selected in the font tab.
26.5.7 Format Cells: Font Tab: Alternate from Tool Bar These options are alternate to the options of Font Tab of Format Cells dialog box.
To make text “Bold” click on
or press “Ctrl + B”
To make text “Italic” click on
or press “Ctrl + I”
To make text “Underline” click on
or press “Ctrl + U”
26.5.8 Alternative Method for Coloring a Text Using Tool Bar Icon Select the cells which contain text and then select the color you wish to fill in the text from “Font Color” drop down list.
Self-Check Questions Fill in the blanks 1. _________________ option is used to wrap the text from Alignment tab of Format Cells dialog box. 2. _________________ option is used to convert a number into a degree pattern for example 450. 3. _________________ format is used to format a number into a percentage for example 100%. 4. _________________ shortcut key combination is used for going to last cell of the worksheet. 5. _________________ shortcut key combination is used to select the current cell and the cell below it. Answer True or False 6. Warp text can be used for more than one cell 7. Custom Category is capable of formatting the cell only for date and time format Match the following shortcut commands with their operations 8. Ctrl + Home 9. Ctrl + 1 10. Right Arrow 11. Down Arrow
a. Used to move forward b. Used to move downward c. Used to go to first cell of the worksheet d. Used to open “Format Cells” Dialog Box
26.6 Summing Up In this lesson you have familiarized yourself with the following: • •
Navigation around the Worksheet Formatting a Worksheet: Cell Formatting o Number Tab o Alignment Tab o Font Tab
26.7 Answers to Self-Check Questions 1. 2. 3. 4.
Wrap Text Superscript Percentage Ctrl + End
5. Shift + Down Arrow 6. T 7. F 8. C 9. D 10. A 11. B
26.8 Terminal Questions 1. 2. 3. 4. 5.
How do you navigate through the worksheet? How do you merge cells in a worksheet? What is the use of vertical alignment? How do you make a text bold in a cell? How do you change the font color?
26.9 Glossary • •
Formatting – A type of style to be applied to cells Alignment – Manage data in a way that it looks presentable.