In my case I needed to export an excel file with different companies' names on the sheet name.
For this you first need to create a row group on the field you need. Then go to Row Groups, select the group like in the picture below:
Go to the properties of that group. You should see something like this:
Set the BreakLocation as you need it and the field name for the PageName.
Run your report and export it to excel. The sheet names will no longer be Sheet1, Sheet2, etc.
