On Your Way To Becoming An Excel Power User

Excel is perhaps the most used Microsoft Office program after Word and with good reason. It is easy to use and its grid-like layout makes it a natural choice to enter tables and numbers. As easy as Excel is, it is a known fact that most people do not use even a fraction of the program’s power. However, by knowing a few techniques you can easily create intricate and powerful spreadsheet applications.

To start your journey to Power-User status, there are three things you should master:

The Vlookup function
Performing two-way searches
Pivot Tables

There are many, many other areas of Excel you would need to know before becoming a power user, but just by using these three techniques will enable you to create powerful spreadsheet applications and quickly render a large mass of data into meaningful information.

Vlookup:

Excel contains many lookup functions but the most useful is by far Vlookup. In fact, just using this function alone can help you draw data from huge lists and connect different parts of large workbooks. The important thing to remember about Vlookup is that the Lookup value must be in the first column of the table that you are searching.

Here’s how to use it:

Notice that the Lookup value, in this case Item6 in cell D13 above, is also contained in the first column of the table named “itemsales”. The value that we need returned (March’s sales), is located in the 4th column of the table and since we want an exact match, “False” is entered as the Match-Type value.

Think of the possibilities! With this simple formula you can inter-connect worksheets and tables making Excel do the tedious lookup work. This is a very powerful formula — learn it!

Two Way Search:

Did you ever want to perform a search on an Excel range based on two criteria? Well, by combining two of Excel’s well known functions, MATCH and INDEX, you can.

As you can see below, we have a table of sales data with the months on the left, items or products on top (i.e. Item1, tem2, etc.) and sales volume information in the C6 to F17 range. To make it easy, we used the following ranges:

Months – B5:B17
items – B5:F5
SalesData – B5:F17

By entering the month and item name in cells C19 and C20 respectively, our Excel formula combining the MATCH and INDEX functions finds the appropriate sales volume. Refer to the screenshot of an Excel Spreadsheet below showing the anatomy of the MATCH and INDEX functions as well as the combined formula: =INDEX(SalesData,MATCH(C17,months,0),MATCH(C18,items,0)).

Pivot Tables:

One of the hallmarks of today’s business environment is the ever-increasing deluge of available information. Often in our work, we need to make sense of an endless sea of numbers to make critical business decisions. Excel comes with a vast array of formulas, functions and ways of calculating numerical data, however, good business decisions hinge on interpreting that data. Pivot Tables can help.

Pivot tables were designed to enable users to quickly and efficiently organize, format and find relationships within large data sets. The ins and outs of Pivot Tables are well beyond the scope of this article. After all, whole books have been written on creating and using Pivot Tables. What I hope to do is to introduce you to Pivot Tables and encourage you to learn all you can about them. Once you know the basics, you’ll come to realize that Pivot Tables are easy to use and one of the best and productive features of Excel.

Prepare your source data:

Data should be in tabular form (i.e. Data Table – columns and fields).
All columns should have a unique heading in a different format than the data (i.e. bold).
The table should not have any blank rows within it.
Apply appropriate cell formats to the data (i.e. currency, number, text, etc.).
Name the table, including column headings. This makes it easier to identify where the source data is when creating the Pivot Table.

To create a basic Pivot Table:

After you perform the above steps, you are presented with a Pivot Table Field List that will allow you to drag and drop fields to various parts of the Pivot Table. As you move fields, Excel reformats the Pivot Table showing you how your data is displayed.

As you can see below, by moving the Month field to “Column Labels”, the Region field to “Row Labels”, and the Sales field to “Values”, I was quickly able to summarize my data by Region, month, and sales volume. The example below is an extremely small table. However, I could have just as quickly and easily summarized a table containing thousands of records.

In Summary:

As you can see, using only these three powerful techniques will allow you to analyze large amounts of data. After all, a spreadsheet containing row upon row of numbers is only a collection of data. To make business decisions, you must be able to correlate the information into meaningful relationships allowing you to see the implications of your analysis.

To learn more about Excel formulas and Pivot Tables read these books:
Excel 2010 Formulas by John Walkenbach
Pivot Table Data Crunching by Bill “Mr. Excel” Jelen and Michael Alexander