Picture Charts!

There is no doubt that Excel provides a considerable array of Chart Types to choose from. There are times, however, when you may want to Add a Bit of Pizazz to your charts. One of the coolest ways to do this is by Replacing the Series Element (Columns or Bars work the best) with a Graphic.

You can easily Add Impact to your charts by doing the following:

1. Copy a Graphic (simple images work the best) to your clipboard
2. Create your Chart using columns or bars
3. Select the Chart Column or Bar Series
4. Go to your Home Tab and Paste the image
5. Bonus: Format the Data Series by going to Series Options and choosing 0% Gap Width

Since I live in southern California, I chose a Surfboard Graphic for my Surfing Days Chart above. To demonstrate the difference this can make, I did a Side-by-Side illustration below. I chose a Bag of Money to replace the Boring column to depict the sales by month.

Picture Charts. Another way to Add Interest and Impact to your Excel worksheets.

Surf’s Up!

Excel on the Cloud

As any technophile knows, The Cloud is the Major Buzz these days. It seems that everyone is scrambling to say, "Me Too" in their quest to capture your traffic and business via cloud-based apps or, as with Microsoft's Office 2013, span both traditional approaches and the cutting edge.

My current favorite Cloud Solution is indeed supplied by the venerable Microsoft. By placing your Excel workbooks on SkyDrive, they can be shared (at your discretion) with the world. All you need to do is go to your SkyDrive, right-click the document, and then click Share. Type the email address of the person you want to share the workbook with, and Bamm! Done!

But Wait! What makes it Really Cool in this instance is that the other parties with whom you are sharing Do Not even have to have Excel on their computer! That Totally Rocks!

Other favorites include the iPad Numbers app. Workbooks created or edited in Numbers can be converted to the Excel format, and shared on iTunes, DropBox, and other cloud-based facilities.

Google Docs is also hugely adaptive and beneficial for anyone (or any organization) who is on a budget, and still wants reasonably high functionality and ease of collaboration.

So, what about me, am I personally Truly embracing the mobile world and the cloud? Well, as an evidenced by this week's blog which I have written entirely on an iPad while awaiting an appointment, yeah, I guess you could say that I am!

Happy Thanksgiving, All!

What’s the Dif, Man?

Back in July of 2010, we looked at the obscure, and curiously undocumented function, DATEDIF. Microsoft, in all of its wisdom, (small amount of gentle sarcasm here), has chosen not to include documented information on this Essential Function in Excel. Over the past 5 years of this blog, this topic has been one of the readers’ favorite.

As any longtime user knows, the way Excel handles dates can be a bit puzzling at times. Finding the Difference between two Dates, for instance, is not readily intuitive. Here is where DATEDIF shines!

The Syntax of the Function is as Follows:

=DateDif(First Date, Second Date, Time Interval)

Where the Time Interval is expressed as follows:

d (Days) = Number of days between the dates
m (Months) = Complete calendar months between the dates
y (Years) = Complete calendar years between the dates

Important Note #1 (you don’t need this craziness): The Second Date must be greater than the First Date, or you will get a Number Error.

A novel application of this function is to nest the NOW() function into it and calculate a Person’s Age. The NOW() function returns the Current Date and Time, and when used with DATEDIF, it can produce an Excel calculator that many find amusing. (Note: the “BirthDate” can refer to an easily changed Cell Value):

=DateDif(BirthDate, NOW(), “y”)

Important Note #2: If you put the Time Interval in the function directly, be sure to put “quotation marks” around it (e.g. “m”). If you put it into the formula via a cell reference, do not use the quotation marks (e.g. the cell should contain m, not “m”).

You may well find a great many ways to use the DATEDIF function, which may lead you to wonder, Why is this Terrific Tool not documented? Ah, well, who am I to question the great Microsoft…

Go To Special

I have a question for you: Do you ever use Go To Special?

If you answered, “No” to the previous question, you are undoubtedly in the vast majority of other Excel users. You may find this to be a useful tool, however.

I’m quite sure that some time in your Excel career, you have found yourself wondering how you can locate certain types of cells within your worksheets. For example, you may have wanted to find the cells in your worksheet which contain Formulas, or perhaps those which have Conditional Formats.

You may be pleased to know that Excel provides a terrific Tool to help you locate these and many other Special Types of cells. The Go To Special dialog box can provide you with options you may not have realized that you have in Excel.

You can find this nifty tool on the far right of your Home Ribbon. Simply click on Find and Select and choose Go To Special. Once there you can choose Comments, Constants, Formulas, Blanks, Current Region, as well as many other choices.

