Are you new to the ribbon interface in Office 2010?? Here’s how you can get up to speed and learn where everything is quickly and easily.

Microsoft has made an interactive guide to Office 2010’s new interface to help users learn their way around the new version.? If you’ve already used Office 2007, then Office 2010 will be very easy to transition to, but if you’re still using Office 2003 you may find the learning curve more steep.? With this interactive guide, upgrading your Office skills doesn’t have to be hard.

Learn Your Way Around the Office Ribbon

Open the Office 2010 interactive guides site (link below) in your browser, and select the Office app you want to explore.

The guides are powered by Silverlight, so if you don’t already have it installed you will be prompted to do so.

Once the guide has loaded, click Start to begin.

Select any menu or toolbar item in the Office 2003 mockup.? A tooltip will appear to show you how to find this option in Word 2010.

If you click the item, the interface will switch to an Office 2010 mockup and will interactively show you how to access this feature.? The Thumbnails view isn’t available by default in Word 2010, so it shows us how to add it to the ribbon.? When you’ve figured this command out, click anywhere to go back to the Office 2003 mockup and find another item.

Currently the guides are available for Word, Excel, and PowerPoint, but the site says that guides for the other Office apps will be available soon.? Here’s the PowerPoint guide showing where the Rehearse Timings option is in PowerPoint 2010.

Install the Interactive Guides to Your Computer

You can also install the guides to your computer so you can easily access them even if you’re not online.? Open the guide you want to install, and click the Install button in the top right corner of the guide.

Choose where you want the shortcuts, and click Ok.

Here’s the Interactive Word 2010 guide installed on our computer.? The downloaded version seemed to work faster in our tests, likely because all the content was already saved to the computer.? If you decide you don’t need it any more, click Uninstall in the top right corner.

Download Office Cheat Sheets

If you’d like a cheat-sheet of Office commands that have changed or are new in Office 2010, Microsoft’s got that for you, too.? You can download Office reference workbooks (link below) that show how to access each item that was in Office 2003’s menus.? Here’s the Word guide showing where each of Word 2003’s commands from the help menu are in Word 2010.

Learn Your Way Around Office 2007, Too!

Microsoft offers similar interactive guides for learning the ribbon in Office 2007, so if you’re still using Office 2007 but can’t find a command, feel free to check it out as well (link below).? Guides are available for Word, Excel, PowerPoint, Access, and Outlook 2007.? You can also download cheat sheets for Office 2007 at this site as well.? Here’s the tutorial showing us where the font options are in PowerPoint 2007.

Conclusion

We have found the ribbon interface to be a great addition to Office, but if you’ve got years of Office 2003 experience under your belt you may find it difficult to locate your favorite commands.? These tutorials can help you use your old Office knowledge to learn Office 2010 or 2007 in a quick and easy way!

Links

Office 2010 interactive guide

Download Office 2010 reference workbooks

Office 2007 interactive guide

Similar Articles	Productive Geek Tips
How To Find Commands and Functions in Office 2007 the Easy Way Make Excel 2007 Always Save in Excel 2003 Format Make Word 2007 Always Save in Word 2003 Format Add or Remove Apps from the Microsoft Office 2007 or 2010 Suite Create a Customized Tab on the Office 2010 Ribbon	TouchFreeze Alternative in AutoHotkey The Icy Undertow Desktop Windows Home Server – Backup to LAN The Clear & Clean Desktop Use This Bookmarklet to Easily Get Albums Use AutoHotkey to Assign a Hotkey to a Specific Window
Latest Software Reviews	Tinyhacker Random Tips
DVDFab 6 Revo Uninstaller Pro Registry Mechanic 9 for Windows PC Tools Internet Security Suite 2010	Outlook Connector Upgrade Error Gadfly is a cool Twitter/Silverlight app Enable DreamScene in Windows 7 Microsoft’s “How Do I ?” Videos Home Networks – How do they look like & the problems they cause Check Your IMAP Mail Offline In Thunderbird

• 0 Comments

Sometimes when you copy text from a webpage and paste it into Word, it can be annoying when the hyperlinks transfer with it. Today we take a look at how to easily remove the hyperlinks if you don’t want them in the document.

