DATEVALUE Function Rules!

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. As an Advanced User, you know that 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 like you 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 matter, 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 DATEVALUE function: Good Stuff!

You Want to Ask, “What If?”

"What If” scenarios are a valuable instrument for nearly any business. Any advanced user should be aware that Excel provides a great tool to accomplish this. Excel can automatically make changes in a variable using Goal Seek (it can save you a ton of work, and make you look like a Genius in the process).

Using a very Simple Example, let’s say that you are a call center sales manager, and you know the approximate number of calls you are going to receive for the next month, as well as the sales goal that has been set by the executive branch. You wish to know the Call-To-Sale-Conversion-Rate so you can advise your staff. A rudimentary calculator can be created in the cells of your Excel worksheet as follows:

Number of Calls x Conversion Rate = Number of Sales (e.g. C4*C5 = C6)

After filling in the values for C4 and C6, you can use Goal Seek to calculate the required value for C5.

Obviously, this tool can be used to greater Advantage with more complex scenarios, but you get the picture. Goal Seek can be a handy tool for the boardroom or any planning venue. As am Advanced User, you owe it to yourself to Give it a Try!

Using the Data Entry Form

Since you are an Advanced Excel User, you may have someone other than yourself who frequently adds records to databases or tables that you have created. If this is the case, you should definitely encourage the use of the Data Form Tool. For anyone who routinely enters data, it can make Life a Bit Easier.

How to Add the Form to Your Workbook for Excel 2007 & 2010 Users:

1. Click the down-arrow of the Quick Access Toolbar in the upper-left corner of your workbook

2. Choose Customize Quick Access Toolbar

3. Select All Commands and choose Form…

4. Click the Add>> button and click OK

Now go to your database or table and select the Form from your Quick Access Toolbar. Presto, there is is! Up pops a New Data Entry Form for updating your database!

Just tab through the Form as you enter your data, and watch your table or database update as you do. Wow, what a Great Little Tool! Give it a try!

Upgrade to Excel 2010?

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

Have no doubt about it, there are some Worthy New Features. The following are those I consider to be the most significant:

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

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

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

4.  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.

5.  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 suddenly find yourself  Out-Of-Touch (Never a good thing…).

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!

Use Excel for Exchange Rates of Currency

So, you are getting ready for that trip to Europe, (or wherever in the world you are going), and you would like to download the most up-to-date currency exchange rates directly into the Excel workbook you are using for your trip planning. Piece of Cake!

Here is what you do in Excel 2003:
1) Select cell A1
2) Go to Data / Import External Data / Import Data and choose MSN MoneyCentral Investor Currency Rates
3) Click OK

Bamm! In a few seconds, you will have the exchange rates from countries feom Argentina  to Venezuela!

If you are using Excel 2007 or 2010:

1) Select cell A1
2) Go to Data / Get External Data / Existing Connections and choose MSN MoneyCentral Investor Currency Rates
3) Click OK

Does that Rock or What? I see that today you can get 1,883 Columbian Pesos for one US Dollar today(sounds like a bargain to me…)!

Database Best Practices

It has recently occurred to me that the design, construction, maintenance, and information-mining of Good Databases are the quintessential keystones of knowledge for any Advanced Excel User.

Creating a Database in an ongoing Excel workbook can save you time, money, and frustration. By creating a database for information that is routinely updated, you can automate your reports and simplify your users’ interface.

Today we will look at what constitutes a Good Database, and what pitfalls to watch out for.

First of all, a database should contain data, and that is all! No formulas should exist in a database, just pure Data waiting to be turned into Information on a separate worksheet.

Secondly, there should be no blank rows (as you know, they are called “Records” in a database) and No blank columns (called “Fields” in a database).

Thirdly, put only one piece of data in each field. This will eliminate the need for repeating fields, and make your information-mining much easier.

Lastly, make sure the information is entered in the proper field. If the data entry person (maybe you) cannot find the right place for a piece of data, perhaps the database needs some redesigning.

Now that you have a Magnificent Database, you can Mine it for Information!

Certification in Excel 2010

