Last week, I pubished a post about customizing your toolbar in Excel in order to increase efficiency. Today is another very quick and easy tip. I know that many of you use dozens of different excel speadsheets. Some are more common than others though and being able to access them very easily can save some time. When you click on the top left corner of excel 2007, here is what you’ll see:

You certainly recognize the screenshot and you probably know that the recent documents are generally placed in order of last use. The issue of course is that some documents that you use every day could end up not showing up here because you’ve used so many other spreadsheets. What you can do however is click on that little “pin” next to a document.

Once that pin is green, the document will remain on the list no matter what which has saved me time and useless energy.

Do you use this or other productivity tips to increase your efficiency in excel?

Today I wanted to discuss a very simple tip that is very helpful if you’re trying to be very productive can give you a hand. It’s a way to add buttons that are easy to access no matter where you are. Here is what things might look like initially:

You first click on file/options:

Then you can get the quick access toolbars:

For example, I personally would add “paste special values” as follows:

Here is the new result:

It’s a small tip but it really does make things much better, trust me on this!

For many of you, working with large sets of data presents many challenges and today I thought I’d give you one way to make life much easier. Here is an example that probably occurs on a regular basis. Look at this spreadsheet:

It’s quite easy to work with this of course. But what if you have thousands of lines and you end up going further down you’d get:

If you add a few columns, you’ll end up with a problem that you’ve probably encountered in the past. You’re stuck going back to line 1 all the time to figure out which column means what.

There Is A Better Way

In such a case, what you do is simple. If you’re looking to always keep that first line visible, you can select cell A2 as follows:

Then, you’d select the menu folder and click on freeze panes:

As you can imagine, either of these 2 options would work:

As you can see, no matter where I go in that sheet, I will still see that first line (with the column headers). The same can be done for a column, etc.

It is very possible that you have heard about an API at some point in the past. Others might be asking what in the world I’m talking about. An API is an IT term that is basically a protocol that makes it easier to interact with different sets of data. Most big companies such as Google, Twitter, Facebook, Amazon, etc all have their own API’s. Why? There are many reasons but let’s focus on the one I’m most interested in. They want to make their data more easily accessible by outside applications.

For example, seeing data about the weather, search results or twitter updates is all possible through the web. What if you’d like or need to access that info from some other type of program? Obviously, they will not just let you connect to their database and start making queries. Instead, they build a protocol or interface that makes it possible to extract the data. The main benefits are the saved time, the (fairly) easy use, etc. The downsides is that in most cases, heavy use will end up costing you money or being regulated at some point. Twitter is one of the more used API’s in the world and used to be free and unlimited but in recent months, Twitter has decided to charge for those that use over 100,000 queries per day or more (I believe that was the criteria). It’s not something I worry about at this point but it’s certainly worth considering:)

So today, following an example given by Microsoft, I signed up for the Wunderground API, which is free at:

http://www.wunderground.com/weather/api/

Once I had signed up and received my key, I’m all set to start using the webservices function in Excel 2013 to make “better” web queries!

Unfortunately, when I tried, I ended up getting an error in my response so I will need to do further research. I’ll certainly keep you posted as I do intend to have this fixed soon:)

Working with excel functions is not easy to learn and usually requires a decent amount of practice. Every once in a while, I try to publish some examples sent by some of you and here is the latest one that I got:

I want correct formula for following:

I want a result in cell c3 of following
1. if b2 is >= 1200 = “A+”
2. if b2 is >= 976 = “A”
3. if b2 is >= 751 = “B”
4. if b2 is >=601 = “C”
5. if b2 is <600 = "D"

As is often the case, the trick here is not overcomplicating things. You can do one step at a time and get to the answer.

Step #1-Determine if the grade is A+

=if(b2>=1200,”A+”,”zzz”)

It’s important to understand this part. I’m simply looking to see if the grade deserves an A+. If not, I replace it with zzz until I can work out the other conditions.

Step #2-Determine if the grade is A. I start off with the previous formula:

=if(b2>=1200,”A+”,”zzz”)

And I replace “zzz” with a condition that will verify if the grade should be “A”

=if(b2>=1200,”A+”,”zzz“)
=if(b2>=1200,”A+”,if(b2>=976,”A”,”zzz”))

Can you see what I did? I replaced the bold part of the first formula by the bold part of the second. Thus, I can already see if the grade should be A+ or A. I will continue

=if(b2>=1200,”A+”,if(b2>=976,”A”,”zzz“))
=if(b2>=1200,”A+”,if(b2>=976,”A”,”if(b2>=751,”B”,”zzz”)“))

Then one more step:

=if(b2>=1200,”A+”,if(b2>=976,”A”,”if(b2>=751,”B”,“zzz”)”))
=IF(B2>=1200,”A+”,IF(B2>=976,”A”,IF(B2>=751,”B”,IF(B2>=601,”C”,”D”))))

That’s it:) Simple enough?