Here we will cover a few different ways you can remove hyperlinks from emails or webpage data that you enter into a Word document using Paste Special and Keyboard Shortcuts.

Remove Hyperlinks in Word Using Paste Special

In Word 2010 we copied part of an article from How-To Geek, as you can see the hyperlinks were copied into the document as well.

To remove the hyperlinks right-click on the document and you’ll see three icons under Paste Options.

We want to select the third one to the right which is Keep Text Only…the text in in the document changes so you can get a preview of how it will look.

After selecting Keep Text Only, you can see the hyperlinks have been removed. However, you’ll need to change the fonts and other layouts if you’re not happy with the default of Calibri.

In Office 2007 under the Home tab click the dropdown menu under Paste and select Paste Special.

In the Paste Special screen select Unformatted Text then click OK.

In Office 2003 copy the text into the document, hover your mouse over the clipboard, click the dropdown menu, then select Keep Text Only.

Keyboard Ninja Style

If you’re a Keyboard Ninja, an easy way to remove all hyperlinks is to use “Ctrl+A” to select everything, then use the key combination “Ctrl+Shift+F9”…We tested this key combination and it works in Word 2003-2010.

This will remove all of the hyperlinks and keep the original format of the text.

Conclusion

There are several different ways to get rid of hyperlinks in text pasted into Word documents. The method you use will depend on your preference and the version of Word you’re using…but the “Ctrl+Shift+F9” keyboard shortcut works in all versions of Word and might be the easiest way. If you always want just text in Word 2007, you can disable automatically disable them.

If you have a different method you use to remove hyperlinks from Word documents, leave a comment and let us know!

Similar Articles	Productive Geek Tips
Preview Documents Without Opening Them In Word 2007 Embed True Type Fonts in Word and PowerPoint 2007 Documents Add Background Color To Word 2007 Documents Use Image Placeholders to Display Documents Faster in Word How To Make Sure Word Prints Document Backgrounds	TouchFreeze Alternative in AutoHotkey The Icy Undertow Desktop Windows Home Server – Backup to LAN The Clear & Clean Desktop Use This Bookmarklet to Easily Get Albums Use AutoHotkey to Assign a Hotkey to a Specific Window
Latest Software Reviews	Tinyhacker Random Tips
DVDFab 6 Revo Uninstaller Pro Registry Mechanic 9 for Windows PC Tools Internet Security Suite 2010	Gadfly is a cool Twitter/Silverlight app Enable DreamScene in Windows 7 Microsoft’s “How Do I ?” Videos Home Networks – How do they look like & the problems they cause Check Your IMAP Mail Offline In Thunderbird Follow Finder Finds You Twitter Users To Follow

• 0 Comments

There are several scenarios where you may run into trouble uninstalling the 2010 Beta and trying to install the RTM (final) release. Today we’ll cover the problems we ran into, and how to fix them.

You would think upgrading from the Office 2010 Beta to the final release would be an easy process. Unfortunately, it’s not always that simple. In fact, we ran into three different scenarios where the install wasn’t smooth whatsoever.

If you currently have the 2010 Beta installed, you have to remove it before you can install the RTM.? Here we’ll take a look at three different troublesome install scenarios we ran into, and how we fixed each one.

Important Note: Before proceeding with any of these steps, make sure and backup your Outlook .pst files!

Scenario 1 – Uninstall Office 2010 Beta & Fix Install Errors

In this first scenario we have Office Professional Plus 2010 Beta 32-bit installed on a Windows 7 Home Premium 32-bit system.

First try to uninstall the Office 2010 Beta by going into Control Panel and selecting Programs and Features.

Scroll down to Microsoft Office Professional Plus 2010, right-click it and select Uninstall.

Click Yes when the confirmation dialog box comes up.

Wait while Office 2010 Beta uninstalls…the amount of time it takes will vary from system to system.

To complete the uninstall process, a reboot is required.

Fixing Setup Errors

The problem is when you start the installation of the 2010 RTM…

You get the following setup error even though you uninstalled the 2010 Beta. The problem is there are leftover Office apps or stand alone Office products. So, we need a utility that will clean them up for us.

