Excel Quick Tricks!

Season Greetings All! I hope everyone is having a safe and enjoyable holiday season.

I have always appreciated brief “Aha!” tricks that can reveal a Quick Easy Way to accomplish a task in Excel. Here are four little known or used tricks, plus the results of a survey:

1. Select Noncontiguous Cells
Selecting noncontiguous cells in a worksheet is simple by holding down the CTRL key and click on the cells you want.

2. Format Individual Characters
Click the F2 key and use your cursor to highlight the character to want to format. Right-click and the Format drop down menu is at your command!

3. Align Text Your Way!
Once again, Right-Click and use access the Alignment tab on Format Cells. It’s a Snap Aligning your text in any orientation you want.

4. Save Your Chart as a Picture
Do you want to Use Your Chart in Another App or location? Copy / Paste as a Picture, and feel confident it will stay true to the original (and take up less space…).

Extra: Survey Results
I set up a survey of More than 3,000 business folks recently. Over 82% of the responders voted that being efficient in Excel can help you in your job “A Great Deal” or “To a Large Extent”.

That survey philosophy can make you glad you are an Excel Enthusiast!

Keeping Things Proper

Having a list of names imported from a source other than Excel can result in all upper-case, all lower-case, or even a mixture of both! Whereas this may not affect recordkeeping or data calculations, it certainly doesn’t look very professional.

So what do you do when you download 5,000 names that are in something other than proper case? (You sure don’t want to change everything manually…). The answer is, of course, use the PROPER function!

It is so simple, you will laugh:

Let’s say that you list of names runs from A2 to A5002. In cell B2, insert the following formula:

     =PROPER(A2)

Then simply copy this formula down to the bottom of your data with a quick double-click. Voila! Proper Names!

One final note of caution, if your database list contains names such as McCarthy or MacNamara in it, you will probably need to change those manually. And if you have any really Odd Names like DeLaMartre, then you will for sure need to make some adjustments by hand…

Cheers for your holidays!

Bob DeLaMartre

A Micro-Graph for All Versions

This is so cool. Here is a way to make a simple Micro-Graph that resides in your table and Works in Any Version of Excel!

This is really easy. Let’s say you have your Products (or sales reps) in Column A as illustrated above, and in Column B you have the Units Sold. Here is the formula you should put in Cell C2:

  = REPT( “l” , B2/10) and then copy it to C7

For Each Approximate Count of Ten, the formula puts a Hash Mark, (using an Arial font works well), in Column C. The result is a simple, easily read, Micro-Chart!

Try it out in the office, and wait for the Kudos to Roll In!

Double-Click Tricks!

This is one of my New Favorite Posts for this blog. If you would like to have some more Mouse Tricks Up Your Sleeve, (not that you would actually like to have mice in your clothing…), then I think you will like some or all of these. Unless noted, these Double-Click Tricks work for Excel 2007 and 2010. We will start off with an old favorite…

1. Perfectly Adjust Column Widths – Just select Multiple Columns and Double-Click on the separators; Works for adjusting row heights too.

2. Insert a Split - Double-Click just above scroll-bar to include a horizontal split; Works for a vertical split too, by clicking on the little bar shape next to the right of horizontal scroll-bar.

3. Close Excel 2007 (only) – Simply Double-Click the Office Button.

4. Collapse Ribbon to Get More Space – I like this one. Just Double-Click on ribbon Menu Names.

5. Lock Format Painter – Save a Ton of Time by Double-Clicking on the Format Painter icon, making it Reusable. (So Cool!...)

6. Jump to Last Row / Column in Table – Another old favorite: Just select a cell, and Double-Click on the cell-border in the direction you want to go. Bamm! You’re there!

Double-Click Tricks Rock! Give Them a Try!

PowerPivot

This week’s topic is for you Power Users out there. It is also intended to perhaps Inspire the rest of us mere mortals, and to bring your attention to a Free (“Free” is always cool) Powerful Tool.

There are times when being able to combine and analyze data from a number of sources would be, (as I like to quote Martha Stewart), "A Good Thing." Let’s say that you have several SQL databases housed on SharePoint and other sources, and you want to load the data and create interactive queries from within an Excel workbook. Scary Business? Well, a bit, but nothing beyond the capabilities of an Excel Enthusiast!

PowerPivot, available on Excel 2010, truly Empowers you to capture the data you need, gain greater insight into the meaning of the data, and do so without overtaxing your system’s resources. With PowerPivot, you can:

• Load very large databases from Nearly Any Source

• Efficiently process huge amounts of data in mere seconds

• Work in a Disconnected Mode once your data is imported

• Leverage your Familiarity with Excel to work with the data

• Use the new PowerPivot Analytic Capabilities

• Utilize the Power of contemporary multi-core processors

