Change the Number of Documents

My Recent Documents List was showing 17 files, and I guess that’s the default number, since I don’t remember changing it. Instead of leaving the default setting, you can show more or fewer files.

To change the setting:

1. Click the Office Button, then click the Excel Options button
2. Click the Advanced category
3. In the Display section, change the number for Show this number of Recent Documents.
4. Click OK, to close the Excel Options window.

The maximum number of files is 50, but that many might not show unless you have a really tall monitor.

Change the Setting in Excel 2003

In Excel 2003, the maximum number of files you can show in the list is 9. To change the setting:

1. On the Tools menu, click Options.
2. On the General tab, change the number for the Recently Used File List.
3. Click OK, to close the Option dialog box.

Clear the Recent Documents List

In either version of Excel, you can clear the list, by changing the number to zero. In Excel 2003, you can also remove the check mark from the Recently Used File List setting.

After the list is cleared, you can change the setting to a higher number, to start building the list again.

Pin Items to the List

Normally, the older items in the list drop off the bottom, as new files are opened. In Excel 2007, there’s a push pin icon at the right of each file name. To keep a file on the list, click that push pin, to activate it.

This is another one of the Excel 2007 features that I didn’t notice until recently. (Maybe that’s why it’s call the Recent Documents list!) Now I use it quite often, to “stick” files that I’m working with for a few days.

In the screen shot below, the ProjectWorkCurrent.xlsm file is pinned to the list. Instead of the flat grey push pin, there’s a vertical green push pin.

That file will work it’s way down the list, if it’s not opened for a while, but it will stick to the list, and won’t drop off.

_____________

Create Hyperlinks By Dragging

Well, forget all that typing — here’s a method that’s even easier. Instead of typing, you can drag cells to create hyperlinks.

In this example, there’s a table of contents sheet (TOC), a Sales sheet, and an Expenses sheet. We’ll create a hyperlink to the Sales sheet, on the TOC sheet.

1. First, save the workbook, if you haven’t done so already. If you’re in a new workbook, that hasn’t been saved, this technique won’t work.
2. Next, select a cell that you want to link to. In the screen shot below, the Sales sheet is activated and cell A1 is selected.
3. Point to the selected cell’s border, and press the right mouse button.

1. We want to drag the cell to the TOC worksheet. Press the Alt key on the keyboard, and drag the cell over the TOC sheet tab.

1. The TOC sheet will be activated, and you can release the Alt key.
2. Drag to cell B4, and release the right mouse button
3. In the popup menu that appears, click Create Hyperlink Here

A hyperlink is automatically created, using the text from the Sales Report cell that you dragged.

To go to the Sales sheet, click the Sales Report hyperlink.

Watch the Create Hyperlinks Video

To see the steps performed, you can watch this short Excel tutorial video.

______________

The PUP sale is Tuesday, November 3, from 11:00 am to 1:00 pm U.S. Eastern Daylight Time (that’s 4:00 pm - 6:00 pm GMT).

Go to his Special 2-Hour Sale Order Form to buy either version:

• PUP v7: $7.77 (normally $40.00)
• PUP v6: $6.66 (normally $39.95)
• The complete VBA source code is available for an additional $20.00

If you’d like to see the PUP utility before you buy it, you can download a trial version.

PUP Features

John sent me a complimentary copy of PUP v7 last year, and I reviewed it here. I still highly recommend it, so if you’ve ever thought about buying it, this is your opportunity.

Even if you use just a few of its many features, you’ll get more than your money’s worth. Where else can you get a Table of Contents creator, Change Case tool, Dice Game and tons of other features, for under $8?

How Do You Price Software?

If you create an Excel utility and offer it for sale, how do you decide what to charge? Do you check out the competition and price your utility in the same range? Do you crunch a pile of numbers in Excel, and base your price on the results?

To help you with pricing, you can download a free pdf file of the book Don’t Just Roll the Dice: Usefully Short Guide to Software Pricing, by Neil Davidson, of Red Gate Software. If you prefer a physical copy of the book, you can buy it on Amazon.

The book explores the economics and psychology of pricing, and the author cites his own experiences (good and bad) with software pricing. It’s easy to read, with clear explanations and examples, and ends with a product pricing checklist. It also has this sensible advice: “Practice trumps theory. Try out your pricing and see what happens.”

______________

This week, while working on a client’s files, I wanted to review some calculations in a price list. The top 15 rows are headings for the printed sheet, then there’s a row of column headings for the price calculations. Below that are hundreds of rows with prices. Here’s a simplified version of the worksheet.

