Download Excel Tutorial - Manuale su Excel and more Schemes and Mind Maps Business Management and Analysis in PDF only on Docsity!
Excel Tutorial to Improve Your Efficiency
Introduction
My purpose with this Excel tutorial is to illustrate some Excel tips that will dramatically improve your efficiency. I make no attempt to be as encyclopedic as some of the 800-page Excel manuals available. I concentrate on common tasks, not every last thing that can be done in Excel. Also, I presume that you have some Excel knowledge. For example, I assume you know about rows and columns, values, labels, and formulas, relative and absolute addresses, and other basic Excel elements. If you know virtually nothing about Excel, you probably ought to work through an “Excel for Dummies” book and then work through this tutorial.
The style of this tutorial should be easy to follow. Main topics appear in bold black type. Specific direction headings are in yellow, and these are followed by detailed directions in red. Additional comments about the directions appear in blue. Then there are “Try it!” exercises in green. These “Try it!” exercises are a key feature of this tutorial. I have embedded numerous sample Excel spreadsheets so that you can try out the directions right away—without switching into Excel. When you double-click on one of these spreadsheets, you launch Excel, and the spreadsheet “comes alive.” The menus and toolbars even change to those for Excel. By clicking outside one of these spreadsheets, you’re back in Word.
A few of the topics are best carried out on your own PC (as opposed to your school’s networked PCs), and I haven’t included sample spreadsheets for these. The reason is that they change the way a specific copy of Excel is set up. If you do one of these exercises on your school’s networked PCs, the chances are that they won’t take effect, at least not permanently, because of the way Excel is set up on the network. These topics have been placed at the end and are preceded by asterisks. Try them on your own home PC, where you have complete control.
The easiest way to maneuver around this tutorial is to switch to outline view. To do so, select Word’s ViewOutline menu item, which gives you an extra Outline toolbar. This toolbar has two dropdown lists. The right one, called Show Level, lets you choose the level of the outline. You’ll get good results by choosing Level 2. This lets you see all first-level and second-level headings. Put your cursor on a heading of interest and then switch back to normal view by selecting Word’s ViewPrint Layout menu item. (Try it right now. It’s easy!)
Finally, I suggest that you save this file–RIGHT NOW–as MyXLTutorial.doc (or some such name) and work with the copy. That way, if you mess anything up as you try the exercises, you can always go back and retrieve the original file ( ExcelTutorial.doc ).
Have fun!
Basic Spreadsheet Tasks
This section illustrates a number of ways to make you more efficient: how to select ranges, how to copy and paste, how to deal with absolute and relative addresses, how to insert and delete rows or columns, and a few others. Most Excel users know how to do these things, but they often do them inefficiently. So even though this material might seem elementary, read on—you might just pick up a few tricks you weren’t aware of.
Moving to the Top of the Sheet
Often you want to reorient yourself by going back to the “home” position on the worksheet.
To go to the top left of the sheet (cell A1):
Press Ctrl-Home (both keys at once).
Try it!
Down in the midst of the worksheet
Using End-Arrow Key Combinations
To go to the end of a range (top, bottom, left, or right):
Press the End key, then the appropriate arrow key. For example, press End and then right arrow to go to the right edge of a range.
Try it! Starting at a corner (a bordered cell), move around to the other corners.
8 1 1 1 5 5 7 6 10 5 3 10 10 10 5 9 5 4 3 4 7 4 5 1 2 8 9 10
The action of an End-arrow combination depends on where you start. It takes you to the last nonblank cell if you start in a nonblank cell. (If there aren’t any nonblank cells in that direction, it takes you to the far edge of the sheet.) If you start in a blank cell, it takes you to the first nonblank cell.
Splitting the Screen
It is often useful to split the screen so that you can see more information.
To split the screen vertically, horizontally, or both:
Click on the narrow “screen splitter” bar just to the right of the bottom scroll bar (for vertical splitting) or just above the right-hand scroll bar (for horizontal splitting) and drag this to the left or down.
Splitting gives you two “panes” (or four if you split in both directions). Once you have these panes, practice scrolling around in any of them, and see how the others react.
Try it! Split the screen either way and then remove the split.
6 3 7 13 9 7 2 5 6 11 1 3 1 2 9 6 4 9 13 1 4 4 2 7 2 4 2 2 8 6
Selecting Multiple Ranges
Say you want to format more than one range in a certain way (as currency, for example). The quickest way is to select all ranges at once and then format them all at once.
To select more than one range:
Select the first range, press the Ctrl key, select the second range, press the Ctrl key, select the third range, and so on.
For example, to select the ranges B2:D5 and F2:H5, click on B2, hold down the Shift key and click on D5 (so now the first range is selected), hold down the Ctrl key and click on F2, and finally hold down the Shift key and click on H5.
Try it! Select all three numerical ranges shown.
10 13 7 9 1 9 12 7 5 4 1 1 6 12 15 2
13 4 10
Copying and Pasting
Copying and pasting (usually formulas) is one of the most frequently done tasks in Excel, and it can be a real time-waster if done inefficiently. Many people do it as follows. They select the range to be copied (often in an inefficient manner), then select the EditCopy menu item, then select the paste range (again, often inefficiently), and finally select the EditPaste menu item. There are much better ways to get the job done!
To copy and paste using keyboard shortcuts:
Select the copy range (using one of the efficient selection methods described above), press Ctrl-c (for copy), select the paste range (again, efficiently), and press Ctrl-v (for paste). (If you practice
pressing Ctrl-c or Ctrl-v with the little finger and index finger of your left hand, it will become automatic in a short time.)
The copy range will still have a dotted line around it. Press the Esc key to get rid of it.
Try it! Copy the formula in cell C2 down through cell C8 using Ctrl-c and Ctrl-v.
3 3 9 4 1 2 3 2 1 5 1 4 2 3 5
To copy and paste using toolbar buttons:
Proceed as above, but use the copy and paste toolbar buttons (on the top toolbar) instead of the Ctrl-c and Ctrl-v key combinations.
Try it! Copy the formula in C2 down through cell C8 using the Copy and Paste buttons.
1 3 3 4 1 5 5 1 4 3 3 3 5 1 5
Buttons or key combinations? This is a matter of personal taste, but either is quicker than menu choices!
A frequent task is to enter a formula in one cell and copy it down a row or across a column. There are several very efficient ways to do this.
To avoid copying and pasting altogether, use Ctrl-Enter :
Starting with the top or left-hand cell, select the range where the results will go. (Use the selection methods described earlier, especially if this range is a long one.) Type in the formula, and press Ctrl-Enter instead of Enter.
Try it! Fill up the range C2:C8 with Ctrl-Enter.
Enter the formula in the top or left-hand cell of the intended range. Double-click on the drag handle.
This method uses Excel’s built-in intelligence, but it works only in certain situations. Let’s say you have numbers in the range A3:B100. You want to enter a formula in cell C3 and copy it down to cell C100. Since this is a common thing to do, Excel does it for you if you double-click on the drag handle. It senses the “filled-up” range in column B and guesses that you want another filled-up range right next to it in column C. If there were no adjacent filled-up range, double-clicking on the drag handle wouldn’t work.
Try it! Copy the formula in C2 down through C8 by double-clicking the “drag handle.”
7 8 56 2 1 10 2 4 1 4 5 3 8 7 8
Copying and Pasting with the Special/Values Option
Often you have a range of cells that contains formulas, and you would like to replace the formulas with the values they produce. Usually, you paste these values onto the copy range, that is, you overwrite the formulas with values. However, you could also select another range for the paste range.
To copy formulas and paste values:
Select the range with formulas, press Ctrl-c to copy, and select the range where you want to paste the values (which could be the same as the copy range). Then (since there is no keyboard equivalent) select the EditPaste Special menu item, and select the Values option. (I do this Paste SpecialValues combination so often that I’ve memorized the keystroke equivalents: Alt-e, then s, then down arrow twice, then Enter.)
Try it! Copy the range D2:D8 to itself, but paste values.
1 1 1 2 3 6 9 5 45 7 4 28 8 8 64 8 10 80 7 6 42
You might want to experiment with the other options on the EditPaste Special dialog box. For example, if you have a set of labels entered as a row and you want this same set of labels entered somewhere else as a column, try copying and pasting special with the Transpose option.
Moving (Cutting and Pasting)
Often you would like to move information from one place in the sheet to another.
To move (cut and paste):
Select the range to be cut, press Ctrl-x (for cutting), select the upper left corner of the paste range, and press Ctrl-v. (The little finger-index finger combination in your left hand is also good for pressing Ctrl-x.)
As with copying and pasting, toolbar buttons can be used instead of key combinations, but either is more efficient than selecting menu items. Also, note that you need only select the upper left cell of the paste range. Excel knows that the shape of the paste range is the same as the shape of the cut range.
Try it! Move the range A2:C8 to the range D2:F8. (Watch how relative addresses affect the eventual formulas in column F.)
2 3 6 7 4 28 3 6 18 3 9 27 6 7 42 6 6 36 1 4 4
Absolute/Relative References
Absolute and references are indicated in formulas by dollar signs or the lack of them, and they indicate what happens when you copy or move a formula to a range. You typically want some parts of the formula to stay fixed (absolute) and others to change relative to the cell position. This is a crucial concept for efficiency in spreadsheet operations, so you should take some time to understand it thoroughly. Here are two important things to remember: (1) The dollar signs are relevant only for the purpose of copying or moving; they have no inherent effect on the formula. For example, the formulas =5B3 and =5$B$3 in cell C3, say, produce exactly the same result. Their difference is relevant only if cell C3 is copied or moved to some range. (2) There is never any need to type the dollar signs. This can be done with the F4 key.
To make a cell reference absolute or mixed absolute/relative using the F4 key:
Enter a cell reference such as B3 in a formula. Then press the F4 key.
In fact, pressing the F4 key repeatedly cycles through the possibilities: B3 (neither row nor column fixed), then $B$3 (both column B and row 3 fixed), then B$3 (only row 3 fixed), then $B3 (only column B fixed), and back again to B3.
Try it! Enter the appropriate formula in cell B7 and copy across to E7. (Scroll to the right to see the correct answer.)
Select the ToolsOptions menu item, click on the General tab, and check or uncheck the
R1C1 reference style option.
Try it! Toggle between A1 and R1C1 reference style in the following spreadsheet,
and for either, examine the formulas inside the border and in the Total row.
I have read one Excel book that advocates the use of R1C1 notation everywhere,
reasoning that this notation makes more logical sense. Maybe the author has a point, but
the A1 notation is so ingrained in most of us that the transition to R1C1 notation would be
painful, to say the least!
Inserting and Deleting Rows or Columns
Often you want to insert or delete rows or columns. Note that deleting a row or column is not the same as clearing the contents of a row or column—making all of its cells blank. Deleting a row or column means wiping it out completely.
To insert one or more blank rows:
Click on a row number and drag down as many rows as you want to insert, and then press Alt-i and then r. (Alternatively, use the menu equivalent: InsertRow).
The rows you insert are inserted above the first row you selected. For example, if you select rows 8 through 11 and then insert, four blank rows will be inserted between the old rows 7 and 8.
Try it! Insert blank rows for the data for Feb, Apr, and May.
Month Price Units sold Revenue Jan $3.00 100 $300. Mar $3.25 50 $162. Jun $3.50 200 $700.
Columns are inserted in the same way, except that the key sequence is Alt-i and then c (or the menu equivalent: InsertColumn).
Try it! Insert blank columns for sales reps Baker, Miller, and Smith (so that the sales reps are in alphabetical order from left to right).
Sales rep Allison Jones Taylor Commission rate 5.4% 6.5% 4.3% Sales $15,000 $12,000 $17, Commission $810 $780 $
To delete one or more rows:
Click on a row number and drag down as many rows as you want to delete, and then press Alt-e and then d (or the menu equivalent: EditDelete). Columns are deleted in exactly the same way.
Try it! The company no longer carries products K322 and R543, so get rid of their rows.
Product Code Units sold Unit price J645 148 $15. K322 278 $17. L254 384 $25. M332 13 $30. R543 247 $22. S654 315 $35.
Filling a Series
Say you want to fill column A, starting in cell A2, with the values 1, 2, and so on up to 1000. There is an easy way.
To fill a column range with a series:
Enter the first value in the first cell (1 in cell A2). With the cursor in the starting cell (A2), use the menu item EditFillSeries to obtain a dialog box. Change the Row setting to Column, make sure the Type setting is Linear, make sure 1 is in the Step Value box, enter the final value (1000) in the Stop Value box, and click on OK.
As you can guess from this dialog box, many other options are possible. Don’t be afraid to experiment with them.
Try it! The series of days in column A should go from 1 to 25, in column D it should go from 26 to 50.
Make sure there is enough room for the transposed version. For example, if the original range has 3 rows and 5 columns, the transposed version will have 5 rows and 3 columns. If you select cell D5, say, as the upper left cell for the transposed version, everything in the range D5:F9 will be overwritten by the transposed version.
Try it! Transpose the range A3:D6 to a range with upper left cell F3.
Range Names
Range names are extremely useful for making your formulas more understandable. After all, which formula makes more sense: =B20-B21 or =Revenue-Cost? Efficient use of range names takes some experience, but here are a few useful tips.
To create a range name:
Select a range that you want to name. Then type the desired range name in the upper left “name box” on the screen. (This box is just above the column A heading. It usually shows the cell address, such as E13, where the cursor is.)
You can also go through the InsertNameDefine menu item, but typing the range name in the name box is quicker and more intuitive. By the way, range names are not case sensitive, so that Revenue, revenue, and REVENUE can be used interchangeably.
Try it! Name the rectangular range containing the numbers Data.
71 31 9 69 5 15 74 46 84 27 14 49 25 38 83 40 43 20 75 83 28 72 30 92 75 41 56 90 89 73 28 81 43 81 61
To delete a range name:
Use the InsertNameDefine menu item. This shows a list of all range names in your workbook. Click on the one you want to delete, and then click on the delete button.
Suppose a range has name Costs and you want to rename it UnitCosts. If you highlight the range, the name box will show Costs. If you then overwrite this with UnitCosts in the name box, the range
will have two names, Costs and UnitCosts. There is nothing inherently wrong with this, but if you want only a single name, UnitCosts, you will have to delete the Costs name, as described here.
Try it! The numerical range is currently named Data. Delete this range name and then rename the range Database.
15 86 50 41 21 63 18 10 1 9 14 52 16 18 65 89 88 28 72 7 15 28 57 92 2 29 90 86 100 83 10 82 100 65 4
Suppose you have the labels Revenue, Cost, and Profit in cells A20, A21, and A22, and you would like the cells B20, B21, and B22 (which will contain the values of revenue, cost, and profit) to have these range names. Here’s how to do it quickly.
To create range names from adjacent labels:
Select the range consisting of the labels and the cells to be named (A20:B22). Then use the InsertNameCreate menu item, make sure the appropriate option (in this case, Left Column) is checked, and click on OK.
Excel tries (usually successfully) to guess where the labels are that you want to use as range names. If it guesses incorrectly, you can always override its guess.
Try it! Name the ranges A3:A8, B3:B8, and so on according to the labels in row 2.
Month UnitsSold UnitPrice Revenue Jan 100 $1.25 $125. Feb 150 $1.25 $187. Mar 200 $1.40 $280. Apr 230 $1.40 $322. May 200 $1.50 $300. Jun 300 $1.50 $450.
Sometimes you have entered a formula using cell addresses, such as =B20-B21. Later, you name B20 as Revenue and B21 as Cost. The formula does not change to =Revenue-Cost automatically. However, you can make it change (and hence become more readable).
To apply range names to an existing formula:
Select the cell (or range of cells) with the formula(s). Then use the InsertNameApply menu item, highlight any relevant range names for the formula(s) involved, and click on OK.
Revenue Cost Profit $1,600 $1, $2,000 $1, $2,100 $1, $2,900 $2, $500 $ $1,700 $1, $2,000 $1, $2,500 $2,
To paste a list of all range names on a sheet:
To document your spreadsheet, it is often useful to create a list of all of your range names and their corresponding cell addresses. This is easy with the InsertNamePaste menu item.
To paste a list of all range names and corresponding addresses, select a cell with plenty of blank space below it, select the InsertNamePaste menu item, and click on the Paste List button.
Try it! Paste a list of all range names, starting in cell D2.
Basic Excel Functions
There are many useful functions in Excel. You should become familiar with the ones most useful to you. For example, financial analysts should learn the financial functions. But here are a few that everyone should know. (By the way, I capitalize the names of these functions, just for emphasis. However, they are not case sensitive. You can enter SUM or sum, for example, with the same result.)
SUM Function
The SUM function is probably the most used Excel function of all. It sums all values in one or more ranges.
To use the SUM function:
Enter the formula =SUM( range ), where range is any range. This sums the numerical values in the range.
Actually, it is possible to include more than one range in a SUM formula, as long as they are separated by commas. (This can also be done with the COUNT, COUNTA, AVERAGE, MAX, and MIN functions discussed below.) For example, =SUM(B5,C10:D12,Revenues) is allowable (where Revenues is a name for some range). The result is the sum of the numerical values in all of these ranges combined. Note that if any cell in any of these ranges contains a label rather than a number, it is ignored in the sum.
Try it! Use the SUM function in cell B10 to calculate the total of all costs.
Table of costs for units produced in one month (along side) for use in another month (along top)
Feb Mar Apr May Jan $4,300 $3,100 $3,400 $5, Feb $5,500 $6,400 $6, Mar $6,800 $6, Apr $5,
Total cost
COUNT, COUNTA Functions
The COUNT function counts all of the cells in a range with numeric values. The COUNTA functions counts all nonblank cells in a range.
To use the COUNT function:
Enter the formula =COUNT( range ), where range is any range. This returns the number of numeric values in the range.
To use the COUNTA function:
Enter the formula =COUNTA( range ), where range is any range. This returns the number of nonblank cells in the range.
For example, if cells A1, A2, and A3 contain Month, 1, and 2, respectively, then =COUNT(A1:A3) returns 2, whereas =COUNTA(A1:A3) returns 3.
Try it! Use the COUNT and COUNTA functions to fill in cells E1 and E2. Note that there are students below the visible portion of the spreadsheet.
Student ID Exam score Number enrolled 3416 62 Number who took exam 6125 73 1535 74 2323 Absent 577 77 9044 57 8403 67 5892 90 4242 77
AVERAGE Function
The AVERAGE function averages all of the numeric cells in a range.
To use the AVERAGE function:
Enter the formula =AVERAGE( range ) where range is any range. This produces the average of the numeric values in the range.
SUMPRODUCT Function
There are many times when you want to sum products of values in two (or possibly more than two) similar-sized ranges. Fortunately, there is an Excel function that sums products quickly.
To use the SUMPRODUCT function
Enter the formula =SUMPRODUCT( range1 , range2 ), where range1 and range2 are exactly the same size. For example, they might be two column ranges with 10 cells each, or they might be two ranges with 4 rows and 10 columns each. The formula sums the products of the values from the two ranges.
There can actually be more than two ranges in the SUMPRODUCT formula, separated by commas, so long as all of them have exactly the same size. This is not as common as having only two ranges, but it is sometimes useful
Try it! Sum the products of the two ranges in the following spreadsheet to find the total shipping cost. Enter the result in cell G1. (Scroll to the right for the answer.) By the way, if you are tempted to write the formula without the SUMPRODUCT function as the sum of 9 products, as many of my students continue to do, imagine how long your formula would be if there were 10 plants and 50 cities!
IF Function
IF functions are very useful, and they vary from simple to complex. I’ll provide a few examples.
To enter a basic IF function:
Enter the formula =IF( condition , expression1 , expression2 ), where condition is any condition that is either true or false, expression1 is the value of the formula if the condition is true, and expression is the value of the formula if the condition is false.
A simple example is =IF(A1<5,10,“NA”). Note that if either of the expressions is a label (as opposed to a numeric value), it should be enclosed in double quotes.
Try it! Enter appropriate IF formulas in columns C and D. (Scroll to the right to see the correct answer.)
For each product, if the end inventory is less than or equal to 50 units, enough units are ordered to bring stock back up to 200; otherwise, no units of that product are ordered
Product End inventory Order placed (yes or no)? # of units ordered 1 100 2 40 3 20 4 70
Sometimes IF functions are nested. For example, there might be three possibilities, depending on whether the value in cell A1 is negative, zero, or positive. A nested IF formula can then be used as follows.
To use nested IF functions:
Enter the formula =IF( condition1 , expression1 ,IF( condition2 , expression2 , expression3 )). If condition1 is true, the relevant value is expression1. Otherwise, check condition2. If it is true, the relevant value is expression2. Otherwise, the relevant value is expression.
An example is =IF(A1<0,10,IF(A1=0,20,30)). Suppose this formula is entered in cell B2. Then if A1 contains a negative number, B2 contains 10. Otherwise, if A1 contains 0, B2 contains 20. Otherwise (meaning that A1 must contain a positive value), B2 contains 30.
Try it! Use a nested IF function to fill in the grades in column C. (Scroll to the right to see the correct answer.)
Each student gets an A (if score is 90 or above), S for satisfactory (if score if 60 or above but less than 90) or U for unsatisfactory if score is below 60
Student Score Grade 1 70 2 95 3 55 4 80 5 60 6 90
Sometimes more complex conditions (AND/OR conditions) are useful in IF functions. These are not difficult once you know the syntax.
To use an AND condition in an IF function:
Enter the formula =IF(AND( condition1 , condition2 ), expression1 , expression2 ). This results in expression1 if both condition1 and condition2 are true. Otherwise, it results in expression.
Note the syntax. The keyword AND is followed by the conditions, separated by a comma and enclosed within parentheses. Also, note that more than two conditions could be included in the AND, all separated by commas.
Try it! Use an IF function with an AND condition to fill in the bordered range. (Scroll to the right to see the correct answer. Note the double quotes for labels.)
