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…).