Archive for the ‘Excel Quick Tricks’ Category

Vlookup the Last Occurence of an Item – Excel

Posted on March 18th, 2009 by Joseph Harris

The title of this post is slightly misleading. We will not be using vlookup to return the corresponding result of the last condition, but rather we will be mimicking vlookup to pull the last item. Consider the following data: George Smith Tom Thompson George Kenny Dan Jackson Sally Richards George Daniels A vlookup of the [...]

#DIV/0! Division by Zero Error in Excel

Posted on March 17th, 2009 by Joseph Harris

The #DIV/0! error happens when the denominator (the number on the bottom) of an equation is zero. Many times you will want to display a 0 or NULL rather than the #DIV/0! error. Below is a simple way to accomplish this. Rather than entering =A1/B1, use =if(B1=0,0,A1/B1), if you want a NULL to appear rather [...]

Multiple Condition Vlookup Function Add-in – Excel

Posted on March 16th, 2009 by Joseph Harris

The question we are asked most often by ExcelYogi readers is how can I use vlookup with multiple criteria or conditions. There are several solutions but some can be pain staking and unrealistic. Knowing that the Excel world is missing this feature, Excel Yogi has created a VLookup addin that will extend the built in [...]

Removing Characters from a String in Excel

Posted on March 3rd, 2009 by Joseph Harris

It is often necessary to replace characters within a string in Excel with other characters. Or you may want to remove some characters entirely. There are several ways to do this but by far the easiest method is to use the built-in replace tool. For example, you have the following in a cell: 610-555-2904 You want to [...]

Counting Visible Rows After Autofilter in Excel

Posted on March 2nd, 2009 by Joseph Harris

Typically when you use autofilter, the task bar at the bottom of your Excel workbook will say: “5 of 10 records found”. The five is your visible rows. In some situations the records found indicator will not be displayed on the indicator. This can happen when there are many complex formulas in your filtered results. [...]

Up and Down Trend Arrows for Scorecards in Excel

Posted on February 23rd, 2009 by Joseph Harris

For quick and easy up and down trend arrows in your dashboards and scorecards there is a simple solution…Marlett. If you do not know about this trick, you are probably unfamiliar with the Marlett font in Excel. Marlett was created by Microsoft for Windows and is used as the user interface font in many Microsoft applications. [...]