MS Excel

Ms-Excel

If the job at hand requires lot of lists, tables, financial calculations, analysis and graphs, excel is just the package for you.

   Excel is great at organizing all types of data, but it is the numerical data where excel is in its home turf. Since most of the times you not need a tool for storing and managing data but also analyzing and querying data, excel’s powerful features help you do all this and more. In case you have used Ms word you would notice the striking similarity between the two packages. The menus, toolbars and icons are very similar to each other. This is keeping in line with Microsoft’s much hyped philosophy and strategy of totally office suite pack. From user point of view, this means less time spent in learning the second package, once you know the first and almost effortless and seamless exchange of data between various components. However, if you have not used Ms word before no need to worry, this book starts from zero level and does

not presume reader’s prior knowledge of any other package.

     Excel for that matter any spreadsheet, essentially comprises of a grid of rows and columns. Intersection of a row and a column is called a cell. Typically, row are numbered numerically 1, 2, 3, 4…. And so on and columns are labeled a alphabetically A, B, C…..and so. Of course columns do not end at z, after z they start AA, AB, AC,…. And BA, BB, BC… and so on.

          Microsoft excel is also a family member of ms office group. They can be used to present a wide variety of information such as budgets, petty cash records, employees wages, etc. once data and formulae have been entered, the program will perform the necessary calculations. Accounting, statistics finance, management fields are the main fields for this program.

       Microsoft excel is an electronic spreadsheet program that enables you to store manipulate and chart numeric data spreadsheet is an electronic sheet that allows you to enter and store data in a ‘grid’ format on a computer system.

        Excel enables you to create and modify worksheets and chart sheets. A worksheet is an electronic version of an accountant’s ledger pad, which is divided into vertical columns and horizontal rows. The rows are numbered from. 1 to 65536 (1,2,3……65536) and the columns are labeled from A to IV (A, B, C…..Z, AA, AB, AC…..AZ……..IV). the intersection of columns are row is called cell. Each cell is given a unique cell consisting of its column letter followed by its number, e.g. a1, a2, a3.

Menu bar: it contains the excel menu commands. To execute a command, you click once on the desired menu bar option and then click again on the command. Commands that appear dimmed are not available for selection.

Select all buttons: the gray rectangle in the upper-left corner of a worksheet the row and column headings meet. It is used to select the contents of entire sheet.

Name box: the box at the left end of the formula bar that identifies the selected cell, chart item, or drawing object. Type the name in the name box and then press enter to quickly name a selected cell or range. To move to and select a previously named cell, click its name in the name box.

Formula bar: a bar near the top of the window that displays the constant value or formula used in the active cell. To enter or edit values or formulas, select a cell, type the data, and then press enter. You can also double click a cell to edit data directly In the cell.

Tab scrolling buttons: they are the arrow buttons to the left of the sheet tabs. To scroll between sheet tabs if the workbook has more tabs than are currently displayed click an arrow for the direction you want to scroll to select a sheet click its tab. To scroll several tabs at a time, press shift while you click one of the middle tab scrolling buttons. To display a menu of sheets in the workbook, click a tab scrolling button with the right mouse button.

Split box: it is the small box at the top of the vertical scroll bar and at the right end of the horizontal scroll bar. To view two parts of a worksheet simultaneously drag the split box in the direction you want the split to appear. To split the window along row or column gridlines, drag the split box into the worksheet to remove a split, double click the split.

Tab split box: it is the small box between the sheet tabs and the horizontal scroll bar. Drag the tab split box to the right or left to display additional sheet tabs or increase the size of the horizontal scroll bar. To return the default display of sheet tabs, double click the box.

Workbook: a workbook is the file in which you work and store your data. Because each workbook can contain many sheets, you can organize various kinds of related information in a single file. Workbook is the collection of worksheet. By default, there are 3 worksheet in a workbook.

Worksheet: the primary document that you use in Microsoft excel to store and work with data is called a worksheet. A worksheet is always stored in a workbook. You can use worksheets to list and analyze data. You can enter and edit data in several worksheets simultaneously and perform calculations based on data from multiple worksheets. When you create a chart, you can place the chats on the worksheet with its related data or a separate chart sheet.

             Worksheet is the combination of rows and columns. In one worksheet, there are 65536 rows and 256 columns.

Row: row is the horizontal line of the cells in a worksheet. There are 65536 rows in one sheet denoted by numbers from 1 to 65536.

Column: is the vertical line of the cells in worksheet. There are 256 columns in one sheet denoted by alphabets A to IV.

Cell: cell is the small rectangular block in a worksheet. it is the meet point of row and column.

Questions,

1. bonus will give 20% of salary per month.
2. tax will pay 10% of (basic salary and bonus) per month.

Solution,

Bonus=C2*20%

Tax=(C3+D3)*10%

Questions,

1. bonus will give 15% of salary, if his/her salary is less and equal to 3500.
2. tax will pay 10% of salary and bonus, if he/she is from ktm.
3. ha will get 5% of salary, if he/she is not from ktm.

Bonus= if(e2<=3500, e2*15%,0)