In these uncertain economic times, it is always good to be able to set yourself apart from your competition through the pursuit of Education and objective Certifications. Anyone can claim to be an Expert in Excel when applying for a new job or elevated position, but those who have bonafide certification from Microsoft will inevitably be held in higher esteem (unless, of course, you are competing against the owner’s nephew…).

Passing the Microsoft Exam 77-882 awards you the Microsoft Office Specialist certification in Excel 2010. Having successfully taken MOS certifications in the past, I can tell you that the preparation work for the testing will most probably introduce you to new skill sets that are outside of your comfort zone. This is, in itself, a good exercise for any Excel Professional.

One note of caution, however: Currently, Microsoft provides very little in the way of formal study materials for the Excel 2010 exam. This can be circumvented, however, by using the plethora of study materials available for the Excel 2007 certification exam and then making sure that you study the new features introduced in Excel 2010. This is important, as Microsoft always likes to test on the new elements being introduced in its latest software upgrade.

In addition to studying the materials for the Excel 2007 certification, it would be wise to become at least reasonably familiar with the following New Excel 2010 tools:

• Sparklines
• Backstage
• Slicers
• New Pivot Table Features
• New Statistical Functions

Becoming Microsoft certified (my wife tells me I am Certifiable, but I think she may be referring to something else…) in Excel can give you a Distinct Advantage in these competitive times. You may wish to consider it…

Spinner Buttons

Since they control the data that can be entered and are easy-to-use, so-called “Spinner” buttons can be a clever addition to a spreadsheet. To add a spinner in Excel 2003 and in earlier versions of Excel, click the Spinner button on the Forms toolbar, and then draw your spinner on your worksheet. (You can size the Spinner to your liking.) To add a Spinner in Excel 2007 (similar in version 2010), click the Developer tab, click Insert, and then click Spin Button in the Form Controls section.

Now you are all set to have some fun! Right-click on the spinner, and then click Format Control. On the Control tab complete the values as follows (this is a test, ma’am or sir, only a test…):

1. Current value: 1
2. Minimum value: 1
3. Maximum value: 10
4. Incremental value 1
5. Cell link: $C$7 (Note: Any of these values can be of your own choosing.)

Now when you click the Spinner control, cell C7 is be updated according to the parameters you set. If you have created a worksheet where other cell values or results are dependent on the value in C7, your worksheet will update according to the quantity you select with the Spinner.

How cool is that! Make your Excel worksheets look like someone spent hours of programming time on them in just 5 minutes. Give it a try (people will think you are a Star!).

Scatterplots and Correlations

For all of you fellow Statistics Fans out there, one of my Excel tools are the Scatterplot (XY) Chart and the Coefficient of Determination function.

A Scatterplot Chart is commonly used to show the relationship between two variables or sets of data. For example, a sales manager could plot the number of sales calls taken with the number of sales made. Another example is comparing the average length of time a customer service representative takes per call and the overall quality score of their calls.

To determine how strong the correlation is between the sets of data, the brilliant (and soon to be more appreciated) Excel user can make a Scatterplot Chart and and then do the following:

1. Right-click on one of the data points and
2. Choose Add Trendline
3. Right-click the Trendline and choose Format Trendline
4. Format the Trendline to your aesthetic preferences and
5. Put a Checkmark next to Display R-squared Value on Chart

The R-squared value is your Coefficient of Determination (COD) that will tell you how strong your data on your two axes.  This will range from -1.00 to +1.00.  In the graph example above the COD value is .5574 (or approximately 56%) representing a strong correlation (and therefore reasonable credible).

That's it in a Nutshell!  Try using a Scatterplot and Coefficient of Determination sometime when seeking the correlation of data sets. It’s easy and can reveal some valuable information.  Just remember, Correlation Does Not Equal Causation...

Working with Excel on iPad

Since you are reading this blog, it is likely that you are a Technophile and also own (or are thinking about owning) an iPad. The iPad can coexist nicely with you other computing equipment, and can provide an Alternative for working on Excel rather than being tied to a full-blown desktop or laptop computer.

