Excel Yogi's Analyst Pulpit

Remove the ‘ Apostrophe from Excel Cells

Joseph Harris — Thu, 02 Apr 2009 04:30:13 +0000

Many applications that export to Excel, export the data with the apostrophe character at the front of the string. One way of removing this character is to use the clean() function:

If in cell A1 you have: ‘output
Then in cell B1 type =clean(A1). The result will be: output

Joseph Harris
Excel Help

Bookmark It

Find the Nth Largest or Smallest Number - Excel

Joseph Harris — Mon, 23 Mar 2009 17:31:57 +0000

Use the Large and Small functions is Excel to find the Nth largest number in a list.

For example:

=Large(A2:E2,3) will return the 3rd largest value in the data set.
=Small(A2:E2, 2) will return the 2nd smallest value in the data set.

Joseph Harris
Excel Help

Bookmark It

No related posts.

Delete Duplicate Rows Using Advanced Filter - Excel

Joseph Harris — Mon, 23 Mar 2009 17:03:19 +0000

There are many ways to remove duplicate rows in Excel, in this articles I will give you a quick and simple method of deleting duplicates using the Advanced Filter.

Assume you have data that contains multiple duplicate rows…

Select all the data that you would like to test for duplicates

Go to Data=>Filter=>Advanced Filter

Select the “unique records only” check box, then OK

Right click highlighted area and choose copy

Paste into another location

The pasted results will be only the unique rows of data from your original data set.

Joseph Harris
Excel Help

If you found this article helpful, buy me a cup of coffee!

Bookmark It

Vlookup the Last Occurence of an Item - Excel

Joseph Harris — Wed, 18 Mar 2009 18:20:07 +0000

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 name George will return Smith, What if you want to lookup the last George listed? The solution is to use the lookup function:

=LOOKUP(2,1/(A1:A6=A7),B1:B6)

Where:
A1:A6 = the lookup range
B1:B6 = the data to return
A7 = The condition (The item to lookup)

For more advanced lookups like returning the 2nd occurrence of George, you can try the VlookupPlus Excel Add in.

Joseph Harris
Excel Help

Bookmark It

#DIV/0! Division by Zero Error in Excel

