Loading...

Messages

Proposals

Stuck in your homework and missing deadline? Get urgent help in $10/Page with 24 hours deadline

Get Urgent Writing Help In Your Essays, Assignments, Homeworks, Dissertation, Thesis Or Coursework & Achieve A+ Grades.

Privacy Guaranteed - 100% Plagiarism Free Writing - Free Turnitin Report - Professional And Experienced Writers - 24/7 Online Support

Excel guided project 4 3

04/12/2021 Client: muhammad11 Deadline: 2 Day

Excel 2013 Chapter 4 Importing, Creating Tables, Sorting and Filtering, and Using Conditional Formatting Last Updated: 2/4/15 Page 1

USING MICROSOFT EXCEL 2013 Guided Project 4-3

Guided Project 4-3 Clemenson Imaging wants to analyze expense reports from field representatives as well as its patient data and image data. To complete the necessary worksheets, you import a comma-separated text file (.csv), use the Subtotal command, and run Goal Seek. You also format data as a table and build an advanced filter. Finally, you display data in a PivotTable.

Skills Covered in This Project

 Create an Excel table.

 Apply a table style.

 Create an Advanced Filter.

 Apply Conditional Formatting.

 Copy, move, and name a worksheet.

 Insert a row and set column widths.

 Import a comma-separated values text file.

 Sort data.

 Use the Subtotal command.

 Expand and collapse groups in an outline.

 Use Goal Seek.

 Create a PivotTable.

 Create a PivotChart.

 Unlock cells.

 Protect a worksheet with a password.

1. Open the ClemensonForecasts-04 start file. Click the Enable Editing button.

2. The file will be renamed automatically to include your name. Change the project file name if directed to do

so by your instructor, and save it.

3. Copy the Past&Projected sheet and name the copied sheet Adv Filter. Move the Adv Filter sheet to the right

of the Past&Projected sheet.

4. Create and format an Excel table.

a. Select cells A4:E60 on the Adv Filter sheet.

b. Click the Quick Analysis tool and choose Tables.

c. Click Table.

5. Apply a table style.

a. Click a cell within the table.

b. Click the More button [Table Tools Design tab, Table Styles group].

c. Select Table Style Medium 15.

6. Create a criteria range for an Advanced Filter. a. Select cells A4:E4 and copy and paste them to cell G4.

b. Type Criteria Range in cell G3 and set the font to Cambria 16 pt.

c. Adjust column K width to show the complete label.

d. Type Extract Range in cell G8 and set the same font and size.

e. Copy the labels from cells G4:K4 to cell G9.

7. Create an Advanced Filter.

a. In cell G5, type >12/31/16.

b. In cell H5, type mri.

Step 1

Download start file

Excel 2013 Chapter 4 Importing, Creating Tables, Sorting and Filtering, and Using Conditional Formatting Last Updated: 2/4/15 Page 2

USING MICROSOFT EXCEL 2013 Guided Project 4-3

c. In cell H6, type ct scan. These criteria will find records dated 2017 or later for MRIs and records for CT scans

from any year (Figure 4-93).

d. Click a cell in the table.

e. Click the Advanced button [Data tab, Sort & Filter group].

f. Select the Copy to another location radio button.

g. Verify that the List range is cells A4:E60. If the range

is incorrect, click and drag to select the range. The

list range includes the header row.

h. Click in the Criteria range box and select cells

G4:K6.

i. Click in the Copy to box and select cells G9:K9.

j. Click OK (Figure 4-94). The CT scan records are

from all years; the MRI records are only those from

2017 and later.

8. Apply Conditional Formatting.

a. Select cells I10:I25.

b. Click the Conditional Formatting button [Home

tab, Styles group].

c. Choose Highlight Cells Rules and Greater Than.

d. Type 751 and choose Green Fill with Dark Green

Text.

e. Click OK.

9. Import a comma-separated values text file.

a. Click the Expense Info sheet tab.

b. Select cell A3.

c. Click the From Text button [Data tab, Get External Data group].

d. Find and select the ClemensonExpenseData-04.csv file in the Import Text File window.

e. Click Import.

f. Select the Delimited button in the first Wizard window. Excel recognizes that this file separates the data

into columns with a comma.

g. Click Next.

h. Click to remove the check mark for Tab and click to place a check mark for Comma.

i. Click Finish and click OK.

j. Insert two rows at row 3.