?

Windows Installer Clean Up Utility

Download and install the Clean Up Utility (link Below) following the defaults.

After it’s installed you’ll find it in Start All Programs Windows Install Clean Up …go ahead and launch the utility.

Now go through and remove all Office Programs or addins that you find in the list. Make sure you are just deleting Office apps and not something you need like Java for example. If you’re not sure what something is, doing a quick Google search should help you out. For instance we had the Office labs Ribbon Hero installed… just highlight and click Remove.

Remove anything that has something to do with Office…then reboot your machine.

Now, you should be able to begin the installation of Office 2010 RTM (Final) Release without any errors.

If you do get an error during the install process, like this one telling us we have old version of Groove Server…

Navigate to C:UsersusernameAppDataLocalMicrosoft (where username is the computer name) and delete any existing MS Office folders. Then try the install again, this solved the problem in our first scenario.

Scenario 2 – Not Being Able to Uninstall 2010 Beta from Programs and Features

In this next scenario we have Office Professional Plus 2010 Beta 32-bit installed on a Windows 7 Home Premium 32-bit system.

Another problem we ran into is not being able to uninstall the 2010 Beta from Programs and Features. When you go in to uninstall it, nothing happens.

If you run into this problem, we again need to download and install the Windows Installer Clean Up Utility (link below) and manually uninstall the Beta. When you launch it, scroll down to Microsoft Office Professional Plus 2010 (Beta), highlight it and click Remove.

?

Click OK to the Warning Dialog box…

If you see any other Office 2010, 2007, or 2003 entries you can hold the “Shift” key and highlight them all…then click Remove and click OK to the warning dialog.

Now we need to delete some Registry settings. Click on Start and type regedit into the Search box and hit Enter.

Navigate to HKEY_CURRENT_USER Software Microsoft Office and delete the folder.

Then navigate to HKEY_LOCAL_MACHINE Software Microsoft Office and delete those keys as well.

Now go into C:Program Files and find any of these three folders…Microsoft Office, OfficeUpdate, or OfficeUpdate14…you might find one, two or all three. Either way just rename the folders with “_OLD” (without quotes) at the end.

Then go into C:UsersusernameAppDataLocalMicrosoft and delete any existing MS Office folders. Where in this example we have office, Office Labs, One Note…etc.

Now we want to delete the contents of the Temp folder. Click on Start and type %temp% into the Search box and hit Enter.

Use the key combination “Ctrl+A” to select all the files in this folder, then right-click and click Delete, or simply hit the Delete key.

If you have some files that won’t delete, just skip them as they shouldn’t affect the Office install. Then empty the Recycle Bin and restart your machine.

When you get back from the restart launch the Office 2010 RTM installer and you should be good to go with installation.

Because we uninstalled the Office 2010 Beta manually, you may have some lingering blank icons that you’ll need to clean up.

Scenario –3 Uninstall 2007 and Install 2010 32-Bit on x64 Windows 7

For this final scenario we are uninstalling Office Professional 2007 and installing Office Professional Plus 2010 32-Bit edition on a Windows Ultimate 64-bit computer. This machine actually had Office 2010 Beta 64-bit installed at one point also, it’s since been removed, and 2007 was reinstalled.?

Go into Programs and Settings and uninstall Microsoft Office Professional 2007.

Click Yes to the dialog box asking if you’re sure you want to uninstall it…

Then wait while Office 2007 is uninstalled. The amount of time it takes will vary between systems.

A restart is required to complete the process…

Again we need to call upon the Windows Installer Clean Up Utility. Go through and delete any left over Office 2007 and 2010 entries.

Click OK to the warning dialog that comes up.

After that’s complete, navigate to HKEY_CURRENT_USER Software Microsoft Office and delete the folder.

Then navigate to HKEY_LOCAL_MACHINE Software Microsoft Office and delete those keys as well.

We still need to go into C:Users usernameAppData Local Microsoft (where username is the computer name) and delete any Office folders. In this example we have Outlook Connector, Office, and Outlook to delete.

Now let’s delete the contents of the Temp folder by typing %temp% into the Search box in the Start Menu.

