OFFICE AUTOMATION – I EXCEL - BASICS 25. INTRODUCTION TO MICROSOFT EXCEL Microsoft (MS) Excel is a powerful spreadsheet that is easy to use and allows you to store, manipulate, analyze, and visualize data.
25.0 Objectives On completion of this lesson, you will be able to: • • •
Start MS Excel and recognize the first screen of MS Excel Navigate through the screen and understand its basic features Understand various functions of the icons and options available in MS Excel
25.1 Introduction This lesson is a logical move forward from the previous unit of MS Word. Now that you have learnt the basics of windows and MS Word, you can explore the features of MS Excel to create, store, and retrieve data. Workbook – An excel file contains 3 worksheets by default Worksheet – A sheet in excel file, which has boxes known as cells Toolbar – A tool bar is a bar which has icons to perform various functions
25.2 Launch MS Excel From the Windows desktop you can launch MS Excel either from Start menu, by clicking on “All Programs” and then “Microsoft Excel” or by clicking on the shortcut icon of the MS Excel available on desktop as shown below.
The first workbook that opens is named as “Book1� by default which can be seen in the title bar at the top of the MS Excel window.
25.3 Excel Window Layout 25.3.1 Worksheet Screen The worksheet screen starts from cell address A1 and goes up to IV65536. Every Address of the cell starts with a column name i.e. from A to IV and ends with a row number 1 to 65536.
25.3.2 Menu Bar The Menu bar is always below the Title bar and it displays the menu items. The menu begins with the word File and continues with Edit, View, Insert, Format, Tools, Data, Window, and Help. You use the menu to give instructions to the software. Point with your mouse to the menu option and click the left mouse button to open a drop-down menu. Use the left and right arrow keys on your keyboard to move left and right across the Menu bar options. You can also use up or down arrow keys to
move. Alternatively, you can also press the “Alt key” from keyboard and press the underlined letter of the menu option to open the menu. For example press “Alt + F” to open the File menu, “Alt + E” to open the Edit menu and so on.
25.3.3 Toolbar Toolbar provides shortcuts to menu commands. Toolbars are generally located just below the Menu bar. It consists of many icons to perform different tasks in Microsoft Excel. MS Excel has 20 tool bars, as shown in the figure. Two main tool bars are: • •
Standard Formatting
Standard Tool Bar
Formatting Tool Bar
25.3.4 Formula Bar Formula bar is used to key in formulae, text or values. Texts or values can be typed directly in to the cells but Formula always starts with a “+” or “=” sign.
25.3.5 Status Bar Status bar is used to know the current status of MS Excel worksheet. It also shows the status of Caps, Scroll and Num lock keys. On the status bar word NUM shows Num Lock key is in use and other keys are OFF and not appearing on the screen.
Additionally status bar has the feature of easy reference. If you right click on the Status bar the options menu appears. You can select the function for easy reference. On the status bar Sum=25 is shown as the Sum function is selected from the options menu.
25.4 Working with Workbooks Microsoft Excel file is known as a Workbook and by default the file is represented as Book1.xls. • Every workbook has three worksheets named as Sheet1, Sheet2 and Sheet3. • Worksheets are used to enter data, make charts graphs or drawing objects, manage data in the form of a table or create formats for data entry. 25.4.1 Create a New File •
To create a new file, select “New” option from “File” menu. Alternatively, Icon can also be used to create a new file
Microsoft Excel – Book1 opens, which looks like this.
Creating a new file using a Template A template is a format which facilitates and allows the user to make workbooks similar to the format. It allows you to enter data as per the requirements by automating some of the common tasks. Some inbuilt templates are available in MS Excel. Click on “Task Pane” option from “View” menu. The task pane appears as shown in the figure below. Click on “Getting Started” drop down menu. Click on the option “New Workbook” from this drop down menu. Task pane with New Workbook options appears.
To select a template to be applied onto the workbook, Click on the option “On my computer …”
Templates dialog box appears. In “General” Tab only one template is available which is selected by default. Click on “Spreadsheet Solutions” Tab and Select “Balance Sheet” template. Click “OK”.
The screen appears with the template applied.
Self-Check Questions 1. Name the first cell in the worksheet. 2. What is the number of the last row in a worksheet? 3. What is the name of the first workbook which opens by default when you start MS Excel? 4. How many worksheets are available in any new workbook? 5. Which sign is used to start a formula?
25.5 Working with Worksheets By default there are 3 sheets in one MS Excel file.
Following operations can be carried out on Worksheets.
• • • • • • •
Right click on the sheet name and this popup menu appears which shows the following options… Insert Delete Rename Move or Copy Select All Sheets Tab Color View Code
25.5.1 Selecting and Navigating in a Workbook To select any of the sheets, click on the sheet individually or if you want to select multiple sheets, press Shift key and then click on the sheets you want to select. The selected sheet is known as active sheet. To select all three sheets click on Sheet 1 and press Shift key and click on “Sheet 3”, all the three Sheets will be selected as shown.
To select any two sheets click on “Sheet 1” and press Ctrl key and click on “Sheet 3”,
Sheet 1 and Sheet 3 will be selected as shown below. To navigate among the sheets use Ctrl + Page Up or Ctrl + Page Down Ctrl + Page Up To move to previous sheet Ctrl + Page Down To move to next sheet
25.6 Working with Cells and Editing Data 25.6.1 Cell Address A combination of column and a row makes one cell address. To enter data in the sheet/cells click on the cell and start typing.
If you want to delete contents from the cell you can use delete key from the keyboard. The use of “delete” key & “backspace” key in MS Excel is similar to that in MS Word. 25.6.2 Enter “Numbers” in a cell Click on cell A1 and type “12000”. The cell will look like as shown below.
Rule for Entering Numeric data: If you press enter, the value entered in the cell will be confirmed to remain there and the cursor will move to the cell below the current cell. If you press Esc key, MS Excel will delete the value from the cell. In case you want to show 0 “ZERO” before a number like 0123. This is required when you want to create a pattern of serial number into a report. This pattern of numbers can be entered by using a ‘ (Apostrophe) sign before a number. The only disadvantage is that this will be considered as a text instead of a number. 25.6.3 Enter “Text” in a cell Click on cell and start typing “I am working with MS Excel for the first time”. The cell will appear as shown below. Rules for entering the data will remain the same.
25.6.4 Enter “Date & Time” in a cell Click on cell and start typing “08/03/2006 10:48:44”. The cell will look like as shown below. Rules for entering data will remain the same.
25.7 Concept of Cut, Copy, Paste and Paste Special Generally you cut or copy information from one cell and paste it on another cell(s) • • • •
Cut operation moves a cell or contents of the cell from one cell to another cell. Copy operation creates a duplicate copy of the cell from one cell to another cell. Paste operation pastes the cell or contents of the cell from one cell or another cell by using either cut or copy operation. Paste Special option pastes the contents, formatting and/or values from one cell to another cell. For example, if you want to copy only the values of the cells and not the formulae from the worksheet, you can choose “values” option from “paste special” dialogue box. This will copy only values of the cell and not the formulae.
Self-Check Questions 6. 7. 8. 9.
Which key is used with mouse click to select continuous sheets? How do you select discontinuous sheets? How will you enter 0 before a number? What are the row number and column name of the cell address B24?
25.8 Find and Replace Find and Replace option is helpful in finding text/data/objects and replacing it with other text/data/objects. From “Edit” Menu select “Find” option. “Find” and “Replace” dialog box appears. Enter the data in “Find what:” box and press “Find Next” button to find matching data in the worksheet.
Press “Find All” button to find all the matching data in the worksheet. Click on “Replace” Tab and enter the data in the “Replace with” box.
Press “Replace” button to replace data one by one or to replace all data at once press “Replace All” button.
25.9 Auto fill Auto fill option is used to enter data automatically with the help of MS Excel. Click on First cell and type 1. Press Enter. And type 2.
Select first and second cells and drag the Fill Handle to fill series.
In order to fill a series, enter the starting value for the series in one cell and press enter and enter a value in the next cell to make a pattern. Drag fill handle to fill the series.
•
•
Fill handle: Fill Handle is the small black square in the lower-right corner of the selection. When you bring your mouse pointer to the fill handle, the pointer changes into a black cross. Press left mouse button and keep pressing while moving the mouse down to fill the series based on the pattern. If you want to fill in series 1, 2, 3, 4, 5..., enter 1 and 2 in the first two cells. If you want to fill in series 3, 6, 9, 12..., enter 3 and 6 in the first two cells. If you want the series 1, 1, 1, 1..., you can leave the second cell blank. In the same way o If you fill Sun, Mon in the cells, the rest of the series can be filled with the help of fill handle, like Sun, Mon, Tue, Wed, Thu, Fri and so on. o Jan, Feb will fill months in the series, like Jan, Feb, Mar, Apr, May etc. o Jan 2006, Feb 2006 will fill months and year in the series, like Jan 2006, Feb 2006, Mar 2006, Apr 2006 etc. After the Dec 2006 the series will automatically show Jan 2007.
25.10 Save a File To save the file, click on “Save” option from “File” Menu or press “Ctrl + S”.
“Save As” dialog box appears. Choose the folder in which you want to save your file from “Save in:” drop down list. Enter the name for your file in the “File Name:” box. Click on “Save” button to save the file.
25.11 Close a File To close a file, from “File” menu select “Close”, or press “Ctrl + W”. If the file is not saved earlier, computer will ask “Do you want to save the changes you have made to “<File Name>”.
Press “Yes” button to save else press “No” button, to exit without saving. Press “Cancel” button to deny closing of file.
25.12 Open Existing File From File menu Click on “Open” option.
“Open” dialog box appears. From the “Look in:” drop down list select the folder in which you had saved your file. Click on the file name and click on “Open” button or double click on the file name. Your file will open on the screen.
25.13 Exit MS Excel Click on “Exit” option from “File” menu.
If the file is already saved then MS Excel will be closed, else computer will ask you “Do you want to save the changes you have made to “<File Name>.” Save the file and you can exit MS Excel.
Self-Check Questions Fill in the blanks 10. To find a value in the worksheet ………………………… option is used from Edit menu. Answer True of False 11. The command CTRL +X is used to cut the contents of the cell 12. Auto fill command can fill the series of a, b, c 13. Find command is used to find and replace one value with another 14. The command Alt + F + O is used to open a file Match the following shortcut commands with their operations. 15. Ctrl + N 16. Ctrl + X 17. Ctrl + S 18. Ctrl + C
a. Save b. New c. Copy d. Cut
25.14 Summing Up In this lesson you have familiarized yourself with the following: 1. 2. 3. 4. 5. 6.
Launching MS Excel Excel Window Layout Working with Workbooks and Worksheets Working with Cells: Editing data Cut, Copy and Paste Find and Replace
7. Save and Close a file 8. Open a File and Exit MS Excel
25.15 Answers to Self-Check Questions 1. A1 2. 65536 3. Book1 4. Three 5. = sign 6. 7. Shift key 8. Press Control (Ctrl) key and select sheet 9. Apostrophe 10. Row number 24 and Column name B 11. 12. Find 13. T 14. F 15. F 16. T 17. b 18. d 19. a 20. c
25.16 Terminal Questions 1. 2. 3. 4. 5.
How do you launch MS Excel? What is a workbook? What is a Worksheet? What is a cell address? Explain the concept of Cut, copy and paste?
25.17 Glossary • • •
Workbook – An excel file contains 3 worksheets by default Worksheet – A sheet in excel file, which has boxes known as cells Toolbar – A tool bar is a bar which has icons to perform various functions