+ Reply to Thread
Results 1 to 5 of 5

Pivot: organizing data in colums

Hybrid View

  1. 12-03-2013, 05:08 AM #1
    Villu
    Villu is offline
    Registered User
    Join Date
    12-03-2013
    Location
    Helsinki, Suomi
    MS-Off Ver
    Excel 2007
    Posts
    2

    Pivot: organizing data in colums

    So I'm trying to make a Pivot Chart out of this: Housing BIG DATA english.xlsx
    The chart is originally in finnish but I translated the beginning just so you can understand what I'm doing. Basicly its a chart about Finnish housing. On the left there is the county starting in Alphabetical order ''Akaa, Alajärvi.'' Then how many 1,2,3,4,5,6,7+ people single-family houses, terraced houses, block of flats and other houses there is in the county.

    I'm trying to make a Pivot chart that could would have various information in it e.g. ''how many single-family houses are in these specific 232 counties and the percentual amount compared to all housing in these 232 counties.''

    The problem is as I'm trying to create the Pivot chart Excel says: '' To create a Pivot Table you must data that is organized as a list with labeled columns.'' Simple enough, but still as an amateur user I'm not able to figure out what should i do exactly.

    So if someone could organize or instruct me to organize the first county so that it is Pivot compatible, I would appreciate it greatly! Thanks in advance!

    Ps. My description of the problem is probably vague and wrong but if you look at the chart you'll propably easily see what the problem is.

    E: my MS Office version is 2010
    Last edited by Villu; 12-03-2013 at 05:21 AM.
    Reply With QuoteRegister To Reply
  2. 12-03-2013, 05:47 AM #2
    Kyle123
    Kyle123 is offline
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,239

    Re: Pivot: organizing data in colums

    You need, to "un-pivot" your data to be able to pivot it. Your data needs to be in the following format:
    County	Persons	House Type  	Year	Qty
Akaa	1 person	Single-family hous	2002	755
Akaa	1 person	Single-family hous	2003	767
Akaa	1 person	Single-family hous	2004	787
Akaa	1 person	Single-family hous	2005	791
Akaa	1 person	Single-family hous	2006	814
Akaa	1 person	Single-family hous	2007	826
Akaa	1 person	Single-family hous	2008	855
Akaa	1 person	Single-family hous	2009	860
Akaa	1 person	Single-family hous	2010	875
Akaa	1 person	Single-family hous	2011	873
Akaa	1 person	Single-family hous	2012	901
Akaa	1 person	terraced house	        2002	469
Akaa	1 person	terraced house	        2003	480
Akaa	1 person	terraced house	        2004	491
Akaa	1 person	terraced house	        2005	482
Akaa	1 person	terraced house	        2006	496
Akaa	1 person	terraced house	        2007	471
Akaa	1 person	terraced house	        2008	498
Akaa	1 person	terraced house	        2009	510
Akaa	1 person	terraced house	        2010	525
Akaa	1 person	terraced house	        2011	534
Akaa	1 person	terraced house	        2012	551
    Reply With QuoteRegister To Reply
  3. 12-03-2013, 06:13 AM #3
    Izandol
    Izandol is offline
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Pivot: organizing data in colums

    To reorganize your data:
    Select your data columns A and B.
    f5, select 'Special...' button, select Blanks and click OK.
    Type = then press up arrow and press Ctrl+Enter
    Insert a new column D.
    enter =A2&"@"&B2&"@"&C2
    and fill this down to the end of the data.
    Start the pivot table wizard (Maybe Alt+DP depending on your language), choose 'Multiple consolidation ranges' as data source and click Next.
    Select 'I will create the page fields' and click Next
    Select your data range from column D to O and Add and click Finish.
    In the new pivot table double click the grand total cell in the bottom corner to extract all data.
    Insert two new columns at column B in the new data table
    Select column A data, choose Data, Text to Columns, Delimited, choose 'Other' and enter @ as the delimiter, then Finish.
    Reply With QuoteRegister To Reply
  4. 12-03-2013, 10:47 AM #4
    Villu
    Villu is offline
    Registered User
    Join Date
    12-03-2013
    Location
    Helsinki, Suomi
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Pivot: organizing data in colums

    Thanks Izandol!

    Just one simple question: could you specify what this means ''Type = then press up arrow and press Ctrl+Enter''? In other words what do I do to the blanks?

    I managed to do all the other steps but of course the Pivot table didn't turn out to be anything informative because I didn't know what to do on that step..
    Reply With QuoteRegister To Reply
  5. 12-04-2013, 03:52 AM #5
    Izandol
    Izandol is offline
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Pivot: organizing data in colums

    It means press the '=' key, then the up arrow, and then press Ctrl+Enter. Then all blank cells will have a formula linking to the cell above and all the blanks will be filled.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Pivot table for organizing cost as a % of total per month
    By amartino44 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 05-17-2013, 04:49 PM
  2. Help with organizing data-pivot table
    By putitwhere in forum Excel Charting & Pivots
    Replies: 10
    Last Post: 04-30-2013, 08:18 PM
  3. Difference between colums in pivot
    By IM2HARD2RESIST in forum Excel General
    Replies: 2
    Last Post: 10-15-2009, 11:25 AM
  4. Help organizing data using Pivot Table
    By Branden in forum Excel General
    Replies: 0
    Last Post: 03-21-2006, 02:55 PM
  5. pivot table - two colums of data are missing
    By JJ in forum Excel General
    Replies: 1
    Last Post: 08-04-2005, 03:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1