Author Archives: karen

How to extract a URL from a hyperlinked image, graphic, or icon on Excel

When you copy and paste data from the Web onto an Excel spreadsheet, you sometimes end up copying and pasting images, graphics, or icons that were originally hyperlinks. To make the data usable, you may want to extract just the hyperlink and get rid of the image, graphic, or icon. You have two options. First […]

Posted in Macros | Leave a comment

Excel formula to change foreign accented characters to regular non-accented characters

Need to change foreign accented characters to regular non-accented characters? I just encountered this problem today. I have a list of hundreds of thousands of city names from all over the world, and I needed to generate a list of these names without the foreign accented characters. Here’s how to get this done in just two seconds. […]

Posted in Extracting words | Leave a comment

How to capitalize the first word in a string (or the first letter in a cell) using an Excel macro instead of an Excel function or formula

Although you can use a Microsoft Excel function or formula to capitalize the first word in a string (or the first letter in a cell), there are times when you may want to change the case of the text in the same cell the data was entered, programmatically. Just follow these steps: 1. Hit [ALT]+[F11] to […]

Posted in Macros | Leave a comment

How to uppercase text using an Excel macro instead of the UPPER function

Although you can use the =UPPER() function or formula to lowercase all the text in a cell, there are times when you may want to uppercase the text in the same cell the data was entered, programmatically. Just follow these steps: 1. Hit [ALT]+[F11] to open the Visual Basic Editor 2. Hit [ALT]+[I]+[M], or just click “Insert” > […]

Posted in Macros | 2 Responses

How to lowercase text using an Excel macro instead of the LOWER function

Although you can use the =LOWER () function or formula to lowercase all the text in a cell, there are times when you may want to lowercase the text in the same cell the data was entered, programmatically. Just follow these steps: 1. Hit [ALT]+[F11] to open the Visual Basic Editor 2. Hit [ALT]+[I]+[M], or just click “Insert” […]

Posted in Macros | Leave a comment

How to capitalize all words using an Excel macro instead of the PROPER function

Although you can use the =PROPER () function or formula to capitalize all the words in a cell, there are times when you may want to capitalize the text in the same cell the data was entered, programmatically. Just follow these steps: 1. Hit [ALT]+[F11] to open the Visual Basic Editor 2. Hit [ALT]+[I]+[M], or just […]

Posted in Macros | 1 Response

Formula to find next Monday’s date

Here’s a formula that you can use to generate next Monday’s date: =TODAY()+8-WEEKDAY(TODAY(),2)

Posted in Functions | Leave a comment

How to make Excel stop calculating automatically

Is your Excel file calculating and recalculating all the formulas automatically, causing your entire computer to freeze? That’s likely because your Excel application’s “Workbook Calculation” is set to “Automatic” instead of “Manual”. Excel 2003: Tools > Options > Calculation > Calculation > Manual Excel 2007: Office button > Excel options > Formulas > Workbook Calculation […]

Posted in General tips | Leave a comment

How to display two Excel sheets side by side on two monitors in Windows 7

If you use Excel a lot and spend most of your day toggling across multiple Excel files, then having a dual monitor setup will help you tremendously! In fact, a New York Times study has shown that having an additional monitor increases productivity by 20 to 30 percent. However, the challenge is that some applications […]

Posted in General tips | 120 Responses

How to make VLOOKUP search from the bottom to the top of the list

Normally, VLOOKUP searches data from top to bottom. But when you have a long list of data and you want to get the latest data at the bottom of the list (instead of the older data at the top), you can’t use VLOOKUP because it always returns the first one it sees from the top, […]

Posted in Functions | 1 Response