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).

I’ve started using and discussing Excel 2012 and while I still have a lot of exploring to do, one thing that I quickly noticed was the fact that one of the main benefits is the fact that it is cloud based. I’ve written about the benefits of cloud based software and how much it has helped Google Docs catch up. In short, two of the major benefits are:

-being able to access your software from any location
-Ability to share documents

From what I understand, unlike Google docs, you cannot have multiple users editing a document all at once. But being able to handle the cloud drive portion is still a very important part of the Excel 2012 software. Once you sign up for the free trial, you instantly also get access to SkyDrive, which is Microsoft’s own cloud-based drive, which competes with Dropbox, Google Drive and Applee’s iCloud among others. Once you get access to Skydrive, you can (and should) install it on your pc. When that happens, you will see it in your explorer:

Then, any file that you save on that part of your drive will automatically be made available through Skydrive on the web:

Ideally, you also have your excel and other office documents in that area in order to:

-have access to them from anywhere
-ability to share them easily

Then, you can log into your Skydrive and access the file, even if that computer does not have excel installed on it, which is certainly a nice benefit. Also, any changes that you make will be reflected in the updated file on your computer.

Is Installing Excel Even Necessary?

I think it’s a valid question but I’m assuming that some of the functions in Excel 2012 do not work in a web based version (such as web queries, certain macros, etc) only work on desktop,,I would certainly hope that over time those missing features are added but I guess time will tell. My main thoughts are that unless the online version of Excel is extremely solid, there will be little use to having Skydrive rather than competing services. There’s no doubt that it requires some adapting but I think it’s a great thing overall.

Have you started using Excel 2012? If so what are your thoughts? And have you started integrating SkyDrive?

Today, I decided to finally go ahead and start testing Office 365, especially the new Excel 2013 version! There is currently a free trial available from the Office website here! Here is what I could see:

Then I simply selected the home version (should be more than enough..from what I understand the main difference is the number of users):

Then, I had to select my country (hopefully your own country appears on this list):

And then I could select my free trial:

Unfortunately, as you can see, in order to try it out I need:

-a Windows/Hotmail user (not a big problem)
-credit card information (this is a bit disappointing, I’d love for the free trial to not require this but this is the new model.. no upfront cost, just a monthly fee:

Since Excel 2013 is cloud-based, I did not expect to have an installation to do but it was the case as you can see here:

After about 2 minutes, I got to the first screen:

I got the presentation which was fairly well done, explained some of the benefits of using Excel 2013.. I’d say the main ones are:

-can be clud-based
-comes with SkyDrive (cloud based program similar to Dropbox or Google Drive)
-easier to share spreadsheets with others, edit with multiple users, etc
-free Skype minutes, etc..

I was also impressed by the number of templates when I logged in for the first time:

I will be testing a lot more in the coming weeks and will keep you updated on my thoughts, new functions, etc.

Have any of you given it a try? If so, how do you like it so far?

For reasons that I won’t get into, I had to buy a new laptop and Office this weekend. I hesitated about buying Office 2010 knowing that Office 2013 is perhaps weeks away from being officially launched. That is when the sales guy told me that not only is 2010 offered at a special low price but it comes with a free 1 year upgrade to Office 2013!

How It Works

Once I install and activate my Office 2010 license, I can ask to be notified as soon as the Office 2013 version is ready for download.

Why A 1 Year Upgrade?

From what I understand, the new office version will not really have an upfront cost. Instead, it will charge a monthly fee to use. I personally don’t have much of an issue with it, it will provide more flexibility, make it easier to do upgrades, etc. It’s not as if I was using an Office version for years anyway. I’ll be very curious to see what the monthly fee will be but I’ve heard it would be of $5 or so which sounds very reasonable to me.

So within a day or two, I will be installing Office 2010 and will hopefully get more information about the free upgrade at the same time. For now, I took the “Office Home and Student 2010” version so I’ll also be interested to see if there are significant differences between the two.

Are You Anxious To Upgrade?

Personally, I’m a little worried that changes might be significant in the new version (it did take me a while to adapt from Excel 2003 to Excel 2010) but I do still think that having a cloud-based will offer significant benefits.. especially if all of my macros and advanced function based spreadsheets do end up working