Pointy Haired Dilbert - Chandoo.org

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 are planning on using the hyperlink for something.

Here is a handy user defined function in VBA for getting hyperlinks from a spreadsheet cell:


Function getURL(forThisCell As Range) As String
'VBA UDF for getting URLs from a cell if any
retVal = ""
If forThisCell.Hyperlinks(1).Address <> “” Then
retVal = forThisCell.Hyperlinks(1).Address
End If
getURL = retVal
End Function


Bonus tip: You can create hyperlink on a cell using “hyperlink()” spreadsheet function. The syntax is simple. =hyperlink("http://chandoo.org/wp","Pointy Haired Dilbert") will create a link in the cell to this blog.

While trying to spell check one my sheets I have learned this cool trick to fetch real time stock quotes without any webqueries or vba or anything.

First enter the stock code in a cell (this works for US stocks only), for eg. AAPL for Apple, MSFT for Microsoft etc. Then ALT+CLICK on that cell, this will open “Research task pane” on the right side of the screen. The screen should look something like this:

Dont worry if you dont see the stock quote, that could be because your research type is set to either “all research books” or “thesaurus”, just click the drop-down and select “MSN Money Stock Quotes” - 2nd option from last (Excel remembers your selection, so next time you alt+click on a company code it automatically shows the stock quote) Once the quote is displayed in the research task pane, just click the “insert price” button to get the quote inserted in to your worksheet. Simple eh?

Now, if you want to track a bunch of company quotes, just create a simple macro to do the alt+click on each of the company code cells and you have a real time quote tracking terminal built into your excel sheet. Just go wild

Bonus tip: If you want a company profile data for a particular stock (like company address, phone number, exchange codes, last year revenue) select the “Thomas Gale company profiles” instead.

Please note that both this options work Excel 2003 and above.

Also read: Create stock / mutual fund portfolio tracker in excel using web lookup queries

Imagine you have long list of data and you need to quickly identify which of the items are unique. You can use Advanced Filters to do this.

Just select the list of items you want to filter, go to menu > Data > Filter > Advanced filter. You will see a dialog box like this:

Thats all, when you click ok you will see unique items of the selected list. Quick, aint it?

If you want to see all items, go to menu > Data > Filter > show all.

Often we work with large data sheets and it becomes tedious if you want to compare data / formulas etc. You can try split (menu > window > split or alt+w+s) to break the sheet in to two. But there is another cool thing you can do, open a new window for the spreadsheet. Just press Alt + w + n (menu > window > new window) to open the same spreadsheet in 2 windows. Any changes you make in one window are immediately visible in another window(s).

Bonus tip: you can do the same thing with WORD and POWER POINT as well, cool eh?

Excel concatenate() is seriously crippled, it can add 2 or more strings together, as long as they are supplied as separate parameters. This means, when you have a range of cells with text which you want to add up to create a large text, you need to write an ugly looking biggish concatenate() or use ‘&’ operator over and again.

I felt bored enough the other day to write a better concatenate(), one that can accept a range as input and output one text with all the contents of the input range. What more you can use this to delimit the input range with your own favorite character.

For example, if each of the 7 cells in a1:a7 have “a”, “b”, “c”, “d”, “e”, “f”, “g”, if you want to add all of them up using concatenate you would have to write concatenate(a1,a2,a3,a4,a5,a6,a7) which can be painful if you are planning to do this over a large range or something.

Instead, you can use concat(a1:a7) by installing the UDF (User defined function) I have written. Its nothing miraculous or anything, it just does the dirty job of going through the range for you. If you want to delimit the input range with a comma just use concat(a1:a7,",") to get the out of a,b,c,d,e,f,g Just download the concat() UDF excel add-in and double click on it to install it. If you are little weary of installing UDFs / Macros from third parties, copy past the below excel code in a new sheet’s VB editor and save the sheet as an excel addin (.xla extension)


Function concat(useThis As Range, Optional delim As String) As String
' this function will concatenate a range of cells and return one string
' useful when you have a rather large range of cells that you need to add up
Dim retVal, dlm As String
retVal = ""
If delim = Null Then
dlm = ""
Else
dlm = delim
End If
For Each cell In useThis
retVal = retVal + cell.Value + dlm
Next
If dlm <> “” Then
retVal = Left(retVal, Len(retVal) - Len(dlm))
End If
concat = retVal
End Function


Did you find this useful? Are you looking for some other excel UDFs as well, drop a comment, I am a busy coffee drinker, but between the sips I can whip out ugly looking but functional vb code

