Wednesday, August 24, 2016

Be CHOOSY!

There are countless undiscovered treasures in Excel, so it pays to explore a bit and be Choosy.  For instance, Nested IF functions can be very powerful, but they have limitations (the maximum nested functions is 7 in older versions of Excel) and they can be a bit difficult and cumbersome.  That is why it is nice to have a Choice!

Having a Choice is almost always a Good Thing, and fortunately, there is a preferable alternative to using the occasionally awkward IF functions. The CHOOSE function is often a better selection, as it is considerably more versatile! The CHOOSE function is remarkably straightforward and simple to use, and is best when combined with other Excel functions. It quite humbly returns a value from a list based on a given Position (Index Number).

Here is the Unsophisticated but Valuable Syntax:

CHOOSE( Index Number, Value1, Value2, ... Value n )

Now for Some Basic Examples:

=CHOOSE(3, “North”, “South”, “Central”, “East”, “West”) returns Central

It also works with ranges:

=Sum(Choose(2, A1:A30, B1:B30, C1:C30) returns the Sum of B1:B30

You can, of course, link it to the value in a cell, making it much more Flexible. For example, you could link it to Cell A1 which contains the Index Number.

If A1 contains the number 4, then =Choose(A1, “North”, “South”, “Central”, “East”, “West”) would quite obviously return East.

The function can handle up to 29 options, which makes it a great choice in many real-life situations. The CHOOSE function is one of the Undiscovered Treasures in Excel. Be Choosy and give it a try sometime!

Wednesday, August 17, 2016

MicroGraphs Rock!

Let’s face it, Not Everyone has a current (or even near-current) version of Excel. This is particularly true in many corporate settings, since not all companies see the value in upgrading our favorite spreadsheet program.  As you may know, “Sparklines” first appeared in Excel 2010, and are for many Excel users, a quick and easy way to graphically demonstrate their data without much work.  Sparklines make it a breeze to create readily-intelligible micrographs within the cells of your Excel spreadsheets that highlight important details of your data. 

But what if you use an older version of Excel, you ask?  Can I still make cool, little micrographs in my workbooks?  Yes, You Can!

A very effective way to make these engaging little graphs is to incorporate an unusual and diminutive Formula along with a splash of Conditional Formatting.  Let’s take a look at how this can be done…

Let’s say you have a Crack Sales Team in your company, and you want to graphically show their results without going to the trouble of creating a bunch of individual charts. Using the illustration below, put the Producers of your company’s products in Column A, and in Column B put the Units Sold each producer has sold. Here is the super-simple formula you should put in Cell C2 (and then copy it to C8):

= REPT( “l” , B2/1,000) 
 
For each Approximate Count of One Thousand, the formula puts an old-fashioned Hash Mark, (using a bold, simple font, such as Trebuchet or Arial works well), in Column C. With a bit of cool Conditional Formatting to add color, the result is a clean, professional report that integrates MicroGraphs!

Using these elementary graphs makes your data Visually Comprehensible and enhances the user’s understanding of what is being said in your Excel reports.
Try it out sometime if you are using a pre-2010 version of Excel, (or even if you are using a more contemporary version), and see what you think. They really do Rock!

Thursday, August 11, 2016

The MMXVI Olympics

Excel users, as I am sure many of you will agree, can be a rather serious lot.  Fun” does not seem to be in many guru’s vocabulary.  Although of no real Practical use, it can be interesting, (in a geeky sort of way), to play around with Roman Numerals. For an even very experienced Excel user, it is particularly interesting to find that an application has a built-in Roman numeral function.

Practicality, of course, can be overrated, and it is readily apparent that Hollywood, Football and, of course, the Olympics have all used Roman numerals on a regular basis. If you also wish to do something off-the-wall in an Excel report, (I probably wouldn’t recommend it for your next quarterly report to your boss, unless you are in the market for a new job soon…), you can use the Roman function.

For a classic numeral, (other formats are available, but why complicate things), simply enter a value in cell A1 and type, “=Roman(A1)” in cell B1. Hit Enter and Presto, a Roman numeral of the A1 number! If you typed in 2016 you will get the Roman Numeral representing this year’s Summer Olympics.  Pretty Cool!


If you are a Super Geek when it comes to Roman Numerals, and you want to explore this function a bit more, you can use the Second Argument option with the ROMAN function, which converts your results to varying levels of brevity.  Note the following values in the table below:
 
 
ROMAN Numerals.  A bit of Fun in this Olympic year!

Thursday, August 4, 2016

Organize Your Data

Today we are going to look at some important Best Practices for organizing your data and laying out your Excel workbooks. These are not, of course, Absolute Rules, but if you follow these practices, you will find Greater Control and More Options in your Excel adventures.

Data on One Worksheet; Reporting on another Worksheet
Whenever possible, put all of your data in one worksheet, and your reports/information in another worksheet. The fewer the worksheets you have, the easier it will be for your users (peers, employees, executives, and yourself, of course) to navigate and obtain the information they want to see when they want to see it.

No Blank Rows
Avoid blank rows and columns in your data table. Your formulas will work better and your data will have greater integrity.  Just don’t do that.

Deconstruct Your Data for Better Analysis
Always try to divide your data down to its Minimum Components. You will be able to use more powerful functions and search your data much more effectively. For instance, if you have a database of employees, create separate fields for the first, middle, and last names (you can always easily combine them later using Concatenation if you find it necessary to do so).

Format Your Headings 
Use a bold font and centering when using headings. Many of the built-in properties in Excel functions will more easily recognize them as headings when they are bolded.  Besides that, they just plain look better!

Sort Your Data 
This is a good habit to adopt, and certain Lookup functions rely on your data being sorted in a logical order.  If it makes logical sense, why not do it…

Columns are for Fields
Excel obviously has far fewer columns than rows, so keep things simple by using the columns for the fields and the rows for the individual records.  This may not be as necessary as Excel becomes more powerful, but it will almost always be More Intuitive.

If you follow these guidelines, you will easily be able to use the most powerful built-in Excel features that require organized data and a logical layout. As I have said in the past, Organization = Simplification = Information.  Good Stuff!

Wednesday, July 27, 2016

The Intersect Operator Revisited

The Intersect Operator is one of my favorite obscure Excel tools. Although relatively obscure, it is highly useful, as there are many ways it can be used to garner information from Excel tables without using awkward formulas that can take time and patience.  Happily, the Intersect Operator, is not only Utile and Flexible, it is also quite Easy to use (which is always a good thing…).

The syntax is simply: = RangeName1 RangeName2 (Please Note: No brackets or commas…) Now, you can do this by using the generic names of the ranges (i.e. =C2:C34 B10:H10), but that is a bit awkward (and who needs that!)

As you may know, I am a huge proponent of creating Named Ranges in your Excel workbooks. This can be Easily Done by highlighting your entire database including the headings with Ctrl + A and then click Ctrl + Shift + F3. This will bring up a default dialogue box as shown below (Just click OK, and Bamm, Named Ranges!).
Once you have your ranges Named, you can then (per the example below) find a value with the rudimentary function: =East Quarter3

But, Wait, That’s Not All! You can combine the useful Intersect Operator with statistical functions to find even more information! For instance, if you wanted to find the overall average quarterly sales for the “East” region, you could use: =AVERAGE((East Quarter1):(East Quarter4))

Are there many other ways of doing this in Excel?  Of course, but any good Excel Guru should have the Intersect Operator in their bag of tricks. (You can never have too many tricks…).

Thursday, July 21, 2016

The Excel Slot Machine



Excel users can be a very sensible lot, but Not Everything in Excel needs to be so terribly Serious. In fact, you can take some the very sensible tools in Excel and have some Fun with them. The post I made back in late 2012 has been one of the most popular in the 8 years I have been writing this blog.

It all revolved around using the fascinating RAND function for purposes not originally intended. In this case, an Excel Slot Machine!

The intriguing RAND function returns a Random Number that is Greater Than or Equal to 0 and Less Than 1. Each time your worksheet recalculates, (by reopening or forced recalculation by pressing F9), the RAND function returns a New Random Number.

Of course, some hard core statisticians have voiced concerns about the true Randomness of the RAND function (it is prone to sequential correlations if large runs of numbers are taken), but it more than suffices for most demanding statistical applications, and is certainly acceptable for us mere mortals.

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: =RAND()*(b-a)+a

If you want only Whole Numbers try using: RANDBETWEEN()

For example, =RANDBETWEEN(1, 100) will produce a Random Whole Number between 1 and 100.

There are countless statistical applications, of course, but you can also use it to have a bit of Fun. 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). I will be happy to send a copy to you.

