Wow, You Can Do That With Excel?

Beyond the Basics: Check Out These Nifty Excel Features

More than a dozen major versions of Excel have led to a program packed with sophisticated math functions, but it’s also adept at manipulating text strings such as names and addresses, dates and times, formatting, and other data characteristics that you might not expect in a “number-crunching” program.

Many small businesses use Excel as their primary database and business application development platform, using Excel’s built-in Visual Basic for Applications (VBA) programming language. But you don’t need to be a programming guru, or hire one, to gain big productivity boosts. Excel’s advanced point-and-click functions can help you perform repetitive tasks easily.

If you're an accountant, a church or club secretary, or a casual home user managing household budget and expenses, this article will prove useful to you. Following are three examples of Excel functions that can save time and prevent data-entry errors. Note that some functions are only available in recent versions of Excel.

Tip 1: Flash Fill, repeating data patterns easily (Excel 2013 and later)

Suppose you have a client or members spreadsheet in which there are columns for each person's first and last name. Now you want to add a column that contains the full name. Filling that column manually would be a tedious copy-and-paste operation. But Flash Fill can do most of the work automatically once you give it an example of what you’re trying to do.

To use Flash Fill, create one or more empty columns adjacent to the existing data. Start typing the data you want to see in the empty columns. Flash Fill will look for a pattern in your work, and when it finds one Flash Fill will complete a new entry for you. If that preview looks right, just press Enter and Flash Fill will fill the entire column in the same way, taking data from adjacent rows.

Flash Fill is handy for re-formatting phone numbers and birth dates, as the illustration above shows. It can also extract ZIP Codes from addresses instantly, enabling more efficient filtering and sorting on such values.

Tip 2: Filter and Calculate with Tables (Excel 2010 and later)

Tables do more than apply styles to ranges of data. When you convert a range of data to a table, Excel also applies filters, enables sorting within the table without disturbing the order of other ranges, and adds an easy way to get subtotals, averages, and other statistical functions of columns.

To apply table formatting to a range of data, highlight the range and click on the Insert tab, then click on the Table icon in the Tables group. A window will pop up showing the range you have highlighted; click OK to create the table using the default style.

Now, check the “Total Row” box in the Table Styles group, which is at the far right end of the Design tab. (The Design tab opens automatically when you apply table formatting to a range of cells.) A new “Total” row will be inserted at the bottom of your table; by default, its cells contain the subtotal of numeric values above them.

Each subtotal cell shows a dropdown menu indicator (downward-pointing arrowhead) when you select it. Left-click on that indicator for a list of functions that can be applied to the data above: sum, average, count, etc.

Tip 3: Dropdown Lists of Existing Data

When entering values in a column, press Alt + down-arrow for a dropdown list of unique existing values in the cells above the one you want to fill. Select a value from the list to fill the current cell. This hidden feature can be handy if you have several people with a first name starting “Rob…” in the column and Excel suggests the wrong one when you type “Rob”.

You can also create custom dropdown lists of frequently-entered values to make data entry go faster and more accurately. Create the list of values in a separate worksheet. Select the range and name it using the Name() function.

Now switch back to the data-entry worksheet and select the column to which you want to add a dropdown list.

Click Data > Data Validation on the menu bar. For the Source, select List from the dropdown menu and enter the name you gave to the list of values in the other worksheet.

From now on, a dropdown list of values will be available in the desired column when you press Alt + down-arrow.

The more you explore the intricacies of Excel, the more tricks like these you’ll find to make life easier and more error-free. I also recommend Allen Wyatt’s ExcelTips websites as a superb learning tool for Excel users. If you have Excel 97, Excel 2000, Excel 2002, or Excel 2003, visit Excel.Tips.net. For Excel 2007, 2010, 2013, 2016, or Excel 2019/Excel 365 (the newer ribbon-oriented versions), see ExcelRibbon.Tips.net.

Your thoughts on this topic are welcome. Post your comment or question below…