Shooting Blanks!

Well, maybe this edition of this blog should be entitled, “Shooting Down Blanks!”  The truth is that it is Not Always Easy to know whether a cell or cells in Excel are Truly Blank!

This is due to the fact there are ways of Hiding Data through the use of identically-colored fonts, empty-string results of a formula, or masking the data with the use of Custom Formatting (three semicolons: ;;; ).  If you don’t know if a cell is Truly Blank, it can cause Mayhem with your calculations.

To detect this Invisible Data, there are at least a couple of techniques.  Assuming your cell in question is A1, you can:

1.      Simply insert this Function in an adjacent cell:  =ISBLANK(A1) 

a.       If the cell is Blank, it will return True; if it is Not Blank, it will return False

2.      Also use an IF Statement as follows:  =IF(A1<>"","Not Blank", "Blank")

a.       This IF Statement obviously returns Blank or Not Blank

By determining if your cells are Truly Blank, you can Avoid Problems on your worksheets, and I don’t know about you, but I think Avoiding Problems, Rocks!

Data Entry Form for Adding Records

If you or someone you work with frequently adds records to databases or tables, you should definitely give the Data Form Tool a try.  For anyone who routinely enters data, it can make your life a bit easier. 

How to Add the Form to Your Workbook for Excel 2007 & 2010 Users:

1.      Click the down-arrow of the Quick Access Toolbar in the upper-left corner of your workbook

2.      Choose Customize Quick Access Toolbar

3.      Select All Commands and choose Form…

4.      Click the Add>> button and click OK

Now go to your database or table and select the Form from your Quick Access Toolbar.  Suddenly…  Bam!  Up pops a New Data Entry Form for updating your database!

Just tab through the Form as you enter your data, and watch your table or database update as you do.  Wow, what a Great Little Tool!  Give it a try!

Enhance Your Charts

Aesthetics seems to be a dirty word in some tech circles. Upon closer examination, however, it is apparent that making things “Look Good” definitely has its place in Excel as well as technology in general.  Why is that important?  Well, just take a look at products by Apple.  Sure, they do a good job, but they also look good, and are therefore add sensory appeal for the user.

When it comes to Charts, Excel 2007 (and Excel 2010) gave you much more control over how your work Looks!

Excel 2007 ushered in the Ribbon, which is clearly a great advantage to anyone making Charts.  Double-clicking on your chart will pull up the Design Ribbon, giving you several ways to improve the appearance of visually-displayed data.  Among the possibilities that are readily are the following Options for you on this ribbon:

1.      Change the Chart Type (easy access to all major types)

2.      Chart Layouts (5 different ways to display titles, labels, legends, etc)

3.      Chart Styles (a sizeable variety of color and 3-D options)

You can also go to the Format tab and find a myriad of ways to further Customize the look of your chart with:

1.      Bevels

2.      Soft Edges

3.      Shadows

4.      Much More

So, once again, why is this important?  Please consider this:  If your audience is going to Understand the Message your chart is conveying, they are going to need to Like it First!  As in all good communication:

1.      Engage Your Audience

2.      Convey Your Message

Cheers!

Duplicate Data

Duplicate Data can be an annoying issue when working in Excel.  Although it is desirable to use the tools of Excel to simply delete all duplicate data, there are times when it is more advisable to clearly identify these values prior to eliminating them.

For instance, let's say you are working with blended human resources data that lists employees with their home addresses.  If the information in the blended data includes duplicates of employees who have had updates , and shows different addresses for these duplicates, it would be good to have a simple way of doing identifying them before taking action.

Solution?  Conditional Formatting to the Rescue! 

Here is How to do this:

1.  Select the range (e.g. A1:A65).

2.  Choose Format / Conditional Formatting to display the CF dialog box

3.  Select Formula Is from the first drop-down list box, and enter =COUNTIF($A$1:$A$65,A1)>1 in the second box

4.  Click the Format button to bring up the Format Cells dialog box.

5.  Select the Patterns tab, and choose a background color

6.  Click OK twice to return to your worksheet

Alacazam!  If the range contains any duplicate entries, they will be highlighted with the background color you chose previously, providing you with Easy Identification of any Duplicate Data. 

How Cool is that!

So, what are you waiting for?  Give it a try!

Cheers!

Interactive Chart Titles

It has been two years since we took a look at one of the advanced (but very simple) ways to make the charts in your report Stand Out from the mundane masses.

Interactive (or dynamic) Chart Titles can be easily added to your charts to reflect the data being displayed.  For instance, if you have created a table of data that changes in accordance with the Employee Name shown in a Drop-Down Box (which is effortlessly generated by using Validation / List), you can Link the Chart Title to reflect the name chosen in the drop-down.

Here is How You Do This:

First of all, if your chart does not have a Title, do the following:

1.      Click anywhere in the chart

2.      On the Design tab, click a layout that contains a title from the Chart Layouts group

Now, on to the Adding the Interactive Title:

1.      Select the Chart Title

2.      Go to the Formula Bar and type an Equals Sign: “ = ”

3.      Then Select the Drop-Down Box Cell to which you want to link

4.      Note: The final cell reference formula should look something like: “=Sheet1!$C$3”

How Cool is That!  Now every time you change the Name or Value in the Drop-Down, the Chart Automatically Updates its Title! 

As they used to say in the 70’s, Far Out, Man!

AutoCorrect: Function and Fun!

The AutoCorrect tool in Excel can make your life easier and more efficient.  It can even be a source of some rascally Fun!

First of all, let’s talk about how it can make your Excel life.  Let’s suppose you have a Long Word String that you often enter into Excel.  Let’s suppose you work for the National Broadcasting System, and the legal department wants the name of your company Spelled Out each time it is entered into a worksheet.  What a drag, you say! 

I quite agree!  That is an instance where AutoCorrect can make things much easier for you. 

Here is What You Do:

1.      Click on the round Office Button in the upper-left corner

2.      Click on the Excel Options button at the bottom of the dropdown

3.      Choose Proofing and click on the AutoCorrect Options button

4.      In the Replace box, type NBC

5.      In the With box, type National Broadcasting System

6.      Click Add and then OK

Voila!  Whenever you type NBC in a worksheet, it will now automatically change that to National Broadcasting System!  How Cool is That!

Now for a little Fun:  While a friend is away from his computer, (let’s suppose his name is Dave…), go into AutoCorrect and enter Dave in the Replace box and The Dork in the With box.

Then have your friend type his name into Excel and prove to him that he is, indeed, a Dork!  Okay, okay, maybe we’re not adolescents anymore, but a little Fun once in a while is Good for Everyone!

 Cheers!

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!