How to work on a spreed sheet document using the information given

 

Eller Software Services has received contract revenue information in a text file. You import, sort, and filter the data. You also create a PivotTable, prepare a worksheet with subtotals, and format related data as an Excel table.

[Student Learning Outcomes 4.1, 4.3, 4.4, 4.5, 4.6, 4.8]

Files Needed: EllerSoftware-04.xlsx (Available from the Start File link.) and EllerSoftwareText-04.txt (Available from the Resources link.)

Completed Project File Name: [your name]-EllerSoftware-04.xlsx

Skills Covered in This Project

  • Import a text file.
  • Use AutoFilters.
  • Sort data by multiple columns.
  • Create a PivotTable.
  • Format fields in a PivotTable.
  • Use the Subtotal command.
  • Format data in an Excel table.
  • Sort data in an Excel table.

Alternate Instruction for Microsoft 365 Apps icon This image appears when a project instruction has changed to accommodate an update to Microsoft 365 Apps. If the instruction does not match your version of Office, try using the alternate instruction instead.

  1. Open the EllerSoftware-04.xlsx start file. Click the Enable Editing button. 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.
  2. Import the EllerSoftwareText-04.txt file downloaded from the Resources link and load it to begin in cell A4. The text file is tab-delimited.
  3. Format the values in column H as Currency with zero decimal places.
  4. Click cell G4 and use the AutoFilter arrow to sort by date oldest to newest.
  5. Click cell F4 and use the AutoFilter arrow to sort by product/service name in ascending order.
  6. Filter the Date column to show only contracts for September using the All Dates in the Period option.
  7. Edit the label in cell A2 to display Contract Amounts for September.
  8. Select cells A1:H2 and press Ctrl+1 to open the Format Cells dialog box. On the Alignment tab, choose Center Across Selection.
  9. Change the font size for cells A1:H2 to 20 pt (Figure 4-102) and close the Queries & Connections pane.Date header displays filter icon.Figure 4-102 Imported data sorted and filtered
  10. Copy the Contracts sheet to the end and name the copy Data.
  11. Clear the date filter.
  12. Select cell A5 and click the PivotTable button [Insert tab, Tables group]. The range is identified as the EllerSoftwareText file.
  13. Verify that New Worksheet is selected, deselect the Add this data to the Data Model button, and click OK.
  14. Name the sheet PivotTable. Close the Queries & Connections pane.
  15. Show the Product/Service and Contract fields in the PivotTable.
  16. Drag the Contract field from the Choose fields to add to report area below the Sum of Contract field in the Values area so that it appears twice in the report layout and the pane (Figure 4-103).Values are identical.Figure 4-103 “Contract” field appears twice in the report
  17. Select cell C4 and click the Field Settings button [PivotTable Analyze tab, Active Field group]. Type Average Contract as the Custom Name, choose Average as the calculation, and set the Number Format to Currency with zero decimal places.
    Alternate Instruction for Microsoft 365 Apps icon Select cell C4 and click the Field Settings button [PivotTable Tools Analyze tab, Active Field group]. Type Average Contract as the Custom Name, choose Average as the calculation, and set the Number Format to Currency with zero decimal places.
  18. Select cell B4 and set its Custom Name to Total Contracts and the number format to Currency with zero decimal places.
  19. Apply Brown, Pivot Style Dark 3.
  20. Select the Data sheet tab and copy cells A1:A2. Paste them in cell A1 on the PivotTable sheet.
  21. Select Align Left for cells A1:A2 and 16 pt. as the font size. Edit the label in cell A2 to display Contract Amounts, September through December (Figure 4-104).Completed PivotTableFigure 4-104 Completed PivotTable
  22. Copy the Data sheet to the end and name the copy Subtotals.
  23. Select cell D5 and sort by City in A to Z order.
  24. Select cell A5 and convert the table to a range. Select cells A5:H31 and apply No Fill [Home tab, Font group].
  25. Use the Subtotal command to show a SUM for the contract amounts for each city.
  26. Edit the label in cell A2 to display Contract Amounts by City.
  27. Format the sheet to Landscape orientation, center the page vertically, and scale it to fit one page.
  28. Click the Billable Hours sheet tab and select cell A4.
  29. Click the Format as Table button [Home tab, Styles group], use Orange, Table Style Medium 10, and remove the data connections.
  30. Type 5% Add On in cell E4 and press Enter.
  31. Build a formula in cell E5 to multiply cell D5 by 105% and press Enter to copy the formula.
  32. Select cells A1:A2 and left align them. Then select cells A1:E2, click the Launcher for the Alignment group [Home tab], and select Center Across Selection from the Horizontal list.
  33. Use the AutoFilter arrows to sort by date in oldest to newest order.
  34. Save and close the workbook (Figure 4-105).Excel 4-4 completedFigure 4-105 Excel 4-4 completed
  35. Upload and save your project file.
