Wednesday, June 29, 2011
First off, we have the CLEAN function. This removes the nonprinting (but often, irritating) characters, as well as other garbage characters that may have tagged along in your imported data.
Another useful function is TRIM, which eliminates any Unwanted Spaces. This is very helpful, since extra spaces are frequently imported from some sources.
Then we have one of my very favorites. The REPLACE function is quite versatile and a good tool to have at your disposal. Let’s say that you have imported data which has 5 leading unwanted characters (such as “0.00 ”). Please note that the characters may be numbers, letters, punctuation, or even spaces. If, for example, your data starts in cell A1, you could put the following formula in B1 and drag it down for the remainder of the range:
Presto! No more unwanted leading characters. So, there you have it! Three easy-to-use methods of Cleaning Up Your Data. It is always better when things are clean!
Happy Fourth of July, All!
Thursday, June 23, 2011
For example, let’s say you want to convert the number of Feet in a measurement to the corresponding number of Meters. CONVERT to the rescue! In fact, CONVERT can translate a Wide Variety of measurements that include time, distance, temperature, weight, and much more.
The Syntax of the CONVERT function is as follows:
CONVERT(Number, From Unit, To Unit) where:
1. Number is the Amount in From Units to convert
2. From Unit are the units Being Converted
3. To Unit are the Units in the Result
The table below shows the results of some of the more Commonly Used Conversions:
For a Full List of all the measures that can be converted, please see the CONVERT function Help topic.
The CONVERT function. It is always there when you need it. Check it out when you have a moment!
Thursday, June 16, 2011
Here are the Top 5 Ways I use Paste Special on a regular basis:
1) Paste Values: Simply copy the results of formulas, and Paste the Values in a new range of cells
2) Multiply or Divide: You can copy a value and use Paste Special to multiply or divide an entire existing range of numbers
3) Paste Validation: Easily copy a cell that has Validation applied, and paste that validation only in a new cell or range
4) Transpose: You can easily transpose a column into a row, or a row into a column
5) Paste a Link: Simply copy a cell in another worksheet, and using the Paste Link button on Paste Special, you can paste a dynamic link to a new location.
Paste Special is truly one of the indispensible tools in Excel. If you haven’t been using it, give it a try today. You will wonder how you ever lived without it!
Thursday, June 9, 2011
Charts are, of course, a wonderful way to communicate information in your Excel Worksheet. A well-designed chart can immediately translate your data in a compelling visual way. Sometimes, however, your chart may need a little help.
The overall trend of your data is valuable information that may not be readily discernable. As in the case of the illustration above, is the trend up, down, or flat? It is not easy to tell. This is where a simply applied Trendline can make all the difference in the communicative value of your chart.
To add a Trendline, simple right-click on the Data Series Line on your chart, and choose Add Trendline from the dropdown list. This will immediately insert a Trendline on your chart.
But don’t stop there! Right-click your Trendline and add some formatting magic to make it look professional in every sense. Changing the Color, Weight, and Line Style can make a world of difference.
As in the illustration below, it is now easy to see that there is an overall upward trend in your data. Good News, if you are in Sales!
Trendlines. Easy stuff. Give them a try!
Thursday, June 2, 2011
Believe me; if you are not using keyboard shortcuts on a regular basis, you are letting yourself down. They add speed, efficiency, relief from the stress of overusing the mouse, and they are Just Plain Cool!
So, in David Letterman-style, here are my current favorite (and highly recommended) Top Ten Excel Shortcuts in reverse order:
10. Shift + F3 (Pulls up your Insert Function – Handy!)
9. F5 (Brings up the Go TO dialogue box – Great for navigating to a named range)
8. Ctrl + F4 (Closes the Current Window – Quick and easy!)
7. Ctrl + P (Pulls up the Print Dialog Box – Gotta love it)
6. F12 (Brings up the Save As dialogue – Nice!)
5. Ctrl + Z (Undo Previous Action – Oldie, but a goodie)
4. Ctrl + H (Find and Replace – I use it often!)
3. Alt + = (Brings up the AutoSum – Beats going to the toolbar)
2. Ctrl + Home (Brings you to the Start of the Worksheet – It is always good to go home)
And My Current Number One Best Shortcut Is (Drum Roll and Cymbals, Please)...
1. F11 (Creates an Instant Chart – Always a crowd pleaser!)
There you have it! Try a few of them, and see how they can make your Excel life better!