Workbooks and Worksheets/Spreadsheets



Introduction To Worksheets/Spreadsheets & Their Applications

In Microsoft Excel, 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.

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

The names of the sheets appear on tabs at the bottom of the workbook window. To move from sheet to sheet, click the sheet tabs. The name of the active sheet is bold.

Notes

Although worksheets have countless applications and operations but the more commonly operations associated with their use are as follows:

Auditing

Calculating

Copying & Moving Data

Custom View

Deleting Data

Formatting Data

Displaying Graphics

Hiding & Displaying Data

Linking to Lotus 1-2-3

Page Layout

Printing Worksheets

Renaming Worksheets

Scrolling Worksheets

Sizing Worksheets

Spell Checking

Splitting Worksheets

Switching Worksheets

Creating Sheet Templates for new worksheets

Viewing etc..

Working With Rows & Columns, Changing Height,Width And Cell Formatting

Adding Rows or Columns to a Table

1. Select the row below where you want to insert new rows, or select the column to the right of where you want to insert new columns. Select the same number of rows or columns as the number of rows or columns you want to insert.

2. On the Insert Menu Click Rows or Columns to insert new rows & columns.

Notes

▪ You can also use the Draw Table tool to draw the row or column where you want.

▪ To add a row at the end of a table, click the last cell of the last row, and then press TAB.

▪ To add a column to the right of the last column in a table, click just outside the rightmost column. On the Table menu, click Select Column, and then click Insert Columns.

Changing Column Width and Row Height

You can adjust the width of columns and the height of rows. You can also define the default width of columns for a worksheet. Defining the default column width adjusts all columns to the same width, except columns that have previously been changed.

Change column width

Changing Column Width

1. Drag the boundary on the right side of the column heading until the column is the width you want.

2. The displayed column width is the average number of digits 0-9 of the standard font that fit in a cell.

Notes

▪ To change the column width for multiple columns, select the columns you want to change. Then drag a boundary at the right of a selected column heading. To change the column width for all columns on the worksheet, click the Select All button, and then drag the boundary of any column heading.

▪ To make the column width fit the contents, double-click the boundary to the right of the column heading.

Changing row height

1. Drag the boundary below the row heading until the row is the height you want.

Notes

▪ To change the row height for multiple rows, select the rows you want to change. Then drag a boundary below a selected row heading. To change the row height for all rows on the worksheet, click the Select All button, and then drag the boundary below any row heading.

▪ To make the row height fit the contents, double-click the boundary below the row heading.

Defining the default column width

1. To define the default column width for all worksheets in a workbook, select all worksheets.

2. On the Format menu, point to Column, and then click Standard Width.

3. Type a new measurement. The number that appears in the Standard column width box is the average number of digits 0-9 of the standard font that fit in a cell.

Notes

▪ To define the default column width for all-new workbooks and worksheets, create a workbook template and a worksheet template.

Basic Functions

AUDITING

With the Auditing toolbar, you can examine the relationships between cells and formulas on your worksheet and identify errors. When you use the auditing tools, tracer arrows point out cells that provide data to formulas and the cells that contain formulas that refer to the cells. A box is drawn around ranges of cells that provide data to formulas. When you use the buttons on the Auditing toolbar, tracer arrows point to cells referred to by formulas. If those cells contain formulas, click the button again to see the next level of cells that provide data to the formula.

CALCULATIONS

Calculation is the process of computing formulas and then displaying the results as values in the cells that contain the formulas. By default, Microsoft Excel automatically calculates all open workbooks. However, you can control when calculation occurs.

Whenever possible, Microsoft Excel updates only those cells dependent on other cells that contain values that have changed. This type of calculation helps to avoid unnecessary calculations. Microsoft Excel also calculates workbooks each time they are opened or saved.

Microsoft Excel calculates the underlying, or stored, values in cells. The value you see on the screen depends on how you choose to format and display the stored value. For example, a cell that displays a date as "6/22/96" also contains a serial number that is the stored value for the date in the cell. You can change the display of the date to another format (for example, to "22-Jun-96"), but changing the display of a value on a worksheet does not change the stored value.

