 |
|
|
|||||||||||
|
|||||||||||||
 |
|||||||||||||
|
|
|
|
A Self Test Spreadsheet in Excel
I want to create a self-test in an Excel spreadsheet. When a user enters answers to my questions in the spreadsheet, I want it to indicate whether the answer is correct. How can I do this? Here's a simple way to
create a self-test spreadsheet. Enter all the questions in column A, leaving the first row for column headers. Enter the corresponding correct answers in column B. The cells in column C are the users' input area. Select
only the input area, right-click, and choose Format Cells. On the Protection tab, remove the check next to Locked and click on OK. Right-click the cell header of column B and choose Hide from the pop-up menu. Add
appropriate column headers in row 1. In cell D2, enter the formula =IF(C2=B2,"YES",""). Copy that formula down column D for all of the question rows. Below the last question row—still in column
D—enter the formula =COUNTIF(D2:D##,"YES"), where ## is the number of the last question row. Finally, choose Tools | Protection | Protect sheet from the menu, uncheck all the boxes except Select unlocked
cells, and click on OK. Enter a password when prompted, then save the sheet—and you're done! You can now give copies of this spreadsheet to others to use as a self-test. As soon as a user enters an answer that matches
the hidden correct answer, the adjacent column will display YES. Uppercase and lowercase distinctions don't matter, but a user must enter the precise words or numbers that you defined as correct. |
|
|
Absolute and Relative References in Excel I am
working on a spreadsheet with thousands of cells. When I'm summing, I'm able to replicate formulas by using the Fill | Right function from the Edit menu. But when I use the same method to replicate a formula for
calculating proportions, I get an error message. How can I replicate the formula calculating proportions throughout the numerous worksheets I'm working with? The key here lies in understanding absolute and relative cell references. Here's your opportunity to learn by doing. Launch Excel and follow along as we illustrate
the concept with an example. In a new workbook, enter the column headings North, South, East, West, and Total, starting in cell B1. Enter the row headings Spring, Summer, Fall, Winter, Total, and Proportion, starting in
cell A2. Enter some random numbers in the rectangle B2:E5. In cell B6, enter the formula =SUM(B2:B5) or just click in that cell and press Alt-<Equal sign> to insert the formula automatically. Highlight the cells
from B6 to E6 and choose Edit | Fill | Right. Note that Excel did not copy the formula exactly; it modified the cell references relative to the column. For example, the formula in cell E6 is =SUM(E2:E5). The same thing
happens when you copy and paste a formula or copy it by clicking in the cell and dragging that cell's fill handle. Now we'll add row totals. Click in cell F2 and press Alt- to insert the formula =SUM(B2:E2)
automatically. Highlight the range from F2 to F6 and choose Edit | Fill | Down from the menu. You now have row totals, with a grand total in cell F6. The next step is to determine what proportion each row total is
from the grand total. Click on cell B7 and enter the simple formula =B6/F6. Now, as before, use Edit | Fill | Right to copy that formula into the other columns. This time, it doesn't work! All the other columns display
a #DIV/0! error message. When you click on the formula for one of those columns, you'll see why. With each move to the right, Excel adjusts both cell references, so the formula in column C is =C6/G6. But G6 and the
cells to the right of it are empty, so dividing by them naturally causes an error. To fix the problem, go back to cell B7 and change the formula to =B6/$F6. The dollar sign here tells Excel that the column in the
reference F6 is absolute, meaning it should not be changed when the formula is copied. Fill the remaining columns with the modified formula and they'll all correctly display the proportions of the grand total they
represent. Use the $ character in front of the column letter or the row number or both to make that portion of any reference absolute. |
|
|
Add a data series to a chart with a few mouse clicks You can use your mouse to quickly add a data series to an existing chart. For example, let's say you have a chart that shows sales for January and February. To add the data for
March, follow these steps:
1. Click the existing chart. Excel will highlight the corresponding data series for January and February in the spreadsheet.
2. Click and drag the lower handle of the selection until the third series is included.
Excel automatically adds the data for March to the chart. Expanding the existing selection works well when the data you want to chart is in adjacent columns. For nonadjacent cells, select the
cells that contain the data, and click and drag the selection to the existing chart. Excel automatically adds the selected data series to the chart and a generic name to the legend. To add the correct name
of the series to the legend, activate the Chart Wizard, and follow the steps to add the new data series. |
|
|
Add a picture to a worksheet's header Excel 2002 lets you add a picture to a worksheet's header or footer. For example, suppose you want to create a header that features your company name and logo.
Follow these steps:
1. Open the worksheet, and go to File | Page Setup. 2. On the Header/Footer tab, click the Custom Header button.
3. Put your cursor in the section you want the company logo to appear, and click the Insert Picture button. 4. Navigate to the picture you want to insert, select it, and click Insert.
5. To make any desired changes, click the Format Picture button. 6. Enter your company's name in the section where you want it to appear.
7. To make any desired changes, click the Font button. 8. Click OK twice. |
|
|
Add impact to your chart data with graphics You can add impact to a chart's data by replacing those plain solid bars with graphics. For example, let's say you have an existing bar chart that displays an increase in
holiday sales of $4 million. To replace the solid bars with stacked images (such as a Christmas tree or bell), follow these steps:
1. Right-click the data series, and choose Format Data Series. 2. On the Patterns tab, click the Fill Effects button. 3. On the Picture tab, click the Select Picture button.
4. Navigate to the picture file, and click Insert. 5. Select Stack in the Format section, and click OK to exit the dialog boxes.
Excel will replace the solid color bars with stacked images of your chosen picture. |
|
|
Add selected values with the Conditional Sum Wizard Excel's Conditional Sum Wizard helps you build formulas that add values based on certain criteria. For example, let's say you have a spreadsheet that lists each invoice
number in the InvoiceNo column, the amount of the invoice in the Amount column, the regional office in the Office column, and the due date in the DueDate column. From this data, you want to find the total dollar amount
of all invoices from your New Jersey office. Follow these steps:
1. Go to Tools | Conditional Sum. (If Conditional Sum isn't an option, go to Tools | Add-Ins, select the Conditional Sum Wizard check box, and click OK.)
2. Select the spreadsheet, and click Next. 3. Select Amount from the Column To Sum drop-down list. 4. Select Office from the Column drop-down list.
5. Select = from the Is drop-down list. 6. Select New Jersey from the This Value drop-down list. 7. Click the Add Condition button, and click Next.
8. Select Copy The Formula And Conditional Values, and click Next. 9. Enter a cell where you want to copy the conditional value (New Jersey), and click Next.
10. Enter a cell where you want to copy the conditional sum value formula, and click Finish.
Excel will display the total amount of the invoices for New Jersey in the cell you specified in Step 10. |
|
|
Add text to a chart When
creating a chart, you don't need to confine yourself to Excel's built-in titles. You can replace those titles with free-floating text boxes that offer more formatting options.
To add a free-floating text box to your chart, follow these steps:
1. Click anywhere within the chart except a chart title or data label. 2. In the formula bar, type the text you want to appear in the text box, and press [Enter]. A text box containing
the text you entered will appear in the middle of the chart. 3. Move the text box to the desired position in the chart.
4. Double-click the text box to open the Format Text Box dialog box. 5. Add the desired formatting, and click OK.
In addition, you can also use the Drawing toolbar's functions to draw an arrow or a line from the text box to another data element in the chart. |
|
|
|
Adjust Column Widths When Pasting When you copy a range of data (several columns), you can't automatically paste the column widths that accommodate your data. You must first select the data you want to copy and press Ctrl+C. Click a destination cell, and press Ctrl+V to paste the data to the new range. Right-click the new range and choose Paste Special. Select the Column Widths optioning the Paste section, then click OK. When you return to your worksheet, the columns in the new range will be the same width as the columns in the original range. |
||
Adjust Page Breaks Using two Page Setup options can reduce and enlarge your worksheet data for printing purposes. If you use this method, you should be sure to check your page breaks when resetting these options. Chances are if you change print options, you'll need to adjust any manual page breaks you've set. To quickly reset all page breaks, switch to Page Break Preview by clicking Print Preview, then Page Break Preview. Right-click any cell on the sheet and choose Reset All Page Breaks from the context menu. This action removes manual page breaks and restores all automatic page breaks. |
||
Adjusting the Printing Solution Choosing File, Print displays the Print dialog box, where you can choose from a number of print options, including the range you want to print. When you click the Print button on the Standard toolbar, Excel prints the default print area. This may or may not be what you meant to do, (especially if you don't even remember what the default print area is.) If you find you often end up with unexpected results when using this button, just remove it from your toolbar and replace it with the Print... button. This button displays the Print dialog box before printing. To make toolbar changes, first, right-click any toolbar and choose Customize from the context menu. (Or select View, Toolbars, Customize.) To delete a button, simply drag it off the toolbar. To add a button, click the Toolbars tab and identify the toolbar you're modifying. Then, select the Commands tab and select the appropriate item in the Categories control. |
||
|
|
All or nothing
Do you design Excel solutions with calculations that depend on complicated business rules? If so, the "all or nothing" trick might help make your
formulas easier to read and maintain. Here's how it works: suppose you've got a simple SUM formula that totals the values in a given range, such as =Sum(A2:A100). You want to add certain values to that total, but
only when certain conditions are true. To illustrate, suppose your business rules state that you must add 10 to the total if the value in cell D10 is greater than 100; you must add an additional 15 to the total if
the value in cell E10 is greater than 200, and you must add another 20 to the total if the value in cell F10 is greater than 300.
Instead of using a typical IF test, try using logical expressions instead. A sample formula might look like this: =Sum(A2:A100)+10*(D10>100)+15*(E10>200)+20*(F10>300). If the value in cell D10 is 175,
the expression (D10>100) evaluates to true, or one. That one is multiplied by 10 and added to the result from the SUM function. If cell D10 contains a value of 98, the expression (D10>100) evaluates to zero. Since
zero times 10 is zero, that part of the equation always evaluates to either 10 or zero. When the values in cells D10, E10, and F10 are too low, their logical expressions evaluate to zero. At the other extreme, when
the values in these cells are large enough, then all three logical expressions evaluate to true. When that happens, 10 is added to the total for D10, 15 is added for E10, and 20 is added for F10. This adds a grand total
of 45 to the sum of the range A2:1200. |
|
|
Analyze chart data with trendlines One way to analyze a downward or upward trend is to add a trendline to a series of chart data. For example, let's say your chart displays the average age of a population over a
10-year span. To find out if the population is getting older or younger, right-click the data series, select Add Trendline, and click OK. When you create a new trendline, it often looks as if
you've added a new data series to your chart. To distinguish a trendline from a data series, make changes to its format in the Format Trendline dialog box. To open the dialog box, double-click the trendline. |
|
|
Analyze XML data with Excel 2002
Excel 2002 lets you open and analyze an XML file as a native Excel file. You can use all of Excel's analysis tools, including pivot tables and pivot charts, with XML data. To open an XML file, go to File |
Open, and select XML Files from the Files Of Type drop-down list. Navigate to the XML file you want to open, and click Open. Excel displays XML tags as column headings and lists the data in the rows below
the appropriate column headings. For example, in an XML Orders file, every Orders element may have Ship Region as a child element. In the worksheet, Excel represents the child element as the column header /Orders/Ship
Region/. Excel opens XML files as read-only files, so users can't make changes to the original file while analyzing it in Excel. |
|
|
Array Formulas Have you ever sat
in front of your monitor pulling your hair out trying to identify duplicate entries in a list? If so, you should learn about Microsoft Excel's array formulas. In fact, you can use array formulas to perform calculations
that are otherwise impossible in Excel, and you can enhance the power of some of the program's existing functions. In Excel, an array is a block of adjacent cells that are treated as a group. To see this, highlight a
block of cells, type a number, and then press Ctrl-Shift-Enter. You'll see the number in all the highlighted cells. With array formulas, the array indicates that the formulas act on sets of values rather than a single
value. Each set of values (known as an argument) must have the same number of rows and columns. You enter array formulas the same way as normal formulas except that, as above, you press Ctrl-Shift-Enter. Though you
can use array formulas to return either a single result or multiple results, we will keep things simple by focusing on the former type. Here's an array formula that calculates the total stock value from a list
containing numbers of items in stock (numberInStock) and their prices (price): =sum(price*numberInStock) To enter this as an array formula, type it and press Ctrl-Shift-Enter. (If you press only Enter by mistake,
simply click on the cell, press F2 and then press Ctrl-Shift-Enter.) If you select the cell, you'll see that the formula is enclosed in a set of curly brackets, which indicates it is an array formula. The formula
takes each value in the range price and multiplies it by the corresponding value in the range numberInStock. The results are then added, and a single value is returned. (Coincidentally, this formula performs the same
task as Excel's Sumproduct function.) As you can with regular formulas, you can use range names (as we have), or actual cell references in array formulas.
Extending SumIF
Excel's SumIF function sums a series of numbers if a certain condition is true. So if you have a series of last names in column B (with the range named salesperson) and sales in column C (sales), the following
formula sums the sales for the salesperson Smith: =SUMIF(salesperson,"Smith",sales) You can write this as an array formula: =SUM(IF(salesperson="Smith",sales)) Extending this array formula to
calculate multiple conditions is easier than attempting it with the SumIF function. So if there are month names in column A (month), the following array formula calculates the sales for Smith in January:
=SUM(IF((salesperson="Smith")*(month="Jan"),sales)) The formula works by comparing each value in the salesperson range with the word Smith and returns True (1) if there is a match and False (0) if
not. It also compares each value in month with the word Jan and returns 1 for a match and 0 otherwise. When the results are multiplied, only entries where the salesperson is Smith and the month is January will return a
True result and be passed to the Sum function to be added. (Note that any nonzero number returns a True result.) Performing a calculation using OR criteria is similar. In this case you add the condition results
instead of multiplying them. The following example calculates the total sales for January or February: =SUM(IF((month="Jan")+(month="Feb"),sales)) Any of these examples can be easily adapted to
your worksheets. Simply replace our range names with your own and type the words to match between the quotation marks. If you're using OR criteria, you can look up two items in the same range (as above) or two items
from different ranges
Check for Unique Data
The handy array formula below checks to see whether the contents of a column of numbers or text (dataRange) contains duplicate entries: =MAX(COUNTIF(dataRange, dataRange)) It uses the CountIf function with the
same range passed to it for both arguments (range and criteria). This forces Excel to compare every value in the range dataRange with every other value in the range to determine whether the values are the same. Each
value will (of course) be equal to itself, but you need to know whether it is also equal to any other value (indicating duplicates in the range). Countif counts the number of matches for each comparison and passes the
results to Max, which returns the maximum number of matches. If the result is 1, each number matches only itself; there are no duplicates. If the result is larger than 1, there are duplicates.
You can combine this formula with an IF function to give a textual explanation of the results: =IF(MAX(COUNTIF(dataRange, dataRange))=1,"No Duplicates", "Duplicates") When you're working with
array formulas, start with a small amount of data on a fresh worksheet. This lets you test a formula to ensure its accuracy. You should also use named ranges in your formula instead of cell references, so you can copy
the formulas to other worksheets that use the same named ranges. |
|
|
Array shortcut
Many Excel users bristle when you bring up the subject of arrays. The term array conjures up images of unwieldy syntax and complicated mathematical
concepts. Help your students or end users overcome their objections with this practical application of array constants that can help save keystrokes. To illustrate one use of array constants, consider how you use the
OR function to evaluate the contents of a given cell. The formula =OR(A1=4,A1=5,A1=6) evaluates to true if A1 contains 4, 5, or 6. You typically use the OR function in conjunction with the IF function, in the form
=IF(OR(A1=4,A1=5,A1=6),true_result,false_result). If A1 contains 4, 5, or 6, the IF function returns the true_result. If A1 contains any other value, the formula returns the false_result. By using an array constant,
you can save yourself some keystrokes and decrease the chance of typographical errors. The expression =OR(A1={4,5,6}) yields the same result as the expression =OR(A1=4,A1=5,A1=6). If you want to add 8 to the list of
possible values that make the OR function evaluate to True, you'd just add a comma and the number 8 in the form =OR(A1={4,5,6,8}). When using arrays, keep in mind that the array constant, by definition, works only
with values. If you need to compare the entry in A1 to other cells or named ranges, you must use the A1= approach. |
|
|
Auditing errors
No one likes to see an error message show up in his or her Excel sheets. The presence of an error message means that somehow, somewhere, a typographical
error has been made in a formula, or a piece of information is either missing or incorrect. If you composed the formula that's returning an error, you may not have much trouble determining where the problem is.
However, if you're working on someone else's worksheet, try using the Auditing tools to discover what's causing the error. Just click on the cell that contains the error message and then go to Tools | Auditing |
Trace Error. When you do, Excel will draw an arrow to the cell that contains the error, from every cell that the formula references. Then you can tell at a glance which cells you need to audit to confirm that they
contain the right kind of information. After you've audited the problem, remove the arrows by going to Tools | Auditing | Remove All Arrows. |
|
|
Auto Shaping key values If you want to make a particular word, phrase, or calculated value stand out onscreen and in your printouts, try displaying it within an
AutoShape object. With AutoShape, you won't have to manually update this object every time the data changes. Here's how it works:
1. Display the Drawing toolbar by going to Insert | Picture | AutoShapes, or by going to View | Toolbars and choosing Drawing.
2. When the Drawing toolbar appears, click on AutoShapes and select one of the available shapes. 3. In the worksheet, click-and-drag to draw the object. 4. With the AutoShapes object
still selected, click in the Formula bar and type a reference to the cell that contains the value you want to display. Use the form =cellref. For example, if the value or string you want to display is in A1, you'd
type =A1 in the formula bar, and press [Enter]. 5. When you press [Enter], Excel displays the contents of your cell (e.g., A1) within the AutoShapes drawing.
To fine-tune the way the data looks in AutoShape, double-click on the drawing to display the Format AutoShape dialog box. Use this box to adjust font, color, and other formatting options.
Note: You can only enter references to single cells in the Formula Bar; complex formulas are not allowed. That is, while you can enter =A1 in the Formula Bar with the AutoShape drawing selected, if you enter
=A1*10, you will receive an error stating: The text you entered is not a valid name reference or defined name. If you enter a range name associated with more than one cell, the AutoShapes drawing will only
display the value |
|
Automatic Formatting of Dates and Time When you enter data, Excel does its best to determine the type of data you're entering. Dates are a good example of Excel's intuitive efforts. If you enter a value that contains a slash (/) or hyphen (-), and that value also complies with the Windows standard date format, Excel will interpret your entry as a serial value and format the entry accordingly. Similarly, Excel will format any value that contains a colon (:) as a time value. Or you can follow the time value with an A or P, representing AM and PM, respectively. |
|
|
|
Average Numbers The SMALL
function is what you need. Suppose a series of ten numbers falls in the range A1:A10. The formula =SMALL(A$1:A$10,1) returns the smallest, =SMALL(A$1:A$10,2) returns the second-smallest, and so on. As you might expect,
the LARGE function returns the specified largest values in a range. Averaging a series of numbers while omitting one or more of the smallest ones is a fairly common task. For example, teachers frequently discard the
lowest one or two scores when grading students. Start by naming the range containing the values. Select the range, click on the Name box to the left of the Formula box, and type the name (for example, MyRange). This
formula will calculate the average of the numbers in the range, omitting the lowest two values: =(SUM(MyRange)-SMALL(MyRange,1) -SMALL(MyRange,2))/(COUNT(MyRange) -2). The formula first sums all the values in
MyRange and then subtracts the two smallest. It divides the result by the count of items in MyRange minus 2. Of course, you can write this formula using an ordinary cell reference, like A1:A10, rather than a named
range, but the range appears four times in the formula, which means you have four chances to mistype it. Naming the range makes writing the formula much easier. |
|
|
Averages Without Extremes In Excel I have a
column in Excel that contains account balances. I would like to find the average balance after throwing out the one or two highest and one or two lowest values. I have not found a clean way to keep track of the count
while averaging only the values that meet my criteria. Is there a way to choose how many values to trim from the top and bottom before calculating the average? Editor's Note Suppose your balances are in a range named Bal
that starts in cell A1 and extends down column A. If you want to discard only the highest and lowest values, you could do that with a formula like this: =(SUM(Bal)-SMALL (Bal,1)LARGE(Bal,1))/(COUNT(Bal)-2). But we've
found no easy way to go from this to a formula that lets you discard a variable number of items from the high and low ends. Instead, we worked up a second range of cells that indicate whether the adjacent cells should
be included in the average. Suppose the number of low values to be dropped is in cell D1, the number of high values to be dropped is in cell D2, and column B is empty. In cell B1, enter this formula:
=AND(A1>SMALL(Bal,D$1),A1< is="SUMIF(Temp,TRUE,Bal)" items remaining the of average for formula The otherwise. if FALSE and in included be should balance adjacent TRUE contain will range this item Each
Temp. name cells those all select Then balances. your to column down it Copy D$2)).> Unfortunately, that system breaks down when the value in D1 or D2 is 0 (that is, if you cut off no items at the high or low
end). If your spreadsheet must handle that eventuality, change cell B1 to this lengthy formula: =AND(IF(D$1>=0,A1>SMALL(Bal,D$1),TRUE),IF(D$2>=0,A1<LARGE(Bal,D$2),TRUE)). Then copy the formula throughout the
remainder of the Temp range. You'll still get a #DIV/0 error if you cut off so many that none remain, but that's not unreasonable. |
|
|
Avoid accidental overwriting when pasting Have you ever accidentally pasted a block of cells over data when you really wanted to insert the copied cells instead? One way to avoid that problem is to insert a number of
blank rows or columns before you start copying and pasting, but if you insert too few rows or columns, you may still destroy existing data. Excel 2000 provides a sure-fire way to avoid overwriting data when you copy and
paste. Follow these steps:
1. Select your cells, and press [Ctrl]C (or go to Edit | Copy) to copy those cells to the clipboard. 2. Click the first cell where you want to paste the data, and press [Ctrl][Shift][+].
3. On the Insert Paste dialog box, click the radio button to select which way you want Excel to shift the existing cells. 4. Click OK to complete the copy.
With this process, Excel automatically makes room for the data you're pasting and existing data is untouched. |
|
|
Adjust text to fit within a cell
When text is too long to display in a label cell, you can use Excel's AutoFit feature to enlarge the cell enough to fit the contents. But this can result in too much white space in the rest of the row or column.
Rather than fitting the cell to the label size, Excel also lets you resize the contents to fit within the cell. Follow these steps:
1. Select the cell with text that's too long to fully display, and press [Ctrl]1. 2. In the Format Cells dialog box, select the Shrink To Fit check box on the Alignment tab, and click OK.
However, sometimes this method shrinks text to the point of sacrificing legibility. An alternate method is to wrap the label text within the selected cell by selecting the Wrap Text check box on the
Alignment tab of the Format Cells dialog box. Keep in mind that this method will increase the height of the cell. |
|
|
||||||||||||||||||||||||||||||||||||||||||||||||||
