Opening the vault on Excel, part 3

Neale Blackwood CPA began writing his monthly Excel Yourself column in 2002, before the articles and their popular accompanying videos were available online.

Blackwood is revisiting some of his early articles – reviewing information and adding newer, easier solutions or built-in functions, in addition to supplementary videos. Here is the third instalment in the “Excel Vault” series, referencing articles from April and May 2003.

I answered four questions in April and May 2003.

1. I want to sort in a specific order that isn’t alphabetic or numeric. Is this possible in Excel?

Yes, Excel has a feature called Custom Lists, which allow you to sort in a custom order. You need to create a list of the entries in the order that you wish to sort. Then, save that list as a Custom List. You can use that Custom List as the sort order.

In most cases, you already have the list existing in a sheet somewhere. Select the list and then click the File ribbon tab and click Options (bottom, left-hand side). Click the Advanced option (left-hand side) and scroll down to the bottom using the scroll bar to the right of the dialog box.

Click on the Edit Custom Lists button as per Figure 1.

Figure 1.

The dialog box (Figure 2) that displays has the built-in Custom Lists on the left. You can add your own list.

Figure 2.

Click the Import button and the list will appear on the right as per Figure 3.

Figure 3.

Click OK and OK again to save the Custom List.

Note: The Custom List is saved on your PC/laptop. If you need to use the list on another computer, you must create it on the other computer as well. You only create it once on each computer.

If you type the first entry from the list in a cell and use the Fill Handle (black cross bottom, right-hand corner of the cell) to drag it, Excel will populate the other entries from the Custom List.

To see how you can use the Custom List to sort a list, use the Fill Handle to drag the list down so that the entries are duplicated multiple times.

Right click the longer list, choose Sort, and then Custom Sort – see Figure 4.

Figure 4.

In the Sort dialog box that opens (Figure 5), click in the Order drop-down (on the right) and choose Custom List.

Figure 5.

In the next dialog box that opens (Figure 6), choose the list you want to use, click OK and then OK again. Job done.

Figure 6.

2. Is there a quick way to enter today’s date in a cell?

Yes, the easiest way to enter today’s date in the current cell is to press Ctrl + ; (see Figure 7). This is a data entry of today’s date and it won’t change.

Figure 7.

If you need today’s date to always be visible and update whenever the file is opened, then you can use the TODAY function. It updates whenever the file is calculated – see Figure 8.

Figure 8.

3. I've seen a drop-down list within a cell. How is that done?

To create a drop-down list in a cell, you need to use Data Validation, which is on the Data ribbon tab – see Figure 9.

Figure 9.

A shortcut to open the Data Validation screen is Alt A V V, pressed in sequence, not held down; see Figure 10.

Figure 10.

From the Allow drop-down, select List. Click in the Source box and navigate to wherever the list is and select the list – see Figure 11.

Figure 11.

If you only need a few entries, you can type them directly into the Source box. You don’t need to use quotation marks, but you must separate each item with a comma – see Figure 12.

Figure 12.

4. Is there a way to put a note in a cell formula?

Yes, Excel has a function you can use to insert a note inside a formula.

The N function was included in Excel for compatibility to older spreadsheets. We can use it to include a note. When N has text between its brackets, it returns a zero. You can add the function to any existing calculation formula and not change the outcome. Two examples of the N function are shown in Figure 13.

Figure 13.

An alternative to a note in a formula is Excel’s cell Comment system. This system has changed in the most recent versions, and it allows you to add a comment to a cell. In the latest versions of Excel, Comments have been renamed to Notes and Comments, and now provide a threaded commentary instead of a single comment.

To add a Comment (Note) to a cell, press Shift + F2 (Figure 14).

Figure 14.

This shortcut also edits any existing Comments (Notes). Cells that have Comments (Notes) display a small red triangle in the top right corner of the cell – as per Figure 15.

Figure 15.

The companion Excel file will go into more detail to demonstrate these techniques.


Like what you're reading? Enter your email to receive the fortnightly INTHEBLACK e-newsletter.

Recommended for You

READ MORE

Opening the vault on Excel, part 2


READ MORE
Four excel questions have been reviewed and updated with newer easier solutions.

Opening the Excel Yourself vault


READ MORE
These are some of the best Excel updates for accountants

Best Excel updates for accountants


September 2019
September 2019

Read the September 2019 issue of INTHEBLACK magazine.

Each month we select the must-reads from the current issue of INTHEBLACK. Read more now.

CONTENTS