A Few Excel Tips

September 12th, 2011 - by Angela

excel-tipsSince the number one application that I do training on is Excel, I thought I would include some tips on Excel in this post.  Most of the training requests that I get are for Excel Intermediate or Advanced.  Over the years, it seems that there are a few features that have come in handy several times, either for myself or in helping others in a training class. I hope you are able to use these tips, too!

 For those of you who use the database features in Excel, leading zeros may present a problem. For example, say you are entering names and addresses.  In the Zip Code field there may be a zip code beginning with a zero (05678). When you key in 05678 and press enter, Excel will not display the leading zero. Excel will display the number as 5678.

One way to correct the problem is to create a custom number format for all the cells in which you might be entering numbers beginning with a zero. To create a custom number format, first select all of the cells in which you will be entering numbers that might begin with a zero. Right click on that selection and choose Format Cells. On the Number tab, choose Custom under Category.

In the Type field, clear any displayed information (General, for instance) and enter a zero for every digit - including zeros - you want to show in the selected cell or cells. For example, if you wanted to enter 05678 you would enter 00000 in the Type field. Then all you need to do is click OK-a very simple solution to a one of those frustrating Excel situations!

Another tip to use within Excel’s database element (in the 2007 and 2010 versions) is the Excel table feature.  You can use the Table feature to help you calculate, format and sort data quickly. If you have ever needed to make an Excel spreadsheet look good in a hurry, then you will love the table feature.

To give your spreadsheet data a more professional appearance, start by selecting the data range and clicking on the Insert tab.  (NOTE: It will not work if you have blank rows between your column headings and your actual data.) Click the Table icon from the Table group to determine your range and click OK.  Next, select from one of the pre-designed table styles.  Click the “Total” check mark in the Table Styles option group and then scroll to the last row in the table.  If you click the drop-down arrow to the right of the column(s) you want summarized, you can then choose to Average, Count, Sum, etc. To sort the data on a specific column, you can click the down arrow at the top of that column and choose the sort option you would like.

Another question that I have dealt with recently was related to conversions between different measurements.  In the process of helping someone from another country, I had to figure out some exchanges between our American standard of measure and the Metric system.  Since I do not have all of those conversions memorized, I was able to use Excel to help out. If you have ever had to convert miles to kilometers, inches to centimeters, or Fahrenheit degrees to Celsius degrees then Excel can help you, too.

For Excel 2007/2010 users, click the Office Button in 2007 (or File tab in 2010) > Click Excel Options button > Click Add-Ins on the left > Click on Analysis ToolPak from list >    Make sure Excel Add-Ins is selected in the Manage box > Click Go.  Click to put a check mark in the box next to Analysis ToolPak and then click OK to return to your worksheet.

For Excel 2003 (or earlier) users, Go to the Tools menu > Select Add-Ins > Select the Analysis ToolPak check box > Click OK.

Then, you can use the Excel functions to do the conversions.  For example, if you want to convert 2 1/2 pounds to kilograms, use the following Excel function: =CONVERT(2.5, “lbm”, “kg”) Your answer should be 1.133981. To find the kilogram equivalent of 1 pound, just replace 2.5 in the formula with 1.0, which should yield a result of 0.453592.  For a complete list of measurement unit conversions available in Excel, visit the following Microsoft web site: http://office.microsoft.com/en-us/excel/HP100623111033.aspx

Hope you are able to excel with Excel using these tips!


Leave a Reply