Is an Excel Slot Machine an acceptable application of the power of Excel? Well, certainly some of the more Sensible ones among us may not think so, but hey, it’s good to have a bit of fun now and then. The RAND function. Great for use in statistical applications, building games, slot machines, and other Fun Stuff!

Thursday, July 14, 2016

Quizzes in Excel

Only a very small percentage of Excel users ever get around to exploring Form Controls.  This is unfortunate, as they can be instrumental in creating engaging, dynamic Excel workbooks and tools.  Option Buttons, (one the tools contained in Form Controls) are one way to add this ability to your Excel creations, and make them more professional in the process.

Option Buttons can be very useful in creating Quizzes in Excel, and although they can at first appear a bit complex, they are in fact quite easy to create.  The following is a Step-By-Step Explanation of how to create your first quiz using Option Buttons:

1.   Add the Developer Tab on your toolbar:

a.   Click the File tab and then click Options, and then click the Customize Ribbon category.

b.   In the Main Tabs list, select the Developer check box, and then click OK

2.   Under the Developer tab, choose Insert/Form Controls/Option Button

3.   Draw the Option Button box on your worksheet and repeat for however many buttons you wish to have for choices

4.   Now, (this is Important), to make the buttons work together, return to your Insert dropdown and choose Group Box from the Form Control group

5.   Draw your Group Box all the way around your Option Buttons

Hang in There, We are Almost Done:

6.   Right-Click one of the Option Buttons, choose Format Control from the dropdown and select a Cell Link that you want your Option Buttons to populate

7.   Now is where it get Fun: Create a Formula that is based on the value that is shown in the Cell Link

8.   For example, let’s say you have linked three Option Buttons to Cell $E$5 and you wanted Option 2 to return a “Correct” response and the others to return “Incorrect”, here is what you do:

In cell E6, type =IF(E5=0,"", IF($E$5=2, "Correct!", "Sorry, Incorrect"))

Now when the quiz-taker chooses Answer 2 of the three possible, they are rewarded with the “Correct!” feedback.  Option Buttons, a remarkably forthright tool that let’s you do Even More in Excel!