Introduction To Ms-excel

Microsoft Excel by BALJEET SINGH/1

INTRODUCTION TO ELECTRONIC SPREADSHEETS A worksheet is basically an integrated application program that allows the user mathematical calculations, database management and graphical interpretation of data for interpretation of trends and patterns that are normally not apparent through numbers. • •

•

Mathematical Calculation: These calculations would involve arithmetic, statistical, scientific, trigonometric functions. Database Management: This would involve the creation, addition, and deletion, editing of a database that holds related information. You can also query on the database and set search criteria on specific fields of information desired. Graphs/Charts: This allows user to represent numerical data in the form of graphs, some spreadsheet programs allows for 2D and 3D graphs within the type of Bar, Line, Points, Pie etc.

Some are the common applications of worksheet are in:  Budgeting  Annual reports statements & Income  Income Calculations  Payroll  Invoices & Bills  Accounts Payable & Receivable  Production & Marketing Analysis  Investments and loans analysis  Banking  Inventory control  Tender evaluation  Scientific Calculations  cost effective analysis  Target/ Performance Analysis

Tax

Some of the standarly used spreadsheet packages in the world today are:  Lotus 1-2- 3  Quattro Pro  VisiCalc  V P Planner  Multiplan  Symphony  Microsoft Excel Starting Excel Click on Start button of taskbar and choose Microsoft Excel from the item Program; or click on Excel’s button from Microsoft Office Taskbar.

Microsoft Excel by BALJEET SINGH/2 Title Bar • A horizontal Bar at the top of a window, dialog box, or toolbar that shows the name of the document, application or toolbar. • Like the Title Bar in other Windows applications, the Excel Title bar consists of the control menu button (at the extreme left corner), the application name and the name of the active workbook (if the worksheet area window has been maximized), the minimize, maximize/restore button. Worksheet Name • The default workbook opens with 3 worksheets, named Sheet1 through Sheet3. The sheet names appear on tabs at the bottom of the workbook window. By clicking on the tabs you can move from sheet to sheet within a workbook. The tab of the active sheet is always bold. • A workbook can contain one sheet or as many as 255 sheets. Menu Bar • A list of commands that drops down from the menu bar. Excel displays the menu bar across top of an application window and lists the menu names, such as File and Edit. • Under the Title Bar is the Menu Bar, consisting of a list of menu options each having a pull down menu list of commands. Tool Bar • A bar with buttons that performs some of the most common tasks in Excel. Toolbars contain buttons that give you quick mouse access to many commands and features in Excel. For example, you can open a file by clicking the new button on the Standard toolbar. • Excel has toolbars for different purposes. When you first start Excel, the Standard toolbar and Formatting toolbar appear. • You can display or hide many of the built in toolbars by pointing to any toolbar and clicking the right mouse button. When you are working in an Excel worksheet, you can also display or hide most toolbars by choosing toolbars from the View menu. • You can see the name of each toolbar button in a ToolTip. When you point to a button with the mouse the button name will appear in a box. You can turn ToolTips on and off in the View Toolbars dialog box. FORMULA BAR - A bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts. Displays the constant value or formula used in the active cell. COLUMN NAME - Columns are the vertical blocks demarcated by solid lines and reference by a letter of alphabet A, B... IV. In Excel there are a total of 256 columns. The column names appear on the column bar above the worksheet area. The default width is 8.43 points. Row No. - Rows are the horizontal strips demarcated by solid lines and reference by a number - 1,2,3,65536. The row numbers appear on row bar along the left side of the work sheet area. Cell - The intersection of column row forms a cell, which is referenced/addressed by a combination of column letter and row number.

Microsoft Excel by BALJEET SINGH/3

Fill Handle – The active cell has a small box at the right bottom corner called the Fill handle. Meant for filling a range of adjacent cells. You can use AutoFill to fill a range of adjacent cells. By dragging the Fill handle on the selected cell or range you can. Keys for working in menus ALT or F10 Shift + F10 Esc ALT + BACKSPACE or CTRL + Z F4

Activates the menu bar. Activates the shortcut menu Cancels the shortcut menu. Undoes the last command Repeats the last command if applicable

