Getting Unique Values Only

Often times when you are working with Excel, you want to reduce a list of items so that only the unique instances are listed. In other words, if, within a column or list an item happens to be duplicated, you want that item to appear only once. Maybe you have a mailing list, for example, and fear that some customers may be entered more than once. Or perhaps you’ve been collecting emails and are now ready to send out promotional or “Thank you” emails, but don’t want to spam people with multiple messages.

Luckily, Excel makes it very easy to do this, saving you from yourself. In fact, there are two ways. One is simpler and built in to Excel’s multiple functionalities. The other is a bit more difficult and less intuitive, but is more flexible and dynamic. I explain them both to give you flexibility in dealing with these types of business problems. For my example, I use a simple list of animals where a couple of them (cat, bird) are each repeated.

Note: This tip is for Excel 2007, but is applicable, with a tweak or two, with other versions of Excel.

Method 1:

  1. Select data from which you want to retrieve only the unique values. Underline, or otherwise differentiate, the header so that Excel can recognize it as such.
  2. Under the “Data” Ribbon, select the “Advanced” Option.
  3. Under Action, select “Copy to another location.”
  4. The “List Range” should be populated with the data set selected in Step 1
  5. Choose the same range for the “Criteria Range” (I normally copy and paste) from above (the “List Range”)
  6. Check the box next to “Unique Records”
  7. Hit the “OK” button Read more of this post

Data Tables

Performing what-if analysis in business is very common, crucial even. What if a new project is delayed by 3 months? Or what if the company is unable to secure financing at the projected cost of capital? Successfully answering questions like these often means the difference between a business finally turning the corner and becoming profitable and, well, it not. But this type of analysis is also helpful for individuals. Far from overly complicated but seldom used by consumers, tools that allow for thoughtful consideration of important personal finance decisions should be used much more often then they are. They can help one understand how much more a car will cost over time or what term and interest rate combination is required to make sure the monthly payment fits into the family budget. They can even reveal how your credit scoreis helping or hurting you.

Data Tables, one of the features in Excel's What-If-Analysis Package, is a very useful tool for comparing.

So let’s dig in. How would you perform this type of analysis? What tools would you use? Microsoft Excel, of course! Until recently, I’d approach this—or any other 2 dimensional, two variable scenario– by building a grid in Excel with all of the intersection values. For example, I might look at an auto loan with six possible interest rates, 5% to 10%, and five different possible terms, ranging from one to five years. To fill out the grid, I’d utilize the power within absolute and relative references to allow easy copy and filling across all rows and columns of the grid. Read more of this post

It’s Absolute(ly) Relative

If I had a dollar for every time someone asked me “what the heck are those dollar signs for?,” I’d be a millionaire.  Well, anyone worth their weight in gold as an Excel user should really understand what those pesky dollar signs are used for.

Most of us know how to use relative references

It’s really pretty simple.  The inclusion of dollar signs ($) in a formula changes at least part of the cell reference from relative to absolute.  Let me explain.  You see, Microsoft Excel is a very, very powerful tool. So powerful, in fact, that more times than not, it tries to help you get your work done faster and more effectively, even attempting to save you, the user, from yourself.  Like auto-numbering and spell-check in Microsoft Word, this type of “help” is what we’ve come to expect with Microsoft products.  So when you copy a formula for use somewhere else in your worksheet, which I’m sure you’ve done, Excel assumes that you want to keep the same relationship.

For example, say if you have three columns of data that represent daily transactions for your store.  Column A represents quantity sold, Column B lists the price per item, and Column C is the product of the two (A*B).  Because of this intuitiveness built into Excel, you need only enter “=A2*B2” into column C (assuming the first row is your column headings).  You can then copy and paste or fill-down the formula. And, as I’m sure you know, Excel will continue with the relative positions (A3*B3, A4*B4, etc.). Read more of this post