Clean Up Your Charts

Charts are one of the most powerful tools in Excel, as they Visually Convey your data in a very quickly comprehended (if done correctly) manner.  There are several easy ways to Clean Up Your Charts, however, and your work will look much more professional if you take just 3-4 minutes to tweak your work.

Here are 4 Quick, Easy Tips to Polish Your Charts:

1.      A Legend very seldom adds any additional information to a well-constructed chart.  Right-click and Delete the Legend!

2.      Gridlines often Add Clutter more than information.  If that is so, right-click and Delete the Gridlines!

3.      Rounded Corners can add a Touch of Style to your chart.  Go to Format Chart Area, select Border Styles, and put a checkmark next to Rounded Corners.

4.      Add a little Pizazz to your charts by Formatting Your Plot Area.  A quick right-click and choice of Gradient Fill will add finesse while maintaining a professional look.

This really takes very little time, and will Separate Your Work from the mundane charts that we are all much too familiar with.  So Clean Up Your Charts - It is Time Well Spent!

Treasure on the Status Bar

With all that current versions of Excel have to offer, it is easy to overlook the Treasure Chest of information available right at your footstep on the Status Bar.  If you are an Excel 2007 or Excel 2010 user, you probably are aware of real-time display of common data such as Average, Count, and Sum of any cells you have selected.

What you may not be aware of, however, is that an entire trove of Excel Goodies is just a click away.  Simply Right-Click the Status Bar and Bam!  You will see a List of 22 pieces of Information that can be instantly added or controlled in this area.

For instance, you can easily Add or Delete the Number Count, Maximum or Minimum, Average, and so forth.  But that’s not All!  You can also Control such wondrous goodies as Macro Recording, Fixed Decimal, Permissions, and much more!

I don’t know about you, but I like to have things At My Fingertips for easy access and control.  The Status Bar is a very handy place to add some more Control in your life.  So give it a try!  Just right-click and Bam!  Check out all of the Goodies!

Date Night in Excel

It is September 1^st and for many of us this Date signals the coming of fall.  (It’s 85 and sunny here in Southern California, but my roots are in Minnesota, so I remember…).

Speaking of Dates, it is good to know how Excel treats this information, as it is not entirely intuitive at times.

Dates are treated as Sequential Numbers in Excel.  For instance, September 1, 2011 is represented (behind the scenes) as 40787.  September 2, 2011 is 40788, and so forth.  Therefore, if you subtract Today’s Date from September 23, 2011, (the official first day of fall this year), you get 40809 – 40787 = 22.  22 days until the true first day of autumn!

If you want to use a handy function for Today’s Date, you can use the following to get the above results: 

=September 23, 2011 - TODAY()   (Note: You may have to format the results as “General”)

If you want to get a bit Fancier, (but still reasonably basic), you  can try out a formula that determines if the Date you have chosen is Today, the Future, or the Past, you can use the following:

