Tag Archives: Excel 2010

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 Workspace Feature Help Save You Time!

Do you need to work with several workbooks (files) at one time in a particular arrangement on the screen? If so, did you know that you can group them so they can be opened in one step by creating what is known as a workspace?

A workspace is a file that groups all of the workbooks in your group and opens them all at once in the desired arrangement. The workspace feature creates a file that has an .xlw file extension. You save time because you don’t have to open each file individually and then re-size. This feature comes in handy if you are working with three or four files at a time.

To create workspace, just complete the following steps:

  1. Open all of the workbooks that you want to reside in the workspace.
  2. Size and position them as would like them to appear on the screen.
  3. Click the View tab and click the Save Workspace button in the Windows group.
  4. Type a name for your workspace file and navigate to the file location where you want your workspace file stored.

IMPORTANT:

The workspace file does not contain the workbooks themselves, so you will still need to save any changes you make to your workbooks to the actual workbooks. Hint: You can add the Save All and Close All icons to your QUICK ACCESS TOOLBAR to help you quickly save and close all of the files.

Remember, if you need to work at another computer, you need to have the workspace file as well as all of the workbook files—so it’s a good idea to save all of the files in one handy folder.

Source: Microsoft Excel 2010 Illustrated Series: Complete by Elizabeth Eisner Reding and Lynn Wermers, Course Technology/Cengage Learning, Boston: 2011, p. 145.

If you need to take one or more classes to help you enhance your technology skills and productivity, consider taking one of the Microsoft Office classes or other skills/productivity development classes from the BOSS area at Richland College. For more information on BOSS software and productivity course offerings, the BOSS degree and certificates contact Becky Jones, Associate Dean, bjones@dcccd.edu 972-238-6215.


How to Link a Single PowerPoint Slide to Word or Excel 2007/2010
Have you ever had to prepare for a meeting and needed to enhance a Word or Excel report with one or two PowerPoint slides from an existing PowerPoint presentation?
You may already know how it is easy to create a link to a PowerPoint file within Word or Excel, but what if you don’t want to view the entire presentation? You will then find yourself “clicking through” the presentation to get to that meaningful slide, which just happens to be Slide 20! Furthermore, you should not be forced to make a separate presentation file for that all important slide or two you want to show your group at this event.
Ellen Finkelstein, who writes for the PC Pitstop Newsletter, has a great tip that can help you navigate quickly and easily from Word or Excel to that specific slide in PowerPoint.
Follow these steps to use this handy tip:
1. Suggestion: Put all of your Word/Excel and PowerPoint files for this project in the same folder.
2.  Open your PowerPoint presentation, go to the slide you want to link to Word/Excel, and note the the number of the slide, e.g., 6.
Tip: If you want the title of the slide to appear in the Tool Tip for the hyperlink, note the slide title as well, e.g., Contact Information.
3.  Then choose File Save As and change the file type to PowerPoint Show.
4.  Open your Word/Excel file, and go to the location in this file where the hyperlink will be inserted. You can either right click to get the shortcut menu, or use the Insert tab and choose Hyperlink.
5.  Once you are in the In the Insert Hyperlink dialog box, click the Existing File or Web Page button. Select the file—notice the icon is different for a PowerPoint Show than a regular PowerPoint presentation, and the file extensions are also different—.ppsx versus .pptx.
6. You’ll see the name of the file in the dialog box’s Address text box. If there are spaces in the file name, you may see %20 in place of the spaces. The HTML code for a space is %20, so just leave the codes as they are if they are found in name of your file.
7.  At the end of the file name in the Address box, add a # symbol, then the slide number. Depending on the configuration of your system, however, you may not have the %20 codes.
An example of the %20 codes might be: How%20To%20Link%20A%20Single%20Slide.ppsx#6.
Tip: To add a tool tip to your hyperlink back in your Word/Excel file, be sure you are still in the PowerPoint file, then click the Outline tab in the left-hand pane and copy and paste the slide title from so that it appears as the end of the name in the Address box. Tool Tips come in handy when you are hovering over a hyperlink because they help identify the specific link. In our example the title of the slide is Contact Information.  See an example in the illustration above.
8. When you have finished with the Hyperlink dialog box, click OK to return to your Word/Excel document.
9.  Test your hyperlink by holding the Ctrl key + clicking on the hyperlink on the text (or object in our case) to see it open to the specific slide in PowerPoint.
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.

