The 46-year-old told the phone hacking trial at London's Old Bailey yesterday the relationship started in 1998, but was not "continual" and there were long periods where they were just close friends.
The married father-of-three acknowledged the "pain" the on-off relationship caused, particularly to his wife Eloise.
Jurors had previously heard that Ms Brooks and Mr Coulson had "periods of intimacy" during their time working together.
Jurors have seen a draft letter Ms Brooks wrote to Mr Coulson declaring her love for him. But in her evidence, she said they "weren't meant to be".
Mr Coulson followed Ms Brooks as editor of the now-defunct Sunday tabloid between 2003 and 2007, and before that was appointed in 2000 as her deputy.
He told the court that the pair first got to know each other in 1996 then became colleagues in 1998 when Ms Brooks was made deputy editor of The Sun.
The former Downing Street spin doctor said: "There was an affair that started in 1998. It ended quite soon after but it did re-start, as the court has heard. What I want to say is that it was not by any means continual. There were very long periods - very long periods - where the relationship was what it should have been," saying they were just close friends and colleagues.
"But I don't want to minimise it or excuse it. It was wrong and it shouldn't have happened."
Both Ms Brooks and Mr Coulson are accused of conspiring to hack phones and face separate charges of conspiracy to commit misconduct in public office.
His lawyer Timothy Langdale QC questioned him on how the affair affected their working relationship. Mr Coulson agreed he would exchange confidences with Ms Brooks in a way he would not normally do with an editor.
But he denied the suggestion that the closeness of their relationship involved him "breaching professional standards or rules".
Mr Langdale said: "In particular, it has been suggested as a result of the closeness of your relationship you would share sensitive or exclusive stories."
Mr Coulson said: "No, that did not happen - with the caveat unless on very particular occasions there was a pre-determined deal when there was a share between the two papers."
Mr Coulson told how he spent a weekend with Prime Minister David Cameron after he resigned as his media adviser amid controversy over what he knew about phone hacking NotW editor.
The witness said he had not spoken to the Prime Minister since that pre-arranged social occasion in spring 2011.
Mr Coulson resigned as NotW editor in 2007, after the conviction of former royal editor Clive Goodman for hacking, and joined Mr Cameron's team. He resigned as media adviser in January 2011 amid the controversy over phone hacking, and was charged in 2012.
Mr Coulson said he had had sparing contact with former boss Rupert Murdoch since his resignation. He added: "My family and I spent a weekend with him in the spring after I left. I have not spoken to him since."
The invitation came before he left Downing Street.
Mr Coulson said the intrigue and secrecy at the NotW had become "destructive".
Mr Coulson, of Kent, is charged with conspiring to hack phones with Ms Brooks and Stuart Kuttner and conspiring with former royal editor Clive Goodman to commit misconduct in a public office.
All seven defendants deny charges against them. The trial continues.