Starting with Excel
Open the Excel Spreadsheet “Dwarves_Income.xls”. If
you haven't already downloaded the necessary Excel files, open a browser and
download "Excel 2 " from
http://www.csuchico.edu/stcp/workshops/download.
Save the file to your Desktop. On the Desktop, double-click "2001_Excel_1.exe"
to install the files.
Now, you are ready to begin using Excel!
1. Go to File in the Menu Bar and pull-down to
Open. An Open
window pops-up. At the top in the "Look in:" field is the current directory
(also called a folder) that you are in.
2. Click on the pulldown menu next to the current directory
name (also called a folder).
From there, choose the C: drive.
3. Now, double-click on the "My Documents" folder and then
double-click on the "STCP_Tutorials" folder.
Inside is where the file "Dwarves_Income.xls" is located.
4. When "Dwarves_Income.xls" is found,
double-click it to open it.
I. Defining functions & formulas
a. Summary of functions & formulas
A formula is an equation that performs operations on worksheet data. Formulas can perform mathematical operations, such as addition and multiplication, or they can compare worksheet values or join text. 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.
Typically, a formula looks something like the following: =SUM(C3:C6). Formulas and/or functions are always preceded by an “=” sign. This is to avoid confusion about whether you wanted to type in data or an equation into a cell.
A formula can also refer to constant values and to other cells. The cell that contains the formula is known as a dependent cell when its value depends on the values in other cells. For example, in “Dwarves_Income.xls,” cell C8 is a dependent cell if it contains the formula =SUM(C3:C6).
Whenever a cell that the formula refers to changes, the dependent cell also changes, by default.
1. Change DOC's $400.00 to $1000.00.
Notice how the TOTALS beneath it change? If you use constant values in the formula instead of references to the cells (for example, =30+70+110), the result changes only if you modify the formula yourself.
2. Change DOC's THIS YEAR income back to $400.00.
b. Inserting functions
There are typically three ways to insert functions into a spreadsheet.
1. Autosum
a. Click on cell D8. It should now be highlighted.
b. Click the Autosum button in the Toolbar above. Notice that the cell now says "=SUM(B8:C8)."
c. Select the correct range of cells. Click on D3 and drag your mouse down to D6. Notice how the range in the summation formula appears as you click and drag your mouse over the selections.
d. Hit Enter on the keyboard when finished. This will add the selected cells together. At this time, the sum of the cells is zero, so a placeholder of "-" holds the space.
2. Paste Function
Use the Paste Function to insert a summation equation.
a. Click on cell D8. Click on the Copy Function in the Toolbar.
b. Click on cell E8. This is the TOTALS for the CHANGE column.
c. Click the Paste Function button in the Toolbar.
d. Press Enter on your keyboard.
e. At this time, the sum of the cells is zero, so a placeholder of “-“ holds the space.
3. Manually Entered Formulas
Sometimes, it's simply necessary to insert your own formulas into the cells. In cases like this, you need to decide whether it will be a range of data or individual cells that you wish to select.
Ranges vs. Individual CellsRanges are when multiple adjacent cells are selected. We used a range of cells when we used the Autosum button. Ranges are usually expressed as “D3:D6”. The “:” denotes that all the cells from D3 to D6 (inclusive) are selected.
At times, you only need the information from one cell pitted against the information in another cell. In these cases, they don't have to be adjacent, and this works well for simple operands such as addition, subtraction, multiplication, or division.
a. Click on cell E5.
b. Type in the following: =C5-B5
c. Hit Enter on the keyboard.
II. Understanding relative vs. absolute cell referencing
When we copied the formulas above using the Fill Down feature, it relatively copied the function. This means that every time we move one row down, the formulated cells move down one row, too. This is what makes Excel easy.
Sometimes, copying formulas doesn't work so well and Excel will return errors
instead of results. When this happens, it might be worth looking at the guts of
the formula to determine what went wrong.
1. Click on cell D3 and begin typing the
formula: =C3/C8
2. Press Enter on your keyboard.
So far, this formula is working correctly. We see the decimal result, which we can later turn into a percentage. But what happens when we try to Fill Down with this formula?
3. Highlight the range of cells D3:D6.
4. Go to the Edit menu again and pull-down to Fill . Within the Fill menu, choose Down.
#DIV/0 is the result that we see. This error stands for “Divide by Zero Error.” In cases like this, we need to determine what the problem is, especially since the first formula in cell D3 works, but the subsequent ones do not.
5. Double-click on cell D3.
Notice how the respective cells used in the formula are highlighted, helping you find which cells are being referenced: C3 and C8.
6. Press the Esc key on your keyboard. (This cancels any changes that might have accidentally occurred while in the editing mode.)
7. Now double-click on cell D4.
Do you see how C4 is the correct cell we want for the nominator, but cell C9 is
NOT the correct cell—In fact, there's nothing in C9! What happened?
When we Fill Down , we were copying the formula relatively, so as the cells
move down, four rows would always separate the top number and the bottom
number.
However in this instance, we want cell C8 to be our constant denominator. What
this means is that we will have to find some way to anchor cell C8 to stay in
place while the nominator moves accordingly. This is called absolute cell
referencing.
To anchor cell C8 into place, we need to retype the original formula.
1. Go back and single-click on cell D3.
2. Type in this function instead: =C3/$C$8.
The “$” anchors the column and row so that when it is copied anywhere else, it will always refer to C8 appropriately. An easy way to remember this is to say, “I'm paying money ($) to the cell (C8) to stay where it is!”
3. Now, highlight cells D3:D6 again and Fill Down as before.
The #DIV/0 error should be replaced by the correct decimal numbers.
III. Create charts and graphs using your data
a. Select the data
Highlight all of the data for LAST YEAR, THIS YEAR, % TOTAL and CHANGE, along with the labels for the Dwarves' (cell range A2:E6). This includes all of the Dwarves' names, their LAST YEAR, THIS YEAR, % TOTAL and CHANGE data values. Be sure to exclude the TOTALS from this selection.
1. Click the Chart Wizard button in the Toolbar.
This leads us into selecting and choosing different chart options for our data.
b. Choosing the best chart
Now that we're ready to select a chart, we need to determine which one will best suit our needs. Column charts are common and easy to read. Bar charts evaluate information and are useful for monetary comparisons. Pie charts are great for percentages, and line charts show progress effectively. Experiment with the different types available to see what works best for you.
1. For the Chart type: select Bar.
2. In the Chart sub-type: choose the first one in the first row.
3. Click on the Next > button.
4. Be sure that the Series selected is Rows and not Columns.
Observe how changing between rows and columns changes the way the information is charted.
5. Click the Next > button again.
6. Skip this section of additional information by simply clicking on the Next > button once more.
7. Select As new sheet: and leave the chart named “Chart1”.
8. Click Finish .
You should now be looking at the chart you just created. This chart is a worksheet all of its own. Looking at the bottom of your screen, you can see the tabs for our Sheet 1 sheet and for this new chart, “Chart1.”
c. Switching to a different chart
Once a chart is created, you can still change your mind about what type of chart you want to use.
1. To change the current bar chart to be a column chart, start by single-clicking in the gray area of the charted data.
2. Right-click with your mouse in the same area.
3. From the pop-up menu, select Chart Type.
This should look familiar to you, since this is where we first started when building the chart.
4. Select Column from the Chart type.
5. Under Chart sub-type: , select the first one in the first row if it isn't already highlighted.
6. Click OK at the bottom of that window.
The changes are immediately applied without having to scrap the chart and start over.
d. Select the data (with a twist)
1. Go back to your data by clicking on the Sheet 1 tab at the bottom of your spreadsheet.
2. From here, highlight the labels for the Dwarves' (cell range
A2:A8). This includes all of the Dwarves' names data values.
Now, we're going to include some data that is farther away, but since we want
to include it in our chart, we need to select it as well.
3. While the names are still highlighted, press and hold the Ctrl key on the keyboard.
4. Keeping the Ctrl key depressed, move your mouse to highlight the range of cells from % TOTAL (D2:D8).
5. Now, by looking at your screen, you should see that you have two separate pairs of information that are highlighted: The Dwarves' names and the % TOTAL column data. Let's build another chart with these.
6. Click the Chart Wizard button in the Toolbar.
Refer back to section B, "Choosing the Best Chart" in this document and make this chart a Pie Chart. Leave the data Series In: Columns. Select As new sheet.
e. Customizing the chart
Excel allows you to customize charts by changing colors, sizes, textures and even viewing angles. Every section of the chart is a different component, so take care when selecting areas you want to modify. Remember, if you did something that you didn't mean to do, you can always reverse errors by going to the Edit menu in the Menu Bar and pulling down to Undo .
We're going to enhance a few items on our chart: Change the color and texture of one of the bars, and add data values to the charts.
Chart #1
1. Go back to “Chart1” by clicking on its tab at the bottom of your spreadsheet.
2. From here, double-click in the middle of the cyan colored bar to change its formatting properties.
Currently, we are looking at the Format Data Series window. We should be under the Patterns tab.
3. Find the Fill Effects button in the window and click on it.
4. Click on the Texture tab located at the top of this new window.
5. Select any texture from the palette by single-clicking on it and then clicking on OK with your mouse.
We're back at the Patterns tab under the Format Data Series window.
6. From here, simply click on OK.
The former cyan colored bar is now replaced with whatever texture you chose from the texture palette.
7. Do all this once more with the magenta bar.
Chart #2
Moving to “Chart2” by clicking on its tab at the bottom of your spreadsheet, take a moment to notice that exact percentages would be hard to evaluate at this point.
We can fix this dilemma by adding the values to this pie chart.
1. Take care to single-click somewhere in the middle of the cyan wedge.
2. In the same cyan area, right-click with your mouse to bring up the pop-up menu.
3. From here, select Format Data Point.
From this window, we can change a variety of items, such as the patterns, the axes information, labels, and rotation.
4. Go to the Data Labels tab and check the box next to Percentage
5. Click OK.
Now, you can see that the information displayed is accompanied by the appropriate percentage value
IV. Creating Flow Charts and Text Boxes
a. Open the Drawing Toolbar.
1. Click View->Toolbars->Drawing
2. Now the drawing Toolbar should be visible at the bottom of the screen. If you don't see it, make sure that "Drawing" has a check next to it under the Toolbars menu.
b. Add flow chart shapes to the spread sheet.
1. Click AutoShapes->Flowchart->Alternate Process (second shape from the upper left).
2. Notice that many different shapes are available and when you leave your cursor over them for a second their type is displayed.
3. Click and drag the mouse to create shapes of various sizes. -Make two Alternate Processes (rounded rectangles)
4. Select one process by clicking on it and then select the other in addition by holding down <ctrl> and clicking the other one.
5. Right-click one of the shapes and select Format AutoShape.
6. A window will pop up with various options that will change the properties of both shapes that were selected.
7. Change the color to some color (blue is used in this example), and select Fill Effects from the Color drop down menu.
8. Select the first option under Shading styles, which is Horizontal. Click ok to close that window and then set the transparency to 25%. Click ok.
9. Now both shapes are colored with a shading style and are slightly transparent.
10. Next, add a decision shape by clicking AutoShapes->Flowchart->Decision (its the rhombus or diamond shape next to the rounded rectangle).
11. Following the same steps as before, add a different color to this shape.(Note: you can constrain the proportions of the shape you are making by holding shift down as you adjust the size)
12. Now your shapes should look something like this.
c. Insert text into each shape
1. Right click the top shape and select Add Text.
2. Now we can type whatever content we want into the shape.
3. Just like in Word, we can change the properties of the text by selecting it and changing the settings on the top tool bar, such as font, size, and color.
4. You can also edit the text properties by selecting the text and then right clicking it and selecting Format AutoShape
5. Add text to each shape (In this example I am entering "Asleep" in the first shape, "Wake?" in the rhombus, and "Awake" in the bottom Process or rectangle).
6. To change the text you have already set, simply click the text in whichever shape you wish to edit.
7. To change the properties of the shape now that we have text in it, you have to right click the shaded area surrounding the shape, and not inside the shape itself.
8. You can tell you are selecting the right area if the cursor changes to the four-directional shape. You also move the shape by left clicking this area and dragging it.
9. Select all the shapes using <ctrl> left click and then right click one of the shaded areas around one of the shapes and select Format AutoShape.
10. Now we have an alignment tab since we added text into each shape. In this example the horizontal and vertical are set to center.
11. Your shapes should now look like this.
d. Add connectors to link the shapes to each other
1. Click AutoShapes->Connectors->Straight Arrow Connector.
2. You can connect shapes by clicking one of the four points on a given shape and dragging the line to another point on another shape. (Note: The four points only appear once you hover over a shape).
3. You can put connecting arrows anywhere on the chart, but if you want them to "stick" to a shape, you need to make sure you select one of the tiny squares that appears on the shape.
4. You can change the type of connector by selecting the connector first and then right clicking it and then selecting a different type of connector such as elbow or curved.
5. To distinguish the two connectors coming from the decision shape, create two rectangles and set their fill transparency to 100% and their line color to no line.
6. Now we can position the text boxes next to their respective connecting lines which should make sense given the names of the states which they go to.
7. Finally we can use the Print Preview to see what our final flow chart will look like when printed.
8. For additional shapes to use select AutoShapes->More AutoShapes... A ClipArt window will open up on the right hand side of the screen. These shapes can be edited just as we have done with the previous ones.
Additional resources:
For additional resources on Excel, access to the Microsoft e-Learning Library is available at this link:
http://mantis.csuchico.edu/login?url=http://etraining.csuchico.edu