Introduction to Excel
Excel is a spreadsheet program that can perform simple calculations, complex calculations, organize, and sort large amounts of information. Using the familiar tools available in the Microsoft Office suite, you will learn to perform the basic functions of Microsoft Excel. For this lesson you will be participating in some fact finding research.
To open Microsoft Excel go to the Start Menu at the lower left hand side of your screen. Now select Programs and look for the icon that says Excel next to it click on the icon. You should now have a blank excel document. As with any new file you are working with it is a good idea to save right away. Navigate to the menu bar and click File > Save. Select the folder in which you want to save your file. Name the file and click Save. Your file is now saved in the folder you specified.
Spreadsheets are setup with columns and rows. In most Excel spreadsheets, columns are labeled by letters (A, B, C, etc.) and rows are labeled with numbers (1, 2, 3, etc.). You will also notice that there are different sheets in the lower left hand corner of the document. This can be useful for creating spreadsheets that look across different sheets of data.
Using M&M candies and MS Excel, your task is to average the total number of colored candies that are in our packages and create a graph that displays the number of colored candies in each package. This exercise will expose you to a few of the basic tools available in Microsoft Excel.
First, we will set up our spreadsheet to represent the colors of M&M candies. Begin by moving the cursor (the thick plus sign) to cell A2 and click on the cell to highlight it and begin adding information. Add the different colors into A2 through A7. Now that we have our list of colors we want to click on cell B1 and type "Package One" to specify our first package Now we want to move to cell C1 and type "Package Two". Now, open your M&Ms and begin counting. Counting, not eating! Enter the numbers for each color in the cells that correspond to the color. Once you have filled in your cells for "Package One" enter the numbers for "Package Two". Now we want to add the total number of M&Ms from our package. To add the total for "Package One" move your cursor into cell B8 and click Insert > Function.
Note: There are a lot of functions that you can use for mathematical equations, as well as being able to sort text based entries.
Find the SUM function and then click OK. Another menu will appear so that you can enter the range of cells that you are going to add.
Excel takes a guess at what range you want to add together. You will already see in the section labeled Number 1 the list of cells you will be adding if you click OK, in this example it is all the cells between B2 and B7. Make sure that this looks correct for the cells you added data to and click OK. Now go ahead and do the same for column C. Now that you have the two totals we want to add them together so we can have an average of our total M&M colors and the average pieces of candy for the two packages.
In order to find the average of the two packages we need to find the equation for average. Click cell D2 so that we can place our averages in the D Column. Now select Insert > Formula and scroll down to find the formula for Average.
Note: For finding the correct formula you can use the Microsoft Office Assistant. By typing what you want to do in the search bar he will bring up a selection of formulas then by asking for more information you will receive an explanation of the math behind the formula.
To enter the values that you want to average simply click on the first cell and then drag to the second cell to average these two cells together. In this example I averaged the first color between the two packages.
As you work within Excel you will find that there are ways to have functions change in one area and then change in the corresponding area, if you choose to have this happen. This makes Excel very powerful when working with numbers. This is only a simple example of using Excels functionality.
Now that we have used some basic functions, how can we create a graph of our results? By clicking Insert > Chart this will start a Chart Wizard that will guide you through the chart creation process. The first window of the wizard will ask you what kind of chart you would like to create be it a bar graph, a line graph, area graph, or a variety of other graphs that can be displayed. After you have decided on the type of graph that is best for your data click Next.
Now take sometime to create the three graphs of the data collected, one for each package and one for the average of the two. Try creating your last graph as an object in your data sheet so you can see how the graph changes as the data changes. Let's take a look at some of the buttons and give you an example of how to sort text based information. Remember, there are a lot of similarities between all of the Microsoft Office products. Don’t be afraid to try something in one if you know how it works in another, they tend to have the same effect.
Printing your Data
When you are done collecting and sorting your data there is a good chance that you will want to print it out. One thing that often happens when you have a lot of data and a huge work area is that you print it once and it doesn’t look right. There is a feature that you can use by going to File>Print Preview that will help you solve this problem. Once you are in the print preview mode if you click on the button labeled Page Break Preview you will enter a new screen that will display the following pop up.
By clicking and dragging this yellow break you can change the amount of information that appears on the pages of your printed document. This can be quite handy when you are printing out a lot of information and want it to look a specific way.
You can change the orientation in the print preview menu. To do this simply click on the Set Up File:ExcelPageSetup.jpg button which will open another menu which will allow you to change a lot of different printing options. The first tab that you should see when you open the Set Up menu looks like this.
This menu gives you the basic set up as Word and PowerPoint do. The Header/Footer and the Sheet tabs are where you gain a lot of flexibility when you are printing.
The Header and Footer tab allows you to add information much like you would in Word since it is not an option in any of Excels menus. You can add a selection of preset fields into the header or footer using the pull down menus below each of the blocks, or if you click on custom header or footer you can add your own text or a variety of other automatically formatted text.
The Sheet tab also gives you some flexibility when you are printing, it is useful to creating charts that are easier to read. In this menu you have the option to print the grid lines on you copies, making the chart easier to follow. You can include the Row and Column headings on each sheet which is helpful to when looking at multiple sheets.
The Edit menu also adds a lot of Excel specific commands, though the basic cut, copy, and paste are still there. Since Excel treats each cell as its own text area there are a few options specific to working with cells the first is Edit>Fill. If you wanted to fill a set area with a number you would simply type the number in one of the boxes then go to Edit>Fill and use the correct fill direction to fill the cells with that number. You may also want to delete a large group of cells if you highlight the cells and press backspace it only deletes the contents of on cell. To delete a large group of cells highlight those cells and then go to Edit>Delete this will open a menu which asks how you want to shift the remaining cells around the deleted area choose the correct orientation and click OK. Another option you may want to use is instead of actually deleting the cells you simply want to clear the information from the cells and replace it. Highlight the cells that you want to clear and go to Edit>Clear> then pick what you want to clear. One selection that you want to be careful about using is the Edit>Delete Sheet option. If you look at the bottom of your spreadsheet you will notice a tool bar that has tabs that are labeled sheet 1, sheet 2 etc.
This will allow you to format the tab that identifies each sheet, as well as delete or move the sheet.
The Insert Menu
This insert menu gives you a couple of options that you may find useful when working in Excel. Using this menu you can Insert>Rows, Insert>Columns, Insert>Worksheets, and even Insert>Pictures. To insert a row you need to first highlight the row that you want to insert your new row. Move your cursor onto the number that designates the row that will be below your new row, click on the number to highlight that row, and then go to Insert>Rows. You can add more than one row at a time by highlighting multiple rows below where you want your rows to appear, and then go to Insert>Rows. To Insert a column move your cursor over the letter that designates the column that will be after your new row, click on the letter to highlight that column, and then go to Insert>Columns. You can add more than one column at a time by highlighting multiple columns to the right of where you want your columns to appear, and then go to Insert>Columns. Inserting a picture in Excel is that same as it was in Word and PowerPoint. Your picture will not appear in a specific cell, but will float above your spreadsheet.
The Format Menu
This tab allows you to change the way a cell appears in a data sheet. Some people like to have their titles at an angle so that you can read down the data sheet. This is where you would be able to do this by changing the angle of the text. You can also change the vertical and horizontal alignment of the text within the cells.
Textual Data and Sorting
Simply make sure that the expand selection button is pressed, and then click Sort this will bring up another menu that looks like this.
There are a lot of other things that can be done within Excel. If you ever get stuck on what you are trying to do go to the Help menu and type in the question you would like to ask. The help menu will let you search for ways to accomplish your goal. Also feel free to contact one of the BOCES ITS staff for additional help.