1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to convert Australian Bureau of Stats data cube to useful layout

Discussion in 'Ask an Excel Question' started by atlasB, Jan 11, 2018.

  1. atlasB

    atlasB New Member

    I often get data from the ABS (Australian Bureau of Statistics). For example, this data on nutrient intake percentiles. It comes in Excel format, in a very cumbersome layout.

    I spend a lot of time reformatting and combining ABS outputs to make them into the long data format. I arrange the data manually, then use Excel's pivot table wizard to turn it into a useable format. Doing it manually takes age.

    Different ABS outputs have different, but similar, layouts. Here is another output on household expenditure.

    What can I do to make using this data easier? Is there a macro or something that could save me from copying and arranging the data bit by bit? My current process is tedious and error prone, and so boring...

  2. Hui

    Hui Excel Ninja Staff Member


    Firstly, Welcome to the Chandoo.org Forums

    Have you contacted the ABS directly?
    explain the issues

    They may have other download/online systems to simplify this process

    I regularly use ABS data as well, but apart from them changing data locations, I don't have many issues, but i only use a small set of common data
  3. NARAYANK991

    NARAYANK991 Excel Ninja

  4. atlasB

    atlasB New Member

    I have, and I have also checked out TableBuilder. TableBuilder is great if you want census data, otherwise the data access seems to cost about $1000 per data set (which is out of my budget for what is essentially a hobby). And the ABS output layout is a relic from the days when they would publish everything in pdf, so the excel sheets they produce are designed to look like a printed page.
  5. NARAYANK991

    NARAYANK991 Excel Ninja

    Last edited: Jan 15, 2018

Share This Page