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.