Getting Started with Microsoft Excel - Entering and Editing Data

Entering Data As discussed before, data is entered by selecting a cell and entering data. In the illustration below, if you wanted to enter the year column (column A) you would click on cell A2, type 1997 and press [ENTER]. Entering the data would automatically advance the active cell to the next row or cell A3. The reason A2 was chosen as the first cell to start entering the year labels is to allow a row (row 1) to be used for headings of each of the columns. Subsequently, columns B, C, D, etc., were entered. This was done by first entering the column heading, e.g., Net Income, and then the values 80000, 78900, 67800, etc. Note the values entered were "plain" numbers such as 80000 instead of the formatted number $80,000.00. It is important to enter the number as a plain number and format the cell to the required specification. We will do this in a later section of this tutorial.

Editing Data Once you have entered data into a cell, that data can be edited by first clicking on the cell to make the cell active and then clicking on the formula bar above the worksheet. This allows you to insert or delete characters in that cell. An alternate method of editing the contents of a cell is to click on the cell to make it the active cell and then press the [F2] function key at the top of the keyboard. You can delete the contents of a cell by clicking on the cell and pressing [Delete] on the keyboard.

Inserting Columns, Rows and Cells To insert a column, click on the column you wish the new column to appear ahead of or to the left of and click Insert on the menu bar at the top of the screen. Selecting an existing row to insert a new row ahead of or above is done the same way. Once you have selected Insert on the menu bar at the top of the screen, you can click Columns to insert a new column or Rows to insert a new row. You can experiment inserting new columns, rows and cells. You will notice that when requesting a new cell, Excel asks if you wish to move the existing cells to the right or down.

Getting Started with Microsoft Excel - Working with Formulas

The power of a spreadsheet application is demonstrated most clearly by formulas. Formulas can be

simple, like dividing the values in two cells, or they can be very complex. The scope of this tutorial will focus on a more simple example of working with formulas.

Entering and Editing Formulas A formula can always be identified because it starts with an equal sign (=). To enter a formula, click on the cell that is to contain the formula and start the formula by pressing the equal sign (=). This tells Excel to handle the contents as a formula instead of a label or simple text. In cell E2 (1.724137931) in the illustration above, we entered the formula =C2/D2. After pressing [ENTER], Excel automatically advanced to cell E3. Cell E2 displayed the results of the formula or 1.724137931. The formula =C2/D2 is still the content of the cell, only Excel has displayed the result of the formula. In our illustration above, to determine Total Assets Turnover for each year, we would continue entering formulas in each cell in column E referencing the cells for Sales divided by Total Assets for each year. As stated, when a cell contains a formula, the value produced by that formula is displayed, as in cell E2 in the illustration above. The formula itself, however, is displayed in the formula bar above the worksheet. Once the formula is entered in the cell, it will be displayed only if the cell is being edited. Again, to edit a cell, click on the cell making it the active cell and either click on the formula bar above the worksheet, or press the [F2] function key at the top of the keyboard.

Copying Formulas Between Cells Since our illustration above is relatively short, it is not much of a problem to type each individual formula. However, if our illustration required 500 formulas instead of five, it would be very cumbersome to enter each formula. Once we have entered a formula into cell E2 we can copy that formula to cells E3 through E6. When we do this, Excel adjusts the formula's references so that each formula refers to the cells in each respective row, meaning the formula in row 3 becomes C3/D3, the formula in row 4 becomes C4/D4, etc. To do this you would:

Click on cell E2 to make it the active cell. Again, notice the formula =C2/D2 in the formula bar. From the menu bar at the top of the screen, click Edit and then Copy. You will notice a marquee around cell E2. Click on cell E3. However, when you click on the cell hold down the mouse button and drag through cells E4 through E6. You will notice each of the cells (E3...E6) become highlighted. Don't get confused that cell E3 is not "blacked out" or highlighted. Notice there is a heavy border around the cells.

With cells E3 through E6 highlighted, from the menu bar at the top of the screen, click Edit and then Paste.

Cells E3 through E6 will fill with the results of the new formula that was just copied. Scroll through the cells and notice that the formula in each cell reflects the appropriate cell references.

Return to Microsoft Excel Tutorial List

Getting Started with Microsoft Excel - Formatting the Worksheet

There are many formatting tools that can be used to highlight a worksheet and make it more readable. Headings are better defined when they are in bold text and dollar amounts are better understood when they reflect the standard currency convention.

