Insert Function Wizard Revisited

Occasionally I like to revisit the topics that the readers of this blog have found the most interesting in times past. Over 2 years ago, we discussed the Insert Function Wizard, and it has become one of the most popular subjects that has been addressed in the past 4 years.

Whichever version of Excel you may be using, the Insert Function Wizard can be a very helpful in performing a bit of Magic when you need it, )and who among us can’t use a little “Magic” now and then?)

Let’s face it, there are times when you may think that Excel has a Function that you can use in a formula, but you just don’t know what it is called or how to use it. You can describe it to another user, but what Excel calls it is simply a mystery. The solution is (a drum roll and an Alacazam here…) the Function Wizard!

Where Is It?
To find the wizard, to Insert Function under Formulas if you are using Excel 2007 or 2010, and type a brief description of what you want to do. For instance, let’s say you want to calculate what your Loan Payments are going to be when you buy the new GuruMobile you have had your eye on.

How Do You Use It?
1) Pull up the Insert Function Wizard
2) Type your description in the search textbox (in this case, “loan payment”)
3) Select the function (in this instance it would be PMT)
4) Complete the Function Arguments and click OK

Magic
Alacazam! You have your specialized function for your formula! Give it a try and find out how easy it is to perform a bit of Magic when you need it.

Excel on iPad?

As the business world is becoming ever more aware of the value of getting Real Work done on a tablet, it is high time we take another look at doing spreadsheets on the increasingly ubiquitous iPad. With utmost respect to Android-based tablet users, (Documents to Go is outstanding on these fine devices, but that is another story unto its own), we are just going to be looking at the world of Apple in today's blog.

The Apple spreadsheet application, Numbers, is a truly capable workhorse which can abide quite nicely with Excel. For anyone who has wrestled with a laptop on a long plane trip, or simply didn't want to tote their 7-pound monster to a quick business stop, you can understand the beauty of being productive on an iPad.

It is true, of course, that not all Excel’s features are available when working on an iPad (for instance, it has about half as many functions). Unless you are getting into more uncommon tasks, however, it is more than sufficient, and there is something very satisfying about being flopped on a sofa and still being able to work on a spreadsheet. Not only that, but as Apple continues to refine their software, the possibilities continue to grow.

Compatibility

So, what about compatibility with Excel you ask? Obviously, since the world revolves around Excel, (or at least that’s what we Excel fans think…), you want to be able to import/export between Numbers and Excel the good news is, of course, that you can, subject restraints on such things as Pivot Tables (no such animal in Numbers).

Navigating/Viewing

Although it is a bit foreign at first, tapping to select cells and using the convenient selection handles to choose a range becomes second-nature quite quickly. The now commonly known pinching gestures zoom you in or out on your data and charts, and it is easy to get hooked on these new ways of getting around a spreadsheet.

Sharing Your Work

It is, of course, always good to have options for sharing your work. Once you have created your spreadsheet masterpiece on your iPad tool, you can easily email it in its original Apple format, a PDF or, of course, as an Excel document.

Although it may not be for everyone, you may find this novel way of interfacing with your data a pleasant and productive alternative way of working with your Excel creations. Cheers!

Debugging Techniques

There are times in any Excel user's life when you're just not sure what is going on behind the scenes in your workbook. In this edition of Excel Enthusiasts, we are going to take a look at two fundamental, but Vital Techniques for investigation of this nature.

1)  Are there Formulas in My Workbook?

Rather than clicking on each cell to reveal the formula in the Formula Bar, (as you needed to do in some of the older versions of Excel), you can reveal all of this information in your entire worksheet with the Keyboard Shortcut:

Ctrl + ~

This simple keyboard combination unveils the formulas in All of your cells, from which you can then determine if there is a flaw in their construction.

2)   What If I Need to do an In-Depth Review of Precedents and Dependents?

If you need More Power in seeing what your data is doing and where it comes from, look no further than the Formula Auditing group on the Formulas tab. There you will find Trace Precedents, Trace Dependents, and even Show Formulas. The information is returned in easily-understood graphics that help you make sense of the workbook you inherited from old what’s-his-name.

These two Fundamental Tools can make your Excel life a little easier. (And who wouldn’t want that?...)

Take Excel for a Spin!

Since we haven’t discussed “Spinner” buttons for over 2 years, let’s take a look at this sophisticated, but easy-to-master, way to control your data.

To add a Spinner in Excel 2007 or 2010, click the Developer tab ribbon(which is simple to add to your toolbar, if it is not already there). Then click Insert (on the ribbon, not the toolbar), and click Spin Button in the Controls section. (You can size the Spinner to your liking.)

Now is when the Cool Stuff begins! Right-click on the Spinner, and then click Format Control. On the Format Control tab complete the values as follows (this is a example, ma’am or sir, only a example…):

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

Now when you click the Spinner control, cell B2 is be updated according to the parameters you set. If you have created a worksheet where other cell values or results are dependent on the value in B2, your worksheet will update according to the quantity you select with the Spinner.

How cool is that! Make your Excel worksheets look like someone spent hours of programming time on them in just 5 minutes. Some amazing additions to your worksheets are just too good to pass up, so give this a try sometime (people will think you are a Star!).

Data Validation Formulas

The simple use of Validation in Excel can serve the worthy purposes of maintaining database integrity. This tool in Excel is easy to use and can save you time and potential errors.

Where this Valuable Excel Feature really shines, however, is when you team it up with Formulas! We will look at some Great Examples of this, but first it is important to note that the formula you use must result in a True or False response. You can enter a formula of this type in Validation by selecting the Custom option in the dropdown box.

Here are Some Highly Useful Examples:

Accepting Non-duplicated Data Only
This example allows No Duplicate Entries in cells A1:B30:
      =COUNTIF($A$1:$B$30, A1) = 1

Accepting Text Only
Supposing your active cell is A1, the following will allow only Text entries:
      =ISTEXT(A1)

Accepting Fridays Only
For the cell A1, this formula will allow only dates that are Fridays:
      =WEEKDAY(A1) = 6

Accepting a Larger Value than Previous Cell
Simply insert the following formula in your Custom Validation box:
      =A2 > A1

Data Validation is a fine example of another tool any Excel Guru should have in his or her tool belt. Give it a try sometime!