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!

Cell Counting Techniques!

It is frequently handy to be able to count certain types of cells in a worksheet range. The following are four common Excel Functions that accomplish this quite efficiently:

1. COUNT: Simply counts the number of cells in a range that contain a number.
2. COUNTA: Counts the number of non-blank cells in a range (including those with text).
3. COUNTBLANK: Counts the number of empty cells.
4. COUNTIF: Flexible counting function (that we will explore with some examples)

So how can I use the COUNTIF function, you might be asking. For a bit of Inspiration, the following are some examples (in each case, the formula uses a range named Data):

Count the number of cells that contain the word "Charles" (not case sensitive):
=COUNTIF(Data,"Charles")

Count the number of cells that contain the words "Charles" and “Mary”:
=COUNTIF(Data,"Charles") + COUNTIF(Data,"Mary")

Count the number of cells containing any text (ignoring the numbers):
=COUNTIF(Data,"*")

Count the number of 4-letter words:
=COUNTIF(Data,"????")

Count the number of cells containing text that begins with the letter "T":
=COUNTIF(Data, “T*”)

Count the number of cells that contain a value greater than or equal to 15:
=COUNTIF(Data,">=15")

Count the number of cells that contain a value from 5 to 20:
=COUNTIF(Data,">=5")-COUNTIF(data,">20")

Being able to count cells on your terms is a great tool to have in your Excel Tool Kit. Especially the versatile COUNTIF! (I could say, “Make your spreadsheets Count”, but that would be way too corny…).

Cheers!

You are the Excel Guru!

Magic
Using the keyboard to quickly effect major changes in an Excel workbook can leave the impression of Magic to the onlookers (including your boss) of a Skilled Excel Practitioner. Here are a couple new favorites of mine that can help you increase your status as Excel Guru (or Excel Master or Excel Boss if you prefer).

Let’s say that you have 12 columns of data for which you want to calculate the Sums. Simple matter, of course, but if you want to do it in True Guru Style, simply select the entire block of data, and press Alt and = on your keyboard. Presto! The sums for all of your columns will appear like Magic in the row below.

More Magic
You can do more Magic by once again selecting the entire range of data and choosing Average, Count, Max, or Min from the AutoSum icon on your toolbar. The calculations will Instantly Appear for all columns of data!

As with nearly all things Excel, there are several different ways of achieving the same results. The Key is to do it with Style and Panache! After all, that is only fitting for a true Excel Guru…

I am Here for You

Have you ever been working on an Excel project and gotten Stuck on making it work like you want it to? Have you ever thought to yourself, “There must be an easier way?” Have you ever wished you had the services of an Excel Expert (or another expert if you are one yourself)?

We have all been there, and if you have been doing Excel for a long time, you probably been there more than once.

I was looking over past posts and questions from readers and, (feeling curiously generous with my time right now), I have decided to make a Special Offer to All of My Kindle Blog Subscribers: Now through the end of June, if you have an Excel project or problem on which you could use some help, please email me at the following address. I am devoting up to ½ Hour to each of my subscribers for any Excel help they need through June.

rdelamartre@earthlink.net

This is NOT, I assure you, some sleazy way to get you to buy anything, (services or whatever), but merely an altruistic offer to my Kindle Readers for whom I am truly grateful.

I am here for you, so if you think I might be able to help, please send me an email. I will be delighted to hear from you!

All the best,

~Bob