Archive for August, 2011

Excel Macros – How To Build Excel Macros

Excel macros

Last week we took a look at what excel macros are, why using them makes so much sense and some of the ways they can be used. Today, we will look at how to create your first macro. It can be intimidating at first but it’s actually much easier than you could imagine. There are two different ways to create macros in excel. The more advanced one is of course to write the code directly, which is much more challenging when you are a beginner and we do not recommend starting off that way. The second way is to record your macro. What does that mean?

Recording a macro simply means executing a series of tasks and the macro will then do exactly the same thing.

Example: Imagine that a store that sells tennis items wishes to send a summary of the orders from the past month to their 4 clients. Here is the spreadsheet which includes all orders from that month, you can see it here and/or download the spreadsheet here.

In order to do that I will simply start recording by clicking on:

Then, I proceed, so I started by filtering the columns, by selecting them and then clicking on:

Then, I select client #1:

I then copy the columns, create a new spreadsheet and paste those columns. I then saved the file to: C:/client1.xls

I closed that file and did the same thing for the 3 other clients.

Once that was done, I simply “stopped the recording” by clicking on the “stop button”

The result is that I now have a macro that can reproduce those exact steps. If you want to either see it or start, you would simply go back to tools/macro and click on step into:

Here is the code that I see:

Please do not panic. I know that it looks much more complicated than you were hoping for. Believe me, it will get much easier. First off, it’s important to know that code written when recording macros looks much more complicated. Also, the goal is simply to learn a few lines of code every time, not to understand it all from day one.

You can download our spreadsheet here and try the macro for yourself, you will see how it works.

Murphy’s Law When Starting a Business

Business & entrepreneurship, Career

As I wrote in a previous post, I recently left my job to start my own business.  The first few weeks have been exhilerating, liberating, and yes, often-times quite frightening.  I have also learned all about business-style Murphy’s Law.   Here is some advice from the first few weeks of my business, along with some personal anectodes to illustrate my experience thus far and what dangers lurk out there when starting a business.

You Will Underestimate the Expenses

Prior to starting my business, I had worked on my anticipated budget extensively.  I reviewed the anticipated start-up expenses over and over again.  I was convinced that the old adage “people always understimate startup costs” would not apply to my business.  Well…guess what…

I’m now $1,500 over-budget.  For a small business, with $5,000 startup costs overall, that’s significant.  Murphy’s Law came into play–and has hurt my ability to stick to a budget.  Here are some examples:

  • I was counting on being able to use my MacBook Pro as the company computer.  Unfortunately, one piece of important software that I need does not run on a Mac.  Therefore, I had spring for a new laptop.  Even though I bought the cheapest new laptop I could find (around $330.00 including tax), that’s still a big expense I was not anticipating. 
  • I destroyed some equipment while moving.
  • Some items that I thought I could borrow from others did not pan out as expected.

Other times, I simply failed to accurately estimate the costs associated with a specific purchase.  I never realized it would be $25.00 per month for an extra telephone line.  Even worse, I am beginning to think I may need to hire a receptionist to be taken seriously in my field. 

Also, it’s only natural that we forget to include small expenses in our budget.   My budget had just about all the big-ticket items listed.  But I forget small things such as a stapler, staples, tape, and a wireless router.  These “small/inexpensive” items add up, and before you know it, you have spent hundreds or even thousands of dollars more than you initially expected to.

Things Will Go Wrong

My business is–like most professional businesses–very reliant on Word documents.  Which means the word processing (something to do with the formatting) issue that suddenly came about this week couldn’t have come at a worse time.  Spending hours on the telephone with”technical support’ is not how

 I invisioned spending my precious time during the start-up phase of my business.  Worst yet, the issue still isn’t fully resolved. 

Plenty of other issues have arisen as well.  Outside vendors have taken longer than anticipated or promised to help me get various systems in place.  Time sucks have occurred at almost every stage of the business.  Spending hours building furniture is a reality–but not something you think about when dreaming of your business.

Not Everyone Will Be Supportive

I understand that I am taking a huge risk in starting my own business.  I left a steady job, where I got paid a consistent paycheck each week and worked with fair and decent people.  That said, some people act as though I am questioning  their entire way of living.  Sometimes the digs will be focused on my relative youth.  Other times, the digs will be more general in nature.  Either way, the negativity from some friends, colleagues and family members is unexpected and not very helpful.  Although my network has overall been very supportive, stand ready for some unexpected neighsayers early on when starting a business. 

You’ll Be Your Own Worst Enemy

As you sit there, having turned your entire life upside down–and wondering if you made a huge mistake–you will likely start to doubt yourself.  You’ll become agitated over the telephone not ringing.  If you’re anything like me, you’ll likely envision yourself in a debtor’s prison or the like–or at the very least seen as a major failure.  Again, these negative thoughts are detrimental–and must be kept at bay before they negatively impact your business or chances of success. 