A Frozen Wasteland

I wanted to see those column headings, and as much of the price list as possible. However, if I selected row 18, and froze the worksheet, I’d be stuck with a couple of inches of wasted space, with all the headings visible at the top.

Instead of freezing, I could drag the split bar down, to split the screen, and create a small section at the top. Next, I could scroll the column headings into view in the top section of the screen.

A Splitting Headache

I’m not sure why, but I don’t use split windows too often. Anyway, as I played with the scroll bar, and thought about splitting the window, I accidentally froze the panes. (Trust me, it’s easier to do that in Excel 2003 than in Excel 2007.) When this happened, row 17 was at the top of the window, and row 18 was selected.

Miraculously, row 17 was frozen at the top of the screen, and all the rows above it were out of sight. So, instead of wasting 2 inches of space, only 1/4 inch was used, and I could see more of the price list.

Why didn’t I know about that before? Or maybe I did know it, many years ago, and forgot about it.

So, a new world of freezing opportunities has opened for me, and not just because the Canadian winter is just around the corner.

The Downside

The only downside that I can see is that my client might think the first 16 rows have been hidden, and using the Unhide command won’t make them visible. I’ll have to remember to unfreeze when I’m finished working.

So what did you learn about Excel this week? Something less embarrassing than my lesson, I hope!

Excel Giveaway Reminder

On Wednesday, I announced the prize winners for the Very Scary Fall Giveaway for Excel Nerds! Please email me at ddalgleish @ contextures.com if you’re a prize winner, and haven’t yet claimed your prize. The deadline is 5 PM (Eastern time zone) on Monday November 9, 2009, or your prize will be forfeited.

________

• Patrick O’Beirne of Systems Modelling Ltd
• Jan Karel Pieterse, of JKP Application Development Services
• Charley Kyd of ExcelUser, Inc.
• John Walkenbach of J-Walk & Associates, Inc.
• Jon Peltier, of Peltier Technical Services
• Chandoo of Pointy Haired Dilbert
• Matt Kennedy of Apress

A Bonus Prize for Every Participant

As a bonus for everyone who entered the giveaway, Patrick O’Beirne has an entertaining pdf file — “The Devil’s guide to creating spreadsheets.” If you’d like a copy, send me an email at ddalgleish @ contextures.com and I’ll email you a link to the download.

And the Winners Are…

I did a random draw of prizes and entries, using the macro that I created for the Summer Giveaway for Excel Nerds. You can see the Summer video there, and download the macro sample file.

After the numbers were selected, I used VLOOKUP formulas to pull the prize names and winner names from the original lists of numbered prizes and entries. Congratulations to all the winners! Here’s the list:

Collect Your Prize

I’ll send an email to all the winners today, with instructions for claiming your prize. If you’re on the list of winners, and don’t receive an email by end of today, please let me know at ddalgleish @ contextures.com or add a comment here.

Please reply by 5 PM (Eastern time zone) on Monday November 9, 2009, or your prize will be forfeited. 

______________

My Horror Story

One of my Excel horror stories involves sheet names. I set up a client’s workbook with pre-formatted data entry sheets, so sales managers could plan their annual product promotions. They would rename the sheets while working, to make it easier to navigate the completed workbook.

On a hidden summary sheet, I added formulas to calculate the sheet names. Then, INDIRECT formulas pulled data from specific cells on each sheet, and other formulas created grand totals. At the front of the workbook, the summary data was displayed in a monthly calendar, for sales managers to review. It was a work of art!

The Scary Phone Call

Everything worked well in testing, so we distributed the files to all the sales managers, and they started filling in their data. The next day, the phone rang – some of the workbooks were “broken.” Budget deadlines were looming, and the sales managers with broken files were in a panic. They sent me a couple of problem files, so I could figure out what was wrong.

On the Summary sheet, some of the formulas were working correctly, but others showed #REF! errors. Comparing the good and bad sheets, I couldn’t see any problems with the data that had been entered.

Finally, after checking a few of the problem sheets, I spotted a similarity. All of them included an apostrophe in the sheet name! I removed the apostrophes, and the problem was solved. All the data showed up in the summary sheets, and the world was in harmony once again.

Sheet Naming Rules

I hadn’t anticipated that problem, since I never use apostrophes in sheet names. They’re valid characters for a sheet name, but maybe they shouldn’t be.

It’s hard to find the sheet naming rules in Excel’s help, but you may have seen an error message that lists them.

