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

by

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

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

Tab

Excel Table Function

  1. Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer. Kutools for Excel: More than 300 Advanced Functions for Excel 2019, 2016, 2013, 2010, 2007 and Office 365. Classic Menu for Office: Bring back familiar menus to Office 2007, 2010, 2013, 2016, 2019 and 365, as if it were Office 2000 and 2003.
  2. 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.).

I recently wanted to transform a summary table of the form:

into a table with rows of (activity, date, value) so that I could add columns for year and month and then analyze the data using a pivot table.

Data Table In Excel

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

Cross

How To Create A Table In Excel

It turns out that you can do this with another pivot table, yay! I followed this tutorial to convert my summary columns into data rows using Microsoft Excel 2010.

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

  1. Press Alt-D, P to get to the secret pivot table wizard that’s different from the one you get from Insert > Pivot table.
  2. Choose Multiple consolidation ranges. Click Next.
  3. Choose I will create the page fields. Click Next.
  4. Select the range and add it. Go through the rest of the wizard to create a pivot table.
  5. Remove the row and column fields.
  6. Double-click on the total.

You should now see a table with the data from your crosstab.

Neato! Pivot tables are even cooler than I thought.