### Free Educational Resources

• > Your niece and nephew have a test on state capi...
+

# Your niece and nephew have a test on state capitals, abbreviations, and statehood dates next week

##### Rating:
(0)
Author: matthew stock
##### Description:

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 fileexploring_e10_grader_a1.txt, the XML fileexploring_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

(more)
##### Try a College Course Free

Sophia’s self-paced online courses are a great way to save time and money as you earn credits eligible for transfer to over 2,000 colleges and universities.*

No credit card required

25 Sophia partners guarantee credit transfer.

226 Institutions have accepted or given pre-approval for credit transfer.

* The American Council on Education's College Credit Recommendation Service (ACE Credit®) has evaluated and recommended college credit for 21 of Sophia’s online courses. More than 2,000 colleges and universities consider ACE CREDIT recommendations in determining the applicability to their course and degree programs.

Tutorial