In our illustration we have made row 1 the heading row. To better distinguish it, we have made the contents of each cell bold by highlighting row 1 and clicking on the Bold Style Button on the toolbar above the worksheet.

When first entering text into the heading cells, you may notice that the headings do not appear entirely. That is because the columns are too narrow to display the full text of the headings. To widen the columns:

Select the entire column by clicking on the worksheet column heading. For example, if we want to select column B, click on B at the top of the column. Once the column is selected, from the menu bar at the top of the screen, click Format, then Column, then AutoFit Selection

Excel adjusts the column widths to fit the cell contents. In the illustration above, column E (Total Assets Turnover) is the best example of the need to increase the column width. Next, when entering number values into cells, it is best to enter the "plain" number and then format the cell to reflect the appropriate function of the number. For example, when entering a column of dollar amounts such as column B (Net Income) in the illustration above, enter the simple numbers 80000, 78900, 67800, etc. After entering all of the numbers:

Select the cells to be formatted by clicking on the first cell in the column, in this case B2, holding down the mouse button and dragging through all of the cells that are to be selected. You will notice a heavy border around the cells included in the selection.

Click a format style button on the toolbar above the worksheet.

Although the illustration above uses the Currency Style, style buttons are available for percentages, setting commas and decimal places. In addition to the style buttons on the toolbar, multiple format options are available under the Format option on the menu bar at the top of the screen.

Getting Started with Microsoft Excel - Creating a Chart

Charts are visual representations of worksheet data. Various types of charts can be created in Excel, such as bar, line and pie charts. Charts can be used to clarify trends or relationships that might not be apparent in the worksheet data alone. Once a chart is created, as data on the worksheet is updated the chart automatically changes to reflect the updates.

To discuss charting data, let's consider the illustration above. We have a worksheet that contains data cells on the left and those same data cells charted on the right. To chart the data cell range L9:N14, do the following: 1. 2. 3. 4. 5. 6. 7. Select the range L9:N14 by clicking cell L9, holding the mouse button and dragging down and to the right to cell N14. With the cell range highlighted, click the ChartWizard button on the toolbar above the worksheet. You will notice the cursor change to a crosshairs with a small chart. Position the cursor on the worksheet where you want to place the chart and click the mouse. The ChartWizard dialog box appears. You've already selected the range you want to chart, so click Next >. As the type of chart, select Line and click Next >. Select the angular line style with no data points as the format of the line chart and click Next >. Note that at any step you can click < Back and repeat previous steps. The ChartWizard displays a sample line chart using your data. However, there is one small problem. Notice the box that reads: Use First 0 columns for Category (X) Axis Labels. In our illustration, the first column (years) is the label for the X Axis, so you want to enter 1 in this box. To do so, you can either overwrite the 0 that is in the box or click the up arrow to cycle the

counter to 1. After doing so, you will notice the chart change automatically to a better representation of the chart in the illustration above. Click Next >. 8. The next step allows us to add a legend and define titles to the chart, as well as each axis. After specifying titles if you wish, click Finish.

Excel creates the chart on the worksheet and displays the Chart Toolbar. To hide the Chart Toolbar, click anywhere on the worksheet outside the chart.

Getting Started with Microsoft Excel - Printing the Worksheet

Print Preview It is a good idea to always preview before you print so that you can make any adjustments before printing and save yourself repeated trips to the printer. To preview what you're about to print, from the menu bar at the top of the screen, choose File and then Print Preview. You can click the mouse button to magnify the area around the cursor and click again to return to full page view.

Printing You might notice that you can switch to Print Preview mode from Page Setup mode and vise versa. Page Setup and Print Preview modes are available to ease the task of setting up for printing. Once the worksheet has been prepared for printing, it can be printed by clicking File from the menu bar at the top of the screen and then Print...

SAVING THE WORKSHEET You should save your work frequently. If you have a power outage or some other problem, you can start working again from your last saved version. When you create a new worksheet and save it for the first time, you are always asked for a name to assign to the worksheet. From the File option on the menu bar at the top of the screen, click Save. If the worksheet is new, a dialog box will appear asking for a name and location to save the worksheet. If the worksheet you are working on as been previously saved, clicking Save from the File menu option will save a new copy of the worksheet overwriting the previous version. If you would like to save an existing worksheet to another name, thus keeping the original version in its original condition, from the menu bar at the top of the screen, click File and then Save As... A dialog box will appear asking for a name and location to save the worksheet.