ADD WORDART TO A WORKSHEET

EXCEL 2013 OFFERS AN EXCEL APP STORE

Although developers have been creating VBA add-ins for Excel since 1993, Excel 2013 and Excel 2016 of- fer an Excel App Store featuring JavaScript apps. Right now, the apps are limited to read-only access to a range of cells and will then create some new visualization in an application window.

In Excel 2013, go to Insert, Apps for Office. Many apps are free.

ADD A DROPDOWN TO A CELL

Problem: I need my sales managers to select a product from our company’s product line. All the pricing lookups in the worksheet rely on the product being entered correctly. I find that if I allow my managers to type an entry, they will find too many ways to misspell items. For example, where I may be expecting PDT-960, they are likely to enter PDT 960, 960, and many other variations. If I could offer them a list to select from, they would automatically select the correct spelling of the product.

Strategy: You can easily allow managers to select from a list by using the Data Validation command. It turns out that every cell has a data validation setting to allow any value. You can change this default set- ting:

1. In an out-of-the-way section of the worksheet, type a valid list of values.

2. Select a cell where the person will be entering data and choose Data, Data Validation.

3. Choose the Allow dropdown and change Any Value to List. The check box for In-Cell Dropdown appears and is automatically checked.

4. Point to the range in the Source field. Alternatively, if the list is short, you can skip step 1 and type the list items, separated by commas, in this box. This particular worksheet already has the valid products as the first column of a lookup table used to get prices.

Figure 1397 Specify the location for the list.

5. Optionally, use the Input Message tab of the Data Validation dialog to provide instructions to the sales managers. You can also use the Error Alert tab to display custom text when the sales managers do not select from your list.

Figure 1398 ­Optionally, provide a ToolTip with a note.

6. Click OK to apply the validation.

7. When someone selects the cell, a dropdown will appear, along with your input message. Choose the dropdown arrow, and the managers will be able to select from a list of products.

Figure 1399 Choose from the list.

Additional Details: After you have set up the validation in one cell, you can copy it to other cells. You select the cell and press Ctrl+C to copy. Then you select cells B7:B20 and select Home, Paste dropdown, Paste Special, Validation.

Gotcha: I am always on the lookout for sales managers who know just a little too much about Excel. If a manager were smart enough to delete row 5, he could also delete row 5 of the lookup table off to the right. If you store your list on a hidden sheet with a range name, you can prevent this.

Gotcha: If someone copies a bunch of cells and pastes them over your validated cells in B, the validation will not work. Anyone can get an invalid value in a cell by using Copy and Paste.

CONFIGURE VALIDATION TO “EASE UP”

Problem: I set up a worksheet with data validation to ease the job of the sales managers. One of the managers is entering an order for a brand new product. The product is so new that it does not appear in the product list. Using default Excel list validation, the rep will be nagged and prevented from entering the order for the new product.

Figure 1400 By default, data validation is pretty strict.

You can tell what will happen here. At the next sales conference call, the sales manager will say that he couldn’t enter his $4.5 million order because the lousy spreadsheet wouldn’t let him. As the spreadsheet designer, you will be demoted to manager of the “revenue prevention” department.

Strategy: There are three different settings on the Error Alert tab of the Data Validation dialog. The de- fault is the hard-line version of the message, shown above. This is known as the Stop style of Validation.

On the Error Alert tab of the Validation dropdown, you can change Stop to Warning. With a warning, the person using the spreadsheet is greeted with a dialog box with Yes, No, Cancel, and Help buttons. The default button is No, but people can override and allow the value if they are absolutely sure. You should type a message to indicate this.

4

Figure 1401 Warning is probably the best setting.

When a sales rep enters incorrect data, he will see the message below. Of course, because the message is longer than five words, he will press Enter without reading the message. Because the default button is No, he will then need to choose from the list.

Figure 1402 No is the default button.

The final choice is to set the Error Alert style to Information. This choice is the “ease up” king. The error message defaults to having the OK button selected. You will certainly end up with a lot of invalid data if you use this setting.