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!

Naming Ranges – A Best Practice

Naming Ranges is one of those Best Practices that is so often neglected.  In spite of the fact that you can save you an enormous amount of time and make your formulas more intuitive to any user of your data, most users of Excel simply do not take the (very little) time to complete this important step.

Why Named Ranges

Using a Named Range in a formula does away with the need to make the range an absolute reference because it will always point to the correct range, regardless of where you copy the formula.

For example, let’s say you are managing a Sales Department, and you are using a dropdown box in a cell that refers to a list of your employees. For convenience and to assure consistent spelling, you have created the dropdown box using the List feature on Validation.

Rather than having to put up with the bother of adding/deleting employees from the reference list and then having to adjust the list range, you can create a Named Range “SalesEmployees”, (with sufficient room to add names in the future), and refer to “Employees” in the list box in the Validation dialogue box.  Wow, amazingly simple!

Combining Named Ranges with the Validation tool in Excel is a very powerful way of maintaining consistency in a spreadsheet, and making them more accessible for anyone to understand.

How to Create Named Ranges:

1.     The Quickest Way is to Use the Create from Selection Tool

a.      Select your Table or Database (including the headers…)

b.     Go the to the Defined Names group on the ribbon

c.      Choose Create from Selection

d.     Assure the Top Row is checked

e.      Click OK and Bamm! All of your ranges are named at once!

2.     Alternatively, You Can Use the Name Box

a.      First Select the Range of cells you wish to name

b.     Now click inside the “Name” box on your toolbar at the upper-left of your screen

c.      Type Your Chosen Name for the range

d.     Hit the Enter key and Presto! Your individual range is named.

 Named Ranges truly are a worthy Best Practice for you to adopt in 2016.  It will add efficiency and clarity to any applicable workbook.

 Happy New Year, All!

Most Popular Topic of 2015

Of the 30,000+ views this blog received in 2015, the most popular topic was the Insert Function Wizard.  A Wizard is, of course, a help feature in Excel that automates finding a function by asking the user a series of easy-to-answer questions.

Tried and True

This wizard has been around a long time, and has been vital tool for many neophyte and experienced Excel users alike.  The names the functions are not always intuitive, so it can quickly help you zero in one the function you need.

Easy to Find

Whether you are using Excel 2007, 2010, 2013, or 2016, you can find the Insert Function Wizard nestled neatly in the left corner on the Formulas ribbon.  Simply enter a brief description of what you want to do.  For instance, let’s say you want to calculate the Yield of security you are considering, and Bamm, a list is presented for you to choose from.

Easy to Use

Then is a simple matter to complete follow the step-by-step instructions of the utensil to complete you function/formula.

The simplicity and efficacy of this handy wizard is the driving reason it has remained one of Excel’s favorite Go-To Tools. 

Cheers!

Three Swift Actions

As I have told my students many times, “There is no glory in effort”.  If you can achieve the same results in a short time and with less effort, Bravo to you, well done!

Here are 3 Swift Shortcut Actions that can save you time, effort, and (perhaps most importantly) make you even more indispensable as the Go-To Professional for manipulating data in Excel:

 
#1 – Swift Navigation to Worksheets: Rather than scrolling a large workbook, access a quick and easy shortcut to the worksheet of your choice by right-clicking the lower-left corner of your workbook. A Menu will appear showing the Workbook Tab Names. You can then choose your location and navigate to it directly. You can go right to your worksheet and skip by the rest – Totally Cool!

#2 – Swift Formatting: Select the cell or range with the formatting you wish to copy, click the Paint Brush on your toolbar. Your cursor will then turn into a brush that you can use to “Paint” any other cell or range with the formatting you have picked up from the previous cell or range (you can think of your selection as the Paint Can).

#3 – Swift Chart:  Always a crowd-pleaser, you can simply select your table of data, and press F11 at the top of your keyboard. In the blink-of-an-eye, a full-featured chart will be inserted in your workbook. If you don’t like the default chart choice, just right-click it and choose Change Series Chart Type.

Three Swift Tricks – There’s No Glory in Effort!