Tag Archives: Excel 2007

The VLOOKUP Function (A Mystery to Many Excel Users)

by Meggen Mills

The VLOOKUP Function is one of the most useful functions in Excel.  It is also one of the most confusing and least understood functions.  This article describes the formula syntax and usage of the VLOOKUP function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Excel.

Description

You can use the VLOOKUP function to search the first column of a range of cells, and then return a value from any cell on the same row of the range.

Example:

You are a new employee in the Benefits department of a large company with over 2500 employees.

Suppose your department has just sent a notice to selected company employees (approximately 750 employees) notifying them that they still have  vacation days available this year, and your boss has asked you to determine the department these selected employees work in. The only problem is that the notice you sent did not ask for their department; you only know their Name and Employee ID number. Continue reading


Let Excel’s Text Functions Help You “Save The Day”!

Have you ever been faced with the situation of having to import text from another source into Excel, and then to your dismay, discovered that the text did not look presentable? The text is either in all capitals or all lowercase letters–it’s not formatted in a professional manner.

Let’s say that you have imported data from a mainframe that included a large number of names of individuals, but the names appear in all lowercase letters. Your dilemma is you need to include the imported information as part of a larger report in Excel, and that the report must be formatted correctly and presented to your boss for an upcoming meeting. At this point, you are probably saying to yourself, “I will need to spend precious time re-typing so that it looks presentable, and I already have a million other things to do!” You might also be thinking to yourself, I know that I can use the Find and Replace feature, but I will have to be very careful so that no text is replaced in error.”

If you want your names to appear with the first letter of each name capitalized, simply use Excel’s PROPER function to help you.

In our example, we will use the names of individuals and their departments to illustrate how easy it is to use the PROPER function:

1.  Insert a blank column to the right of each column that you want to change. Then be sure to copy the appropriate column heading names at the top of the new columns:

2.  Type the PROPER function with the cell reference of the item to the left. In our example, we’ll use  =PROPER(A2)

3.  Copy the formula into the remaining cells where you want to see the names appear in the correct format.

4.  Repeat Steps 2 and 3 for the new Department column.

5.  When you have finished, simply right click on the Column A heading to get the shortcut menu and then select Hide Column. (IMPORTANT: Don’t delete the old column because you will lose your cell references and get error messages in your new column.) Then do the same for Column C. A screen shot of the final results appears below. There are a number of text functions that can help you. You can explore them by clicking on the Formulas tab and clicking the drop-down arrow for the Text icon in the Function Library Group.

For more information on BOSS software offerings, the BOSS degree and certificates, and how the BOSS program can help you with your career, contact Becky Jones, Associate Dean, bjones@dcccd.edu 972-238-6215.


Let Excel Help You Easily Make Metric Conversions

Have you ever had to convert miles to kilometers…inches to centimeters…gallons to liters…Fahrenheit degrees to Celsius degrees…or some other measurement unit?

Well, Excel can help you quickly and easily convert these measurement units.

For Excel 2007/2010 users:

  1. Open Excel and then click the Office Button in 2007 (click File tab in 2010)
  2. Click Excel Options button
  3. Click Add-Ins on the left
  4. Click on Analysis ToolPak from list
  5. Make sure Excel Add-Ins is selected in the Manage box
  6. Click Go
  7. Click to put a check mark in the box next to Analysis ToolPak and then click OK to return to your Excel worksheet

For Excel 2003 (or earlier) users:

  1. Open Excel
  2. Go to the Tools menu
  3. Select Add-Ins
  4. Select the Analysis ToolPak check box
  5. Click OK

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

For more information, contact Becky Jones, Associate Dean, bjones@dcccd.edu 972-238-6215.


Use Excel 2007/2010’s Table Feature to Help You Quickly Calculate, Format, and Sort Data

Have you ever needed to make an Excel spreadsheet look good in a hurry? Well, Excel’s table feature (introduced in the 2007 version) can help you get that data computed, sorted, and formatted in just five easy steps.

Here’s how the data initially looked on the left. Notice, the data has not been organized on any fields, and the format is very bland. Look at the difference once the table feature has been applied! (Click on the image to enlarge)

Use Excel's Table Feature To Make A Difference!

To give your spreadsheet data a more professional appearance, simply: 

  1. Select the data range and then click the Insert tab. ***IMPORTANT***Be sure there are no blank rows between your column headings (fields) and your actual data.
  2. Click the Table icon from the Table Group to determine your range and then click OK
  3. Select from one of the other 60 pre-designed table styles, if you don’t want to use the default table style.
  4. Click the Total check mark in the Table Styles Options Group and then scroll to the last row in the table and click the drop-down arrow to the right of the column(s) you want summarized (Count, Sum, Average, Min, etc.). In our example, we will include a total (SUM) for 1st Quarter Sales and take a count (COUNT) of the sales reps by last name.
  5. Sort the data on specific fields. In our example, we will sort the data by last name (LName field) by clicking on the arrow for that field and select the Sort A to Z option.

For more information, contact Becky Jones, Associate Dean, bjones@dcccd.edu, 972-238-6215