Get 20% Discount on This Paper
Pages (550 words)
Approximate price: -

Try it now!

Get 20% Discount on This Paper

We'll send you the first draft for approval by at
Total price:
$0.00

How it works?

Follow these simple steps to get your paper done

Place your order

Fill in the order form and provide all details of your assignment.

Proceed with the payment

Choose the payment system that suits you most.

Receive the final file

Once your paper is ready, we will email it to you.

Our Services

Custom Writings Help is a Quality-Oriented Company in Online Writing as a result of hiring exceptional professionals to execute clients' tasks.

Essays

Research Papers

At Custom Writings Help,We understand the struggle of research paper writing, and that is why at Custom WritingS Help, we are all out to help you. We pride ourselves on having a team of clinical writers. The stringent and rigorous vetting process ensures that only the 'BEST' Writers are chosen for the job. We have highly qualified Ph.D. and MA writers working with us; we equally offer these experienced writers specific bonuses and incentives to make them deliver highly original, unique, and informative content at reasonably low prices.

Admissions

Thesis Writing Service

Worlwide, Many Masters Students are struggling with Thesis Completion. A thesis is likely to be the longest and most challenging piece of work a student has ever completed. However, unlike essays and other assignments, a student can pick a particular interest topic and work on their initiative. Fortunately, we are there for you. At Custom Writings Help, you are assured of an authentic, imaginative, informative, linguistically great, and advantageous thesis that adheres to all your needs. So, why continue considering different writers when you have discovered the best in the field?

Editing

Custom Papers

Not a single student can avoid writing custom papers. However, a total lack of experience, skills, and time makes it very hard to produce a superb writing piece. Therefore, if you are seeking professional help, turn to us. Our specialized and experienced writers compose a variety of model papers, including custom essays, college term papers, research papers, book reports, MBA essays, executive summaries, dissertations, Ph.D. theses, admission essays, and research proposals for college and university students at any level.

Coursework

Essay Writing

Most of the students disregard the critical principles of essay writing and compose papers below sensible guidelines. Therefore, with Custom Writings Help, one should not worry about his/her essay. Our Writers compose informative and engaging content on all complexities and topics. We write meaningful and smart essays while prioritizing all aspects that bring about a good grade, such as impeccable grammar, proper structure, zero-plagiarism, and conformance to guidelines.

Coursework

Coourse Work Writing

Don't let the seemingly never-ending onslaught of writing assignments get you down. If you are looking where to get course work assistance online, the writers at Custom Writings Help are here to assist you with all of your writing needs. We undertake to unique delivery of papers that meet the professor's requirements. The content is proofread, edited, and checked plagiarism before submission to customers. No matter how big or small your work is, we will deliver on time. Try US Now! !

Coursework

Dissertation Writing Service

High-Quality Dissertation Writing Services are rare. They require Ph.D. academicians – not easily found. However, are an exception. The years, time, and resources we have invested in the dissertation world has given us a competitive advantage over others. Choose to come to Custom Writings Help; You will find perfect Ph.D. consultants who have written hundreds of dissertations theses ready to help you. Let our dissertation-writing services help you craft your dissertation, for you are assured we will give you the results.