|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||||||||||||||||
|
|
|
|||||||||||||||||||||
|
|
|
|||||||||||||||||||||
|
|
|
|||||||||||||||||||||
 |
 |
|
||||
|
||||||
Excel Thinks For You
Tips to speed up your work
Remember the time your friend copied the assignment from you and got better marks than you did? Spreadsheets are like that friend of yours—show it what to do for one set of figures, and it’ll duplicate the action on a whole spreadsheet-full of figures, thousands of times faster than you possibly could. And that’s exactly why spreadsheets are so useful.
Almost every time you sit on a computer, you do some amount of copying and pasting, be it in a word processor, DTP package, Windows 95 Explorer or even across applications. Most of the time this is just a simple duplication of matter from one spot to another. Microsoft Excel 97 adds several dimensions to copying and pasting.
The best way to see the various options is to start the program and actually try them out. If you have Excel 97 on your computer, it would be useful to start it at this point and follow along on your computer. If you have an older version of Excel, or some other spreadsheet, e.g. Lotus 1-2-3, you can still follow along although some specific commands or features may be different.
Let’s build a database of students along with their marks, percentage and rank as shown. Enter about 15 names along with a roll number and marks out of 250. Do not enter the serial number, percentage or rank.
Simple duplication
Move the cursor to cell E2 and type in 250, if you haven’t already done so, and copy it using either the toolbar button or the keyboard short-cut. Now click on cell E3, drag the selection to E16, and click on the Paste button. All the cells from E2 to E16 should show 250 as the Total Marks.
Series—the old-fashioned way
Move to cell A2 and type in 1 as the first serial number. Now move to A3 and type in the formula: =A2+1. When you press Enter, the number 2 should appear in this cell. While the cursor is on A3, click on the Copy button. Select cells A4 to A16 and click on Paste. The serial numbers appear. As Excel pasted the formula into each cell, it changed the cell reference appropriately so that each number is one more than the one in the previous row. Instead of copying and pasting, you could also simply select cells A3 to A16 and press Ctrl-D (the shortcut for Edit-Fill-Down). Quick, simple and painless but no whistles either.
Series—the Excel way
First click on the Undo button to get rid of the serial numbers we just generated. In cell A3 type the number 2 and then select cells A2 and A3. Now move the mouse cursor over the tiny black square at the lower, right corner of A3—called the fill handle. When the pointer is on the fill handle, the pointer changes from an arrow to a crosshair. Click and drag down to A16. All serial numbers are filled. No copying, no pasting and no typing of formulas. Just select the minimum number of cells which show Excel how to proceed with the series and drag the fill handle to cover the required cells. Drag down or to the right to fill cells with data based on the current selection. Drag it up or to the left to clear the contents of selected cells.
Let’s complete the Percentage column in the sample spreadsheet. Move to cell F2 and type in the formula:=D2/E2*100. Now click on the fill handle and drag it down to cell F16. All the percentages appear correctly. It took 15 seconds to calculate 15 percentages! Quick, simple, painless and now we have the whistles too.
Excel 97 offers a third way of creating series through the Edit-Fill-Series dialog box but this is cumbersome for a simple numerical series. Explore it for creating date and complex series.
When it doesn’t work...
Let’s make another spreadsheet showing sports activities and number of students who have taken up each activity. Fill in the name of the sport and the number in column B. Move to cell B7 and click on the AutoSum button twice to get the total.
We want to know what percentage of students have taken up basketball and what percentage are swimming and so on. Move to cell C2 and type in the formula =B2/B7*100. Now click on the fill handle and drag it down to copy the formula up to C6. Oops! What happened? The spreadsheet shows several rows of #DIV/0! This is an error message that means you have tried to divide a number by 0—which is illegal. Examine the formulas in cells C3 to C6. Excel has faithfully copied the formula to reflect the percentage based on the contents of the cell one row to the left and 5 rows down. But all cells below B7 are blank—hence the error. Excel isn’t that smart after all.
To correct this problem, we need to tell Excel to use the contents of cell B7 for all rows. We could type in the total, 92, as part of the formula but then if we changed any of the numbers in column B, the Total in B7 would change but the percentages would no longer be correct. Cell references in formulas are usually relative references, i.e. the reference indicates a certain number of rows up or down or a certain number of columns to the left or right. To fix the reference, we precede the column and/or row number with the $ sign (the icon of absolute-ness), e.g. $B$7. When this is copied to different cells, the cell reference doesn’t change. Correct the formula in C2 to read =B2/$B$7*100 and copy it to cells C3 to C6 by dragging the fill handle. All percentages appear correctly now.
AutoComplete
Excel 97 even types for you! If the first few characters you type in a cell match an existing entry in that column, Microsoft Excel fills in the remaining characters for you. This is applicable only for those entries that contain text or a combination of text and numbers; entries that contain only numbers, dates, or times are not completed. To accept the proposed entry, press ENTER, otherwise simply continue typing.
Move to cell A6 and type ba. The word Basketball appears in the cell. Since we don’t actually want Basketball in this cell (this was just to demonstrate the AutoComplete), press Esc.
Pie for dessert
Before filling up the ranks in the Examination Results spreadsheet (which we’ll do in the next issue), let’s add some pizzazz to our Sports spreadsheet—we’ll put in a pie chart.
Select cells A1 to B6 and click on the Chart Wizard button. Select Pie as the Chart type and 3D as the sub-type. You needn’t go through the rest of the steps, just click on Finish. Now don’t you wish you could make apple pie with just a few mouse clicks?