Camera Tool – Just Say Cheese!

A picture may be “worth as thousand words”, but sometimes a graphical representation of a chart can use a little support from the source table with the numbers. With the Camera Tool, you can have the Best of Both Worlds.

Though initially hidden on Excel, the Camera Tool can be highly useful when you are presenting a chart and would like to include a resizable image of the source data included within your exhibit.

To find it in Excel 2007, simply right-click your toolbar and choose “Commands Not in the Ribbon”. Then scroll down to Camera and click Add.

To find it in Excel 2003, right-click the toolbar and choose "Customize" / "Commands" and choose "Tools". Then scroll down to the Camera and drag it onto your toolbar.

After that, it is easy (and it hasn’t even been hard…):

1) Select the data table of which you wish to "take a picture" and Click the Camera Icon on your toolbar
2) Then, go to the area on the sheet on which you want the data to appear, and Left-Click
3) Bamm! You have an image of the table (Resize and Reposition if desired).

Bonus! This “image” of your source table is Linked to the original table. Any change you make in the original table will be reflected in the new table you have placed on your chart.

Now, I ask you, is that cool or what! Cheers.

Working with the DATE Function

Working with dates in Microsoft Excel can be a puzzling and, at times, aggravating experience. Adding days, months, or years to a date can be at first a mystifying task.

Happily there are some excellent time functions built into Excel, and one of the most versatile is the (cleverly named) “DATE” function.

The syntax of the DATE function is =DATE(Year, Month, Day), therefore if you enter as follows, =DATE(2010, 6, 17), it will return today’s date of June 17, 2010.

What is particularly Cool about the DATE function is that it very flexibly accepts inputs from cells, functions, and calculations. For example, =DATE(2010+1, 6, 17) returns June 17, 2011. =DATE(2010, 6, 17+7) returns June 24, 2010. You get the picture.

The DATE function can handle more complex situations as well. For instance, =DATE(2010, 6+9, 17) returns March 17, 2011 (Hey, isn’t that St. Patrick’s Day?...).

So let’s say that you have an interactive What If report that has cell B1 with a continuously updated current date, (you can use the =TODAY() function for that), and cell C1 displaying the date which is a variable of numbers of years in the future based on the value you place in cell A1. Your formula in C1 would look like the following:

=DATE(YEAR(B1) + A1, MONTH(B1), DAY(B1))

If today is June 17, 2010 and you have the number 4 in cell A1, the above formula would return June 17, 2014.

Take a few minutes and experiment with the DATE function. It is one “date” that will never disappoint.

First Names and Last Names

As Excel practitioners, we are frequently presented with data that is Not in an ideal format. For instance, let’s say you are presented with list of employee names showing the Entire first and last name in each cell in a column.

As any good database manager knows, it is best to Break Down or Parse Your Information into the smallest discreet portions reasonable. To facilitate maximum information retrieval, you would naturally prefer to have the first names in one column and the last names in another column. If you have hundreds of employees in your company this would, obviously, be Pure Drudgery to convert manually.

Extracting the first names and last names into their own separate fields (columns) with Convenient Excel Formulas is a Breeze. Here is how you do it.

The Excel functions that we will be using in the formulas are:
LEN - Returns the number of characters in a text string
FIND – Returns the starting position of one text string within another text string
LEFT – Returns the specified number of characters from the start of a text string
RIGHT - Returns the specified number of characters from the end of a text string

Okay, here we go! To extract just the First Name, use the following formula:

=LEFT(A1, FIND(" ", A1, 1)-1)

To extract just the Last Name:

=RIGHT(A1, LEN(A1)-FIND(" ", A1))

These relatively simple formulas can really save your day sometime. Go ahead, give them a try!

Save Your Wrist (And Look Cool Doing It…)

Yesterday while breezing through an Excel workbook and entering some repetitive data, I realized I have never mentioned a couple of my favorite Gee Whiz tricks. They not only have Wow Factor, they are also very practical and Ergonomic!

Any time you can use your keyboard and avoid your mouse, you are probably doing your wrist a favor. There is typically much less stress using the keyboard.

Elegant Navigation

Rather than using a mouse to navigate to another worksheet within an Excel workbook, you can simply use CTRL+PAGE DOWN to move one worksheet to the right or CTRL+PAGE UP to move one worksheet to the left. Not only is it efficient, but you will Look Cool doing it!

Enter in Bulk

Let’s say you want to enter the same numbers or same text in a block of cells. There are a number of inelegant ways of doing this, but if you want to save your wrist and be Totally Cool in the process, you can do the following:

1. Select the entire range of cells you want to contain the same content

2. Type your words or values (be sure to do this while having the entire block of cells selected)

3. Press CTRL+ENTER

Presto! You are a Genius! Go ahead, give it a try!