I use new superstars gmail labs feature a lot, to mark reader mails that I need to followup, comments I should respond to, ideas that are interesting, bill mailers that should be paid. The default setting allows any of the 4 superstars to be added. while trying to change my mail signature, I have realized that now you can add more super stars to your account with simple drag & drop. Pretty cool feature. Read more.

Thanks gmail team

Earlier on Google: Mute conversations to reduce mailbox clutter
Google maps adds directions by public transport
Create your budget tracker using google docs

• 0 Comments

Passion, excitement, inspiration or love - whatever you choose to call it, is the key reason we do remarkable things. Take a minute break here, think about the most significant moments in your life.

What is common in all of those moments?

I am sure your answer will be Passion or excitement, love, inspiration.

I want to talk about the latest Batman movie - The Dark Knight, the movie has a production budget of $185 mn [boxofficemojo], but excited millions of people all over the world. There are 130,000 people rating the movie at 10 on 10 (excellent) out of the 194k people who voted at IMDb. The movie grossed $ 390 Mn in the first 3 weeks. Compare this how many companies, institutions, businesses spend millions of dollars without exciting single individual.

Have you excited anyone today? or got excited?

Creating time value of money tables in excel, if you work involves financial calculations like leasing, cash flow analysis then this article on tvm calcs blog can be helpful.

Filter by color, font or active cell’s value in excel 2007 Ron tweaks Excel 2007’s cell based filtering options using VBA to come up with more ways to filter.

Use charts to validate your data tables, Jorge suggests a simple technique to quickly validate data tables, especially if you have lots of data in them. Just fire up a line chart with the data and see if you can find any anomalies, go back and correct your tables if needed. This is very useful if you constantly import data from other sources and work with it.

More on adding target lines your bar charts, Jon at PTS blog provides more alternatives for adding target lines to bar charts to show target vs. actual performance. This can be useful if you build dash boards and need to show more details.

Do you have any excel tips / links, share them with our readers, drop a comment.

Also see:

• Cool Infographics of the week [July 31]
• Excel Links around the web - [July 29]
• Excel Links Around the Web [July 22]
• Cool Infographics / Charts of the Week [July 24]

• 2 Comments

Blog reader Richard asks through email:

I’m a non-programing user of Excel. I import stock prices, using what is called ( I believe) a dde link, into my spreadsheets,from a trading program.
I would like to add a feature to my spreadsheets that, like conditional formatting, alerts me to a price change in a cell, but with a sound or .wav file, instead of a color change of that cell.
Something like: =if b4 =>10.00, play .wav
Is there something you can help me with? I am using office 2003 pro.

You can write a vba macro that can create a windows media control (active-x) component and use it to play a .wav (or mp3) file when a certain event happens. But it would be both time consuming and difficult to implement.

The simple solution is of course “beep” whenever a certain condition is met, in this case, the stock price getting changed.

Thankfully, we have a vba command to do just the job, Beep, will play a standard windows beep sound when called. So all you have to do is, create a user defined function shown below:


Function beepNow()
Beep
End Function