1. The name can’t be more than 31 characters, and you can’t leave the sheet tab blank 

1. Only a few characters are invalid:

  : \ / ? * [ ]

Sheet Naming Suggestions

In addition to those rules, I have a couple of guidelines of my own.

1. Use only letters, numbers and underscores in sheet names. Sometimes I have to use a space character, if a client requests specific sheet names, but I try to avoid it. For example, I’d use SalesData or Sales_Data, not Sales Data.
2. Use different names for sheets and named ranges, to avoid confusion.

Your Sheet Naming Rules

• What kind of names do you use for worksheets?
• Any characters that you avoid or problems you’ve run into?

____________

In the annoying, but not scary category, I’ve been entering network paths in Excel, and they turn into hyperlinks when I press Enter. Sometimes that’s a helpful feature, but in this case it makes it hard to go back and edit the cell.

Turn Off Hyperlink Creation

If you don’t want hyperlinks created automatically, you can turn the feature off.

To turn the hyperlink option off in Excel 2007:

• Click the Office Button, then click Excel Options
• Click the Proofing category, and click the AutoCorrect Options button
• Select the AutoFormat As You Type tab
• Remove the check mark from Internet and network paths with hyperlinks
• Click OK, twice, to close the dialog boxes

To turn the hyperlink option off in Excel 2002 or Excel 2003:

• On the Tools menu, choose AutoCorrect Options
• Select the AutoFormat as you type tab
• Remove the check mark from Internet and network paths with hyperlinks
• Click OK

Remove a Hyperlink Manually

If you want to leave the hyperlink feature on, you can undo the hyperlink immediately after it’s created.

To manually remove the hyperlink:

• Type the email address and press Enter
• Immediately, press Ctrl+Z.

This is a shortcut for Undo, and will convert the hyperlink back to text.

Remove Selected Hyperlinks Programmatically

To change a group of cells that contain hyperlinks, you can use the following code.  It deletes all the hyperlinks in the selected cells.

Sub delHyperlinks()
Dim myCell As Range
For Each myCell In Selection
   myCell.Hyperlinks.Delete
Next myCell
End Sub

__________________

To enter, write an original (and suitable for work!) comment below, describing either:

• A) an Excel related costume (real or imagined). For example, here’s a picture of my daughter and son, many years ago, ready for some trick-or-treating. My daughter was dressed as The Formula Doctor and my son was an early prototype of an Excel Web app. I’m sure they won’t mind me posting this picture, but if you see them, maybe you shouldn’t mention it.

OR

• B) your scariest Excel related experience. I’m sure you’ve had horrifying days, buried under spreadsheets, and workbooks that come back to haunt you. Tell the other readers about one of those gruesome times. Remember, misery loves company. We’ll feel sorry for you, as soon as we stop laughing.

The submission deadline is 12:00 noon (Eastern Daylight Time) on Tuesday, October 27, 2009. Winners will be announced here on October 28th.

Note: You can make as many comments as you like, but only the first one will count as your entry.

The Ghastly Goodies

Some scarily smart Excel authors, developers and publishers have contributed a monstrous mound of books and utilities for me to give away as treats.

Thanks to all the contributors – Patrick “Ogre” O’Beirne, Jan Karel “The Creeper” Pieterse, Charley “Crypt-Kicker” Kyd, John “Walking Dead” Walkenbach, Jon “Petrifier” Peltier, “Chiller” Chandoo and Matt “Killer” Kennedy.

Systems Modelling Ltd

From Patrick O’Beirne of Systems Modelling Ltd

• one copy of XLTest: Spreadsheet testing and auditing add-in — “XLTest helps you to check the integrity of your spreadsheets far more quickly than with tedious cell-by-cell inspection.”

• PATRICK’S TREAT for everyone who enters, a pdf file — “The Devil’s guide to creating spreadsheets” (I’ll email you a link to the download.)

JKP Application Development Services

From Jan Karel Pieterse, of JKP Application Development Services:

• one copy of Professional Excel Development, 2nd Edition
• one copy of Excel 2007 VBA Programming For Dummies
• one USB key (1 GB) with a set of tools like Name Manager and a free RefTreeAnalyser license (1 copy of this prize is available)

ExcelUser, Inc.

From Charley Kyd of ExcelUser, Inc.

• one copy of IncSight® DB: Excel Dashboard Templates Linked to Data — “Set up your first Excel dashboard report in less than an hour. Add any number of reports. You can update your reports in seconds because they’re linked to an Excel database.”
• The winner can select one of the two versions available:
  • IncSight® DB for Excel 2007
  • IncSight® DB for Excel 2003

