The standard collision damage waiver included within your rental agreement will cover bodywork damage except in most cases to the underside or roof of the vehicle. This will incur an excess should a claim be made for damages and it is this cost the car rental excess insurance covers. It is always worth double check with the terms and conditions linked to your rental to make sure what is and is not covered.
So if you damage your rental vehicle during the period its under your care, be it a simple scratch or a dent the collision damage waiver will cover the costs apart from the initial excess on the policy which will be left down to you to cover. This excess can vary in figure between rental desks, suppliers and locations quite heavily.
If your question isn't answered here you may want to checkout these pages: