Spreadsheet basicsScreen elementsExcel allows you to create spreadsheets much like paper ledgers that can perform automatic calculations. Each Excel file is a workbook that can hold many worksheets. The worksheet is a grid of columns (designated by letters) and rows (designated by numbers). The letters and numbers of the columns and rows (called labels) are displayed in gray buttons across the top and left side of the worksheet. The intersection of a column and a row is called a cell. Each cell on the spreadsheet has a cell address that is the column letter and the row number. For example, in the image below the cell address of the cell resulting from the intersection of column E and row 4 is "E4". Cells can contain either text, numbers, or mathematical formulas. Adding and Renaming WorksheetsThe worksheets in a workbook are accessible by clicking the worksheet tabs on the bottom of the screen. By default, three worksheets are included in each workbook. To add a sheet, select Insert|Worksheet from the menu bar. To rename the worksheet tab, right-click on the tab with the mouse and select Rename from the shortcut menu. Type the new name and press the ENTER key. The Standard ToolbarThis toolbar is located just below the menu bar at the top of the screen and allows you to quickly access basic Excel commands. Modifying a WorksheetMoving Through CellsUse the mouse to select a cell you want to begin adding data to and use the arrow keys to move through the cells of a worksheet. Examples of movements and corresponding key strokes: One cell up: up arrow keyOne cell down: down arrow key or ENTER One cell left: left arrow key One cell right: right arrow key or TAB Top of the worksheet (cell A1): CTRL+HOME End of the worksheet (last cell containing data): CTRL+END End of the row: CTRL+right arrow key End of the column: CTRL+down arrow key Any cell: File|Go To menu bar command Adding Worksheets, Rows, and ColumnsWorksheets - Add a worksheet to a workbook by selecting Insert|Worksheet from the menu bar. Row - To add a row to a worksheet, select Insert|Rows from the menu bar, or highlight the row by clicking on the row label, right-click with the mouse, and choose Insert. Column - Add a column by selecting Insert|Columns from the menu bar, or highlight the column by click on the column label, right-click with the mouse, and choose Insert. Resizing Rows and ColumnsThere are two ways to resize rows and columns.
Selecting CellsBefore a cell can be modified or formatted, it must first be selected (highlighted). Examples for selecting groups of cells: One cell: click once in the cell To activate the contents of a cell, double-click on the cell or click once and press F2. Changing FormatTo change the format of cell contents, highlight cells and select Format|Cells from the menu bar or right-click with mouse and choose Format Cells. The dialog box allows you to change:
Moving and Copying CellsMoving Cells Copying Cells Pasting Cut and Copied Cells Drag and Drop Fill Down, Fill RightTo fill a set of adjacent cells in a column by copying the content of the cell at the top of the set, highlight the set of cells that need filling, then select Edit|Fill|Fill Down or press CTRL+D. To fill a set of adjacent cells in a row by copying the content of the cell at the far left of the set, highlight the set of cells that need filling, then select Edit|Fill|Fill Right or press CTRL+R. AutofillThe Autofill feature allows you to quickly fill cells with repetitive or sequential data such as chronological dates or numbers, and repeated text.
Paste SpecialInstead of copying entire cells, you can copy specified contents from the cells. For example, you can copy the resulting value of a formula without copying the formula itself.
Formulas and functionsThe distinguishing feature of a spreadsheet program such as Excel is that it allows you to create mathematical formulas and execute functions. FormulasFormulas are entered in the worksheet cell and must begin with an equal sign "=". The formula then includes the addresses of the cells whose values will be manipulated with appropriate operands placed in between. After the formula is typed into the cell, the calculation executes immediately and the formula itself is visible in the formula bar. An example is given below. The following image shows a worksheet used to create an array of equally-spaced points within the interval [2,4] using a fixed increment of 0.5. Cell D1 (column D row 1) contains the value of the increment; the cells in column A show the point labels; Cell B2 contains the value of the start point of the interval; Cell B3 contains the value of the first point in the interval; and so forth. To obtain the value in cell B3, the formula "=B2+D1" is used, as shown in the formula box. This means that the value "2.5" shown in cell B3 is calculated by adding the value stored in cell D1 (the increment) to the value in cell B2 (point x0). Relative, Absolute, and Mixed ReferencingCalling cells by just their column and row labels (such as "A1") is called relative referencing. When a formula contains relative referencing and it is copied from one cell to another, Excel does not create an exact copy of the formula. It will change cell addresses relative to the row and column they are moved to. For example, if the simple addition formula used in cell B3 "=B2+D1" in the image above is copied to cell B4, which is a cell in the same column but one row down, the formula would change to "=B3+D2" to reflect the new row. To prevent this change, cells must be called by absolute referencing and this is accomplished by placing dollar signs "$" within the cell addresses in the formula. Continuing the previous example, if the formula in cell B3 read "=B2+$D$1", when copied to cell B4, the formula would change to "=B3+$D$1", allowing the value of cell B4 to be the sum of cells B3 and D1. Both the column and row of cell D1 are absolute and will not change when copied. Mixed referencing can also be used where only the row OR column is fixed. For example, the formula in cell B3 could read "=B2+D$1", so that only the row of cell D1 is fixed. Then, when copied to cell B4, only the row in column B would change and the new formula in cell B4 would read "=B3+D$1". In this case, the calculated value in cell B4 would still be "3", as before, because the formula was copied in the same column. If, instead, for some reason the formula were copied to cell C3, one column to the right and one row down, the new formula in C3 would read "=C3+E$1" because only the row of the second term in the addition is absolute and would not change when copied. Basic FunctionsFunctions can be a more efficient way of performing mathematical operations than formulas. For example, if you wanted to add the values of cell B2 through B6 in theexample above, you would type the formula "=B2+B3+B4+B5+B6". A shorter way would be to use the SUM function and simply type "=SUM(B2:B6)". A few examples of functions are given in the list below:
ChartsCharts allow you to present data entered into the worksheet in a visual format using a variety of graph types. You can create a chart on its own sheet or as an embedded object on a worksheet. To create a chart, you must first enter the data for the chart on the worksheet. Then select that data and use the Chart Wizard to step through the process of choosing the chart type and various chart options. Chart WizardThe Chart Wizard brings you through the process of creating a chart by displaying a series of dialog boxes. The example below shows how to create a chart to plot the values of the function f(x)=x^2 over the interval [2,4].
Resizing the ChartTo resize the chart, click on its border and drag any of the nine black handle to change the size. handles on the corners will resize the chart proportionally while handles along the lines will stretch the chart.Moving the ChartSelect the border of the chart, hold down the left mouse button, and drag the chart to a new location. Elements within the chart such as the title and labels may also be moved within the chart. Click on the element to activate it, and use the mouse to drag the element to move it. |