As calculation proceeds, you can choose commands or perform actions such as entering numbers or formulas. Microsoft Excel temporarily interrupts calculation to carry out the other commands or actions and then resumes calculation. The calculation process may take more time if the workbook contains a large number of formulas, if the worksheets contain data tables, or if the worksheets contain functions that automatically recalculate every time the workbook is recalculated.

COPY DATA WITHIN A ROW OR COLUMN

1. Select the cells that contain the data you want to copy.

2. Drag the fill handle across the cells you want to fill and then release the mouse button. Existing values or formulas in the cells you fill are replaced.

Notes

▪ Fill Handle

The small black square in the corner of the selection. When you point to the fill handle, the pointer changes to a black cross. To copy contents to adjacent cells or to fill in a series such as dates, drag the fill handle.

To display a shortcut menu that contains fill options, hold down the right mouse button as you drag the fill handle.

▪ To quickly fill in the active cell with the contents of the cell above it, press CTRL+D. To fill in with contents of the cell to the left, press CTRL+R.

▪ If you drag the fill handle up or to the left of a selection and stop in the selected cells without going past the first column or the top row, you will delete the data in the selection.

▪ If values such as numbers or dates are incremented through the selected range instead of copied, select the original values again and hold down CTRL as you drag the fill handle.

MOVING OR COPYING CELL DATA

When you copy a cell by dragging or by clicking Cut or Copy, and Paste, Microsoft Excel copies the entire cell, including formulas and their resulting values, comments, and cell formats.

If the selected copy area includes hidden cells, Microsoft Excel also copies the hidden cells. If the paste area contains hidden rows or columns, you might need to unhide the paste area to see all of the copied cells.

Notes

▪ Copy Area

The cells you copy when you want to paste data into another location. After you copy cells, a moving border appears around them to indicate that they've been copied.

When you copy cells, references to the original cells aren't affected. Microsoft Excel adjusts relative references of formulas that are pasted into a new location.

Paste Area

The target destination for data that's been cut or copied by using the Clipboard.

CUSTOM VIEWS

You can save the current appearance of a workbook so that you don't have to change the settings every time you view or print the workbook. You can save the different view settings including hidden rows or columns and filter settings , as well as print settings. If you hide a sheet before a view is added, Microsoft Excel will hide the sheet each time you show the view.

Before you create a view, set up the workbook to appear the way you want to view it as well as how you want it to look when you print it. If you include print settings in a view, the view will include either the currently defined print area for each sheet or the entire worksheet if the sheet has no defined print area.

Notes

▪ Print Area

One or more ranges of cells you can print when you don't want to print an entire worksheet. If a worksheet includes a print area, only the print area will be printed. You create a print area by using the Set Print Area command (File menu, Print Area submenu).

CLEAR OR DELETE CELLS, ROWS, OR COLUMNS

When you delete cells, Microsoft Excel removes them from the worksheet and shifts the surrounding cells to fill the space. When you clear cells, you remove the cell contents (formulas and data), formats, or comments, but leave the blank cells on the worksheet.

Clear contents, formats, or comments from cells

1. Select the cells, rows, or columns you want to clear.

2. On the Edit menu, point to Clear, and then click All, Contents, Formats, or Comments.

Notes

▪ If you click a cell and then press DELETE or BACKSPACE, Microsoft Excel removes the cell contents but does not remove any comments or cell formats.

▪ If you clear a cell, Microsoft Excel removes the contents, formats, comments, or all three from a cell. The value of a cleared cell is 0 (zero), and a formula that refers to that cell will receive a value of 0.

▪ To remove all comments from a worksheet, click Go To on the Edit menu, click Special, and then click Comments. Then point to Clear on the Edit menu, and click Comments.

Delete cells, rows, or columns

1. Select the cells, rows, or columns you want to delete.

2. On the Edit menu, click Delete. Surrounding cells shift to fill the space.

Notes

▪ Microsoft Excel keeps formulas up to date by adjusting references to the shifted cells to reflect their new locations. However, a formula that refers to a deleted cell displays the #REF! error value.

DISPLAYING OR HIDING DRAWING OBJECTS AND IMPORTED GRAPHICS

Hide drawing objects and graphics if you want to speed up scrolling on the screen or printing.

