Wow, You Can Do That With Excel?

Category: Software

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

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)

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.

Excel is the best-known spreadsheet, but it's not your only option. There are quite a few Excel alternatives, some of which are free! Some of them are traditional locally-installed apps, and some run right in your browser. My article See These Free Microsoft Office Alternatives goes into detail on LibreOffice, Google Docs and several others you can try.

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.

Most Useful Excel Keyboard Shortcuts
Most Useful Excel Keyboard Shortcuts

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, 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…

 
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 19 Jun 2020


For Fun: Buy Bob a Snickers.

Prev Article:
[HOWTO] Inbound Faxes Without a Fax Machine

The Top Twenty
Next Article:
Is Microsoft Edge Taking Over?

Most recent comments on "Wow, You Can Do That With Excel?"

Posted by:

Laurie
19 Jun 2020

I use Excel every day for these types of tasks and more. I strongly suggest getting comfortable with building or writing functions in Excel. It’s not hard at all. And these go a long way to save time when manipulating both numeric and string data. The Internet is full of sites to help you put together a syntax-correct, labor and time saving functions. Excel (and other spreadsheet apps like LibreOffice Calc) do so much to make flat data easy to work with. Obviously, it can’t work well when you need a real relational database, but if you’re not in a position where you need that, then a spreadsheet app like Excel is a great answer. It’s so versatile and time saving.


Posted by:

Neil Hopkins
19 Jun 2020

When Excel begins to use icons for their more complicated functions like @subtotal or pivot, or concatenate, I'll be impressed.
When will they make Excel truly user friendly so that we don't need a 40 hour training course to learn it.


Posted by:

paul
19 Jun 2020

Example of what I have in Excel:

Name and last name
address
phone number

I have all of these in four separate columns and when I print the page I have all these addresses on both sides of the paper. They are all NOT in alphabetical order.
How to place them in alphabetical order.


Posted by:

Frank MacKenzie-Lamb
19 Jun 2020

I've used Excel for years as a home user for creating a budget (I don't like Quicken at all). I enter the various expense categories by name, made one for each month with an extended 5th week if one occurs that month. I plug in what I spent on categories by keeping all my sales slips and paperless bills paid, which created a total for each category at at the end of the month, created a column displaying last months costs, has an over/under budget figure, and finally total monthly amounts in the red or the in the black and have that figure transfer over to the next months so I will be able to see after several months or on the last day of December how I did that year over all. Lots of cell size changes, math formulas used.


Posted by:

Fran
19 Jun 2020

To: Paul -- Hope this helps.
Highlight your data range (all the names, addresses, etc. that you’ve typed)
Data tab >Sort box >Sort by (Last name)
Order A to Z
OK

AND a huge thank you to Bob Rankin for sharing all of your research, etc.


Posted by:

Joel Bergmann
20 Jun 2020

Another Oh So helpful column by Bob Rankin. I just think it's the perfect opportunity to thank Bob for ALL the help he has provided! Many, many thanks Bob!


Posted by:

DBAsteve
20 Jun 2020

Any discussion of spread sheets needs a reference to LANPAR and to VisiCalc.


Posted by:

Beverly
22 Jun 2020

If I could only have one app on my computer it would be Excel. It does everything. If I need text, there is a text box. If I need to sort, you can easily sort. I used to be a guru with Excel but haven't used many of the more difficult functions the past few years so I'm a little rusty now but I do still use it on a daily basis for the simple things.


Posted by:

Marge Teilhaber
23 Jun 2020

I use Excel but don't know what I'm doing. I have Office 365. I have to click "home" to see the basic functions. How to make that always show. It used to but I clicked something that ruined it. All I see is file, home, insert, page layout, formulas, data, review, view, help. I need a tutor!!


Posted by:

Marge Teilhaber
23 Jun 2020

And if someone jumps in to help me, I might not see the reply because to my knowledge there's no email reminder available here that would alert me to a reply and it's only if I remember to check back here that I'll see a reply. Am I missing something?


Posted by:

Neil Bonine
25 Jun 2020

There are many programs available to make a photo collage, but learning them is just another hassle- especially in Word.

Excel permits you to import photos with full sizing capabilities and I found that 100 pixels equal 1" in cell dimensions. So you can open a worksheet, size the area you want to put a photo in, import the photo and size it to the boarders of the cell in no time.


Posted by:

Frances
29 Jun 2020

I come from the Amiga world and have used Professional Calc for years. I'm stll using it in Amiga Forever but I can't, at the moment, print from it so things get transferred to Open Office. I must say that Open Office is more than complicated enough for me. I'm used to ProCalc's straightforward drop-down menus and keyboard shortcuts and hunting through Open Office is a pain. I can't imagine what it would be like with Excel.

Yes, a spreadsheet program makes a good database.


Posted by:

Rod Nordberg
08 Jul 2020

Not good for historical society that Excel can't sort dates prior to 1900. Other spreadsheets do.


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! Comments of a political nature are discouraged. Please limit your remarks to 3-4 paragraphs. If you want to see your comment posted, pay attention to these items.

All comments are reviewed, and may be edited or removed at the discretion of the moderator.

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


Free Tech Support -- Ask Bob Rankin
Subscribe to AskBobRankin Updates: Free Newsletter

Copyright © 2005 - Bob Rankin - All Rights Reserved
About Us     Privacy Policy     RSS/XML


Article information: AskBobRankin -- Wow, You Can Do That With Excel? (Posted: 19 Jun 2020)
Source: https://askbobrankin.com/wow_you_can_do_that_with_excel.html
Copyright © 2005 - Bob Rankin - All Rights Reserved