The Advanced Filter

As you may well agree, there are a great many powerful gadgets in Excel that are seldom used.  The Advanced Filter is just one such example of an often-overlooked, but enormously useful tool.

The beauty of this tool is that it is, well, Advanced.  You can, however, use it in comparatively simple ways to get an immediate understanding of how it works.  We will take a look at a couple of examples of how to use the Advanced Filter.

First of all, we will assume you have the following small database (keeping in mind, of course, that this tool works equally well with databases containing thousands of records):

 As is the case with any well-designed database, each field (column) has a header/name.  Not surprisingly, this is a necessity when using this tool.

Next, we will access the Advanced Filter by going to the Sort & Filter group on the Data tab, and clicking on Advanced. 

In the List range field put the location of your database (in this case it is B3: C11).

For the Criteria range, let’s assume you want to see all of the sales for the North, West, and East regions, and you want to place this filtered result below your database (use B13 for a starting point).  Simply set up a range such as in the following cells E3:E6 (or wherever you wish):

Your result will appear as follows in your chosen location:

Okay, Cool, but don’t stop there; you can use multiple field names and criteria to extract a wealth of information in a mere three clicks.

When you’re ready to Graduate up to another level, give the Advanced Filter a try.  I think you will like it!

Automatic Fun!

It is probably fair to say that the vast majority of us appreciate Spellcheck in the apps we use on a daily basis (except, of course, when it chooses to do weird substitutions…).  What many Excel pros are missing, however, is that they can Customize automatic corrections with the highly useful, (but typically underutilized), Autocorrect tool.

This is particularly useful if you find yourself typing long (or even moderate) Boilerplate phrases that are tedious and time-consuming.  For a brief example, let’s say you work for Harley-Davidson Motor Company and the company policy is that no abbreviations of the corporate name be used in correspondence.  Typing the entire name every time you do any kind of spreadsheet or correspondence may not seem terribly onerous, but it can be a small irritation (and who needs that!).

The way you set up a phrase (company name in this case) in Autocorrect may seem like a bit of a chore, AutoCorrect can make this is as easy as typing HDMC.

Here is How You can Set this Up:

1. Go to FILE and select Options from the bottom of the column

2. Choose Proofing and click on the AutoCorrect Options button

4. In the Replace box, type HDMC

5. In the With box, type Harley-Davidson Motor Company

6. Click Add and then OK. That’s all there is to it!

But wait! There is a chance for some Tricks along with the Treats.  Many of you know the gag of accessing a fellow employee’s computer, and switching the mouse controls from (for example) to left-handed from right-handed, and then watching the bewilderment of the user.  Well, you can also have some fun (all very adolescent) with a person with Autocorrect. 

Let’s say you have a friend named Jim at work (or whomever). While he is away from his computer, go into AutoCorrect and enter Jim in the Replace box and Easy Rider in the With box. This can be done in Word too, of course, and he will perhaps think he is channeling the Harley experience.  (Just be sure you have him do this in your presence, so he doesn’t unnecessarily embarrass himself.)

Autocorrect.  Useful for productivity and a bit of mischief. 

Never Retire!

Since I have my 65^th birthday coming up in a few days, I thought it would be interesting to take a look at a really cool formula or two that calculates when you are going to retire.  This can be a useful tool if, for example, you work in Human Resources or if you are doing Financial Planning.  It can also be entertaining if you just like to Daydream about days in the future when all you are concerned about is laying on the beach (getting even more wrinkly…).

I, for one, have absolutely no desire to retire anytime soon, since I enjoy teaching, blogging, and instructional design too much to give it up (besides, I look like I’m only in my 40s, Ha!).

If you have worked with Excel for some time, you know that it can be a bit quirky when it comes to handling dates.  Today, however, we will look at a couple of truly elegant ways of calculating your retirement date based on the Retirement Age you choose and on your Date of Birth.

In our first example, we’ll assume that the person’s birth date is in cell A2 and retirement age is 66.  In cell A3 create a formula as follows:

=DATE(YEAR(A2)+66,MONTH(A2),DAY(A2))

Another, even shorter, formula that you can use is the following (please note that you will need to format your cell as a date after using these formulas):

=EDATE(A2,66*12)

 

Well, there you have it; two uber-cool ways for Calculating Your Retirement Date. Now pardon me while I get back to work (laying on the beach can wait…).

Power Query

Pulling data from sources outside of Excel is a fact-of-life for many Excel users.  In the past, Microsoft Query was the Go-To add-in for importing data of this sort into Excel, but it had several limitations.  It is therefore exciting to see that Microsoft is offering a New (Free!) add-in called Power Query!

Microsoft Power Query for Excel provides an intuitive user experience for uncovering, combining, and refining data across a wide variety of outside sources. The list of possible sources is shown below, and even includes FaceBook and Wikipedia:

• XML file
• Text file
• Web page
• Facebook
• Wikipedia
• Sybase Database
• Teradata Database
• SharePoint List
• OData feed
• CSV file
• SQL database
• Microsoft Exchange
• SQL Server database
• Azure SQL Database
• Access database
• Oracle database
• IBM DB2 database

With Power Query, you can create, share, and manage queries from search data available inside and outside your organization. Users of this powerful tool can find and use shared queries to mine the underlying data in the queries for their data analysis and reporting.

Using Power Query you can:

• Zero in on the specific data you care about from your sources
• Further discover relevant data using the search capabilities within Excel
• Combine data from multiple, dissimilar data sources and prepare it for further analysis with tools in Excel and Power Pivot
• Share the queries that you created with others within your organization

If you are a Power User and your job involves analysis of information stored in other sources and formats, you will likely want to explore Power Query.  It is, of course, all about Power (and did I mention it is Free?!?)