Basic Skills for Excel
Formulas are where the real power is in spreadsheet analysis. It is relatively easy to create formulas that will solve intricate equations. Basic formulas combine constant values and simple operators (+, -, *, /) to calculate a new value from existing ones. More elaborate formulas include cell references, functions, and even text.
Here are some examples showing the "anatomy" of a formula.
To create a formula:
As soon as you type "=" the formula bar icons appear.
As you type the formula, it shows in the formula bar, and in the
target cell. You can create formulas either by typing cell
addresses or by using the mouse to click on the cells required in
the formula. After clicking on a formula cell, you must either
type a math operator, or <enter> to indicate that the
forula is completed.
If you want to multiply A2 x B2, after typing the =, type "A2*B2", then <enter> (don't type the quotes) or click on A2, type "*" (the multiplication indicator), click on B2 and <enter>.
The following table lists the operators recognized by Excel in the order that they are evaluated within a formula. The same order is recognized almost universally by all spreadsheet programs.
| Operator | Description |
| - | Negation (as in -10) |
| % | Percent |
| ^ | Exponentation |
| * and / | Multiplication and division |
| + and - | Addition and subtraction |
| & | Text joining |
| = < > <= >= <> | Comparison |
This order can be altered by using parentheses in a formula. To see how this works, try solving the following formulas. Then enter them into a blank worksheet. Write down the result and see how well you did.
| Formula | Your Result | Excel's Result |
| =4*3-2 | ||
| =4*(3-2) | ||
| =2^2-1 | ||
| =2^(2-1) | ||
| =5-4+6*6/2 | ||
| =5-(4+6*6)/2 |
The following table explains the errors you may see when something is awry with a formula. Most of these descriptions will make more sense as you learn more about spreadsheets later in the course.
| Error Value | Cause |
| #DIV/0! | Divided by zero |
| #N/A! | Different meanings depending on circumstance (usally means no value available or inappropirate argument was used) |
| #NAME! | Reference to an invalid name |
| #NULL! | Reference to intersection of two areas that do not intersect (e.g. if named areas January and Profits do not intersect, =January Profits returns #NULL!) |
| #NUM! | Incorrect use of a number (unacceptable numeric argument, such as SQRT(-1), or formula returns a number too large or too small to be represented in Excel |
| #REF! | Invalid cell reference |
| #VALUE! | Usually caused by incorrect argument(s) or operands. |
A circular reference is a reference that either directly or indirectly refers back to itself. Such references are generally created by accident. Suppose you are using the SUM() function to add a column of figures, and when you designate the range, you include the cell containing the formula. Instant circular reference. You will see a dialog box indicating that you have created a circular reference. You will need to click OK to continue. The formula containing the circular reference will evaluate to zero, and there will be a note on the status bar indicating the cell containing the circular reference. Fix circular references as they happen. Later, they can be hard to find.
References to other cells of the worksheet are what make a formula really powerful. As mentioned previously, references are based on the column and row headings in a worksheet. You can also refer to a range of values by using the references of the first and last cells in the range. For example, A1:B12 would refer to the range of cells between (and including) A1 and B12).
The following excerpt from the Microsoft Excel documentation describes the difference between the three types of references.
There are three types of references: relative references, absolute references, and mixed references.
Relative References: A reference such as A1 tells Microsoft Excel how to find another cell, starting from the cell containing the formula. Using a relative reference is like giving someone directions that explain where to go from where that person starts - "go up two blocks and over one."
Absolute References: A references such as $A$1 tells Microsoft Excel how to find a cell based on the exact location of that cell in the worksheet. An absolute reference is designated by adding a dollar sign ($) before the column letter and the row number. Using an absolute reference is like giving someone a street address - "3812 Atlantic Street."
Mixed References A reference such as A$1 or $A1 tells Microsoft Excel how to find another cell by combining a reference of an exact column or row with a relative row or column. A mixed reference is designated by adding a dollar sign ($) before either the column letter or the row number. For example, in the mixed reference $A2, the column reference ($A) is absolute and the row reference (2) is relative. Besides typing mixed references directly, you can select the reference in the formula or place the insertion point within the reference and press F4.
Keep the differences between reference types in mind as you create formulas. As we will see later, when you get ready to copy or move formulas, the reference type will make a big difference in the way the result is calculated.
When writing formulas, keep in mind that you can also use values from another sheet in your calculations. To do this, the reference must include the sheet name. For example, to refer to cell A1 in Sheet4, use the cell address Sheet4!A1. Note that the sheet name is separated from the cell reference by an exclamation point.
Sharing data between workbook files is another powerful feature. It is fairly easy to make references between files, and once those references are made, when one file changes, the other file can be updated automatically. The easiest way to enter these references is by actually opening the second file and pointing to the cell or range of cells you want to use. More on this later.
Functions are at the core of the powerful formulas that we build into spreadsheets. Functions are simply shorthand for complex mathematical process. For example, we have already introduced the AVERAGE and SUM functions. The SUM function is a quick way to add a group of numbers together. Instead of typing the formula =A1+A2+A3+A4+A5+A6+A7+A8, you can simply use the formula =SUM(A1:A8). The AVERAGE function calculates the average of a range of numbers. =AVERAGE(A1:A8) is the same as =(A1+A2+A3+A4+A5+A6+A7+A8)/8. Combining functions and range names allows for easy to read, powerful formulas that can be updated and traced simply.
When you use a function, you must type the name
of the function, followed by parentheses. The arguments
for the function are contained in the parentheses, and may be
either cell addresses or constants, depending on the funtion.
Excel provides a function wizard to
guide you through the process. 
is the icon that calls the function wizard. When you
click the icon, you enter a dialog box shown below:
Functions are arranged by category. If you have used a function recently, it will be in the "Most Recently Used" group. Highlighting All will show you all functions in alphabetical order (there are several hundred). Most of the functions you need will be found in the Financial, Math & Trig or Statistical sections.
Pick the name of the function in the right box, and click the NEXT button. You will be led through the process.
Here's an example of calculating the monthly payment on an installment purchase. Start with the following spreadsheet. Note that the cell to contain the answer has been made active and the "=" has been typed.
Highlight Financial in the left box and PMT in the right box.
Click the NEXT button, and the dialog box shown below appears.
The cursor will be in the rate box. Click on cell B3 in the spreadsheet and it will show up in the rate box. However, because the payments are monthly, type "/12" to divide the rate by 12, converting the yearly percentage to a monthly percentage. <TAB> to move to the nper (number of periods) box, and click on cell B4. <TAB> to pv (present value), which is the same as the principal amount, and click cell B2. You can get an explanation of each item by clicking the HELP button in the dialog box. If you make a mistake, just click on the appropriate box and make the changes. You can correct an error in a previous box by clicking BACK. Fv and type are optional. Leave them out for the example.
When you have completed the dialog box, click the FINISH button. The completed dialog box should look like this. Also note that as you complete the dialog box, the information is being entered in the formula bar.
Important Concept: Do not enter the principal, number of payments, or interest rate into the dialog box or its resulting formula. If you do so, the formula will work only for that combination. Click cells instead. Then, you can enter new data into the spreadsheet and play "what if" to see the effect of changing various items on the monthly payment.
After you click the FINISH button on the dialog box, your spreadsheet should look like this:
Note that the formula is present in the formula bar and in the target cell. Next, <Enter> or use an arrow to move the active cell. The completed work looks like this:
The payment is $314.68 per month. It's shown in red because it represents a minus to your bank account. If you like, you can change it to a positive number by using the ABS() function.
Beyond Wizards: Wizards are like training wheels on a bicycle. They're great, but soon you outgrow the need to use them. Once you become familiar with the functions you use regularly, just type the function and click the necessary cell addresses.
Following is a listing of some common functions and their descriptions. The functions are grouped by category. This list was adapted from the Microsoft Excel on-line help.
DATE Returns the serial number of a particular date
DATEVALUE Converts a date in the form of text to a serial number
DAYS360 Calculates the number of days between two dates based on a 360-day year
NOW Returns the serial number of the current date and time
TIME Returns the serial number of a particular time
TIMEVALUE Converts a time in the form of text to a serial number
TODAY Returns the serial number of today's date
CONVERT Converts a number from one measurement system to another
PMT Returns the periodic payment for an annuity
PV Returns the present value of an investment
AND Returns TRUE if all its arguments are TRUE
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
TRUE Returns the logical value TRUE
HLOOKUP Looks in the top row of an array and returns the value of the indicated cell
OFFSET Returns a reference offset from a given reference
VLOOKUP Looks in the first column of an array and moves across the row to return the value of a cell
ABS Returns the absolute value of a number
COS Returns the cosine of a number
COUNTIF Counts the number of non-blank cells within a range which 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
FLOOR Rounds a number down, toward zero
INT Rounds a number down to the nearest integer
LN Returns the natural logarithm of a number
LOG Returns the logarithm of a number to a specified base
PI Returns the value of Pi
RADIANS Converts degrees to radians
RAND Returns a random number between 0 and 1
ROUND Rounds a number to a specified number of digits
SIN Returns the sine of the given angle
SQRT Returns a positive square root
SUM Adds its arguments
SUMIF Adds the cells specified by a given criteria
TAN Returns the tangent of a number
AVERAGE Returns the average of its arguments
COUNT Counts how many numbers are in the list of arguments
COUNTA Counts how many values are in the list of arguments
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
QUARTILE Returns the quartile of a data set
RANDBETWEEN Returns a random number between the numbers you specify
RANK Returns the rank of a number in a list of numbers
STDEV Estimates standard deviation based on a sample
LEFT Returns the leftmost characters from a text value
MID Returns a specific number of characters from a text string starting at the position you specify
RIGHT Returns the rightmost characters from a text value
TEXT Formats a number and converts it to text
TRIM Removes spaces from text
VALUE Converts a text argument to a number
More information about each of these functions can be found in Excel's online help function. Just search for help using the function name as the search parameter.
If you learn just a few of the functions and how they work, you will be able to figure out just about any of the rest of the functions. The following are among the most common functions that are used on a day-to-day basis. They work almost exactly the same way no matter what spreadsheet program you are using (Excel, 123, Quattro, etc.). Some of the functions in this table may not be discussed in detail during lecture. You should try each of them on your own, making any notes that you need to help you remember what they do. You will be responsible for any and/or all of these on the exam.
| Function | Usage Notes |
| ABS | |
| AVERAGE | |
| COUNT | |
| INT | |
| LOG | |
| MAX | |
| MIN | |
| PI | |
| SQRT | |
| STDEV | |
| SUM |
There are also a few other functions that we will discuss in detail in class. They will be mentioned later in the handout material.
Printing spreadsheets is a relatively simple task in Excel. Assuming you have a printer configured for Windows, you should be able to print just about anything that you can create.
What do you want to print? Seldom do you want to print your entire spreadsheet. Select the area you want to print, then click File -- Print Area -- Set Print Area. The area you selected will be set up for printing.
How do you want it printed? Click File -- Page Setup.
From the PAGE tab, you can choose landscape vs. portrait printing, or adjust the size of printing by scaling to a percent of normal size. Choosing the Fit To radio button if you want to fit the output to a specific number of pages.
From the MARGINS tab, you can set the top, bottom, left or right margin, or center your work horizontally or vertically on the page.
The Header/Footer section allows you to set up headers or footers. If you do not want a header or footer, scroll to the top of the selection and choose None.
The Sheet section will show the print area you have chosen. In addition, if your spreadsheet is large, you can choose headings or columns to repeat on each page. In the Print section, you can choose to print with or without grid lines (check the box if you want them.) Page Order indicates how printing proceeds if the job is too long or too wide to fit on one page.
Print Preview: You can always see how your work will look on the page
by clicking 
.
It brings up a dialog box that allows you to access most of the
items on the Page Setup menu. Click the Margins button, and you
can reset the margins and resize contents by clicking and
dragging.
Selecting the printer: Only one printer is available in the lab. In other situations, there may be several. Clik File -- Print, and make your printer selection.
Printing individual pages, multiple copies, etc. Click File -- Print and make the appropriate choices.
As with word processing documents, the appearance of your spreadsheet often determines whether someone will take the time to look at it in detail. There are innumerable ways to format spreadsheets in Excel. You can change fonts, alignment, patterns, borders, and the way that numbers are displayed. The two-step process that you use to format part or all of a spreadsheet is:
Formatting commands are found in the formatting menu. The Format - Cells command brings up a dialog box that contains the vast majority of the formatting options. Use the on-line help feature of Excel for help on specific formatting commands.
Format Painter: The format painter icon 
allows you to
copy formatting from one cell or group of cells to another. To
use the format painter, select the cell(s) whose format you want
to copy. Click on the format painter icon. Then click on the cell
where you want the format to be applied.
AutoFormat: Selecting a range and choosing Format - AutoFormat will bring up a dialog box that is full of preset formatting schemes. This is often a good way to get started with formatting. Once you have selected one of the autoformats, you can customize the results to your own taste.
Tips for Mastering Excel.