MOS Excel 2019 | Module 6: SAM Project 1b Roscoe’s Sports Blog CREATE, SORT, AND QUERY TABLES GETTING STARTED Open the file SAM_EX19_MOS_6b_FirstLastName_1

Subject:MS ExcelPrice:19.99 Bought6

Share With

MOS Excel 2019 | Module 6: SAM Project 1b

Roscoe’s Sports Blog

CREATE, SORT, AND QUERY TABLES

• GETTING STARTED

• Open the file SAM_EX19_MOS_6b_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as SAM_EX19_MOS_6b_FirstLastName_2.xlsx by changing the “1” to a “2”.
  • If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
• To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer:
  • Support_SAM_EX19_MOS_6b_Authors.txt
• With the file SAM_EX19_MOS_6b_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
  • If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

• PROJECT STEPS

1. Jack Roscoe started running a sports blog two years ago, and since then, it has provided a second income for him and his family. Jack is reviewing the recent articles written for the blog along with the payments to the authors and the income from ads. He asks for your help in using Excel tables to analyze the data.  
   
   Go to the Home Page worksheet. Jack wants to include a list of links to resources his managers are likely to need. However, the first link he added is unnecessary. (Hint: Click Enable Content button and Continue button if prompted to enable content and update links.)
   Remove the hyperlink from the text in cell C4.
2. Jack wants to make it easy for users to navigate to the other worksheets. Add links to other places in the workbook as follows:
   1. In cell C5, insert a link to cell A1 of the August Articles worksheet.
   2. In cell C6, insert a link to cell A1 of the Current Articles worksheet.
   3. In cell C7, insert a link to cell A1 of the Proposed Articles worksheet.
   4. In cell C8, insert a link to cell A1 of the August Advertising worksheet.
   5. In cell C9, insert a link to cell A1 of the Lookup worksheet.
   6. In cell C10, insert a link to cell A1 of the Authors worksheet.
   7. In cell C11, insert a link to cell A1 of the Subscribers worksheet.
3. Go to the August Articles worksheet, which lists the articles published in August 2021. Create a table so that Jack can summarize and filter the data and display articles with the highest payment amounts as follows :
   1. Format the August articles data (range A1:G26) as a table with headers using Aqua, Table Style Medium 5.
   2. Use AugArticles as the name of the table.
   3. Filter the table using a custom AutoFilter to display projects with a Payment amount greater than or equal to $500.
4. Go to the Current Articles worksheet, which contains the CurrentArticles table listing articles that are scheduled for publication during the first 10 days in September. Jack received a proposal for a new article that he wants to publish on September 10.
   
   Add a row to the end of the CurrentArticles table for a new record containing the data shown in Table 1.

Table 1: New Record for the CurrentArticles Table

 

Article Title	Author	Publication Date	Number of Words	Article Type	Sports Level	Payment	Approved?	Published?
Weekend preview	Aken	9/10/2021	482	News	College	$578	Yes	No

 

5. Sort the CurrentArticles table in descending order by payment amount so that Jack can quickly track the payments for each article.
6. Jack wants to list the published articles in a separate part of the worksheet. Use an advanced filter to list these articles in a new range as follows:
   1. In cell I18, type Yes as the value to filter on in the criteria range.
   2. Create an advanced filter using the CurrentArticles table (range A1:I13) as the List range.
   3. Use the range A17:I18 as the Criteria range.
   4. Copy the results to another location, starting in the range A20:I20.
7. As a contrast, Jack also wants to list the projects that are not in development.
   
   In the CurrentArticles table, display the filter arrows, and then filter the table to display the articles that have not been published yet. (Hint: If the Filter Button check box is not available, remove and then redisplay the header row.)
