Have it Your Way…

No, we’re not talking Burger King here.  The fact is that we have discussed various ways that you can customize Excel to suit your specific needs.

The Standard, Off-the-Rack Excel Setup suits most users just fine, as it provides the features that most people want.  With an analogy to camera buffs, it is sort of a Point-and-Shoot approach to spreadsheets.  Nothing fancy here.  Of course, as a reader of Excel blogs, you are not a typical Excel user.

Excel offers an abundance of choices in customization for the more sophisticated user. The current and recent past versions of Excel, for instance, offer a One-Stop-Shopping location to choose from a massive array of Excel Options. This location can quickly be found by clicking on File on the toolbar, and going to Options button in the lower-left corner.

There you will be presented with the following 10 Categories of options:

1.   General

2.   Formulas

3.   Proofing

4.   Save

5.   Language

6.   Advanced

7.   Customize Ribbon

8.   Quick Access Toolbar

9.   Add-Ins

10. Trust Center

As an illustration, two of my all-time favorite options, the Analysis ToolPak, and Microsoft Solver Add-in can be found and activated by going to the Add-Ins category. The “Toolpak” provides a short list of powerful analysis tools that nearly any knowledgeable Excel specialist will find valuable.  The “Solver” add-in enables you to harness the sheer number-crunching power of contemporary computers, and with a bit of care, will provide you Best-Case-Scenarios for a potentially endless variety of business needs (and besides that, it is Totally Cool!).

Taking just a bit of time to add a Customized Interface to your Excel setup can provide you a wealth of powerful advantages in the future. If you haven’t explored these potential treasures, take 5-10 minutes at the end of the day and see if there isn’t something there which can make your life with excel better and more convenient. Why not “Have it Your Way…”?

Squares, Powers, and Roots

Though use of Powers and Square Roots is not a frequent need in business, knowledge of how these functions can be used in Excel can be of particular value, especially in the Financial arena. Dedicated financial calculators, such as the Texas Instruments BA II Plus, are more accessible, of course, but some skills with powers and roots in Excel can come in handy (maybe even with your children’s homework…).

The following are some of these valuable features and some examples:

The Power Function
As with all of the functions we are discussing this week, the Power Function works well with directly inserted numbers or cell references, and adds more flexibility than using the caret symbol (^) to raise a value to a power.

The syntax of the Power function is quite simple. If you wish to raise 21 to the third power (resulting in 15,625), you would use the following:

=POWER( 25, 3 )

The POWER function also allows you to raise a value to a Fractional power. Let’s say you with to raise 25 to the power of 1/3:

=POWER( 25, 1/3 ) This would give you a result of approximately 2.92

Square Roots
The syntax of the Square Root function is also not terribly challenging. If you wish to find the Square Root of 25, you would use the following:

=SQRT( 25 ) This, of course, produces a value of 5.

SQRT cannot handle negative numbers (not surprising), but you can work around that annoyance by using the Absolute function as follows:

=SQRT( ABS( -25 )) Once again, the result is a value of 5.

Power and Square Root.  You may not have immediate need for these functions in Excel, but they are good to know should the need ever arise.

Cheers!

Oddly Mod…

Back a long, long time ago when I was attending Sheridan Elementary School, there was a popular gambling game among 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 lunch money on buying new supplies of marbles to gamble with, I apparently wasn’t very good at the game.

Knowing whether entries are Odd or Even can also be helpful at times when working with your databases or tables.  Such instances can include street addresses, employee ID numbers, statistical studies, and several other pursuits.

So is there 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 probably the most intuitive. Assuming your data is in Column A, you could use this formula and copy down your range:

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

~ Or ~

2. =IF((ISEVEN(A1) = TRUE), "Even", "Odd") This IF formula incorporates the (also a bit obscure) ISEVEN function to generate the desired results.

Odds and Evens: Knowing this information can be Just What You Need at times to ease your data analysis.  Now, anyone what to make a guess about the marbles in my fist?

Texting

Well, perhaps not exactly “Texting” but in this case of our topic today, TEXT. Yes, using Text in Excel.

Obviously, Excel is all about working with numbers. However, there are times when you want to be able to manipulate your data into specially formatted Text. One excellent solution is the TEXT function! By using specially formatted strings, TEXT allows you to specify how you display the numerical value in text with format of your choosing.

The TEXT syntax is: TEXT(value, format_string) 

