Formatting Your Charts

Aesthetics is often scorned by would-be Excel gurus. “The data is all that matters”, they may say. “How it looks doesn’t matter”, they scoff.

Well, they are right to a point, but they are missing the fact that if people find your work easy to understand, professional-looking, and agreeable to look at, they will take it more seriously and give it the attention it deserves. Charts are, after all, one of the most powerful tools in Excel. Chart Visually Convey your data in an easy-to- comprehended (done correctly, of course…) manner. 

Consequently, it is well worth any true guru’s time to assure the charts he or she has created conveys skill and insight. No one has any time to waste on frivolous enhancements, so here is a Quick, 2-Minute Drill to get real Value for your Chart-Improvement Efforts:

1.  Chart Type: Quickly scan the types of charts available and consider which suits your illustration be best (it may be one which your department has never used before).

2.  Gridlines often add Clutter your visual information. If that is the case, then Right-Click and Delete the Gridlines!

3.  Legends seldom add any additional information to a well-constructed chart. Assure that the chart is communicating well and then Right-click and Delete the Legend - Hasta la Vista, Baby!

4.  Formatting:  Add a little Zing to your charts by Formatting Your Plot Area. Right-Click and choose a Gradient Fill that adds a touch of Finesse while maintaining a professional look.

5.  Rounded Corners can add a bit of class to your chart and set it apart from the mundane. Go to Format Chart Area, select Border Styles, and put a check mark next to Rounded Corners. Totally Cool!

Taking an extra minute or two can Separate Your Work from the commonplace charts that we all see too often. A wee bit of effort can make all the difference!

A Primer on Prime

Prime Numbers, those curious digits that are divisible only by the number 1 and by themselves.  Mathematicians and other sundry geeks have long been fascinated with these oddities.  Just yesterday, NBC News ran a story that the current largest prime number ever discovered is more than 22,000,000 digits long!  (Ummm, and how many football fields long would that be?...).

In Excel, you can test to see if a number is Prime by creating a Custom Function called ISPRIME (pretty geeky!) with the following VBA code:

Function ISPRIME(num As Long) As Boolean

num_root = Sqr(num) + 1
If num < 2 Then
ISPRIME = False
ElseIf num = 2 Or num = 3 Then
ISPRIME = True
ElseIf num Mod 2 = 0 Or num Mod 3 = 0 Then
ISPRIME = False
Else
For i = 6 To num_root Step 6
If num Mod (i - 1) = 0 Or num Mod (i + 1) = 0 Then
ISPRIME = False
Exit Function
End If
Next
ISPRIME = True
End If
End Function

Taking this a step further, we can explore Mersenne Prime Numbers. A Mersenne number is in the form of Mn=(2^n)-1. Although Excel does not have sufficient power to do this in great depth, it is fun to see what can be done with our favorite spreadsheet software.

With the assumption that you have a Bit O’ Geek in you, here is what you can do. 

1. Create a simple spreadsheet with a similar format to the following starting in A1:

2. Starting in A3, insert consecutive numbers starting with 2 into Column A.
3. In B3, place the formula, =(2^A3)-1 and copy down with relative references
4. Lastly, use the custom ISPRIME function starting in C3 to determine if the numbers in Column B are prime 

Whether you find this Cool or not will be a factor of your individual Geekiness.  If you are like me (you poor soul…) you may well find this Totally Rad!

Little Known Tricks…

Some little-known Excel tricks (for keyboard or mouse) are Totally Cool, and if you have never run into the topic of this week’s blog, I am sure you will agree that it is a potential Crowd Pleaser!

Let’s say that you have 14 columns of data for which you want to calculate the Sums. “Piece of Cake”, you say!  Well, you’re right, it won’t take more than a couple of minutes to set up and accomplish.  But what if you want to do it with Style and Panache?  Here is what you do: Simply select the Entire Block of data, and press Alt + = on your keyboard. Ala Kazam! The sums for All of your columns will instantly appear like magic in the row below your data.

Some More Fun
You can do even 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, once again, Instantly Appear for all columns of data!

There are, of course, several different ways of achieving the same results in Excel. The Key is to do it with Speed and Flair! After all, you want to be one of the Cool Kids, right? Give these tricks a try, and listen to everyone go Oooh and Aaaah…

Navigating in the New Year!

It is often a wise strategy to occasionally review and brush up on some basics. What better time to do this than the beginning of a new year!

Any Excel user spends a great deal of his or her time Navigating their Excel workbooks. This can potentially be a bit of drudgery, especially if you are working with very large worksheets. If you are working with a worksheet with 40 records, No Big Deal - If you are working with one with 40,000 records, however, it is a significant consideration!

A few Cool Keyboard Tricks can save your day, and maybe even let you get out of the office early. Here are few Simple Actions you can make without ever touching a mouse:

1. Control / Down Arrow: Takes you to last cell in column with data
2. Control / Right Arrow: Takes you to last cell in row with data
3. Control / End: Takes you to the last row, column and cell
4. Control / Home: Returns to cell A1

Another way to navigate, of course, is to use some clever moves with your mouse. To go to the end of your data, (whether in a column or row), carefully place your pointer on the adjacent Border of the Cell in your range and double-click. For instance, if you wish to navigate to the last cell in a column of data that starts with cell E1, you can select E1 and double-click on the bottom border of the cell.  Bamm, away you go!

Another, occasionally useful way to navigate to a specific location is to use the Name Box in the upper-left corner of your worksheet. This is beneficial when you know the exact address of some remote cell. Simply enter the address (e.g. DM5200) in the Name Box and Presto, you are taken directly to that location!

Using these elementary (and sometimes forgotten) methods can take the chore out of navigating your worksheets. Try them out for the New Year!