Welcome to Blaisdell's Little Corner of the Web

Home Browsers

MsOffice 97 & 2000

Site Search Windows

Freeware | Freeware From A-Z | Security | Virus Information

Office Index II | Office Index I |                                                    | This site was Updated on  02/28/2006 |

Welcome to page 3 of Excel with Excel

Page Index

  1. FIND THE SERIAL DATE IN EXCEL
  2. Color Grindline In Excel
  3. NEED TO TRANSPOSE TABLES WITH FORMULAS?
  4. Protect an Excel WorkBook
  5. SORT CELLS IN EXCEL
  6. Merge styles between Microsoft Excel workbooks
  7. SELECTING EXCEL ROWS AND COLUMNS WITH KEYBOARD SHORTCUTS
  8. EXPORTING AN EXCEL DOCUMENT TO THE ADDRESS BOOK
  9. EXCEL HEADER TEMPLATE
  10. SELECTING EXCEL DATA
  11. SETTING THE EXCEL SOLVER OPTIONS
  12. MULTIPLE PRINT AREAS
  13. FOOLING EXCEL - PRINTING MULTIPLE RANGES
  14. WATCHING AN EXCEL CELL
  15. USING A LOGO IN EXCEL
  16. MOVE EXCEL WORKSHEETS TO A NEW WORKBOOK
  17. A SIMPLE EXCEL CALCULATION
  18. USE PICTURES IN EXCEL LINE CHARTS
  19. Copy a formula to a new location in Microsoft Excel without the relative references changing.
  20. FIND THE MAXIMUM VALUE IN A GIVEN MONTH OF A GIVEN YEAR
Go to Index Go to Start Page Go to Excel with Excel Main index Go to Secondary Index

Solver Index


FIND THE MAXIMUM VALUE IN A GIVEN MONTH OF A GIVEN YEAR

Ever need to extract the largest value in a given month? What if you have peak usage of a given product every month and you wish to extract the peak usage for the month. This Array formula will do just that.

Place the year in Cell Where the region for the dates are in: A1:A10
Where the region of the values are in: B1:B10
Type the year in Cell C1 (For example 2001)
Type the Month Digit in cell D1 (For example 2 (Feb))
The formula would read as follows {=Max(if((Year(A1:A10)=C1)*(Month(A1:A10)=D1),B1:B10))}
Make sure to hit Ctrl+Shft+Enter to activate the array.


PROBLEM: You want to copy a formula to a new location in Microsoft Excel without the relative references changing.

SOLUTION: Before copying the formula, edit it and remove the leading equal sign (=). This effectively changes the contents of the cell to text, which means you can copy and paste it anywhere you want without having to worry about the relative cell references changing. Add the equal sign back to the original cell, and the formula is a formula again. Ditto for the copy of the formula.


FIND THE SERIAL DATE IN EXCEL

In cell A1. You would like to see the serial number that represents that date. You can click A1 and choose Format, Cells. When the Format Cells dialog box opens, click the Number tab and then, under Category, select General from the list. Click OK to close the dialog box and apply your selection. Cell A1 now displays 36526--the serial form for 1/1/2000.


Color Grindline In Excel

If you would like to check out color grid lines, run Excel and open a blank worksheet. Choose Tools, Options and, when the Options dialog box opens, click the View tab. Under Window Options, click the arrow at the right side of the Color list box and select a new color. Make sure the check box labeled Gridlines is selected and click OK to close the dialog box and apply your new color.

If you would like to print the grid lines in your new worksheet, choose File, Page Setup. When the Page Setup dialog box appears, click the Sheet tab. Now, under Print, select the Gridlines check box and click OK to close this dialog box and save your new setting.

For purposes of this demonstration, let's print something. Enter any number (or text) in cell A1 of your blank worksheet. Next, move to cell L24 and enter a number. Choose File, Print and click OK to print your worksheet.


