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!

Hoor, our Bangalore Excel trainer, shares some little known text functions with you

FIXED Function:

The FIXED function rounds a value to specified precision and then converts the rounded value to text. The function uses the following syntax:

=FIXED (number, decimals, no _commas)

The number argument supplies the value that you want to round and convert to text. The optional decimals argument tells Excel how many places to the right of the decimal point that you want to round. The optional no_commas argument needs to be either 1 (if you want commas) or 0 (if you don’t want commas) in the returned text.

For example, to round to a whole number and convert to text the value 1234.56789, use the following formula:

=FIXED (1234.56789,0,1)

The function returns the text 1,235.

REPT function:

The REPT function repeats a text string. The function uses the following syntax:

=REPT (text, number _ times)

The text argument either supplies the text string or references the cell holding the text string. The number_times argument tells Excel how many times you want to repeat the text. For example, the following formula:

=REPT(“Bora”,2”)

Returns the text string BoraBora.

VALUE function

The VALUE function converts a text string that looks like a value to a value.

The function uses the following syntax:

=VALUE (text)

The text argument either supplies the text string that you want to convert or it references the cell holding the text string. For example, to convert the text string $123,456.78 — assume that this isn’t a value but a text string you can use the following formula:

=VALUE (“$123,456.78”)

The function returns the value 123456.78.

Excel Excels!

December 18, 2009

Interesting article last week about the Top 10 Trends in Analytics, Business Intelligence and Performance Management for 2010 here:

http://www.enterpriseirregulars.com/5706/the-top-10-trends-for-2010-in-analytics-business-intelligence-and-performance-management/

Over the next few weeks we will be exploring each of these trends in greater detail for our student community here.  But the one that interests all of us at ATI most is the prediction that Excel will continue to provide the dominant paradigm for end-user BI consumption. With the launch of Excel 2010 with enhanced charting, collaborative and conditional formatting features, Excel is more powerful than ever before. The BI world can truly be divided in to B.E (before Excel) and A.E (after Excel) – and you don’t want to know how companies managed before Excel!!

Excel has many features that even old hands don’t know about, and fidgeting with its ‘Help’ feature is as good a way as any to spend a quiet day in the office.

For today, we leave you with this tiny featurette that we can bet most of you didn’t know about. Did you know that you could remove duplicates from a long list of entries using the Advanced Filter feature?

Just click on Data > Filter> Advanced Filter and select the column with duplicated entries in List Range. Select Copy to Another Location and click on ‘Unique Records Only’. Now, go to “Copy to” and select the starting point where you would like to see your unduplicated list.

And voila!

You don’t even need to sort the rows before doing this!

If you have any other interesting Excel Tricks that you would like to share with us, write to us. We are always willing to learn!!