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!

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!