8. Go to the Proposed Articles worksheet, which lists articles that have been proposed for publication in September. Jack suspects the ProposedArticles table has a duplicate record. Identify the duplicate as follows:
   1. Clear the filter from the ProposedArticles table to display all the records.
   2. In the range A2:A16, create a Conditional Formatting Highlight Cells Rule that displays cells with duplicate values using Light Red Fill with Dark Red Text.
   3. Delete the second instance of the duplicate record so that you can summarize the data accurately.
9. Jack includes photos with all articles, but he asks a graphic artist to create additional illustrations such as charts and infographics for feature stories longer than 400 words. Add a column to the ProposedArticles table, and determine which articles meet the criteria as follows:
   1. In cell H1, type Extra Art as the column heading.
   2. In cell H2, enter a formula using the AND function that includes structured references to display TRUE if an article has a [Number of Words] value >400 and an [Article Type] of "Feature". Fill the range H3:H15 with the formula in cell H2 if Excel does not do so automatically.
10. Add a Total Row to the ProposedArticles table, which automatically counts the number of Extra Art values.
    
    Using the total row, display the sum of the payment amounts.
11. Jack asks you to identify the articles that contain 500 words or more, those that contain 400 words or more, and those that contain less than 400 words.
    1. In the Number of Words column (range D2:D15), create a new Icon Set Conditional Formatting rule using the 3 Traffic Lights (Rimmed) icons.
    2. Reverse the icon order.
    3. Display the red traffic light icon in cells with a Number type value greater than or equal to 500.
    4. Display the yellow traffic light icon in cells with a Number type value greater than or equal to 400.
    5. Display the green traffic light icon in cells with a Number type value less than 400.
12. Add alt text to the "Number of Words per Article" chart that reads as follows:
    A 2-D Clustered Bar Chart representing the number of words per blog article.
13. Jack has applied a conditional formatting rule to the range G2:G15 using Blue Gradient Fill data bars to compare the funding amounts visually. He doesn't feel that the visual adds much to the presentation.
    
    Remove the conditional formatting from the range G2:G15.
14. Wrap the text in cell K1 to display the complete contents of the cell.
15. In the range J9:M14, Jack needs to insert a summary of the proposed and published articles from September 2020. Insert this data as a table as follows:
    1. Insert a table in the range J9:M14, specifying that the data has headers.
    2. In the new table, enter the data shown in Table 2.
    3. AutoFit the contents of columns J:M to display the complete cell contents.
    4. Apply Aqua, Table Style Medium 5 to the new table to match the formatting of the ProposedArticles table.

Table 2: Data for the New Table

 

Article Type	Proposed	Published	Payment
Analysis	5	3	1,998
Feature	5	4	2,065
News	4	3	1,603
Opinion	3	3	1,512
Profile	3	2	1,004

 

16. Go to the August Advertising worksheet, which lists all the articles published in August. Jack wants to display the data by article type and then list the projects by publication date.
    
    Sort the data in the table in ascending order first by article type and then by publication date.
17. Jack also wants to calculate ad amount subtotals for each article type:
    1. Convert the table to a range.
    2. Insert a subtotal at each change in the Article Type value.
    3. Use the Sum function to calculate the subtotals.
    4. Add subtotals to the Ad Amt values only.
    5. Include a summary below the data.
    6. Collapse the outline to display only the subtotals for each article type and the grand total.
18. Go to the Authors worksheet, which lists partial information about the Roscoe's Sports Blog authors which is contained in a text file. Import data from text file as follows:
    1. Import the data from the Support_SAM_EX19_MOS_6b_Authors.txt file in cell G12.
    2. Format the imported data in the range G12:M18 as a table using Aqua, Table Style Medium 5, if necessary.
19. Jack wants to list the authors in the range A5:E10. The text file table separated the first and last names, but Dean wants to list the full name on the Authors worksheet. List the first and last names of each team member in a single cell as follows:
    1. In cell A5, enter a formula using the CONCATENATE function that displays the first name shown in cell H13 followed by a space (" ") and then the last name shown in cell I13.
    2. Fill the range A6:A10 with the formula in cell A5 to list the full names of the remaining team members.
