MS-Excel

[Microsoft Excel]

Q.1.   What is the MS-Excel?

Ans.   MS Excel is an electronic Spreadsheet software package.  Excel includes all standard features, such as- automatic recalculations, graphs and formulas. Formulas are used to perform basic mathematical operations, to find the average, maximum, or minimum values in a specified range of data. Any type of data Excel can used to include numbers, data and formulas.

Q.2.   What is the Spreadsheet?

Ans.   A Spreadsheet is a table used to store various types of data. The data is arranged in rows and columns to make it easier to store, organize, and analyze the information.

Q.3.   What is the use of MS-Excel?

Ans.   The package provides, statistical, financial and scientific functions. Excel is a powerful tool used to create and format spreadsheets. It is commonly used to prepare the financial statements, Business statements and accounting and in Engineering environments and to analyze numerical data.

  1. File Tab: – Office Button display several options such as-New, Open, Save, Save As, Print, Print Preview and close Button in the left side top corner of the window.
  2. Quick Access Toolbar–The quick access tool bar provides shortcut icon such as – save, Undo, Redo etc. These icons are used Save-to save the work Book, Undo- to reverse the last command and Redo-to reverse the undo command.
  3. Title Bar–Display the name of program and currently active Excel Work Book, control menu icon as like Minimize, Restore, Maximize and close button.
  4. Menu Bar–MS Excel has Seven Menus such as-Home, Insert, Page Layout, Formulas, Data, Review and View.
  5. Ribbon Tool Bar–Ribbon Tool bar is designed to help you quickly to find the commands that you need to use to complete a task.
  6. Cell Reference (Name Box)– It is positioned just below the ribbon tool bar in left side, a cell reference identifies the location of row and column.
  7. Formula Bar-A formula bar is used to apply the various type of formulas to calculate mathematical values-such as sum, subtracts, multiply and divide etc.
  8. Column Headings–Columns are known by ABCD letters in vertical in the spreadsheet.
  9. Row-Rows are mansions in numbers in left side of spreadsheet. The row travel horizontally from top to bottom.
  10. Cell-Cells are the basic rectangular building blocks of a spreadsheet.
  11. Sheets- Excel is organized like a workbook in multiple pages marked as: sheet1, sheet2, and by a default sheets-3- are displayed in order to work with more sheets.
  12. Scroll Bar-MS-Excel window has two scroll bars such as- vertical Scroll bar and Horizontal scroll bar. The Vertical scroll bar is used to move the worksheet vertically and horizontal scroll bar is used to move the Worksheet horizontally. 
  13. Task Bar-‘Task Bar’ situated bottom of the window. Displayed open several programs    and Work Books in the Task Bar.

Q.4.   How many rows, column and sheets in a workbook?

Ans.   Excel can maintain large volumes of data at a time. A workbook can contain 255 worksheets. A Worksheet can contain 1048576 row and 16384 columns. A single cell can contain a maximum of 255 characters.

          Formula for Sum–

          =sum (B2:F2) Press Enter Key [Horizontally]

          =sum (B2:B14) Press Enter Key [Vertically]

