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!