When menu bar is active Esc Cancels the menu Spacebar Displays the Microsoft Excel Control Menu Hyphen Displays the workbook control menu Letter key Selects the menu that contains the underlined letter. Left or Right Selects the menu to the left or right. Arrow Down or Up Selects the next or previous command on the menu. arrow With menu displayed Letter key Chooses the menu command that contains the underlined letter. Enter Choose the selected command. Down or Up Selects the next or previous command on the menu. arrow Left or Right With a submenu displayed, toggles selection between arrow main menu and submenu. Menu Bar Options File This menu has a series of options that are concerned with file operations like, opening, saving, printing, searching page setup, and of course quitting Excel. Edit This menu consists of standard editing functions, like - undoing or repeating the recent operation, cut, copy, paste, find & replace, erasing, filling and Goto. View This menu is used for changing the way Excel is displaying it’s various components on screen, you may decide whether to display or not to display the formula bar, status bar, toolbar, full screen or may magnify display by zooming. Insert This menu allows one to insert cell, row, column, entire/part of a worksheet, chart, macro, a manual page break, function, range names, notes, picture from clipart and objects from different applications. Format This menu allows you to change the appearance of the selections. Tools This menu provides auditing and what if analysis tools, records and runs macros, changes general options. Data This menu helps to perform data access facilities, also uses list

Microsoft Excel by BALJEET SINGH/4

Window Help

management and pivot table. This menu is used to rearrange windows or activate specific windows. This menu guides you in accessing information for learning and using MS Excel.

Standard Toolbar A bar with buttons that performs some of the most common tasks in Excel, such as opening, copying, & printing files. New

Creates a new workbook.

Open

Displays the Open dialog box so that you can open an existing workbook.

Save

Saves changes made to the active workbook.

Print

Prints the active workbook according to the options you previously specified in the Print dialog box.

Print Preview

Displays each page as it will look when printed. The status bar at the bottom of the screen shows the current page number and the total number of pages in the selected sheet.

Spelling Cut

Checks the spelling of text in worksheets and charts, including text in text boxes, buttons, headers and footers, cell notes and the formula bar. Removes the selection and places it onto the clipboard.

Copy

Copies the selection and places it onto the clipboard.

Paste

Pastes the contents of the Clipboard into the selection

Format Painter

Copies only the formats from the selected cells or objects.

Undo

Reverse the last command you choose, if possible, or deletes the last entry you typed.

Repeat

Repeats the last command you choose if possible, including any dialog box option settings.

AutoSum

Automatically invokes the SUM function and suggests the range of cells to be added.

Microsoft Excel by BALJEET SINGH/5 Function Wizard

Opens the function wizard dialog box and inserts the selected function into the formula bar or active cell.

Sort Ascendin g Sort Descendi ng

Sorts the current list in order from lowest value too highest value, using the column that contains the active cell. Sorts the current list in order from the highest value to lowest value, using the column that contains the active cell.

Chart Wizard

Starts the chart wizard, which guides you through the steps required to create a new chart.

Text Box

Draws a text box in which you can type text on a worksheet.

Drawing

Displays the drawing toolbar.

Zoom

Allows you to see more or less details by changing the scale of the sheet.

Tip Wizard

Opens the tip wizard, who displays tips based on the actions you perform.

Help

Adds a question mark (?) to the mouse pointer. When you place the pointer over a command name or screen element and click the mouse button, you get information about the command or screen element.

Formatting toolbar Contains buttons that help you format objects, cells, and cell contents. You can also format chart objects, such as text and gridlines. Font Lists the available fonts Font Size

Lists available sizes for the font selected in the font box

Bold

Applies bold formatting to selected characters.

Italic

Applies italic formatting to selected characters.

Underline

Applies a single underline to selected characters.

Align Left

Aligns the contents of the selected cells or text boxes to the left.

Align Center

Centers the contents of the selected cells or text boxes to the right.

Microsoft Excel by BALJEET SINGH/6

Align Right

Aligns the contents of the selected cells or text boxes to the right.

Center Across Columns Currency Style

Centers the text from one cell horizontally across the selected cells.

Percent Style

Applies the currently defined percent style to selected cells.

Comma Style

Applies the currently defined comma style to selected cells.

Increase Decimal

Adds one decimal place to the number format each time you click the button.

Decrease Decimal

Removes one decimal place from the number format each time you click the button.

Borders

Displays a palette of border styles you can use to apply borders to selected cells.

Color

Changes the color of a selected cell or object.

Font Color

