Smart MS Excel

  Summary :- In this session , you learned : What is an Excel workbook and worksheet ,and how to work in it . Data is entered in the cells and can be edited , if required . Names can be given to a single cell or a range of cells. You can move in the worksheet either with keyboard ,mouse or menu. Data which has been entered can be searched and replaced . The contents of the cell can be copied and moved anywhere in workbook. The worksheet ,which has been created, can be saved and you can also protect it from unauthorized users by giving passwords. Commands to leave the Excel workplace and come out to Windows shell.

Exit Excel within workbook  :- When you work is finished and it has been saved properly  to close the file and exit MS- Excel , Choose File -> Close command  Choose File -> Exit , If you have not saved your work before closing the file , the dialogue box to save the worksheet is displayed . Cheek Your Progress :- 1.  What are the different features of Excel that provide the ease of work ? 2. Differentiate between a Workbook and a worksheet .  3. What are the different type of data can be entered in worksheet cells? 4. What is fill -handle and how is it useful? 5.  Explain the three ways of referencing the cells. 6.  What are the different ways to name or copy cell contents? 7.  How the access of Workbook can be restricted from other users?

Save and Protect Workbook Within Excel :-  After you finish up your work with the workbook you will required it to be saved so that it can be opened later to resume the work in it. To give the name and save the workbook :- Choose File -> Save As command . To Simply save the workbook :- Choose File -Save command . or  Click on the Save File Tool button To Protect the Workbook :- Your data can be very confidential which has to be saved from Unauthorized people . Click on Options button from the File -> Save   As dialogue box. You can restrict the access of that workbook from other users in 3 different ways: Protection Password  1. Type the Password. which will required to open the workbook .The Password can be up to 15 characters long ,can include alphabets (Upper case or Lower case ), numbers and special characters. It is case sensitive also . 2.You will be asked to re-enter the same password . 3.Click on ok . Write Reservation Password:- This password will be r...

Inserting or Deleting Rows and Columns within Excel Worksheet :- Many a times you may require to add a row of data or a column of data which you forgot to enter earlier . Excel provides you the facility to insert rows or columns into the existing worksheet very easily . One thing , which has to be taken care of , is that inserting a row of data will shift the rest of the rows down and cause the last row of the worksheet to be removed . Same is the case while we insert a column in the worksheet , The rest of the columns shift right . This happens because the total number of rows and columns remain the same . If there is any data in the last row or column of the worksheet that will be lost . To insert cells , rows or columns . 1. Select the cell (s) where you want to insert the cell (s),row or column. 2. From the Insert menu.          1. Choose Entire Column to insert the column.         2. Choose Entire Row to Insert the row . Choose Shift ...

Rearrange Cell Contents Within MS Excel :-  Rearrange cell contents includes moving or copying or clearing cell contents . This also involves inserting or deleting rows or columns . This can be done either by cut and paste method or drag and drop method .   Move or Copy Cell Contents . Drag and Drop method  1. Select the range of cells which are to be moved . 2. Point to the border or range . The mouse pointer will change its shape to an arrow. 3. To move , press the left mouse button, drag it to the new location and release the mouse button . 4. To copy , Press the cell key while dragging the contents. Short -end menu.    1. Press the right mouse button while dragging and dropping cell contents . 2. From the short  -end menu, choose Move to move the contents copy to copy the contents. Cut and Paste Method  1. Select the cell range . 2. Select Edit - > Cut ( to move the range of cells )                 ...

Search and Replace Data Within Excel Worksheet :- To look for the data which is entered by the user ,  Choose Edit -> Find .  To  Replace the data , click on Replace . Button Or Choose Edit -> Replace .  

  With Mouse :- You can move the mouse pointer to move to any cell of any worksheet of the workbook. This is done through:- 1. Click on up-arrow or down -arrow button on the scroll bar . 2. Click on the sheet tabs. 3.Click on any cell . 4. To move quickly to any cell choose Edit ->Go to  With Keyboard  TO Move                                                                   KEY PRESSED  Right ,Left ,Up ,Down one cell                                 Arrow Keys Up or Down one screen                                              Pg Up or Pg Down  Beginning of row          ...

A name can be given to a single cell or a range of cells ( e.g.B3:F9). The range name can be used in place Of these cell references .Also the range specified for a range name can be modified. To define a name ,following points have to be considered . 1. Name contain only letters from A-Z., or a-z, numbers 1-9.     The period ( .) and the underscore ( _ ) . 2. Maximum length can be up to 255 characters . 3. Name cannot be same as cell reference like $F10$ or F10 . 4.  Spaces are not allowed .

