Download Application of spread sheets and more Lecture notes Computer Applications in PDF only on Docsity!
UNIT IV
4. Introduction to MS Excel 2007
Spread sheet software is very versatile and can be used for both very simple and very complex tasks. Lists, such as vocabulary, parts for a project or grocery shopping, can be easily composed in a spread sheet. Adding or deleting items from a list like this is also simple, so the lists can be used many times.
The management of more complex data, such as earnings, expenses, budgets and other accounting, is also made easier with a spread sheet. Spread sheet programs include features that can calculate complicated math, including everything from basic addition and subtraction to percentages, taxes and multi-step problems. This makes spread sheets essential for businesses, self-employed individuals and anyone who needs to keep an account of expenses and income.
4.1. Application of spread sheets
▲ Finance and Accounting :This is the area of business with the biggest reliance and benefit
from Excel spread sheets. Advanced formulas in Excel can turn manual processes that took weeks to complete in the 1980s into something that takes only a few minutes today.
▲ Financial Analysis : Excel can help to do financial analysis, to list customer and sales
targets can help you manage your sales force and plan future marketing plans based on past results.
▲ Summarize the sales : Using a pivot table, users can quickly and easily summarize customer
and sales data by category with a quick drag-and-drop.
▲ Calculating employee wages and salaries : Excel allows users to discover trends,
summarize expenses and hours by pay period, month, or year, and better understand how your workforce is spread out by function or pay level.
▲ Maintain employee data : HR professionals can use Excel to take a giant spread sheet full of
employee data and understand exactly where the costs are coming from and how to best plan and control them for the future.
▲ Games : When planning a team outing to a baseball game, you can use Excel to track the
RSVP list and costs.
▲ Customer Forecast : Excel creates revenue growth models for new products based on new customer forecasts.
▲ Budgeting : When creating a budget for a small product, you can list expense categories in a spread sheet, update it monthly and create a chart to show how close the product is to budget across each category.
▲ Create Lists : You can create lists, from shopping lists to contact lists, on a spreadsheet. For
example, if you entered store items to a spreadsheet along with their corresponding aisles, you could sort by aisle and print before your shopping trip. Your list would provide an aisle-by- aisle overview.
4.2. Menus and Toolbars
MS Excel Menu Bar allows one to perform various calculations. Each menu has its own specific calculation. The main menus of MS Excel Menu bar are listed as below.
4.2.1. The Excel Window
4.2.1.1. The Microsoft Office Button
In the upper-left corner of the Excel 2007 window is the Microsoft Office button. When you click the button, a menu appears. You can use the menu to create a new file, open an existing file, save a file, and perform many other tasks.
4.2.1.2. The Quick Access Toolbar
Next to the Microsoft Office button is the Quick Access toolbar. The Quick Access toolbar gives you with access to commands you frequently use. By default, Save, Undo, and Redo appear on the Quick Access toolbar. You can use Save to save your file, Undo to roll back an action you have taken, and Redo to reapply an action you have rolled back.
4.2.1.3. The Title Bar
Next to the Quick Access toolbar is the Title bar. On the Title bar, Microsoft Excel displays the name of the workbook you are currently using. At the top of the Excel window, you should see "Microsoft Excel - Book1" or a similar name.
4.2.1.4. The Ribbon
- Alignment : Text vertical and horizontal alignment, indentation, text wrapping… etc.
- Number : Number formatting e.g. Number, Date, accounting. Also decimals, comma style and percentage.
- Styles : Allows you to specify Conditional formatting by defining formatting rules. You can also format a selected cell by selecting one of the built in formatting styles or you can define a range as a table, and give it one of the predefined styles, or further more you can define your own style.
- Cells : Allows you to Insert, delete or format, not only cells but also whole columns, rows or sheets. Formatting also includes renaming, hiding or protecting.
- Editing : Mainly for formulas, sorting, filtering, find and replace.
4.2.2.2. Insert Tab
The Insert tab in Excel 2007 user interface consists of 5 groups:
- Tables : Allows you to define a range of cells as a table for easy filtering and sorting and create a pivot table form your data.
- Illustrations : It is used to insert picture, clip art, shapes or SmartArt (a more stylish flowchart-like shapes).
- Charts : To insert different types of charts. More chart specific options will be shown when the chart is created.
- Links : Allows you to insert a hyperlink to a place in the same workbook or an external one.
- Text : To insert text box, header and footer, wordart, digital signature, object (embedded document like word or power point presentation) or a symbol.
4.2.2.3. Page layout Tab : The page layout tab in Excel 2007 ribbon consists of 5 groups:
- Function Library : Chose from a set of Excel 2007 built-in functions. Functions are divided into groups for easy access.
- Defined Names : Manage names of ranges. Create, edit, delete or use in formulas.
- Formula Auditing : Show or hide formulas, trace dependents, check errors … etc.
- Calculation : Specify calculations options for formulas : manual, automatic … etc.
- Solutions : From here you can use the conditional sum wizard and lookup wizard to help you in creating new formulas.
4.2.2.5. Data Tab : The Data tab in Excel 2007 ribbon consists of 6 groups:
- Get External Data : Here you can import data into Excel from various external sources like Microsoft Access, Microsoft SQL Server, text files or the web.
- Connections : Create and edit connections to external data sources that are stored in a workbook or in a connection file.
- Sort and filter : Sort or filter data based on a specified criteria.
- Data Tools: Here you have various data tools like removing duplicates, validation or data analysis.
- Outline : Here you can group data based on a selection or you can create a subtotal in a given column based on values (keys) of another column. 6. Analysis : Provides various data analysis tools like random number generation, histogram or moving average.
- Show/Hide: You can show or hide gridlines, formula bar or columns and row headings.
- Zoom : Zoom in or out with different options.
- Window : Create new windows, arrange windows and switch between them, freeze panes or save a workspace.
- Macros : This group handles VBA macros. You can record, edit or run macros.
- Worksheets
Microsoft Excel consists of worksheets. Each worksheet contains columns and rows. The columns are lettered A to Z and then continuing with AA, AB, AC and so on; the rows are numbered 1 to 1,048,576. The number of columns and rows you can have in a worksheet is limited by your computer memory and your system resources.
The combination of a column coordinate and a row coordinate make up a cell address.
4.3.1 Operations on Work sheets
4.3.1.1. Creating a new blank worksheet
- Left-click the Microsoft Office button.
- (^) Select New. The New Workbook dialog box opens, and Blank Workbook is highlighted by default.
- Click Create. A new blank workbook appears in the window.
4.3.1.2. Opening an existed Worksheet
You can use the Open command or the Open button on the toolbar to open a worksheet.
- Office 2007 - From the Office button select Open or click the Open button on the Quick Access toolbar. Office 2003 - From the File menu choose Open or click the Open button on the toolbar.
- Use the Look In box to specify the folder in which the workbook you want to open is stored.
- Select the workbook you want to open and click Open (or double-click the workbook name).
4.3.1.3. Save a Worksheet
You can use the Save As command to save a worksheet for the first time.
- Office 2007 - From the Office button choose Save As. Office 2003 - From the File menu choose Save As.
- In the File name box, type a name for the worksheet.
- Click Save.
4.3.1.4. Close a Worksheet
- Office 2007 - From the Office button choose Close. Office 2003 - From the File menu choose Close.
- If necessary, Save the worksheet.
- If you want to quit Excel, Office 2007 - From the Office button choose Quit. Office 2003 - From the File menu choose Quit. 4. Working with Cells
A cell is the intersection between a row and a column on a spreadsheet that starts with cell A1. Below is an illustrated example of a highlighted cell in Microsoft Excel; the cell address , cell name , or cell pointer "D8" (column D, row 8) is the selected cell and the location of what is being modified.
A cell can only store 1 piece of data at a time. You can store data in a cell such as a formula, text value, numeric value, or date value.
You can also select multiple cells at the same time. A group of cells is known as a cell range. Rather than a single cell address, you will refer to a cell range using the cell addresses of the first and last cells in the cell range, separated by a colon. For example, a cell range that included cells A1, A2, A3, A4, and A5 would be written as A1:A5.
3. Editing or deleting text in the cell:
- Select the cell.
- Press the Backspace key on your keyboard to delete text and make a correction.
- Press the Delete key to delete the entire contents of a cell.
You can also make changes to and delete text from the formula bar. Just select the cell, then place your insertion point in the formula bar.
4.3. Coping and pasting cell contents:
- Select the cell or cells you wish to copy.
- Click the Copy command in the Clipboard group on the Home tab. The border of the selected cells will change appearance.
- (^) Select the cell or cells where you want to paste the information.
- Click the Paste command. The copied information will now appear in the new cells.
To select more than one adjoining cell, left-click one of the cells, drag the cursor until all of the cells are selected, and release the mouse button.
The copied cell will stay selected until you perform your next task, or you can double- click the cell to deselect it.
4.4. Cut and Paste cell contents:
- Select the cell or cells you wish to cut.
- Click the Cut command in the Clipboard group on the Home tab. The border of the selected cells will change appearance.
- Select the cell or cells where you want to paste the information.
- Click the Paste command. The cut information will be removed from the original cells and now appear in the new cells.
Note : The keyboard shortcut for Paste is the Control Key and the V key.
4.5. (^) Drag and drop text:
- Select the cell or cells you wish to move.
- Position your mouse pointer near one of the outside edges of the selected cells. The mouse pointer changes from a large, white cross to a black cross with four arrows.
You can refer to cells that are on other worksheets by appending the name of the worksheet followed by an exclamation point (!) to the start of the cell reference. In the following example, the worksheet function named AVERAGE calculates the average value for the range B1:B10 on the worksheet named Marketing in the same workbook.
Reference to a range of cells on another worksheet in the same workbook
- Refers to the worksheet named Marketing
- Refers to the range of cells between B1 and B10, inclusively
- Separates the worksheet reference from the cell range reference
1. Click the cell in which you want to enter the formula.
2. In the formula bar , type = (equal sign).
3. Click the tab for the worksheet to be referenced.
4. Select the cell or range of cells to be referenced.
There are two types of cell references:
Relative and absolute references behave differently when copied and filled to other cells. Relative references change when a formula is copied to another cell. Absolute references, on the other hand, remain constant, no matter where they are copied.
4.8. Relative references
By default, all cell references are relative references. When copied across multiple cells, they change based on the relative position of rows and columns.
For example, if you copy the formula =A1+B1 from row 1 to row 2, the formula will become =A2+B2.
Relative references are especially convenient whenever you need to repeat the same calculation across multiple rows or columns.
4.9. Absolute reference
Earlier, we saw how cell references in formulas automatically adjust to new locations when the formula is pasted into different cells. This is called a relative reference.
Sometimes when you copy and paste a formula, you don't want one or more cell references to change. An absolute reference solves this problem. Absolute cell references in a formula always refer to the same cell or cell range in a formula. If a formula is copied to a different location, the absolute reference remains the same.
Note : An absolute reference is designated in the formula by the addition of a dollar sign ($).
It can precede the column reference or the row reference, or both.
Examples of absolute referencing include:
- Creating an absolute reference:
- Select the cell where you wish to write the formula (in this example, H2).
- Type the equals sign (=) to let Excel know a formula is being defined.
- Click on the first cell to be included in the formula (F2, for example).
- (^) Enter a mathematical operator (use the multiplication symbol for this example).
- Click on the second cell in the formula (C2, for example).
- Add a $ sign before the C and a $ sign before the 2 to create an absolute reference.
- Copy the formula into H3. The new formula should read =F3*$C$2. The F reference changed to F3 because it is a relative reference, but C2 remained constant because you created an absolute reference by inserting the dollar signs.
▲ Source data : The range of cells that make up a chart. The chart is updated automatically whenever the information in these cells changes.
▲ Title : The title of the chart.
▲ Legend : The chart key, which identifies what each color on the chart represents.
▲ Axis : The vertical and horizontal parts of a chart. The vertical axis is often referred to as the Y axis, and the horizontal axis is referred to as the X axis.
▲ Data series : The actual charted values, which are usually rows or columns of the source data.
▲ Value axis : The axis that represents the values or units of the source data.
▲ Category axis : The axis identifying each data series.
4.6.2. Chart tools
Once you insert a chart, a new set of Chart Tools, arranged into three tabs, will appear above the Ribbon. These are only visible when the chart is selected.
4.6.3. Changing the chart type:
- (^) Select the Design tab.
- Click the Change Chart Type command. A dialog box appears.
- Select another chart type.
- Click OK.
The chart in the example compares each salesperson's monthly sales to his or her other months' sales; however, you can change what is being compared. Just click the Switch Row/Column Data command, which will rotate the data displayed on the x and y axes. To return to the original view, click the Switch Row/Column command again.
4.6.4. Changing the chart layout:
- Select the Design tab.
- Locate the Chart Layouts group.
- Click the More arrow to view all of your layout options.
- Left-click a layout to select it.
If your new layout includes chart titles, axes, or legend labels, just insert your cursor into the text and begin typing to add your own text.
4.6.5. Changing chart style:
- Select the Design tab.
- Locate the Chart Style group.
- Click the More arrow to view all of your style options.
- Left-click a style to select it.
4.6.6. Moving the chart to a different worksheet
- Select the Design tab.
- Click the Move Chart command. A dialog box appears. The current location of the chart is selected.
- Select the desired location for the chart (i.e., choose an existing worksheet, or select New Sheet and name it).