1. On the Tools menu, click Options, and then click the View tab.

2. To display the drawing objects and imported graphics, click Show all. To hide the drawing objects and imported graphics, click Hide all.

LINKING LOTUS 1-2-3 AND MICROSOFT EXCEL WORKSHEETS

You can use the data within a Lotus 1-2-3 worksheet in a Microsoft Excel worksheet without converting the Lotus 1-2-3 worksheet to Microsoft Excel workbook format. Then, when you change the data in the Lotus 1-2-3 worksheet, the Microsoft Excel worksheet is automatically updated. For example, you can link sales figures from various Lotus 1-2-3 worksheets to a single Microsoft Excel worksheet, and then you can create a PivotTable to summarize the data, create a chart to emphasize the summary values, or use the format and print features of Microsoft Excel to create a summary report of sales. To learn how to create a formula that links to data on a Lotus 1-2-3 worksheet.

You can link Microsoft Excel worksheets to files saved in any of the following Lotus 1-2-3 formats: WKS, WK1, WK3, and WK4. If you use Lotus 1-2-3 to edit a Lotus 1-2-3 worksheet that is linked to a Microsoft Excel worksheet, the linked cells will update when you open the Microsoft Excel workbook.

If you are unsure of the name or location of a Lotus 1-2-3 worksheet that provides data to a Microsoft Excel worksheet, you can open the Microsoft Excel workbook and then use the Links command (Edit menu) to open the Lotus 1-2-3 worksheet. You can also use the Links command to change or redirect the links in the Microsoft Excel worksheet to refer to another worksheet.

Create a formula to calculate data on a Lotus 1-2-3 worksheet

1. Open the Lotus 1-2-3 worksheet that contains the source data.

2. On the Lotus 1-2-3 worksheet, select the cells you want to link to.

3. Click Copy.

4. Switch to the Microsoft Excel workbook that will contain the link formula.

5. Select the first cell in which you want the link.

6. On the Edit menu, click Paste Special.

7. Click Paste Link.

CHANGING THE WORKSHEET AREA THAT APPEARS ON A PRINTED PAGE

If your work doesn't fit exactly on the number of printed pages you want, you can adjust, or scale, your printed work to fit on more or fewer pages than it would at normal size. You can also specify that you want to print your work on a certain number of pages.

Reduce or enlarge a worksheet to fit the page

1. Click the worksheet.

2. On the File menu, click Page Setup, and then click the Page tab.

3. In the Adjust to box, enter the percentage by which you want to reduce or enlarge the worksheet.

Print a worksheet on a specified number of pages

1 Click the worksheet.

2 On the File menu, click Page Setup, and then click the Page tab.

3 Click Fit to.

4 Enter the number of pages on which you want to print the work.

Notes

▪ Printed data will not exceed the specified number of pages. Microsoft Excel will not enlarge the data to fill the pages.

▪ Microsoft Excel ignores manual page breaks when you fit the worksheets on a specified number of pages.

Insert or move a page break

If you want to print a worksheet that is larger than one page, Microsoft Excel divides it into pages by inserting automatic page breaks. These page breaks are based on the paper size, margin settings, and scaling options you set. You can change which rows are printed on the page by inserting horizontal page breaks; you can insert vertical page breaks to change which columns are printed on the page. In page break preview, you can move page breaks by dragging them to a different location on the worksheet.

Print a specific area of a worksheet

1. On the View menu, click Page Break Preview.

2. Select the area you want to print.

3. Right-click a cell within the selection, and then click Set Print Area on the shortcut menu.

Notes

▪ You can add additional cells to a print area in page break preview. Select the cells you want to add, right-click a cell in the selection, and then click Add to Print Area on the shortcut menu.

RENAMING A SHEET

1. Double-click the sheet tab.

2. Type a new name over the current name.

Notes

▪ Sheet tab

A tab near the bottom of a workbook window that displays the name of a sheet. Click the sheet tab to make a sheet active. To display a shortcut menu, click a tab with the right mouse button. To scroll through the sheet tabs, use the tab scrolling buttons to the left of the tabs.

MOVING AND SCROLLING THROUGH A WORKSHEET

