Microsoft Excel 2010 – Text functions to Automate Data Cleansing

As a data analyst, I deal with a lot of data.  Most of my time dealing with data is spent doing data cleansing.  Data cleansing involves taking the text you are given and converting it into a more usable form.  There are several Excel functions that will automate the data cleansing process.  Knowing these functions can save you hundreds of hours of time in your data cleansing.
Continue reading

Microsoft Excel 2010 – Conditional Formatting

If you follow the stock market like I do, you may notice that Google Finance has a neat way of showing trends.  It puts the stocks that gained in value in green, and the stocks that lost in value in red.  This is a very helpful way to display data to show trends.

Conditional Formatting is an Excel tool that allows you to create this kind of effect.  Conditional Formatting is not new to Excel 2010;  it has been around since Excel 2003.  This is an incredibly helpful tool, but one that can easily be overlooked by a novice.   Today, I am going to show you how to put conditional formatting into practice!
Continue reading

Microsoft Excel 2010 – Paste Special

One of my favorite commands in Excel is Paste Special.  Let’s say you set up a formula in cells B1:B5 based on the values in cells A1:A5.  Then you want to paste the results into cells E1:E5.

If you simply do a Copy > Paste, the values pasted in will be totally different.  This is because the formula you had set up is no longer referencing the values in cells A1:A5.  Now, it is referencing the values in cells D1:D5.

We can correct this by doing a Paste SpecialPaste Special will copy the values rather than the formulas.  The values that were calculated in cells B1:B5 are now moved over to cells E1:E5.
Continue reading