Tutorial: Hands-On Excel

Tutorial Description
Excel is Microsoft's version of a spreadsheet program. Find out what a worksheet is and why you might use one. Learn to create, edit, format, save and print a worksheet. In this a quick start course, you will also use formulas, functions, and charts.

What can I use Excel for?
You can use Excel to store almost any kind of character or numeric information you want. Think of it as the electronic equivalent of an accountant’s spreadsheet pad. Any number can be written in any block (any “cell”) of the accountant’s pad. Any note or label can be written in any block of the pad. In any cell, you can store the result of a calculation.

The real purpose of Excel is to help you accumulate the information (text and numbers), to manipulate it in ways you want (calculate numbers and sort lines of related data). It excels in helping you do calculations once, and letting Excel recalculate automatically after changes or additions.

Edit a Worksheet
Let’s build a spreadsheet to track the value of our stock investments. We’ll use several local and technology stocks as examples.

Look at the bottom of the worksheet, and make sure that Sheet1 is the currently selected sheet. If not, left-click once on the word Sheet1.


Let’s start with Cell A1, with a title. Left-click once in cell A1, and type “Stock Prices” and press the enter key. [by default, this moved you to cell A2.] Now, press the right arrow key once, to move to cell B2. Enter the following in cells B2, B3, B4, B5 and B6– XYZ, Exxon, Microsoft, Apple, IBM. Now, you can enter the following data into Sheet1 or open file HOExcel_file.xls.

Saving your work
On the menu bar, click on File, Save As, select the directory in which you want to save your file, and call it Student-HOExcel-1.xls. Make sure that the “Save as type” is Microsoft Excel Workbook (*.xls). Of course, if you want to be able to open it another program, select that type (e.g., WK4 for Lotus 1-2-3), which will also change the extension from xls to wk4.

Back to "Edit a Worksheet"
Left-click on cell A1 and continue holding down the mouse button. Drag it across to G6, then let go. This highlights these cells. On the menu bar, click on Edit, Copy.

Now, let’s prepare the other sheets of this workbook. Click on Sheet2 on the tab at the bottom of the worksheets, click on cell A1 in Sheet2, and then Edit, Paste. Click on cell A1, and type Shares Owned. Left click on cell C2 and drag to cell G6, and press the Delete key.

Then, click on Sheet3, click on cell A1 in Sheet3, and then Edit, Paste. Click on cell A1, and type Investment Value: Left click on cell C2 and drag to cell G6, and press the Delete key.

Now, left click on the diskette icon (Save) on the “standard” toolbar, which will save your work in the same place with the same time.


Formulas and Functions
This is where we’ll see the real value of spreadsheets to do calculations. Left-click on Sheet2. Left-click on cell C2. Type =200 or +200 and press enter. You just entered your first formula. Characters will automatically be recognized as text. Numeric values will be numbers. Mixed numbers and text will be treated as text. Finally, if you want to have a formula, always start with an = sign or a + sign.

Left-click on cell D2, type =c2+25 and press enter. This formula says to take whatever value is in cell C2 and add 25 to it. In this case, the answer is 225.

Now, we’re going to copy a formula. Left click on C3 (that shows 225). Then, Edit, Copy. Left-click on D2 and drag to G2. Then, Edit, Paste. If all went well, cell G2 should read 300.

The other way to copy a formula is to select a cell or group of cells, then left-click-and-drag the small square at the bottom right of the selected cell(s). Let’s do it. Select cells C2 through G2. Left-click-and-drag the small square down to G6. This copies row 2 into rows 3 through 6.

Save your work. At this point, you should have the equivalent of file HOExcel_file2.xls.

Go to worksheet Value. In cell C2, enter + , then left-click on the ClosingPrice tab, and left-click on cell C2, enter + , then left-click on the Shares tab , and left-click on cell C2. Press enter.

Select cells C2 through G2. Drag the copy-box down to cell G6.

Select cell C7. Type ‘------ and hit enter (if you forget to type the single quote, Excel will try to evaluate this as a formula, and will decide you have an error. It will offer to add the single quote. You can click OK). Left-click on cell C7, and drag the copy-box to cell G7.

Left-click on cell C8. On the standard toolbox, click on the summation sign ? .Then press enter. This will create a Sum formula covering cells C2 through C7. Select cells C2.C7, and drag the copy-box to cell G7. Now, you have your total investment value for each period.


Toolbars and Options
I mentioned the standard toolbar. If you right click on any of the blank space next to the toolbar icons, you’ll get a list of toolbars you can display.



You can display the same list by doing View, Toolbars on the menu bar.

Click on Tools, Options to get a window with many of the configuration options. Several important ones you might want to change are: File Locations / Documents and General / Recently Used Files List (#)

Formatting
Let’s make the spreadsheet look good now.

First, left click on the Sheet1 tab. Now, double-click on Sheet1 – see how it selects (highlights) the word so we can edit it. Type ClosingPrice and hit the enter key.

Now, left click on the Sheet2 tab. Change it to read Shares. Change Sheet3 to read Value.

We’ve got stock prices as decimals, but in a general format – for example, if the number is a whole number, there are no decimals shown. We want to show the prices with 2 decimal places and a dollar sign. Left click on the ClosingPrice tab, and then select cells C2 through G6.


Charting & Graphing
Let’s create a pie chart of our investments below the value table. This way, we can eventually print the values and the graphs on the same page.

First, let’s add the stock symbols into cells A2.A6 – these values are ALB, XOM, MSFT, AAPL, and IBM.

Go to cell B10. From the menu bar, Insert, Chart, select Pie, and take the default flat, un-exploded pie chart. Press Next. Enter the Data Range as G2.G8, click on the Columns radio button, and then select the Series tab. For the “Category Labels” field, click on the selection box at the right side of the field. Highlight cells A2.A6 in the spreadsheet (notice they are then entered automatically in the Chart Wizard Step 2 of 4) and press the return box at the right side of the Chart Wizard.

Click Next. Click Show label and percent, and click Next. The default puts it here in this spreadsheet, and that’s good. Click Finish. Now, drag the graph where you want it to be.


Now, let’s make a chart showing the change over time. Insert, Chart, select Area as the type, and the default Area chart. Next, enter data range c2.g6, and click Rows. Click the Series tab. Click the Category (X) axis labels wizard button. Highlight C1.G1, and press the wizard return button. In the Series box, click Series1, and in the Name field, enter ALB. Click Series2 and enter XOM. Click Series3 and enter MSFT. Click Series4 and enter AAPL. Click Series5 and enter IBM. Click Next., Next, Finish. Move the chart below the other chart.

Note that the chart adds labels for the intervening months and has straight lines between the data points we entered.

Printing the worksheet
Highlight the section you want to print, such as the Values table and the two charts. From the menu bar, select File, Print Area, Select Print Area. Then, you can click on the File, Page Setup to get a multi-tabbed menu. Here you can set margins, force the spreadsheet to fit on one page, set headers and footers, etc.

To preview the printed page, press the Print Preview icon, or File, Print Preview.

To print, press the Print icon or File, Print.

Saving the file
Don't forget to save your worksheet file. You might want to do that several times during your project. From the main menu, File / Save As. Then, move to the location where you want to save the file, enter the filename HOExcel1-file4.xls, and press Save.

You can save subsequent versions with File / Save or Control-S, although I like to add a dash and a counter to the end of the name. I've learned that it is better to have multiple, interim versions than to have one original and mess it up -- and have to start over.