Saturday, February 18, 2012

The Excel Macro

So I thought I'd share a very useful tool we could all use to improve productivity if you ever work with excel.  A lot of people who use Excel don't use the programming side, and you do need some programming experience to get adept at it.  However, there are some useful tools you can use even if you aren't a programmer.  If you plan to never touch excel, then you can feel free to stop reading here.  But I think these techniques can be really helpful if you ever need to sort or make sense of a lot of data.  This is probably most useful if you are in some kind of corporate role and need to organize excel data.

Anyway, the focus of this post is on the excel macro.  First of all, what is a macro?  It is essentially a program that takes an input and gives an output.  It is used by programmers to make work less tedious and less prone to error.  If you have several sheets of data in a similar format and need all of them changed (which isn't difficult at all, but quite tedious, especially if you have several sheets to which to apply the same changes), a macro would be perfect for the job.

A more specific example, let's say you have five sheets with a list of the months and how much money you spent in each one over the past 10 years.  And let's say you want to highlight the month with the highest expenditure in yellow and the one with the lowest expenditure in red.  And let's say you have a different sheet for you and your five friends.  This is probably an example that would never happen to anyone but let's just go with it for now.  What you can do is follow the video below and start recording your actions on your current sheet.  You would select all the data, sort it by the expenditure amount highest to lowest, highlight the top one yellow, resort the data by expenditure amount lowest to highest, highlight the top one red, and then finally resort it by the date.  You could then end the macro recorder there, move onto the next sheet, run the macro recorder, and voila, the exact same actions are performed in mere seconds.

This is a relatively small example but imagine if you had to make a lot more changes than just highlighting two numbers.  Programming in general is a great skill to learn to help make you more efficient at your white collar job or on your own free time if you ever sort through information, and it can be especially helpful if you are still a college student trying to analyze data for a class.  It is also very helpful going through market data which you can collect off the internet.  The producer of this video below also has several other topics on the macro recorder which I find very helpful and easy to understand.  I would definitely recommend checking it out.

No comments:

Post a Comment