J-Walk & Associates, Inc.

From John Walkenbach of J-Walk & Associates, Inc.

• one copy of the Power Utility Pak (PUP). — “Power Utility Pak Version 6 (PUP v6) is a useful collection of add-ins that brings significant new functionality to Excel. When PUP is installed, you can do things with Excel that you never thought were possible.”
• The winner can select one of the two versions available:
  • Power Utility Pak v6 for Excel 2000, Excel 2002 and Excel 2003
  • Power Utility Pak v7 for Excel 2007

Peltier Technical Services

From Jon Peltier, of Peltier Technical Services:

• one copy of a PTS Charting Utility – “When installed the utilities provide buttons on the Excel menu or ribbon that allow you to select a regular worksheet range and create a specialized and customized Excel chart.”
• The winner can select one from the following:
  • PTS Waterfall Chart Utility
  • PTS Box and Whisker Chart Utility
  • PTS Cluster Stack Utility
  • PTS Marimekko Chart Utility
  • PTS Dot Plot Utility

Pointy Haired Dilbert

From Chandoo of Pointy Haired Dilbert

• one copy of the newly released Project Management Bundle of templates for Excel — “The bundle contains 24 highly reusable excel templates for project planning, task management, timesheets, issue tracking, risk logging, status reporting and more.”
• The winner can select one of the two versions available:
  • Project Management Bundle for Excel 2007
  • Project Management Bundle for Excel 2003

Apress Publishers

From Matt Kennedy of Apress, 2 prizes – e-books that you can download from the Apress website:

• Pro Excel 2007 VBA, by Jim DeMarco (e-book)
• Pro Excel Financial Modeling: Building Models for Technology Startups, by Tom Y. Sawyer (e-book)

Contextures

And finally, from Contextures – 3 prizes. The 3 winners can each select one of my pivot table books:

• Beginning Pivot Tables in Excel 2007
• Excel 2007 PivotTables Recipes
• Excel Pivot Tables Recipe Book

The Rigid Rules

• To enter, submit an original (and suitable for work!) comment below, describing either an Excel-related costume or horrifying Excel-related experience
• The comment must be submitted before the deadline of 12:00 noon (Eastern Daylight Time) on Tuesday, October 27, 2009
• One entry per person – any additional entries will be deleted from the draw
• A random draw will select each prize and its winner. No substitution of prizes.
• Winners will be notified by email, so please provide a valid email address. This will not be publicly visible, but may be shared with the contest sponsors, so they can contact prize winners to arrange delivery.
• Physical prizes will be shipped, postage paid, but taxes or other charges (if any) will be the responsibility of the recipient.

______________

Even though all my workbooks are error free I accepted his offer. Patrick sent the add-in, instructions, and a couple of sample files. Here’s a screen shot of the first sample. If your files look like this, you might need more help than this add-in can provide!

The Add-In Commands

After I installed the add-in, a drop-down menu appeared on the Ribbon, as well as all the icons. I’d rather have just the drop-down menu, so maybe there’s a way to turn off one or the other.

Key Shortcuts

The add-in adds 8 key shortcuts that you can see in the Ribbon, and in the popup menu that appears when you right-click a cell. Since the add-in features these shortcuts, let’s look at a few of those first.

Copy Formula: This is handy if you want to move a formula without changing the relative references. It’s quicker than copying the formula from the Formula Bar, and pasting it into another cell, which is the way I’d do it without this shortcut.

Operate on Selection: Select non-contiguous cells, in multiple rows and columns, then move or copy them to a different location. If you try this in Excel, you’ll get an error, so this shortcut could really save you some time and aggravation. To get this to work in Excel 2007, I had to select the top left cell last.

Select Formula Region: Selects all the cells in the current region that have the same formula (in relative R1C1 terms) as the active cell. This helps you see if you’ve entered or updated a formula in all the relevant cells. Excel’s error checking could flag those cells for you, but I usually have that turned off because it clutters up the worksheet.

Jump to Bottom Right: I use Ctrl+End to go to the bottom right, so I’d rather have another feature shortcut here.

Document Your Workbook

The rest of the commands let you test your workbooks for errors, starting with the Start New Test Session command. It opens a dialog box that lets you choose from 4 options for keeping logs, recording settings, opening files and closing open workbooks.

