Flash Fill

It isn’t often that I get really excited over a new Excel feature, but this one is The Cat’s Pajamas (to use an old phrase from the 1920s…)!

Flash Fill enables the Excel 2013 user to Reformat lists of information with remarkable ease and speed.  This fabulous new tool was first introduced in Excel 2013, and automatically fills your data when it senses a pattern.  It works with lists of names, numbers, and a variety other data.

For Example, we will first look at how it works with names.  Let’s say you have imported a list of names that are in the format of Last Name, First Name with each name entirely in one cell.  For this example, we will assume that you wish to convert these names into two fields, with the first field being just the First Names and the second field just the Last Names. 

With past versions of Excel, you could do this by using somewhat complex formulas that are a bit intimidating to many users.  With Flash Fill, however, it is amazingly simple.  For our example, all you need to do is type the first name of the first entry in the field adjacent to your list, and go to Fill / Flash Fill on your Home ribbon.  Excel does the rest by automatically filling in the entire field of first names!  You can then extract the last names using the same approach.

Alternatively, you can just start typing the second first name in the new field, and Excel will sense the pattern and suggest filling the entire range (no need to go to Fill / Flash Fill).

One More Example:  Let’s say you have a field of Social Security Numbers that are not formatted with hyphens.  Simply type the first SSN (with the hyphens inserted) in the field next to the list, and use the same technique as we used for the names.  Voila!

 Flash Fill.  It’s Lightning Fast!

Random Ideas

Among the many obscure functions in Excel is the useful and versatile RAND function. Each time your worksheet recalculates, (by reopening or forced recalculation by pressing F9), the RAND function returns a new Random Number that is greater than or equal to 0 and less than 1.  The syntax for the Rand function is simply:  =RAND()

So, what are some thoughts on using this interesting function?  Well, how you use it is only limited by your imagination, but here are a few examples of Random Ideas:

·       Use =RAND()*100 to return random numbers between 1 and 100

·       Or =RAND()*100 + 100 to return random numbers between 100 and 200

·       If you want only whole numbers, use:

     =INT(RAND()*100+100 to return integers between 100 and 200

Or consider this, let’s say you have a database of 1,000 records from which you want to take a random sample of 15% of the data.  If your data is, for instance, in Column ‘A’, put in the following formula in Column ‘B’, and Autofill it to the end of data field:

=RAND()<=0.15

Then simply sort your columns based on Column B and retain the first 150 of your data field.  This will, of course, change every time the worksheet is recalculated because RAND is, well, Random!

Some extremely clever Excel Enthusiasts have created some amazing games using RAND.  For my own part, (modest in comparison to what some others have done), I have used the RAND function (along with other functions and graphics), to create a Vegas-style Slot Machine in Excel.  If you are interested, please send me a request at ExcelEnthusiasts@gmail.com if you would like a copy of the Slot Machine workbook, and I will be happy to send it to you.

By the way, if you have any RANDom Ideas regarding this cool function, I would love to hear about them!

Converting to British Metrics

As with many technological advances that preceded it, it is axiomatic that the Internet has furthered the effect of the world getting smaller.  Mastery of different systems of commerce and communication has become all the more important as we evolve at light speed.  The fact that hundreds of Excel users in the United Kingdom access this blog each month is a minor testament to our interconnectivity. 

With the many websites devoted to exchanges, it is a rather simple matter to Convert from One System of Measure to Another.  If you are working in Excel, however, there is no need to access any outside tools for this purpose. The built-in CONVERT function can handle most of our needs in this regard, and is one of those obscure little tools in Excel that you may find to be very convenient.

 

The CONVERT function can translate a Wide Variety of measurements that include Time, distance, Temperature, Weight, and much more.

Using the appropriate abbreviations for the Units, the Syntax of the CONVERT function is as follows:

CONVERT(Number, From Unit, To Unit) where:

1. Number is the Amount in From Units to convert
2. From Unit are the units Being Converted
3. To Unit are the Units in the Result

Some Commonly Used Abbreviations are shown in the table below.  For a Full List of all the measures that can be converted, please see the CONVERT function Help topic.

Meter	“M”
Inch	“in”
Foot	“ft”
Yard	“yd”
Degree Celsius	“C”
Degree Fahrenheit	“F”
U.S. Pint	“us_pt”
U.K. Pint	“uk_pt”
Gallon	“gal”
Imperial Gallon	“uk_gal”
Liter	“L”

The CONVERT function.  Another one of the Little-Known Gems in Excel that can make your life just a bit easier.  Give it a try some time…

Mimic a Webpage

Occasionally one of my students will express dismay at the difficulty of navigating around a large Excel workbook. Although I am a great advocate of containing your data and Excel reports in as few worksheets as possible, the fact remains that some workbooks are by necessity quite large. This is also true of websites. Some sites are by their nature quite expansive and would be considerably difficult to navigate if it weren’t for the ubiquitous linking system.

So what does a clever Excel Guru do to solve this problem? He or she uses Hyperlinks!

Most Excel users do not take advantage of hyperlinks to enhance the ease-of-use of their workbooks. By employing this feature within a workbook, you can add both Functionality and Coolness! (And, after all, “Coolness” is what really counts…)

Inserting a Hyperlink into your workbook is Incredibly Easy. Simply choose the cell or shape that you want to put the link into, and Right-Click / Hyperlink. Then click on the appropriate Link-to area in the left-hand column, and complete the address information. Bamm! Simple as that!

For Coolest effect, you can Mimic a Website by drawing a Shape like a Button for your link. Here is what you do:

1)  First of all, get rid of all of your extraneous gridlines by going to the View ribbon and Uncheck Gridlines.