20. Incorporate the imported data in the range B5:E10 as follows:
    1. Copy the data from the range J13:J18 and paste only the values in the range B5:B10.
    2. In cell C5, enter a formula using the PROPER function to capitalize the first letter in each word in the Article Type text in cell K13.
    3. Fill the range C6:C10 with the formula in cell C5 to list the article types of the remaining team members.
    4. In cell D5, enter a formula using the LEFT function to insert the first 2 characters on the left of cell L13. Copy the formula in cell D5 to the range D6:D10.
    5. In cell E5, enter a formula using the RIGHT function to insert the last 2 characters on the right of cell M13. Copy the formula in cell E5 to the range E6:E10.
    6. Resize columns A:D to their best fit, resize columns E:F to 7.00, and resize column G to 21.00.
    7. Hide rows 12 to 18 so that the worksheet does not display duplicated data.
21. Go to the Subscribers worksheet, which lists article preference, current subscription count, and monthly revenue by state. Jack wants you to make some final edits and then explore the completed analysis as follows:
    1. Freeze the top row (row 1) of the worksheet.
    2. Add the following document properties to the workbook:
       Title: Roscoe's Sports Blog Status: In progress
    3. Go to the Proposed Articles worksheet. Display formulas in the worksheet.
    4. Select cell range A1:I17 and choose Print Selection, Landscape Orientation, and Fit Sheet on One Page.
    5. Preview the Print Settings but don't Print.
    6. Click Show Formulas again to hide the formulas.
    7. Inspect Workbook for issues, scroll through the results in the Document Inspector, and then close the Document Inspector withtout making any changes.

Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.

 

 

Final Figure 1: Home Page Worksheet

 

 

Final Figure 2: August Articles Worksheet

 

 

Final Figure 3: Current Articles Worksheet

 

 

Final Figure 4: Proposed Articles Worksheet

 

 

Final Figure 5: August Advertising Worksheet

 

 

Final Figure 6: Lookup Worksheet

 

 

Final Figure 7: Authors Worksheet

 

 

Final Fig

MOS Excel 2019 | Module 6: SAM Project 1b

Roscoe’s Sports Blog

CREATE, SORT, AND QUERY TABLES

• GETTING STARTED

• Open the file SAM_EX19_MOS_6b_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as SAM_EX19_MOS_6b_FirstLastName_2.xlsx by changing the “1” to a “2”.
  • If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
• To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer:
  • Support_SAM_EX19_MOS_6b_Authors.txt
• With the file SAM_EX19_MOS_6b_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
  • If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

• PROJECT STEPS

1. Jack Roscoe started running a sports blog two years ago, and since then, it has provided a second income for him and his family. Jack is reviewing the recent articles written for the blog along with the payments to the authors and the income from ads. He asks for your help in using Excel tables to analyze the data.  
   
   Go to the Home Page worksheet. Jack wants to include a list of links to resources his managers are likely to need. However, the first link he added is unnecessary. (Hint: Click Enable Content button and Continue button if prompted to enable content and update links.)
   Remove the hyperlink from the text in cell C4.
2. Jack wants to make it easy for users to navigate to the other worksheets. Add links to other places in the workbook as follows:
   1. In cell C5, insert a link to cell A1 of the August Articles worksheet.
   2. In cell C6, insert a link to cell A1 of the Current Articles worksheet.
   3. In cell C7, insert a link to cell A1 of the Proposed Articles worksheet.
   4. In cell C8, insert a link to cell A1 of the August Advertising worksheet.
   5. In cell C9, insert a link to cell A1 of the Lookup worksheet.
   6. In cell C10, insert a link to cell A1 of the Authors worksheet.
   7. In cell C11, insert a link to cell A1 of the Subscribers worksheet.