Next, you can document your workbook with the Worksheet Documentation command. Select all the options, or just a few, and list the results in a new workbook or existing one. All the details are reported in a well organized worksheet. Here’s a small section of the report for the demo file.

In Excel 2007, when I selected Number Formats with the other options, the Format Cells dialog box stayed open, and none of the Custom Number Formats were listed in the documentation. Everything else was correctly documented though, including the VBA modules.

Inspect Your Workbook

For me, the main feature in the XL Test add-in is the Detailed Inspection. Instead of spending hours or days combing through your worksheets, click a button and get a report in a few seconds. Again, there were problems with reporting the Number Formats, but I’m sure Patrick can sort that out quickly.

It creates a detailed report, with errors and other problems listed. You can quickly focus on the crucial errors, and get things fixed.

I don’t know what happens if you choose to see errors in separate cells, and there are more errors than columns. Maybe it wraps around, or maybe its head explodes!

Other Tests for Your Workbook

There are several other tests that you can run in the XLTest add-in. For example, colour and document the data validation, conditional formatting or number formats on a worksheet. These test will quickly highlight any cells in a range that are different than their neighbours, and allow you to fix them.

After testing, you can click the add-in command to clear all the fill colour from a worksheet. Since the tests also add rectangle shapes with hyperlinks, it would help if those could also be removed with a single click.

The add-in also has a command for Batch Testing, so you can run all the tests on a workbook, with a single click, instead of running each test individually. The documentation warns of Excel memory problems if you try this on a large workbook.

Additional Features

Test Cases: The XLTest add-in can run a list of tests, and create a report on the results of each test. Use this to ensure that a new version of a workbook works the same as the previous version, except where you have intentionally changed things. The add-in will also convert any existing Scenarios to test cases, so you can run those.

Comparison: With the add-in, you can compare worksheets or workbooks, and create a detailed list of differences. For workbooks, even the VBA code is compared.

Housekeeping: There are several housekeeping features, such as creating a table of contents, unprotecting a sheet, and deleting custom styles.

Functions: The add-in also adds 14 functions to Excel, such as GetFormula, ColorName and FileSize.

Should You Buy the XLTest Add-in?

If you’re an expert programmer, you might have your own code that does error testing, comparison and housekeeping, so you won’t need Patrick’s add-in.

If you don’t have your own code, this add-in would be well worth its purchase price (£199, approx $296 US), in the time you’d save in looking for errors, and other tests. Yes, the add-in is more expensive than many other utilities that I’ve seen. It’s a bargain though, when compared to hiring an Excel programmer or trying to do the testing yourself.

For workbooks that you’ve inherited from colleagues or clients, you might not even know where to begin the error hunt. The XLTest add-in can do most of the detective work for you – it even unprotects and unhides sheets, rows and columns.

And, of course, the real value in XLTest is in finding those critical errors that you didn’t even know you should look for. If the add-in saves your job, it’s priceless!

____________

For example, in this table, row 4 is M7 and row 5 is m7. There’s a different value for each of these in column B. If I do a lookup for m7, I want the result to be 5, not 4.

VLOOKUP Is Not Case Sensitive

You could try a VLOOKUP formula, to find the value for m7, but it’s not case sensitive. In the following screenshot you can see a VLOOKUP formula in the formula bar, and the result of 4 in cell E1.

On the Microsoft website, there’s an article that explains how to perform a case sensitive lookup. One sample formula uses IF and EXACT with VLOOKUP to check the case. In our sample sheet, the suggested formula is:

=IF(EXACT(D1,VLOOKUP(D1,A1:B6,1,FALSE))=TRUE,VLOOKUP(D1,A1:B6,2,FALSE),”No exact match”)

However, this doesn’t work in our sample table, because it stops at the M7, and that’s not an exact match for the lookup value m7.

Case Sensitive INDEX MATCH

The Microsoft article has other sample formulas, including an INDEX MATCH, but they all have the same problem, stopping at the M7 above the m7 value.

Fortunately, a search in Google Groups led me to an array formula posted by my old friend, former Excel MVP Peo Sjoblom. For our table, Peo’s formula would be:

=INDEX(B1:B6,MATCH(1,--EXACT(A1:A6,D1),0))

This is an array formula, so type the formula then press Ctrl+Shift+Enter. Curly brackets will automatically appear at the start and end of the formula.

In the screenshot below you can see the formula, and the correct result of 5, in cell E1. The formula finds an exact, case sensitive match for the lookup value.

More Excel Function Examples

On the Contextures website you can find more examples of the Excel INDEX function and the Excel MATCH function.

__________________