Check Out These Nifty Excel Features

Category: Software

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

Excel: Beyond the Basics

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)

Flash Fill in Excel

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.

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

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

 
Ask Your Computer or Internet Question

  (Enter your question in the box above.)

It's Guaranteed to Make You Smarter...

AskBob Updates: Boost your Internet IQ & solve computer problems.
Get your FREE Subscription!


Email:

Check out other articles in this category:



Link to this article from your site or blog. Just copy and paste from this box:

This article was posted by on 20 Jul 2018


For Fun: Buy Bob a Snickers.

Prev Article:
Geekly Update - 19 July 2018

The Top Twenty
Next Article:
[HOWTO] Get The Lowest Airfares Online

Most recent comments on "Check Out These Nifty Excel Features"

Posted by:

Richard A Battista
20 Jul 2018

Very good article on Excel ... I have a question:
After I enter into Excel all the Birthdays, Anniversaries, etc is there a way I can have Excel send me an email notification when one of those dates arrive?
Thanks,
Dick


Posted by:

Sarah Hogg
20 Jul 2018

I agree we need to spend more time studying the Excel features in detail before we jump in.
Thank you for the link to ExcelRibbon.Tips.net, very useful.


Posted by:

Bill
20 Jul 2018

Dick:
Excel is not an email or a reminder program. You could program a macro to send out emails but you would have to have Excel running and that spreadsheet open for it to work.
There are reminder programs out there that stay running in the background so that they can remind you. I don't know if which ones can send emails.


Posted by:

BZMan
20 Jul 2018

Dick, looks like you need to be using Google calendar. It does what you want after you enter in the dates and set up the reminders. A popup will appear on your computer or phone/tablet. It even does emails.


Posted by:

William
20 Jul 2018

Dick, Outlook can also remind you of those important events.


Posted by:

Dan
20 Jul 2018

Wives are pretty good at it as well. They're also good at reminding you to take out the trash, wash the windows,etc,etc,etc.


Posted by:

Mac 'n' Cheese
20 Jul 2018

I personally enjoyed subscribing to Wyatt's Excel Tips and his Word tips for many months (his paid versions).

His explanations are clear, concise, and well-illustrated. And he sure knows his stuff.

My only frustration is that his material wasn't indexed. For example, he might say something like "We talk about Pivot Tables in another newsletter," but not tell you which one or where to find it.

A year or two ago, I suggested to him that he prepare an annual or twice-yearly index of subjects and offer it to his paid subscribers, but I don't think it was a high priority for him.

I finally cancelled my subscription over the frustration.

However, he many hard-copy reference books available on multiple specific topics for Word and Excel, and I found them quite helpful.

Mac 'n' Cheese


Posted by:

Daniel Wiener
21 Jul 2018

An extremely annoying "feature" of Excel is the way it treats hexadecimal numbers, particularly in conjunction with the HEX2DEC function. When you paste data into Excel it will automatically convert certain hexadecimal numbers to dates (e.g., FEB8 becomes the date 8-Feb) or it will convert them to exponents (e.g., 1E05 becomes 1.00E+5). Different attempts to define the values or cells as text don't solve the problem. So every once in a while you get a garbage value in the midst of your data instead of the expected hexadecimal value.

This is a long-standing problem which many people have complained about. It boggles the imagination that after all these years Microsoft has failed to add a simple option which would allow users to block these automatic conversions.


Posted by:

jumpmanlives
23 Jul 2018

Thanks Bob. Here my to favourite shortcuts:
CTRL + 1 Cell Formatting (ie. Text, Currency)

Right-mouse drag for fill options ie. Fill formatting only or fill with no formatting


Posted by:

BeachBum
06 Aug 2018

Thanks for the tips. you've got a new fan!!!


Post your Comments, Questions or Suggestions

*     *     (* = Required field)

    (Your email address will not be published)
(you may use HTML tags for style)

YES... spelling, punctuation, grammar and proper use of UPPER/lower case are important! And please limit your remarks to 3-4 paragraphs. If you want to see your comment posted, pay attention to these items.

All comments are previewed, and may be edited before posting.

NOTE: Please, post comments on this article ONLY.
If you want to ask a question click here.

Free Tech Support -- Ask Bob Rankin
RSS   Add to My Yahoo!   Feedburner Feed
Subscribe to AskBobRankin Updates: Free Newsletter
Copyright © 2005 - Bob Rankin - All Rights Reserved
Privacy Policy -- See my profile on Google.


Article information: AskBobRankin -- Check Out These Nifty Excel Features (Posted: 20 Jul 2018)
Source: https://askbobrankin.com/check_out_these_nifty_excel_features.html
Copyright © 2005 - Bob Rankin - All Rights Reserved