To transpose tables with formulas do this:

  1. Select the region with the formulas.
  2. From the Edit menu, select Replace.
  3. Under Find what type = Under Replace with type <
  4. Select and copy the entire table.
  5. Select a cell or destination where you want the
     transposed table to be placed.
  6. Under the Edit menu choose Paste Special.
  7. From the Paste Special dialog box select Transpose.
  8. From the Edit menu, select Replace.
  9. Under Find what type < Under Replace with type =

  The transposed table now reflects the original referencing without using absolute as shown in Figure


Protect an Excel WorkBook

PROBLEM: You want to protect a Microsoft Excel workbook so that its structure and its windows can't be changed without the correct password.

SOLUTION: First, select Tools, Protection, Protect Workbook to display the Protect Workbook dialog box. Check the Structure check box (already checked by default) and click on OK (entering a password is optional). Now right-click on a sheet tab at the bottom of Excel's display window and observe that the Insert, Delete, Rename, and "Move or Copy" commands have been disabled. If you check the Windows check box in the Protect Workbook dialog box, you can no longer resize(restore)/ maximize/minimize child windows, split/freeze panes, create new windows, and so on, and the control buttons actually vanish from the child windows. To eliminate the protection, select Tools, Protection, and choose Unprotect Workbook.

This tip applies to Excel 97 and Excel 2000.


USE PICTURES IN EXCEL LINE CHARTS

You often use pictures in the bars of an Excel bar chart. You now need to make up a series of line charts and would like to use pictures in the points that make up the lines. Is this possible?

Yes, you can put pictures in the points of a line chart in Excel. The primary difference between pasting a picture in a bar and pasting one in a point is that you need smaller pictures for the point.

Run Excel and open a blank worksheet. Add some numbers to cells A1 through A5--1, 2, 3, 4, 5, will do fine. Select the data and choose Insert, Chart. When the Chart Wizard opens, select Line and click Finish. Use the mouse to draw the chart area. Next, click somewhere away from the chart and choose Insert, Picture, ClipArt. Double-click a ClipArt picture to insert it into the worksheet. Next, use the mouse to size the picture and then choose Edit, Copy.

Select the chart and then click one of the points to select them. Now, choose Edit, Paste to paste the picture into the line chart points.

If you need a different picture for each point, insert and size all the pictures first. Then click the picture you want to paste into the first point and choose Edit, Copy. Next, click the points as before, then wait a second and click the single point into which you want to paste the picture. When that point is selected, choose Edit, Paste to paste the picture into the point. Repeat this procedure for each of the pictures and their matching points.


Suggestion for sorting cells in an Excel worksheet without disturbing the formulas

If you set the calculation mode in Excel to Manual, a sort will not disturb the formulas. When the sort completes, you can set the calculation mode back to Automatic."

To do this, open the worksheet you want to sort and choose Tools, Options. When the Options dialog box opens, click the Calculation tab. Now select the Manual radio button and click OK to close the dialog box and apply your change.

To set the calculation mode back to Automatic, choose Tool, Options again and click the Calculation tab. This time, select the Automatic radio button and click OK.


A SIMPLE EXCEL CALCULATION

If you need to sum a row of numbers, all you
have to do is hold down the Alt key while you type in the equal sign
(=).

For example, enter the following in cells A1 through A5:

1
2
3
4
5

Then click in cell A7 and press Alt as you type the equal sign to
enter the Sum formula. All you have to do now is press Enter to get
the sum.

MOVE EXCEL WORKSHEETS TO A NEW WORKBOOK

You can copy an entire workbook. First open your original workbook and choose Edit, Move Or Copy Sheet. When the Move Or Copy dialog box opens, click the arrow at the right side of the To Book list box. Choose New Book from the list, then select the check box labeled Create A Copy and click OK. Excel will copy the selected worksheet to a new workbook.


USING A LOGO IN EXCEL

To place a company logo at the top of each Excel worksheet next to the sheet title.

Just have your logo file ready and run Excel. Choose Insert, Picture, From File and locate your logo file. Double-click the file to close the dialog box and insert the picture. Now use the mouse to size and place the logo as you wish.