3. Go to the August Articles worksheet, which lists the articles published in August 2021. Create a table so that Jack can summarize and filter the data and display articles with the highest payment amounts as follows :
   1. Format the August articles data (range A1:G26) as a table with headers using Aqua, Table Style Medium 5.
   2. Use AugArticles as the name of the table.
   3. Filter the table using a custom AutoFilter to display projects with a Payment amount greater than or equal to $500.
4. Go to the Current Articles worksheet, which contains the CurrentArticles table listing articles that are scheduled for publication during the first 10 days in September. Jack received a proposal for a new article that he wants to publish on September 10.
   
   Add a row to the end of the CurrentArticles table for a new record containing the data shown in Table 1.

Table 1: New Record for the CurrentArticles Table

 

Article Title	Author	Publication Date	Number of Words	Article Type	Sports Level	Payment	Approved?	Published?
Weekend preview	Aken	9/10/2021	482	News	College	$578	Yes	No

 

5. Sort the CurrentArticles table in descending order by payment amount so that Jack can quickly track the payments for each article.
6. Jack wants to list the published articles in a separate part of the worksheet. Use an advanced filter to list these articles in a new range as follows:
   1. In cell I18, type Yes as the value to filter on in the criteria range.
   2. Create an advanced filter using the CurrentArticles table (range A1:I13) as the List range.
   3. Use the range A17:I18 as the Criteria range.
   4. Copy the results to another location, starting in the range A20:I20.
7. As a contrast, Jack also wants to list the projects that are not in development.
   
   In the CurrentArticles table, display the filter arrows, and then filter the table to display the articles that have not been published yet. (Hint: If the Filter Button check box is not available, remove and then redisplay the header row.)
8. Go to the Proposed Articles worksheet, which lists articles that have been proposed for publication in September. Jack suspects the ProposedArticles table has a duplicate record. Identify the duplicate as follows:
   1. Clear the filter from the ProposedArticles table to display all the records.
   2. In the range A2:A16, create a Conditional Formatting Highlight Cells Rule that displays cells with duplicate values using Light Red Fill with Dark Red Text.
   3. Delete the second instance of the duplicate record so that you can summarize the data accurately.
9. Jack includes photos with all articles, but he asks a graphic artist to create additional illustrations such as charts and infographics for feature stories longer than 400 words. Add a column to the ProposedArticles table, and determine which articles meet the criteria as follows:
   1. In cell H1, type Extra Art as the column heading.
   2. In cell H2, enter a formula using the AND function that includes structured references to display TRUE if an article has a [Number of Words] value >400 and an [Article Type] of "Feature". Fill the range H3:H15 with the formula in cell H2 if Excel does not do so automatically.
10. Add a Total Row to the ProposedArticles table, which automatically counts the number of Extra Art values.
    
    Using the total row, display the sum of the payment amounts.
11. Jack asks you to identify the articles that contain 500 words or more, those that contain 400 words or more, and those that contain less than 400 words.
    1. In the Number of Words column (range D2:D15), create a new Icon Set Conditional Formatting rule using the 3 Traffic Lights (Rimmed) icons.
    2. Reverse the icon order.
    3. Display the red traffic light icon in cells with a Number type value greater than or equal to 500.
    4. Display the yellow traffic light icon in cells with a Number type value greater than or equal to 400.
    5. Display the green traffic light icon in cells with a Number type value less than 400.
12. Add alt text to the "Number of Words per Article" chart that reads as follows:
    A 2-D Clustered Bar Chart representing the number of words per blog article.
13. Jack has applied a conditional formatting rule to the range G2:G15 using Blue Gradient Fill data bars to compare the funding amounts visually. He doesn't feel that the visual adds much to the presentation.
    
    Remove the conditional formatting from the range G2:G15.
14. Wrap the text in cell K1 to display the complete contents of the cell.
15. In the range J9:M14, Jack needs to insert a summary of the proposed and published articles from September 2020. Insert this data as a table as follows:
    1. Insert a table in the range J9:M14, specifying that the data has headers.
    2. In the new table, enter the data shown in Table 2.
    3. AutoFit the contents of columns J:M to display the complete cell contents.
    4. Apply Aqua, Table Style Medium 5 to the new table to match the formatting of the ProposedArticles table.