I can now say with great certainty that Murphy’s Law applies when starting a new business or company.  What else could one expect?  After all, it’s not easy to leave “The Rat Race.” 

Have you ever experience “Murphy’s Law”–Business Style?  How did you get through the initial start-up period–and all of its pitfalls? 





Excel Macros – What Are Excel macros

Excel macros

What Is An Excel Macro? difference with functions

On this blog, we have written quite a bit of material that concerns excel functions such as vlookup, sumif, and even combinations of functions such as nested functions (if and or functions combined together). When you start getting a good knowledge of these functions and how to use them, you start to feel as if excel is incredibly powerful… and it is. The possibilities are almost endless.

That being said, functions are only a small part of the story. If you work on excel and use functions, you are tapping into a bigger part of excel than most users. However, there a lot more ground to cover. Excel macros can range from the easiest operation that you could have easily performed with functions but can also include interacting with other files, other windows applications, complex operations with variables, etc. Basically, any task that you perform in excel can be replicated with excel macros. Sounds powerful? It truly is.

-What Does It Look Like In Excel?

When you add functions to a spreadhseet, it is very obvious . One big difference when building Excel Macros is that when you look at the spreadsheet, you will not necessarily know that some macros exist in the spreadsheet. There are a few ways that you can find out. First off, users often insert buttons on their spreadsheets that make it easier to start macros, here is an example:

Another way to see is to look through the menu:

Finally, if while you are in the excel sheet you press on: ALT+F11, this will give you a better view of the document. You can see some of what is going on in the background. Macros are usually found in “modules”, that are “attached” or part of the excel document but they could be found in the background of the sheets themselves. Here is a small view of the structure of my document.

-What Are Some Of The Possible Ways To Use Them?

There are a million (or more) different ways that macros can be used and I think there are many reasons why you would use macros. Some of the possible uses are more complex rules that would be difficult to replicate, combination of different excel fonctions, advanced usage of utilities such as filters, but also trying to either get data from external spreadsheets, saving data to other drives, etc. We will be going through some of the things that can be done over the next few weeks and hopefully you will also be asking any questions that you might have regarding the use of macros.

-Why Use Macros? What Are The Benefits?

Macros are obviously not perfect but they do offer many advantages.

-Executing complex operations that would not be possible with regular functions or would require a lot of time.

-Clean Spreadsheet: Since macros are built in the background, once they are done, it becomes much easier to run without fearing of erasing a function, etc. It is also very easy for outsiders to use the macros.

-Speed: Running macros can make complex operations that take hours for a regular individual be done

-Easy to improve/modify: Well written code can become very easy to modify and improve which is not necessarily the case in heave spreadsheets

-Excel Macro Downsides

Honestly, there are few downsides to using macros, one of them would of course be that they take more to build, especially when you are a beginner. As well, it can be more difficult for a user to understand how the spreadsheet and macro is reacting compared to a more standard and simple spreadsheet. Other than that, I honestly cannot think of big downsides.

-Next Week

In our next post, we will take a look at how to build your first macro, how it should be done and we’ll go from there!

The Dual Expenditure Phenomenon

Personal finance

Duel Splurges With Your Significant Other

When it comes to personal finances, duel splurging tradeoffs between you and your significant other can be a huge mistake. But what do I mean by making “tradeoffs” with your significant other? I mean this:

“You can buy X if I can buy Y.” As in, “you can buy the new Adele CD if I can buy the Lincoln Lawyer on DVD.” What would have been one expense, at its worse, has now become 2 separate and generally equal expenses.

How many times has this occurred in your relationship? Your girlfriend wants to go out to dinner with her friends so, with your night now open, you decide to go out to the bar with your friends? Combined, it’s now going to be a very pricey night for you and your significant other–provided of course that you’re commingling funds.

Of course, if you have a ton of disposable income, then perhaps it’s not an issue. For the majority of people, myself most certainly included, however, it is a big deal. This type of relationship financial trade-off can actually be quite damaging over a period of time. My wife and I have a combined six-figure student loan debt. In other words, the absolute last thing in the world we need is “entertainment” costs eating up money that would be better spent paying off our debt or being saved.

The night out at the bar and the dinner for your significant other can end up costing hundreds of dollars that could have been used in a much more productive manner.

When you have very little disposable income, you’re more likely to be extremely frugal. In that situation, it actually makes you more likely to barter. The reason being that if you actually want to do something–you’ll feel really guilty about doing so without your significant other being able to enjoy in the process as well.

For example, if I say I’m going out to the bar with my friends, I’ll almost always next say: “and you should go out with your friends that night, too.” And although my wife likely wasn’t even thinking of going out with her friends, she’ll then likely say “yes,” because we don’t get out that often and it beats sitting around doing nothing on a weekend. But with the double expenses (and of course, this all works both ways), it is now 2x as bad a personal finance decision for a couple with the limited financial means that we possess.

