I had read about the fact that creating charts was now much easier in the new Excel version so today I decided to do something quite straightforward. I downloaded closing prices for a few dozen days for 3 different stocks, Apple (AAPL), Amazon (AMZN) and Google (GOOG) and this what I had in my spreadsheet:

Then, I simply selected that data:

Clicked on “insert” and then the type of chart I was looking for:

And here is the result:

WOW! Now of course, I’d need to change the title and perhaps a few details but overall, it’s very close to what I was looking for and took me about 30 seconds… This is certainly one of the more interesting aspects of Excel 2013.

I continue to dive into Microsoft Excel 2013 looking for new features, functions, differences and in the past few days, I’ve been diving into a new aspect that is truly fascinating. What is it? Quite simply, Microsoft Excel has added an incredible number of templates. How do you access them? It’s quite simple. You start off by simply opening Microsoft Excel and then click on:

File/New/Templates

Then, you can simply select the template you’d like to try or search for it:

You can get more details about the actual spreadsheet then press on “create”:

You can then get a great step ahead with a spreadsheet that includes formulas, charts, apps (as you can see on the left, which needs to be downloaded):

The best part is that there are dozens if not hundreds of different template categories which can give you ideas or a start no matter what you’re trying to build. I’ll certainly be looking into some of these templates to discuss the Excel 2013 opportunities.

Have any of you tried to work with templates? How has your experience been so far?

As I continue to look into Excel 2012, there are a few changes or improvements that were made to some of the more common functions. Today, I decided to take a look into the rank function which is very used in excel. First, let’s create a simple situation. Suppose that you have a list of a few hundred students and their grades in a final exam. Then, you’re looking to get the top 5 students. In my example, I’ll only have 7 students for simplicity purposes. You can see my list of students here:

Then, I’m trying to use the “rank” function to determine the top few students. Remember that my list could have a few hundred or a few thousands students.

In Excel 2012, the rank function was split into two:

Rank.EQ

Rank.AVG

The main difference is that when two lines have the same “number”, they will react differently:

Rank.EQ = if 2 students have the “top grade”, they will both have a rank of “1”. You can see the result here:

Rank.AVG = if 2 students have the “top grade”, they will both have the average rank. You can see the result here:

I guess your preference would depend on what you’re trying to do but I’d generally prefer using the “Rank.Eq” function. Still, it’s not perfect. Just look at what happens when I try to use the vlookup function to look for the the top 5:

As you can see, I have a problem. What I generally try to do is to avoid “ties” by adding a fraction:

This way, I get no “ties”. That being said, it’s not perfect, but here is my result:

The most important part is understanding the difference between the 2 “Rank” functions in Excel 2012 and how you can use them.

Don’t hesitate to ask if you have any questions!

One of the most promising parts of using Excel 2013 is the web-based interface that is made available in the same way that Google Docs currently offers it. I did know of some limitations but have gotten a better idea of those as I started using and exploring Excel 2013 a lot more.

Today, I opened a spreadsheet and got 2 sets of warnings:

1-This spreadsheet is currently open on another spreadsheet so you can only view, not edit the file

2-Then I saw the following window:

Basically, as most of you readers would know, I am a regular user of web queries and also VBA macros. I’ve written about both and use both quite a bit in my different spreadsheets so it’s very disappointing to not be able to use them. Also, I know that some formulas from add-ins such as Bloomberg do not work (which is to be expected) but while I can open spreadsheets with “calculations turned off” in Excel, the web-based software does not seem to offer that option.

The Solution

For now, Microsoft recommends installing the Excel version on all devices but that is not always possible obviously, and not Excel 2013 does not work on all types of machines. To be fair, the Office365 licence can be installed on 5 PCs or Mac computers.. which will certainly cover my (and most people I know) household.

Check it out here

In The End

I can certainly live with these limitations but I’ll be very disappointed if I ever get a sense (or hear directly) that Microsoft does not intend to get these working. The whole point of moving to a web-based software is for the experience to be seamless no matter where I’m connecting from. That is not yet the case. I do understand that it would probably not be easy to do in the first version but hopefully Microsoft continues working on this.If that is the case, I will continue to be a big believer in Microsoft’s direction with Excel 2013 as a cloud based software will clearly make my life easier and diminish risks (such as losing my files).