And use it in your excel sheet like: =IF(C2<>C3,beepNow(),”"). So when the data gets dynamically refreshed, you would hear a ding if C2 is not equal to C3.

What would use beepNow() for?

• 1 Comment

Amul has one hell of a branding strategy, they take whatever is happening in politics, entertainment, sports etc and turn the situation in to a billboard to tell their butter / jam / milk product story. Almost every week they do this, so restlessly that when something big happens, customers tend to think how Amul would portray it. Above you can see how they have turned the joker character from the latest batman movie in to Amul milk boy

This tells a lot about why big marketing budgets, huge ad campaigns are not always necessary, what you need is a genuinely interesting way to interact with your customers, tell them your story and engage them in a conversation, even if you are selling something that is soo commoditized like butter.

Check the Amul hits from 2008.

Earlier on Amul: Amul is an agile brand, Best Indian Outdoor Advertising - Amul Billboard Ads

PS: Amul is an Indian Milk Co-operative based out of Anand, Gujarat. They sell, milk based products, it is an interesting company if you wish to study how co-operatives can be successful. Do visit Amul’s website.

• 1 Comment

A big hello to readers from Lifehacker, Delicious and countless other sites. Thanks for making the Excel can be exciting - 15 fun things you can do under 5 seconds post so popular. I am overwhelmed by the visits, comments and love shown.

A quick introduction to this blog and what you can find here:

Pointy Haired Dilbert - Chandoo.org is a site about Excel tips, Technology tidbits and Business insights.

The blog has around 620 articles in topics like excel, technology, advertising, business, MBA life in India and photography.

Every weekday I post 1-2 articles that can make you more productive, provide you interesting bite sized info you can use.

Join the 700+ reader community by subscribing to RSS feed. Or get updates through email, every weekday. Get in touch with me: chandoo [dot] d [at] gmail [dot] com. Or know more.

Read some of the most popular posts here to get started:

Be an Excel Conditional Formatting Rock Star
11 very useful excel keyboard shortcuts
73 Free Designer Quality Excel Chart Templates
Tracking mutual fund / Stock portfolios using Excel sheets
Become a Dashboard Ninja with Bullet Graphs
Getting RSS feeds to your Excel Sheet - Learn how to
How to count words in a cell using excel

• 0 Comments

Every Friday I post a photo taken in the recent days, to share with my readers, to get some feed back so that we all can become better photographers, learn from others comments (of course, most of the time it is me who gets the brickbats).

I go running every other day, 3 miles (~5k). And this trusty ipod shuffle is with me, to motivate at those hard moments when hitting the sidewalk becomes really painful. What motivates you to run?

• 0 Comments

Who said Excel takes lot of time / steps do something? Here is a list of 15 incredibly fun things you can do to your spreadsheets and each takes no more than 5 seconds to do.

Happy Friday

1. Change the shape / color of cell comments

Just select the cell comment, go to draw menu in bottom left corner of the screen, and choose change auto shape option, select a 32 pointed star or heart symbol or a smiley face, just wow everyone

2. Filter unique items from a list

Select the data, go to data > filter > advanced filter and check the “unique items” option.

3. Sort from Left to Right

What if your data flows from left to right instead of top to bottom. Just change the sort orientation from “sort options” in the data > sort menu.

4. Hide the grid lines from your sheets

Go to Options dialog in tools menu, uncheck the “grid lines” option to remove gridlines from your worksheets. You can also change the color of grid line from here (not recommended)

5. Add rounded border to your charts, make them look smooth

Just right click on the chart, select format chart option, in the dialog, check the “rounded borders”. You can even add a shadow effect from here.

6. Fetch live stock quotes / company research with one click

Just enter the stock symbol (MSFT, GOOG, AAPL etc.) in a cell, alt+click on the cell to launch “research pane”, select stock quotes to see MSN Money quotes for the selected symbol. You can fetch company profiles in the same way. Learn more.

7. Repeat rows on top when printing, show table headers on every page

When you are on the sheet view, just hit menu > file > page setup, go to the last tab, specify “rows to repeat”. You can “repeat columns while printing” as well from the same menu.

8. Remove conditional formatting / all formatting with one click

Just go to Menu > Edit > Clear > All to remove all the formatting from selected cell / range.

9. Auto sum cells with one click

Select a bunch of cells and click on the Sigma symbol on the standard tool bar. Alternatively you can use Alt+= keyboard shortcut.

10. Find width of a column with formula, really!

Just use =cell("width") to find the width of the column to which that formula cell belongs. Width is returned as the nearest integer.

11. Find total working days between any two dates, including holidays

If you work on project plans, gantt charts alot, this can be totally handy. Just type =networkdays(start date, end date, list of holidays) to fetch the number of working days. In the above sample you can see the number of working days between New years day and September first of this year (labor day).

12. Freeze Rows / Columns in your sheet, Show important info even when scrolling

Select the cell diagonally beneath the row / columns you want to freeze (for eg. if you wan to freeze row 1&2 and columns A&B, click in C3), go to menu > window and click on freeze panes.

13. Split sheets in to two, compare side by side to be more productive

Just click on this little vertical bar on the bottom right corner of the sheet (see below) and drag it to create a vertical split. You can do the same way for a horizontal split as well

14. Change the color of various sheet name tabs

Right click on sheet and select “Tab color” option to change the worksheet tab colors. Group them with similar colors if you have lot of sheets, it looks nice.

15. Insert a quick organization chart

Click on menu > insert > diagram to open the above dialog, just select the organization chart option, enter node values and you have a pretty organization chart. Alternatively learn how to create org charts in excel.

So what do you say now? Isn’t Excel Exciting?

• 32 Comments