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.

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!

Excel – Count text values

It is easy to sum or count a group of numbers in Excel. What is harder is summing or counting a group of words. For example, let’s say you have a column of data that contains “Yes” and “No” and you want to count the number of “No’s”

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 Special.  Paste 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.

Purchasing MS Office for Cheap

If you were to buy MS Office in Staples or Target, it would be very expensive.  However, there are a few places where you can get MS Office for far less. 

Microsoft Home Use Program – Because I work for the state, I have access to this program.  On the site, you can enter your work email and Microsoft will let you know if you are eligible for this program.  If you are, you can get MS Office Professional Plus for $9.95.  This includes Access, Excel, PowerPoint, Word, InfoPath, Sharepoint, Outlook and OneNote.  You can download the software to your computer, or you can purchase the DVD at an extra cost.  Either way, it is a tremendous bargain. 

Your college or University – If you are a current college student, you can probably get MS Office for a reasonable price through your school.  Take advantage of the tuition you are paying! 

If you are not a student, and your workforce does not participate in the Microsoft Home Use program, you can buy the “Office Home and Student 2010,” which has Excel, OneNote, Word and PowerPoint for $119.  This is enough for the average computer user.