To move between cells on a worksheet, click any cell or use the arrow keys. When you move to a cell, it becomes the active cell. To see a different area of the sheet, use the scroll bars.

To scroll Do this

One row up or down Click the arrows in the vertical scroll bar.

One column left or right Click the arrows in the horizontal scroll bar.

One window up or down Click above or below the scroll box in the vertical scroll bar.

One window left or right Click to the left or right of the scroll box in the horizontal scroll bar.

A large distance Drag the scroll box to the approximate relative position. In a very large worksheet, hold down SHIFT while dragging.

Notes

▪ The size of a scroll box indicates the proportional amount of the used area of the sheet that is visible in the window. The position of a scroll box indicates the relative location of the visible area within the worksheet.

REDUCING OR ENLARGING A WORKSHEET TO FIT THE PAGE

1. Click the worksheet.

2. On the File menu, click Page Setup, and then click the Page tab.

3. In the Adjust to box, enter the percentage by which you want to reduce or enlarge the worksheet.

CHECKING PARTS OF A WORKSHEET FOR SPELLING

Unless a range of cells or an object is selected when you check spelling, Microsoft Excel checks the entire active worksheet, including cell values, cell comments, embedded charts, text boxes, buttons, and headers and footers. However, Microsoft Excel does not check protected worksheets, formulas, or text that results from a formula.

If the formula bar is active when you check spelling, Microsoft Excel checks only the contents of the formula bar.

SWITCHING TO ANOTHER SHEET IN A WORKBOOK

1. Click the sheet tab for the other sheet.

If you don't see the tab you want, click the tab scrolling buttons to display the tab. Then click the tab.

Notes

▪ If your workbook contains many sheets, right-click the tab scrolling buttons. Then click the sheet you want.

RESTORING A SPLIT WINDOW TO A SINGLE PANE

1. To restore a window split into two scrollable areas, double-click any part of the split bar that divides the panes.

To remove unscrolling "frozen" panes, click Unfreeze Panes on the Window menu.

CREATING A SHEET TEMPLATE FOR NEW WORKSHEETS

1. Create a workbook that contains one worksheet. On the worksheet, include the formatting, styles, text, and other information you want to appear on all new sheets of the same type.

2. On the File menu, click Save As.

3. In the Save as type box, click Template (*.xlt).

4. In the Save in box, select the folder where you want to store the template.

To create the default worksheet template, select either the XLStart folder in the Microsoft Excel folder or the alternate startup folder. To create a custom sheet template, select the Templates folder in your Office folder or your Microsoft Excel folder.

5. In the File name box, type sheet to create a template for default worksheets.To create a custom sheet template, type any valid file name.

6. Click Save, and then click Close on the File menu.

Built In Functions

Functions are predefined formulas that perform calculations by using specific values, called arguments, in a particular order, called the syntax. For example, the SUM function adds values or ranges of cells, and the PMT function calculates the loan payments based on an interest rate, the length of the loan, and the principal amount of the loan.

Arguments can be numbers, text, logical values such as TRUE or FALSE, arrays, error values such as #N/A, or cell references. The argument you designate must produce a valid value for that argument. Arguments can also be constants, formulas, or other functions. For more information about using a function as an argument for another function, also known as nesting functions.

The syntax of a function begins with the function name, followed by an opening parenthesis, the arguments for the function separated by commas, and a closing parenthesis. If the function starts a formula, type an equal sign (=) before the function name. As you create a formula that contains a function, the Formula Palette will assist you

HOW FORMULAS CALCULATE VALUES

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. The following example adds the value of cell B4 and 25 and then divides the result by the sum of cells D5, E5, and F5.

Formulas calculate values in a specific order that is known as the syntax. The syntax of the formula describes the process of the calculation. A formula in Microsoft Excel begins with an equal sign (=), followed by what the formula calculates. For example, the following formula subtracts 1 from 5. The result of the formula is then displayed in the cell.

=5-1

Cell References

A formula can refer to a cell. If you want one cell to contain the same value as another cell, enter an equal sign followed by the reference to the cell. The cell that contains the formula is known as a dependent cell ¾ its value depends upon the value in another cell. Whenever the cell that the formula refers to changes, the cell that contains the formula also changes. The following formula multiplies the value in cell B15 by 5. The formula will recalculate whenever the value in cell B15 changes.

