Normally, Excel allows you to double-click on the divider bar between column heads in order to widen a column. For instance, if you need to widen column C, all you need to do is move the mouse so it points to the divider between column headers for column C and column D; the mouse pointer changes to a double-headed arrow. When you double-click, column C is widened to accommodate the widest information in its cells.

There are times when double-clicking to widen a column won't work, however. Excel won't widen a column in this manner if the worksheet is protected. (The obvious solution is to unprotect the worksheet.)

You also cannot double-click to widen if the column is formatted using the Wrap Text attribute. You can use any different method of widening the column, but you cannot double-click.

Every time you start Excel, it is supposed to open a blank worksheet. However, for some Excel users Excel always opens Personal.xlsb instead of a fresh worksheet.

The Personal.xlsb worksheet is used, most often, to contain macros that you want available whenever you are using Excel. Normally the worksheet is hidden, unless it has been specifically unhidden and then saved.

To solve the problem, just start Excel and when the Personal.xlsb file is visible, display the View tab of the ribbon and click Hide in the Window group. The worksheet goes away, and you should immediately exit Excel. (If you are asked if you want to save your changes, say YES.)

The next time you start Excel, you should no longer see Personal.xlsb because it is hidden. Instead you should see a regular blank worksheet, exactly as you expect.

Excel provides several different ways you can align information from top to bottom (vertically) within a cell. You set the alignment by first selecting the cells you want to format and then displaying the Alignment tab of the Format Cells dialog box. (To display the dialog box, display the Home tab of the ribbon and click the small icon at the bottom-right of the Alignment group.)

On the Alignment tab, use the Vertical drop-down list to make your selection. There are five different alignment options available:

• Top. The information in the cell is situated such that the top line of text appears at the top of the cell.
• Center. Information is centered half-way between the top and bottom borders of the cell.
• Bottom. This is the default vertical alignment. Information is aligned at the bottom of the cell.
• Justify. Text is spread evenly throughout the cell. The information within the cell is wrapped within the column (if necessary), and the row height is adjusted so that all lines fit within the cell and so all information reaches both the top and bottom borders of the cell. Column width is not affected at all.
• Distributed. When selected, text is spread evenly between the top of the cell and the bottom. Effectively, blank space is placed between each line so that the complete cell is filled.
  

The typical way that people apply formatting to cells is to use the tools on the ribbons or to display the Format Cells dialog box and make formatting choices. For those who prefer to not remove their hands from the keyboard Excel provides several handy shortcuts you can use to format your cells.

A common editing task is copying information from one source and pasting it in your workbook. By using a minor variation on pasting, you can actually cause your pasted information to appear as a hyperlink in an Excel worksheet. To paste information in this manner, follow these steps:

1. Select the information to be copied and press Ctrl+C. This copies the information to the Clipboard.
2. Switch to Excel and select the cell where you want the link to appear.
3. Click the down-arrow under the Paste option on the Home tab of the ribbon and then choose Paste As Hyperlink.

The information appears as a hyperlink, and if you click on it, you are taken directly to the spot in the original document from which it was copied.

Excel allows you to define graphical controls within your worksheet. Buttons and other controls are placed in your worksheet by displaying the Developer tab of the ribbon and using the Insert tool in the Controls group.

One of the form controls you can place in your worksheet is called a list box. This is a scrollable list of options from which the user can choose. You might use these in a worksheet if you have a group of options from which you want the user to choose. Let's assume you have a worksheet that provides a price quote and the price is contingent on which colour of widget the user wants. Having the users type the name of a colour is open to error. For instance, they might enter the name of a colour you don't carry, misspell the colour name, or refer to a colour as "gray" when your terminology is "slate." Rather than have them type a colour in, you can present the various colours in a list from which they can choose.

To place a list box control in your worksheet, display the Developer tab of the ribbon, click the Insert tool (in the Controls group), and then click the List Box tool in the Form Controls section. You then use the mouse to define the rectangle that will hold the list box and the scroll bar at the right side of the box.

To use a list box effectively, you must link it to two separate areas on your worksheet. The first is called an input list, the options in the list. The second is the cell link, which contains the currently selected option. To set these areas, follow these steps:

1. Choose the list box control (the one you placed on your worksheet) by clicking on it once.
2. Click on the Properties tool in the Controls group on the Developer tab of the ribbon. Excel displays the Format Control dialog box.
3. Make sure the Control tab is selected.
4. In the Input Range field, supply the name or address of the cell range that contains the options to be included in the list box.
5. In the Cell Link field, specify the cell that will contain the value indicating which option is selected.
6. In the Selection Type portion of the dialog box you can specify how many options in the list can be selected at the same time. When you use list boxes on a regular Excel worksheet, it is important to leave this option set to Single. The other options only have meaning if you are creating a custom dialog box.
7. Click on OK.

You should note that the Cell Link setting specifies a bi-directional relationship between the cell and the control. This means that a change in the selected option will change the value in the cell, but a change in the cell value will also change which option is selected in the list box. The value stored in the linked cell indicates the number of the option selected in the list. Thus, if you have a list of seven options and the second option in the list is selected, the value in the linked cell will be 2.

Microsoft has begun rolling-out Excel’s anticipated XLOOKUP feature to some Office 365 subscribers. While not all Office 365 subscribers yet have access to this feature, if you are an Office 365 subscriber, you can expect receive this feature during 2020.  And once you do gain access to it, you will no doubt want to take full advantage of this great new enhancement to Excel.

XLOOKUP is alternative to VLOOKUP, HLOOKUP, and INDEX. While these three functions will remain in Excel, many users will find XLOOKUP to be simpler, more intuitive, and even more powerful. Some of the key differences between XLOOKUP and other lookup functions include:

• XLOOKUP defaults to an exact match, whereas VLOOKUP and HLOOKUP default to an approximate match.
• With XLOOKUP you do not have to specify a column index number as you do with VLOOKUP or a row index number as you do with HLOOKUP.
• The arrangement of columns and rows does not matter with XLOOKUP because it can look to the left or right when using it as alternative to VLOOKUP. Likewise, it can look above or below when using it as an alternative to HLOOKUP.
• XLOOKUP allows you to specify what happens if your lookup value isn’t found, without having to include an IFERROR

To begin to understand the advantages of working with XLOOKUP, consider the example presented in Figure 1. In this illustration, XLOOKUP is used to find the value from cell H2 in the range of B3 through B15. Keep in mind, this function defaults to an exact match. Once it finds the value it is looking for, it returns the corresponding value from the range D3 through D15. If no match is found, then the formula would return the phrase “Item Not Found.”

Figure 1 - Simple Example of XLOOKUP-based Formula

Now compare the formula in Figure 2 to the one in Figure 1. See how the values to return are in the column to the left of values in the lookup column. This type of formula would not have been possible with a VLOOKUP function.

Figure 2 - XLOOKUP-based Formula Finding Values to the Left of the LOOKUP Range

Not all Office 365 subscribers yet have access to XLOOKUP. However, the impending general release of the new feature is creating a lot of conversation. XLOOKUP is considered by most to be a superior option to VLOOKUP, HLOOKUP, and INDEX. Accordingly, as you gain availability to this terrific new function, you will no doubt want to incorporate it into your Excel toolbox.

To learn more about this and other Excel features, consider some of the Excel-focused learning sessions available from K2E Canada Inc. For information, visit www.k2e.ca/training