Displays a palette of colors you can use to change the font color of selected characters in cells, text boxes etc.

Applies the currently defined Currency style to selected cells.

Commands of Excel (Except taught in Word) – Print Preview – This command shows the screen on the base of WYSIWYG. After taking hard copy you will find your paper same as showed in this view. In this view we will get some new button as – Next – Shows next page if available. Previous – Shows previous pages if we are on second or later pages. Zoom – Toggles between original size and full screen. Print – Used to set the print options, i.e. number of copy, page range etc. Setup – Used to set the page setup, margins, header & footer and Sheet Options. In Sheet option we can set about the gridlines printing, print order i.e. either in down than over or over than down. Margins - Click margins to display or hide margin handles that you can drag to adjust page margins, header and footer margins, and column widths. Page Break Preview/Normal View - Click Page Break Preview to switch to page break preview, in which you can adjust the page, breaks on the active worksheet. You can also resize the print area and edit the worksheet. Click Normal View to view the active sheet in normal view. The

Microsoft Excel by BALJEET SINGH/7 name of the button changes to Normal View if you were in page break preview when you clicked Print Preview. Close - Click Close to close the print previews window and return to the previous view of the active sheet. Q. Ans.

What are functions? Name few of the Excel functions. In Microsoft Excel, functions are calculation tools that we can use to perform decision-making, action-taking and value-returning operations automatically. Microsoft Excel provides a wide variety of functions that perform many different types of calculations. We use worksheet functions to calculate and return values based on information we provide. Worksheet functions take a value or values, perform some operation on them, and return a value or values. Functions on a worksheet are calculated as soon as we enter them and faster than formula created by us. Sum – This button is used to add the series of numbers either in rows or in columns. 2. Average (Number range/reference) – these option returns the average (arithmetic mean) of its arguments, which can be numbers or names, arrays or references that contain numbers. 3. Max (Reference Range) - Returns the largest value in a set of values, ignores text and logical values. 4. Min (Reference Range) - Returns the smallest value in a set of values, ignores text and logical values. 5. Left (Text, Num. Characters) – Returns the first (or leftmost) characters in a text string. 6. Right (Text, Num. Characters) – Returns the last (or rightmost) characters in a text string. 7. Len (Text) – Returns the length of text within a text string. 8. Mid (text, start_num, num_chars) – Returns the specific number of characters from a text starting at the position you specify. 9. Lower(text/cell address) – Converts the text in lower case character. 10. Upper (text/cell address) – Converts the text in upper case character. 11. Proper (text/cell address) – Converts the text in Proper case character, i.e. first character of each word will convert in uppercase and rest in lowercase. 12. Value (Text) – Converts a text string that represents a number to a number. 13. Abs(Value/Cell reference) – This function returns the absolute value of indicated cell or value, i.e. distance from 0 to that numbers ignoring either it is negative or positive. 14. Int(Value/Cell reference) – This function returns the whole number from any decimal number. 15. If (Logical Text, Value if true, Value if false) - Returns one value if a conditions you specify evaluates to TRUE and another value if it evaluates to FALSE. 16. Sumif(Range, Criteria, Sum range) - Adds the cells specified by a given condition or criteria. 17. Countif(Range, Criteria) - Counts the number of cells within the range that meet a given condition. 18. Hlookup & Vlookup 1.

Microsoft Excel by BALJEET SINGH/8

Hlookup(Lookup_value,Table_array, Row_index_num,Range_lookup) – This function is used to looks for a value in the top row of a table of values and returns the value in the same column from a row you specify, i.e. looks a value horizontaly. Lookup_value is the value to be found in the first row of the table and can be a value, a reference or a text string. Table_array is a table of text, numbers or logical values in which data is looked up. Table_array can be a reference to a range or a range name. Row_index_num is the row number in table_array from which the matching value should be returned. The first row of values in the table is row 1. Range_lookup is a logical value: to find the closest match in the top row (sorted in ascending order) = TRUE or omitted; find an exact match = FALSE. Example : =HLOOKUP("Year",A1:E10,7,FALSE) will return – 1998

Region East North North North West West East South South

Year Sales Person 1997 Amit 1997 Abhishek 1997 Rinki 1998 Amit 1998 Gaurav 1998 Suresh 1999 Gaurav 1999 Suresh 1999 Rinki