k. Type Representative in cell A4, Date in cell B4, and Amount in cell C4.

l. AutoFit column C.

4-93 Advanced Filter results

4-94 Advanced Filter results

Excel 2013 Chapter 4 Importing, Creating Tables, Sorting and Filtering, and Using Conditional Formatting Last Updated: 2/4/15 Page 3

USING MICROSOFT EXCEL 2013 Guided Project 4-3

10. Sort data.

a. Click cell A5.

b. Click the Sort & Filter button [Home tab, Editing group].

c. Choose Sort A to Z.

11. Use the Subtotal command.

a. Click cell A5.

b. Click the Subtotal button [Data tab, Outline group].

c. Click the At each change in arrow and choose Representative.

d. Click the Use function arrow and choose Average.

e. Check the Amount box in the Add

subtotal to area.

f. Click OK.

g. Format the values in column C as

Currency with no decimal places.

12. Use Goal Seek.

a. Type Target March Expense in cell E4.

b. Click cell C8.

c. Click the What-If Analysis button [Data

tab, Data Tools group] and choose

Goal Seek.

d. In the To value box, type 600. This value

sets a lower expense for Mary Jo.

e. In the By changing cell box, click cell

C7.

f. Click OK.

g. Note the target expense and click Cancel.

h. Type $600 in cell F5.

13. Collapse outline groups.

a. Click the collapse symbol (-) for row 12.

b. Click the collapse symbol (-) for each of these rows: 16, 20, 24 (Figure 4-95).

14. Create a PivotTable.

a. Click the Past&Projected sheet tab.

b. Select cells A4:E60. You must select the

range to display the Quick Analysis tool.

c. Click the Quick Analysis tool and

choose Tables.

d. Point at several PivotTable options to see

the Live Preview.

e. Choose the option that shows a sum of

each field (Figure 4-96).

f. Rename the sheet PivotTable.

4-96 Suggest PivotTable choices from the Quick Analysis tool

4-95 Expanded and collapsed groups after the Subtotal Command

Excel 2013 Chapter 4 Importing, Creating Tables, Sorting and Filtering, and Using Conditional Formatting Last Updated: 2/4/15 Page 4

USING MICROSOFT EXCEL 2013 Guided Project 4-3

15. Add a PivotChart to the PivotTable worksheet.

a. Click a cell within the PivotTable.

b. Click the PivotChart button [PivotTable Tool Analyze tab, Tools group].

c. Click Line in the type list, and choose Line with Markers PivotChart.

d. Click OK.

e. Position the chart to the right of the PivotTable.

f. In the PivotChart Fields pane, click to remove the check mark for # of Employees (Figure 4-97). The

PivotTable and PivotChart are linked so that field is removed from the table and the chart. The task pane

toggles between the PivotTable and PivotChart fields panes based on which element is active.

16. Remove the Locked property.

a. Click the Past&Projected sheet tab.

b. Select cells C5:E60 and right-click any cell in the range.

c. Select Format Cells from the menu.

d. Click the Protection tab.

e. Clear the check mark for Locked.

f. Click OK.

17. Protect a worksheet with a password.

a. Click the Protect Sheet button [Review tab, Changes group].

b. Click to place check marks for Select locked cells and Select unlocked cells if necessary.

c. Type the password: 321.

d. Click OK.

e. Retype the password: 321.

f. Click OK. The cells for which the Locked property was disabled can be edited.

18. Save and close the workbook.

19. Upload and save your project file.

20. Submit project for grading.

4-97 PivotChart and its PivotTable

Step 2

Upload & Save

Step 3

Grade my Project

Homework is Completed By:

Writer Writer Name Amount Client Comments & Rating
Instant Homework Helper

ONLINE

Instant Homework Helper

$36

She helped me in last minute in a very reasonable price. She is a lifesaver, I got A+ grade in my homework, I will surely hire her again for my next assignments, Thumbs Up!

Order & Get This Solution Within 3 Hours in $25/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 3 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

Order & Get This Solution Within 6 Hours in $20/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 6 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

Order & Get This Solution Within 12 Hours in $15/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 12 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

6 writers have sent their proposals to do this homework:

Quick Mentor
Top Academic Guru
Math Guru
Coursework Assignment Help
Instant Assignment Writer
Assignments Hut
Writer Writer Name Offer Chat
Quick Mentor

ONLINE

Quick Mentor

