A User Guide to International Student Data
Contents
1. button This reveals the Show data as component AEI International Student Data User Guide December 2008 www aei gov au 26 20 Click the drop down button currently set to Normal 21 Select Difference From from the Show data as box 22 Select Year from the Base field box 23 Select Previous from the Base item box 24 Click OK Your pivot table should now look like this the Month filter and data may be different 2003 2004 2005 2006 2007 2005 247 695 276 124 297 953 313 745 341 155 400 373 474 369 31029 19229 15 92 27410 59218 74016 12 53 6 90 5 30 8 74 17 36 16 49 25 Finally move the Sector field into the row area to the left of the Data fields to break the data down by sectors Your pivot table should now look like this the Month filter and data may be different COA CEE Month Jag 3 gt gt y 2 State Jam o 3 Nationality Ja fm z KAC 200 2003 2004 2005 2006 2007 2008 T 3 191745 147 723 159 518 165 984 171 392 17 19182 15978 11795 6466 5408 17 04 12 13 7 98 405 326 Sum of DATA YTD Enrolments 47 201 50 2 51 471 57 509 70 928 Growth 3006 6038 13419 Growth 370 52 11 73 23 33 13 ELICOS Sum of DATA YTD Enrolments 1 294 35 8 527 48 493 56 736 Growth 3064 116 34 8243 Growth 5 929 AT 0 07 17 00 46 Schools Sum of DATA YTD Enrolments 21 567 Growth Growth 19 Other Sum of DATA YTD Enrolments 22 070 24079 24 201 Growth 2009 122 Growth 9 10 0 51 E 22 Tota2. 2008 www aei gov au 4 Stakeholder engagement Through comprehensive stakeholder engagement its international network and its communication tools AEl makes sure the world knows about the quality and diversity of Australian education Student mobility AEI supports student and professional mobility into and out of Australia through Endeavour Award scholarships subsidies and international representation About this guide This guide was developed by AEI s International Research and Analysis Unit IRAU The purpose of this guide is to assist the Australia international education industry to understand the publicly available International Student Data released by AEI The guide will also provide information on how to manipulate the data using the Pivot Tables published on the AEI Online website The AEI data is published in the AEl Online website in a series of pivot tables Microsoft Excel files The data provides an up to date time series from 2002 to the current month of onshore enrolments by international students studying in Australia on a student visa The data is updated monthly and is generally available within six weeks of the end of a reporting month Prerequisites A working knowledge of Microsoft Office Excel is required to use the AEI pivot tables Excel versions from 2003 provide the greatest range of pivot table options Pivot tables will work on earlier versions of excel but not all options will be available Pivot tables
3. BRAD OR ENREDO RE 4 NES role a e doa Reece ere ee eee o E OO EE eee et ee een Rene eee are ere 4 Whal AN ext be 6 o atasca ae ik ea mea eee eee ia aa 4 Policy development and advice stos a a aE 4 International and government to government engagement cceceececeeeeeeeeeeeeeeeeeeeeeeeeneeeneeeeeeeenenenes 4 Quality assurance and consumer protectiON occcconccconccconecconeccnnenoncononcononcarononcnnonrnnnnrnnonrnrnnnnanennons 4 Qualllicatiohs eco MON eiii laicas 4 Market research and analisis ii cialis 4 Ste gl O A 5 Md a a a a tenets etnias wheat etal 5 An E anda ce RSA DRI a nO Er ac Pa tae A SR ar a tance 5 FS CS IN A a o o E O 0 OR 5 En ro 5 The AEON WEDS NG asii 7 Navigating to the AEldata ssa oe at oe ee ant alo ot cae oie ia 7 International StuGentGa tala a di ie ls a 9 Other Repons and tales la 10 Monthly Time Series of Student Enrolments Summaries by Sector ocoocconconconconconconncnnonnonconoononanons 11 PIVOT Tables ia aia 12 Downloading AEI pivot tables and using Zip files oocoocconconconconconionionnonocnnononnnononnnononnnnnnnnnnns 12 Detailed Monthly Tables PDF and Excel formats ococccccccnccnconconnononncononnnnnnnnonnnnonnnononnnnnnnnnnnnnanons 14 Other Useful Information on the AEI website occoccoccccnccnccnconconcononncononncononnnononnnnnonnnnnonnnnnnnnnnnnnnnnnnons 15 Research and Market Data Snap Sicilia 15 Research papelito an 15 Using Pivot Tables IM EXCOL id isla 15
4. Guide December 2008 www aei gov au 11 Pivot Tables 2008 Pivot Tables Note Items marked with are available only to organisations who have access to AEI s Market Information Package MIP For non MIP clients access arrangements are outlined in Accessing AEI Monthly Statistics Changes to the monthly data AEI has made some changes to AEI international student enrolment data to enhance the usefulness of the data to industry The VTE sector has now been renamed to VET In reponse to industry feedback about the reliablility of current finishes data these data have been removed from the detailed pivot table AEI will investigate whether a more accurate indicator can be used to determine the reason for an enrolment ceasing The initial display of the pivot table has been re designed to improve manipulation of the table Includes the variables Sector Nationality Month and Year for year to date Basic Pivot Table 2008 enrolments and commencements zip 7 40 MB AEI advises that the pivot table is compatible with Excel 2003 or later Includes those available in the Basic Pivot Table and also includes State and Standard Pivot Table Territory Broad Field of Education Level of Study Region and Provider Type 2008 zip 13 33 MB Warning Unzipped file is approximately 82Mb AEI advises that the pivot table is compatible with Excel 2003 or later Includes those available in the Basic Pivot Table and also includes State and Territory B
5. are supported by Apple Macintosh versions of excel but some of the functionality may be different to the windows version and some options my not be available Note AEI continually develops and improves the Pivot Tables As a result pivot table content may change without notice Guide objectives Once you have completed this guide you should e Be able to find the data on the AEl website e Understand the source of the data e Understand how the data are derived e Be able to extract data from the AEl Pivot Table on the AEl website e Be aware of the various AEI statistical products AEI International Student Data User Guide December 2008 www aei gov au 5 Throughout this manual you will see activities designed to guide you through the course These are formatted as in the example below gt Open the AEl website in your browser If you are viewing this manual electronically there are many links embedded into the file you can click on to take you to web pages If you are viewing this manual as a hard copy simply copy the link details into the address bar of your internet browser AEI International Student Data User Guide December 2008 www aei gov au 6 The AEI Online website Before you start using the AEI data familiarise yourself with the AEI Online website www ael gov au gt Click the link above to open the AEl Online website in your browser This website is AEI s portal for disseminating market intelligence on internat
6. exceeds 255 items Only the first 255 items will be displayed as columns To include all of the field s data in your PivotTable report drop it into a different area such as the data area Cancel e In this case the data will not be very useful or readable You should reorganise the fields so the data is more usefully displayed Pivot Table Field List Not all available fields need to be shown in the Page Row Column or Data areas To simplify using the pivot table only those fields currently being used need to be displayed e A full list of all the fields available can be seen in the Pivot Table Field List e To open the Pivot Table Field List o Hover over the pivot table with the mouse pointer and right click to reveal the Pivot Table Sub Menu o Select Show Field List e Fields that are currently displayed on the pivot table are shown in bold e Fields that are not displayed are shown in normal font e To display a field not already on the pivot table o Use the mouse to select the field from the Pivot Table Field List o Drag Drop it to the appropriate area on the pivot table o The field will now be shown in bold in the field list e To remove a field that is currently displayed on the pivot table o Select the field to be removed with the mouse o Drag it out of the pivot table area until you see a red X beside the mouse icon o Drop the field icon o The field will now appear in the Pivot Table field List in normal font e Th
7. right hand side o The Month Nationality and State fields are in the Page Area of the chart and can be used for filtering the data o The DATA YTD Enrolments field is in the Data Area and the summary type is set to Sum o The Sector field is providing values for the X axis o The Year field is providing the breakdown within each sector value A legend is automatically generated 9 The chart is of limited use as currently set up To make it more useful o Drag drop the Year field to the right of the Sector field The chart will change in appearance o Drag Drop the State field to where the year was AEI International Student Data User Guide December 2008 www aei gov au 30 10 Your pivot chart should now look like this the Month filter and data may be different Month Aug Nationality All 7 Sum of DATA YTD Enrolments 200 000 180 000 160 000 140 000 120 000 100 000 80 000 60 000 40 000 20 000 We now have a chart showing Enrolment Growth as at Year to date August by Year and Sector with a breakdown by State We can see from this that VET sector enrolments are growing quickly ELICOS enrolments are also growing but at a slower rate and from a smaller base Higher Education enrolments are growing at a decreasing rate Schools and Other sector enrolments are relatively stable To finish the exercise manipulate the fields to determine which state is growing the fastest This is a very brief s
8. 2005 2006 2007 2008 16 759 16 801 19255 22306 6 318 6504 7468 7 030 24 060 25 622 20 316 34 119 45 353 51 779 571 297 59418 39056 6393 6064 6 496 113 769 121 250 132 651 157 801 10 973 11 299 12 026 16 254 7 664 7 636 6 721 TAH 11 621 12 046 20 175 33 735 42314 47 077 51 768 54 049 5 620 5444 5 668 6 115 119 627 164 738 197 473 206 664 229 009 AEI International Student Data User Guide December 2008 www aei gov au 20 Filtering Data Fields in the PAGE area can be used to filter the data For example you may just want to analyse data for one particular sector To apply a filter do the following e Make sure the field to be filtered is in the Page Area If the Sector field is not in the page area use the mouse to drag it there e Click the down arrow at the right of the field box This will reveal the items of the field Because the field is in the page area only one item can be selected e Click the item to be filtered on and click OK e The data and summaries will now reflect the filter applied e The field will show the value of the filter applied e To remove the filter follow the same procedure but select All e Inthe example below you can see that the items of the Sector field are All and ELICOS Schools VET Higher Education and Other Higher Education is selected and this is reflected in the Sector field on the pivot table Ds E EE AER SEAN OIE 2h Ago nl lig Jucation E a a aaao All e Mi
9. 2008 Latest Top 10 Nationality Data Detailed July 2008 Data AEI has available the following statistical related information on international education activities APPLY FOR MIP E NEWSLETTER International Student Data ITEMS OF INTEREST z Full annual and monthly International Student Data including commencements and cessations of enrolments are available for 2002 onwards ACTIVITIES b gt International Student Data b Study in Australia Website Traffic Reports SUBSCRIBE TO AEI e NEWSLETTER View e Newsletter Research Snapshots The Research Snapshot series has been developed by the International Research and Analysis Unit AEI to provide stakeholders with summary updates on current activities and developments in international education research The Snapshots which will be produced regularly will cover a range of topics relating to data research Market Data Snapshots The Market Data Snapshot series has been developed by AEI to provide international education stakeholders with summary market specific data and research information These snapshots combine the wealth of information available in the AEI International Student Data series with the AEI international network Other Research Other AEI Research papers on AEI student enrolment data English Australia Survey of Major ELICOS Regional Markets Introduction and Executive Summary of the English Australia EA report on its annual survey of the ELICOS mark
10. 5kb April pdf 598kb May pdf 596kb June pdf 2 49Mb July pdf 637 kb August pdf 625 kb September October November December Monthly Tables 13 to 26 MIP Subscribers Only PDF Format Tables 7 to 26 Excel January xls 2 52Mb February xls 2 58Mb March xls 2 59Mb April xls 2 59Mb May xls 2 59Mb June xls 2 60Mb July xls 2 70Mb August xls 2 7Mb September October November December Monthly Tables 13 to 26 MIP Subscribers Only Excel Format gt Click the link to the most recently available Tables 7 to 12 AEI International Student Data User Guide December 2008 www aei gov au 14 Note PDF files required Adobe Acrobat Reader to open This can be downloaded at www adobe com Other Useful Information on the AEI website Research and Market Data Snapshots The Research Snapshot series was developed to provide stakeholders with one page summary updates on current activities and developments in international education data and research The series provide examples of the ways in which Detailed Pivot Table data can be manipulated to meet an individual users needs Similarly Market Data Snapshots present a one page summary of market specific data together with local insights from AEI s International Network to help explain trends in enrolment data from overseas markets Research papers A number of research papers have been developed by AEI to provide international education industry
11. 8 www aei gov au 33 Paste Special The data values will be pasted as unformatted values The underlying data will not be pasted resulting in a smaller destination file See the result below FERE A A AA ERES SM EFE AAA ES EM Aug i All 3 Nationality All 4 Level Of Study All 5 Broad Field Of Education All 6 ProviderType All All 8 Broad Field Of Study All 9 DATA As at 1st Month all 10 DATA Enrolments for Month All 11 Data Commencements for Month All 3 1 Sum of DATA YTD Enrolments Year 14 Sector 2002 2003 2004 2005 2006 2007 2008 15 Higher Education 112 563 131 745 147 723 159 518 165 084 171 392 179 136 47 201 50 207 51 471 57 509 70 928 102 655 147 073 44 204 47 358 48 527 48 493 56 736 75 793 92 627 21 567 25 335 26 031 23 878 23 028 25 181 27 541 22 070 24 079 24 201 24 347 24 479 25 352 28 012 20 Grand Total 247 695 278 724 297 953 313 745 341 155 400 373 474 389 21 This method also the advantage that fields in the page area of the source file are copied across with the data so users can see what filters are applicable to the data AEI International Student Data User Guide December 2008 www aei gov au 34
12. Sector field is moved to the Page Area the selection ELICOS and VET will be retained and the data and summaries displayed will reflect this o The Sector page field item will show Multiple Items o To change the selection of items or to include all items you must move the field to the row or column area and open the item selector to make you new selection 2003 2004 2005 2006 2007 2006 91 495 97 565 99 996 106 002 127 664 18 448 239 700 AEI International Student Data User Guide December 2008 www aei gov au 22 Drill Through Drill Through enables you to view the raw data that makes up the summaries on the screen This is especially useful if you are using the Standard or Detailed pivot tables from the AEI website To drill through select a number in the pivot table and double click on it A new worksheet will open with the raw data Sorting Items and Data It is often convenient to sort items in different ways For example you may want to sort Nationality alphabetically or by the number of enrolments To sort items by name o Locate the field to be sorted in the row or column area o Select one cell in the item list o Click the sort buttons in the menu at the top of the screen to sort the ot 44 items based on their name At To sort items by data values o Locate the field to be sorted in the row or column area o Select one cell in the data area o Click the sort buttons in the menu at the top of the screen to sor
13. Statistics International Student Data International student data are provided for 2002 onwards These include enrolment data by month and data on commencements and cessations of enrolments as well as annual summaries The data are depicted in a number of ways with several tables being available for free More detailed information can be accessed through AEI s Market Information Package MIP or generated on a fee for service basis Return to Top Final Annual International Student Enrolment Statistics for 2000 and 2001 Year 2001 Final International Student Enrolments Year 2000 Final International Student Enrolments Other Statistical Sources Link to Education Resources on the Australian Bureau of Statistics Website e These links lead to the monthly pivot tables for the current year and for previous years Data for previous years can be accessed by clicking on the year of interest e They also provide a suite of detailed data from 2002 onwards by a range of variables including Nationality State Sector and Month e The Explanatory Notes clarify common data related terms such as enrolments commencements nationality sector provider type level and field of study counting methodology and describe AEl data sources gt Click the link under International Student Data to the current year AEI International Student Data User Guide December 2008 www aei gov au 10 Monthly Time Series of Student Enrolments Summaries b
14. Understanding Era A A eta 15 Sucre ora VOU TADE saias aa e a 17 E A a RR Sd aa 17 Page ANCA UA O ue A ER 18 Data AG APA o a dao rd E 18 FLOW PG A nn RE OE POE o sa SAS T 18 E rsss aan Todo condi nanda aa a SUS li dr GA aa Rg 18 PIVOT able Fela LIS assa mass aaa OG asa ips pd DAS aa 19 Using Fields to summarise data e eeeeeeeeeree rea rr area re arena cancer cen cancer cancer cansa a eae ane nnaaa 20 Moving Fields e ra as te O bate a a 20 LACKING EE A q a 20 RISING DA ses a O eo a 21 DALTA OJO en A NR O RR 23 Sorting tems and Atay cassada di dd suds DE sas GE 23 Summarising Data in Pivot Tables eee re ere rear eee ee rece ce cancer cancer ca rca a ceara nanda 24 Creating and using PVOtCNANS sereen n ac 28 Interpreting Data in Pivot Tables soriana it 31 Copying Dala trom PIVO TADIC Saaie a do a a a a a a aa as 33 AEI International Student Data User Guide December 2008 www aei gov au 3 Introduction AEI s role and goals Australian Education International AEI provides leadership across all levels of government and industry to support the sustainable development of a world class and globally connected international education and training system in Australia As the international arm of the Australian Government s Department of Education Employment and Workplace Relations DEEWR we are well placed to work with governments and industry to realise for Australia the intellectual cultural economic and securit
15. a E 6 Sum of DATA YTD Enrolments Year gt Fo o 2006 1 604 1 874 152 160 a 4 3 794 3 902 A 1 0 1 44 112 563 131 745 147 723 159 518 165 984 171 392 179 Selecting Data When fields are displayed in the row or column areas you can use the Item Selector to limit what data is displayed This is similar to applying a filter but you can select more than one item from the field to display e The Item Selector is a good way to see what items are contained within a Field if the filed name is ambiguous e Inthe example below the Sector field items are shown with just ELICOS and VET selected e When specific selections are made the summaries in the data area and grand totals reflect these selections AEI International Student Data User Guide December 2008 www aei gov au 21 e The Show All option enables you to over ride an existing selection Clicked once it will SELECT ALL items Clicked a second time it will DE SELECT ALL items e The example below shows the items of the Sector field with just ELICOS and VET selected and displayed 4 Sum of DATA YTD Enrolments Year 6 Sector 2002 ELICOS 8 VET 17 201 028 9 Grand Total 91 49 564 178 448 239 700 Cancel IMPORTANT o If you make a selection of items in a field that is in the row or column area and then MOVE this field to the Page Area the selection will be retained and the data will be filtered based on this o Inthe example above if the
16. adila A User Guide to yee lee l n te r n a ti O n a Australian Education International St u d e n t D a t a Contact Australian Education International AEI GPO Box 9880 Canberra ACT 2601 AEl enquiry hotline 1300 363 079 Email aei deewr gov au Disclaimer The Commonwealth of Australia its officers employees or agents disclaim any responsibility for any loss howsoever caused whether due to negligence or otherwise from the use of information in this publication No representation expressed or implied is made by the Commonwealth of Australia or any of its officers employees or agents as to the currency accuracy or completeness of the information contained in this report The reader should rely on their own enquiries to independently confirm the information and comment on which they intend to act Copyright Commonwealth of Australia 2008 This work is copyright Apart from any use as permitted under the Copyright Act 1968 no part may be reproduced by any process without prior written permission from the Commonwealth Requests and inquiries concerning reproduction and rights should be addressed to the Commonwealth Copyright Administration Attorney General s Department Robert Garran Offices National Circuit Barton ACT 2600 or posted at www ag gov au cca AEI International Student Data User Guide December 2008 www ael gov au 2 Contents a O de RE NNE ee sc sce rece ae a eae e sas dc SEE RO DO DRDS seu tea tices aa SE
17. b ACTIVITIES b STATISTICS b Study in Australia Website Traffic Reports contacts search products amp services shopping cart my account login Offshore Support Publications amp Research Government Activities Qualifications Events Recognition Media International Student Data On this page Monthly Summaries of International Student Data International Student Data 2002 Onwards Final Annual International Student Enrolment Statistics 2000 and 2001 Explanatory Notes for AEI Enrolment Data Other Statistical Sources Monthly Summaries of International Student Enrolment Data 2008 December not yet available November not yet available October not yet available September not yet available August not yet available July pdf 33kb or rtf 1 11Mb June pdf 60kb or rtf 3 09Mb May pdf 60kb or rtf 3 10Mb April pdf 58kb or rtf 3 05Mb March pdf 53kb or rtf 2 72Mb February pdf 52kb or rtf 3 01Mb January pdf 51kb or rtf 2 96Mb Return to Top e These summaries are published monthly and provide a commentary on the latest AEI data and trends e They are available as PDF files requires Acrobat Reader or as RTF files requires Microsoft Word or similar e Reports are available for elapsed months of the current year and for months of previous years by clicking the year links AEI International Student Data User Guide December 2008 www aei gov au 9 Other Reports and
18. between the enrolments in one year and enrolments in the previous year Growth There is no figure for 2002 because there is no data for years prior to 2002 This is the percentage difference between the enrolments in one year and enrolments in the previous Growth Jesh There is no figure for 2002 because there is no data for years prior to 2002 AEI International Student Data User Guide December 2008 www aei gov au 32 Copying Data from Pivot Tables It is often useful to copy data summaries from a pivot table to another excel file for archiving presentation or further analysis Care should be taken when copying data from a pivot table to another spreadsheet especially if the new spreadsheet is to be subsequently emailed Depending on how you copy and paste the data you can inadvertently copy all the data underlying the pivot table as well which will increase the size of the excel file many fold although you may not be able to see the data To copy data from a pivot table to another excel file 1 Select a single cell in the table and right click 2 Choose Select Entire Table Field Settings Table Options Show PivotTable Toolbar Show Field List 3 Press Ctrl C on your computers keyboard 4 Navigate to desired cell in the destination excel file 5 Select Edit Paste Special from the top menu select Values and number formats and click OK AEI International Student Data User Guide December 200
19. e Pivot Table Field List can be locked to the sides of the screen Drag the box to the left or right and when positioned correctly it will snap to the side and expand out In this position it will remain displayed while a cell in the pivot table is selected AEI International Student Data User Guide December 2008 www aei gov au 19 gt Identify the Page Row Column Data Areas on the AEI Basic Pivot table and open the Pivot Table Field List Using Fields to summarise data Moving Fields e Fields in any part of the pivot table can be moved to any other part of the pivot table e To move fields simply select them with the mouse and drag drop them in the new location e When you move fields around you will see the cursor change indicating the area into which the field is to be dropped e Be careful where you locate data fields they should only be in the data area Stacking Fields e Fields can be stacked in the row or column areas enabling more complex Summaries of the data e To stack fields select the fields and drag drop them in the appropriate area e You can stack several fields in the row and or column areas but stacking too many fields can make the data difficult to read e Inthe example below the State blue highlight and Sector yellow highlight fields are stacked in the Row Area providing a breakdown of enrolments data by State and Sector Row and Year Column for year to date August Page Area 2003 2004
20. ector click the item called Sum of Data YTD Commencements to de select it and click OK 3 Check that the Month field has a filter for the current month applied AEI International Student Data User Guide December 2008 www aei gov au 28 4 Your pivot table should now look like this the Month filter and data may be different 7 Month Aug 2 Nationality All 3 jste EO 2003 2004 2005 2006 2007 2006 112 563 131 745 147 723 159518 165 984 171 392 179 136 47201 50 207 57 471 57 509 70 926 102655 147 073 44794 47 556 46 527 46 495 56 736 75 793 21 567 275 335 26 031 23 876 23 026 25 181 22 070 244 079 24201 24347 24479 25 352 247 695 218 124 297 953 313 745 341 155 400 373 5 Now the data for the chart is set up Next notice the Pivot Table tool bar PivotTable You may have to click inside the pivot table to expose the Pivot Table tool bar If you cannot see it go to the top menus in Excel and select View gt Toolbars gt PivotTable 6 Select one cell inside the pivot table and click the chart icon gt ll AEI International Student Data User Guide December 2008 www aei gov au 29 7 The chart is displayed in a new worksheet Your pivot chart should now look like this the Month filter and data may be different Sum of DATA YTD Enrolments 1 200 000 1 000 000 800 000 600 000 400 000 8 Notice that the fields are represented in the chart with the same drop down arrows to the
21. er see which individual is which gender or eye colour To add more value we can construct different groupings of eye colours and genders summing the number of records at each intersection Eye Colour___ Gender Count pp Bue Female 0 we Te Male j4 Female Male Total Brown Female 2 Blue Jo J4 J4 Male 1 JOR Brow 2 Ji 03 Hazel Female 1 Hazel Ji 1 J2 Male 1 Green 1 fo 1 Green Female 1 Totai 4 6 10 Male OA Total OOOO Importantly in every case the total number of records equals 10 We would not usually group on the names because this would not summarize our data We would still end up with 10 rows gt Open the Basic Pivot table on the AEI website AEI International Student Data User Guide December 2008 www aei gov au 16 Structure of a Pivot Table Page Fields ae Se Se ae ae a ae Em 1 Page Field Item 2 Month E Nationality F Column Field Column Items 5 Data Field E Year Row Field F ticos 2002 2003 2004 2005 20062007 2008 ROW 3 ELICOS 44 294 47 358 48 527 48493 56 736 75 793 92 627 ct 21 567 25 335 26 031 23878 23 028 25181 27 541 VET 47 201 50 207 51 471 571 509 70 928 102655 147 073 11 Higher Education 112 563 131 745 147 723 159 518 165 984 171 392 179 136 12 Other 22 070 24079 24 201 24347 24479 25352 28 012 Grand Total 247695 Diagram 1 Structure of a Pivot Table The following points refer to the example abo
22. et The survey covered ELICOS enrolments and is available through the following links e English Australia Survey of Major ELICOS Regional Markets in 2007 pdf 120kb e English Australia Survey of Major ELICOS Regional Markets in 2006 pdf 116kb e English Australia Survey of Major ELICOS Regional Markets in 2005 pdf 1650kb e English Australia Survey of Major ELICOS Regional Markets in 2004 pdf 72kb e English Australia Survey of Major ELICOS Regional Markets in 2003 e English Australia Survey of the ELICOS Market in 2002 Study in Australia Website Traffic Reports Regular monthly report on traffic figures through AEI s student website Study in Australia The WHAT S NEW box at the top right has a link to the most recent pivot tables There is a Search link on the AEI Online website The screenshot below highlights this _ Australian Government Australian Education International Market Information Government About AEI Activities AEI International Student Data User Guide shopping cart myaccount login I contacts 3E L Offshore Support Publications amp Research Qualifications Events Recognition Media December 2008 www aei gov au 8 International student data The International Student Data page includes Monthly Summaries of International Student Enrolment Data About AEI b OVERVIEW b CURRENT MARKET INFORMATION b APPLY FOR MIP b E NEWSLETTER b ITEMS OF INTEREST
23. ic Pivot Table practise moving and stacking fields filtering and selecting data drilling through sorting by item lists and data values Summarising Data in Pivot Tables There are many ways to summarise the data In a pivot table As well as simple aggregations of data you can find differences percentages contributions and more You can concurrently show different kinds of summaries for the same data field For example you can show the year to date enrolment volumes the change on the previous year and the percentage change We will investigate how to use different types of summaries in the exercise below gt EXERCISE 1 Use the AEI Basic Pivot Table to show Year to Date Enrolments Differences from Previous Year and Percentage Growth by Sector and Year 1 Close and re open the AEl Basic Pivot Table 2 Organise the table as follows o Month Sector State and Nationality fields are in the Page Area o The Year field is in the Column Area AEI International Student Data User Guide December 2008 www aei gov au 24 o The DATA YTD Enrolments field is in the Data Area 3 The DATA YTD Commencements field is not displayed Open the Data item selector click the item called Sum of Data YTD Commencements to de select it and click OK 4 Check that the Month field has a filter for the current month applied 5 Open the Pivot Table Field List right click in the data area Show Field List 6 Drag the field called DATA YTD Enrolment
24. ional student activity and includes the range of AEl statistical material As well as pivot tables the website includes many reports newsletters explanatory notes and snapshots of AEl data Navigating to the AEl data The screenshot below is the AEI Online home page as at September 2008 It may not look exactly like this when you open the page but the panel on the right should be there with the link to International Student Data Click the International Student Data link indicated below A Australian Government contacts search products amp services shopping cart my account login x Australian Education International Government Market Offshore Publications amp Qualifications Activities Information Events Support Research Recognition Media About AEI Link to AEI Data Who we are OA A _ A__A gt International Australian Education International AEI is the international arm of the Department of Education Employment and Workplace Student Data Relations With around 370 000 students from 190 countries studying in Australia and another 120 000 enrolments in Australian courses Country Education Profiles Online offshore we have plenty to do to support the industry Transnational What we do l l o o Quality Strategy AEI works with industry and governments to support the international education industry which is Australia s third largest export earner bringing in around 12 5 billion in 2007 Flw ESOS B
25. ip window To unzip and open the pivot table file just double click the file name The file will automatically unzip and open Detailed Monthly Tables PDF and Excel formats 2008 Detailed Monthly Tables The detailed monthly tables expand on the information provided in tables one to six These tables include information on monthly enrolments commencements and year to date data Monthly data adds new enrolments for the month to the number at the start of month Tables 7 to 12 contain information about each Sector by top 10 Nationalities and State and Territory The focus of these tables has changed from counting the enrolment and commencement activity during the reference month to counting the year to date activity up to and including the reference month The data in the following tables is updated throughout the year resulting in some revisioin to previously published data Note Items marked with are available only to organisations who have access to AEI s Market Information Package MIP For non MIP clients access arrangements are outlined in Accessing AEI Monthly Statistics Tables 7 to 12 PDF January pdf 173kb February pdf 173kb March pdf 172kb April pdf 179kb May pdf 173kb June pdf 179kb July pdf 173kb August pdf 172kb September October November December Monthly Tables 7 to 12 Available to all users PDF Format Tables 13 to 26 PDF January pdf 536kb February pdf 604kb March pdf 59
26. l Sum of DATA YTD Enrolments 247 695 278 724 297 953 313 745 341 15 400 373 23 Total Growth 31029 19229 15792 27410 Total Growth AEI International Student Data User Guide December 2008 www aei gov au 27 The table now shows e Enrolment volumes Sum of Data YTD Enrolments Volume growth on the previous year Growth Percentage growth on the previous year Growth e For year to date August of each year displayed Month filter Aug e By Sector and Year no filters applied to these fields Creating and using Pivot Charts Pivot Charts are a powerful and flexible way of visually representing data contained in pivot tables The look and feel of the chart can be changes and the fields represented in the chart can be dragged and dropped and filtered in the same way as in the pivot table Pivot Charts are linked to pivot tables They are usually created in a separate worksheet in the pivot table file We will investigate how to use different types of summaries in the exercise below gt EXERCISE 2 Create a Pivot Chart to show Year to Date Enrolments by Year and Sector 1 Close and re open the AEl Basic Pivot Table 2 Organise the table as follows o Month State and Nationality fields are in the Page Area o The Year field is in the Column Area o The Sector field is in the Row Area o The DATA YTD Enrolments field is in the Data Area o The DATA YTD Commencements field is not displayed Open the Data item sel
27. road Field of Education Narrow Field of Education Detailed Field of Detailed Pivot Table Education Level of study Region and Provider type 2008 zip 45 53 MB Warning Unzipped file is approximately 348Mb AEI advises that the pivot table is compatible with Excel 2003 or later To assist you with using the AEI Pivot Tables is the AEI User Guide to International Student Data which provides some basics to get you started with using the pivot tables Return to Top The Basic Pivot Table e is available for free to all users of the internet e contains a limited number of variables but provides sufficient information to understand the major trends in Australia s international education enrolments and commencements since 2002 The Standard Pivot Table and Detailed Pivot Table e contain additional variables as detailed in the boxes to the right of the links e Access to these files is restricted to subscribers to the Market Information Package MIP Downloading AEI pivot tables and using Zip files Notes This part of the manual applies to computers using windows operating systems only WinZip must be installed on the destination computer The excel files available on the AEl website are converted into zip files AEI International Student Data User Guide December 2008 www aei gov au 12 A zip file is contains all the information in a normal file but is a fraction of the size of the normal file This minimises
28. s into the data area 7 Repeat step 6 8 Drag the Data field to the Row Area Your pivot table should now look like this the Month filter and data may be different 3 sae O a S ES 2004 2005 2006 2007 2000 Sum of DATA YTD Enrolments i 3 313 341 iT a EJ Sum of DATA YTD Enrolments 247695 278724 297953 31 3745 3411 55 400373 474389 Sum of DATA YTD Enrolments3 247695 270724 297953 313745 341155 400373 474309 9 Right click on the cell called Sum of YTD Enrolments2 and select Field Settings The PivotTable Field dialogue box opens AEI International Student Data User Guide December 2008 www aei gov au 25 10 11 12 13 14 15 16 17 18 19 Pirvotable Field Source field DATA YTO Enrolme Format Cells l PivotChart PivotTable Wizard Refresh Data Select Group and Show Detail Order Table Options Hide PivotTable Toolbar ie y PER Hide Field List DATA YTD Enrolmen E In the Name box type Growth Click the Options gt gt button This reveals the Show data as component Click the drop down button currently set to Normal Select Difference From from the Show data as box Select Year from the Base field box Select Previous from the Base item box Click OK Right click on the cell called Sum of YTD Enrolments3 and select Field Settings The PivotTable Field dialogue box opens In the Name box type Growth Click the Options gt gt
29. stakeholders with the findings of more detailed analysis of AEI s international student database Using Pivot Tables in Excel Pivot Tables are a powerful yet easy to use data analysis tool that enables you to reorganise filter group and summarise data by dragging and dropping variables into and out of a table The data can be located within the excel file itself or the pivot table can be linked to an external data source such as an Access database In this part of the manual some of the information is presented as text and images and other information is presented in the form of exercises Understanding Grouping Before starting to use Pivot Tables it is important to understand the concept of grouping Consider the list below of fictional office staff Barnsev Male Blue Tezza Female Brown Kazza Female Hazel Davo Male Blue AEI International Student Data User Guide December 2008 www aei gov au 15 There are 10 records in the list The name column indentifies individuals but the gender and eye colour columns do not The name column means we must have one record per individual For analysis purposes we may not need to know the names of individuals so we can group the data and count the records for each entry Gender Count Eyecoiow Count Female fa e qa me 6 Brown Total 1000 0 IS EAU Do o o sem tat E Now instead of 10 rows we have 2 rows for Gender and 4 rows for Eye Colour but we can no long
30. t the AN items based on their data values 4 Grouping Items You may wish to group part of an item list and view the remaining items as individual items For instance you may wish to look at data for New South Wales Victoria and Queensland as individual states but group all the other states into a single item and show aggregate data for the group When items are grouped like this the data aggregates based on the groupings To group items e Locate the field containing the items to be grouped in a row or column area For this example use Row Area e Using the mouse and holding down the Ctrl key select the items to be grouped In the example above this would be Western Australia South Australia Australian Capital Territory Tasmania and the Northern Territory AEI International Student Data User Guide December 2008 www aei gov au 23 e Right click in the selected cells and select Group and Show Detail gt Group e The table now has a new field called State2 added o Inthe State2 field the grouped states are collectively shown as Group1 o Aggregations in the data area reflect the grouping o The State field remains in the Row Area This can be moved to the Page Area e See the example below A A A A 1 Month Aug lw 2 Sector Jf AM fr 3 Nationality am All 2003 2004 2005 2006 2007 106 346 157 801 78 392 119 627 44 466 59 004 49 518 63 949 313 45 341 155 Using the AEI Bas
31. the space the file takes up on the AEI server and reduces the volume of data that is downloaded When you download a pivot table file off the AEI website the file needs to be unzipped or uncompressed before it can be used WinZip is the program used to do this To download unzip and use AEI pivot tables e Navigate to the AEI website page with the pivot tables e Click the pivot table for downloading In this example it s the Basic Pivot Table Includes the variables Sector Nationality Month and Year for year to date Basic Pivot Table 2008 enrolments and commencements zip 7 19MB AEI advises that the pivot table is compatible with Excel 2003 or later The dialogue box below opens rile Download Do you want to open or save this file a Name 2008 Basic Pivot zip Type WinZip File 7 41MB From a i gov au e Always ask before opening this type of file Be While files from the Intemet can be useful some files can potentially g ham your computer f you do not trust the source do not open or save this file What s the risk AEI International Student Data User Guide December 2008 www aei gov au 13 Click Open This will activate WinZip WinZip 2008 Basico Pivotid par File Actions Options Help Wizard Type Modified Size Ratio Packed Path Microsoft Ex 21 10 2008 15 39 69 969 408 89 7 774 Selected O files O bytes Total 1 file 68 330KB The pivot table file can be seen in the WinZ
32. ummaries determined by the organisation of the fields in the row page and column areas e Inthe examples above the data field is DATA YTD Enrolments e The type of summary used in the example is SUM giving Sum of DATA YTD Enrolments e See below for more way to summarised the data IMPORTANT When using AEI pivot tables data fields are prefixed by the word DATA These are the only fields that should be placed in the data area Row Area e Pale Yellow highlight in diagram 1 e The items of fields placed in the row area will be displayed and the summaries created will form the rows of the data area e Inthe example above the Sector field is in the row area e For fields in the row area when the items are revealed selection boxes appear next to each item enabling selection of specific items Column Area e Blue Yellow highlight in diagram 1 e The items of fields placed in the column area will be displayed and the Summaries created will form the columns of the data area e Inthe example above the Year field is in the row area e The same item selection rules apply to fields in the row area and column areas e Note that in Excel 2003 and previous versions the maximum number of columns is 256 If you try to add a field with more than 255 items to the AEI International Student Data User Guide December 2008 www aei gov au 18 column area the following dialogue box will appear The field you are dropping into the column area
33. ummary of how to use Pivot Charts There are many other things you can do such as change the chart type show 2 types of summary on the same chart manipulate colours use calculated fields and more Interpreting Data in Pivot Tables Understanding the fields in a pivot table is a critical aspect of their successful use Invalid interpretations of data can occur when field are not properly understood AEI International Student Data User Guide December 2008 www aei gov au 31 This is particularly the case when using the data fields in the AEl pivot tables The data fields are aggregated each month through the year So the March Enrolment and Commencements data is the sum of the January February and March data So you would not sum the year to date enrolments or commencements data over 2 months say July and August because you would them be adding together the data from J anuary to July AND January to August giving a highly inflated result Therefore in most cases you would locate the Month field in the Page Area and filter on the month required For year to date information it would be the month of the most recent data update For calendar year data you would set the month to December In this case you would not see any data for the current year Looking at the fields we have we have used so far The number of enrolments from 01 January to the end Sum of YTD Enrolments of the reference month in the reference year This is the difference
34. ut maybe even more importantly what we do helps people and institutions around the world create long term personal cultural E and intellectual relationships whose benefits reach well beyond bottom lines business meetings and technology based transactions As part of the Department of Education Employment and Workplace Relations AEI has a unique advantage No other country Australian Scholarships integrates three critical components government to government relations national quality systems and export marketing support for industry as Australia does for international education through AEI STUDY IN DS aua Home Site Map Feedback Privacy Disclaimer Copyright Australian Government i Live Learna Grow AEI International Student Data User Guide December 2008 www aei gov au 7 From the AEI Statistics main page you can access a range of AEI statistical material including e International Student Data e Research Snapshots e Market Data Snapshots e other useful research material A Australian Government Australian Education International Sree contacts search products amp services shopping cart my account login Nes Qualifications Recognition Publications amp Research Offshore Support Government About AEI Activities Events Media OVERVIEW WHAT S NEW CURRENT MARKET Statistics INFORMATION International Student Data Latest Pivot Table July 2008 Monthly Summary Ouly
35. ve Fields e Inthe pivot table you will see a series of fields such as Month State Nationality Sector Year and Sum of DATA YTD Enrolments e Within the fields are Items For example the items of the field called Sector are o ELICOS o Schools o VET o Higher Education o Other e Fields enable the data to be either filtered or summarised e There must also be at least one data field in the table to provide the numbers for the summary In the example above it is DATA YTD Enrolments e All of the fields can be moved with the mouse to different areas in the pivot table to give different summaries of the data e Clicking the down arrow at the right of the field name will reveal the item selector of the field showing the items that make it up AEI International Student Data User Guide December 2008 www aei gov au 17 Page Area e Bright Yellow highlight in diagram 1 e The page area is used to locate the fields used for filtering the data e t can also be used as a convenient place to locate fields not currently being used also see the Pivot Table Field List e When viewing the items of a field that is in the Page Area only one item can be selected for filtering In the example above the Month State and Nationality fields are in the PAGE area and the Month field has been set to Aug This filter is reflected in the numbers in the data area Data Area e Green highlight in diagram 1 e This area contains the numeric s
36. y Sector Monthly Time Series of Student Enrolments for 2008 Summaries by Sector The following tables give a month by month analysis of international student data by sector including information on enrolments commencements finishes and year to date totals More detailed data are contained in the 2008 Detailed Monthly Tables section below Changes to the monthly data AEI has made some changes to AEI international student enrolment data to enhance the usefulness of the data to industry In reponse to industry feedback about the reliablility of current finishes data these data have been removed from Tables 1 to 6 AEI will investigate whether a more accurate indicator can be used to determine the reason for an enrolment ceasing Please click on the images below to view the tables individually or a full set of the tables 1 to 6 pdf 105kb are available to download Table 1 All Sectors Table 2 ELICOS Table 3 Higher Education Table 4 VET Table 5 Schools Table 6 Other Return to Top e These tables give a month by month analysis of international student enrolments and commencements by sector and for all sectors combined You can either o Click on the charts individually to view them in a new browser window or o Click the tables 1 to 6 link highlighted in the red box above This opens a freely available PDF file containing data and charts of AEI data up to the current reference month AEI International Student Data User
37. y benefits of internationalisation We seek to achieve this goal through an integrated approach to policy regulation international engagement and promotion We encourage and facilitate Australian activity through our national office in Canberra and 25 offices in Australian diplomatic missions in 17 economies What AEI does Policy development and advice With strong links to all education sectors and all levels of government AEI provides a national focus for debate and policy development International and government to government engagement AEl represents Australia s interests in international and multilateral bodies and in negotiating agreements with governments around the world We work with state and territory governments to coordinate international education policy and practice Quality assurance and consumer protection Through the Education Services for Overseas Students ESOS legislation AEI helps safeguard the quality and reputation of Australia s courses and institutions We administer world s best practice in student consumer protection Qualifications recognition AEI provides information on how Australian and overseas qualifications compare and helps overseas qualified people study and work in Australia Market research and analysis AEI produces research and analysis to provide industry with intelligence and advice on education markets around the world AEI International Student Data User Guide December
Download Pdf Manuals
Related Search