S. NO.COMMANDRESPONSE
1CONTROL+HOME KEYMove the cell in first row and column ‘A’
2PRESS TAB KEYMove the cell left to right by one-one column
3PRESS SHIFT + TAB KEYMove the cell right to left by one-one column
4PRESS ENTER KEYMove the cell top to bottom by one-one row
5PRESS SHIFT+ENTER KEYMove the cell bottom to top by one-one row
6SHIFT+àSelect left to right one by one column
7SHIFT+ßSelect right to left one by one column
8SHIFT+âSelect up to down one by one cell
9SHIFT+áSelect down to up one by one cell
10SHIFT + SPACE KEYSelection of the one row
11CONTROL+SPACE KEYSelection of the one column
12CONTROL+SHIFT+ ­Selection of the Data
13CONTROL+ASelect all worksheet
14CONTROL + Page Down KeyMove the sheet Forward
15CONTROL + Page UP KeyMove the sheet Backward
16SHIFT + F11 KeyInsert worksheet
17CONTROL + SHIFT + =Open dialog box- Insert row, column and cell
18ALT + =Auto Sum Formula
19CONTROL +1Open Dialog box- Format Cell
20CONTROL+F9Minimize worksheet
21CONTROL + F10Maximize worksheet
22CONTROL+F1Display/Hide Ribbon Tool bar
23CONTROL+F2Print Preview
24CONTROL+F4Close worksheet
25CONTROL+F5Restore worksheet
26CONTROL+2Bold-Number, data & Formulas
27CONTROL+3Italic- Number, data & Formulas
28CONTROL+4Underline- Number, data, & Formulas
29CONTROL+9Hide the selected  Row
30CONTROL+SHIFT+(Unhide any hidden rows within the selection
31CONTROL+0Hide the selected Column
32CONTROL+SHIFT+)Unhide any hidden columns within the selection
33CONTROL+ (-)Open dialog box- delete, row, column & cell
34CONTROL+FOpen dialog box-find a number
35CONTROL+GOpen dialog box-Go To, Formula, comment, etc.
36CONTROL+HOpen dialog box-Find and Replace-Number, data
37CONTROL+  ;Insert current date
38CONTROL+SHIFT+;Insert current time
39SHIFT+F2Insert comment
40SHIFT+F3Open dialog box – Insert Function
41ESCCancel a command
42CONTROL+ [Go to Data Sheet
43CONTROL + ]Go to Formula
44ALT+TABSwitch to the next window
45ALT+SHIFT+TABSwitch to the previous window
46ALT+F5Restore the size of the active window
47TAB or SHIFT + TABWhen a task pane is active, select the next or previous option in the task pane.
48CONTROL+SHIFT+ &Applies the outline border to the selected cells
49CONTROL+SHIFT+  _Removes the outline border from the selected cells.
50CONTROL+SHIFT+ $Applies the currency format with two decimal places .
51CONTROL+SHIFT+ ~Applies the General number format.
52CONTROL+SHIFT+ %Applies the Percentage format with no decimal places.
53CONTROL+SHIFT+ !Applies the number format with two decimal places.
54CONTROL+DUse the Fill Down command to copy the contents and format of the topmost cell of a selected range into the cells below.
55CONTROL+RUses the Fill Right command to copy the contents and format of the leftmost cell of a selected range into the cells to the right.
56PRESS ‘Page Down’ KeyMove one screen down side in a worksheet
57Press ‘Page Up’ KeyMove one screen up side in a worksheet
58ALT + Page DownMove one screen to the right in a worksheet
59ALT + Page UpMove one screen to the left in a worksheet
60CONTROL+SHIFT+Page DnSelect the current and next sheet in a workbook
61CONTROL+SHIFT+Page UpSelect the current and previous sheet in a workbook.
62CONTROL + F6Switches to the next workbook window when more than one workbook window is open.
63SHIFT + F10Open the short command
64F8Extend the Selection in a worksheet
65CONTROL+SHIFT+EndSelect all data start to End in the worksheet
66CONTROL+SHIFT+HomeSelect all data end to start in the worksheet

File Tab: –

          New– Create a New Worksheet.

          Open- Opens and find the Worksheet.

          Save- Save the active Worksheet by any name.

          Save As– Save the active Worksheet with a different name.

          Print Preview– Show the Worksheet when you print it.

          Close- Close the Worksheet.

[1]      Home Menu: –

