Introduction of Spreadsheet (MS Excel)

MS Excel is a windows based spreadsheet (worksheet) software in the Microsoft Office suite. It allows us to store, organize and analyze numerical and text data. It consists of several spreadsheet consisting of row s and columns. We can be used to perform mathematical calculations. The details of bank pass book, tax inventory, purchase and sales can also be maintained using a spreadsheet package. Some of the spreadsheet package examples are: Lotus 1231, Quatrpro, UPP, MS-Excel

Common terms used in Spreadsheet

1. Spreadsheet
   Spreadsheet is the main and the biggest part of excel screen, which looks like a graph paper. It is very big is size and you can't see full spreadsheet at a time on the screen, but its upper left part is definitely visible from where you can start entering the data. Each small rectangle of spreadsheet is called a cell, which you can format and increase/decrease its size as per your data requirement.
2. Worksheet
   Microsoft Excel is a popular choice among individuals and companies to organize analyze and present data in columns and rows in a document called worksheet. Many worksheets can be created. A workbook consists of 3 worksheets by default. Each work is named uniquely like Sheet1, Sheet2, Sheet3 etc.
3. Workbook
   Excel documents and files are known as workbooks, Each workbook contains 3 worksheets. Adding or deleting the sheets can change the number of sheets. A workbook can also contain chart sheets, which are named as Chart 1, Chart2 etc. by default.
4. Workspace
   A workspace is just a file that contains display information about your workbooks, not the actual workbook themselves. To do this  Go to File > Save workspace, enter a file name: box and click ok. This will save workspace as an .xlw not a standard .xls file.
5. Cell and Cell Address
   The intersection of a column and a row is called a cell. Each cell has a name or cell address. The cell address consists of the column letter and row number. For example, the first cell is in first column and first row. First column name is A and first row number is 1. Therefore the first cell address is A1. Similarly the address of last cell is IV65536 i.e. column IV and row number is 65536. The total cells in a worksheet are 256x65536.

Different menus available in MS-Excel

1. Home Menu: The menu consists of different buttons. Each button performs their own functions separately. The menu consists of cut, copy, paste, format painter under clipboard sub-menu. Font [font color, font style, size, bold, italic, underline, button border, background, increase, font size, decrease font size], Alignment [Top, Button, Middle, Centre, Left Right, Increase Indent, Decrease Indent, Orientation, Text Wrap, Merge etc.],Number Style [Conditional Formatting, Format as Table, Cell Styles]. Cells [Insert, Delete, Format], Editing [Auto sum, Fill, Clear, Sort & Filter, Find and Select] etc.
2. Insert Menu: The menu consists of different sub menus and options performing different tasks. The menu includes Tables (Pivot table, table), Illustrations (picture, clip art, shapes, smart art) Charts (Column, line, pie, bar, scatter, other charts), Links (Hyperlink). Text (text box, header & footer, word art, signature line, object, symbol) etc.
3. Page Layout Menu: The menu consists of different sub-menus are Themes (Themes, Colors, Fonts, Effects). Page Setup (Margins, Orientation, Size, Print Area, Breaks, Background, Print Titles). Scale to Fit (Width, Height, Scale), Sheet Option (Gridlines, Headings), Arrange (Bring to Front, Send to Back, Selection Pane, Align, Group, Rotate) etc.
4. Formula Menu: The menu consists of different sub-menus. These sub-menus are Function Library (Insert Function, AutoSum. Recently Used, Financial, Logical, Text, Date and Time, Lookup and Reference, Math & Trig. More Functions), Defined Names (Name Manager, Define Name, Create from Section), Formula Auditing (Show Formula, Evaluate Formula, Error Checking. Trace Precedents, Trace Dependence. Remove Arrow, Watch Window).. Calculation (Calculation Options, Calculate Now, Calculate Sheet) etc.
5. Data: The Data Menu consists of different sub-menus. These sub-menus are Get External Data (From Access, From Web, From Text. From Other Source), Connections (Refresh All. Connections. Properties, Edit Links). Sort & Filter (Sort, Filter. Clear, Reapply, Advanced). Data Tools (Text to Column, Remove Duplicates, Data Validation. Consolidate, What-if Analysis), Outline (Group. Ungroup, Subtotal, Show Detail, Hide Detail) etc.
6. Review: The Review menu consists of different sub-menus. Some of them are Proofing (Spelling, Research. Thesaurus, Translate), Comments (New Comment, Show all Comment e Changes (Protect Sheet, Protect Workbook. Share Workbook. Protect and Share Workbook. Allow Users to edit Ranges, Track Changes) etc.
7. View: The View menu consist of Workbook views (Normal, Page Layout, Page Break Preview, Custom Views, Full Screen). Show/Hide (Gridlines, Formula Bar, Headings), Zoom (Zoom, 100%, Zoom to Section), Window (New Window, Arrange All, Freeze Panes, Split, Hide, Unhide, Save Workspace, Switch Windows), Macros etc.

Main bar available in MS-Excel.

A) Title Bar: Displays the application name, file name and various Window controlled like minimize button, maximize button and close button. 

