Welcome to Blaisdell's Little Corner of the Web
MsOffice Index 1 | MsOffice Index 1I | Microsoft Mac Office 8.0 | Microsoft PowerPoint 97 | Microsoft Office XP | Microsoft Bug of the Month | Microsoft Macro Virus from MacOffice | Updated 11/14/05
Welcome to Bo's Microsoft Excel Tips-n-Tricks VOL 4
More on Lookups in Excel
As you start using Lookups you soon discover that you have to decide what to do about the #N/A that Exact Match returns for a missing value. Often you want to substitute zero or blank for the #N/A.
The simplest and slowest way is to use an IF function containing two Exact Match lookups:
You can avoid the overhead of a double exact lookup if you use exact MATCH once, store the result in a cell, and then test the result before doing an INDEX:
In A1 =MATCH(lookupvalue,lookuparray,0) In B1 =IF(ISNA(A1),0,INDEX(tablearray,A1,colnum))
When using a lookup over a large number of rows two approximate lookups are usually faster than one exact lookup. The breakeven point is at about 10-20 rows. If you can sort your data but still cannot use approximate match because you can't be sure that the value you are looking up exists, then try this trick:
This does an approximate lookup on the lookup list, and if the lookup value equals the answer in the lookup column you have found an exact match, therefore you want to redo the approximate lookup on the column you want, otherwise it's a missing value. When using this trick it's a good idea to add a dummy entry (usually zero or blank) to the list with a value smaller than the smallest value you will ever lookup.
Often with lookups you want to get a number of columns and not just one.
If you are doing exact lookups on multiple columns you can save a lot of time using one MATCH and many INDEX statements rather than many VLOOKUPs. Just add an extra column for the MATCH to store the result (stored_row) and then for each column use:
Alternatively you can use VLOOKUP in an array formula:
When using INDEX you can also return multiple columns or rows at once by using 0 as the column or row number.
For example if you want to lookup a number of contiguous columns then use:
This returns columns A to J in the stored row created by a previous MATCH.
Often you can choose between using VLOOKUP, INDEX and MATCH or OFFSET. VLOOKUP is slightly faster (approx. 5%), simpler and uses less memory than a combination of MATCH and INDEX or OFFSET.
But INDEX and MATCH offer much more flexibility:
You can do two dimensional Lookups:
You can do Lookup using Wildcards: MATCH allows you to use the wildcard characters * and ? when doing Exact Matches against text lists.
You can store the result of MATCH and reuse it in many INDEX or OFFSET functions.
You can do two stage lookups: If you have a table with two sorted indexes, such as product within country, you can concatenate the country and product columns and Lookup against that. This is slow. For large tables it can be much faster to use MATCH to find the first and last row for the country you want to Lookup, and then do a Lookup for product only within the rows for that Country.
INDEX has been improved from Excel 97 onwards and is now a non-volatile function (speeds up recalculation) however OFFSET, which is also very fast, has not been changed and is still volatile.
So the next time you find a slow-running spreadsheet check for Lookup bottlenecks, and apply these tips and techniques to eliminate them!
Mapping With Excel
By Helen Bradley
By some twist of fate (or the application of some bizarre logic), the maps for Europe
and the World Countries were omitted from Office/Excel 2000. However, if you have your old
Office 97 discs, you can install these maps from this earlier version. Place your Office
97 or Excel 97 CD in the drive and locate the missing files in the \Os\Msapps\Datamap\Data
folder. While there's a long list of files you need set out in a <A
Knowledge Base article Q235303</A>, in fact what you want are all the files with filenames beginning with either E or W - there are 32 of these in total. Select them by choosing Tools, Find, Files or Folders and search for "E*.*,W*.*" (without the quote marks) then copy these to your C:\Program Files\Common files\Microsoft Shared\Datamap\Data folder and the maps and their features will now be available.
To see how the Map tool works, enter some data such as this in a sheet:
Sales 1999 Sales 2000
HI 3400 5000
CA 5000 7500
NY 3700 5600
GA 5600 3700
Select this data area, click the Map tool, and drag a rectangle for your map. If more than one map matches your data, choose the one to use, in this case you'd use United States (AK & HI Insert). If the Map tool can't match any of your geographic names with it's own list, it offers alternate names for you to select from. Choose one of these or choose to discard the data from your map.
By default, only one column (the first) of data is mapped. The states for which no data exists are colored and the states for which there is data are shaded grey according to the value of the data.
To use colors instead of shades of grey, drag the Category Shading button in the left of the Microsoft Map Control dialog and drop it on the Sales 1999 button (in the bottom right of the dialog) -- now color is used to represent various values as indicated in the legend. There are other
plot formatting options you can use by dragging the button from the left of the dialog and drop in onto the data button. If an option isn't available for that data, the data button will be grayed out when you try to drop a formatting option onto it.
To replace the color for the unused states, right click the map and choose Features, Custom and choose a color to use. To alter the colors used for the states for which data is available, for example, where you've used Category Shading, right click the Legend and click Edit, Category Shading Options tab.
The Zoom Percentage of Map drop down list on the Map toolbar allows you to change the size of the map within the rectangle you've created for it. Use the Grabber tool to move the map around in this area to get the best display and use the Select Objects tool to move the Legend if it's overlapping the map.
Click the Map Labels option and choose Map Feature Names and click Ok. Now hold your mouse over some of the states on the map and notice that their names appear. For states you've plotted data you'll see your labels and for others the full state names. Click on a state to add that state's name to the map. To add text to your map, select the Add Text button, click where the text is to go, type it in the dialog and click Ok. There's also a Map Features option which will add features like cities, highways and airports to your map, right click the map and choose Features to see your options.
To plot two series of data on the map, drag the second series from the top of the Map Control dialog and drop it over a Format/Column grey marker in the bottom right of the dialog. This second series can be formatted using some other option than the one used for the first, for example, use Value shading for one and Dot Density for the second. You can't use the same format for both if you're using these types of formats.
However, you could plot two series on a Column chart. To do this, drag both series out of the window to remove them (the bottom right of the dialog should now be empty). Drag the Column Chart button over the Format marker then drag the first series and drop it to the right of the Column
Chart button over the Column marker. Now drag the second series and drop it to the immediate right of the first, over the Column marker, (don't use the one below it). Now you'll have a Column chart for each state with two bars, one representing each series of data.
If you change the figures in the underlying worksheet, make sure you refresh the map.
There were some problems with Excel 95 and 97 in that it didn't refresh when you selected
the Map Refresh button and I find Excel 2000 is, at best, unreliable at updating the map.
So, have a healthy disregard for this ability and always check any changes
If the map doesn't automatically update when you click the Map Refresh button (or if the Map Refresh button is disabled), click outside the map, right click it and choose Map Object, Refresh Data. If you still have problems, try saving and closing the file and reopen it and refresh the map if it isn't updated now. At worst, you may have to save and close the file, exit Excel and load and open the Map again -- this was the workaround suggested by Microsoft for versions 95 (v7).
Don't let difficulties with updating data put you off the Mapping tool. It's very handy and pretty easy to use once you get familiar with it's interface and where to find everything.
Note: Excel Energizer columnist Helen Bradley specializes in Office applications and writing tips and hands-on tutorials. Her columns appear regularly in a number of publications in the USA, Australia, UK and Canada.
DEALING WITH GROWING DATA (This instruction set
provided by Helen Bradley Excel Energizer columnist for Woody's Office Watch WOW)
This week's Excel Energizer column was prompted by a reader query which went something like this: "I am trying to make a spreadsheet to present the results from a test. The workbook is called Test, column A contains a series of dates and column B contains values for each of these dates. The number of rows is not known in advance because new date/value pairs are added from time to time. My first problem is to find the value of the cell in column A, for which the cell in column B has a maximum value".
In suggesting a solution, I'll assume that:
1. Row 1 contains headings and your data begins in row 2 2. The data is in Sheet1 of a workbook 3. That the data in columns A and B represent data pairs and there are no entries in column A for which there is no corresponding entry in column B, and vice versa. 4. In the instance that there are two cells which contain the 'maximum' value, it is in order to return the date for the first match
Based on these assumptions, here's a solution which returns the date from column A which relates to the largest value in column B (or the first 'largest' value if this figure appears more than once):
Begin by naming the data in column B. To do this, choose Insert, Name, Define and type the name Data into the Names in workbook area, then in the Refers to area type:
The CountA formula returns then number of filled cells in column B which is the number of data cells plus one heading row. The range is, therefore, set to begin at cell B2 and to end at the cell which is COUNTA rows below (less one row to account for the heading row).
When this is done, you can add data to columns A and B and the range DATA will alter to include those cells. This gives you a named range which you can use to refer to the contents of column B without having to repeatedly calculate what cells in column B actually contain data.
Now, to extract the cell in column A which relates to the highest value in column B, use this formula:
MAX(DATA) returns the maximum value for the data in column B. The MATCH function returns the position of this value in the DATA range, in this case the resulting number will represent one row less than the actual row which contains the data, because of the existence of the heading cell. The OFFSET function returns the value of a cell which has a stated relationship to cell A1. In this case it is the MATCH number of rows down from A1 and in the same column.
You can place this formula in any cell in the worksheet - just ensure you format the cell to a date format so you can read the resulting date.
The reader's second question is this: "Using the same data as described above, make a chart, using the values in column A as the X-axis and the values in column B as the Y-axis. Define the data source for the chart in such a way, that when more date/value pairs are added to the sheet Test, the chart is automatically extended to encompass them - without a need for manual editing of the data source of the chart to include the new values."
The solution to this problem builds on the solution to the previous one. Begin by repeating the process used above to name the cells in column A, in this case call the range DATEDATA and name it using this Reference:
Create a chart for your data as you would normally do. Then, with the chart selected, choose Chart, Source Data. In the Values area type =sheet1!data and in the Category (X) axis labels area type =sheet1!datedata and click OK.
The ranges which the chart is based on will now expand as new date/value data pairs are added to the sheet.
The formulae for naming a range of cells so that the range will expand and contract when data is added to or removed from it, and the method of altering a chart series to use these ranges, are not my own but are based on solutions offered by John Walkenbach in his excellent book Microsoft Excel 2000 Formulas. This book would make a welcome addition to any Excel user's library.
Finally, reader Terry Farrell offers an alternative to the recent solution for placing an Excel filename and path in Excel's title bar and which uses the little seen Office Web Address button. Terry recommends you add this to your Menu Bar by right clicking the Menu Bar and choosing Customize, Commands tab. From the Categories list choose Web then click and drag the Address tool to the far right of your Menu Bar. Use your mouse to drag its right hand border so it stretches to the full remaining width of the Menu Bar. When you do this, the name and path to the current file is displayed clearly on the Menu Bar. Nice idea Terry!
EXCEL WORKBOOK NAME IN TITLE BAR
Playing around in the Microsoft Knowledge Base recently, I found instructions for some simple macros to add the workbook filename and path to the Excel title bar. The macros themselves do little more than bring up the appropriate Save or Open dialog and then, when you've finished with the dialog, the file's name and path are added to the title bar using this piece of code:
Application.ActiveWindow.Caption = ActiveWorkbook.FullName
You'll find everything in article number Q272717 so, when you've finished reading what I've got to say, dive over to the Knowledge base at
and check it out.
There are a couple of things to consider and the first is that, in spite of there being multiple options suggested for running the macros, there's only one which makes any real sense. This is the suggestion to save them in a file that's opened automatically whenever you open Excel and to assign the macros to your existing Save and Open toolbar buttons and to your File menu's Open, Save, and Save As
However, a word of warning. If you do alter your toolbar buttons and file menu options to run the macros and, if you lose your personal.xls file, none of these buttons or menu options will work anymore. In this case you'll need to remove the macro assignment from the buttons by reversing
the process of assigning them. While the Knowledge Base article suggests you add the code
to just any file and save it in your XLStart folder, I prefer to use the Personal Workbook, Personal.xls, which Excel will create for you if you don't have it already. Check if you have personal.xls by choosing Window, Unhide. If it's there, unhide it and if not, or if Unhide is grayed
out, then you can quickly get Excel to create the file for you.
Create your personal.xls file by choosing Tools, Macro, Record New Macro, type a name for the macro (anything will do as it's just a way to create the file) and, from the Store Macro In dropdown list choose Personal Macro Workbook and click Ok. Click in a cell on the worksheet to select
it, which results in something being recorded, then choose Tools, Macro, Stop Recording. Voila! you have a Personal.xls workbook so go ahead and unhide it using Windows, Unhide.
Follow the instructions in the Knowledge base article and add the code to a module in your Personal.xls workbook.
Then Hide personal.xls again by activating it and choose Window, Hide. This makes the macros available anytime Excel is opened, just remember to answer Yes to the prompt to save Personal.xls when you exit Excel and it will be saved to your XLStart folder automatically. Now you can assign
the macro code to your toolbar buttons and your File menu options.
You'll find some small limitations to the macros, for example, when you open a file from a hyperlink in another workbook or when you open one from the list of last opened files on the file menu the filename and path don't appear. However, in these instances, you can run the macro by
simply hitting the Save button or add another custom toolbar button to run just the one line of code I mentioned above which will have the same result.
PROTECT CELLS IN EXCEL
PROBLEM: In Microsoft Excel you can protect an individual sheet, but how do you protect or unprotect individual cells?
SOLUTION: To protect a cell's contents, the sheet it lives on must be protected: Tools, Protection, Protect Sheet; check the Contents box. By default all cells in a sheet are protected (locked). So the trick is to first unprotect (unlock) just the cells you want to be able to edit, then protect the sheet since by default all remaining cells are locked. To unlock an individual cell/range select it, then select Format, Cells, click the Protection tab, clear the Locked check box and click OK. Only unlocked cells can be edited. By pressing the Tab key you can move from one unlocked cell to the next, making data entry a snap and avoiding accidental edits to formulas.
Create a Macro that will work in all workbooks like
When you save a macro in Word, it is saved in Normal.dot, and is available for all Word documents. On the other hand, when you save an Excel macro, it works only in that worksheet. Right? Not necessarlily, there a way to make Excel macros available to all worksheets.
Here is how: You can create a worksheet to hold all the macros and then have that worksheet always run when you open Excel. To do this, run Windows Explorer and go to c:\Program Files\Microsoft Office\Office\Xlstart (assuming you used the default location when you installed Office). Right-click in the right pane of Xlstart and choose New, Text Document. Name your new document Personal.xls and press Enter. When you're asked if you want to change the extension, click Yes.
Now, double-click your new file to open it in Excel. Next, choose Window, Hide and then choose File, Exit. When asked if you want to save the Personal macro workbook, click Yes. The next time you run Excel, Personal.xls will run hidden.
To store your macros in Personal.xls, you will have to choose Window, Unhide to unhide Personal.xls. After you finish storing your macros, choose Window, Hide to hide Personal.xls again.
ERASING EXCEL CELL CONTENTS WITH THE MOUSE
When you have your hand on the mouse and don't want to reach over to press the Delete key to erase the contents in Excel cells, simply select the cells that contain the data you want to erase. Now, use the handle and drag backward over the cells. The cells will turn gray as you do this. When you release the mouse button, the cell content is gone. This operation is exactly the opposite of dragging the handle to AutoFill cells.
CLEARING AN EXCEL CELL
Some Excel users get into the habit of just pressing Space to clear a selected cell. However, this is a habit you ought not get into, because some macros might have a problem if they encounter a space in a cell.
The best way to clear a cell is to select the cell and choose Edit, Clear. When the Clear submenu opens, choose All, Formats, Contents, or Comments.
If you need to clear only the contents, select the cell and press Delete.
USING AUTOCOMPLETE IN EXCEL WORKSHEETS
You can use AutoComplete in Excel worksheets to ease your load with data entry. For example, if you type
in one cell, then type
in another cell, Excel completes the entry and places
in the new cell.
If this doesn't work for you, you (or someone) may have turned off AutoComplete. To turn on this feature, choose Tools, Options. When the Options dialog box opens, click the Edit tab. Now select the check box labeled Enable AutoComplete For Cell Values and click OK to close the dialog box and turn on AutoComplete.
USING FORMAT PAINTER IN EXCEL WORKSHEETS
We often talk about how to use the Format Painter in Word documents, but sometimes we seem to forget that Excel has a Format Painter, too. This comes in very handy when you need to use the same format at several locations in your worksheet.
To see how Format Painter works, open a blank worksheet and click cell A1. Type
and press Enter. Move to cell C1, type
and press Enter.
Now click cell A1 again to select it. Next click the arrow at the right side of the Font Color button (its icon is a bucket of paint). Select a new color from the color list. Click the Format Painter button now, and then click cell C1. The data in C1 assumes the color of the data in cell A1.
RANGE NAME RULES
What are the rules governing a range name in Excel?
The first character of a range name must be either an underscore character or a letter, and the character count maximum is 255. Each character beyond the first can be either a period, underscore, letter, or number. This means that spaces aren't allowed, but you can separate words in a name with periods or underscores, like this: "Profit_Per_Unit" or "Profit.Per.Unit". You can use mixed case, but Excel doesn't differentiate names based on case, so if you have a range name "Profit" and create a new one "PROFIT," the second name replaces the first. A name can't be the same as a cell reference, so "A1" and "R2D2" are invalid names. Bug alert: Although the Name Box displays range names as long as 255 characters, if a name contains more than 253 characters, when you select it nothing happens.
NCREASE YOUR EXCEL FLUENCY
What's the fastest way to create a named range in Excel?
First select the cell or range of cells you want to name. Then click on the "Name Box" control. This control is located in the upper-left area of the worksheet display (when you hover your mouse over it the tooltip reads "Name Box"). It is a combo box control that sits above the grid's display of column headers (directly above the "A" column). In a new worksheet it always reads "A1." Once you've selected the Name Box, type the desired name and press Enter.
SLANTED COLUMN HEADINGS IN EXCEL
In the days when people made spreadsheets by hand, you would often see a slanted column heading used to save space and enhance the spreadsheet's appearance. It's very easy to do the same thing in an Excel worksheet. All you have to do is select the cell or block of cells that contains the header text and choose Format, Cells. When the Format Cells dialog box opens, click the Alignment tab. Now use the Degrees spin box to set the direction and amount of slant you want for the headings. When you finish, click OK to close the dialog box and apply your new text setting."
DRAGGING FROM EXCEL TO WORD
You need to drag a block of selected cells from an Excel worksheet to a Word document. Follow these instructions, and you'll never accidentally deselect the block:
First select the block of cells in Excel. Now move the mouse pointer over an edge of the selected block. When the pointer turns to a white arrow, press the left mouse button and drag the selection to your Word document.
By the way, this method moves the cells to the Word document. If you want to COPY the cells instead, move the mouse pointer over the edge; when it turns to a white arrow, press Ctrl, then press the left mouse button to drag the selection to the Word document.
HIDE DATA IN EXCEL WORKSHEETS
Sometimes you may need to hide data in a particular cell in an Excel worksheet. Let's say you have some data in cell C5 you would like to hide from the casual viewer. Click cell C5 to select it, then choose Format, Cells. When the Format Cells dialog box opens, click the Numbers tab (if necessary), then select Custom from the Category list. Now double-click the Type entry box and type three semicolons:
Click OK to close the dialog box and accept your new formatting.
At this point, the data in cell C5 disappears. It's still there and will work in calculations, but it isn't visible. If you need to check the data, just click the blank cell and the contents appear in the Formula entry box.
SAVE MACROS TO RUN IN ALL EXCEL WORKSHEETS
When you save a macro in Word, it gets saved in Normal.dot and is available for all Word documents. However, when you save an Excel macro, it only works in that worksheet. Is there a way to make Excel macros available to all worksheets? Yes, there is.
You can create a worksheet to hold all the macros and have that worksheet always run when you open Excel. To do this, run Windows Explorer and go to c:\Program Files\Microsoft Office\Office\Xlstart (assuming you used the default location when you installed Office).
Right-click the right pane of Xlstart and choose New, Text Document. Name your new document personal.xls and press Enter. When asked if you want to change the extension, click Yes.
Now double-click your new file to open it in Excel. Next, choose Window, Hide and then choose File, Exit. When asked if you want to save the Personal macro workbook, click Yes. The next time you run Excel, personal.xls will run but remain hidden.
To store your macros in personal.xls, choose Window, Unhide to unhide personal.xls. After you finish storing your macros, choose Window, Hide to hide personal.xls again.
SOFTWARE SECRET: INSTANT HEADERS IN EXCEL
PROBLEM: You regularly create column headers in Microsoft Excel using the days of the week, but you're tired of typing these in manually each time.
SOLUTION: First, open a new worksheet and type "Monday" (without the quotes) in A1. Make sure A1 remains the active cell. Next, position your mouse pointer on the cell's "fill handle" (the tiny black square in the lower right corner of the active cell). The mouse pointer should turn into a black cross-hair shape. Left-click and drag the fill handle one cell to the right-- don't release the mouse yet--and you'll see a tooltip reveal that the next cell will be filled with "Tuesday." You can drag over to the right as far as you need the day series to run. The series fills in once you release the mouse.
ADD SOME FORMATTING TO YOUR EXCEL PRINTOUTS
Excel doesn't print grid lines unless you ask it to do so. However, sometimes a worksheet needs some lines to make it easier to read. In such a case, simply convert the data area to a table.
All you have to do is select the data you want to format and choose Format, AutoFormat. When the AutoFormat dialog box opens, select the type of formatting you want to apply, then click OK to close the dialog box and apply your selection.
AutoFormat does a great job of sprucing up your worksheets. If you use a black-and-white laser printer, you should select one of the simpler black-and-white formats. Otherwise, your printout may look too dark and cluttered. Even if you have a color printer, some of the more colorful formats can prove difficult to read.
ERASE EXCEL CELL CONTENTS WITH THE MOUSE
This tip is handy when you have your hand on the mouse and don't want to reach over to press the Delete key to erase certain cell contents. Here's how it works:
Select the cells that contain the data you want to erase. Now use the handle to drag backward over the cells. The cells turn gray as you do this. When you release the mouse button, the cell contents disappear.
This operation is exactly the opposite of dragging the handle to AutoFill cells.
Return to Page Index
SET DECIMAL PLACES IN EXCEL
When you set an entire column to one decimal place, you can be sure that Excel will always round the numbers consistently. Try this example.
Enter the following into cell A1:
Then choose Format, Cells, and set the decimal places to 1. Excel will display 23.6. If you enter
Excel will display 23.5. Excel rounds UP if the digit is 5 or greater, and DOWN if the digit is less than 5.
As far as calculations go, Excel retains its standard accuracy regardless of the number of decimal places you choose to display. To check this out, enter the following numbers respectively into cells A1 and A2:
Into cell A4, enter
Now select A4 and choose Format, Cells. When the Format Cells dialog box opens, click Number and enter three decimal places. Click OK to apply your settings and close the dialog box. Cell A4 will display 43.703.
Select cells A1 through A2 and choose Format, Cells to set the decimal places to one. Click OK, and cell A4 will still display 43.703 even though cells A1 and A2 now display 23.5 and 20.2.
Return to Page Index