Excel Tutorial

Excel Tutorial Excel is a spreadsheet. That means that it is a software package that can be used to do a lot of mathematical calculations in table format. It can also do a number of other things. In the course of the worksheets for this course, we will use Excel for a number of things. We will use it for practice problems, explorations, and projects. We will start off looking at some of the basics of Excel. At the top of Excel you will see the menu bar. The menu bar contains many of the same menus as do other Microsoft programs. Just below the menu bar, you will see toolbars. Exactly what you see on the toolbar may vary, depending on how your particular computer has been set up. Again, many of the items on the tool bars will be the same as in other Microsoft programs. Now on to some things that are more Excel specific. The main units for work in Excel are the "cells." These are the boxes that in which you will enter your data or text. Each cell has an "address" made up of a column letter and a row number. The one at which the arrow is pointing in this picture is cell A1. The cell addresses are important for finding data and for doing calculations. The column names start with A, B, C, and on to Z. The next is AA, then AB, on to AZ. Then follow BA, BB, and so on until we finally get to column IV. That is a total of 256 columns. The rows range from number 1 to number 65536. That gives us a total of 16,777,216 cells on each work sheet. The line below the tool bars is called the command line. The command line shows the things you will type in cells. Each Excel worksheet contains three or more "sheets." When the program opens, you will generally be on Sheet 1. You can move from one sheet to another using the "tabs" at the bottom of the Excel screen. Entering Data Entering data or text into a cell is simply a matter of clicking on the desired cell and typing the desire input. You can always use the tool bars to make numbers or text in bold type or italics or various sizes. The place where Excel comes in really handy is in doing arithmetic that you may find of interest with such a chart. For example, suppose you wanted a column to the right of Dave that shows the total cars sold by the four salesmen in each month. Putting a heading on that column for Monthly Sales and click on cell G4, where we would want the sales total for January. Any time you want Excel to do a calculation, you will start by typing an equal sign (=). Then, you want to tell Excel what calculation you want done. In this case, we want the data in cells C4, D4, E4, and F4 added together. There are several ways to do this. One would be to simply type "=C4+D4+E4+F4." For short sums like this one, that is not a bad way to go. However, suppose we wanted to add up numbers from 25 columns. Typing in the address of each individual cell would be very tedious and time consuming. Instead we can use some built in functions. Since we are interested in adding the data values, the command "sum" will be the one we want. The syntax for the command, when wanting to add a group of cells from a particular row or column is as follows. We want to add all of the entries in row 4 from column C to column F so we type =sum(C4:F4) Excel has many, many built in functions. We will look at others as we make our way through these exercises and tutorials. A discussion of some of the more common ones is at the end of this tutorial. Notice that Excel highlights the cells you are referencing. Next, hit enter and you will see the sum. What we want to do next is the same thing in row 5, then row 6 and row 7. There are two ways to do that. The most obvious way is to go to cell G5 and type "=sum(C5:F5)" and repeat the process in rows 6 and 7. That will definitely work. Again, however, Excel has a way that makes it easier when you want a sequence of cells to have commands identical (except for row or column address) to that of another cell. Click on the cell containing the formula you want to replicate. In this case, that is G4. Hold the shift key down and use the down arrow to highlight the other three cells in which you want the identical formula used. Now, on the menu bar, click on Edit, then choose Fill. We want to fill the cells down from the starting point so choose Down. Once you do that you will see the sums materialize in each cell. The "Fill" command takes whatever is in a cell and copies it to the highlighted cells. If it is a formula, "Fill" will adjust the formula to apply it to the various rows or columns occupied by the highlighted cells. If you were to click on any of cells G5, G6 or G7 and look at the command line, you would see the formulas for each row. As practice, see if you can put commands in row 8 that will allow you find the total sales by each of Allan, Bob, Charlie and Dave, and the total of the monthly sales for the four months. You should end up with something like this. Notice three buttons on the right hand side of the second tool bar. The first button allows you to put borders around cells. The second allows you to color the cells. The third allows you to change the text color. Another item that will also be helpful is another means of formatting cells. If you right click on a cell, a menu of options will come up. From that menu, choose Format Cells. From this window you have a number of options for doing different things to cells. "Border" will allow you to put borders of varying thicknesses around cells. Experiment with these as well. You can do a wide variety of things to make your spreadsheets look more attractive. Notice on our spreadsheet, the words "Monthly Sales" do not fit in their column. Move the cursor on to the line between column headings G and H. It should change into a cross-like shape. Click and hold the left mouse button. Slide that line to the right to widen the column. Putting all of this together with some other formatting from the tool bars gives something like this. This is enough to get you started in Excel. We will learn more things as we make our way through the book. SOME COMMONLY USED EXCEL FUNCTIONS There are a number of functions in Excel that will be of use to you. Following are some of those. We may not use all of these in these exercises but all can be helpful. The descriptions for the functions are directly from Excel's Help feature. Information COUNTBLANK Counts the number of blank cells within a range Logical AND Returns TRUE if all its arguments are TRUE - Example - AND(a1=5,b2=4) FALSE Returns the logical value FALSE IF Specifies a logical test to perform NOT Reverses the logic of its argument OR Returns TRUE if any argument is TRUE - Example - OR(a1=5,b2=4) TRUE Returns the logical value TRUE Lookup and Reference TRANSPOSE Returns the transpose of an array Mathematics and Trigonometry ABS Returns the absolute value of a number ACOS Returns the arccosine of a number ASIN Returns the arcsine of a number ATAN Returns the arctangent of a number COMBIN Returns the number of combinations for a given number of objects COS Returns the cosine of a number COUNTIF Counts the number of nonblank cells within a range that meet the given criteria DEGREES Converts radians to degrees EXP Returns e raised to the power of a given number FACT Returns the factorial of a number LN Returns the natural logarithm of a number LOG Returns the logarithm of a number to a specified base LOG10 Returns the base-10 logarithm of a number MDETERM Returns the matrix determinant of an array MINVERSE Returns the matrix inverse of an array MMULT Returns the matrix product of two arrays PI Returns the value of pi RADIANS Converts degrees to radians RAND Returns a random number between 0 and 1 RANDBETWEEN Returns a random number between the numbers you specify ROUND Rounds a number to a specified number of digits SERIESSUM Returns the sum of a power series based on the formula SIN Returns the sine of the given angle SQRT Returns a positive square root SUM Adds its arguments TAN Returns the tangent of a number Statistical AVERAGE Returns the average of its arguments CORREL Returns the correlation coefficient between two data sets COUNT Counts how many numbers are in the list of arguments COUNTA Counts how many values are in the list of arguments FREQUENCY Returns a frequency distribution as a vertical array GROWTH Returns values along an exponential trend INTERCEPT Returns the intercept of the linear regression line LINEST Returns the parameters of a linear trend LOGEST Returns the parameters of an exponential trend MAX Returns the maximum value in a list of arguments MEDIAN Returns the median of the given numbers MIN Returns the minimum value in a list of arguments MODE Returns the most common value in a data set NORMDIST Returns the normal cumulative distribution NORMINV Returns the inverse of the normal cumulative distribution NORMSDIST Returns the standard normal cumulative distribution NORMSINV Returns the inverse of the standard normal cumulative distribution PERCENTILE Returns the k-th percentile of values in a range PERCENTRANK Returns the percentage rank of a value in a data set PERMUT Returns the number of permutations for a given number of objects QUARTILE Returns the quartile of a data set SLOPE Returns the slope of the linear regression line STANDARDIZE Returns a normalized value STDEV Estimates standard deviation based on a sample TREND Returns values along a linear trend