a. Minimize button: This is used for changing a window/sheet into a button.
b. Maximize button: This is used for enlarging a window/sheet minimized or restored.
c. Close button: This is used to close a window/sheet.

B) Menu Bar: This has different options for selections . In addition to minimize and close button (described above) it has a restore button which is used for bringing a window/sheet to its original size and adjusting the size of a window/sheet

• File: This helps in creating a new file; opening an existing file; saving a file: printing print preview; setting up of print area; closing the worksheet; exiting Excel etc.
• Edit: This helps in copying, cutting, deleting a range of text: Pasting a text which has been copied or cut from some other location: Clearing the contents of cells; Finding the particular text in the worksheet etc.
• Tools: This helps with the spell checker, protection of worksheet, workbooks by providing the password. The worksheet can be customized according to one's specification etc.
• Data: This is used to sort (ascending/descending), filter the list, to find subtotal etc.
• Window: This is used to hide/unhide the work book; to create new window, to split the pane etc.
• Help: This can be used to get any help about Excel.

C) Standard ToolBar

• New: This is used to create a new work book.
• Open: This is used to open an existing file.
• Cut: This is used to move a selected block from one location to another.
• Copy: This is used to copy a selected block we want from one location to another.
• Paste: This is used to make appear the block selected during the copy or cut operation at a certain location.
• Undo: This is used to retain any modifications made to a file.
• Redo: This is used to reverse the last undo action performed on the file.
• Auto sum Button: This is used to add the numbers in particular range.
• Paste Function Button: This is used to do different operations on a selected set of numbers, such as finding average or finding the minimum or maximum of set of numbers etc.
• Sort Ascending: This is used to arrange a set of numbers in ascending (increasing) order.
• Sort Descending: This is used to arrange a set of numbers in descending (decreasing) order.
• Chart Wizard Button: This is used in creating chart graphs for a set of numbers.
• Drawing: This is used to add the drawing tool bar just above the status bar.
• Spelling Checker: This is used to check the spelling and grammatical errors in the file.

D) Formatting Tool Bar

• Font: This helps in changing the style of text in the worksheet. You can select a required font from the available font list and change style of the text which is inside the selected block.
• Font Size: This helps in changing the size of the text. You can select required size for the font from the available list and change the size of text which is inside the selected block.
• Bold: This helps in making the selected text look bolder than the text.
• Italic: This helps to make the text in the selected block look . Underline: This helps in getting a underline to the selected text. slanted.
• Align Left: This helps to left justify the contents of cell which is inside the selected block.
• Centre: This helps to centre justify the contents of cell which is inside the selected block.
• Align Right: This helps to right justify the contents of cell which is inside the selected block.

C) Formula Bar

It shows formula you typed.

D) Status Bar
The status bar is located at the bottom of the Microsoft Excel Window. It displays Ready. or Edit on the left hand side and NUM on the right hand side. 

