Pivot Tables

We just covered Pivot Tables in my current Intermediate Excel Class today, so I decided it would be a good topic for this week’s blog post.
 

Pivot Tables are truly amazing tools for quickly categorizing data in such a way that it makes analysis a Snap!  They are hugely powerful and easy to use.  “Easy to use” you say?  Yes, in spite of the fact that many Excel users find them mystifying and perhaps a bit intimidating, they really are indeed very Easy!

 

Pivot Tables provide interactive ways to quickly encapsulate large data sets, and are designed for:

·        Querying large databases (which by refreshing the data can automatically update entire pivot table reports)

·        Subtotaling and aggregating data by categories and subcategories

·        Creating custom calculations and formulas

·        Filtering, sorting, and grouping a specific subset of data enabling you focus on the information you want

Pivot Tables flexibility is one of its key strengths.  With 3-4 easy clicks of a mouse, you can achieve a report that may otherwise take you hours to create.  Pivot Tables, powerful and easy tools that can make your Excel life easier.

Wildcards

As we all know, there is an abundance of Super Cool tricks in Excel. Even the grandest of Excel masters can occasionally stumble upon a tool that can astonish the guru a bit.  It is, of course, arguable that the use of Functions is the coolest “trick” of all in Excel.  To make functions even more powerful, however, you can use Wildcards.

 

The ability to use Wildcards is a highly valuable feature in Excel.  Several functions can use these keenly useful tools to better filter the results you are attempting to get. The following is a list of some of the more common Excel functions that can make use of Wildcards:

• AVERAGEIF
• AVERAGEIFS
• COUNTIF
• COUNTIFS
• MATCH
•  SUMIF
• SUMIFS
• VLOOKUP

There are three different Wildcard Characters in Excel.  Though that may seem to be a trifling few, you may be Amazed at what you can do with them.

The summary below offers an explanation and examples of how these characters can be used in the functions noted above.

Wildcard Character	To
Question Mark (?)	Find any single character (letter, number, etc), such as “B?nk” finds Bank, Bonk, or Bunk
Asterisk (*)	Find any number of characters (once again, letters, numbers, etc), such as “Post*” finds Postage, Postpone, Postcard, Postulate, etc
Tilde (~) with another Character	Insert a literal question mark (?), asterisk (*), or tilde (~) such as “Jameson~?” finds Jameson?, “Jameson~*” finds Jameson*, etc

As you further your mastery of Excel formulas, keep in mind the power of Wildcards.  They can give you a great deal of help as you explore the Wonderful Wild World of Excel!

Instant Information!

Although it’s not a very good solution for general reporting, the AutoFilter tool is a quick and easy way for you to display only the desired records of data that fit into your defined grouping. By filtering out the records that do not interest you, your view of your data is Immediately Focused on the information you wish to examine.

First select any cell within your data, and then go to Data, Sort & Filter, and click on the Filter (the Funnel icon…). AutoFilter Arrows will appear at the top of each column, allowing you to filter on whichever column you choose. Filtering by Multiple Choices in the columns can also be done to narrow your focus on the data.

The AutoFilter tool is a sometimes an underused and under-loved convenience that has many built-in tricks most Excel users are unaware exists. Since a lot of Excel users are at least somewhat familiar with the basics in Autofiltering, let’s jump right into some of the lesser-known Cool Stuff!

Let’s say that one of the cells in your database or table contains information that you want to zero-in on.  By Right-Clicking (“Right-Click is our friend!”) the value, you can filter by the cell’s Value, Color, Font Color, or Icon!  Bamm, just like that! 

Depending on the nature of your data, you can also use the Number Filters or Text Filters that are available as Autofilter options.  You can even save yourself a bit of time and Sort without going out of the Autofilter menu.  Really Cool Stuff!

As I mentioned earlier, this tool may not be the best for routine reporting, but if Instant Information is what you are after, give it a try!

Conditional Formatting Basics

Occasionally it is a good idea to Go Back to the Basics. By Highlighting important data in your tables or databases, you can quickly draw attention to the information you want to study.

Let’s say that you have a table showing the results of your sales force, and you wish to exemplify the Above-Average performers in your group. Here is an easy way to highlight these values in a range:

1.     At the bottom of your range, enter the formula for Average: =Average(range)

2.     Select the values in the range (not including the Average formula)

3.     On the Home tab, choose Conditional Formatting from the Styles menu

4.     Select Highlight Cells Rules / Greater Than

5.     Select the dialogue box and click on the cell in which you have the Average Formula

6.     Choose the Formatting (e.g. Bold font with a Yellow fill…) you wish to apply

7.     Click Enter and Bamm! Values that are Above-Average are highlighted!

Use this simple method to highlight Above-Average values (You are, after all,  Above-Average Yourself!)

Not Your Uncle’s Filter

Most any Excel user knows how to use the Filter tool to quickly sort a table or database. Using a Filter can be very effective and can save a great deal of time in many instances.

But what if you need more Flexibility and Power, but still want to do your analysis with a fast, easy-to-use filter? The answer is, of course, the Advanced Filter (definitely Not your Uncle Jim’s filter…).

The beauty of this tool is that it is Advanced, but still very quick and easy to use. We will take a look at a couple of simple examples of how to use the Advanced Filter to help get an immediate understanding of how it works.

With the realization that this tool can work equally well with databases containing thousands of records, we will assume you have the following small database:

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:

You can also use multiple field names and criteria to extract a wealth of information in a mere three clicks.

Another one of the myriad ways you can use the Advanced Filter it to Create a Separate List of Unique Values.  If you wish to retain the original list, and create a separate list of Unique Values, you can click on our new favorite tool, and select your range. Then do the following when the menu appears:

1. Select the Copy to another location option
2. Insert the starting cell in the Copy to dialogue box
3. Put a check mark in the Unique records only box
4. Your unique items will appear in the newly-created range

Pretty Cool, you say. Absolutely! Give the Advanced Filter a 5 minutes tryout, and you’ll be Hooked!