# Equal Width Cells per Column

Location: http://www.mvps.org/dmcritchie/excel/equicols.htm
[View without Frames]

## Pivot Table Example

A Pivot Table is a useful tool for summarizing data created from Data -> Pivot Tables, and then using some drag and drop techniques.

 A B C D E F 1 Name QTY Size Hooded Color Logo 2 Abe 1 S Y Red Team 3 Bob 1 XL N Blue School 4 Chuck 2 L N Blue Team 5 Chuck 2 M Y Red Team 6 Dwain 1 S Y Red Team 7 Evan 1 M N Blue School 8 Frank 2 XXL Y Blue School 9 Fred 2 M Y Red Team 10 George 3 S N Blue School 11 Hal 1 S Y Red Team 12 Harvey 1 XL Y Blue School 13 Jim 2 XXL Y Blue Team 14 Paul 1 M N Blue Team
The following is a Text table suitable for use in a newsgroup posting where attachments are not allowed which was created by reworking the data into cells of equal width per column in a selection with a macro.
```Name   QTY Size Hooded Color Logo
Abe      1 S    Y      Red   Team
Bob      1 XL   N      Blue  School
Chuck    2 L    N      Blue  Team
Chuck    2 M    Y      Red   Team
Dwain    1 S    Y      Red   Team
Evan     1 M    N      Blue  School
Frank    2 XXL  Y      Blue  School
Fred     2 M    Y      Red   Team
George   3 S    N      Blue  School
Hal      1 S    Y      Red   Team
Harvey   1 XL   Y      Blue  School
Jim      2 XXL  Y      Blue  Team
Paul     1 M    N      Blue  Team
```

To Create a Pivot Table
Select all of the table i.e. A1:F14
Data, Pivot Tables and .
Create on separate sheet
drop Color, Hooded, and Logo on left side for rows
drop Size into Columns
drop Qty into Data

make sure you Sum of Qty and not Count of Qty

 Sum of QTY Size Color Logo Hooded L M S XL XXL Grand Total Blue School N 1 3 1 5 Y 1 2 3 School Total 1 3 2 2 8 Team N 2 1 3 Y 2 2 Team Total 2 1 2 5 Blue Total 2 2 3 2 4 13 Red Team Y 4 3 7 Team Total 4 3 7 Red Total 4 3 7 Grand Total 2 6 6 2 4 20

In the above table there are unwanted subtotals.  The subtotals were colored to separate them from the parts that were wanted.

the subtotals can be suppressed after the Pivot Table has been generated using this tip from Ken Wright: right click on each of the subtotals (gray pulldowns), select field settings and change option selected from automatic to none.
 Sum of Qty Size Color Logo Hooded L M S XL XXL Grand Total Blue School N 1 3 1 5 Y 1 2 3 Team N 2 1 3 Y 2 2 Red Team Y 4 3 7 Grand Total 2 6 6 2 4 20

To get the sizes in the correct order (S, M, L, XL, XXL), first create a custom order (tools, options, custom lists), then select the area to be sorted left to right (D4:H15), then Data, Sort, Options, Order: S, M, L,..., then Ascending.
 Sum of QTY Size Color Hooded Logo S M L XL XXL Grand Total Blue N School 3 1 1 5 Team 1 2 3 Y School 1 2 3 Team 2 2 Red Y Team 3 4 7 Grand Total 6 6 2 2 4 20

## Macro code to create equal width cells in a column (#macro)

```
Sub Equal_columns()
'David McRitchie, 2004-01-27, create equal width columns
'Reformat the content in a slection so that each cell in the column
'   has an equal width.   Results can be pasted into a posting
'   and should be viewd in a fixed font  (View, font,  fixed).
Dim c As Long, r As Long, l As Long
Dim kon30 As String
kon30 = "                                "
For c = 1 To Selection.Columns.Count
l = 0
For r = 1 To Selection.Rows.Count
l = Application.Max(l, Len(Selection(r, c).Text))
Next r
For r = 1 To Selection.Rows.Count
If Selection.Cells(r, c).HorizontalAlignment = -4152 Or _
IsNumeric(Selection.Cells(r, c)) Then
Selection(r, c) = "'" & _
Left(kon30, l - Len(Selection(r, c).Text)) _
& Selection(r, c).Text & " "
Else
Selection(r, c) = "'" & Selection(r, c).Text & _
Left(kon30, l - Len(Selection(r, c).Text)) & " "
End If
Next r
Next c
End Sub
```
If not familiar with installation and use of macros, see Getting Started with Macros and User Defined Functions

Excel questions not directly concerning my web pages are best directed to newsgroups
such as news://msnews.microsoft.com/microsoft.public.excel.misc where Excel users all around the clock from at least 6 continents ask and answer Excel questions.  Posting suggestions and netiquette.  More information on newsgroups and searching newsgroups.    Google Groups (Usenet) Advanced Search Excel newsgroups (or search any newsgroup).