WATCHING AN EXCEL CELL

Use a simple IF function. Lets say you want to watch cell A2 for values below 100, move to cell C1 and enter

=if(a2<100,"Low Balance","")

When you enter a number less than 100 into A2, this formula will display Low Balance. When the number in A2 is 100 or greater, nothing appears.


FOOLING EXCEL - PRINTING MULTIPLE RANGES

Frequently, you may want to print several groups of cells, skipping a few rows or columns. For example, you might want to select cells A4:D10 and F4:H10 to print, skipping column E entirely.

How do you select multiple ranges? Highlight the first range. Then press the Ctrl key as you highlight each additional range.

When you select several ranges, normally Excel prints each range on a separate page. To preview how this looks, do not use the Print Preview button on the toolbar. Instead choose File, Print and in the Print dialog box mark Selection and click the Preview button.

However, you can have these ranges appear on one printed page by "fooling" Excel. First, you must hide (temporarily) the rows or columns you don't want to print. In our example, this would mean hiding column E. To hide a column, select any cell in that column and choose Format, Column, Hide. To hide a row, you would choose Format, Row, Hide.

After the column is hidden, you can select the ranges you want to print. Now, you must choose File, Print. You can not use the Print button on the Standard toolbar or the keyboard shortcut (Ctrl+P) to print. The reason? Because the default printing option will print the entire worksheet. If you want to print only part of the worksheet, you must tell Excel that. Choose File, Print and mark the Selection option. The cells will be printed on one page.

You'll want to redisplay the column(s) you've hidden. The trick to unhiding a column is to select the column headings on either side of the hidden column. If column E has been hidden, you would select column D and drag the selection to column F. After the columns on either side of the hidden column are selected, choose Format, Column, Unhide. You can apply these same principles for unhiding rows.


CREATING MULTIPLE PRINT AREAS (SORT OF) IN EXCEL
To Print a specific area of a worksheet do this
To have a certain number of rows appear on each page

In Excel, you can select a range of cells and designate those cells as an area of the worksheet you routinely want to print. This is known as a Print Area. Unfortunately Excel allows you to designate only one range of cells as a print area. This limitation is irritating when you have a large worksheet that includes several areas you want to print. For example, if you have a worksheet that includes a list of countries divided up by region, you might want to print each region separately instead of printing the entire sheet. This limitation can be overcome by using range names instead of the Print Area feature.

Start off by selecting the cells for your first range you want to print. If you have a large worksheet, you will have greater control in highlighting cells if you use your keyboard to select cells. How? Select the first cell in the range. Press the Shift key and use the arrow keys on your keyboard to expand the selection.

Once you have the range selected, create a name for that range. If you don't know how to name a range, Here is how:

First, select the group of cells you want to print. Then choose Insert, Name, Define and type in a name for that group of cells. Click on OK. Repeat this process for each group you want to print.

Repeat this process for each range you want to be able to print separately.

To Print a specific area of a worksheet do this:

1    On the View menu, click Page Break Preview.
2    Select the area you want to print.
3    Right-click a cell within the selection, and then click Set Print Area on the shortcut menu.


Tip You can add additional cells to a print area in page break preview. Select the cells you want to add, right-click a cell in the selection, and then click Add to Print Area on the shortcut menu.

