PowerPivot

This week’s topic is for you True Power Users out there. If you need some new, Real Muscle, this is for you!

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. Well, this can be a bit daunting at times, but not if you have the Right Tools.

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!

Make Your Charts Look Good!

Aesthetics seems to be a dirty word in some tech circles. Upon closer examination, however, it is apparent that making things “Look Good” definitely has its place in Excel as well as technology in general. Why is that important? Well, just take a look at products by Apple. Sure, they do a good job, but they also look good, and are therefore add sensory appeal for the user.

When it comes to Charts, Excel 2007 (and Excel 2010) gave you much more control over how your work Looks!

Excel 2007 ushered in the Ribbon, which is clearly a great advantage to anyone making Charts. Double-clicking on your chart will pull up the Design Ribbon, giving you several ways to improve the appearance of visually-displayed data. Among the possibilities that are readily are the following Options for you on this ribbon:

1. Change the Chart Type (easy access to all major types)

2. Chart Layouts (5 different ways to display titles, labels, legends, etc)

3. Chart Styles (a sizeable variety of color and 3-D options) You can also go to the Format tab and find a myriad of ways to further Customize the look of your chart with:

a. Bevels
b. Soft Edges
c. Shadows
d. Much More

So, once again, why is this important? Please consider this: If your audience is going to Understand the Message your chart is conveying, they are going to need to Like it First! As in all good communication:

1. Engage Your Audience

2. Convey Your Message

Happy Thanksgiving, All!

Calculate Car Payments

Okay, let’s say it is time to trash the Nerdmobile and buy a new car. Well, no Self-Respecting Geek is going into a car dealer without his or her trusty tablet or laptop loaded with the most recent version of Excel.

Why is that, you ask? To calculate the monthly car payments, of course! Here is how:

The Excel PMT() function can calculate the monthly repayment using the following format:

=PMT(Annual Interest Rate / 12, Number of Payments, Amount of Loan)

For example, if your annual interest rate is 5%, on a 4-year (48 months) loan of $35,000, your formula would look like this:

=-PMT(0.05%, 48, 35000)

Note that a negative sign (-) was placed in front of the function in order to return a positive value (negative payment values look 20th century…)

With your formula in place, hit Enter and, Bamm!, you have your monthly payment. If you don’t like the results, play around with the interest rate, the length of the loan, or maybe check out some cheaper cars. How cool!

Interactive Chart Titles Rock!

Here is a Terrific Way to make the charts in your report Stand Out from the mundane masses.

Interactive (or dynamic) Chart Titles can be easily added to your charts to reflect the data being displayed. For instance, if you have created a table of data that changes in accordance with the Employee Name shown in a Drop-Down Box (which is effortlessly generated by using Validation / List), you can Link the Chart Title to reflect the name chosen in the drop-down.

Here is How You Do This:

First of all, if your chart does not have a Title, do the following:

1. Click anywhere in the chart
2. On the Design tab, click a layout that contains a title from the Chart Layouts group

Now, on to the Adding the Interactive Title:

1. Select the Chart Title
2. Go to the Formula Bar and type an Equals Sign: “ = ”
3. Then Select the Drop-Down Box Cell to which you want to link
4. Note: The final cell reference formula should look something like: “=Sheet1!$C$3”

How Cool is That!  Now every time you change the Name or Value in the Drop-Down, the Chart Automatically Updates its Title!

As they used to say in the 70’s, Far Out, Man!

The RAND Function

An interesting and at times very useful function in Excel is the RAND function. This function returns a Random Number that is greater than or equal to 0 and less than 1. Each time your worksheet recalculates, (you can force recalculation by pressing F9), the RAND function returns a new random number. While some hard core statisticians have voiced concerns about the true Randomness of the RAND function, it suffices for nearly all but the most demanding statistical applications.

The syntax for the Rand function is simply:
RAND( )

If you want to create a random number between two numbers, (where a is the smallest number and b is the largest number), you can use the following:

=RAND()*(b-a)+a

If you want only Whole Numbers you can use:

RANDBETWEEN ()

For example, =RANDBETWEEN(1,200) will produce a random whole number between 1 and 200.

So how can you use this nifty little function? It can be used in myriad ways; for example, it can be used in conjunction with other functions to create a Password Generator. There are countless statistical applications, but you can also use it for entertaining applications. For example, one Excel Enthusiast used it create a Tetris-style game in Excel.

I have used the RAND function in conjunction with other functions and graphics to create a Slot Machine in Excel (send me a request at excelenthusiast@gmail.com if you would like a copy of the Slot Machine spreadsheet).

The RAND function. Great for use in statistical applications, as well as some RANDom FUN!

 

Cheers!