Tax=   if(C2=”ktm”,(E2+F2)*10%,0)

Ha   =  if(C2<>”ktm”,E2*5%,0)

Total=salary + bonus – tax

Question,

1. bonus:
1. bonus will give 15% of salary, if his salary is less and equal to 3500.
2. Bonus will give 12% of salary, if his salary is greater then 3500 and less then and equal 6500.
3. Bonus will give 10% salary if his salary is greater than 6500.
2. tax will pay 10% of salary and bonus if he is not ktm.
3. ha:
1. ha will give 15% of salary is he/she is from pokhara.
2. Ha will give 10% of salary if he/she  is from ktm.
3. Ha will give 5% of salary if he/she is from outside of pokhara and KTM.

Solution,

Bonus   =if(E2<=3500,E2*15%,if(and(E2>3500,E2<=6500),E2*12%,E2*10%))

Tax       =if(C2<>”ktm”,(E2+F2)*10%,0)

Ha         =if(C2 =”pokhara”,E2*15%,if(C2=”ktm”,E2*10%,E25%))

Result =if(and(C2>=32,D2>=32,E2>=32,F2>=32,G2>=32,H2>=32,I2>=32),”pass”, ”fail”)

Division  =if(L2>=6o,”first”,if(and(L2>=45,”second”,if(and(L2<45>=32),”third”, “no division”)))

Remark   =if(M2=”first”, ”excellent”, if(M2=”second”,” well”, if(M2=”third”, ”good”, “try again”)))

File menu:

New: creates a new blank file based on default values contained in a file called excel.txt.

Open: opens or finds as exiting file.

Close: closes the active file without exiting the application. If the file contains any unsaved changes, you will be prompted to save the file before closing.

Save: saves the active file with its current file name, location, and file formant. In case a new file is saved for the first time the user has to specify the file mane, location and fie format.

Save as: saves the active file with a different file name, location or file format.

Save as web page: saves the file in Web page format required for creating documents for the internet (www).

Versions: saves and manages multiple versions of a document in a single file. After you save versions of a document, you can go back and review, open, print and delete earlier versions.

Page setup: sets margins, paper source, paper size, page orientation, and other layout options for the active file.

Print preview: shows a preview of how exactly a file will look when you print it.

Print: prints the active file or selected items, to the selected printer or fax.

Send to: sends the document to a mail recipient as an e-mail.

Properties: displays the property sheet for the active file.

Exit: closes MS Excel after prompting you to save any unsaved files.

Edit Menu:

Undo: reverses the last command i.e. undoes the effect of the last command you executed.

Repeat: Reverses the undo command i.e. restores the last command.

Cut: removes the selection from the active document and places it on clipboard a special place in computer’s memory from where it can be retrieved again.

Copy: copies the selection to the clipboard.

Paste: inserts the contents of the clipboard at the insertion point, and replaces any current selection.

Paste Special: Paste, links, or embeds the clipboard content in the current file in the format you specify.

Select all: selects all text and graphics in the active windows or the selects all text in the selected object.

Clear: deletes the selected object or text without putting on the clipboard.

Find: Searches for specified text, formatting, symbols, comments, footnotes, endnotes in the active document.

Replace: Searches for and replaces specified text, formatting footnotes, endnotes or comment mark in the active documents.

Go To: Moves the insertion pointer to the item where you want to go.

Links: displays or changes information for each link in the current file, including the name and location of the source file, the item, the type and whether the link is updated automatically or manually.

Object: Activates the application in which the selected object was created so you can edit it.

View Menu:

Normal: Switches to normal view, which is the default document view for most word processing task.

Page Break Preview: Switches to normal view, which is the default view for most tasks in Ms excel, such as entering data, filtering, charting and formatting.

Toolbars: toolbars allow you to organize the command in word, the way you want to so that you can find and use them quickly. Toolbars can contain buttons, menus, or a combinations of both. The menu bar is a special toolbars at the top of the screen that contains menus such as file, edit and view etc. a menu displays a list of commands.

Formula Bar: Display or hides the formula bar.

Status bar: display and hides the status bar.

Header and footer: adds or changes the text that appears at the top and buttom of every page or side.

Comments: displays all comments made by all reviewers in the comment pane.

Custom views: creates different views of a worksheet. A view provides an easy way to see your data with different display options. You can display print and store different views without saving them as separate sheete.

Full screen: hides most screen elements so that you can view more of your’s document.

Zoom: Enter a magnification between 10 & 20 percent to reduce or enlarge the display of the active document.

Insert Menu:

Cells:  Inserts the number of cell you select.

Rows: inserts the number of rows you select.

Columns: Inserts the number of columns you select.

Worksheet: Inserts a new worksheet to the left of the selected sheet.

Chart: Starts the chart wizard, which guides you through the steps for creating an embedded chart on a worksheet or modifying an exiting.

 Page Break: Insert a column, section or page break.

Function: Displays a list of functions and their formats and allows you to set values for arguments.

Name: defines, creates, pastes and applies names.

Symbol: Insert symbols and special character from the fonts that are installed on your computer.

Comment: Inserts a comment at the insertion point.

Picture: Inserts pictures from clip art and other libraries.

Object: Inserts an object such as a drawing, word art text affect, etc. at the insertion point.

Hyperlinks: Insert a hyperlink through which you can jump to a location in the current document or web page, or to a different word document or web page, or  to a file that was created in a different program.

Format Menu:

Cells: Applies formats to the selected cells. The command might not available if the sheet is protected.

Rows: Format rows- increase/ decrease heights, autofits selection and hides unhides rows.

Columns: Format columns increase/ decrease heights, autofits selection and hides /unhides columns.

Sheet: Formats worksheets renames, hides or unhides sheet.

Autoformat: Applies a built in combination of formats, called an autoformat, to a cell rangs or a pivot table. If a single cell is selected, excel automatically selects the range surrounded by blank cells and applies the autoformat to that range, if the selection is part of a pivot table the entire table except for the page fields is selected and formatted. This command is not available is the sheet is protected.

Conditional formatting: Applies formats to the selected cells that meet specific criteria based on values or formulas you specify.

Style: Defines or applies to the selection a combination of formats called a style.

Tools Menu:

Spelling and Grammar: Checks the active document for possible spelling, grammar and writing style errors, and displays suggestions for correcting them.

Auto Correct: Sets the options used to correct text automatically as you type, or to store and reuse text and other items you use frequently.

Share Workbook: Switches to shared workbook mode which allows you and another users on your network to edit and save changes to the same workbook.

Track Changes: Marks changes in the current document and keeps track of each change by reviewer name.

Merge Workbook: Combines changes from multiple copies of a shared workbook into one workbook.

Protection: Prevents changes to cells on worksheets, items in a chart, graphic objects on a worksheet or chart sheet, or code in a visual basic editor form.

Online Collaboration:  Starts an impromptu online by sending an invitation to participants who must be running Microsoft NetMeeting on their computers. It also schedules an online meeting by using names from the address book of your e-mail program, and displays the Discussions toolbar, where you can insert a new discussion.

Goal Seek: Adjusts the  value in a specified cell until a formula that is dependent on that cell reaches a target value.

Scenarios: Creates and saves scenarios, which are sets of data you can use to view the results of what if analyses.

Auditing: Finds cells that have a relationship to a formula displays formulas affected by changes in a cell, and tracks down the sources of errors values.

Macro: Opens the macro dialog box, where you can run, edit, or delete a macro. Use record new macro to record a series of actions as a macro, or click visual basic editor to write a macro. Use security to set a high, medium or low security level for files that might contain macro viruses.

Customize: Customizes toolbar buttons, menu commands, and shortcut key assignments.

Options: Modifies settings for Microsoft Office programs such as screen appearance, printing, editing, spelling and other options.

Data Menu:

Sort: Arranges the information in selected rows or lists alphabetically, numerically or by date.

Filter: Displays only those rows that match the value in the active cell and AutoFilter arrows to the right of each column label.

Form: Displays a data form in a dialog box. You can use the data form to see, change, add, delete, and find records in a list or database.

Subtotals: Calculates subtotal and grand total values for labeled columns you select. MS Excel automatically inserts and labels the total rows and outlines in the list.

Validation: Defines what data is valid for individual cells or cell ranges restricts the data entry for to a particular type such as whole numbers. Decimals numbers or text; and sets limits on the valid entries.

Table: Creates a data table based on input values and formulas you define. Data tabled can be used to show the results of changing values in your formulas.

Text to columns: Separates text in one cell on a worksheet into columns by using the Convert Test to Columns Wizard helps you specify how you want the text divided into columns using a separator or delimiter, such as tabs or commas.

Consolidate: summarizes the data from one or more source areas and displays it in a table.

Group and outline: creates and clean groups, subgroups and outlines.

Pivot Table and Pivot Chart Report: starts the PivotTable Wizard, which guides you through creating or modifying a PivotTable.

Get External Data: Creates and runs Web or database queries.

Refresh Data: Updates the data in a PivotTable if the source data has Changes.

Window menu

New window: opens a new window with the same contents a the active window. So you can view different parts of a file at the same time.

Arrange: displays all open files in separate windows on the screen. The arrange command makes it easier to drag between files.

Hide: hides the active workbook window. A hidden window remains open.

Unhide: displays hidden workbook window.

Spilt: splits the active window into panes. The user can specify the size of two windows. In case the current window is already  split this option would change to remove split, and can be used to removes the split.

Freeze panes: freezes the top pane, left pane or both in the active worksheet. Use the freeze panes button to keep columns and rows title in view while your’s scrolling through a worksheet freezing titles in a worksheet does not affect printing.

Help menu:

Microsoft excel help: to launch the Ms office assistant which provides help and tips to you execute many tasks.

Show the office assistant: displays or removes the office assistant from view.

What’s this?: Provides a link to Microsoft’s home site containing free stuff, feedback, answers to frequently asked questions, technical help etc.

Detect and repair: Automatically finds and files errors in this programs.

About Microsoft excel: Shows you the Excel’s version details and copyright message along with detailed information about your computer system (called system info)