Excel Tutorial - Henderson State University



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.

[pic]

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.

[pic]

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.

[pic]

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.

[pic]

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.

[pic]

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.

[pic]

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 (=).

[pic]

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)

[pic]

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.

[pic]

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.

[pic]

Now, on the menu bar, click on Edit, then choose Fill.

[pic]

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.

[pic]

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.

[pic]

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.

[pic]

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.

[pic]

From that menu, choose Format Cells.

[pic]

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.

[pic]

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

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download