3 Excel Features You Haven’t Used Before (And when to use them)

April 15, 2010

The beauty of Excel really is the number of unusual things this tool can do.  When people write the history of analytics and research in the years to come, it won’t surprise us if it is divided into two distinct aeons – B.E- Before Excel and A.E- After Excel.(and boy, are we glad that we are working in the enlightened A.E aeon!)

Here are 3 lesser known functions we have found useful on their day. There are many other similar ones,  that we have never used before. If you know any such , share them in the comments below!

1. INFO – Have you ever walked into the client site and been handed a brand new computer? Not sure how to use it, you breathe a sigh of relief when you notice Excel and quickly begin work there. But try as you might, you can’t access the computer’s control panel or properties to find out more about the machine. Let Excel help you. Want to check how much = total memory is available for use on the system? Click on any cell and type in ‘=INFO(“totmem”). Not sure which OS version you are working on? Try ‘=INFO(“osversion”). And once open, don’t know where this file is from? Try ‘=INFO(“directory”)’

2. Date and Timestamps- Working on a long financial model over many days and weeks- and want to remember when you last corrected each row? Just go to the end of the row and Click CTRL +; The day’s Date will be stamped right there. Move to the next column and click CTRL+SHIFT+; the current system time will be stamped. This function is also very useful for teams working in different shifts. Ask your evening shift colleague to leave this time stamp on every row he tampers with. Come in next morning, and you know that all the rows with timestamps after 6:00 PM have not been changed by you.

3.  Custom Views: You know how it is. You are creating a detailed budget plan, with many assumptions, some of which you don’t necessarily want your boss to see. You have that giant ugly worksheet with a million calculations, 5 different regions, and  a hundred assumptions that you have created. Then you hide some rows and columns, format some others, size the page up into a single- page print layout and print this version for him. “Great work”, he says, and asks you to share just the Kolkata figures with your Kolkata team mate. You filter the relevant information, take another print out and are just about to fax it when you notice an error in the calculations.

Don’t fret. Use Custom Views. Click on View>Custom View> and Create one View called Boss which has only the rows that you want to show your Boss, and another called Kolkata, created after filtering for the region. Work on the entire unhidden, unfiltered sheet and whenever you need to rush for a print out, just select the relevant custom view!

%d bloggers like this: