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

East coast gang sign - Lehman brothers case study answers - Is advertising a period or product cost - The starry night poem analysis - Catcher in the rye banned book - The evolution of inequality deborah rogers - Discussion - Best marshmallow and spaghetti tower - Self service banner web - Acquisition and disposition of property plant and equipment - Two questions - Australian ideal college hobart - Reflections - AIGN#3ECOM402-E-SUPPLY - Elevator traffic handbook pdf - Walmart contingency plan - Fin decisions - Improvement plan presentation - Technicial writing - Audience Analysis- 5 paragraph only!!! - ORGL 2 - Observations of chemical reactions lab - Boxall and purcell 2011 - What are people currently doing to achieve security objectives for their organization? - Interest Rate, Stock Valuation, Risk and Returns - In the direct cutover conversion strategy the new system - +91-8890675453 vashikaran specialist IN Bardhaman - Unit 08 e commerce p5 - Briefly discuss residential aged care sector - Restoring pride richard taylor epub - Five pillars of sustainable change - Reverse leakage current diode - Umbrella of tourism and hospitality industry - Marketplace simulation login - Book review - April fools day poem by kenn nesbitt - Whipps cross outpatients pharmacy - How to write a reflection rmit - What Makes You Happy - Nicomachean ethics book 1 section 7 - Coco movie questions - Assignment - Applying Performance Improvement Tools - Discussion: Use of Group Designs in Program Evaluation - Wais iv determining strengths and weaknesses - Aurasa2002 apply safe working practices in an automotive workplace answers - What is the name for hbr - Which of the following would cause a leftward shift in the supply curve for car washes? - Model the cardinalities of the following business policies - Reflection memo - Difference between military and professional bearing - Electricity meter box location requirements - Groundwater hydrology lecture notes - 2018 patient safety goals nursing - Crime scence - Transformer room ventilation calculation ashrae - Weiser lock battery change - 7 - Project Proposal and Presentation on Implementing a new HIT Solution - Nurs 340 week 1&2 - Brent staples why colleges shower students with a - Camp lake hubert dates - Nist sp 800 88 software free - Teaching for understanding lesson plan template - Oxidation reduction lab report - Occupational therapy case study example - What effect does leasing have on a firm's balance sheet - Finance/Business details - Kaskas auto sales exeter ca - Paper writing - Exchange mail flow troubleshooter - Marketing and distribution of mushroom case study - Benchmark assignment spiritual needs assessment and reflection - Apple corporate social responsibility report - Medicare levy variation declaration - Operational Excellence - Welcome to the group - 21 falkirk road nerrina - Brisbane state high entrance test - 200 pfc dimensions nz - Gram stain lab report - Macroeconomics - Compute the ending inventory at september 30 - Swansea bay campus map - Cell structure and function packet answers - Art and religion in thera - Practical Connection Assignment - International business management - 13 roles of human service professionals - How does specialization make us more efficient - Tunch ilkin weight loss - Andrew esherick cause of death - 63.6 kg in lbs - Why cultural diversity matters michael gavin - Practical connection assignment - General boundary in machine learning - Ichthopaste zinc paste bandage - Berkshire instruments case study answers - Bill nye heat transfer conduction convection and radiation - Adc 0808 pin diagram - A random sample of 10 shipments of stick-on labels showed the following order sizes. - American History Discussion