T here are 16384 rows and 256 columns in one worksheet .The first cell is labelled as A1 (column as A and Row number as 1) or R1C1 . Example :- 3C3 refers to row 3 column 3 and is equivalent to C3. Thus he last cell is labelled as IV16384. This is called the cell address And they reused to identify the cell . Cell reference are given in different ways: Relative -This refers to the cell relative to the given position . Example: Enter the following values : In cell A3 enter the value 34 In cell A4 enter the value 23 In cell A5 enter the value 89 In cell A6 enter the formula =A3+A4+A5 Copy this formula to the cell A7,and see the result. Observe the formula entered in cell A7.It is A4+A5+A6.The formula entered in cell A6 means the sum of the values given in the three cell given above the current cell. Therefore when we move down , the relation specified in the formula was copied (  I . e . the three values given above the current cell which is A7 now ) Absolute -  This refers to ...

Create a Custom List . 1. Enter the given list starting from cell L5 to L8 .      *Left       *Right       *UP      *Down  2. Select this range of cells . 3. Select Options .. from Tools menu. 4. Select Customs tab from the Custom dialogue box. 5. Click on OK. OR 1. Select Tools -> Options . 2. Click on Custom List tab. 3. Type the custom list in the List Entries Box. 4. Click on OK. Data , If written incorrectly , can be edited by following ways: 1. Select the cell with some value to be edited . 2. Press F2 and using Backspace key erase the previous entry. you can retype the correct data. OR Simply select the cell and retype the entry . To bring back the previous entry either click on Undo button on Standard Toolbar or choose Edit-> Undo option. OR Pressing Alt + Backspace or ctrl-Z keys can also be used to perform the undo task. OR To clear the cell entry , select the cell to be cleared and press Del...

The various kinds of entries that can be made in a cell are : Text - First select the cell in which the data has to be entered and type - in the text. Press Enter Key to finish your entry . The data will be displayed in the active cell  as well as in the formula Bar . To enter digits as text ( calculations cannot be done using these kind of entries ) use an apostrophe(') as the first character . Number -Numbers include digits from 0 to 9 and some special characters like: $ % + - / ( ) , . E e Date and Time -Date and Time entries are interpreted in a special way in Excel. They are represented as numbers for Excel given to each day from 01.01.1900 ( Serial number 1 ) to 31.12.2078 (serial number 65380 ). Although when you type a date in the cell like 01/01/2022, It will be displayed as it is. Example : Ty[e TODAY ( ) in a blank cell and press Enter Key .( This function will display the system date and time ). Data in Series - You con fill a range of cells either with the same ...

Open the workbook to start your work on a worksheet .At times , you might required name to insert or Delete a worksheet , and cells to be selected . Create a Workbook  -Select the New ..command from the File menu. The workbook is opened by Default Name of BOOK1. Open a Workbook - To open an existing workbook ,sheet Open option from the File menu . Names of The Recently used files are listed in the file menu and can be selected by merely clicking on the name Of the file to be opened . Find a Workbook - Choose  File ->Open command .Select Find File . option from the dialogue box That is opened .Consequently ,another dialogue box is opened in which the details like name ,directory are to be given. Insert a Worksheet - To insert a workbook between two worksheets choose Worksheet option from the Insert menu. Delete a Worksheet -To remove a worksheet from the workbook ,select Delete Sheet  from the Edit menu. A dialogue box will appear to confirm this action. Move the ...

MS-Excel document is called as a workbook. Each workbook contains 16 worksheets by default. This number can by changed if required by resetting the default options . Worksheets within workbooks Make it easy to bind files of related information.  When a workbook is opened , all worksheets of that workbook become available to the user automatically .

  Menu Bar -Different options for selection . Standard Toolbar -It is displayed by default and provides shortcuts for the common commands used. Formula Bar  -Any text, data or formula that is being keyed in or edited is displayed in it . Name Box-D isplays the   address of the current cell. Scroll Box- Used to move up or down in the worksheet using mouse. Sheet Tab -Displays the name of  the current worksheet that can be changed by double clicking on it and giving a new name in the dialogue box . Status Bar -Left part displays the various messages like Ready mode (when Excel is ready to accept data into the spreadsheet ) or Edit mode (While in the editing mode ). The right part highlights the status of keys of the keyboard . Row Headings -They are numbered as 1,2,3,........16384. Column Headings -These are addressed as A,B,C,...IV. Active Cell -Intersection of each row and column is called a cell.

B efore you start working on the worksheet ,the knowledge of the concepts and movement on the workbook is necessary. TO Start Excel . 1-Start Windows , 2-Click on the program manager icon. 3-In the program manager window ,chick on MS-Office icon. 4-In the MS-Office window , chick on MS-Excel icon as shown below ;                                                                      Microsoft Excel