There is no doubt that not all Excel’s features are available when working on an iPad. For a great many common tasks, however, it is more than sufficient, and there is something very positive about being Flopped on a Sofa and still having access to your favorite software. Not only that, but as the software makers further refine and create new applications that can handle spreadsheets, the possibilities continue to grow.

Applications

There are a growing number of applications that the Excel Enthusiast / iPad Owner can use. My favorites are Quickoffice’s Quicksheet and Apple’s Numbers. Although DocsToGo is a worthy contender, most users (in my humble opinion) will find the features and user interface more pleasing with the other two apps. I have been using all three applications since shortly after the iPad’s debut and I find I seldom use DocsToGo for anything other than PowerPoint.

Compatibility

While either Quicksheet or Numbers can handle a great variety of formulas, creating Charts in Apple’s Numbers is a real treat. Both of these applications can import and export in Excel format. This is of keen importance, of course, as what good is a spreadsheet if you can’t export it back to Excel.

Navigating/Viewing
Although it may be a bit foreign at first, tapping to select cells and using the convenient selection handles to choose a range becomes second-nature quite quickly. The now commonly-known Pinching Gestures zoom you in or out on your data and charts, and it is easy to get hooked on these new ways of getting around a spreadsheet.

Sharing
Once you have created your spreadsheet masterpiece on your iPad tool, you can easily email it in its original Apple format, a PDF or, of course, as an Excel document.

Although some proclaim distain for this new way of interfacing with your data, I firmly believe that if you give it a chance, you will find that it makes a Pleasant and Productive alternative way of working with your Excel creations.

Cheers!

Easter Eggs

Well, it’s not exactly Easter, but it is always fun to reminisce about the so-called Easter Eggs that have been hidden in Excel. Virtual Easter Eggs are hidden games or messages that are built into software by crafty developers who have a Sense of Humor and enjoy building in a bit of intrigue for the “Insiders” who wish to search for the cryptic content.

The term, Easter Egg, was coined in the late 1970s at Atari by the renowned computer game designer, Warren Robinett. Since at that time designers were not given credit for the games they created, the clever Robinett included a hidden screen which said “Created by Warren Robinett”.

The Excel 97 version had a very ambitious Flight Simulator hidden within the application. Using a rather simple combination of keyboard commands brought you to this remarkable simulator.

Although more difficult to access, Excel 2000 included a Car Racing Easter Egg which resembled Spy Hunter.

Excel 2003 included an Office Quiz featuring the Crabby Office Lady. If you still have this version and you are connected to the internet, you can access this egg by typing in “Tortured Soul” in the search box.

Although there have been rumors to the contrary, there are no widely-known hidden gems (or germs, depending on your view…) in Excel 2007 or Excel 2010. The general consensus is that Easter Eggs have been eliminated from Excel due to potential security concerns. The inclusion of these eggs have also come to be considered Unprofessional (some people just don’t like to have any fun…)

If, however, you know of any eggs in these later Excel versions, please write to me at ExcelEnthusiast@gmail.com. I would love to share them with our merry group! Cheers!

Calculate Your Car Payments

I have to admit, I am a Function Junkie, and let’s say it is time to trash the Nerdmobile and buy a new car. Well, no self-respecting Excel Guru is going into a car dealer without his or her trusty laptop or Excel-compatible tablet loaded with the most recent version of Excel.

And why do I want to bring my computer you say, (besides just to look cool)? 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(AnnualInterestRate/12, NumberOfPayments, AmountOfLoan)

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

=-PMT(0.04%,60,30000)

Note that a negative sign (-) was placed in front of the function in order to return a positive value (negative payment values just don’t look cool…)

With your formula in place, hit Enter and, Bam!, 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 is that!

Welcome to the Advanced Excel Enthusiasts Blog!

Hello Excel Gurus!

Today marks the debut of the Excel Enthusiasts: Advanced blog! In the weeks and months ahead, this blog will explore intermediate and advanced Excel. Topics will range from highly useful ways of mining and displaying your data, to truly esoteric, wild and wonderful looks at what this powerful tool can do.

I hope you will check in now and then. You are also encouraged to join the 2,500+ member Excel Enthusiasts Group on LinkedIn and stay connected with other Excel experts!

All the best,

Bob (The Excel Guy)