Expert answer:Need help with Excel homework
Answer & Explanation:question requirements in a9 pdfA9.pdf A9 data.xlsx
a9.pdf
a9_data.xlsx
Unformatted Attachment Preview
Part 1 (40 points)
The following steps assumed that you have downloaded right version for your computer and installed
and enabled Power Query. If your installation is correct you should see the Power Query Tab when you
launched your Excel.
Download the data file and save to your computer.
Click on the Power Query Tab, then click the From File icon and select From Excel.
Navigate to where you save the data file (a9 data.xlsx) and select it. Then click the OK button.
You should see the Navigator window like the following.
Click the Population table and then click edit button on the right bottom of the Navigator window
In this case Power Query would have selected the first row as column headings.
We do not need the source column, so delete it by right clicking the column heading and the whole
column will be highlighted and a window will be displayed. Click Remove.
Now we need to change the data type of the date column to date. To do this, right click the date
column headings and follow the screen shot below.
Your Date column should like the following:
Following the above procedure, change the data type of the Population column to Whole Numbers.
You will also need to shorten the Country (or dependent territory) to Country. To do this, double click the
header then type the new format.
Your table in the Query editor should look like the following:
Next we would have to change the data type in the last column (the % of world) into numbers. If you
follow the steps above you will get errors in the whole column.
To delete the last step of the query, click on the X in the APPLIED STEPS windows on the right and your
table will revert back to the previous state.
Therefore to change the data type to decimal you first need to remove the % symbol then change the
data type to decimal.
To remove the % symbol, right click the column heading (% of world), select Replace Values…
In the Replace Values window, type the % in the Value to Find box, then click OK
You should have the % of world column that looks like the following:
Also you will need to change the Rank column to a number format.
You are done!!. Click the Close & Load button on the top left corner of the Query Editor. Rename your
worksheet as Part 1.
Part 2 (60 points)
In this part of the assignment we will use an updated data set from Wikipedia.
Add a new worksheet to your workbook. Click on Power Query Tab and then click on
In the search box under the Data Catalog search on the top left corner, type “population by countries”
You will notice that this data set is from Wikipedia.
If you move your mouse over the Countries – List of countries… you will see the following:
Click on edit button and Power Query editor will load the file into the query editor window.
Go through the same process as Part 1 to obtain the same number of columns and data type.
Save the worksheet as Part 2.
Submit your work book to BlackBoard using the same naming convention.
Rank
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
Country (or dependent
Population
territory) Date
% of world population
Source
China
1,368,570,000 March 6, 2015
18.9%
Official population clock
India
1,267,830,000 March 6, 2015
17.5%
Official population clock
United States
320,529,000
March 6, 2015
4.43%
Official population clock
Indonesia
255,461,700
July 1, 2015
3.53%
Official estimate
Brazil
203,975,000
March 6, 2015
2.82%
Official population clock
Pakistan
189,150,000
March 6, 2015
2.62%
Official population clock
Nigeria
183,523,000
July 1, 2015
2.54%
UN projection
Bangladesh
157,941,000
March 6, 2015
2.18%
Official population clock
Russia
146,270,033
January 1, 2015 2.02%
Official estimate
Japan
126,970,000
February 1, 2015 1.76%
Monthly official estimate
Mexico
121,005,815
July 1, 2015
1.67%
Official projection
Philippines
101,098,400
March 6, 2015
1.4%
Official population clock
Vietnam
90,730,000
July 1, 2014
1.26%
Annual official estimate
Ethiopia
90,076,012
July 1, 2015
1.25%
Official projection
Egypt
88,123,300
March 6, 2015
1.22%
Official population clock
Germany
80,925,000
June 30, 2014
1.12%
Official estimate
Iran
78,165,200
March 6, 2015
1.08%
Official population clock
Turkey
77,695,904
December 31, 20141.07%
Annual official estimate
Democratic Republic
71,246,000
of the CongoJuly 1, 2015
0.99%
UN projection
France
66,104,000
February 1, 2015 0.91%
Monthly official estimate
Thailand
64,871,000
July 1, 2014
0.9%
Official annual projection
United Kingdom 64,105,654
July 1, 2013
0.89%
Annual official estimate
Italy
60,782,309
September 30, 20140.84%
Monthly official estimate
South Africa
54,002,000
July 1, 2014
0.75%
Official estimate
Burma
51,419,420
March 29, 2014 0.71%
Preliminary 2014 census result
South Korea
51,342,881
January 1, 2015 0.71%
Official projection
Colombia
48,025,400
March 6, 2015
0.664%
Official population clock
Tanzania
47,421,786
July 1, 2014
0.66%
Official Projection
Kenya
46,749,000
July 1, 2015
0.65%
UN projection
Spain
46,464,053
July 1, 2014
0.64%
Official estimate
Argentina
43,131,966
July 1, 2015
0.6%
Official annual projection
Ukraine
42,928,900
January 1, 2015 0.59%
Monthly official estimate
Algeria
39,500,000
January 1, 2015 0.55%
Official estimate
Poland
38,484,000
December 31, 20140.53%
Official estimate
Sudan
38,435,252
July 1, 2015
0.53%
Official annual projection
Iraq
36,004,552
July 1, 2014
0.5%
Official annual projection
Canada
35,675,834
October 1, 2014 0.49%
Official estimate
Uganda
34,856,813
August 28, 2014 0.48%
Preliminary 2014 census result
Morocco
33,543,100
March 6, 2015
0.464%
Official population clock
Saudi Arabia
31,521,418
July 1, 2015
0.44%
Official annual projection
Peru
31,151,643
July 1, 2015
0.43%
Official annual projection
Venezuela
30,620,404
July 1, 2015
0.42%
Official annual projection
Malaysia
30,511,900
March 6, 2015
0.422%
Official population clock
Uzbekistan
30,492,800
January 1, 2014 0.42%
Official estimate
Nepal
28,037,904
July 1, 2015
0.39%
Official annual projection
Ghana
27,043,093
July 1, 2014
0.37%
Official annual projection
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
Afghanistan
26,556,800
Yemen
25,956,000
Mozambique
25,727,911
North Korea
25,155,000
Angola
24,383,301
Australia
23,766,500
Taiwan
23,440,278
Syria
23,087,363
Ivory Coast
22,671,331
Madagascar
21,842,167
Cameroon
21,143,237
Sri Lanka
20,359,439
Romania
19,942,642
Niger
19,268,000
Burkina Faso
18,450,494
Chile
18,006,407
Kazakhstan
17,417,500
Netherlands
16,892,500
Malawi
16,310,431
Mali
16,259,000
Ecuador
15,945,200
Guatemala
15,806,675
Zambia
15,473,905
Cambodia
15,405,157
Chad
13,606,000
Senegal
13,508,715
Zimbabwe
13,061,239
South Sudan
11,892,934
Bolivia
11,410,651
Belgium
11,237,160
Cuba
11,210,064
Somalia
11,123,000
Rwanda
10,996,891
Greece
10,992,589
Tunisia
10,982,754
Haiti
10,911,819
Guinea
10,628,972
Czech Republic 10,528,477
Portugal
10,477,800
Dominican Republic
10,378,267
Benin
10,315,244
Hungary
9,849,000
Burundi
9,823,827
Sweden
9,753,627
Azerbaijan
9,593,000
United Arab Emirates
9,577,000
Belarus
9,481,000
July 1, 2014
0.37%
July 1, 2014
0.36%
July 1, 2015
0.36%
July 1, 2015
0.35%
May 16, 2014
0.34%
March 6, 2015
0.329%
January 31, 2015 0.32%
March 6, 2015
0.32%
May 15, 2014
0.31%
July 1, 2013
0.3%
July 1, 2013
0.28%
March 19, 2012 0.28%
January 1, 2014 0.28%
July 1, 2015
0.27%
July 1, 2015
0.26%
July 1, 2015
0.25%
January 1, 2015 0.24%
March 6, 2015
0.234%
July 1, 2015
0.23%
July 1, 2015
0.22%
March 6, 2015
0.22%
July 1, 2014
0.22%
July 1, 2015
0.21%
July 1, 2015
0.21%
July 1, 2015
0.19%
November 19, 20130.19%
August 17, 2012 0.18%
July 1, 2015
0.16%
July 1, 2015
0.16%
January 1, 2015 0.16%
December 31, 20130.16%
July 1, 2015
0.15%
July 1, 2014
0.15%
January 1, 2014 0.15%
April 23, 2014
0.15%
July 1, 2015
0.15%
April 2, 2014
0.15%
September 30, 20140.15%
December 31, 20130.15%
July 1, 2014
0.14%
July 1, 2015
0.14%
December 31, 20140.14%
July 1, 2015
0.14%
January 31, 2015 0.13%
January 1, 2015 0.13%
July 1, 2015
0.13%
January 1, 2015 0.13%
Official estimate
Official estimate
Annual official projection
UN projection
Preliminary 2014 census result
Official population clock
Monthly official estimate
Official population clock
Preliminary 2014 Census Result
Annual official estimate
Official estimate
Final 2012 Census Result
Annual official estimate
UN projection
Official estimate
Official annual projection
Monthly official estimate
Official population clock
Official annual projection
UN projection
Official population clock
Official estimate
Official annual projection
Official annual projection
UN projection
2013 census result
2012 Census Result
Official annual projection
Official projection
Monthly official estimate
Annual official estimate
UN projection
Annual official estimate
Official estimate
Preliminary 2014 census result
Official projection
Preliminary 2014 census result
Official quarterly estimate
Annual official estimate
Official estimate
Official projection
Annual official estimate
Official annual projection
Monthly official estimate
Official estimate
UN projection
Quarterly official estimate
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
Honduras
8,725,111
Austria
8,579,747
Tajikistan
8,354,000
Israel
8,309,400
Switzerland
8,211,700
Papua New Guinea
7,398,500
Hong Kong (China)
7,264,100
Bulgaria
7,245,677
Togo
7,171,000
Serbia
7,146,759
Paraguay
7,003,406
Laos
6,802,000
Eritrea
6,738,000
Jordan
6,698,310
El Salvador
6,401,240
Sierra Leone
6,319,000
Libya
6,317,000
Nicaragua
6,134,270
Kyrgyzstan
5,895,100
Denmark
5,659,715
Finland
5,475,526
Singapore
5,469,700
Slovakia
5,421,034
Norway
5,165,802
Central African Republic
4,803,000
Costa Rica
4,773,130
Turkmenistan 4,751,120
Republic of the Congo
4,671,000
Ireland
4,609,600
New Zealand
4,566,220
Palestine
4,550,368
Liberia
4,503,000
Georgia
4,490,500
Croatia
4,267,558
Oman
4,130,593
Lebanon
4,104,000
Bosnia and Herzegovina
3,791,622
Panama
3,764,166
Mauritania
3,631,775
Moldova
3,557,600
Puerto Rico (U.S.)3,548,397
Uruguay
3,404,189
Kuwait
3,268,431
Armenia
3,013,900
Mongolia
3,000,000
Lithuania
2,919,306
Albania
2,893,005
July 1, 2014
0.12%
January 1, 2015 0.12%
January 1, 2015 0.12%
January 31, 2015 0.11%
September 30, 20140.11%
July 1, 2013
0.102%
December 31, 20140.1%
December 31, 20130.1%
July 1, 2015
0.099%
January 1, 2014 0.099%
2015
0.097%
July 1, 2015
0.094%
July 1, 2015
0.093%
March 6, 2015
0.0927%
2014
0.089%
July 1, 2015
0.087%
July 1, 2015
0.087%
2013
0.085%
January 1, 2015 0.082%
January 1, 2015 0.078%
February 1, 2015 0.076%
July 1, 2014
0.076%
September 30, 20140.075%
January 1, 2015 0.071%
July 1, 2015
0.066%
June 30, 2014
0.066%
December 27, 20120.066%
July 1, 2015
0.065%
April 1, 2014
0.064%
March 6, 2015
0.0632%
July 1, 2014
0.063%
July 1, 2015
0.062%
January 1, 2014 0.062%
July 1, 2012
0.059%
February 18, 2015 0.057%
July 1, 2012
0.057%
October 15, 2013 0.052%
July 1, 2015
0.05%
July 1, 2015
0.05%
January 1, 2014 0.049%
July 1, 2014
0.049%
June 30, 2014
0.047%
July 1, 2012
0.045%
September 30, 20140.042%
January 24, 2015 0.041%
February 1, 2014 0.04%
January 1, 2015 0.04%
Annual official estimate
Quarterly provisional figure
Official estimate
Official monthly estimate
Quarterly provisional figure
Annual official estimate
Official estimate
Official estimate
UN projection
Annual official estimate
Official estimate
Annual official projection
UN projection
Official population clock
Official estimate
UN projection
UN projection
Official estimate
Official estimate
Quarterly official estimate
Official population clock
Official estimate
Official estimate
Quarterly official estimate
UN projection
Official estimate
Preliminary 2012 census result
UN projection
Annual official estimate
Official population clock
Official estimate
UN projection
Annual official estimate
Annual official estimate
Weekly official estimate
Official estimate
Preliminary 2013 census result
Official estimate
Annual official estimate
Official estimate
Official estimate
Annual official estimate
Official estimate
Quarterly official estimate
Official estimate
Monthly official estimate
Annual official estimate
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
Jamaica
2,717,991
Qatar
2,334,029
Lesotho
2,120,000
Namibia
2,113,077
Macedonia
2,065,769
Slovenia
2,065,857
Botswana
2,024,904
Latvia
1,986,700
The Gambia
1,882,450
Kosovo
1,827,231
Guinea-Bissau 1,788,000
Gabon
1,751,000
Equatorial Guinea1,430,000
Trinidad and Tobago
1,328,019
Bahrain
1,316,500
Estonia
1,312,252
Mauritius
1,261,208
East Timor
1,212,107
Swaziland
1,119,375
Djibouti
900,000
Fiji
859,178
Cyprus
858,000
Réunion (France)844,994
Comoros
763,952
Bhutan
757,940
Guyana
746,900
Macau (China) 631,000
Montenegro
620,029
Western Sahara 604,000
Solomon Islands 581,344
Luxembourg
549,700
Suriname
534,189
Cape Verde
518,467
Transnistria
505,153
Malta
425,384
Guadeloupe (France)
405,739
Brunei
393,372
Martinique (France)
386,486
The Bahamas
368,390
Belize
349,728
Maldives
341,256
Iceland
329,040
Northern Cyprus 294,906
Barbados
285,000
New Caledonia (France)
268,767
French Polynesia 268,270
(France)
Vanuatu
264,652
December 31, 20130.038%
February 28, 2015 0.032%
July 1, 2015
0.029%
August 28, 2011 0.029%
December 31, 20130.029%
March 6, 2015
0.029%
August 22, 2011 0.028%
February 1, 2015 0.027%
April 15, 2013
0.026%
2015
0.025%
July 1, 2015
0.025%
July 1, 2015
0.024%
July 1, 2013
0.02%
January 9, 2011 0.018%
July 1, 2014
0.018%
January 1, 2015 0.018%
July 1, 2014
0.017%
July 1, 2014
0.017%
July 1, 2015
0.015%
July 1, 2015
0.012%
July 1, 2013
0.0119%
January 1, 2014 0.012%
January 1, 2014 0.012%
July 1, 2014
0.011%
March 6, 2015
0.0105%
July 1, 2013
0.01%
September 30, 20140.0087%
April 1, 2011
0.0086%
July 1, 2015
0.0084%
July 1, 2013
0.008%
December 31, 20130.0074%
August 13, 2012 0.0074%
July 1, 2014
0.0072%
January 1, 2014 0.007%
December 31, 20130.0059%
January 1, 2013 0.0056%
June 20, 2011
0.0054%
January 1, 2013 0.0053%
July 1, 2013
0.0051%
July 1, 2013
0.0048%
September 20, 20140.0047%
December 31, 20140.0046%
April 30, 2006
0.004%
July 1, 2013
0.0039%
August 26, 2014 0.0037%
August 22, 2012 0.0037%
July 1, 2013
0.0037%
Annual official estimate
Monthly official estimate
UN projection
Final 2011 census result
Official estimate
Official population clock
Final 2011 census result
Monthly official estimate
Preliminary 2013 census result
Official annual projection
UN projection
UN projection
Annual official estimate
2011 census result
Official annual projection
Official estimate
Official estimate
Official estimate
Official projection
UN projection
Annual official estimate
Official estimate
Official annual estimate
Official estimate
Official population clock
Official estimate
Official quarterly estimate
Final 2011 census result
UN projection
Annual official estimate
Annual official estimate
Preliminary 2012 census result
Official annual projection
Official estimate
Official annual estimate
Official annual estimate
Preliminary 2011 census result
Official annual estimate
Annual official estimate
Official estimate
Preliminary 2014 census result
Official quarterly estimate
Official census
Official estimate
Preliminary 2014 census result
Preliminary 2012 census result
Annual official estimate
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
Abkhazia
240,705
2011
0.0033%
French Guiana (France)
239,648
January 1, 2012 0.0033%
Mayotte (France)212,645
August 21, 2012 0.0029%
Samoa
187,820
November 7, 2011 0.0026%
São Tomé and Príncipe
187,356
May 13, 2012
0.0026%
Saint Lucia
185,000
July 1, 2015
0.0026%
Guam (U.S.)
159,358
April 1, 2010
0.0022%
Curaçao (Netherlands)
154,843
January 1, 2014 0.0021%
Saint Vincent and109,000
the Grenadines July 1, 2015
0.0015%
Aruba (Netherlands)
107,394
October 31, 2014 0.0015%
Kiribati
106,461
July 1, 2013
0.0015%
United States Virgin
106,405
Islands (U.S.) April 1, 2010
0.0015%
Grenada
103,328
May 12, 2011
0.0014%
Tonga
103,252
November 30, 20110.0014%
Federated States 101,351
of Micronesia July 1, 2013
0.0014%
Jersey (UK)
99,000
December 31, 20120.0014%
Seychelles
89,949
July 1, 2013
0.0012%
Antigua and Barbuda
86,295
May 27, 2011
0.0012%
Isle of Man (UK) 84,497
March 27, 2011 0.0012%
Andorra
76,949
July 1, 2014
0.0011%
Dominica
71,293
May 14, 2011
0.00099%
Bermuda (UK) 64,237
May 20, 2010
0.00089%
Guernsey (UK) 63,085
March 31, 2012 0.00087%
Marshall Islands 56,086
July 1, 2013
0.00078%
Greenland (Denmark)
55,984
January 1, 2015 0.00077%
Cayman Islands (UK)
55,691
2013
0.00077%
American Samoa 55,519
(U.S.)
April 1, 2010
0.00077%
Saint Kitts and Nevis
55,000
July 1, 2015
0.00076%
Northern Mariana53,883
Islands (U.S.) April 1, 2010
0.00075%
South Ossetia 51,547
January, 2013
0.00071%
Faroe Islands (Denmark)
48,679
December 1, 2014 0.00067%
Sint Maarten (Netherlands)
37,429
January 1, 2010 0.00052%
Liechtenstein
37,132
December 31, 20130.00051%
Monaco
36,950
December 31, 20130.00051%
Collectivity of Saint
35,742
Martin (France)
January 1, 2012 0.00049%
San Marino
32,789
December 31, 20140.00045%
Turks and Caicos Islands
31,458 (UK)
January 25, 2012 0.00044%
Gibraltar (UK) 30,001
December 31, 20120.00041%
Åland Islands (Finland)
28,875
September 30, 20140.0004%
British Virgin Islands
28,054
(UK)
July 12, 2010
0.00039%
Caribbean Netherlands
23,296(Netherlands)
January 1, 2013 0.00032%
Palau
20,901
July 1, 2013
0.00029%
Cook Islands (New14,974
Zealand)
December 1, 2011 0.00021%
Anguilla (UK)
13,452
May 11, 2011
0.00019%
Wallis and Futuna13,135
(France)
July 1, 2013
0.00018%
Tuvalu
11,323
July 1, 2013
0.00016%
Nauru
10,084
October 30, 2011 0.00014%
Official census
Annual official estimate
2012 census result
Final 2011 census result
2012 census result
UN projection
Final 2010 census result
Annual official estimate
UN projection
Official quarterly estimate
Annual official estimate
Final 2010 census result
2011 census result
2011 census result
Annual official estimate
Annual official estimate
Annual official estimate
Preliminary 2011 census result
2011 census result
Annual official estimate
Preliminary 2011 census result
Final 2010 census result
Annual official estimate
Annual official estimate
Annual official estimate
Official estimate
Final 2010 census result
UN projection
Final 2010 census result
Estimate
Monthly official estimate
Official estimate
Semi annual official estimate
Annual official estimate
Annual official estimate
Monthly official estimate
2012 census result
Annual official estimate
Official estimate
2010 census result
Official estimate
Annual official estimate
Final 2011 census result
Preliminary 2011 census result
Annual official estimate
Annual official estimate
2011 census result
235
236
237
238
239
240
241
242
243
244
245
246
247
Saint Barthélemy 9,131
(France)
January 1, 2012 0.00013% Annual official estimate
Saint Pierre and Miquelon
6,069
(France)January 1, 2012 0.000084% Annual official estimate
Montserrat (UK) 4,922
May 12, 2011
0.000068% 2011 census result
Saint Helena, Ascension
4,000 and Tristan
July
da1,Cunha
2015 (UK) 0.000055% UN projection
Falkland Islands (UK)
3,000
July 1, 2015
0.000041% UN projection
Svalbard and Jan Mayen
2,562 (Norway)July 1, 2014
0.000037% Official estimate
Norfolk Island (Australia)
2,302
August 9, 2011
0.000032% 2011 census result
Christmas Island (Australia)
2,072
August 9, 2011
0.000029% 2011 census result
Niue (New Zealand)
1,613
September 10, 20110.000022% Final 2011 census result
Tokelau (NZ)
1,411
October 18, 2011 0.000020% Final 2011 census result
Vatican City …
Purchase answer to see full
attachment
How it works
- Paste your instructions in the instructions box. You can also attach an instructions file
- Select the writer category, deadline, education level and review the instructions
- Make a payment for the order to be assignment to a writer
- Download the paper after the writer uploads it
Will the writer plagiarize my essay?
You will get a plagiarism-free paper and you can get an originality report upon request.
Is this service safe?
All the personal information is confidential and we have 100% safe payment methods. We also guarantee good grades