Excel Table Cleaning Question

[Dastnai]

Hi Everyone,

I have an excel table with the same grades across a row (20/40, 30/50 etc.) Above that I have merged columns representing the data for each grade IE. Avg Lead Time. The customer is listed as a column. Can anyone come up with a cleaner way to represent the data?

			Avg Lead Time	Replenishment Lead Time	Min Lead Time
BP#	Site	Basin	20/40	30/50	40/70	100M	20/40	30/50	40/70	100M	20/40	30/50	40/70	100M
9701	CIG-Von Ormy	EAG	17	15	15	17	17	15	16	17	10	10	10	10
9704	CIG-Loving	PRM	17	15	15	17	17	17	16	17	7	7	2	2
9705	CIG-Lubbock	PRM	17	15	15	17	20	15	16	19	10	10	2	2
9708	Granite Peak-Rock Springs	NIO	17	15	15	17	17	15	15	17	10	10	10	10
9711	Equalizer-Victoria	EAG	30	30	30	30	30	30	30	30	10	10	10	10
9713	Maalt-Enid	MID	17	15	15	3	17	15	18	10	10	10	10	10

<colgroup><col><col><col><col span="12"></colgroup><tbody>
</tbody>

 

[James006]

Hi,

Two simple recommendations :

1. NEVER Merge ...

2. Insert a Pivot Table ...

Hope this will help

 

[Dastnai]

Even though this table has multiple cell references to other tables? In total I have 104 columns and about 30 different formulas.

 

[SpillerBD]

Merged Cells are often used to get a formatted result. A better option, IMHO, is to use the formatting option of "Center across Selection" in the horizontal alignment.

I agree on using Pivot Tables. However, this may require some ETL (Extract, Transform, Load) of your data if it is not a Proper Data Set.
Watch this and maybe some of the others in Mike's playlist. (Mike Girvin, ExcelIsFun)
https://www.youtube.com/watch?v=IXyrgvtQrVE

 

[Dastnai]

Thanks everyone!

 

[James006]

You are welcome ...