Google-sheets – In Google Sheets, create a hyperlink in a cell that is clickable directly. No need to hover for popup link

I wasn't able to reproduce your results. As a matter of fact, it worked perfectly.

What you tried to do is most probably the following:

In A1 you typed in =HYPERLINK(CONCATENATE("http://www.example.com/id/",A1);A1) and this yields an error of coarse.

Update

If you really want to get the result in A1, then you need to use a script.

Code

// global
var ss = SpreadsheetApp.getActiveSpreadsheet();

function onOpen() {
  var menu = [{name: "create URL", functionName: "createURL"}];
  ss.addMenu("URL", menu);
}

function onEdit(e) {
  var activeRange = e.source.getActiveRange();

  if(activeRange.getColumn() == 1) { 
    if(e.value != "") { 
      activeRange.setValue('=HYPERLINK("http://www.example.com/id/'+e.value+'","'+e.value+'")');
    }
  }
}

function createURL() {
  var aCell = ss.getActiveCell(), value = aCell.getValue();
  aCell.setValue('=HYPERLINK("http://www.example.com/id/'+value+'","'+value+'")');  
}

Explained:

The e.value will retrieve the cells value (only applicable a cell). The setValue() will add the concatenated string into the getActiveRange(). All is only executed when e.value contains something and the active range is in column A.

I've created an extra menu option as well, to be able access the script this way.

Example:

I've created an example file for you: onEdit URL builder
Add this script via Tools>Script editor, into the script editor. Press the "bug" button and you can use the script.

Google Query Language will return a JSON string. To get the cell value and format as you see in the spreadsheet, instead of /gviz/tq?tq= and the query string, use /pubhtml?gid= and the "right" parameters.

The easiest way is to get the URL resulted of publishing the spreadsheet to the web. Then...

Add range=A1 to the URL of the published spreadsheet, where A1 is the cell that you want to display.

I.E. for https://docs.google.com/spreadsheets/d/1NIIqpRSYCXT0OlvDgXa0g4GVxmrnvQOSn_L-HcgPqVA/pubhtml?gid=0&single=true

to display only the cell B13, the resulting URL is:
https://docs.google.com/spreadsheets/d/1NIIqpRSYCXT0OlvDgXa0g4GVxmrnvQOSn_L-HcgPqVA/pubhtml?gid=0&single=true&range=B13

to remove the header, add chrome=false:

https://docs.google.com/spreadsheets/d/1NIIqpRSYCXT0OlvDgXa0g4GVxmrnvQOSn_L-HcgPqVA/pubhtml?gid=0&single=true&range=B13&chrome=false

Note:
Use ? before the first parameter.
Use & when adding the second and subsequent parameters.

References
Publish a document, spreadsheet, presentation, or drawing - Docs editors Help
Cómo publicar en Web un rango - Ejemplos para los foros (How to publish a range in the Web - Examples for forums, self citation)