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.
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.
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.
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.
Format the values in column H as Currency with zero decimal places.
Click cell G4 and use the
AutoFilter
arrow to sort by date oldest to newest.
Click cell F4 and use the
AutoFilter
arrow to sort by product/service name in ascending order.
Filter the
Date
column to show only contracts for September using the All Dates in the Period option.
Edit the label in cell A2 to display
Contract Amounts for September
.
Select cells A1:H2 and press Ctrl+1 to open the
Format Cells
dialog box. On the
Alignment
tab, choose Center Across Selection.
Change the font size for cells A1:H2 to 20 pt (Figure 4-102) and close the
Queries & Connections
pane.
Figure 4-102 Imported data sorted and filtered
Copy the Contracts sheet to the end and name the copy
Data
.
Clear the date filter.
Select cell A5 and click the PivotTable button [
Insert
tab,
Tables
group]. The range is identified as the
EllerSoftwareText
file.
Verify that New Worksheet is selected, deselect the Add this data to the Data Model button, and click OK.
Name the sheet
PivotTable
. Close the
Queries & Connections
pane.
Show the Product/Service and Contract fields in the
PivotTable
.
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).
Figure 4-103 “Contract†field appears twice in the report
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.
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.
Select cell B4 and set its
Custom Name
to
Total Contracts
and the number format to Currency with zero decimal places.
Apply Brown, Pivot Style Dark 3.
Select the Data sheet tab and copy cells A1:A2. Paste them in cell A1 on the PivotTable sheet.
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).
Figure 4-104 Completed
PivotTable
Copy the Data sheet to the end and name the copy
Subtotals
.
Select cell D5 and sort by City in A to Z order.
Select cell A5 and convert the table to a range. Select cells A5:H31 and apply No Fill [
Home
tab,
Font
group].
Use the
Subtotal
command to show a SUM for the contract amounts for each city.
Edit the label in cell A2 to display
Contract Amounts by City
.
Format the sheet to Landscape orientation, center the page vertically, and scale it to fit one page.
Click the Billable Hours sheet tab and select cell A4.
Click the Format as Table button [
Home
tab,
Styles
group], use Orange, Table Style Medium 10, and remove the data connections.
Type
5% Add On
in cell E4 and press Enter.
Build a formula in cell E5 to multiply cell D5 by 105% and press Enter to copy the formula.
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.
Use the
AutoFilter
arrows to sort by date in oldest to newest order.
Save and close the workbook (Figure 4-105).
Figure 4-105 Excel 4-4 completed
Upload and save your project file.
Submit project for grading.
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.
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.
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.
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.
Format the values in column H as Currency with zero decimal places.
Click cell G4 and use the
AutoFilter
arrow to sort by date oldest to newest.
Click cell F4 and use the
AutoFilter
arrow to sort by product/service name in ascending order.
Filter the
Date
column to show only contracts for September using the All Dates in the Period option.
Edit the label in cell A2 to display
Contract Amounts for September
.
Select cells A1:H2 and press Ctrl+1 to open the
Format Cells
dialog box. On the
Alignment
tab, choose Center Across Selection.
Change the font size for cells A1:H2 to 20 pt (Figure 4-102) and close the
Queries & Connections
pane.
Figure 4-102 Imported data sorted and filtered
Copy the Contracts sheet to the end and name the copy
Data
.
Clear the date filter.
Select cell A5 and click the PivotTable button [
Insert
tab,
Tables
group]. The range is identified as the
EllerSoftwareText
file.
Verify that New Worksheet is selected, deselect the Add this data to the Data Model button, and click OK.
Name the sheet
PivotTable
. Close the
Queries & Connections
pane.
Show the Product/Service and Contract fields in the
PivotTable
.
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).
Figure 4-103 “Contract†field appears twice in the report
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.
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.
Select cell B4 and set its
Custom Name
to
Total Contracts
and the number format to Currency with zero decimal places.
Apply Brown, Pivot Style Dark 3.
Select the Data sheet tab and copy cells A1:A2. Paste them in cell A1 on the PivotTable sheet.
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).
Figure 4-104 Completed
PivotTable
Copy the Data sheet to the end and name the copy
Subtotals
.
Select cell D5 and sort by City in A to Z order.
Select cell A5 and convert the table to a range. Select cells A5:H31 and apply No Fill [
Home
tab,
Font
group].
Use the
Subtotal
command to show a SUM for the contract amounts for each city.
Edit the label in cell A2 to display
Contract Amounts by City
.
Format the sheet to Landscape orientation, center the page vertically, and scale it to fit one page.
Click the Billable Hours sheet tab and select cell A4.
Click the Format as Table button [
Home
tab,
Styles
group], use Orange, Table Style Medium 10, and remove the data connections.
Type
5% Add On
in cell E4 and press Enter.
Build a formula in cell E5 to multiply cell D5 by 105% and press Enter to copy the formula.
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.
Use the
AutoFilter
arrows to sort by date in oldest to newest order.
Save and close the workbook (Figure 4-105).
Figure 4-105 Excel 4-4 completed
Upload and save your project file.
Submit project for grading.
Eller Software Services
Contract Amounts, September through December
Eller Software Services
Billable Hours
Client Name
Heather Guyan
Karen Talon
Mike Gunderson
Thomas Larson
Wade Whitworth
Paula Valentine
Richard Malinowski
Robert Gneiss
Susan Charlotte
Martin Green
Heather Guyan
Craig Brand
Glenn Ladewig
Terri Olander
Karen Talon
Hilary Marschke
Shelly Vlcko
Wade Whitworth
Michelle Cronin
Paula Valentine
Paula Valentine
Hilary Marschke
Arthur Weston
Christine Barton
Charlie Lindberg
Hilary Marschke
Shelly Vlcko
Mike Gunderson
Jeremie Midboe
Arthur Weston
Susan Charlotte
Thomas Larson
Arnold Robert
Wade Whitworth
Paula Valentine
Michelle Cronin
Amir Atef
Richard Malinowski
Charlie Lindberg
Christine Barton
Robert Gneiss
Product/Service
Training
Accounting Software
Technical Support
Training
Accounting Software
Training
Technical Support
ERP: Enterprise Resource Planning
ERP: Enterprise Resource Planning
Accounting Software
Training
Technical Support
Accounting Software
POS: Point of Sale Software
Accounting Software
Training
Accounting Software
Accounting Software
POS: Point of Sale Software
Training
Training
Training
Technical Support
POS: Point of Sale Software
ERP: Enterprise Resource Planning
Training
Accounting Software
Technical Support
POS: Point of Sale Software
Technical Support
ERP: Enterprise Resource Planning
Training
Accounting Software
Accounting Software
Training
POS: Point of Sale Software
Training
Technical Support
ERP: Enterprise Resource Planning
POS: Point of Sale Software
ERP: Enterprise Resource Planning
Date
Billable
10/7/2019
3.25
10/1/2019
5.50
12/18/2019
4.50
12/5/2019
5.00
11/14/2019
5.00
11/15/2019
4.00
1/5/2020
3.00
9/1/2019
6.00
3/5/2020
8.00
3/7/2020
4.00
2/3/2020
4.50
10/1/2019
8.00
9/1/2019
5.00
10/15/2019
6.50
9/1/2019
3.50
3/8/2020
5.00
9/15/2019
5.00
2/2/2020
6.00
12/15/2019
4.00
4/5/2020
4.00
10/15/2019
4.00
11/15/2019
5.00
5/5/2020
6.00
11/15/2019
4.00
4/5/2020
6.00
11/15/2019
5.00
11/16/2019
5.00
10/28/2019
4.75
11/1/2019
3.50
10/1/2019
3.00
11/1/2019
6.00
3/18/2020
4.00
9/15/2019
5.00
5/17/2020
5.00
10/15/2019
4.00
12/15/2019
4.00
12/15/2019
4.00
10/1/2019
4.50
9/1/2019
4.00
11/15/2019
2.00
2/5/2020
7.00
Martin Green
Jerri Salzman
Juan Garcia
Elizabeth Jones
Robert Lawlor
Patricia Glunz
Accounting Software
POS: Point of Sale Software
Training
Technical Support
Technical Support
POS: Point of Sale Software
12/15/2019
3/9/2020
11/15/2019
10/1/2019
1/8/2020
12/15/2019
5.00
4.50
4.00
3.50
2.75
3.25
Purchase answer to see full
attachment