Tip 2: If you want a certain number of rows to appear on each page of your printout (Say as labeled or constant information headings do this:

Select File, Page Setup. On the page setup menu select the sheet tab then select the Rows to Repeat at Top drop down box by clicking on the colored figure on the right of the box. Now simply select the rows you want to repeat on each page and hit your enter or return key. Now every page has this information on it. Transversely you can do the same procedure with columns.

Once you have the range names created and the page setup options selected, you are ready to print. Here are the steps you want to follow:

1. Use the range names to highlight the range you want to print. The easiest way to do this is to select the range name from the Name box on the Formula Bar. This is the box that typically displays the cell address. There is a drop-down arrow on the right side of the box. When you click that arrow, a list of all the range names you created displays. If you are having trouble locating the Name box, an alternative way to see a list of the range names is to press F3. A pop-up box displays the range names. Select the name and choose OK.

2. With the range highlighted, choose File, Print. You cannot use the Print button on the toolbar or the keyboard shortcut (Ctrl+P) to print a range --- you MUST use the Print dialog box.

3. Choose Selection and click OK.

You can repeat these three steps for each range you want to print.


  1. SETTING THE EXCEL SOLVER OPTIONS

  2. CONTROLLING THE PRECISION

  3. DEFINING THE SEARCH

  4. INFLUENCING THE CALCULATIONS

  5. TROUBLESHOOTING SOLVER ERRORS

Choosing the Options button in the Solver Parameters dialog box displays the Solver Options dialog box. The Solver Options dialog box enables you to control the amount of work the Solver does and how it goes about reaching a solution.

In many cases, you do not need to even look at these options, much less change them. On occasion, though, you will find it necessary to make some adjustments.
Solver Index

CONTROLLING THE PRECISION

The default value of 100 for both the number of seconds and the number of iterations that the Solver uses is fairly generous. But for a very complicated problem, and especially when a nonlinear model is involved, the Solver might return to the Solver Results dialog box with a message that it could not find a solution. If it seemed to you that the Solver took a long time before it displayed the Solver Results dialog box, you might try increasing the Max Time and Iterations values. Doing so gives the Solver more opportunity to converge precisely on the target value that you specified. The maximum permissible value for Max Time and for Iterations is 32,767.

You can give the Solver some flexibility by increasing the value in the Precision edit box. When the Solver evaluates the current value of a constrained cell or of the target cell, it does so in terms of the value of the Precision option. The precision value must be between 0 and 1. The smaller the number, the higher the precision.

The Tolerance option is another way of saying "close enough." It applies only to models that have constraints using the INT operator. In other words, you have constrained at least one cell to be an integer. When a constrained cell cannot take a fractional value -- when that cell is constrained to integer values only -- it might not be possible to reach the exact value that you specified for the Target Cell. By increasing the Tolerance to, say, 10%, you indicate that a solution as much as 10% higher or lower than your target value is acceptable.

Suppose that you have $100,000 to spend on the purchase of several cars. You have set up a model that Solver will use to arrive at an average purchase price of $17,000. One of the constraints is the number of cars to purchase. You cannot purchase a fractional number of cars, so you constrain that cell to integer values.

But to arrive at an average purchase price of $17,000 on a total purchase of $100,000, you must purchase 5.88 cars. That conflicts with the integer constraint. Therefore, set the Tolerance to 15%, allowing a result somewhere between 85% and 115% of $17,000. Doing so enables an average purchase price based on either five or six cars -- both integer values.

The Convergence option comes into play when the Solver is nearing a solution. The Solver continually examines the most recent five solutions it has calculated as it varies the values in the Changing Cells. If 1 plus the ratio of the amount of change -- not the values themselves, but the change in values -- from solution to solution is less than the Convergence value for five consecutive solutions, the Solver concludes that it's not going to get any closer to your target value. Make the Convergence more precise by providing a smaller number -- any fractional value between 0 and 1.
Solver Index

DEFINING THE SEARCH

The Solver Options dialog box also enables you to establish some control over how the Solver will go about seeking a solution to the problem you set for it.

If you check the Assume Linear Model check box, you make a fundamental change in how the Solver goes about evaluating the problem. Recall from the prior section "Understanding the Solver's Reports" that the Solver proceeds by modifying values in the Changing Cells and by observing the resulting differences in the function's gradient.

You prevent this from occurring by assuming a linear model. With a linear model, the Solver can simply calculate a linear equation that fits different Changing Cell values to Target Cell values. In effect, the Solver backtracks from a specified solution to a set of requisite Changing Cell values by means of that equation. This speeds up the solution process dramatically because it's not necessary to evaluate a series of trial solutions.

If there is no exponentiation in any of the Changing Cells, in the Target Cell, or (more typically) in any of the formulas that come between the Changing Cells and the Target Cell, then it's quite possible that the model is a linear one.

It can happen, though, that nonlinearity exists in the model, not immediately apparent to you, that's induced by the relationships among the Changing Cells and between the Changing Cells and the Target Cell. If so, you may be setting the Solver an impossible task by telling it to assume a linear model. You might get an error message that the conditions for a linear solution aren't met. Or it's possible that the Solver will return the correct solution to the wrong problem.

Although a nonlinear search is slower, in a time when many users are running Excel with processors whose clock speed exceeds 200MHz, you can probably afford to assume a nonlinear model. If your model turns out to be linear, its linearity is unlikely to make a difference in the solution. So when the difference in solution time is a matter of a few seconds and when you're likely to obtain the same result, it's sensible to leave the Assume Linear Model check box in its default cleared state.

It can help to select the Use Automatic Scaling check box when the scale of measurement of the Changing Cells and that of the Target or Constraint cells are very different. In this chapter's example, the Changing Cells use both percentages (to derive costs) and hundreds of thousands of dollars (to represent revenue estimates). The automatic scaling option can speed up calculation in this and similar situations.

The Assume Non-Negative option is a good shortcut when you have Changing Cells that should not be assigned negative values. In the current example, if you were willing for any cost percentage to be zero but not negative, you could dispense with the Constraints and select the Assume Non-Negative check box instead.

It can be useful to select the Show Iteration Results check box. Doing so causes the Solver to pause after each iteration. This gives you an opportunity to examine the current values of the Changing Cells and the Target Cell and to save the current solution as a scenario.

Subsequently examining the differences among the temporary solutions can give you some insight into the relationships among the Changing Cells and the Target Cell.
Solver Index

INFLUENCING THE CALCULATIONS

When the Solver establishes a gradient of partial derivatives, it extrapolates from that gradient to choose the next set of values that it assigns to the Changing Cells. It can do that on either a linear or a nonlinear basis. If you keep the default value, Tangent, for Estimates, the Solver lays a straight line that's tangential to the gradient. It then extrapolates along that line to reach the next set of values it will try for the Changing Cells.

If you choose Quadratic instead of Tangent, the Solver lays a curve against the gradient. In nonlinear problems, this can speed up performance because it's not necessary to repeatedly create straight-line tangents to extrapolate to the next set of Changing Cell values. Again, though, because of the speed of today's microprocessors, the option will usually be irrelevant to you.

When the Solver is in the midst of evaluating a current solution and trying to figure out the next set of Changing Cell values it should try, it does so by altering each Changing Cell slightly and keeping track of the combined effect on the rate of change, the derivative, of the Target Cell value. This is how the default option for Derivatives, Forward Differencing, works.

If you choose Central Differencing, the Solver makes two estimates at each iteration, instead of just one. It does this by selecting values for the Changing Cells that cause the two estimates to lie in different directions along the gradient from the current estimate. This results in more accurate calculations at each step, but it also results in more calculations.

The Search option specifies the algorithm used at each iteration to determine the direction to search: Newton or Conjugate. The default is Newton. The choice between the Newton and the Conjugate methods is difficult to understand. Very briefly, the Newton method calculates analytic derivatives at each iteration. The conjugate gradient method constructs each iteration's estimate by means of a minimization procedure. It takes longer per iteration and requires more iterations than the Newton method. You'll usually do fine by accepting the default Newton option.
Solver Index

TROUBLESHOOTING SOLVER ERRORS

If you use the Solver on a data set and get one solution, another colleague may use the Solver on the data set and get a different solution. Most often, this occurs when two users have some Solver option set differently. But it can happen even when both users have set the Solver options identically. Very small differences in the starting values of the Changing Cells can exert an apparently disproportionate influence on the results returned by the Solver. Try copying the starting values from one user's worksheet to the other user's worksheet, and then running the Solver again.

If you set the constraint for a range of cells to Integer, when the Solver finished, you might find that the cells weren't integers. The Solver comes as close as it can to integer values for integer-constrained cells. It can happen, however, that slight variances from the integer value occur. When this happens, the variances tend to be beyond the tenth decimal place; for example, instead of 3, the Solver returns 3.000000000594. For most purposes this is close enough. But if not, use the TRUNC function on the Changing Cells in question, copy the results, and paste them as values over the original Changing Cells. Then compare the new value for the Target Cell with the one that the Solver returned.
Solver Index


SELECTING EXCEL DATA

Using Excel 97, how do you select all the data in a large sheet? For example, if you have a formula and want to apply to an entire column in a large worksheet, how can you do that without clicking and dragging all the way down to the end?

You can do this with a few keystrokes. To see how the technique works,
open a blank worksheet and enter

1
2
3
4
5

in cell A1 through A5. Now click cell A7 and type

=sum(

Next, click cell A1 and press Ctrl-Shift-down arrow. This selects cells A1 through A5. Press Enter to complete the formula.

If you need to select a row, use Ctrl-Shift-right arrow.


EXCEL HEADER TEMPLATE

Example:
Open new Excel worksheets with a standard footnote for printing, such as 'This report by Bill".

Run Excel and open a blank worksheet. Choose File, Page Setup. When the Page Setup dialog box opens, click the Header/Footer tab. Next, click Custom Footer and then click in the entry box that you want to use--Left, Center, or Right, depending on where you want your footnote. Type your footnote and click OK. Back in Page Setup, click OK again to close the dialog box and save your new footer.

To turn your workbook into a style template for your Excel installation, choose File, Save As and then click the arrow at the right side of the Save As Type list box. Select Template from the list and then name your file book. Click the Up One Level button (its icon is a folder with an up arrow). This will take you to the Microsoft Office folder. Double-click the Office folder and then double-click the XLSTART folder. Click Save to save the file book.xlt in the XLSTART folder.

Choose File, Exit to close Excel. Run Excel again, and you'll find that your footer is still in place. Since book.xlt is now your new global style template, the new footer will appear in all your new workbooks.


EXPORTING AN EXCEL DOCUMENT TO THE ADDRESS BOOK

It's easier if you follow a few conventions, though. If you don't already have headers in your worksheet, you need to add some. The example below shows a portion of a worksheet you can import into the Address Book.

First Name/Last Name/Middle Name/E-mail Address/Home Street/Home City Andrew/Smith/andrew@nada.com Bill/Jones/bill@nada.com Carl/Williams/carl@nada.com Chris/Johnson/chris@nada.com

You must now save the worksheet as a comma-delimited text file. To do this, choose File, Save As. When the Save As dialog box opens, click the arrow at the right side of the Save As Type list box and select CSV (Comma Delimited) from the list. Give your file a name and click Save. You will get a message stating that you are about to save a special file type. Click OK to continue. When you choose File, Exit, you'll get another dialog box about file types. This time click No to keep your original file format choice (CSV).

Now open Outlook Express and click Addresses to open your Address Book. In the Address Book, choose File, Import, Other Address Book. When the Address Book Import Tool opens, select Text File (Comma Separated Values) and click Import. Click Browse to locate your CSV file, then follow the wizard to import what you need.


SELECTING EXCEL ROWS AND COLUMNS WITH KEYBOARD SHORTCUTS

Use the keyboard to select a whole column in Excel by simply pressing Ctrl-spacebar.  

To select a row, click any cell in the row and press Shift-spacebar.


Merge styles between Microsoft Excel workbooks.

First, open the workbook or template that contains the styles you want to merge (we'll call this the "source file"). Next, create a new workbook/template or open an existing one--this is where you'll copy the styles--and make sure it's the active workbook. Select Format, choose Styles, and click on the Merge button. Finally, choose the source workbook from the "Merge styles from" list. All the styles in the source file are copied to the destination file. If the same style name is used in both the source and destination files, the destination style is overwritten by the incoming style.

Entertainment Government Technology About Email Me