Then delete all of the files and folders in the Temp directory.

If you have some files that won’t delete, just skip them as they shouldn’t affect the Office install. Then empty the Recycle Bin and restart your machine.

If you try to install the 2010 RTM at this point you might be able to begin the install, but may get the following Error 1402 message.

To solve this issue, we opened the command prompt and ran the following:

secedit /configure /cfg %windir%infdefltbase.inf /db defltbase.sdb /verbose

After the command completes, kick off the Office 2010 (Final) RTM 32-bit edition. This solved the issue and Office 2010 installed successfully.

?

Conclusion

Except for the final scenario, we found using the Windows Installer Clean Up Utility to come in very handy. Using that along with deleting a couple folders and registry settings did the trick. In the last one, we had to get a bit more geeky and use some command line magic, but it got the job done.

After some extensive testing in our labs, the only time the upgrade to the RTM went smoothly was when we had a clean Vista or Windows 7 system with a fresh install of the 2010 beta only. However, chances are you went from 2003 or 2007 to the free 2010 Beta. You might also have addins or other Office products installed, so there are going to be a lot of different office files scattered throughout your PC. If that’s the case, you may run into the issues we covered here.

These are a few scenarios where we got errors and were not able to install Office 2010 after removing the beta. There could be other problems, and if any of you have experienced different issues or have more good suggestions, leave a comment and let us know!

Link

Download Windows Installer Clean Up Utility

Similar Articles	Productive Geek Tips
Remove Office 2010 Beta and Reinstall Office 2007 How to Upgrade the Windows 7 RC to RTM (Final Release) Upgrading Ubuntu from Dapper to Edgy with Update Manager Disable Office 2010 Beta Send-a-Smile from Startup Add or Remove Apps from the Microsoft Office 2007 or 2010 Suite	TouchFreeze Alternative in AutoHotkey The Icy Undertow Desktop Windows Home Server – Backup to LAN The Clear & Clean Desktop Use This Bookmarklet to Easily Get Albums Use AutoHotkey to Assign a Hotkey to a Specific Window
Latest Software Reviews	Tinyhacker Random Tips
DVDFab 6 Revo Uninstaller Pro Registry Mechanic 9 for Windows PC Tools Internet Security Suite 2010	Case Study – How to Optimize Popular Wordpress Sites Restore Hidden Updates in Windows 7 & Vista Iceland an Insurance Job? Find Downloads and Add-ins for Outlook Recycle ! Find That Elusive Icon with FindIcons

• 0 Comments

Are you frustrated by always having to change the font size before you create a document it Word?? Here’s how you can end that frustration and set your favorite default font size for once and for all!

Microsoft changed the default font font to 11 point Calibri in Word 2007 after years of 12 point Times New Roman being the default.? Although it can be easily overlooked, there are ways in Word to change the default settings to anything you want.? Whether you want to change your default to 12 point Calibri or to 48 point Comic Sans…here’s how to change your default font settings in Word 2007 and 2010.

Changing Default Fonts in Word

To change the default font settings, click the small box with an arrow in the right left corner of the Font section of the Home tab in the Ribbon.

?

In the Font dialog box, choose the default font settings you want.? Notice in the Font box it says “+Body”; this means that the font will be chosen by the document style you choose, and you are only selecting the default font style and size.? So, if your style uses Calibri, then your font will be Calibri at the size and style you chose.? If you’d prefer to choose a specific font to be the default, just select one from the drop-down box and this selection will override the font selection in your document style.

Here we left all the default settings, except we selected 12 point font in the Latin text box (this is your standard body text; users of Asian languages such as Chinese may see a box for Asian languages).? When you’ve made your selections, click the “Set as Default” button in the bottom left corner of the dialog.

You will be asked to confirm that you want these settings to be made default.? In Word 2010, you will be given the option to set these settings for this document only or for all documents.? Click the bullet beside “All documents based on the Normal.dotm template?”, and then click Ok.

In Word 2007, simply click Ok to save these settings as default.

Now, whenever you open Word or create a new document, your default font settings should be set exactly to what you want.? And simply repeat these steps to change your default font settings again if you want.

