All articles with 'udf' Tag

Even faster ways to Extract file name from path [quick tip]

Published on Oct 24, 2012 in Excel Howtos, VBA Macros
Even faster ways to Extract file name from path [quick tip]

The best thing about Excel is that you can do the same thing in several ways. Our yesterdays problem – Extracting file name from full path is no different. There are many different ways to do it, apart from writing a formula. Learn these techniques to be a data extraction ninja.

1. Using find replace
2. Using text to columns
3. Using UDFs

Continue »

Excel Links – After a long time edition

Published on Oct 24, 2011 in excel links

Long time readers of Chandoo.org know that we have a regular featured called as “Excel Links“, where I share some interesting Excel articles published around the web. This used to be a weekly feature, then became a monthly thing and later once a few months thing. As I had a hectic weekend, when I got […]

Continue »

Interactive Dashboard in Excel using Hyperlinks

Published on Jul 20, 2011 in Charts and Graphs, Learn Excel, VBA Macros
Interactive Dashboard in Excel using Hyperlinks

Last week we learned how to create dynamic hyperlinks in Excel. Today, I want to show you something even cooler. An interactive dashboard based on hyperlinks, like shown above.

Isn’t it impressive?

Well, to create something like this, you don’t need a degree in advanced cryogenics. You just need a bunch of data, a chart, a one line macro code and some pixie dust (go easy on pixie dust).

Continue »

10 Excel Formula Myths – Busted!

Published on Jun 7, 2011 in Learn Excel
10 Excel Formula Myths – Busted!

Many of us start using Excel to keep track of something. And along way, we realize that Excel has a powerful feature called formulas, using which we can automate a lot of things. BOOM! Before we realize, we are in the thick of VLOOKUPs and SUMIFs.

But, along way, we also pick up a few bad habits or believe a few myths. Today, lets bust 10 Excel formula myths that we hear often.

Read this post to learn what these myths and why you should not trust them.

Continue »

Are You Trendy ? (Part 3)

Are You Trendy ? (Part 3)

In the final post of the Are You Trendy? series we will look at the use of Excel Charts and associated Trendlines for trend analysis and I will give you a free tool (Normally valued at $200, I wish) which will allow interactive assessment of a Charts Trendline Y value for any X value.

Continue »

Excel Links – My First International Excel Workshop Edition

Published on Jan 17, 2011 in excel links
Excel Links – My First International Excel Workshop Edition

Stage is almost set for my first international Excel workshop. That is right. I am doing a physical excel workshop on Intermediate & Advanced Excel at Maldives between January 23 and 27, 2011. I feel quite excited to do this.

While I derive immense pleasure and learn lots of new things by running Excel School, there is one nagging problem. It is an online program, so the scope of physical interaction with students is limited.

Doing a physical class is a great way to meet new people, gather material for new content, get ideas, learn new things and get challenged. And that is why I am looking forward to do my workshop in Maldives next week.

If you would like to join this workshop: Please call Mr. Guru Raj, Training Manager at IIPD, Malè. His number is +960 7625338. (Workshop agenda)

Because I will be busy with the workshop next week, I will not be able to post much on the blog. I have requested Hui, our guest author to keep you all engaged. So expect some delicious stuff from him while I am away.

Continue »

Track Your Mutual Fund Portfolio using Excel [India Only]

Published on Dec 28, 2009 in excel apps, personal finance
Track Your Mutual Fund Portfolio using Excel [India Only]

Excel is very good for keeping track of your investments. Due to its grid nature, you can easily create a table of all the mutual fund holdings and monitor the latest NAVs (Net Asset Values) to see how your investments are doing. A while back we have posted a file on tracking mutual funds using excel. Today we are going to release an upgrade for that file.

Read the rest of this post to understand how this template works and download the free template.

Continue »

Get cell comments using Excel Formula

Published on Sep 3, 2009 in Learn Excel, VBA Macros
Get cell comments using Excel Formula

Excel has a very useful feature called “cell comments” using which you can add comment to a cell. This is a very good way to gather remarks and review comments when a workbook is shared with colleagues and others. But what if you have typed a ton of cell comments and now want a way to extract them and do something with that data?

Continue »

29 Excel Formula Tips for all Occasions [and proof that PHD readers truly rock]

Published on Aug 24, 2009 in Excel Howtos, Featured, Learn Excel

It is no exaggeration that knowing excel formulas can give you a career boost. From someone starting at the long list of numbers, you can suddenly become a data god who can lookup, manipulate and analyze any spreadsheet.

So when our little excel blog hit the 5000 RSS Subscriber milestone, I celebrated the occasion by asking you to share an excel formula through twitter or comments with rest of us. And boy, what an excellent list of formula tips you have shared with us all.
Here is the complete list of entries for the twitter formula contest.

Continue »

Networkingdays() an improved version of networkdays formula

Published on Jun 9, 2009 in Excel Howtos, Learn Excel, VBA Macros
Networkingdays() an improved version of networkdays formula

We all know that networkdays() an extremely powerful and simple excel formula can help you calculate no. of working days between 2 given dates.

But there is one problem with it. It assumes 5 day workweek starting with Monday to Friday. Not all countries have workweek from Monday to Friday.

This got me thinking and I ended up writing a user defined formula (UDF) to calculate working days between 2 given dates with any criteria. This will be good for calculating payrolls for temporary workers, offshore partners and of course people working countries where Saturday or Sunday or not usually holidays.

Continue »

Clean up Incorrectly Formatted Phone Numbers using Excel

Published on Sep 30, 2008 in ideas, Learn Excel
Clean up Incorrectly Formatted Phone Numbers using Excel

In our Utopian imaginations all the data would have been standardized and shareable across systems and people. But alas, the reality is totally different. We seldom get data in the format / way we desire it to be. In other words, the ingredients are all there, but for us to prepare the dinner, you must […]

Continue »

Handling spelling mistakes while searching your data using excel

Published on Sep 25, 2008 in Analytics, ideas, Learn Excel
Handling spelling mistakes while searching your data using excel

Spelling mistakes are a thing of day to day carporate life. Most of the data in spreadsheets is entered by people and hence prone to having spelling mistakes or alternate spellings. For eg. a person named John could have been spelled as Jon. And when John calls you back to confirm his reservation and you […]

Continue »

Microcharting in Excel – 7 Alternatives Reviewed

Published on Sep 5, 2008 in All Time Hits, Charts and Graphs, Featured, ideas, Learn Excel
Microcharting in Excel – 7 Alternatives Reviewed

With each passing day the amount of information contained in a single spreadsheet, slide, document is growing. Thanks to demanding bosses, clients and colleagues, we are now supposed to provide all the relevant information in as much less space as possible. This is where micro charting or light weight data exploration has become a rage. […]

Continue »

Conditionally play sounds when a cell value changes

Published on Aug 4, 2008 in hacks, Learn Excel

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, […]

Continue »

Extract URLs from an excel spreadsheet cell using UDFs

Published on Jul 23, 2008 in hacks, Learn Excel, technology

Often my work involves processing web page data in excel sheets. This includes extracting the hyperlinks from cell contents. There is no formula for extracting hyperlinks though, you can right click on cell and choose “edit hyperlink” to see which address the cell is linking to. But that is a tedious process especially if you […]

Continue »