Joseph Harris — Tue, 17 Mar 2009 20:47:57 +0000

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 than zero, enter =if(B1=0,”",A1/B1).

Joseph Harris
Excel Help
Excel Vlookup Add in

Bookmark It

Multiple Condition Vlookup Function Add-in - Excel

Joseph Harris — Mon, 16 Mar 2009 21:51:36 +0000

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 Vlookup features by a mile.

The VlookupPlus add-in allows you to use the following functions:

vlookup2() Look up a result based on two criteria / conditions
vlookup3() Look up a result based on three criteria / conditions
vlookup4() Look up a result based on four criteria / conditions
vlookupinstance() Returns the nth occurence of an item
vlookupall() Returns all results for the chosen condition

Detailed Examples:

Based on the image above, check out some example results using the VlookupPlus addin:

Function: =vlookup2(A2:E10,5,”Larry”,”Meyer”) Result: Mitchell
Function: =vlookup3(A2:E10,5,”John”,”Smith”, 265) Result: South
Function: =vlookup4(A2:E10,5,”John”,”Smith”, 212,”black”) Result: Northwest
Function: =vlookupinstance(”John”,A2:A10,D2:D10,3) Result: Red
Function: =vlookupall(B2:C10,”Smith”) Result: 212, 265, 212

The addin comes with simple step by step install instructions that everyone can follow.

The Excel VlookupPlus Add-in is available below for a donation of .98 cents. The download will begin automatically after donation is processed.

Amount	Description
$0.98	VlookupPlus: Microsoft Excel Add-in, compatible with Excel versions 2000 and later

Thanks,
Joseph Harris

Bookmark It

Scrolling Excel Charts - Scroll Bar Graphs

Joseph Harris — Fri, 13 Mar 2009 19:05:32 +0000

There are many times when you may have a lot of data that does not look right when graphed all together. 300 data points is probably too many for a small chart. So what is the solution…scrolling charts! I will show you in this article how to create a scrolling chart without using dynamic ranges. This is a more simple technique for most Excel users, but for the more advanced, I would recommend you check out this technique.

To begin with, I have made the scrolling chart workbook available for download here:

Scrolling Charts

Look at the picture below and set your workboook up in the same fashion. In column B enter your time periods…Could be days of the week, months, years or other. In column C enter your data. Column A will contain what I call “labels”. The labels are a count of your time periods.

Next, place a scroll bar on your worksheet (available on “forms” toolbar, view=>toolbars=>forms). Right click the scroll bar and choose “Format control”. On the control tab enter 1 for the current value, min value = 1, max value = number of labels, incremental change = 1, page change = 10, cell link = $D$10.

Cell D1 will contain the result from the scrollbar. This is the key to making this method work.

In D3 type = D1, in D4 type =D3+1 then drag the D4 formula to the bottom of your data. In columns E and F write vlookup formulas to lookup the D column on the table A3:C17. For example, the E3 formula is =VLOOKUP(D3,$A$3:$C$17,2,FALSE) and the F3 formula is =VLOOKUP(E3,$B$3:$C$17,2,FALSE). These formulas can simply be dragged down. See the workbook for more info.

The next step is to create a column or line chart using the data in columns E and F. Do not select all of the data or there will be nothing to scroll through. In the example workbook, I selected E3:F11.

That is it! As you scroll, the number in D1 should increase. and the chart should scroll towards the last available date. I added the trend line to see what the trend was on the moving 9 month range.

Joseph Harris
Excel Help

If you found this article helpful, buy me a cup of coffee!

Bookmark It

Excel Tutorials - Microsoft Excel Video Professor

Joseph Harris — Fri, 13 Mar 2009 18:57:49 +0000

While compiling videos for the Excel tutorials section of ExcelYogi, I came across Excel Video Professor. After reading about it, I decided to see if it was any good, so I ordered it for free here:
Free Video Professor.

The shipping was very fast, I ordered the Excel training on a Thursday and received it on Monday. The training came with three CDs, three books and 2 handy “quick guides”.

The Excel training consisted of three lessons:

Lesson One
Understand, customize and manage the Excel environment
Move through a workbook using the worksheet tabs
Changing Excel options and managing shortcuts
Working with formulas

Lesson Two
How to create, format, edit and print spreadsheets
Learn to manage multiple workbooks and worksheets simultaneously
Determining what type of chart to use based on the context of your data
Hide data on worksheets

Lesson Three
Merge and format cells
Create, populate and edit databases
Use the AutoFilter to filter specific information within a table, or create a custom filter
And more.

I was pleasantly surprised at the flow of the videos and the amount of information covered. All versions of Excel are available. It is the easiest Excel tutorial I have seen and for a price of zero, it is hard to beat. You can order the tutorial I reviewed in this article here:

Video Professor

Joseph Harris
Excel Help

If you found this article helpful, buy me a cup of coffee!

Bookmark It

No related posts.

Removing Characters from a String in Excel

Joseph Harris — Tue, 03 Mar 2009 22:08:40 +0000

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 remove the hyphens from the cell. To do this, select the cell and press ctrl+H. The replace tool will pop up. In the “find what” line put your -. in the “replace with” line, you have several options. To remove them and them entirely, leave “replace with” blank. This will give you 6105552904. To leave the space but remove the hyphen you would enter ” “.

Joseph Harris
Excel Help

If you found this article helpful, buy me a cup of coffee!

Bookmark It

#VALUE! Error in Microsoft Excel

Joseph Harris — Tue, 03 Mar 2009 21:43:43 +0000

The #VALUE error is an error that is seen almost as often as #N/A. In short, the VALUE error occurs when a formula refers to a cell that contains a different than expected format. In this article, I will discuss the main causes of #VALUE! and some possible solutions.

The top 3 causes of #VALUE! error are:

1. Creating a formula that refers to a cell that contains a data type that cannot be handled.
2. Pressing enter after entering an array formula.
3. Entering a range when a single cell reference is required.

To correct the #VALUE! error, the first step should be to trace the error. You can do this by selecting the cell and then choosing “trace error” on the formula auditing toolbar (view=>toolbars=>formula auditing, pre-Excel 2007). This will show you what cells are originating the error. After you have identified the cells causing the error, look at the formula in your error cell. is it trying to perform data manipulation on the cells shown in the trace error? If so, make sure all of the cells are of the same data type. The most common cause of the VALUE error is performing a mathematical task on cells that contain text.

To address the second most common cause (pressing enter after an array formula is entered) simply press ctrl+shift+enter after the formula is complete rather than enter. This is how all array formulas must be entered.

The third common cause of the #VALUE! error is a simple fix as well. Rather than entering C2:C20 in your formula, perhaps your formula only supports a single cell reference such as C2.

If you decide your error values need to be left as is but you do not want them to appear when you print, then you can remove them from the print file by doing this:

1. Select the worksheet with the error you don’t want to print.
2. On the File menu, click Page Setup, and then click the Sheet tab.
3. Under Print in the Cell errors as box, click , — , or #N/A.

Joseph Harris
Excel Help

If you found this article helpful, buy me a cup of coffee!

Bookmark It