Table 2: Data for the New Table

 

Article Type	Proposed	Published	Payment
Analysis	5	3	1,998
Feature	5	4	2,065
News	4	3	1,603
Opinion	3	3	1,512
Profile	3	2	1,004

 

16. Go to the August Advertising worksheet, which lists all the articles published in August. Jack wants to display the data by article type and then list the projects by publication date.
    
    Sort the data in the table in ascending order first by article type and then by publication date.
17. Jack also wants to calculate ad amount subtotals for each article type:
    1. Convert the table to a range.
    2. Insert a subtotal at each change in the Article Type value.
    3. Use the Sum function to calculate the subtotals.
    4. Add subtotals to the Ad Amt values only.
    5. Include a summary below the data.
    6. Collapse the outline to display only the subtotals for each article type and the grand total.
18. Go to the Authors worksheet, which lists partial information about the Roscoe's Sports Blog authors which is contained in a text file. Import data from text file as follows:
    1. Import the data from the Support_SAM_EX19_MOS_6b_Authors.txt file in cell G12.
    2. Format the imported data in the range G12:M18 as a table using Aqua, Table Style Medium 5, if necessary.
19. Jack wants to list the authors in the range A5:E10. The text file table separated the first and last names, but Dean wants to list the full name on the Authors worksheet. List the first and last names of each team member in a single cell as follows:
    1. In cell A5, enter a formula using the CONCATENATE function that displays the first name shown in cell H13 followed by a space (" ") and then the last name shown in cell I13.
    2. Fill the range A6:A10 with the formula in cell A5 to list the full names of the remaining team members.
20. Incorporate the imported data in the range B5:E10 as follows:
    1. Copy the data from the range J13:J18 and paste only the values in the range B5:B10.
    2. In cell C5, enter a formula using the PROPER function to capitalize the first letter in each word in the Article Type text in cell K13.
    3. Fill the range C6:C10 with the formula in cell C5 to list the article types of the remaining team members.
    4. In cell D5, enter a formula using the LEFT function to insert the first 2 characters on the left of cell L13. Copy the formula in cell D5 to the range D6:D10.
    5. In cell E5, enter a formula using the RIGHT function to insert the last 2 characters on the right of cell M13. Copy the formula in cell E5 to the range E6:E10.
    6. Resize columns A:D to their best fit, resize columns E:F to 7.00, and resize column G to 21.00.
    7. Hide rows 12 to 18 so that the worksheet does not display duplicated data.
21. Go to the Subscribers worksheet, which lists article preference, current subscription count, and monthly revenue by state. Jack wants you to make some final edits and then explore the completed analysis as follows:
    1. Freeze the top row (row 1) of the worksheet.
    2. Add the following document properties to the workbook:
       Title: Roscoe's Sports Blog Status: In progress
    3. Go to the Proposed Articles worksheet. Display formulas in the worksheet.
    4. Select cell range A1:I17 and choose Print Selection, Landscape Orientation, and Fit Sheet on One Page.
    5. Preview the Print Settings but don't Print.
    6. Click Show Formulas again to hide the formulas.
    7. Inspect Workbook for issues, scroll through the results in the Document Inspector, and then close the Document Inspector withtout making any changes.

Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.

 

 

Final Figure 1: Home Page Worksheet

 

 

Final Figure 2: August Articles Worksheet

 

 

Final Figure 3: Current Articles Worksheet

 

 

Final Figure 4: Proposed Articles Worksheet

 

 

Final Figure 5: August Advertising Worksheet

 

 

Final Figure 6: Lookup Worksheet

 

 

Final Figure 7: Authors Worksheet

 

 

Final Figure 8: Subscribers Worksheet

 

ure 8: Subscribers Worksheet