Product T.V. Cooler Computer Computer Computer Computer Cooler T.V. T.V.

Sale Amount 16000 28000 52000 45000 120000 78000 36000 32000 36000

Microsoft Excel by BALJEET SINGH/9

File -> Save Workspace – Saves a list of the open workbooks, their sizes, and their positions on the screen to a workspace file so that the screen will look the same the next time you open the workspace file. Saving a File with Password - Password is a secret word or expression known only to authorized users of a file. A password prevents access by authorized access by unauthorized users to the protected item. To do so, Choose Save as option from file menu. Now from the Save as dialog box choose Options button. You will get second Dialog box for setting save options. At the bottom of that you get Prompt two types of password – one for opening the file and another for modifying the file. Using multiple files – If there is more than one file is open then, we can arrange them at a time on screen by selecting Arrange option from Window menu pad. This option provides us four options to arrange them, i.e.- Tiled, Horizontal, Vertical and Cascade. Formatting Cells – This option is used to manually for the cell according to our choice. Here we get number of Tabs to for the cell/cells. They are – 1. Number – This tab is used to set the number style. We can set a number in as date, currency, and percentage etc. format. 2. Alignment – This option says the Excel how any content will be set in particular cells. We can also turn the text in any angle within the cell. 3. Font – This option is used to change the font style, i.e. name of the font, style, size etc. 4. Border – This option is used to set the inside or outside border of cell/cells. Edit -> Clear – This option is used to clear the cell or cells providing several options. All option is used to clear all things, Formats is used to clear only format, Contents delete the content of the cell but the format remain same and Comments options is used to delete only comments of the selected cell. Comments for any cell may be insert through the Insert->Comments command. Transposing Your Data – You can quickly switch data that is in rows to data in columns by using Excel’s transposing feature.

Microsoft Excel by BALJEET SINGH/10 For this select the range and copy it. Then select the cell where you want to paste it. Choose Edit->Paste Special and cross the Transpose checkbox. Hiding/Unhiding Rows, Columns Worksheet and Workbooks – For this select the required rows or columns to which you want to hide. Then select Format->Column/Row->Hide. To show it again select visible range of the rows or columns that includes the hidden column/row. To hide a worksheet click on the corner where Row 1 and Column A touches each other. The entire sheet will be selected. Now choose Hide option from either Format->Column or Format->Row. All things will be hides. To unhide it, select again that point and choose unhide option in the same manner. To hide whole workbook chooses Window->Hide option. Now the whole workbook will disappear. To unhide it selects Window->Unhide option. You will get prompt to unhide which workbook. Select required workbook and click on OK. Inserting Columns and Rows – To insert a column or row, select the column or row header next to where the new area will appear. New columns are inserted to the right of your selection and new rows underneath. Then select Insert>Columns or Rows from the menu bar. What is Database? Database management system (DBMS) is software used to organize, analyze, search for, update and retrieve data. The structure of a database can be described in the following way : • Database range : A database range refers to the rectangular range of worksheet cells defined as a database and the first row of the database range contains the names of the fields. • Record : A record is a single row in a database, which contains the same categories of data as every other record. • Field : Each column in the database range contains an individual piece of information, which together with all other field forms the details of a single record. Each cell in a column is a field. The individual fields in a record are arranged in the same sequence/order. • Field Name : A field name is the title with which each individual piece of information is referenced. The top row of a database must contain the field names, i.e. each column of information must bear a title. • Computed Field : A computed field is a field that is a result of the formula applied on the values of other field constants within the database, with or without other functions. Generating a Data Form to Add records easily : A data form provides a simple way to see, change, add and delete records from a database, or to find specific records based on criteria you specify. When you choose the Form command from the Data Menu, Excel