2)  Go to the Insert ribbon, click on Shapes, choose a shape that makes your spreadsheet distinctive, and draw it onto your worksheet (take a few moments to make sure the formatting is just right…).

3)  Then Right-Click / Hyperlink, and finish the link as previously noted.

Hyperlinks. Add convenient navigation options and Pizzazz to your Excel sheets with this simple tool. Think about them the next time you paging through a big workbook. They may be just the touch of Coolness and Functionality you need!

Excel on iPad

Microsoft Excel, along with Word and PowerPoint, have just been made available on iPad. This is a terrific boon for those of us who extensively use iPads, as well as desktops and laptops. I immediately downloaded it when it was released last Friday, and I have been putting it through its paces this week.

First of all, I want to cut to the chase and say that I really, really like the way Microsoft has created this touch-centric Excel app for iPad. I find it to be intuitive, powerful, and remarkably compatible with the PC versions of Excel. 

Ever since iPads were first introduced four years ago, I have used Apple’s Numbers app for any spreadsheet work I did on my tablet. Although I like Numbers, and I have always thought it does an admirable job, it simply does not compete favorably when it comes to compatibility and ease-of-use.

Excel for iPad makes better use of the screen real estate than Numbers, and it is a breeze to enter text or formulas. It is also easy to access the various Excel features and functions with which we are all accustomed. The toolbar includes the familiar HOME, INSERT, FORMULAS, REVIEW, and VIEW. Clicking any one of them brings up a small, but efficient, Ribbon.

Tapping the Insert Function (“fx”) button automatically inserts an “=” sign into your formula bar, and displays the available functions. Included in the functions are not only those that are commonly used, (as you might expect in a scaled-down version of Excel), but the sophisticated and even somewhat esoteric Database, Financial, Logical, and Text functions.

A QWERTY keyboard and a Special Keyboard for data entry are effortlessly switched at a click of a button located on the right side of the screen. Navigation is enhanced with an Arrow Pad and a Tab button on the data entry keyboard. It truly is Cool!

If you are an iPad user, I think you will Excel for iPad to be a pleasurable and productive experience without much of a learning curve. And that, dear friend, ROCKS!