One of the most frequent tasks for any manager is “planning”, be it putting together a hiring schedule or designing a jumbo jet, it all starts with a simple project plan and gantt chart is simple and intuitive representation of the same. But how to make a gantt chart in excel without writing too many formulas or adding conditional formats? Do not worry! with the simple trick we are going to learn today, you will be able to “gantt in 60 seconds”

For our example purposes, we will look at a fictitious project plan shown below:

Even though you can use this trick to pretty much any data format, it works better when the project plan is structured around how I represented it above.

Now lets build a gantt chart in 60 seconds, get your stopwatches out and get, set …. GO!

1. Create a bar graph

Select the data part of your plan (ie all the cells except header row in the above table) and click on chart icon in excel. Select “bar chart” as chart type and “stacked bar 2d” as sub-type (2nd left on the top row) as shown here.

Click finish. At this point your gantt chart should look like this:

2. Transform your bar graph to gantt chart

Now we will convert this stacked bar graph to a gantt chart by using chart formatting options.

• First we will reverse the way data is charted, so that it looks like a gantt chart. For this we need to right click anywhere on the “y-axis” of the graph and select “format” option. Go to “scale” tab and check “Categories in reverse order” option.
  

  Click ok, now out gantt chart should look like this:

  

  btw, what is the time on that stop clock, 34 seconds, well, thats just fine, we have got plenty of time to spruce this up.

• Now, lets get rid of first data series so that our graph looks more like gantt chart. Select the first data series of your chart (should be in violet blue color ), right click and go to “format data series” option.
  

  Select “none” for “border” and “area” options in the “patterns” tab. This will make sure that the first series is invisible, so we see second data series floating on the chart, thus making it look almost like a gantt chart.

  

  Go to “data labels” tab and check “category name” option. This will make sure our gantt chart will show labels (but on the now invisible first data series)

  

  Click ok, at this point our gantt chart should look like this:

  

• Finally we will adjust labels and backgrounds to convert this to a perfect gantt chart
  1. First lets remove the legend box on the right by selecting it and hitting “del” key.
  2. Now, lets also remove the y-axis since labels are visible on data-series 1. Just click on the y-axis (or category axis) and hit “del” again.
  3. Lets adjust the alignment of the data labels on series 1 so that they are properly visible. Right-click on the data labels and select “format” option. Go to “alignment” tab in the dialog and select “Right” for horizontal alignment and “inside end” for label position. This will ensure that our data labels are right aligned and shown at the end of data series 1, ie closer to the actual series 2 (duration of the task). See the below screen cap for more help.

     

  4. Lets also remove the plot back ground to remove the annoying grey color from our gantt chart. Just click on the grey color anywhere and hit “del”. While we are at it, you can also change the line color of x-axis (the days) to white or transparent to reduce the eye sore.
  5. Finally, lets adjust the plot area size so that we can read all the data labels and everything looks normal.

  At this point our gantt chart should look something like this:

  

  If you still have few seconds left, you can tweak the chart format to make it look better. I had 3 more seconds left, so I tried this

  

Bonus tips for enthusiastic excel experimenters:
1. Adjust the grid line format to make them more subtle
2. Select a particular task’s data point and change its color to emphasize progress / stalled statuses
3. Enhance this to add another column with no. of resources (or difficulty etc.), add this to the stacked chart and make it invisible just like series 1, but show the data labels.
4. You get the picture… so start gantting…

Also read:
Learn how to create project plans / gantt charts using conditional formatting
Create art grade excel charts with these 73 designer templates
Other uber cool excel tricks to make your colleagues zealous and your boss happy

Here is a ridiculously easy trick to do nice incell pie charts in excel, what more, they will make you look like a charting wizard.

1. Download free pie chart font from here (direct download link). The font has 21 pie chart codes starting with empty circle (character ‘a’) and ending with full circle (character ‘u’)
2. Install the font (just extract the zip file, copy the font file to your fonts folder, click here to learn how to install a font in windows)
3. Open excel, go to your data table and insert a column where you want incell pie charts
4. Enter a formula like =CHAR(CODE("a") + ROUND(data cell*21,1)), just replace the data cell with actual cell code.
5. Finally change the font to “pie charts for maps” from font dialog.
6. There is no step 6, so go ahead and show off this cool table to your colleagues, make their jaws drop in awe.

You can add some conditional formatting to the pie charts like I did to make it nice.