Micro-Charts are Cool!

Charts and Graphs are, without a doubt, one of the things that Excel does best. They make your data Visually Accessible and enhances the user’s understanding of what is being said in your spreadsheets.

Micro-Charts are quite new, and have gained in popularity. They are a decidedly different and easy way to better communicate your data, but the earlier versions of Excel do not natively include useful tool. There are some workarounds, of course, and here is a way to make these cool little charts in Any Version of Excel!

This is Way Easy! Let’s say you have your Products in Column A as illustrated above, and in Column B you have the Items Sold. Here is the formula you should put in Cell C2 (and then copy it to C12):

= REPT( “l” , B2/10) 

Simple, isn't it?  For each Approximate Count of Ten, the formula puts a Hash Mark, (using a bold, simple font works well), in Column C. Add a bit of Conditional Formatting, and result is a clean, professional report that incorporates a Micro-Chart!

Try it out sometime and see what your colleagues think. I’m sure you will get Great Reviews!

Spinning!

Using Spinner Controls is one of my favorite ways of adding Style and Functionality to many of my Excel workbooks. Spinner buttons provide an intuitive approach to controlling the data you enter into your spreadsheets, and make you look Cool doing it (that is, after all, the most important thing…).

First you have to find where they are located, of course. In Excel versions 2010 or 2013, do the following:

1. Click on File and select Options
2. Choose Customize Ribbon and then put a checkmark next to Developer

After you have completed the above and clicked OK, you will find the Developer ribbon available from your toolbar. Along with a great many somewhat intimidating resources, the Developer ribbon supplies all of the Form Controls (simply click on Insert to see the controls available). Hover your mouse over the selections until you see Spinner button, give it a click, and draw the button on your worksheet.

Now you are all set to have some fun! Right-click on the spinner, and then click Format Control. In the initial test of this cool control, enter the following in the boxes on the Control tab dialogue box:

1. Current value: 1
2. Minimum value: 1
3. Maximum value: 20
4. Incremental value 1
5. Cell link: $E$8 (Note: Any of these values can be of your own choosing…)

Now when you click the Spinner control, cell E8 is be updated according to the numbers you choose. 

Try it the next time you create a worksheet where other cell values or results are dependent on the value shown in a single cell, and put a Spinner in charge of the values chosen. Your worksheet will update according to the quantity you select with the Spinner, and you will undoubtedly gain even more prestige as an Excel Guru!

Spinner buttons! They can make the world go round!

DATEDIF & TODAY

Bringing more ease and efficiency to your Excel work is always a worthy goal. How Excel handles your typical numeric data is for the most part, quite intuitive. Dates, however, can be a bit troublesome at times.

As most seasoned Excel users know, the way Excel handles dates can be a bit puzzling at times. Finding the Difference between two Dates, for instance, is not readily intuitive. Here is where DATEDIF shines!

Curious Note
One small curiosity about DATEDIF is the fact that it is not a “documented” function in Excel. You cannot, for instance, go to the Insert Function wizard and find it in any of the lists.

The Syntax of the Function is as Follows:

=DATEDIF(“First Date”, “Second Date”, “Time Interval”)

Where the Time Interval is expressed as follows (Note: Unless referring to cell values for the dates, all arguments must be enclosed in Quotation Marks):

“d” (Days) = Number of days between the dates
“m” (Months) = Complete calendar months between the dates
“y” (Years) = Complete calendar years between the dates

A novel application of this function is to nest the TODAY() function into it and calculate a Person’s Age. The TODAY() function returns the Current Date, and when used with DATEDIF, it can produce an Excel calculator that you may find amusing. (The “BirthDate”, of course, can refer to an easily changed to any easily updated Cell Value):

=DATEDIF(BirthDate, TODAY(), “y”)

Another interesting way to use DATEDIF and TODAY is to make a dynamic Day Number Calculator. The elapsed number of days in the current year can be determined in a Single Cell with the formula:

=DATEDIF(“1/1/2014”, TODAY(), “d”)

By keeping this little hidden gem in mind, you may find great many ways to use the DATEDIF function in the future. Curious that it is not documented, isn’t it?...

Proper English

This week’s topic arises from yet another External Database that I imported into Excel recently. In addition to using the import functions native to Excel, software such as MYSQL, Filemaker, Adabas, and others do a wonderful job in the hands of skilled practitioners.

As is often the case, the data you import into Excel pays little heed to Proper Capitalization. All lowercase is quite common, even when it is a list of Proper Names. This is not crucially important, as it probably doesn’t affect your recordkeeping or data calculations, but it certainly doesn’t look very professional!

No Excel Guru is worth his or her lofty title if they even consider changing the capitalization of the names Manually (the thought probably makes you shudder…). So what do you do when you download 3,000 names, and you want to change them to the Proper case? The answer is, of course, use the PROPER function! (It really is Proper, you know…)

For example, let’s say that you have imported a database of employee names, and you have popped them into a list that runs from A2 to A3002. In cell B2, simply insert the following formula:

=PROPER(A2)

Give the handle of B2 a quick double-click to copy this simple formula down to the bottom of your list, and Alacazam! Proper Names!

It’s Not Perfect

If your list contains names such as McAndrews or MacDonald, or something really offbeat such as DeLaMartre, (there are only about 10 of us in the U.S.), you will probably need to change those manually. Ah, well, nothing’s perfect, eh?...