Equal Width Cells per Column

Location: http://www.mvps.org/dmcritchie/excel/equicols.htm      
Home page: http://www.mvps.org/dmcritchie/excel/excel.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.

 ABCDEF
 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).
This page was introduced on January 27, 2004. 
[My Excel Pages -- home]    [INDEX to my site and the off-site pages I reference] 
[Site Search -- Excel]     [Go Back]    [Return to TOP

Please send your comments concerning this web page to: David McRitchie Send Email Comments


Copyright © 1997 - 2004,  F. David McRitchie,  All Rights Reserved