To begin making your crosstab, from the main menu of Excel, choose INSERT and click the PivotChart button. (Figure 2) Figure 2 Once clicked, the PivotChart dialog box will open.

• Points

  22

  Trophies

  1

  Posts

  6

  Hi,

  
  

  I have a simple spreadsheet that looks like this:

  
  

  Origin State Dest State Company
  AL AL A
  AL AR A
  AL AZ B
  AZ AL A
  AZ AR C

  
  

  ....and so on. Basically, the first 2 columns have all states/provinces and
  column 3 has the company we use. I need to create one matrix with all
  states/provinces in Row 1 and in Column 1, and all the cells in between are
  populated with the company.

  
  

  Question is How can I do this using MS Excel using Pivot Table. I can do this using Business Objects or Crosstab Query in MS Access but how do I do it in Excel using Pivot Table? This can be done by using INDIRECT and MATCH functions but this involves a couple of steps. Is there any shorter method?

  
  

  I have attached an excel sheet that gives what I have, What I can do using Access or Business Objects and what I get using Pivot Table in Excel. The normal Pivot Table in Excel gives numbers instead of the company name even if I use Max or Min function (which works fine in MS Access query). So what am I missing here?

  
  

  Thanks in anticipation,
  Warm Regards,
  Kallol

  1. Navigation
  2. OzGrid
  3. Forum
  4. Members
  5. Options
  6. Current Location
  This site uses cookies. By continuing to browse this site, you are agreeing to our use of cookies.

  Your browser has JavaScript disabled. If you would like to use all features of this site, it is mandatory to enable JavaScript.

Click inside the table to select it. Then, click on the Design tab on Excel's ribbon. On the left side of this menu, find the Table Name box and type in a new name for your table. Make sure that it's a single word (no spaces are allowed in table names.). If you're using Excel for Mac 2011 and earlier, the PivotTable button is on the Data tab in the Analysis group. Excel will display the Create PivotTable dialog with your range or table name selected.

Quickly convert 2-dimensional/cross table to list in Excel

Normally if you want to convert a two-dimensional table to a list table, you have to manually copy and paste each data one by one. It will take a lot of time to convert a big two-dimensional table. But the handy Transpose Table Dimensions tool of Kutools for Excel can save your time, and help you easily deal with following operations quickly:

Transpose Table Dimension demo

• Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
• More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
• Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
• Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
• Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
• Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
• More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

Click Kutools >> Range >> Transpose Table Dimensions. See screenshots:

Convert a cross (two dimensional) table to list

1. Please select the cross table which you want to convert into a list as follows, and then apply the utility.

2. Select Cross table to list option and specify a single cell to deploy the result in the Results range. Note: You can specify the Results range across different worksheets and workbooks.

3. Click OK. The 2-dimensional table has been converted to list table. See screenshots:

Convert a list to a cross (two dimensional) table

1. Please select a list which you want to convert into a cross table, and then apply the utility. Note: you must select a list which contains 3 columns.

2. Select List to cross table option and specify a single cell to deploy the result in the Results range. Note: You can specify the Results range across different worksheets and workbooks.

How To Create A Cross Tab Table In Excel For Mac 2016 Free

3. Click OK. The list has been converted to a cross table. See screenshots:

Notes:

1. A cross (two dimension) table will be converted into a flat list with three columns.

2. If converting a list to cross table, the list must contain three columns.

3. This tool supports Undo (Ctrl + Z).

4. If you select a list which contains headers as follows (see screenshot), you must check the My data has headers option, otherwise the convert operation won't work properly.

Pivot Table In Excel

Transpose Table Dimension demo

Kutools for Excel: with more than 300 handy features, free to try with no limitation in 30 days. Download and free trial Now!

Kutools for Excel

The functionality described above is just one of 300 powerful functions of Kutools for Excel.

Designed for Excel(Office) 2019, 2016, 2013, 2010, 2007 and Office 365. Free download and use for 60 days.

How To Create A Cross Tab Table In Excel For Mac 2016 Download

How To Create A Cross Tab Table In Excel For Mac 2016 Autorecover Location

How To Create A Cross Tab Table In Excel For Mac 2016 Version

• To post as a guest, your comment is unpublished.
  Outlines of your eyes and visions of you Girl, I think I need a minute To figure out what is, what isn't These choices and voices, they're all in my head .Sometimes you make me feel crazy. 1v1 lolslope unblocked