• Ready: This indicates that the work book is ready to accept data from the user.
• Edit: This indicates the work book is in edit mode that means the contents of the cell are being modified or a new content is being placed in the cell.
• NUM: This appears on the right hand side of the status bar and represents the status of Num Lock indicator on the keyboard. If Num Lock is enabled on the keyboard. NUM will appear and if Num Lock is disabled NUM will disappear from the Status Bar

Components of an Excel Work Book 

1. Row Numbers: The horizontal group of cells is termed as a row. Each row is assigned a number. The row numbers appear on the left side of the work book.
2. Column Number: The vertical group of cells is termed as a column. Each column is assigned a number. The column number appears below the Formula Bar in the workbook.
3. Vertical Scroll Bar: The vertical scroll bar consists of two buttons Up Arrow Scroll Button and Down Arrow Scroll Button. Clicking of any of these buttons allows you to see those rows of a sheet which are not visible on the screen.
4. Horizontal Scroll Bar: The horizontal scroll-bar consists of two buttons Right Arrow Scroll Button and the Left Arrow Scroll Button. Clicking of any of these buttons allows you to see those columns of a sheet which are not visible on the screen.
5. Select all Button: This is the first place where the row numbers are column headings meet.
6. Sheet Tab: Using this one can move from one sheet to other of the work book. By default an Excel workbook has three sheets and has its name displayed. The default names of these sheets are Sheet1, Sheet2 and Sheet3. These names can be changed and a new natte can be assigned to it. This tab appears just above the Status bar.
7. Minimize, Maximize/Restore and Close Buttons: There are two sets of Minimize, Maximize/Restore and Close Buttons; on the right hand corner of the Title Bar which is corresponding to Excel window and another set on the right hand corner of the Menu Bar which is corresponding to Excel workbook.

• Minimize Button: This is used for changing a Excel window/book into button.
• Maximize Button: This is used for enlarging a Excel window/book. Restore Button: This is used for changing a Excel window/book into the original size and adjusting the size of a window/book.
• Close Button: This is used to close the Excel window/book.

How to Open MS Excel ?

Move the mouse on the down right corner of the screen or press (Windows + C) key from  your keyboard.
Different icons appears
Click on Search and Type Excel and click in Search. MS Excel will open.
OR
Go to start menu
Choose all program
Click ms office
Click ms Excel. Now Ms excel will open as shown in the figure.

Important Common Functions of Excel

1. Sum( )
   It is mathematical function used to add the numeric value in a range of cells. 
   E.g. = SUM(starting cell address: ending cell address)
2. Average( )
   This function calculates and returns the average (arithmetic mean) of the numeric values in the given range of cells. It is statistical function. 
   E.g. = Average (C17:C20)
3. Max( )
   This function returns the largest value in the given range of cells.
   E.g. = Max(C5:C10) - Highest value in the range C5 to C10
4. Min( )
   This function returns the lowest value in the given range of cells.
   E.g. = Min (C5:C10) - Lowest value in the range CS to C10
5. Count( )
   This function used to count the number of cell addresses containing the data. It is a statistical function. =Count (range) 
   e.g. =count(C5:C12) Numeric cells in the range C5 to C12
6. Upper( )
   This function is used to convert the text in a cell address to capital letters. 
   =Upper (cell address)
7. Lower( )
   This function is used to convert the text in a cell address to lower case letter.
   =Lower(cell address)
8. Counta( )
   Counts the number of cells that are not empty in the specified range. 
   =Counta(range)
9. Countblank( )
   Count empty cells in a specified range of cell. Cell with zero values are not counted.
   =Countblank(range)

Frequently used command in Excel

[Note: ⇐ ⇒ ⇑ ⇓ are the arrow keys of keyboard]

Features of MS Excel