This menu is divided in seven groups such as-Clipboard, Font, Alignment, Number, Styles, Cells, Editing,

  1. Clipboard Group–
  2. Cut– Remove the selected data and objects.
  3. Copy- Copies the Selected data and objects.
  4. Paste- Paste the copies data and objects on the particular cell.
  5. Format painter- Copy formatting from one place and apply in to another.
  6. Font Group-
  7. Font- Change the font of selected data.
  8. Font size- Change the font size of selected data.
  9. Bold- Bold the selected Data.
  10. Italic– Italic the selected data.
  11. Underline- Underline of the selected Data.
  12. Bottom Border- Draw Border to selected range in the worksheet.
  13. Fill Color- Fill color in the background of selected cell.
  14. Font Color- Change the color of selected text or numbers.
  15. Alignment Group-
  16. Top Align– Align text to the top of cell in the worksheet.  
  17. Middle Align– Align text so that it is centered between the top and bottom of the cell.
  18. Bottom Align- Align text to bottom of the cell in the worksheet.  
  19. Align Text Left- Align text and number to the left side in the cell.
  20. Center- Apply center alignment in selected data range.
  21. Right Align– Apply the right alignment in selected data range.
  22. Orientation– Rotate text and number to a additional angle or vertical orientation.
  23. Wrap Text– Make all content visible within a cell by displaying it on multiple lines.
  24. Merge & Center-Join the selected cells into one larger cell and centers the contents in the new cell.
  25. Number Group-
  26. Change number in currency, percentage, date and time etc.
  27. Style Group-
  28. Conditional Formatting- Display number in different color and style according your condition.
  29. Format as Table- Quickly format a range of cells and convert table by choosing a pre-defined table style.
  30. Cell Styles– Quickly format a cell by choosing from pre-defined styles.
  31. Cells Group-
  32. Insert- Insert cells, rows, or columns into the active worksheet.
  33. Delete- Delete rows or columns from the active worksheet.
  34. Format- Change the selected row high or column width, in the active worksheet.

 E. Editing-

      a. Auto Sum- Display the automatically sum of the selected cells.

      b. Fill- Fill the cell in any direction of the active worksheet.

      c. Clear- Remove the selected data and formatting in active worksheet.

      d. Sort & Filter- Display the data ascending or descending order in the worksheet.

      e. Find & Select- Find, replace text & number or select object and comments.

[2]      Insert Menu: –

This menu is divided in five groups such as-Tables, Illustrations, Charts, Links and Text.

  1. Tables Group–
  2. Pivot Table- This option is used to arrange complicated data in easier summarize.
  3. Table– Make a table to manage and analyze related data.
  4. Illustrations Group–
  5. Picture- Insert a picture from other folder or documents in active worksheet.
  6. Shapes- Insert several types of basic shapes in the active worksheet.
  7. Smart Art- Insert Smart art graphics of different category.
  8. Chart Group- Create all type of chart from any data table such as- Column, Line, Pie, Bar, Area, and other chart etc.
  9. Links Group– Create a link to a web page, a picture, an e-mail address, or a program. But it is not used to here.  
  10. Text Group-
  11. Text Box– Insert a text box that can be positioned anywhere on the worksheet.  
  12. Header & Footer– Edit the header & footer of the Worksheet. The Content in the header & footer will appeared at the top and Bottom of each printed page.
  13. Word Art– Insert decorative data in the active Worksheet.
  14. Object- Insert the Object from another program (Bitmap Image) in the active Excel Worksheet.
  15. Symbol-Insert characters that are not on your keyboard such as Alfa, Vita, Gama etc.

 [3]     Page Layout Menu: –

This menu is divided in five groups such as- Themes, Page setup, Scale to Fit, Sheet Options, Arrange.

  1. Themes Group–
  2. Change the overall design of the active Worksheet including, colors, fonts and effects.
  3. Page Setup Group–
  4. Margins- Set margins of the active Worksheet, like- top, bottom, left and right.
  5. Orientation– Set paper orientation such as- Portrait and Landscape.
  6. Size– Set paper size such as- A4, Letter, A3, and Legal etc.
  7. Print Area– Select the data area then click print area option for the print.
  8. Breaks– This option is used to only for Page break.
  9. Background– Choose an image to display as the background of the sheet.
  10. Print Titles– Specify rows and columns to repeat on each printed page.
  11. Scale to Fit Group– The maximum height and width must be set to “Automatic” use this feature. In actual size. 
  12. Sheet Options– Display or hide Gridlines and rows or columns headings in preview and print.
  13. Arrange Group–
  14. Bring to Front– Set the object to front of two objects.
  15. Send to Back– Set the object to back of two objects.
  16. Align­- Set the alignment of the multiple selected objects.
  17. Group– Make a group of many selected objects.
  18. Rotate– Flip Rotate of any selected objects.

[4]      Formulas Menu: –