Editing your default template file

Another way to change your default font settings is to edit your Normal.dotm file.? This file is what Word uses to create new documents; it basically copies the formatting in this document each time you make a new document.

To edit your Normal.dotm file, enter the following in the address bar in Explorer or in the Run prompt:

%appdata%MicrosoftTemplates

This will open your Office Templates folder.? Right-click on the Normal.dotm file, and click Open to edit it.? Note: Do not double-click on the file, as this will only create a new document based on Normal.dotm and any edits you make will not be saved in this file.

?

Now, change any font settings as you normally would.? Remember: anything you change or enter in this document will appear in any new document you create using Word.

If you want to revert to your default settings, simply delete your Normal.dotm file.? Word will recreate it with the standard default settings the next time you open Word.

Please Note: Changing your default font size will not change the font size in existing documents, so these will still show the settings you used when these documents were created.? Also, some addins can affect your Normal.dotm template.? If Word does not seem to remember your font settings, try disabling Word addins to see if this helps.

Conclusion

Sometimes it’s the small things that can be the most frustrating.? Getting your default font settings the way you want is a great way to take away a frustration and make you more productive.

And here’s a quick question: Do you prefer the new default 11 point Calibri, or do you prefer 12 point Times New Roman or some other combination?? Sound off in the comments, and let the world know your favorite font settings.

Similar Articles	Productive Geek Tips
Change the Default Font in Excel 2007 Add Emphasis to Paragraphs with Drop Caps in Word 2007 Keep Websites From Using Tiny Fonts in Safari Make Word 2007 Always Save in Word 2003 Format Stupid Geek Tricks: Enable More Fonts for the Windows Command Prompt	TouchFreeze Alternative in AutoHotkey The Icy Undertow Desktop Windows Home Server – Backup to LAN The Clear & Clean Desktop Use This Bookmarklet to Easily Get Albums Use AutoHotkey to Assign a Hotkey to a Specific Window
Latest Software Reviews	Tinyhacker Random Tips
DVDFab 6 Revo Uninstaller Pro Registry Mechanic 9 for Windows PC Tools Internet Security Suite 2010	Spyware Blaster v4.3 Yes, it’s Patch Tuesday Generate Stunning Tag Clouds With Tagxedo Install, Remove and HIDE Fonts in Windows 7 Need Help with Your Home Network? Awesome Lyrics Finder for Winamp & Windows Media Player

• 0 Comments

A tasks list can be convenient but most times you can not include details for those tasks or have to have an online account to do so. If you want to keep your tasks list with you on your computer or laptop and be able to add plenty of details then you might want to look at toDoo.

Note: Requires Adobe AIR (download link at bottom of article).

toDoo in Action

Once you have installed toDoo everything is rather straightforward for getting started. The first time that you start toDoo there will be a temporary “fill-in” for the “Subject & Details Areas”. Simply highlight over the temporary text and add your information. Notice that if desired you can easily set a custom date and time for your tasks right below the “Details Area”.

Note: toDoo does not minimize to the “System Tray”.

Once you have everything set all that you need to do is click on “add task”.

Here was our first new task being viewed in the “toDoo Description Tab”.

Time to add a second task…here you can see the drop-down calendar. You can scroll through and select a different month very easily…just click on the desired day and it will be automatically set.

Adding our second task…

If you need to edit any of the details for a particular task you can do so in the “Edit toDoo Tab”. This nice little app is convenient and easy to use.

Conclusion

ToDoo is a simple straightforward app that lets you keep track of your tasks list and relevant details without an online account (especially helpful if you are without a wireless connection at a given moment). If you are looking for more of a list approach that runs on your desktop, then check out our article on Doomi here.

Links

Download ToDoo at Softpedia

Download ToDoo at Adobe Marketplace

Download Adobe AIR

