Reformattign Data/Converting-Combinign Vertical Data to Horizontal

[Tunderwood]

I have a report formatted in Crystal Reports and exported to Excel. It lists
costs for various products vertically grouped by source.

EX:
Source #1 Full $3.00
Source #1 Half $2.00
Source #1 Qtr $1.00

I need this Data to be displayed in a workbook as follows:

Source #1 $ Full $3.00 Half $2.00 Qtr $1.00

The Full Half and Qtr woudl be the Column Headings with the $$ amounts under
each. This involves deleteing the duplicate Source #1 from the Row format and
combining them into one row.

I need help? This has been doen for a while here at my company and the
person who was handling this is no longer with us and left no instructions.

 

[Bernie Deitrick]

Tunderwood,

Use a pivot table.

I'm assuming you have column headings. Select all your data, including the headings, then use Data /
Pivot Table... and click Finish.

Then drag the column heading for the Source values to the row field area, the column heading for the
Full/Half column to the column field area, and the dollar values column heading to the data items
area. Right-click on the grey tab if it says "Count of...." instead of "Sum of ..." and change the
field setting to SUM.

You can turn off grand totals and subtotals,a nd format the cells to get the table to look the way
you want.

HTH,
Bernie
MS Excel MVP