Let’s look at an observable example: Inserting the format_string $#,##0 as the second part of the TEXT function provides a monetary formatting with a dollar sign and comma providing immediate recognition. 

TEXT can also be used with Concatenation (which is one of my favorite somewhat-obscure functions) to return user-friendly reports. In the illustration below, the following formula (note the space after “is”) was used in the merged cells of A6 and B6:

="The Net Profit is "&TEXT(B4," $#,##0")

 The Bottom-Line Statement in the merged cells is cleanly formatted and immediately understandable to the resulting profit. This ultimately well-defined declaration of the data may not be necessary for the tech-savvy few, but I can assure you that it will be well-received by those non-techy types!

Named Range Shortcuts

As I have noted on several occasions, Naming Your Ranges is most assuredly a Best Practice when creating or maintaining a database in Excel. Employing Named Ranges is not very difficult, but using some Shortcuts is, of course, well, quicker and easier. Let’s look at Five Powerful Shortcuts that will be sure to elevate your reputation as The Excel Guru:

1. Name a Single Range
Select the range, and then click in the Name Box (far left on the formula bar) and type a One-Word Name. Piece of cake…

2. Name All Your Ranges at Once
Select your entire database including the headings with Ctrl + A and then click Ctrl + Shift + F3. Easy…

3. Navigate to a Named Range
Click F5 (Great shortcut for selecting Named Ranges!)

4. Edit a Named Range
To delete or edit a named range click Ctrl+F3. Couldn’t be simpler…

5. Display the Paste Names Dialog Box for Use in Formulas
Click F3 and use your arrow keypad to select (Extremely helpful when creating complex formulas)

Utilizing Named Ranges is an effortless way to expand your capabilities as an Excel user – Using Shortcuts with Named Ranges makes it easier.

More on Dates…

You’ll notice that the title of this week’s blog is “More on Dates”, not “Moron Dates” (a phrase I have overheard one of my more colorful colleagues use on an occasion…).

The fact is that what may Look Like a Date, may not “Play Nice in the Sandbox” with other dates that you have in your workbook. 

Example: Let’s say, for instance, that you have inherited an Excel workbook created by some Genius who no longer works in your company (I’m sure that his high IQ has led him to bigger and better things – cough, cough…).  Now you (a true Genius) wants to perform some Analysis that save the company countless hours and expense. The problem that this now-absent bright fellow has not documented his work, and 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.

So what is a well-meaning Excel Guru to do? DATEVALUE to the Rescue! Yes, indeed, my friends, DATEVALUE will calm your upset stomach, relieve that itch on your back that you can’t quite reach, cure that nagging doubt that you are being watched (you are, you know…), and make any Dates in your worksheet work in consistency with all of the dates therein. (Well, maybe I’m being a Snake Oil Salesman with some of the attributes, but it will make your dates play nice with each other…). DATEVALUE will instantly convert anything that looks-like-a-date into the standard Excel serial number, and you can then format it as you wish.  Formatting is, of course, rudimentary…

How Totally Cool is that! Mismatched dates can cause you a world of grief, but this simple DATEVALUE trick can save the day

One Note of Caution to Apple Users: For you MacBook Users out there, (I use Excel on one occasionally myself), Microsoft Excel for the Macintosh uses a different date system as its default (don’t ask me why…). 

Don’t Be Duped by Duplicates

The old adage, GIGO (Garbage In, Garbage Out) is a common premise to Good Database Management. Along with GIGO, it is also important to minimize or eliminate Duplicate Data in your records, as it can cause havoc when extracting information.

A Duplicates Issue can arise in many instances, and it is almost never intentional: Combining of departmental databases, updating data, common entry errors, and others.  Ever since Excel 2007, there are ways to Quickly Remove all of you duplicate data.

How to Remove Duplicates:

These days, it is amusingly easy to remove your duplicate records, and to do so with a Bit of Discretion:  

1.  Select the entire database (Caution: Do not select any self-generating key fields)
2.  Go to the Data Tools group on the Data tab, and click Remove Duplicates
3.  Select your database or table and click OK
4.  A Remove Duplicate information box will pop up (as shown below) giving you options as to how you want Excel to identify “Duplicates”
5.  Using our example as a reference, you can then choose to eliminate all duplicates by Month, Rep, and/or Sales. 

 When you first consider the task of Removing Duplicates to maintain the Integrity of your data, it may seem quite challenging. As with nearly all things Excel, however, once you know how to do it, it seems so Simple, you may actually Laugh…