Archive for October, 2012

Transition From Excel 2003 to Excel 2007

Excel spreadsheets (.xls)

I’ve talked about the upcoming cloud based Excel that will be launching in a few months and I’m certainly excited to try out the upcoming version. That being said, I’m not exactly running the most recent version of excel. In fact, I currently use Excel on a few computers:

-Work computer: Excel 2003
-Home computer #1: Excel 2007
-Home computer #2: Excel 2010
-Wife’s Mac computer: Excel for Mac

This week, they upgraded my Excel at work from 2003 to 2007 (enterprise is a always lagging a bit) and I was reminded of how drastically different those 2 are. Despite already being an Excel 2007 user, I was caught off guard. So today I thought I’d go over a few of the excel options that I looked into to get me comfortable again:)

The big difference of course is that the top menus are completely different (the ribbon menus they are called) and I would argue that more clicks are required and I sometimes feel like I lose time using it. So first off, where can Excel options be found?

First off, I decided to change the color scheme to add a bit of character, see how I did here:

Here is what it looks like:

The critical part in my opinion is finding the excel functions that you use most often and putting an icon on your top bar through the options:

You can add them here:

For example I added the “sort” function here:

Once you are able to add the most popular functions, you suddenly start seeing that Excel 2007’s power makes it a no-brainer upgrade. It’s faster, manages data a lot more efficiently and is just a big upgrade overall.

Have you upgraded to Microsoft Excel 2007? If so, how did it go?

When Do You Give up on a Crappy Job?


“Great jobs, world-class jobs, jobs people kill for — those don’t get filled by people emailing in resumes.” — Seth Godin

Most of us get stuck in some crappy job. It just happens. One day you’re miserable and unemployed. Then someone hooks you up with an interview. Now you’re happy and employed. Some time goes on. Now you’re miserable and employed. How did this happen?

Somewhere along the line the euphoria of finally having a job fades away. You’re stuck with the reality of how much you dread your work and can’t stand your co-workers. You feel stuck. You’re not sure of what to do next. I want to help you here.

When do you know that it’s time to give up on a crappy job?

You have no control over anything.

We all want some control in our lives. We always feel so helpless. It feels great to have some control at work. What if your job doesn’t give you any control at all? Then maybe it’s time you went somewhere that gives you the respect that you deserve.

How much control do you want?

You engage in toxic habits.

I’m not here to judge anyone. There’s nothing wrong with a beer or a cigar after work. We all like to chill out. The problem arises when you feel the need to get completely drunk or engage in any other habit after a day at work. That’s not natural. Your job shouldn’t force you into drinking.

If you find yourself trying to escape all of the time or engaged in toxic habits, perhaps you ought to plan your exit strategy. Your health is priceless. Everything else can be bought.

You get extremely depressed before work.

Once again, your job shouldn’t depress you. We all feel down when a project isn’t complete or if we have bad day. However, it’s not worth getting depressed after work. This transcends into other areas of your life. Do you want to be the guy that’s always complaining about work? It gets really annoying. Your friends love you but you shouldn’t always be miserable.

There’s more potential out there.

Are there other opportunities out there for you? Of course there are. We can all switch careers or do something different. There’s so much potential out there in this world. Why waste one second feeling miserable about yourself?

That’s when it’s time to get out of that crappy job. You owe it to yourself to be happy.

Modifying Small and Big Cap Letters In Excel With Upper, Lower and Proper Functions

Excel function tutorials

If you have any type of spreadsheets that gets data in text form and has to present it in some way to clients, you surely know that in some cases you need to modify how the text is written. There are many different reasons why you’d need to do it. In some cases that I’ve dealt with, I needed users to write down either “Yes” or “No”. Of course, users would write in any of the following ways:


It becomes very difficult to deal with such answers. One way to rectify that is to use excel functions that will transform the text to match the fomat that you’d like. Let’s take the following cases:

-need to use big caps: =UPPER(A2)

-need to use lower caps= LOWER(A2)

Then, you could want the “proper” format which will have first letters in words be caps and other letters be small caps. Here is what it would look like:

As you can see, it’s extremely simple but can prove to be very useful to better manage data that would otherwise be inconsistent.

EXP: Differences Between 2 Dates In Excel

Excel function tutorials

Today I received an interesting question from a reader. He has a list of dates and needed to get the number of weeks and days between the two. Here is what he is looking at:

First off, I added a few more columns that you can see here:

The difference is obviously one minus the other. When you subtract dates in excel, it gives a date format. Ideally, I’d be looking at the number of days so I simply changed the format:

I get a number of days. I need to know how many weeks and obviously I do not want to have decimals and I need to round down in order to have the number of complete weeks. Here is how I’ll do it:


This will round down to the number of weeks:

Then, the number of days will be:

Nb days = nb days – #weeks x 7

In this case:


Then, I can simply add the difference as text by entering the following in G7:

=”The difference is ” & E7 & ” weeks and ” & F7 & ” days.”

You can see the result here:

Not that I want to be “fussy” but since this gives me a good opportunity, I’ll try to adjust text to account for the possibility that the number of days is 0 or 1.

If it is 0, I’d like to only write “X weeks”

If it is 1, I’d like to write “day” instead of “days”

So let’s start with the first condition. I’ll change:

=”The difference is ” & E7 & ” weeks and ” & F7 & ” days.”


=”The difference is ” & E7 & ” weeks” & IF(F7=0,”.”,” and ” & F7 & ” days.”)

And the the final piece, by using a nested if condition:

=”The difference is “&E7&” weeks”&IF(F7=0,”.”,IF(F7=1,” and “&F7&” day.”,” and “&F7&” days.”))

You can see the end result here:

And download the spreadsheet here:)

Managing A Stock Or ETF Portfolio From Google Docs

Google Cloud Spreadsheets

A few weeks ago, I took a look into how you could extract stock prices fairly easy to excel from a website such as Yahoo finance. It’s fairly straight forward. Even easier though is doing this through Google Spreadsheets, the leading cloud spreadsheets solution. There are benefits and downsides but to me it’s a great product to do this. Why?

-Prices are updated automatically!! Google Docs can access data from Google finance if you use the right functions and get live pricing. This enables you to have a live price, live market values and asset allocation, etc.
It’s very easy to use: Once you know the few formulas that you need to use, it becomes very easy to work on the spreadsheet, add positions, etc
It’s Cloud Computing: I’ve written about this but the reality is that being able to access your spreadsheets from any location is a tremendous benefit.

You can see an example of a spreadsheet here:

Try to take a look at any point while North American markets are open (9:30AM-4PM EST) and you will see all of the numbers being updated live! This is something that is simply not possible in Microsoft Excel without adding data subscriptions or subscribing to a service like Bloomberg which is very expensive. You can go see the spreadsheet here:)