Register Now for Fall 2012 Semester!
Registration is already underway for this Fall semester at Richland College, so get a head start by signing up today! Fall classes begin Monday, August 27 and end on Thursday, December 13.

There are plenty of on-campus and online BOSS classes to choose from. Click this link to look at the BOSS lineup for Fall.

The BOSS class offerings range from classes in computer literacy basics, basic keyboarding, business math, records management, office procedures etc., to becoming proficient in the Microsoft Office applications—Excel, Word, PowerPoint, Access, and Publisher that so widely used in offices today.

Get a “leg up” and improve your employment chances by getting your Microsoft Certification in Word or Excel or Access or PowerPoint! The BOSS dedicated Microsoft Office application courses are designed to prepare you for the Microsoft Certification exams.

If you need to improve your business writing or grammar skills, consider the Business Correspondence and Communication class or Business English.

***IMPORTANT***If you plan to take on-campus classes but you did not attend the Spring 2012 semester, you must show proof of having had the meningitis vaccination. Please have your doctor fax the appropriate form to the Registrar’s office. This form must be received BEFORE you can be cleared to register. You can get more details by clicking this link.

However, if you plan to take online BOSS classes, you can secure a waiver for the meningitis vaccination by clicking this link also.

Don’t delay! Enhance your professional growth and skills by taking BOSS classes that can help you right now as well as in the future!

For more information on BOSS Fall course offerings, the BOSS degree and certificates, contact Becky Jones, Associate Dean, bjones@dcccd.edu 972-238-6215.


Excel 2010: Macros, Solver and Data Analysis

When you first use Excel 2010, you may have a few items that you have to go hunting for in the ribbon.  If you use macros, you will need to have the Developer tab available.  By default, the Developer tab does now show.  On the File tab, click Options, then select Customize Ribbon in the left pane.  On the list on the right, click the Developer check box and then click OK.  The developer tab will now be available for you.

Your buttons for access to macros and Visual Basic elements are on the developer tab.  In addition, you have form and Active X controls, XML schemas, and Add-Ins. There are two of these common Add-Ins that you might be looking for that you used in previous versions of Excel or that you would like to explore.  To get these to show up, click on the Developer tab and select the Add-Ins button (look for the gears on the button).  Then, click to place a check box for Analysis Toolpak and Solver Add-in.

Once these two elements are enabled, they will appear under the Data tab.  The Data Analysis button will be on the far right side of the Data tab ribbon.  This is a fantastic tool for statistical analysis of your data.  If you currently use a separate statistics package for market research or similar analysis, you might be able to use Excel instead.  Many of the everyday market research statistics can be done within the data analysis feature of Excel!

Solver is a great tool, too. Once you click the Solver option, it opens a dialog box that will allow you to enter parameters and conditions to try to solve a problem.  For example, if you are attempting to rent vehicles to transport a certain number of people to a conference, you could use solver to plug in the total number of people and the target budget.  You would input which cells had the numbers in it that could change and adjust (i.e. the number of vehicles and the number per vehicle).  You would also key in the constraints, like how many people could ride in each vehicle type (van, car, etc.) and that there could only be whole numbers of people in each vehicle. Once you set all of this information, Solver will attempt to analyze it for you. I actually used solver to figure this out for one of my advanced Excel classes.  It was really interesting to watch the class come together to think through all of the constraints, etc.

Enjoy using your tools!

________________________________________________________

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’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