PowerPivot: Not necessarily for everyone, but if you work for a large company and need a new way to Slice and Dice your data, download it for Free from www.PowerPivot.com and give it a try!

Next week: Back to the mainstream with a cool, easy technique that will give you options you never had.

Is it Time to Upgrade?

So you have Excel 2007, (it seems fewer companies and individuals are upgrading as frequently these days…), and you are wondering if it is worth it to finally get Excel 2010.

Although I have had 2010 since it came out, the need to upgrade is not, in the opinion of some, to be overwhelmingly compelling. That being said, there are some Worthy New Features. The following are those I consider to be the most significant:

Function Enhancements:
The Accuracy of a number of the financial and statistical functions have been improved

Sparkline Charts:
Nifty little tool enabling you to create Small In-Cell Charts

Slicers:
New way to Filter and Display data in Pivot Tables

Image Editing Enhancements:
Since I have an appreciation of making my graphics look good in any Microsoft product, this is one of my favorites. You have much More Control over Graphic Images, including the ability to remove the background of an image.

New Version of the Solver Add-In:
Enables solving some Complex Problems (Cool!)

There are a few others that you may find helpful, but for my book, those listed are the Most Compelling. In any regard, I think it is wise to keep in pace with current upgrades. Otherwise the day will come when you may find yourself Sadly Out-Of-Touch (Never a good thing…).

Happy Thanksgiving All! ~Bob

The Invaluable DATEVALUE Function

A Date is a Date is a Date (well, that certainly wasn’t true back in my college days). Nor is it true in Excel. What may look like a Date, may not “play nice” with other dates that you have in your worksheet.

Let’s say that you have inherited an Excel workbook made by some Genius, (please note the thinly veiled sarcasm), and you want to Perform Some Analysis (in your case, truly genius work) that save the company countless hours and expense. The trouble is that unless you can Rely on the consistency of the way Excel will be handling the “dates” in the worksheets, you Cannot Rely on the efficacy of your results (the old “Garbage In, Garbage Out” cliché).

So what is an Excel Guru to do? DATEVALUE to the Rescue! Yes, indeed, my friends, DATEVALUE will calm your nerves, relieve your upset stomach, cure that nagging doubt that you are being watched, and make any Dates in your worksheet work in consistency with all of the dates therein. (Well, I may have exaggerated some of the attributes, but it will make your dates play nice with each other…). DATEVALUE will instantly convert any looks-like-a-date Date into the standard Excel serial number, and you can then format it as you wish.

How Cool is that! It may sound minor, but it can save you a world of grief in many circumstances.

Note of Caution: For you Apple Users out there, (I use Excel on a MacBook occasionally myself), Microsoft Excel for the Macintosh uses a different date system as its default (go figure…). The

The DATEVALUE function: Good Stuff!

Summarize Lists

Here is an easy, but highly useful technique. Let’s say that you have a Database of All Customers and the City in which they reside. It would quite probably be interesting to all of the stakeholders of this information to see a Summary of how many Customers you have in Each City.

 Well, this is quite simple to do, but is a good review, especially since we are going to once again mention Named Ranges.

First of all, Name the Range that contains the City. This can be done simply by selecting the range, (you can include blank cells not yet filled to allow for future growth), and type the Name of the Range in the Name Box in the upper-left-corner of your worksheet. For illustration, we will assume you have named it “City” (Clever, eh?...).

Then you can list the cities in the database, and (assuming your first entry is in A2) put in the following formula in the first adjacent cell:

  =Countif(City, A2)

After that, just copy the formula down next to each city and, Bamm!, You have your Summary!

Is this Simple? Yes, it is, but often times, the Simple Ways are the Best…

Odds and Evens

When I was in grade school, there was a popular gambling game amongst us boys that involved guessing “Odds” or “Evens” regarding the number of marbles the other boy held in his closed fist. Judging by how many times I spent my allowance on buying new sacks of marbles, I apparently wasn’t very good at the game.

When working with databases or tables, there are many instances when you wish to Identify and Sort whether the number is Odd or Even. Cases may involve street addresses, employee ID numbers, statistical studies, and several other pursuits.

So what is a Good Formula for identifying an Odd or Even number? As with so many things in Excel, there are several ways to do this, but the following are among the most straight-forward. Assuming your data is in Column A, you could use this formula and copy down your range:

1. =IF((ISEVEN(A1) = TRUE), "Even", "Odd") This IF formula incorporates the ISEVEN function to generate the desired results.

~ Or ~

2. =IF(MOD(A1,2)=0,"Even","Odd") This IF formula incorporates the MOD function to generate the results. MOD is an unusual function that finds the Remainder when you divide one figure by another.

Odds and Evens: There are times when knowing this information can be a Boon to your data analysis needs.

Cheers!

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!

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!