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

Find Your Home on the Range

If you are an old pro at Excel, you probably know that Naming Ranges can save you a lot of time and make your formulas more intuitive to any user of your data.

Using a Named Range in a formula does away with the need to make the range an absolute reference because it will always point to the correct range, regardless of where you copy the formula.

For example, let’s say you are working in Human Resources, and you are using a dropdown box in a cell that refers to a list of employees. For convenience and to assure consistent spelling, you have created the dropdown box using the List feature on Validation.

Rather than having to put up with the bother of adding/deleting employees from the reference list and then having to adjust the list range, you can create a Named Range “Employees”, (with sufficient room to add names in the future), and refer to “Employees” in the list box in the Validation dialogue box. Cool!

Using a list with the Validation tool in Excel is a useful way of maintaining consistency in a spreadsheet. Combining it with a Named Range can make it More Effective!

Two Easy Ways of Creating Named Ranges:

1.   Use the Name Box
   a)  First Select the Range of cells you wish to name
   b)  Now click inside the “Name” box on your toolbar at the upper-left of your screen
   c)   Type Your Chosen Name for the range
   d)   Hit the Enter key and Bamm! Your range is named.

2.   Use the Create from Selection Tool
    a)  Select your Table or Database (including the headers…)
    b)  Go the to the Defined Names group on the ribbon
    c)  Choose Create from Selection
    d)  Assure the Top Row is checked
    e) Click OK and Presto! All of your ranges are named at once!

Named Ranges. Another Best Practice for Excel professionals!

Slice and Dice Your Tables

Looking for a new way to get more information out of your data? Slicer may be just the ticket!

First introduced in Excel 2010, Slicer offered a relaxed way to filter your Pivot Tables with the ease of click. Due to its popularity, Microsoft expanded Slicer’s capability to include Any Table in the Excel 2013 version.

Similar in many ways to using Report Filters, (as well as your regular Filter in a non-pivot table), one of the beauties of using a Slicer is the immediate creation of an engaging Interactive Interface for the user. Although many will argue that this is not a compelling reason to use this tool, playing around with them a bit will probably sell you on their appeal.

1.  Using Excel 2013, first make sure you have transformed your table in a true Table by selecting any cell simply select any cell within your data, and going choosing Table from the Insert menu.

2.  Then select any cell within your new table and from your Insert ribbon choose Slicer from the Filters group. A popup menu of your fields will appear, giving you options for filtering your data.

3.  The dialog will ask you which columns you want to create slicers for. Each slicer you choose will filter a single column of data, and you can create slicers as many columns as suits your needs.

4.  Note that the slicers are Additive – you can keep applying more filters as you develop the view you are seeking.

Not just a duplication of the existing table filter drop downs, Slicers give you another tool for Focusing on Your Relevant Data. Another worthwhile tool in the newer Excel versions.

Three Quick Tricks!

This week we are going to take a look at a medley of tricks that do not necessarily fall into any neat category. All they do is Save You Time and Make Your Excel Life a Little Easier.

1. Lock Your Formatting
Using the Format Painter is a terrific way to save time adding a professional look to your worksheets. When you format and select a cell, you can click Format Painter and paste the formatting into any single cell.

The Trick that many Excel users do not know is that if you Double-Click the Format Painter, it will Lock the formatting so you can apply it to as many cells as you like! Piece of Cake!

2. Move the Quick Access Toolbar
Okay, you've customized the Quick Access Toolbar, but you don’t like having it way up in the upper-left corner (after all, why stretch all the distance with your mouse!).

You can opt to have it displayed at the bottom of the ribbon by simply clicking on Show Quick Access Toolbar below the Ribbon in the dropdown menu of the Quick Access Toolbar. Easy as Pie!

3. Prevent Error Checking
The Error Checking function in Excel is highly useful for pointing out Inconsistent Formulas in a particular range. There are times, of course, when you may need to omit a range’s formula in a few cells. Excel will then Flag what it perceives as an Error!

To avoid this annoyance, go to Excel Options and choose the Formulas group. Simply clear any of the error-checking rules that are bugging you. Piece of Cake!

Three Quick Tricks. It’s all Cake and Pie!