Are You Getting The Most Out Of Excel?

Microsoft Excel, now in its 28th year, is the unrivaled king of spreadsheet apps for business. But I’ll wager most people spend less than 28 minutes learning how to use it. As a result, you may be missing out on some nifty features that could help you boost productivity in your business back office…

Excel: Beyond the Basics

Twelve 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; just use Excel’s point-and-click advanced functions to perform repetitive tasks easily.

Following are three examples of Excel functions that can save time and 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 only)

Flash Fill in Excel

Suppose you have a “customers” spreadsheet in which there are columns for each customer’s first and last name. 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.

Total Row in Excel

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 customers named “Bob…” in the column and Excel suggests the wrong one when you type “Bob…”

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, Excel 2010, and Excel 2013 (the newer ribbon-oriented versions), see ExcelRibbon.Tips.net.

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


More Posts about Uncategorized:

  • Are You Getting The Most Out Of Excel?

  • Protecting Against Online Extortion

  • Automating Repetitive Business Tasks

Comments

  1. Mac 'n' Cheese says:

    Woo-hoo! I’ve been using Excel since it first came out–and Lotus 1-2-3 (and VisiCalc) before that, but I didn’t know about these three handy shortcuts. Unfortunately, I haven’t updated since Office 2007. But you’ve given me more reasons to.

    Mac

  2. You really should stop being an ad for MS and respect the major underdog, WordPerfect! I saw a QuattroPro demo that focused on its extra spreadsheet functions, especially running an animai based on the sheet. That was in 1991 in version 4 – they are in V 17 now! Is there anything QP doesn’t do well? How about a comparison of the two instead of shilling for MS?

    • Bob Rankin says:

      I’m still waiting for that fat check from Microsoft… when will they realize all that I’ve done for them? And what’s up, Mr. Google? I mentioned Gmail several times on my blog recently, and still nothing in the mailbox! Sheesh.

      I use Excel, because it’s familiar to me. If you want to write up a comparison of spreadsheet products, please feel free.

  3. I am a member of a small investment group. After 40 years our treasurer is retiring and the group is looking at alternative accounting programs.
    Would Excel be the best choice? We use member value spreadsheets, liquidation sheets, buying/ selling spreadsheets , a general ledger and a transaction journal.
    Thanks for any ideas you might suggest.

Join the Conversation! Leave Your Comment...

*


Free Small Business Tech Support -- The Rankin File
Subscribe to The Rankin File: Free Newsletter
Copyright © 2005 - Bob Rankin - All Rights Reserved
Privacy Policy -- See my profile on Google.