Cashback Offer from 22nd to 27th July 2021. Get Flat 20% Cashback credited to your account for a minimum transaction of $50. Post Your Question Today!

Question Details Normal
$ 15.00

Excel Chapter 10: State Information

Question posted by
Online Tutor Profile
request

**do not open file: Sastamoine_exploring_e09_grader_a1.xlsx I cant seem to delete it. Open 2nd one

 

State Information

 

Project Description:

Your niece and nephew have a test on state capitals, abbreviations, and statehood dates next week and asked for your help. You downloaded a text file containing states and statehood dates. You will create a workbook with this data and then create a web query to data containing capitals. As you get the data ready to help your niece and nephew study, you will also practice your skills in data manipulation using text functions.

 

Instructions:

For the purpose of grading the project you are required to perform the following tasks:

Step

Instructions

Points Possible

1

Start Excel. Download the text file exploring_e10_grader_a1.txt, the XML file exploring_e10_grader_a1.xml, and the Excel file exploring_e10_grader_a1.xlsx. Open the workbook.

0

2

In the blank worksheet in cell A1, import the text file. When using the Text Import Wizard, select appropriate options for importing a tab-delimited file and that the first row contains headers. Import the date column as Text.

10

3

Rename the sheet as Statehood.

5

4

Set these column widths: 15 for column A, 20 for column B, and 7 for column C.

5

5

Insert a column between columns B and C. Set the column width to 16.  Format the new column C with the General number type. Type Statehood Year in cell C1.

10

6

Enter a text function in cell C2 that extracts just the year from cell B2. Copy the function to the range C3:C51.

10

7

Apply bold and horizontal center alignment to the range A1:D1. Center horizontally data in column C.

5

8

Insert a new sheet named Capitals.

5

9

In cell A1 of the Capitals sheet, create a web query to http://www.stateabbreviations.us/. Select the table containing the US States, Capitals, and Government Links. Select Full HTML Formatting and then import the data.

10

10

Merge cells E2:E3 and type Capital, State.

5

11

Enter a text function in cell E4 that combines the capital and postal abbreviation like this: Montgomery, AL. The second argument in the function must include the comma and space. Copy the function to the range E5:E53.

10

12

Set the width of column E to 18. Apply the All Borders border style to the range E2:E53.

10

13

Insert a new sheet named Nicknames.

5

14

In cell A1 of the Nicknames sheet, import the exploring_e10_grader_a1.xml file.

10

15

Ensure that the worksheets are correctly named and placed in the following order in the workbook: Statehood, Capitals, Nicknames. Save the workbook. Close the workbook, and then exit Excel. Submit the workbook as directed.

0

 

Total Points

100

                                                                                                                       

                                              

Available Answers
$ 10.00

[Solved] Excel Chapter 10: State Information

  • This Solution has been Purchased 1 time
  • Submitted On 15 Nov, 2016 06:04:27
Answer posted by
Online Tutor Profile
solution
Your niece and nephew have a test on sta...
Buy now to view the complete solution

The benefits of buying study notes from CourseMerit

Assurance Of Timely Delivery
We value your patience, and to ensure you always receive your homework help within the promised time, our dedicated team of tutors begins their work as soon as the request arrives.
Best Price In The Market
All the services that are available on our page cost only a nominal amount of money. In fact, the prices are lower than the industry standards. You can always expect value for money from us.
Uninterrupted 24/7 Support
Our customer support wing remains online 24x7 to provide you seamless assistance. Also, when you post a query or a request here, you can expect an immediate response from our side.
closebutton
Only 45 characters allowed.
closebutton

$ 629.35