1. Office button: Displays as a Microsoft Office logo and when clicked, displays a list of document management actions, such as save or print and list of recently opened workbooks.
2. Quick Access toolbar: Contains buttons for commonly used commands which can be executed with a single mouse click.
3. Tabs: Commands and features in the ribbon are organized into related group which are accessed by clicking a tab name.
4. Title bar: Displays workbook name followed by Microsoft Excel.
5. Ribbon: Area from which commands and features for performing actions on a cell or worksheet are accessed. Begin by selecting a tab and then choosing the command or feature.
6. Name text box: Displays the active cell address or name assign to active cell.
7. Formula bar: Displays the contents stored in the active cell.
8. Active cell: Location in the worksheet that will display typed data or that will be affected by a command.
9. Worksheet area: Contains cells used to create the worksheet.
10. Cell pointer: Select cells when you see the icon by clicking or dragging the mouse.
11. Vertical and horizontal scroll bars: Used to view various parts of worksheet beyond the current screen.
12. Sheet tabs: Identifies the worksheets in the workbook. Use these tabs to change the active worksheet.
13. Status bar: Displays current mode, action messages, buttons and zoom slider.
14. Relative cell reference: Formula with relative reference is copied, the cell reference used in the formula will automatically change in the copied cell. E.g. BC*C2 in cell D2 is copied to D3 and D4. The formula will be =B3*C3 in the cell D3 and =B4*C4 in cell D4.
15. Absolute cell addressing (reference): A reference to a particular cell or group of cells that does not change i absolute cell reference. E.g. in spreadsheet program, the cell reference “$A$3" is an absolute cell reference that always points to the cell in the first column and third row.
16. Cell Reference: A cell reference identifies the location a cell or group of cells in worksheet.
17. Mixed reference: A mixed cell reference is a combination of relative and absolute cell references.
18. 3-D reference: The 3-D reference in Excel refers to the same cell or range on multiple worksheets.
19. Chart Sheet: A chart sheet is a Microsoft excel worksheet that is dedicated to one sheet.
20. OLE: Object Linking and Embedding is Microsoft Framework for a compound document technology. It is the technique used to insert data from one paragraph to another.
21. Source program: In general, source is the location of information is gathered. For example if you are coping or doing work in database and data need to be bring from Excel, then excel is source program.
22. Macro: Macro is a set of instruction which are stored in Visual Basic Module which will make excel to perform commands and actions for you by running them. Excel can repeat a task at any time by using a macro.
23. Goal seek: Excel's goal seek feature allows you to alter the data used in a formula in order to find out what the result will be.
24. Pivot table: Pivot tables are one of the excel's most powerful features, allows you to extract the significance from a large detailed data set.
25. Data table: A data table is way to see different results by altering an input cell in your formula.
26. Data marker: A data marker is a single bar, dot, pie, slice or other symbol in a chart or graph that represents a single value from all of the cells of data selected for display in the chart.
27. Sort Data: Sorting data is an integral part of data analysis. Sorting is making a list of names in alphabetical order, compiles a list of inventory or in short sorting is the way of arranging data sequentially.
28. Custom list: A custom list is a collection of some commonly used words which can be reproduced by dragging fill handle.
29. Outline: Outlining data makes your data easier to view. Outlining help in making large quantities of data minimizable so that complex spread sheet are easier to navigate.
30. What-if analysis: It is the process of calculating backward to find out an input by providing a specific output.
31. Array: Array is the number of items arranged in some specified way. systematic arrangement of objects, usually in row and column.
32. Embedded object: It is the term used to describe an object, software or hardware that is independent and does not need an external program or device to run it.
33. Formula: The set of rules used to perform calculations on values entered and stored in the cell. The excel formula is any equation entered into a cell on Microsoft Excel spreadsheet.
34. Formula palette: The formula palette displays the name and a description of the function and its arguments.
35. Function: A function is a preset formula in excel. Some of the common functions are SUM( ), AVERAGE( ), MAX( ), MIN( ) etc.
36. Scenario: The scenario is used to store several version of data in worksheet. For example, while preparing an annual budget, the marketing and finance developments may have different forecasts for sales.
37. External reference: An external reference in excel is a reference to a cell or range of cell in another workbook. In external reference you can refer to the context of cell to another workbook.
38. Chart: The graphical representation of information.
39. Axis: Axis is any horizontal, vertical and depth axis in the chart. A line
    boarding the chart plot area used a frame of reference for measurement.
