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?






[TABLE="width: 1299"]
<colgroup><col><col><col><col span="12"></colgroup><tbody>[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="colspan: 4"]Avg Lead Time [/TD]
[TD="colspan: 4"]Replenishment Lead Time[/TD]
[TD="colspan: 4"]Min Lead Time [/TD]
[/TR]
[TR]
[TD]BP#[/TD]
[TD]Site[/TD]
[TD]Basin[/TD]
[TD]20/40[/TD]
[TD]30/50[/TD]
[TD]40/70[/TD]
[TD]100M[/TD]
[TD]20/40[/TD]
[TD]30/50[/TD]
[TD]40/70[/TD]
[TD]100M[/TD]
[TD]20/40[/TD]
[TD]30/50[/TD]
[TD]40/70[/TD]
[TD]100M[/TD]
[/TR]
[TR]
[TD]9701[/TD]
[TD]CIG-Von Ormy[/TD]
[TD]EAG[/TD]
[TD]17[/TD]
[TD]15[/TD]
[TD]15[/TD]
[TD]17[/TD]
[TD]17[/TD]
[TD]15[/TD]
[TD]16[/TD]
[TD]17[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]9704[/TD]
[TD]CIG-Loving[/TD]
[TD]PRM[/TD]
[TD]17[/TD]
[TD]15[/TD]
[TD]15[/TD]
[TD]17[/TD]
[TD]17[/TD]
[TD]17[/TD]
[TD]16[/TD]
[TD]17[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]9705[/TD]
[TD]CIG-Lubbock[/TD]
[TD]PRM[/TD]
[TD]17[/TD]
[TD]15[/TD]
[TD]15[/TD]
[TD]17[/TD]
[TD]20[/TD]
[TD]15[/TD]
[TD]16[/TD]
[TD]19[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]9708[/TD]
[TD]Granite Peak-Rock Springs[/TD]
[TD]NIO[/TD]
[TD]17[/TD]
[TD]15[/TD]
[TD]15[/TD]
[TD]17[/TD]
[TD]17[/TD]
[TD]15[/TD]
[TD]15[/TD]
[TD]17[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]9711[/TD]
[TD]Equalizer-Victoria[/TD]
[TD]EAG[/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]9713[/TD]
[TD]Maalt-Enid[/TD]
[TD]MID[/TD]
[TD]17[/TD]
[TD]15[/TD]
[TD]15[/TD]
[TD]3[/TD]
[TD]17[/TD]
[TD]15[/TD]
[TD]18[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]

 

[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 ...