=IF(Date_Chosen = TODAY(), “Today”, IF(Date_Chosen < TODAY(), “Past”, “Future”)

Dates in Excel; They may not be as Fun as Date Night, but they are interesting and very useful when you know how they work.  We will be further exploring How Excel Handles Dates in the future.  In the meantime, do a Little Exploring, and don’t forget to format your dates as “General” to see what is going on behind the scenes.

Cheers!

Ditch the Mouse

As most of us know, relying too heavily on your computer mouse can lead to Unnecessary Stress on your hand and wrist. It is also slower than using the keyboard (and not nearly as Cool…).


One very interesting way to Access More Keyboard Shortcuts is to do the following:

1. Select any empty cell on worksheet
2. Place you mouse pointer on any empty portion of your Toolbar or Ribbon
3. Press the Forward Slash key

Bam! Letters and Numbers will appear (it’s almost like Magic!) on the Toolbar and Ribbon, giving you complete Keyboard Control to access the functions and subsequent menu items. Then you have the Freedom to use your keyboard, (Ah, Freedom!).

As with anything we humans do, the old adage, “Use it or Lose it” is entirely true in this instance, or anytime we learn a new Excel Trick. If you like this nifty shortcut, Use it a few times, and it will soon become Second Nature.

Shortcuts – Gotta Love em!

Alternatives to Excel

I can almost hear what you are thinking: “Alternatives to Excel?? Sacrilege! Blasphemy! What would Bill Gates say?!?”

Yeah, yeah, yeah… I know, Microsoft has without much question, the best, deepest, and most robust spreadsheet application in computing today, but there are times when alternatives may be a viable option.

First of all, many companies, as well as individuals, are facing necessary belt-tightening during our recession and so-called post recessionary times. That being the case, FREE software applications can have a powerful allure! There are also many users who Simply Do Not Need (and would probably never miss) the many bells and whistles that are included in our favorite app.

So what are the best alternatives to Excel? The following are some likely candidates:

• Calc – This spreadsheet application is found in Open Office, and is totally Free. Excel workbooks can be imported into Calc, but this application may have trouble interpreting some of the more sophisticated Excel worksheets. It is, however, certainly a good alternative for those who want a quite powerful spreadsheet application and are on a budget.

• Numbers – This is, of course, the Apple spreadsheet app. Since I often rely on my two iPads and my iPhone, I have become quite familiar with this alternative. Numbers spreadsheets can be exported to Excel, (unlike many of the free apps), and is a worthy rival to Excel. True to Apple’s penchant for making things “Pretty”, Numbers is outstanding for making Engaging Charts. It is inexpensive, and worth a look if you use any Apple products.

• Google Docs – Where would we be if we didn’t include something from Google. Besides getting the job done and being Free, spreadsheets are stored in, and can be retrieved from, the Internet. Cloud Computing may strike many as being the Latest Fad in computing, but this does carry many advantages from Accessibility and Sharing standpoints.

• Lotus Symphony – This IBM product includes an intuitive and free spreadsheet application that offers a surprising amount of Flexibility. Your work can be saved in many formats, including OpenDocument, Microsoft and Apple.

Alternatives; No, I am not going to be switching from Excel to any other application for the majority of my spreadsheet work, but it is nice to know they are there.

Error Handling!

Here is the scene: You are constructing a report that takes data from a table via a VLOOKUP function. Being the exacting person you are, you want the report to have a clean and professional look, without any extraneous and distracting Error Messages.

So, for example, what do you do when you get occasional (and annoying) #N/A results? The answer, of course, is the use of an Error-Handling function. There are several functions that can be used to handle errors, and three of the most common are as follows:

• ISNA – Checks whether a value is #N/A, and returns TRUE or FALSE
• ISERROR – Checks whether a value is any error, and returns TRUE or FALSE
• ISERR – Checks whether a value is an error, excluding #N/A and returns TRUE or FALSE

So back to our example where you are getting some #N/A results while using a VLOOKUP function. The syntax for the VLOOKUP function is as follows,

VLOOKUP( value, table_array, index_number, exact_match ), where the:

1. First argument is the Key Field in the first column of the table.
2. Second argument is the Table.
3. Third argument is Row from which you wish to retrieve the result.
4. The fourth argument refers to an Exact Match (expressed as “False”). Note: If you wish to retrieve and approximate value, use “True” in the fourth argument.

If No Exact Match is found for your first argument, then the VLOOKUP function will return #N/A

If you wish to concentrate only on #N/A errors, and return a blank cell when they occur, you can insert the error-handling function, and use the following formula:

IF(ISNA(VLOOKUP( value, table_array, index_number, exact_match )), “”,VLOOKUP( value, table_array, index_number, exact_match ))

If, on the other hand, you wish to Do Away with the potential for Any Type Of Error message in your result, (for instance, if the index_number is greater than the number of columns in table_array, the VLOOKUP function will return #REF!), you can use the ISERROR function as follows:

IF(ISERROR(VLOOKUP( value, table_array, index_number, exact_match )), “”,VLOOKUP( value, table_array, index_number, exact_match ))

The Error-Handling functions can be used in a variety of cases other than VLOOKUP, of course. In fact, if you get used to using these easily to master tools, you may Never Again produce a report with ugly error values in it! And that would be Very Cool!

Contest Winners and Moving Your Data!

A student of mine recently was having trouble Copying a master worksheet to another workbook. She had unsuccessfully tried to copy and paste the worksheet format and formulas into a new workbook, and was wondering if there was an easy way to do this.

As with most things Excel, there are several ways to do this, but one of the easiest is to use the built-in Move or Copy tool. Since I have not addressed this for over two years, I felt it was time for an update.

It is, of course, really quite simple (but then, anything is when you know how…). If you wish to Move or Copy a worksheet into another workbook, you can do the following:

   1.   Right-click the tab of the source worksheet you wish to transport, then click Move or Copy in the dialog box
   2.   In the Move or Copy dialog box, you can do the following:
         • To Move the worksheet to a spot within its own workbook, select a sheet in the Before sheet  box and click OK
         • To move the worksheet to another open workbook, select the workbook in the To book list and then click OK
         • To keep a Copy of the worksheet in your source workbook (rather than deleting it and moving it), select the Create a copy box in the lower-left corner, and select a location as per the above

This is a comparatively simple tool in Excel, but a highly useful one, nonetheless!

Contest Winners!

You may recall that on July 14th, the Excel Enthusiasts blog ran a contest wherein you were asked to send an email with your Favorite or Most Used Function in Excel. The Three Winners were, (Drum roll, please…):

1.   David Brooks of Olympia, Washington
2.   Kevin McLogan of Royal Oak, Michigan
3.   Gerry Simon of Racine, Wisconsin

Congratulations to the Winners, and thanks so much for taking part in the contest!

See you all next week. Meanwhile, keep on Excelling!