The problem, of course, is that our “bartering” often leads to twice the expenses.

I am writing about this issue, because I also see it all the time with my friends. Even growing up, I would notice my parents “barter” their way to incurring 2x expenses. On at least one prior occasion, I have witnessed a friend purchase a present for their significant other while out splurging on themselves–perhaps to make himself feel better about how much money he had spent on himself that day.

This problem of compounding expenditures deals more with psychology, perhaps, than anything else. The feelings of guilt and fairness that enter into the decisions are not always practical or sensible. On the contrary, they are often ill-advised.

By addressing this phenomenon with your significant other, then perhaps you can both work towards limiting its deleterious affects.

That’s not to say that it’s wholly unacceptable to engage in such behavior, but rather that acknowledging the action as a true phenomenon in your relationship can help your relationship, and your bottom line.


Have you ever noticed this phenomenon in your relationship? How have you coped with the double expenditure phenomenon in your marriage/relationship?

I Just Quit My Job


I just quit my job.

Wait…let’s elaborate further:

I just quit my job in one of the worst economies.  Ever.

But here’s the kicker: My new job is asking for 10-15 hours a day from me, with no guaranteed pay.  Plus, my new boss just might be the biggest jerk I’ve ever worked for.  I am, of course, referring to myself.

Going It Alone

After a long and drawn out game of internal ping-pong (or was it beer pong), I finally decided to quit my job.  My goals in life have always been entrepreneurial in nature, and I decided that if I didn’t go for it I might always regret it.

Here’s a brief list of what I’m facing:

1) No clients to start out with.

2) I have six-figure student loan debt.

3) I’ve only been in my field for one year.

4) I’m “only” 28 years old.  (man is my generation sort of limp–wrist-ed).

5) Did I mention I have no clients?

Here’s a list of what I have going for me:

1) A spouse with a currently secure job.

2) No children.

3) A freelance writing business to help me bring in some income.

4) Perhaps partial insanity (coin-flip as to whether that’s a benefit or a detriment to starting your own business).

Do I Believe I’ll Succeed?

I know there’s no guarantees in life.  Perhaps the odds are stacked against me.  Maybe I’ll wind up losing everything.  But something inside tells me it’s still the right decision.  That starting my own business is an itch I’d have to scratch one way or the other.

Sometimes people say that you can tell if a decision is right or wrong for you by imagining finality.

For instance, imagine you broke up with a significant other you’re on the fence about.  If you feel good with that finality, then it’s probably the best decision for you.

If, however, the thought of losing your significant other (with finality) hurts, then maybe the relationship is worth fighting for.

With this decision, I tried that exercise, but I still couldn’t figure it out.

But now that I’ve gone through with the decision to quit my job, I feel the happiest I’ve been in months. If I end up a failure then I’ll simply know not to trust my gut so much in the future.

Family and Friend Reactions

Reactions from family and friends have ranged from supportive to concerned.  Most people (who are still lucky enough to earn money), do so through a steady paycheck.

Having to eat what you hunt is a lot harder than being fed steadily, and (ideally) in proportion to your value.

The problem is, that always made me personally feel uncomfortable.  It made me feel like I was relying on somebody else.  As a confirmed and admitted control freak, that just wasn’t ideal for me.

Being the family dog might be preferable to the wolf, and you’re sure as hell a lot less likely to starve, but perhaps a lot of this really just comes down to individual personality.

Does Recession Make Self-Employment More or Less Risky?

I’ve had a difficult time wrapping my head around this concept.  I’m hoping in the comments that you’ll share your thoughts as to this issue.

We have all heard (ad nauseam), that “no job is secure in the current economy.”

So, does that mean we’re better off creating our own job rather than risk being laid off?  Alternatively, of course, does the present weak economy simultaneously makes it more difficult to create or sustain a profitable business?

Self-employed or “working for the man.”  Is this all just like asking if you’d rather be shot than poisoned?  Either way, there is a possibility you may die.

Sorry….That Was Morbid

Meanwhile, the optimistic part of me keeps making bold but unsubstantiated claims, like: “This is great timing; with the bad economy you—the new and inexpensive upstart–will be much more palatable.”  And I wish that was true.  I truly wish I was palatable.  But the truth is, that would be like arguing you were the highest jumper in Atlantis.  Eventually, if everything sinks….

Sorry…That Too Was Morbid

At the end of the day, perhaps the greatest lesson I can give about quitting one’s job is this:

Don’t blog about it.


About the Author: I am a twenty-something freelance writer and personal finance junkie.  Please check out my new online personal finance encyclopedia: What Is Personal Finance?