Similar Articles	Productive Geek Tips
Turn Chrome’s New Tab Page into a Google Tasks Page Make To-Do Bar in Outlook 2007 Show Only Today’s Tasks Add a non-Google Tasks List to Chrome Keep Track of Homework Assignments with Soshiku Track the Amount of Time You Spend Online in Firefox	TouchFreeze Alternative in AutoHotkey The Icy Undertow Desktop Windows Home Server – Backup to LAN The Clear & Clean Desktop Use This Bookmarklet to Easily Get Albums Use AutoHotkey to Assign a Hotkey to a Specific Window
Latest Software Reviews	Tinyhacker Random Tips
DVDFab 6 Revo Uninstaller Pro Registry Mechanic 9 for Windows PC Tools Internet Security Suite 2010	Download Videos from Hulu Pixels invade Manhattan Convert PDF files to ePub to read on your iPad Hide Your Confidential Files Inside Images Get Wildlife Photography Tips at BBC’s PhotoMasterClasses Mashpedia is a Real-time Encyclopedia

• 0 Comments

In a recent article, we introduced the Excel function called VLOOKUP and explained how it could be used to retrieve information from a database into a cell in a local worksheet.? In that article we mentioned that there were two uses for VLOOKUP, and only one of them dealt with querying databases.? In this article, the second and final in the VLOOKUP series, we examine this other, lesser known use for the VLOOKUP function.

If you haven’t already done so, please read the first VLOOKUP article – this article will assume that many of the concepts explained in that article are already known to the reader.

When working with databases, VLOOKUP is passed a “unique identifier” that serves to identify which data record we wish to find in the database (e.g. a product code or customer ID).? This unique identifier must exist in the database, otherwise VLOOKUP returns us an error.? In this article, we will examine a way of using VLOOKUP where the identifier doesn’t need to exist in the database at all.? It’s almost as if VLOOKUP can adopt a “near enough is good enough” approach to returning the data we’re looking for.? In certain circumstances, this is exactly what we need.

We will illustrate this article with a real-world example – that of calculating the commissions that are generated on a set of sales figures.? We will start with a very simple scenario, and then progressively make it more complex, until the only rational solution to the problem is to use VLOOKUP.? The initial scenario in our fictitious company works like this:? If a salesperson creates more than $30,000 worth of sales in a given year, the commission they earn on those sales is 30%.? Otherwise their commission is only 20%.? So far this is a pretty simple worksheet:

To use this worksheet, the salesperson enters their sales figures in cell B1, and the formula in cell B2 calculates the correct commission rate they are entitled to receive, which is used in cell B3 to calculate the total commission that the salesperson is owed (which is a simple multiplication of B1 and B2).

The cell B2 contains the only interesting part of this worksheet – the formula for deciding which commission rate to use: the one below the threshold of $30,000, or the one above the threshold.? This formula makes use of the Excel function called IF.? For those readers that are not familiar with IF, it works like this:

IF(condition,value if true,value if false)

Where the condition is an expression that evaluates to either true or false.? In the example above, the condition is the expression B1, which can be read as “Is B1 less than B5?”, or, put another way, “Are the total sales less than the threshold”.? If the answer to this question is “yes” (true), then we use the value if true parameter of the function, namely B6 in this case – the commission rate if the sales total was below the threshold.? If the answer to the question is “no” (false), then we use the value if false parameter of the function, namely B7 in this case – the commission rate if the sales total was above the threshold.

As you can see, using a sales total of $20,000 gives us a commission rate of 20% in cell B2.? If we enter a value of $40,000, we get a different commission rate:

So our spreadsheet is working.

Let’s make it more complex.? Let’s introduce a second threshold:? If the salesperson earns more than $40,000, then their commission rate increases to 40%:

Easy enough to understand in the real world, but in cell B2 our formula is getting more complex.? If you look closely at the formula, you’ll see that the third parameter of the original IF function (the value if false) is now an entire IF function in its own right.? This is called a nested function (a function within a function).? It’s perfectly valid in Excel (it even works!), but it’s harder to read and understand.

We’re not going to go into the nuts and bolts of how and why this works, nor will we examine the nuances of nested functions.? This is a tutorial on VLOOKUP, not on Excel in general.

Anyway, it gets worse!? What about when we decide that if they earn more than $50,000 then they’re entitled to 50% commission, and if they earn more than $60,000 then they’re entitled to 60% commission?

