One of the more exciting features of the latest Microsoft Excel version was the ability to add apps that would make it much easier to gather data in order to manage it more easily in Excel. You’d think that finance would be one area where Microsoft could deliver. The data required is fairly simple (most investors need metrics such as price, dividend info, P/E ratio, etc), the company has most of that data through Bing Finance and has a lot of experience dealing with financial spreadsheets.

Microsoft Failed

If you look at the app offering:

You will quickly see that the response has not been good so far. The rating currently stands at 1.5 stars (out of 5). There seem to be a lot of bugs, crashes, it does not work well for international stocks.

You Need To Take Action

There are other ways to get this done. One easier option is to build cloud-based spreadsheets using Google docs which does provide a decent infrastructure based off of Google Finance data.

The main alternative is building your own excel spreadsheet, getting your data to web queries or API’s and managing the entire process either manually or by building a macro based spreadsheet. It will certainly take a bit longer to build but will be much more flexible than whatever you’d find.

I’ve written several posts about doing this and would be more than happy to help out more if that would help.

Good morning to all of you! As may of you know, I’ve been looking into ways to improve the way I can help everyone improve their excel skills. More than ever, in a tough economy, being able to distinguish ourselves through Excel can be key in not only finding and keeping a job but getting a step ahead on the competition.

I did end up writing an excel ebook which has now helped hundreds of users. I also make a point to ask every single reader how the ebook has helped and it could get better. I’m looking into many different options in order to help you improve your skills. I have pretty much confirmed the importance of using examples, but there are other things I’d like to know.

-Do you feel like having a broad training course would help or do you prefer using it “on demand” for specific functions when needed?
-How helpful would having videos with demos of the functions be if at all?
-Would having a more structured online excel training course bring value?
-In such a structure, would having “assignments” be helpful? More work but more feedback, etc?
-Would you want some kind of exam that would test your knowledge at the end?

I’m looking at building something in early 2014 and will likely be reaching out to some volunteers who would like to get early access to the material! I’d probably build 2-3 different courses because it seems unlikely that those looking for “beginner’s advice” will be looking for the same kind of info as more advanced users so trying to build a one “cover-it-all” excel course doesn’t seem like the best of ideas.

I’d also love to hear from you if you’ve tried some other courses that are offered online. What did you like of their format and their content? What could be improved?

Over the past few weeks, I’ve been getting an increased number of questions regarding the use of pivot tables. Believe me, I understand. As much as I know how useful and powerful pivot tables can be, I resisted using them for years. I never felt comfortable using them and always looked for alternative methods. Thankfully, I’m getting much better at it. Today, I wanted to look over a few basics because compared to other types of excel functions, using pivot tables is not as easy to grasp initially. What is a pivot table and when should you use it?

Pivot tables are a way to express lengthy tables of data in a simple way. It helps to draw conclusions about the data, etc. In almost all cases, there are alternative ways but pivot tables are very easy to use once you’re used to it.

What do you need? Simple.

-A table with multiple columns that have headers at the top (it simply will not work without headers).

Ideally, you start by finding the place where you’d like to place the pivot table. It can be on a separate sheet (which works very well actually) or anywhere on an existing one.

Here is an example of a table with different stocks and relevant info about each one:

So then I went to a different sheet and clicked:

Insert/PivotTable

Then, I was asked to select a table:

As you can see, I am now able to see a basic table and must now go to work. Then I would select on the right the columns that I’m looking to add. You would drag the categories to the appropriate location at the bottom. For example, if I’d like to know how many stocks of each category I have, I would do the following:

And have this result:

I could also get the market cap per category by adding it to the values section.

As you can see, the result is very interesting:

I hope this helps a bit. I do encourage you to get a table of data and start playing with it, it’s the best way to get yourself familiar with the power of pivot tables. You can also download my spreadsheet here

I’ve done a couple of similar examples but here is one question I got a few days ago:

I am trying to write a formula to pay a percentage up to a certain amount and then increase the percentage once a rep exceeds that amount. Ex: Rep brings in $2,000.
Pay 50% for the first $600
Pay 60% for the next $600
Pay 70% for the next $600
Pay 80% for everything over $1,800.

How will I get it done? Let’s start from the beginning. If my sales number is in cell C6, then I’d start off by checking if the sales is above $1800. If it is, then I’d simply add:

50% of $600+60% of $600 + 70% of $600 to the 80% of everything over $1800. In short it would be:

=IF(C6>=F9,1080+(C6-F9)*H9,1)

As you can see, it works well for that kind of number:

But would not work for a number under $1800:

So I will then work my way to that category:

=IF(C6>=F9,I6+I7+I8+(C6-F9)*H9,IF(C6>F8,I6+I7+H8*(C6-F8),2))

Of course, I must also adjust for numbers in the second category, I will do so with the following:

=IF(C6>=F9,I6+I7+I8+(C6-F9)*H9,IF(C6>F8,I6+I7+H8*(C6-F8),IF(C6>F7,I6+H7*(C6-F7),3)))

And finally, I can replace the “default” here. If the number is not in category 2, we can assume that it is in category 1 (less than $600 in this case) so I simply replace 3 by:

=IF(C6>=F9,I6+I7+I8+(C6-F9)*H9,IF(C6>F8,I6+I7+H8*(C6-F8),IF(C6>F7,I6+H7*(C6-F7),C6*H6)))

And here you go, it works and easy to adjust. You can modify the percentages, levels, etc.

You can download the spreadsheet here!