PowerPivot

This week’s topic is for you Power Users out there. It is also intended to perhaps Inspire the rest of us mere mortals, and to bring your attention to a Free (“Free” is always cool) Powerful Tool.

There are times when being able to combine and analyze data from a number of sources would be, (as I like to quote Martha Stewart), "A Good Thing." Let’s say that you have several SQL databases housed on SharePoint and other sources, and you want to load the data and create interactive queries from within an Excel workbook. Scary Business? Well, a bit, but nothing beyond the capabilities of an Excel Enthusiast!

PowerPivot, available on Excel 2010, truly Empowers you to capture the data you need, gain greater insight into the meaning of the data, and do so without overtaxing your system’s resources. With PowerPivot, you can:

• Load very large databases from Nearly Any Source

• Efficiently process huge amounts of data in mere seconds

• Work in a Disconnected Mode once your data is imported

• Leverage your Familiarity with Excel to work with the data

• Use the new PowerPivot Analytic Capabilities

• Utilize the Power of contemporary multi-core processors

PowerPivot: Not necessarily for everyone, but if you work for a large company and need a new way to Slice and Dice your data, download it for Free from www.PowerPivot.com and give it a try!

Next week: Back to the mainstream with a cool, easy technique that will give you options you never had.

Is it Time to Upgrade?

So you have Excel 2007, (it seems fewer companies and individuals are upgrading as frequently these days…), and you are wondering if it is worth it to finally get Excel 2010.

Although I have had 2010 since it came out, the need to upgrade is not, in the opinion of some, to be overwhelmingly compelling. That being said, there are some Worthy New Features. The following are those I consider to be the most significant:

Function Enhancements:
The Accuracy of a number of the financial and statistical functions have been improved

Sparkline Charts:
Nifty little tool enabling you to create Small In-Cell Charts

Slicers:
New way to Filter and Display data in Pivot Tables

Image Editing Enhancements:
Since I have an appreciation of making my graphics look good in any Microsoft product, this is one of my favorites. You have much More Control over Graphic Images, including the ability to remove the background of an image.

New Version of the Solver Add-In:
Enables solving some Complex Problems (Cool!)

There are a few others that you may find helpful, but for my book, those listed are the Most Compelling. In any regard, I think it is wise to keep in pace with current upgrades. Otherwise the day will come when you may find yourself Sadly Out-Of-Touch (Never a good thing…).

Happy Thanksgiving All! ~Bob

The Invaluable DATEVALUE Function

A Date is a Date is a Date (well, that certainly wasn’t true back in my college days). Nor is it true in Excel. What may look like a Date, may not “play nice” with other dates that you have in your worksheet.

Let’s say that you have inherited an Excel workbook made by some Genius, (please note the thinly veiled sarcasm), and you want to Perform Some Analysis (in your case, truly genius work) that save the company countless hours and expense. The trouble is that unless you can Rely on the consistency of the way Excel will be handling the “dates” in the worksheets, you Cannot Rely on the efficacy of your results (the old “Garbage In, Garbage Out” cliché).

So what is an Excel Guru to do? DATEVALUE to the Rescue! Yes, indeed, my friends, DATEVALUE will calm your nerves, relieve your upset stomach, cure that nagging doubt that you are being watched, and make any Dates in your worksheet work in consistency with all of the dates therein. (Well, I may have exaggerated some of the attributes, but it will make your dates play nice with each other…). DATEVALUE will instantly convert any looks-like-a-date Date into the standard Excel serial number, and you can then format it as you wish.

How Cool is that! It may sound minor, but it can save you a world of grief in many circumstances.

Note of Caution: For you Apple Users out there, (I use Excel on a MacBook occasionally myself), Microsoft Excel for the Macintosh uses a different date system as its default (go figure…). The

The DATEVALUE function: Good Stuff!

Summarize Lists

Here is an easy, but highly useful technique. Let’s say that you have a Database of All Customers and the City in which they reside. It would quite probably be interesting to all of the stakeholders of this information to see a Summary of how many Customers you have in Each City.

 Well, this is quite simple to do, but is a good review, especially since we are going to once again mention Named Ranges.

First of all, Name the Range that contains the City. This can be done simply by selecting the range, (you can include blank cells not yet filled to allow for future growth), and type the Name of the Range in the Name Box in the upper-left-corner of your worksheet. For illustration, we will assume you have named it “City” (Clever, eh?...).

Then you can list the cities in the database, and (assuming your first entry is in A2) put in the following formula in the first adjacent cell:

  =Countif(City, A2)

After that, just copy the formula down next to each city and, Bamm!, You have your Summary!

Is this Simple? Yes, it is, but often times, the Simple Ways are the Best…

Odds and Evens

When I was in grade school, there was a popular gambling game amongst us boys that involved guessing “Odds” or “Evens” regarding the number of marbles the other boy held in his closed fist. Judging by how many times I spent my allowance on buying new sacks of marbles, I apparently wasn’t very good at the game.

When working with databases or tables, there are many instances when you wish to Identify and Sort whether the number is Odd or Even. Cases may involve street addresses, employee ID numbers, statistical studies, and several other pursuits.

So what is a Good Formula for identifying an Odd or Even number? As with so many things in Excel, there are several ways to do this, but the following are among the most straight-forward. Assuming your data is in Column A, you could use this formula and copy down your range:

1. =IF((ISEVEN(A1) = TRUE), "Even", "Odd") This IF formula incorporates the ISEVEN function to generate the desired results.

~ Or ~

2. =IF(MOD(A1,2)=0,"Even","Odd") This IF formula incorporates the MOD function to generate the results. MOD is an unusual function that finds the Remainder when you divide one figure by another.

Odds and Evens: There are times when knowing this information can be a Boon to your data analysis needs.

Cheers!