Now the formula in cell B2, while correct, has become virtually unreadable.? No-one should have to write formulae where the functions are nested four levels deep!? Surely there must be a simpler way?

There certainly is.? VLOOKUP to the rescue!

Let’s redesign the worksheet a bit.? We’ll keep all the same figures, but organize it in a new way, a more tabular way:

Take a moment and verify for yourself that the new Rate Table works exactly the same as the series of thresholds above.

Conceptually, what we’re about to do is use VLOOKUP to look up the salesperson’s sales total (from B1) in the rate table and return to us the corresponding commission rate.? Note that the salesperson may have indeed created sales that are not one of the five values in the rate table ($0, $30,000, $40,000, $50,000 or $60,000).? They may have created sales of $34,988.? It’s important to note that $34,988 does not appear in the rate table.? Let’s see if VLOOKUP can solve our problem anyway…

We select cell B2 (the location we want to put our formula), and then insert the VLOOKUP function from the Formulas tab:

The Function Arguments box for VLOOKUP appears.? We fill in the arguments (parameters) one by one, starting with the Lookup_value, which is, in this case, the sales total from cell B1.? We place the cursor in the Lookup_value field and then click once on cell B1:

Next we need to specify to VLOOKUP what table to lookup this data in.? In this example, it’s the rate table, of course.? We place the cursor in the Table_array field, and then highlight the entire rate table – excluding the headings:

Next we must specify which column in the table contains the information we want our formula to return to us.? In this case we want the commission rate, which is found in the second column in the table, so we therefore enter a 2 into the Col_index_num field:

Finally we enter a value in the Range_lookup field.

Important:? It is the use of this field that differentiates the two ways of using VLOOKUP.? To use VLOOKUP with a database, this final parameter, Range_lookup, must always be set to FALSE, but with this other use of VLOOKUP, we must either leave it blank or enter a value of TRUE.? When using VLOOKUP, it is vital that you make the correct choice for this final parameter.

To be explicit, we will enter a value of true in the Range_lookup field.? It would also be fine to leave it blank, as this is the default value:

We have completed all the parameters.? We now click the OK button, and Excel builds our VLOOKUP formula for us:

If we experiment with a few different sales total amounts, we can satisfy ourselves that the formula is working.

Conclusion

In the “database” version of VLOOKUP, where the Range_lookup parameter is FALSE, the value passed in the first parameter (Lookup_value) must be present in the database.? In other words, we’re looking for an exact match.

But in this other use of VLOOKUP, we are not necessarily looking for an exact match.? In this case, “near enough is good enough”.? But what do we mean by “near enough”?? Let’s use an example:? When searching for a commission rate on a sales total of $34,988, our VLOOKUP formula will return us a value of 30%, which is the correct answer.? Why did it choose the row in the table containing 30% ?? What, in fact, does “near enough” mean in this case?? Let’s be precise:

When Range_lookup is set to TRUE (or omitted), VLOOKUP will look in column 1 and match the highest value that is not greater than the Lookup_value parameter.

It’s also important to note that for this system to work, the table must be sorted in ascending order on column 1!

If you would like to practice with VLOOKUP, the sample file illustrated in this article can be downloaded from here.

Similar Articles	Productive Geek Tips
Using VLOOKUP in Excel Import Microsoft Access Data Into Excel Import an Access Database into Excel Copy a Group of Cells in Excel 2007 to the Clipboard as an Image Share Access Data with Excel in Office 2010	TouchFreeze Alternative in AutoHotkey The Icy Undertow Desktop Windows Home Server – Backup to LAN The Clear & Clean Desktop Use This Bookmarklet to Easily Get Albums Use AutoHotkey to Assign a Hotkey to a Specific Window
Latest Software Reviews	Tinyhacker Random Tips
DVDFab 6 Revo Uninstaller Pro Registry Mechanic 9 for Windows PC Tools Internet Security Suite 2010	Quickly Schedule Meetings With NeedtoMeet Share Flickr Photos On Facebook Automatically Are You Blocked On Gtalk? Find out Discover Latest Android Apps On AppBrain The Ultimate Guide For YouTube Lovers Will it Blend? iPad Edition

• 0 Comments