Now that your cells have been selected, you can perform whatever operation you may see fit. This simple tool can save you a lot of time searching through your worksheets, and can inevitably Help You Prevent Errors.

Go To Special; A useful tool to have in your Excel arsenal!

Excel 2013 Update!

Excel 2013 is part of the new Microsoft Office suite, of course, and many of us are eager to see the final version released. I have been using the beta version of the suite for several months, and you can count me in as one of the early adopters who is excited (and maybe a bit impatient) to get my hands on the Final Release.

Two Big Questions are Out There:

1. When will it be released?
2. How much will it cost?

As concerns the First Question, we do not as yet know with any absolute precision, but industry insiders now say it looks like it will be sometime in January. Although this may seem like a long time to wait for the update on our favorite spreadsheet software, kudos to Microsoft may be in order for waiting until they are more sure the bugs are out.

As concerns the Second Question, that information is here! We now know there will be Two Basic Approaches to acquiring the 2013 version of MS Office (and, of course Excel):

1. The Traditional Method of purchasing software: Locally-installed and with the license valid for just a single PC or Mac. The pricing will be similar to what is has been in the past: Office 2013 Home & Student will cost $139.99, Office 2013 Home & Business is $219.99, and Office 2013 Professional will retail for $399.99.

2. A newer Subscription Method: Alternatively, consumers can opt for the online subscription-based offerings. Office 365 Home Premium is $99.99 per year (or $8.33 per month), and Office 365 Small Business Premium is $149.99 per year per user (or $12.50 per month per user).

If you are like me, you may be adverse to paying for a Subscription, rather than having your own copy on your personal hard drive, but consider this: With Home Premium Subscription, the you also get Outlook, Access, and Publisher (not included with Office 2013 Home and Student edition), an additional 20GB of online file storage with SkyDrive, and 60 minutes per month of Skype calls. Plus, it can be Shared across Five different users and/or devices!

Whichever way you choose to acquire your Excel 2013 product, I am confident that you will find things to like in this latest rendition. If you haven’t yet experienced the beta, you can go to Microsoft.com to download a Free Trial version today.

Happy Halloween and Excelling!

Self-Expanding Charts

This Trick is a real Halloween Treat!

If you have ever created a Chart that needed Updating on a regular basis, you know what a bore it can be to do the frequent revisions. That sort of work may keep some people employed, but there are Better Ways to handle this task.

Building a Self-Expanding Chart is remarkably simple, and can Save You A Good Deal Of Time and boredom (and, after all, who needs more boredom!)

To create your Self-Expanding Chart in Excel 2007 or versions thereafter, here is what you do:

1. Create your Chart as usual ( you can use the sample data below)
2. Select any cell that contains the data that is used by the chart
3. Choose Insert / Tables group / Table to convert the range to a table

Amazingly that’s all there is to it! Excel will recognize the data as a Table and any data that is added to it is Automatically Updated in the Chart.

Give this Charting Trick a try, and find out why it is such a Treat!

Custom Date Formats

Although they are a major part of the grist which drives the Excel, handling Dates can be a bit tricky and are often misunderstood. The most common Date Formats are as follows:

•   m - Month as a number without leading zeros (1-10)
•   mm - Month as a number with leading zeros (01-10)
•   mmm - Month as an abbreviation (Jan - Dec)
•   mmmm - Unabbreviated Month (January - December)
•   d - Day without leading zeros (1-10)
•   dd - Day with leading zeros (01-10)
•   ddd - Week day as an abbreviation (Sun - Sat)
•   dddd - Unabbreviated week day (Sunday - Saturday)
•    yy - Year as a two-digit number (for example, 12)
•   yyyy - Year as a four-digit number (for example, 2012)

But suppose you want to create a Date Format that is Not included in the built-in list in Excel? No worries, here is what you do. Let’s assume that you wish to have a date format that has the elements shown as follows:

1.   4-Digit Year
2.   2-Digit Day
3.   Unabbreviated Month

The Steps are as Follows:

1.  Right-click on the cell containing the date
2.  Select Format Cells
3.   In the Category list on the Number tab, select Date
4.   In the Type list to the right select the format closest to what you want
5.   In the Category list select Custom, and the format you selected will show in the Edit Box above
6.   Modify the format in the Edit Box and see the preview in the Sample
7.  When you are happy with your results click OK
8.  The new format will be applied to the cell and will also be Added to the List of custom formats

For our Example, the results will be a date format in the syntax: yyyy/dd/mmmm.

Controlling your Date Formats, even when they are Custom, is really quite easy in Excel. The payoff is Improved Communication. And that is, of course, The Bottom Line…