Microsoft Excel by BALJEET SINGH/11 reads your data and creates a data form dialog box. When you enter or edit data in these fields, Excel changes the corresponding cells in your list. New – Used to add more records. Delete – Delete the current record. Find Prev – Shows previous record Find Next – Shows next record. Criteria – Used to set specific criteria to see only the records having same criteria. Clear – To clear any criteria use the same command. Restore – Used to restore previous criteria before clear command. Sorting and sifting through Data : Choose Data – Sort command to arrange your data in any order on base of and field. We can also arrange our data without alphabetical or numerical sorting, i.e. according to weekdays or months name. To does so choose options buttons from Sort dialog box and choose required First Key sort order. To create your own Custom List, Choose Tools-Options and click on the Custom Lists tab. Here you can make your own list and then click on Add button. To clear any list selects it and then click on delete button. Filtering the Data : You can use the Filter command on the Data menu to find and work with a subset of the data in your database. A filtered database displays only the rows that contains a certain value or rows that meet a set of criteria, while temporarily hiding the other rows. AutoFilter – Enables you to display only those rows in a list that contain a certain value, or that meet a set of criteria. The AutoFilter command applies drop-down arrows directly to column labels in the list, so you can select the item you want to display. When you select an item from the drop-down list, Excel temporarily hides the rows that do not contain that item. We can also choose ‘Custom’ Option, so that we can apply two conditions with logical AND/OR with the same column. We can also use ‘Top 10’ option to select some Show All – You can show all the data in your list by using the Show All command, or remove the AutoFilter drop-down arrows by clearing the AutoFilter command. Data -> Subtotal: The Subtotal feature allows you to mathematically summarize data by a particular field. Excel displays a subtotal on that field that contains numeric data (for example, Sales) When using the Subtotal feature, make sure to sort on the column field that will be used in the subtotal. Excel must have a contiguous group of the same items to give you an accurate subtotal. At any time you can remove the subtotals and returns the data list to its simple column and row format. When you have finished viewing or printing the subtotaled data, click on any cell

Microsoft Excel by BALJEET SINGH/12 containing data; otherwise Excel will not be able to locate the data list. Then choose Data-> Subtotals, and click on the Remove All button on the right side of the Subtotal dialog box. Your list is returned to its original format. When we right click on sheet icon at the bottom we get following options :Insert - This option is used to insert a blank sheet before the current sheet. Delete - This option is used to delete the current sheet with all its content. Rename - We can change the name of any sheet through this option. Move or Copy - This option is used to move or copy the current sheet at the specified place. Select All Sheets - Selects all sheets of current workbook. Ungroup all - When all sheets are selected, this option deselects them. View Code - Displays Visual Basic Editor, where you can write visual Basic commands. SPREADSHEET : DESCRIPTIVE QUESTIONS Q. Describe in brief about a Data range. How is it selected? Ans. A worksheet contains 256 columns and 65536 rows. In this large area sometimes a certain part of the sheet become important if a user wants to select it for copying, moving, deleting or applying any other effect on it. This part, i.e. a data range, refers to the rectangular range of worksheet and the first row of the database range contains the names of the fields. In this range, one cell from where selection starts is known as Active Cell. To select a data range there are so many ways :I. We should click the mouse at that cell from where selection will be start, drag it in the direction of selection and after completing desired selection release the mouse button. II. Second method is click on first cell and move to the last cell through scroll bar or any other method but remember that active cell should not change. Then press shift and click on last cell. All the rectangular area will be selected. III. To select non-adjacent data range, select the first range through any of the above two method, press CTRL Key and select the next range and so on. Q. Ans.

In an Excel worksheet how can you move to a new location without having to move the active cell? Without moving the active cell, to move at new location we have to use Scroll Bar. There are two types of Scroll bar - Horizontal and Vertical. Each toolbar has two arrow in both of its side and a button within it, which is proportional of the whole data, entered part of worksheet and part shown on the screen. To move slowly we can click on desired side arrow of the scroll bar and to move fast click and drag the scroll button in the desired direction.

Q. Give a brief description of available toolbars in Excel. Ans. There are following toolbars in Excel :♦ Standard - This toolbar contains lot of icons related to general Excel, such as New, Open, Save, Print, AutoSum, Chart etc. ♦ Formatting - This toolbar contains the tools or icons which can apply any other effects, such as font name, size of the character, Merge cell etc., on the created list. ♦ Chart - This toolbar gives us facility to create a chart, choose gridlines etc.

function of be used to Bold, Italic, chart type,

Microsoft Excel by BALJEET SINGH/13 ♦

♦ ♦ ♦

♦

♦

♦ ♦ ♦

♦

