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!

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.