Excel

From TechHelp
Jump to: navigation, search

Contents

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.

Starting Excel

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 Excel icon.jpg 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.

The Research

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.

Gathering Data

Insert Function Menu

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.

ExcelNumberRange.jpg

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.


Rocky - Office Assistant If you fall in a ravine, call Lassie. If you have a problem in Excel, call Rocky

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.

ExcelFormulaAverage.jpg

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.

Creating Graphs

The chart wizard with the Standard Types tab selected.

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.








ExelChartWizard2.jpg
The next window that opens will ask you for the data that you want to enter. Simply click and drag across the area of your spreadsheet that you want the graph to represent. Don’t worry when the window disappears as you click and drag, it will reappear when you are done. For this example I created a graph of the colors per package 2. When you are done selecting your data go ahead and click Next.










ExelChartWizard3.jpg
The next screen that will appear have you label the graph, the X-axis, and the Y-axis. Once you are done labeling your graph go ahead and click Next.










ExelChartWizard4.jpg
The next window that appears gives you the option of creating a separate sheet for you graph, or to include you graph in the same sheet as your data. One neat thing about creating a graph as an object in a sheet is if you select the graph and change the data set you can see the graph change as the data does. For you first graph create it as a new sheet.


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 ExcelPrintingPagebreakPreview.jpg you will enter a new screen that will display the following pop up.


ExcelPagebreakPreviewWarning.jpg


Adjusting a Page Break
You will notice that your information is outlined in blue with a yellow break at the right hand side of your document.

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.


ExcelPageSetupMenu.jpg


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.

ExcelHeadersFooters.jpg

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.

ExcelPageSetupSheets.jpg

Common Menus

Edit Menu

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. ExcelSheetTabs.jpg

Worksheet Options
Each of these sheets can contain a separate spreadsheet or a chart. By choosing Edit>Delete Sheet you can say good-bye to the data that you have on the sheet you are looking at. Only use this option if you are sure that you want to delete a sheet. You can also do this by going to the bottom of the sheet and selecting the specific sheet that you want and right clicking this will bring up the following menu.


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

ExcelFormatCellsNumber.jpg
This menu gives you the ability to format the specific groups of cells, rows, and columns. We will start with the rows and columns. By selecting a specific row or column you can then choose to Format>Column or Row>Auto this will automatically fit all of the columns or rows you have selected to the largest column or row. You can also choose Format> Row>Height to specify a specific height that you want to maintain for a specific row. You can also Format>Column>Width to specify a specific width that you want to maintain for a specific column. When you go to Format>Cells you will get a menu that looks like this.






ExcelAlignmentCells2000.jpg
Formatting the specific numbers that appear in a group of cells can be helpful if you want to make sure that a specific date or currency format are used. By selecting the different types of numbers you can see how each number is formatted at this time, you can also change the format to a specific style that you would like. Once you have the numbers looking the way you want simply click OK and the numbers will change to fit you specifications. One hint make sure that you select the entire set of cells that you want to apply this to. The Alignment tab on the format cells menu looks like this.


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.

ExcelCellColor.jpg
The next tab that is useful in this menu is the Patterns tab. This tab lets you change the color of the cells that you have selected. Using two different colors between different sets of data can make reading the table easier. This also can be helpful to group specific data together that is similar. Simply choose your color and then click OK.


Textual Data and Sorting

Sort Warning Pop-up
One last function that you may find useful in Excel is the ability to sort textual information, such as students by last name. To do this you simply can enter whatever textual data you would like, such as student first and last names in separate columns. Then to sort the data here is what you will want to do. Select the column or row that you want to sort go to Data>Sort then you will get a warning like this.

Simply make sure that the expand selection button is pressed, and then click Sort this will bring up another menu that looks like this.



Data Sort Options
Since I have a header row that tells which column is the first name and which column is the last name I make sure to check the button that tells Excel My list has… Header row. For this example I chose to have Excel sort by Last Name in ascending order and by First Name in ascending order. Now I click OK and my list is sorted from A-Z by last name.

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.

Personal tools
Namespaces
Variants
Actions
Navigation
Technology Services
Toolbox
Wiki Help