Control Toolbox - Sometimes a new user done lot of mistake in data entry, mainly in logic fields. So, to make data entry error free and fast we use check boxes, Text box, option button, spin button etc., so that a user have to select only an option and entry being completed. Drawing - This toolbar contains those icons through which we can draw circle, line, text box etc. in our spreadsheet. External Data - This toolbar is used when we insert data from any other database or spreadsheet program to query, refresh etc. Forms - Sometimes user want to use check box, option box, command button etc. linked to any fields to save data entry time and error free entry. That time this tool helps us to insert exact form of option buttons in our worksheet. Picture - This tool is used to insert or format the picture of any other application. We can also adjust contrast, brightness, transparencies etc. through this toolbar. Pivot Table - Pivot table is a cross board checking system of our entered data. Through this we can see our data in many ways. This toolbar helps us to create pivot table, refresh it if some data changed etc. Reviewing - This toolbar contains tools which is used to show comment and delete it. Visual Basic - This toolbar contains tools to create macros and edit it through Visual Basic editor. Web - Web toolbar contains all those toolbar which is found in standard toolbar of Microsoft Internet Explorer. When we work with Internet then this toolbar helps us to send of receive many information from World Wide Web. WordArt - WordArt is a special feature used to insert artistic words in our worksheet. To insert these word and format it through many ways we can use this toolbar.

Q.

What are the steps that you will engage to edit data in a worksheet? Ans. To edit data in a worksheet we can various methods. First of all we have to reach at desired cell at which correction take place. For this we can use scrolling bar or F5 key to reach at appropriate place. Then either press F2 key or double click on the cell. The edit mode becomes active. Now through the cursor key or mouse go to exact place and add or remove data. After completing editing press Enter key or click the mouse any other place. Sometimes corrections may also be done directly at formula bar, especially in case of a formula editing. Content of active cell appears in the formula bar. Click on the place in formula bar where correction take place and correct the data. Q.

Describe the sequence of steps that you will use to copy data from one location to another in Excel. What is a clipboard? Ans. Clipboard is actually a temporarily storage area of our Hard Disk. Temporarily means this area store one piece of information, only last cutted or copied data on to it as a temporary file. If we quit from window or restart it, then this data become lost. Clipboard makes it possible to share information among documents in the same package as well as among documents in different applications. The clipboard can be made to hold text, graphics, numbers or data. This information may be paste in same documents at another location, in other documents as well as in other application. To copy data from one location to another in Excel, we have to select the data range first which should be copied. Then choose copy command either from Edit menu; click on copy tool at standard toolbar or press CTRL+C.

Microsoft Excel by BALJEET SINGH/14 The selected data will copy on the clipboard. Now we will go to the place where we want duplicate copy of our data and choose paste option from Edit menu, click on paste button of standard toolbar or press CTRL+V. Q. Describe in brief about Macros. Ans. If you perform a task repeatedly in Microsoft Excel, you can automate the task with a macro. A macro is a series of commands and functions that are stored in a Visual Basic module and can be run whenever you need to perform the task. You record a macro just as you record music with a tape recorder. You then run the macro to repeat, or "play back," the commands.

Before you record or write a macro, plan the steps and commands you want the macro to perform. If you make a mistake when you record the macro, corrections you make will also be recorded. Each time you record a macro, the macro is stored in a new module attached to a workbook. With the Visual Basic Editor, you can edit macros, copy macros from one module to another, copy macros between different workbooks, rename the modules that store the macros, or rename the macros. Q. Ans.

List the steps to create Macro buttons. To create a new macro follow the following steps :-

♦ First of all plan that what you want to do with macro. ♦ Go to Tools -> Macro -> Record New Macro ♦ A menu appears in which first of all give the macro name. Then in the Shortcut key section give any keyboard shortcut to use the macro. You can also guide to use this macro in current workbook or in all and click on OK. ♦ A small menu appears with two buttons only in whom one is stop and another is Pause. Now start your work, i.e. that repetitive work that has to be record. After completing your task click on Stop button and the macro will be stored. ♦ To use this macro press the given key combination and all the functions will be automatic execute. Q. Describe usage of AutoSum in Excel. Ans. Autosum in Excel is a function, which is used mostly by user. There are so many work in which a user wants to add a range of values. It may be total of marks, total of stock etc. In all these case it is lengthy to type a formula. So, Excel provides a function and also a unique tool at standard toolbar to add a series of data. User can click on AutoSum button if he is just below the last cell of series. A user can also use "ALT + =" key in place of AutoSum tool. Q.

Describe briefly about the formulas in Excel. How are they created?