40. Legend: In a chart or a graph in spreadsheet programs such as Excel, the legend is most often located on the right hand side of the chart or graph and can sometimes be surrounded by a border. The legend is linked to the data being graphically displayed in the plot area of the chart. The legend also known as chart's key.
41. Date format: It specifies a date format for the selected cell.
42. Gridlines: Gridlines are the faint lines that appear around cell in Excel.
43. Data marker: A data marker is a single bar, column, dot, pie slice, or other symbol in a chart or graphs that represents a single value from all of the cells of data selected for display in the chart.
44. Data series: Data series is the related data point that are plotted in a chart and
    originated from data sheet row or column. Each data series in a chart has a unique color or pattern.
45. Data label: A data label is a static part of a chart, report or other dynamic layout. The label defines the information in the line item. Levels are integral part of reporting and application development.

Operators used in MS Excel 

Operators are symbols used in formula to define the relationship between two or more cell references or between two or more values. They causes excel to perform some action.

1. Arithmetic Operators
   (+) Plus, (-) Minus, (*) Asterisk, (/) Slash, (%) Percentage, (^) Exponential
2. Comparison Operator/Relational Operator
   = (Equal to), > (Greater than), >= (Greater than equal to), <(Less than), <= (Less than equal to), <> (No equal to)
3. Text Operator
   Ampersand (&) the text operator is use to connect two values for producing one continuous values. E.g. “Babu” & “Ram” produce “Babu ram”
4. Reference Operators
   Colon (:) Colon is punctuation mark consisting of two equally sized dots centred on the same vertical line. A colon is used to explain or start an enumeration. 
   Comma (,) It is a sign of punctuation used for indicating a division in sentences, as in setting of a word, phrase or clause especially when such a division is accompanied by a slight pause.
   Space A set of elements or points satisfying specified geometric postulates.

Basic Unit of MS Excel

1. Title Bar: The title bar displays the application name, file name and various windows controls like minimize button, maximize button and close button.
2. Menu Bar: The menu bar consists of different options for selection. The menu consists of different menu and sub menus. It consists of Home Menu. Insert Menu, Paper Layout Menu, Formulas Menu, Data Menu, Review Menu, View Menu etc.
3. Taskbar: The taskbar is the long horizontal bar at the bottom of your screen. Unlike the desktop, which can get obscured by open windows, the taskbar is almost always visible. It has three main sections and other currently opened programs and many more. The three main sections are: Start Button: The button which opens the start menu, Middle Section: The middle section, which shows you programs  and files you open and allows you to quickly switch between them, The Notification Area: This area includes a clock and icons (  small pictures) that communicate the status of certain programs  and computer setting.
4. Name Box: The name box displays the address of the current cell.
5. Formula Bar: The formula bar displays the cell content. It depicts the choose cell contents on the screen in the form formula. It is not the formula but the result of the formula shown in the cell.
6. Current Cell: The active cell is the current cell.
7. Row Headers: There are 65536 rows (lines) numbered as 1, 2, 3, …. 65536. First row number is 1 and the last row number 65536. To go the cell in last row. press End and down arrow key, to return to cell in the first row. press End and Up arrow key.
8. Column Headers: There are 256 column numbered as A, B,... Z, AA …. AZ, BA... IV. First column name is A and last column name is IV. To go to the cell in last column header press End and right arrow key, to return to the cell in first column. Press End and left arrow key.
9. Scroll Bars: Used to scroll through different parts of current sheet.
10. Sheet Tabs: Displays the sheet names. Each worksheet is named as Sheet 1, Sheet 2 and Sheet 3.
11. Status Bar: Displays on the left side various modes like Ready or Edit mode, the status of Num Lock, Caps Lock Scroll Locks on the keyboard on right side, page break preview, page layout, normal etc.

[Note: 10 Marks out of 50 will be asked from this chapter only.]

Click here to see Compute Operator syllabus