Summary: Brahmaji having brought about creation became arrogant due to the effect of Rajas (creation). His fifth head started reciting all the Vedas alongwith ancillaries, the Itihasa, etc., which caused so much Rajasik splendour so as to harm his own creation. The created beings then prayed to Lord Shiva who then plucked out the fifth head and danced around, earning the name Kapali.
The story of the chopping of the fifth head of Brahmaji has been described in the second chapter of the Avantīkṣetra-māhātmya of the Āvantya-khaṇḍa of the Skanda Purāṇa (khaṇḍa version).
At the outset, Brahmaji as Hiranyagarbha was endowed with five faces and four arms. After completing his creation, he became conceited about it due to the effect of excess Rajas:
Thereupon Brahmā became deluded and puffed up with Rajas. He blazed with his brilliance thinking thus: ‘The creative activity is mine. There is no other god equal to me by whom creation of Devas, Asuras, Gandharvas, beasts, birds and other creatures has been effected.’ Thus deluded, that Virañcya of five faces became arrogant.
His four faces were chanting each of the four Vedas respectively, but due to arrogance, the fifth face was chanting all the scriptures due to which, such great Rajasik splendour (described above) emerged from that face, so as to harm his own creation.
He began to recite the ancillaries of Veda and their sub-parts, Itihāsa including the mystic secrets and holy collections and all the Vedas with the fifth face having another eye. Like a lamp at sunrise all the Asuras, Suras etc. faded off, due to the brilliance of the face of that Lord of wonderful splendour. Though they were his good sons, the Devas became extremely distressed and mentally lost. They were not able to approach the splendour, look at it for a long time or even get away from it.
It at this time when the creation of Brahmaji was getting negatively affected by the radiance of the fifth face, that the created beings prayed to Lord Shiva to restore creation to its original state (verses 55-56). Hearing their prayers, Rudra Bhagavan goes to the place where Brahmaji is, creates greater splendour and plucks out the fifth head (enjoy this part, having included one Sanskrit verse because the effect doesn’t come without it):
Rudra, overpowered, with his brilliance, Pitāmaha... The elated Lord Rudra stood in front of him. The face of Brahmā assailed by Rudra’s radiance, did not shine. It did not shine like the moon at the time of sunrise though its rays are bright during the night. On seeing his son, the eternal Rudra Deva, standing proudly, he simply saluted only with (the gesture of his) hand [again showing Rajasik delusion] and spoke the words smilingly. The Lord of uneven (three) eyes replied to Brahmā only in smiles. Since Brahmā did not come to know the Lord as he was enveloped in his lustre, the Moon-crested Lord burst into boisterous laugh.
तेजोराशिशशांकाभः शशांकार्काग्निलोचनः। वामांगुष्ठनखाग्रेण ब्रह्मणः पञ्चमं शिरः॥६५॥ चकर्त कदलीगर्भं नरः कररुहैरिव।
The Lord who had the brilliance of the moon in its height of refulgence, who had the moon, the sun and fire for his eyes, plucked off the fifth head of Brahmā with the tip of the nail of the left thumb, like a man plucking the inner core of plantain with his fingers. Afterwards, the head that had been cut off remained in the hand of Rudra. It was dazzling and terrible. Brahmā looked at it with great fear. The head shone due to the nails of the hand of Maheśvara like another moon stationed in the middle of the group of planets. The Moon-crested Lord jumped and danced along with that skull.
The skull is finally supposed to have been dropped in Ujjain in Mahakalavana. There is also a lore in Badrinath that Lord Shiva performed his prayashchita for ‘Brahma Hatya’ there, earning the place the name of Brahma Kapala. It is said that performing shraddha for the pitars is so beneficial that it ranks second, only to Gaya.
Fun fact: A similar version of the story has also been described by Sadhguru who’s a great Rudra devotee.