Crystal Reports Export To Excel Column Alignment

Ensuring that data can be successfully exported to excel is often a key requirement for business users.

This can be a tricky task inCrystalreports and often involves a lot of painstaking movement of columns, fields and alignments.

The export to excel data-only feature is often utilised by users to get data from a crystal report and allow them to manipulate the data as they require in excel.

Often exported data can have incorrect column and header alignment.
However there is a simple solution in the export options which will keep the alignment the same as displayed in the report.

“Maintain Column alignment” in the File>Export>Report Export options> menu should be selected.

An Alternative, if this option does not resolve the issue, is to insert a blank text box in any gaps in your header columns. This will force excel to populate the cell and maintain alignment from the crystal report.

Excel – Quarter/Year from Date

So you want to display the Quarter Number, Q1,Q2 etc along with the Year in the format Q1-11 Q2-10 etc.

This is a handy formula which will allow you to group all your dates in the relevant quarter of the year.
I found this particularly useful for an Xcelsius dashboard, which needed to group and filters data into Quarters.

Enter your date field in A1 and place the forumla below in the require cell.

=”Q”&INT((MONTH(A1)-1)/3)+1&”-“&RIGHT(YEAR(A1),2)

1/1/2011  Q1-11