=B15 * 5

Formulas can refer to cells or ranges of cells, or to names or labels that represent cells or ranges.

ENTERING A FORMULA TO CALCULATE A VALUE

You can create a wide variety of formulas in Microsoft Excel, from formulas that perform a simple arithmetic operation to formulas that analyze a complex model of formulas.

A formula can contain functions, which are predefined formulas that perform simple or complex calculations. To perform multiple calculations simultaneously and then return one or more results, use an array formula.

Entering a formula

1. Click the cell in which you want to enter the formula.

2. Type = (an equal sign).

If you click Edit Formula or Paste Function , Microsoft Excel inserts an equal sign for you.

3. Enter the formula.

4. Press ENTER.

Notes

▪ You can enter the same formula into a range of cells by selecting the range first, typing the formula, and then pressing CTRL+ENTER.

▪ You can also enter a formula into a range of cells by copying a formula from another cell.

Enter a formula that contains a function

1. Click the cell in which you want to enter the formula.

2. To start the formula with the function, click Edit Formula in the formula bar.

3. Click the down arrow next to the Functions box .

4. Click the function you want to add to the formula. If the function does not appear in the list, click More Functions for a list of additional functions.

5. Enter the arguments.

6. When you complete the formula, press ENTER.

Array formulas and how to enter them

An array formula can perform multiple calculations and then return either a single result or multiple results. Array formulas act on two or more sets of values known as array arguments. Each array argument must have the same number of rows and columns. You create array formulas the same way that you create basic, single-value formulas. Select the cell or cells that will contain the formula, create the formula, and then press CTRL+SHIFT+ENTER to enter the formula.

If you want only a single result, Microsoft Excel may need to perform several calculations to generate that result. For example, the following formula averages only the cells in the range D5:D15 where the cell in the same row in column A contains the text "Blue Sky Airlines". The IF function finds the cells in the range A5:A15 that contain "Blue Sky Airlines" and then returns the value in the corresponding cell in D5:D15 to the AVERAGE function.

{=AVERAGE(IF(A5:A15="Blue Sky Airlines",D5:D15))}

To calculate multiple results with an array formula, you must enter the array into a range of cells that has the same number or rows and columns as the array arguments. In the following example, given a series of five sales figures (in column B) for a series of five dates (in column A), the TREND function determines the straight-line values for the sales figures. To display all of the results of the formula, it is entered into five cells in column C (C10:C15).

{=TREND(B10:B15,A10:A15)}

You can also use an array formula to calculate single or multiple results for a series of values that have not been entered on the worksheet. Array formulas can accept constants the same way nonarray formulas do, but you must enter the array constants in a certain format. For example, given the same five values and the same five dates in the preceding example, you can project the sales figures for two additional dates in the future. Because formulas or functions cannot be array constants, the following example uses serial numbers to represent the additional dates for the third argument in the TREND function:

{=TREND(B10:B15,A10:A15,{35246;35261})}

Multiple Functions Within Functions, Or Nesting

Functions can be used as arguments for other functions. When a function is used as an argument, or nested, it must return the same type of value that the argument uses. If a nested function does not return the correct type of value, Microsoft Excel will display a #VALUE! error value. For example, the following formula uses a nested AVERAGE function and compares it with the value 50. The comparison must return TRUE or FALSE because this is the required type of value for the first argument in an IF function.

A formula can contain up to seven levels of nested functions. When Function B is used as an argument in Function A, Function B is a second-level function. If Function B contains Function C as an argument, Function C would be a third-level function.

You can use the Formula Palette to nest functions as arguments. For example, you can insert Function B as an argument of Function A by clicking the drop-down arrow in the formula bar. If you want to continue entering arguments for Function A, click the name of Function A in the formula bar.

User-Defined Functions

If you use a particularly complex calculation in many formulas or calculations that require several formulas because existing worksheet functions do not meet your needs, you can create custom functions. These functions, known as user-defined functions, are created by using Visual Basic for Applications. For more information about creating user-defined functions, see Visual Basic Help.

-----------------------

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download