home

=Welcome to the TAMU-C MIS Excel Wiki!= The purpose of this wiki is three-fold. First of all, it will give you experience with a widely-used Internet tool, the wiki. Secondly, it will address questions or problems you may have with the Excel Application Exercises we will be working on in this class. Finally, it will provide you with an opportunity to get your questions answered quickly, by either your instructor or a classmate. In collaborating, you will learn from each other.

include component="editors" days="30" imageSize="small" showUsername="true"toc

=Excel Fundamentals= An Excel file is called either a Workbook. Each workbook contains multiple worksheets. The worksheet is where you will see a grid with columns and rows where you enter and summarize data. Excel 2010 contains the familiar Ribbon menu, much the same as you find in Microsoft Word. Each of the spaces where a column and a row intersect is called a cell. These cells are usually referred to by their grid location, such as A2. This cell would be in Column A, Row 2. By using cell addressesyou have found the true power of a spreadsheet. If you need to refer to several cells in a series, for example, if you want to find the total of a series of numbers, you would refer to that series of cells as a [|range]. A range can be represented by a name or by the cell references. A2:A13 indicates a range of cells starting at and including cell A2 through and including cell A13. = = = = = = =The VLookup Function and Absolute Cell References=

The VLookup function allows one to 'look up' data on one worksheet (or even from another spreadsheet) based upon data in another worksheet. Your assignment asks for a combining of the data from two different worksheets. As you open the file, you will see 2 tabs; one for Customers and the other for Miles Flown. Rather than having to switch between the two and not have the customer's name, but rather their number, you are to essentially 'move' the Miles Flown data to the Customer worksheet using the VLookUp function to insure that you put the correct 'Miles Flown' value with the correct customer.

Your assignment also references 'absolute cell addresses.' In a spreadsheet application, we refer to the cells by letter and number. The letters represent the column and the number represents the row. Therefore, if we refer to the value of cell B2 for example, we are referring to a number or word that is actually stored in cell B2. In your first assignment, you were to total the values in a //range// of cells. In the first column of that worksheet (assignment from Chapter 2), you wanted the SUM of the range of cells B2:B11. That formula is read =SUM(B2:B11) or add the values of the cells from and including B2 through and including B11. The total of those values is displayed in the cell in which you typed the formula.

Remember copying that formula across to the other columns? As you did so, the SUM function //changed relative// to the column into which you pasted the function. So, when you copied =SUM(B2:B11) and pasted it to cell C12, the function automatically changed to =SUM(C2:C12)... relative to the column you're now in (C). This is a great help when you are working with large worksheets and want to use the same function across many columns or rows. However, there are times when you need the value of cells to NOT change relative to the row or column they are being copied to. Such is the case in this exercise. Your instructions say to use absolute cell references ; that is, to use the same cell address no matter where you copy the function.

To use absolute references, you simply use the $ in front of the column or row (or both) of the cell reference you do not want to change. For the Vlookup function, you can enter the table references and press F4 to automatically insert the $ for an absolute cell address.