Intersections

This is a Really Cool Trick that you probably have never run across! The concept is known as Range Intersection, and identifies the value in the cell that two ranges have in common.

The syntax is remarkably effortless: =Range1 Range2

You simply state the two ranges in the formula separated by a Space (Important Note: No commas, semicolons, etc, Just a Space).

Where using a Range Intersection becomes particularly powerful is when you use Named Ranges in your table. Using the following table for an example, I have named the columns in accordance with their Headers (Quarter1, Quarter2, etc) and the rows according to the State in Column A (California, Minnesota, etc).

The formula, =Minnesota Quarter2, was then put into cell B7 and the result, 1,420, was returned. This is easily verified by identifying the Intersecting Cell of the two ranges: C3.

Using the Range Intersection tool is an elegant way to select values in a Cross-Tab table. Just another way to accomplish a daily task with our favorite software. Cheers!

Customize Your Date Formats

Most Excel users are familiar with the mm/dd/yyyy date format. It can, however, often be very convenient to use Custom Date Formats. It is surprisingly easy to set up custom formats on your worksheet, and this week’s blog will illustrate several highly practical and useful formats.

To customize the Date Formats of your cells, simply do the following:

1. Right-click the cell or range, and choose Format Cells from the dropdown
2. Choose Custom under the Category
3. Type in your Format Code in the Type dialog box

Using today’s date of June 20, 2012 as an example, I have created the following table that exemplifies this cool technique. Please Note the Formula in the Formula Box ( =TEXT($A$1, A4 ) which references cell A1 and the Format chosen:

By using Custom Date Formats, you can modify how Excel displays your dates, and maybe make your life just a little bit easier. Give it a try sometime! It really is easy…

The Any-Version Anti-Chart

Excel 2010 (and the next Excel generation rumored to be released late this year) has brought new ways of providing Visual Displays of data other than traditional charts. This is a true advance, since creating a dynamic traditional chart can be a bit of a hassle at times.

But what if you are using, (or your audience is using), earlier versions of Excel such as 2003 or 2007? Is there a Cool Way to provide a Visual Display of the data without using a conventional chart?

Yes, There Is! Using the little-know and seldom used REPT function, you can produce an Anti-Chart that can provide you with a display that is immediately understood, dynamic, and easy to create. The REPT function repeats a text string the number of times you specify. For instance, the following formulas returns Nine Asterisks: =REPT(“*”, 9)

The cool thing about this innovative, yet simple technique is that it works in Any Excel Version. You can therefore create one of these visually interesting displays and send it to other users without concern as to their version of Excel.

Let’s say that you have a table with Months in Column A, Sales in Column B, and we want our Bar Chart (we’ll call it an Anti-Chart, since we are not using Excel’s typical charting capabilities) in Column C. We can then insert the following function in our Chart Column C: =REPT(“*”, B2/1,000):

You will note that we are dividing the number in B2 by 1,000 is to keep the scale of the resulting Anti-Chart to a reasonable width. Simply enter the formula into cell C2 and drag it down to complete your visual illustration of your data. Bamm! Anti-Chart!

Cheers!

Hyperlink Coolness!

Navigating via Hyperlinks is a way of life for us on the Internet. What many users do not know (or at least do not take advantage of) is that you can use this feature within an Excel workbook to add both Functionality and Coolness!

By using Hyperlinks in a worksheet, the user can instantly access another area in the workbook, another relevant workbook or application, or a place on the web. You can insert a hyperlink into a cell or a Shape in any version of Excel.

Inserting a Hyperlink into your workbook is, as are most things in Excel when you know how, Super Easy. In any Excel version of the past 10 years, simply choose the cell 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! You have your Link!

If you want to make your worksheet Even Cooler, however, you can quite easily draw a shape like a Button for your link. Here is what you do:

1) First 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 whatever shape makes you happy (and makes your spreadsheet distinctive), draw it onto your worksheet and format it to your specifications.

3) Then Right-Click / Hyperlink, (It bears repeating that “Right-Click is Our Friend”, as it gives you Context Sensitive dropdown choices), and finish the link in the same way we discussed earlier this week.

By using Hyperlinks, you can aid the ease of navigation in your Excel workbook by mimicking website design and, perhaps as importantly, you can look like the Excel Rock Star you really are!

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