I will provide you with the well organized and well research papers from different primary and secondary sources will write the content that will support your points.

$24 Chat With Writer
Top Academic Guru

ONLINE

Top Academic Guru

I am an academic and research writer with having an MBA degree in business and finance. I have written many business reports on several topics and am well aware of all academic referencing styles.

$49 Chat With Writer
Math Guru

ONLINE

Math Guru

I will provide you with the well organized and well research papers from different primary and secondary sources will write the content that will support your points.

$45 Chat With Writer
Coursework Assignment Help

ONLINE

Coursework Assignment Help

I have written research reports, assignments, thesis, research proposals, and dissertations for different level students and on different subjects.

$38 Chat With Writer
Instant Assignment Writer

ONLINE

Instant Assignment Writer

I have read your project description carefully and you will get plagiarism free writing according to your requirements. Thank You

$39 Chat With Writer
Assignments Hut

ONLINE

Assignments Hut

As an experienced writer, I have extensive experience in business writing, report writing, business profile writing, writing business reports and business plans for my clients.

$21 Chat With Writer

Let our expert academic writers to help you in achieving a+ grades in your homework, assignment, quiz or exam.

Similar Homework Questions

List of physics competitions - Bird cockney rhyming slang - Stages of lightening dark hair - It service desk university birmingham - Columbia space shuttle disaster case study - What is regional trade block - The passing of grandison summary - Aumentar memoria virtual windows 10 - Elearning kbs - 13 businesses with brilliant global marketing strategies - Red gum only 19 lyrics - Dulux natural white exterior - Shadow health chest pain answers - Quantitative research questions examples - The poop on composting case study answers - Discussion w7 635 - Maths - Solve the following inequality 2x 1 x 5 18 - 2.4 planning a presentation assignment - The dangers of using slang in business writing include - Translink monthly pass price - What is pivot point in physics - Berger the developing person through childhood and adolescence - Wyndham by request program - Facebook inc the initial public offering case - Hills home hub price list - Andrew carnegie on the triumph of america 1885 - How to calibrate an eyepiece graticule - Nursing tutor - Stonnington council parking permit - Balance sheet approach expatriate compensation - Dispatches from pluto discussion questions - Other drivers depend on you to be rational and __________ - Due today - Sericol ink price list - Benzyl alcohol to benzoic acid - Week 1 writing - Advantages of having a dual court system - Wk 5 - Apply: Policies Review [due Mon] - Current event - American express investor day presentation 2019 - Cons of bilingual education - Agueda by pio baroja in english - Affective Instruction Lesson Plan - IRR, MIRR, NPV etc - Harvard global supply chain management simulation v2 answers - How did bradford initially view the native american - Unit 11 btec sport - How to wire up driving lights diagram - EXCEL HOMEWORK DUE IN 36 HOURS - 580 miles in km - The Stock Market and the Economy Summary - Arnold leisure centre jobs - Given the following data direct materials used is - Barry fitzgerald mcdonald's net worth - Literature review on cyber crime pdf - Ndigel homework - Employee salary details program in python - A baseball player reaches base 35 of the time - Www biologyjunction com cell functions htm - Extron 60 1027 01 - Private limited company tutor2u - Boy overboard teaching activities - Discussion - Damien hirst shark value - Discussion - Melting test station background - Changingminds org leadership vs management - Is 11/14 a terminating decimal - E700 sp dcl pipe - Persepolis marjane satrapi themes - Monument valley tattooed fists lyrics - How to reduce friction on a mousetrap car - Kay's rule for real gas mixtures - Costco car insurance uk - Discussion Questions - Aco vs mco - Present value methods of analyzing capital investments - Internet revenue models - A refresher on marketing myopia - Walden university mission of social change - To kill a mocking bird tree - Graded assignment unit test part 2 basic tools and transformations - Computer science course map monash - Cisco show arp interface - Broward county teachers code of ethics - Lamb chops woolworths price - Then morris gleitzman quotes - Project Schedule, Cost, & Procurement Management - Required practical 7b chemistry - Because of winn dixie scholastic - Craft funeral home port chester ny - Cmit 370 - The most popular technique for gathering primary data is - Eng 125 week 1 discussion 1 - Torsion of circular sections lab report - 1000000 in roman numerals - BUSN235 - Dfd diagram for online food ordering system - Thermochemistry and hess's law lab conclusion