Microsoft Excel by BALJEET SINGH/15 Ans. A formula is an equation that analyzes data on a worksheet. Formulas perform operations such as addition, multiplication, and comparison on worksheet values; they can also combine values. Formulas can refer to other cells on the same worksheet, cells on other sheets in the same workbook, or cells on sheets in other workbooks. In Excel there are 256 columns and 65536 rows. These rows and columns created 1,67,77,216 cells. Each cell has its own address, which is combination of Column name followed by Row number. Through the help of this cell address Excel is widely known for its formulas and functions. In Excel we can create lot of formula with or without any function. This formula is just similar the formula used in mathematics and follows all the rules of mathematics. One important thing is the formula should be started with "=" as first character in any cell. After "=" we have to type the other parameter of formula in which if someone wants to calculate through constant value then it may given directly, but if someone wants to use variable then the Cell Address may be used in place of variable. To give a range of cell as variable in formula use colon (i.e. A1:A10 for indicating data from cell A1 to A10). Following can be used in a formula :For Addtion + Deletion Multiplication * Division / Exponent ^ Bracket ( ) only For example to add content of cell A1 to cell A10 we have to give =A1+A2+A3+A4+A5+A6+A7+A8+A9+A10 or =SUM(A1:A10) For Substraction we can give =A20-A18 or =200-150 Q. Explain the difference between Relative and Absolute references. Ans. When you create a formula, references to cells or ranges are usually based upon their position relative to the cell that contains the formula. In the following example, cell B6 contains the formula =A5; Microsoft Excel finds the value one cell above and one cell to the left of B6. This is known as a relative referencing. 5 6 7

A 100 200

B =A5 =A6

When you copy a formula that uses relative references, the references in the pasted formula update and refer to different cells relative to the position of the formula. In the following example, the formula in cell B6 has been copied to cell B7. The formula in cell B7 has changed to = A6, which refers to the cell that is one cell above and to the left of cell B7. If you don't want references to change when you copy a formula to a different cell, use an absolute reference. For example, if your formula multiples cell A5 with cell C1 (=A5*C1) and you copy the formula to another cell, both references will change. You can create an absolute reference to cell C1 by placing a dollar sign ($) before the parts of the reference that do not change. To create an absolute reference to cell C1, for example, add dollar signs to the formula as follows:

Microsoft Excel by BALJEET SINGH/16 =A5*$C$1 Q. What is a list and what are its components? Ans. A series of worksheet rows that contain related data, such as an invoice database or a set of client names and phone numbers. A list can be used as a database, in which rows are records and columns are fields. The first row of the list has labels for the columns. Following are its components :• Database range : A database range refers to the rectangular range of worksheet cells defined as a database and the first row of the database range contains the names of the fields. • Record : A record is a single row in a database, which contains the same categories of data as every other record. • Field : Each column in the database range contains an individual piece of information, which together with all other field forms the details of a single record. Each cell in a column is a field. The individual fields in a record are arranged in the same sequence/order. • Field Name : A field name is the title with which each individual piece of information is referenced. The top row of a database must contain the field names, i.e. each column of information must bear a title. • Computed Field : A computed field is a field that is a result of the formula applied on the values of other field constants within the database, with or without other functions. Q. What is Autoformat command? How do you apply Auto Format to a worksheet? Also given a brief description of ready-made templates available in Excel. Ans. Autoformat is a built-in collection of formats - such as font size, patterns and alignment - that you can quickly apply to a range of data. Microsoft Excel determines the levels of summary and detail in the selected range and applies the formats accordingly. Apply an autoformat to a range 1 Select the range you want to format. 2 On the Format menu, click AutoFormat. 3 In the Table format box, click the format you want. To use only selected parts of the autoformat, click Options, and then clear the check boxes for the formats you don't want to apply. Microsoft Excel includes templates that automate the common tasks of filling in invoices, expense statements, and purchase orders. Each time you fill in one of these forms online, you can choose to add the set of entered information as a new record in a database associated with the form template. To use the templates, click New on the File menu, and then doubleclick a template on the Spreadsheet Solutions tab. If the template you want is not listed in the new dialog box, you can install it now. If you have access to the World Wide Web, you can obtain additional Microsoft Excel templates designed for tasks such as planning personal finances. You can use these templates with Microsoft Excel versions 5.0 and later.