This menu is divided in Four Groups as like Function Library, Defined Names, Formula Auditing and Calculation.

  1. Function Library Group–
  2. Insert Function– Click on this function-open Insert Function dialog box- In this dialog box you can use many formulas such as-Sum, Average, minimum and maximum marks.
  3. Auto Sum– Display the Automatically sum of the selected cells.
  4. Recently Used– Display the list of recently used options.
  5. Logical– Display the list of logical functions.
  6. Date & Time– Display the list of related date & time functions.  
  7. Defined Names–
  8. Define Name– Name cells so that you can refer to them in formulas by that name.
  9. Name Manager– Create, edit, delete and find all names used in the workbook.  
  10. Formula Auditing–
  11. Trace Precedents– Show all arrow that indicate of cells currently selected formula value. 
  12. Trace Dependents– Show arrow that indicate of formula of selected cells.
  13. Remove Arrow– Remove all arrow at a time.
  14. Show Formulas– Display the formula in each cell instead of resulting value.
  15. Error Checking– Check for common errors in the formulas.
  16. Evaluated Formula– Display the evaluate formula dialog box to evaluating each part of the formula.
  17. Watch window–
  18. Calculation Group-

a. Calculation options- basically every time active automatic calculation option. 

[5]      Data Menu: –

This menu is divided in five groups such as- Get External Data, Connections, Sort & Filter, Data Tools, Outline. Get External Data & Connections both are useless Groups.

  1. Sort & Filter Group–
  2. Sort– Display the name & number ascending or descending order.  
  3. Filter– Filter the greater then/less then number in the active worksheet.
  4. Advanced Filter– Filter the particular alphabet data in the active worksheet.  
  5. Data Tools Group–
  6. Text to Columns– Separate the contents of one excel cell into   separate columns.
  7. Remove Duplicates– Delete duplicates row from a sheet.
  8. Data Validation– Prevent invalid data from being entered into a cell.
  9. What-If Analysis–Scenario Manager– It is used to record title of the permanent in active worksheet. Goal Seek– Goal seek will find the right input when you know the result you want.
  • Outline Group–
  • Group– Group the selected row and columns in the active worksheet.
  • Ungroup– Ungroup a range of cell and columns that were previously grouped. 
  • Subtotal– Total several rows of related data together by automatically inserting subtotals and totals for the selected cells. 

 [6]     Review Menu: –

This menu is divided in three groups such as-Proofing, Comments and Changes.

  1. Proofing Group–
  2. Spelling Grammar– Check the spelling & grammar of the active Worksheet.
  3. Comments Group-
  4. New Comment­ – Add the comment of selected cell in the active Worksheet.
  5. Delete– Delete the selected cell comment in the active worksheet.
  6. Previous– Move the previous comment in the active worksheet.
  7. Next– Move the next comment in the active worksheet.
  8. Show/Hide Comment– Show or hide comment of the selected cell in the active worksheet.
  9. Show all Comments– Display all comment in the active worksheet.
  10. Changes Group-

a. Protect Sheet- Protect the active sheet in open workbook.

[7]      View Menu: –

          This menu is divided in five groups as like Workbook views, Show/Hide, Zoom, Window and           Macros.

  1. Workbook Views Group–
  2. Normal– View the worksheet in the normal view.
  3. Print Layout– View the Worksheet as it will appear on the printed page.
  4. Page Break- View a preview of where pages will break when this worksheet is printed.
  5. Custom Views- Save a set of display and print setting as a custom view.
  6. Full Screen – View the worksheet in the full screen mode.  
  7. Show/Hide Group–
  8. Ruler- Display or hide Ruler bar in the preview worksheet.
  9. Gridlines– Display or hide Gridlines in the active worksheet.
  10. Formula Bar– Hide/Unhide Formula bar in the active worksheet.
  11. Headings– Show/Hide Row or columns heading in the active worksheet.  
  • Zoom Group– Display large and reduce size of the active Worksheet.
  • Zoom– Open the zoom dialog box to specify zoom level of the worksheet.
  • 100%– Zoom the worksheet in 100% of the normal size.
  • Zoom to Selection– Zoom the worksheet so that the currently selected range of cells fills the entire window.
  • Window Group–
  • New Window– Create a new window containing a view of the current Worksheet.
  • Arrange All– Tile all open program windows side by side on the screen.
  • Freeze panes-Keep a portion of the sheet visible while the rest of sheet scrolls.  
  • Split- Split- Split the window into multiple resizable panes contacting views of your worksheet.
  • Switch Windows– Switch to a different currently open window.

=xxxxx=