Open the file in Excel. Review the data. This file contains a listing of your expenses over the past six months. ( SEE ATTACHMENT L8 DATAFILE) Part 1: Create the PivotTable Save the
- Open the file in Excel. Review the data. This file contains a listing of your expenses over the past six months. ( SEE ATTACHMENT L8 DATAFILE)
Part 1: Create the PivotTable
Save the data file as CIS105_Lesson8Project1_MEID.xlsx.
Insert a PivotTable on a new worksheet using data from A1:E246. Use the following fields in your PivotTable:
Amount Paid (Values)
Category of Expenses (Rows)
Payment Date (Columns) – in order to have the table summarize expenses by each month, you will need to drag the "Payment Date" field from the field list into the Columns area of the Pivot Table design grid. If you simply check the box in front of the field name, it will not summarize the dates into months.
Split with Roommates? (Filters)
Compare your PivotTable with the following image to ensure that you have selected the correct fields. (SEE PROJECT 8 PART 1 ATTACHMENT)
Part 2: Format the PivotTable
Rename the labels in your PivotTable:
“Row Labels” should be “Expense Categories”
“Column Labels” should be “Months”
Change the PivotTable design style to any color in the Medium color palette.
Apply the Accounting Number format to the numerical data in your PivotTable.
Rename the PivotTable sheet to Analysis.
Use the Filter to only show expenses that are shared with your roommates.
After viewing the data, remove the filter so that the data shows all expenses.
Insert a Slicer for the “Pay To” field.
Move the Slicer to the right of the PivotTable so that it does not overlap the PivotTable.
Use the Slicer to filter the data in the PivotTable to only show expenses at Starbucks.
Select a Slicer Style that matches the colors in your PivotTable.
Change the Page Layout for the Analysis worksheet to Landscape and the margins to Narrow.
Save the file. Compare your worksheet to the image below. (Your colors may be different.) (SEE PROJECT 8 PART 1 ATTACHMENT)
Expenses
| Category of Expense | Pay To | Payment Date | Amount Paid | Split with Roommates? |
| Utilities | Cox (WIFI) | 1/1/21 | 69.59 | YES |
| Utilities | SRP (Electric) | 1/5/21 | 145 | YES |
| Utilities | City of Chandler (Trash & Water) | 1/5/21 | 32 | YES |
| Utilities | Southwest Gas (Gas) | 1/5/21 | 45 | YES |
| Utilities (Apt. Complex) | Aspen Place | 1/1/21 | 84 | YES |
| Parking | Aspen Place | 1/1/21 | 50 | YES |
| Parking | City of Chandler | 1/10/21 | 2.5 | NO |
| Rent | Aspen Place | 1/1/21 | 1750 | YES |
| Credit Card Bills | Best Buy | 1/15/21 | 45 | NO |
| Credit Card Bills | Kohls | 1/15/21 | 30 | NO |
| Credit Card Bills | Gap | 1/15/21 | 25 | NO |
| Credit Card Bills | Visa | 1/15/21 | 75 | NO |
| Credit Card Bills | Macys | 1/15/21 | 80 | NO |
| Misc. Debt | Student Loan | 1/28/21 | 92 | NO |
| Entertainment | Netflix | 1/30/21 | 14.99 | YES |
| Entertainment | Movie Theatre | 1/8/21 | 14 | NO |
| Entertainment | Movie Theatre | 1/29/21 | 21 | NO |
| Entertainment | Top Golf | 1/15/21 | 53 | NO |
| Food (Groceries) | Safeway | 1/1/21 | 45 | NO |
| Food (Groceries) | Safeway | 1/5/21 | 36 | NO |
| Food (Groceries) | Safeway | 1/15/21 | 83 | NO |
| Food (Groceries) | Walmart | 1/21/21 | 23 | NO |
| Food (Groceries) | Albertsons | 1/8/21 | 34 | NO |
| Food (Groceries) | Albertsons | 1/28/21 | 87 | NO |
| Car Expenses | Chevron | 1/30/21 | 30 | NO |
| Car Expenses | Shell | 1/30/21 | 32 | NO |
| Car Expenses | Ford Motor Co. | 1/15/21 | 350 | NO |
| Car Expenses | Allied Insurance | 1/1/21 | 100 | NO |
| Car Expenses | Jiffy Lube | 1/20/21 | 24.99 | NO |
| Car Expenses | Cobblestone Car Wash | 1/10/21 | 16.99 | NO |
| Food (Eating Out) | Chik-Fil-A | 1/2/21 | 8.75 | NO |
| Food (Eating Out) | Chik-Fil-A | 1/4/21 | 11.35 | NO |
| Food (Eating Out) | Chik-Fil-A | 1/8/21 | 14.25 | NO |
| Food (Eating Out) | Red Robin | 1/20/21 | 23.56 | NO |
| Food (Eating Out) | Chilis | 1/16/21 | 18.75 | NO |
| Food (Eating Out) | Four Peaks Brewing | 1/30/21 | 35 | NO |
| Food (Eating Out) | Starbucks | 1/2/21 | 4.75 | NO |
| Food (Eating Out) | Starbucks | 1/4/21 | 4.75 | NO |
| Food (Eating Out) | Starbucks | 1/5/21 | 4.75 | NO |
| Food (Eating Out) | Starbucks | 1/9/21 | 3.25 | NO |
| Food (Eating Out) | Dutch Bros | 1/12/21 | 5.85 | NO |
| Food (Eating Out) | Dutch Bros | 1/14/21 | 6.35 | NO |
| Food (Eating Out) | Dutch Bros | 1/20/21 | 6.35 | NO |
| Food (Eating Out) | Dutch Bros | 1/29/21 | 6.35 | NO |
| Utilities | Cox (WIFI) | 2/1/21 | 69.59 | YES |
| Utilities | SRP (Electric) | 2/5/21 | 132 | YES |
| Utilities | City of Chandler (Trash & Water) | 2/5/21 | 32 | YES |
| Utilities | Southwest Gas (Gas) | 2/5/21 | 48 | YES |
| Utilities (Apt. Complex) | Aspen Place | 2/5/21 | 86 | YES |
| Parking | Aspen Place | 2/1/21 | 50 | YES |
| Parking | City of Chandler | 2/10/21 | 2.5 | NO |
| Rent | Aspen Place | 2/1/21 | 1750 | YES |
| Credit Card Bills | Best Buy | 2/15/21 | 45 | NO |
| Credit Card Bills | Kohls | 2/15/21 | 30 | NO |
| Credit Card Bills | Gap | 2/15/21 | 25 | NO |
| Credit Card Bills | Visa | 2/15/21 | 75 | NO |
| Credit Card Bills | Macys | 2/15/21 | 80 | NO |
| Misc. Debt | Student Loan | 2/28/21 | 92 | NO |
| Entertainment | Netflix | 2/28/21 | 14.99 | YES |
| Entertainment | Golf Tournament | 2/15/21 | 250 | NO |
| Food (Groceries) | Safeway | 2/1/21 | 45 | NO |
| Food (Groceries) | Safeway | 2/5/21 | 36 | NO |
| Food (Groceries) | Safeway | 2/15/21 | 83 | NO |
| Food (Groceries) | Albertsons | 2/8/21 | 34 | NO |
| Food (Groceries) | Albertsons | 2/28/21 | 87 | NO |
| Car Expenses | Chevron | 2/1/21 | 35 | NO |
| Car Expenses | Shell | 2/1/21 | 28 | NO |
| Car Expenses | Ford Motor Co. | 2/15/21 | 350 | NO |
| Car Expenses | Allied Insurance | 2/1/21 | 100 | NO |
| Car Expenses | Cobblestone Car Wash | 2/10/21 | 16.99 | NO |
| Food (Eating Out) | Chik-Fil-A | 2/4/21 | 8.75 | NO |
| Food (Eating Out) | Chik-Fil-A | 2/6/21 | 11.35 | NO |
| Food (Eating Out) | Chik-Fil-A | 2/10/21 | 14.25 | NO |
| Food (Eating Out) | Chilis | 2/28/21 | 18.75 | NO |
| Food (Eating Out) | Starbucks | 2/1/21 | 4.75 | NO |
| Food (Eating Out) | Starbucks | 2/5/21 | 5.5 | NO |
| Food (Eating Out) | Starbucks | 2/17/21 | 6.35 | NO |
| Food (Eating Out) | Starbucks | 2/26/21 | 8.5 | NO |
| Food (Eating Out) | Dutch Bros | 2/12/21 | 5.85 | NO |
| Food (Eating Out) | Dutch Bros | 2/20/21 | 7.15 | NO |
| Food (Eating Out) | Dutch Bros | 2/28/21 | 8.35 | NO |
| Food (Eating Out) | Dutch Bros | 2/28/21 | 3.5 | NO |
| Utilities | Cox (WIFI) | 3/1/21 | 69.59 | YES |
| Utilities | SRP (Electric) | 3/5/21 | 35 | YES |
| Utilities | City of Chandler (Trash & Water) | 3/5/21 | 32 | YES |
| Utilities | Southwest Gas (Gas) | 3/5/21 | 42 | YES |
| Utilities (Apt. Complex) | Aspen Place | 3/1/21 | 79 | YES |
| Parking | Aspen Place | 3/1/21 | 50 | YES |
| Rent | Aspen Place | 3/1/21 | 1750 | YES |
| Credit Card Bills | Best Buy | 3/15/21 | 45 | NO |
| Credit Card Bills | Kohls | 3/15/21 | 30 | NO |
| Credit Card Bills | Gap | 3/15/21 | 25 | NO |
| Credit Card Bills | Visa | 3/15/21 | 75 | NO |
| Credit Card Bills | Macys | 3/15/21 | 80 | NO |
| Misc. Debt | Student Loan | 3/28/21 | 92 | NO |
| Entertainment | Netflix | 3/30/21 | 14.99 | YES |
| Entertainment | Movie Theatre | 3/15/21 | 14 | NO |
| Entertainment | Baseball Game | 3/20/21 | 95 | NO |
| Food (Groceries) | Safeway | 3/1/21 | 42 | NO |
| Food (Groceries) | Safeway | 3/3/21 | 38 | NO |
| Food (Groceries) | Safeway | 3/15/21 | 52 | NO |
| Food (Groceries) | Walmart | 3/20/21 | 86 | NO |
| Food (Groceries) | Albertsons | 3/10/21 | 31 | NO |
| Food (Groceries) | Albertsons | 3/30/21 | 64 | NO |
| Car Expenses | Chevron | 3/30/21 | 45 | NO |
| Car Expenses | Shell | 3/30/21 | 23 | NO |
| Car Expenses | Ford Motor Co. | 3/15/21 | 350 | NO |
| Car Expenses | Allied Insurance | 3/1/21 | 100 | NO |
| Car Expenses | Cobblestone Car Wash | 3/10/21 | 16.99 | NO |
| Food (Eating Out) | Chik-Fil-A | 3/3/21 | 8.75 | NO |
| Food (Eating Out) | Chik-Fil-A | 3/6/21 | 11.35 | NO |
| Food (Eating Out) | Chik-Fil-A | 3/20/21 | 14.25 | NO |
| Food (Eating Out) | Applebees | 3/12/21 | 18 | NO |
| Food (Eating Out) | Olive Garden | 3/29/21 | 28.15 | NO |
| Food (Eating Out) | Blue 32 | 3/10/21 | 42.5 | NO |
| Food (Eating Out) | Starbucks | 3/1/21 | 4.75 | NO |
| Food (Eating Out) | Starbucks | 3/6/21 | 5.85 | NO |
| Food (Eating Out) | Starbucks | 3/19/21 | 13.5 | NO |
| Food (Eating Out) | Starbucks | 3/25/21 | 3.25 | NO |
| Food (Eating Out) | Dutch Bros | 3/31/21 | 6.35 | NO |
| Food (Eating Out) | Dutch Bros | 3/3/21 | 6.5 | NO |
| Food (Eating Out) | Dutch Bros | 3/10/21 | 8.15 | NO |
| Food (Eating Out) | Dutch Bros | 3/15/21 | 2.35 | NO |
| Utilities | Cox (WIFI) | 4/1/21 | 69.59 | YES |
| Utilities | SRP (Electric) | 4/5/21 | 35 | YES |
| Utilities | City of Chandler (Trash & Water) | 4/5/21 | 32 | YES |
| Utilities | Southwest Gas (Gas) | 4/5/21 | 42 | YES |
| Utilities (Apt. Complex) | Aspen Place | 4/1/21 | 79 | YES |
| Parking | Aspen Place | 4/1/21 | 50 | YES |
| Rent | Aspen Place | 4/1/21 | 1750 | YES |
| Credit Card Bills | Best Buy | 4/15/21 | 45 | NO |
| Credit Card Bills | Kohls | 4/15/21 | 30 | NO |
| Credit Card Bills | Gap | 4/15/21 | 25 | NO |
| Credit Card Bills | Visa | 4/15/21 | 75 | NO |
| Credit Card Bills | Macys | 4/15/21 | 80 | NO |
| Misc. Debt | Student Loan | 4/28/21 | 92 | NO |
| Entertainment | Netflix | 4/30/21 | 14.99 | YES |
| Entertainment | Movie Theatre | 4/15/21 | 14 | NO |
| Food (Groceries) | Safeway | 4/1/21 | 42 | NO |
| Food (Groceries) | Safeway | 4/4/21 | 38 | NO |
| Food (Groceries) | Safeway | 4/16/21 | 52 | NO |
| Food (Groceries) | Walmart | 4/14/21 | 86 | NO |
| Food (Groceries) | Albertsons | 4/30/21 | 31 | NO |
| Food (Groceries) | Albertsons | 4/26/21 | 64 | NO |
| Car Expenses | Chevron | 4/30/21 | 58 | NO |
| Car Expenses | Shell | 4/30/21 | 45 | NO |
| Car Expenses | Ford Motor Co. | 4/15/21 | 350 | NO |
| Car Expenses | Allied Insurance | 4/1/21 | 100 | NO |
| Car Expenses | Cobblestone Car Wash | 4/10/21 | 16.99 | NO |
| Food (Eating Out) | Chik-Fil-A | 4/3/21 | 8.75 | NO |
| Food (Eating Out) | Chik-Fil-A | 4/8/21 | 11.35 | NO |
| Food (Eating Out) | Chik-Fil-A | 4/20/21 | 14.25 | NO |
| Food (Eating Out) | Applebees | 4/28/21 | 18 | NO |
| Food (Eating Out) | Olive Garden | 4/29/21 | 28.15 | NO |
| Food (Eating Out) | Blue 32 | 4/12/21 | 42.5 | NO |
| Food (Eating Out) | Starbucks | 4/1/21 | 4.75 | NO |
| Food (Eating Out) | Starbucks | 4/6/21 | 5.86 | NO |
| Food (Eating Out) | Starbucks | 4/15/21 | 6.45 | NO |
| Food (Eating Out) | Starbucks | 4/15/21 | 12.45 | NO |
| Food (Eating Out) | Dutch Bros | 4/3/21 | 8.45 | NO |
| Food (Eating Out) | Dutch Bros | 4/8/21 | 6.35 | NO |
| Food (Eating Out) | Dutch Bros | 4/25/21 | 5.75 | NO |
| Food (Eating Out) | Dutch Bros | 4/30/21 | 6.15 | NO |
| Utilities | Cox (WIFI) | 5/1/21 | 69.59 | YES |
| Utilities | SRP (Electric) | 5/5/21 | 145 | YES |
| Utilities | City of Chandler (Trash & Water) | 5/5/21 | 32 | YES |
| Utilities | Southwest Gas (Gas) | 5/5/21 | 45 | YES |
| Utilities (Apt. Complex) | Aspen Place | 5/1/21 | 84 | YES |
| Parking | Aspen Place | 5/1/21 | 50 | YES |
| Parking | City of Chandler | 5/10/21 | 2.5 | NO |
| Rent | Aspen Place | 5/1/21 | 1750 | YES |
| Credit Card Bills | Best Buy | 5/15/21 | 45 | NO |
| Credit Card Bills | Kohls | 5/15/21 | 30 | NO |
| Credit Card Bills | Gap | 5/15/21 | 25 | NO |
| Credit Card Bills | Visa | 5/15/21 | 75 | NO |
| Credit Card Bills | Macys | 5/15/21 | 80 | NO |
| Misc. Debt | Student Loan | 5/28/21 | 92 | NO |
| Entertainment | Netflix | 5/30/21 | 14.99 | YES |
| Entertainment | Movie Theatre | 5/8/21 | 14 | NO |
| Entertainment | Movie Theatre | 5/29/21 | 21 | NO |
| Entertainment | Top Golf | 5/15/21 | 53 | NO |
| Food (Groceries) | Safeway | 5/1/21 | 85 | NO |
| Food (Groceries) | Safeway | 5/5/21 | 37 | NO |
| Food (Groceries) | Safeway | 5/15/21 | 67 | NO |
| Food (Groceries) | Walmart | 5/21/21 | 95 | NO |
| Food (Groceries) | Albertsons | 5/8/21 | 14 | NO |
| Food (Groceries) | Albertsons | 5/28/21 | 75 | NO |
| Car Expenses | Chevron | 5/30/21 | 47 | NO |
| Car Expenses | Shell | 5/30/21 | 35 | NO |
| Car Expenses | Ford Motor Co. | 5/15/21 | 350 | NO |
| Car Expenses | Allied Insurance | 5/1/21 | 100 | NO |
| Car Expenses | Jiffy Lube | 5/20/21 | 24.99 | NO |
| Car Expenses | Cobblestone Car Wash | 5/10/21 | 16.99 | NO |
| Food (Eating Out) | Chik-Fil-A | 5/2/21 | 8.75 | NO |
| Food (Eating Out) | Chik-Fil-A | 5/4/21 | 11.35 | NO |
| Food (Eating Out) | Chik-Fil-A | 5/8/21 | 14.25 | NO |
| Food (Eating Out) | Red Robin | 5/20/21 | 23.56 | NO |
| Food (Eating Out) | Chilis | 5/17/21 | 18.75 | NO |
| Food (Eating Out) | Four Peaks Brewing | 5/30/21 | 35 | NO |
| Food (Eating Out) | Starbucks | 5/2/21 | 3.5 | NO |
| Food (Eating Out) | Starbucks | 5/4/21 | 6.84 | NO |
| Food (Eating Out) | Starbucks | 5/5/21 | 8.25 | NO |
| Food (Eating Out) | Starbucks | 5/9/21 | 3.78 | NO |
| Food (Eating Out) | Dutch Bros | 5/12/21 | 6.15 | NO |
| Food (Eating Out) | Dutch Bros | 5/14/21 | 7.15 | NO |
| Food (Eating Out) | Dutch Bros | 5/20/21 | 5.5 | NO |
| Food (Eating Out) | Dutch Bros | 5/29/21 | 6.35 | NO |
| Utilities | Cox (WIFI) | 6/1/21 | 69.59 | YES |
| Utilities | SRP (Electric) | 6/5/21 | 132 | YES |
| Utilities | City of Chandler (Trash & Water) | 6/5/21 | 32 | YES |
| Utilities | Southwest Gas (Gas) | 6/5/21 | 48 | YES |
| Utilities (Apt. Complex) | Aspen Place | 6/5/21 | 86 | YES |
| Parking | Aspen Place | 6/1/21 | 50 | YES |
| Parking | City of Chandler | 6/10/21 | 2.5 | NO |
| Rent | Aspen Place | 6/1/21 | 1750 | YES |
| Credit Card Bills | Best Buy | 6/15/21 | 45 | NO |
| Credit Card Bills | Kohls | 6/15/21 | 30 | NO |
| Credit Card Bills | Gap | 6/15/21 | 25 | NO |
| Credit Card Bills | Visa | 6/15/21 | 75 | NO |
| Credit Card Bills | Macys | 6/15/21 | 80 | NO |
| Misc. Debt | Student Loan | 6/28/21 | 92 | NO |
| Entertainment | Netflix | 6/28/21 | 14.99 | YES |
| Entertainment | Movie Theatre | 6/15/21 | 14 | NO |
| Food (Groceries) | Safeway | 6/1/21 | 45 | NO |
| Food (Groceries) | Safeway | 6/5/21 | 36 | NO |
| Food (Groceries) | Safeway | 6/15/21 | 83 | NO |
| Food (Groceries) | Albertsons | 6/9/21 | 34 | NO |
| Food (Groceries) | Albertsons | 6/28/21 | 87 | NO |
| Car Expenses | Chevron | 6/1/21 | 45 | NO |
| Car Expenses | Shell | 6/1/21 | 32 | NO |
| Car Expenses | Ford Motor Co. | 6/15/21 | 350 | NO |
| Car Expenses | Allied Insurance | 6/1/21 | 100 | NO |
| Car Expenses | Cobblestone Car Wash | 6/10/21 | 16.99 | NO |
| Food (Eating Out) | Chik-Fil-A | 6/4/21 | 8.75 | NO |
| Food (Eating Out) | Chik-Fil-A | 6/6/21 | 11.35 | NO |
| Food (Eating Out) | Chik-Fil-A | 6/10/21 | 14.25 | NO |
| Food (Eating Out) | Chilis | 6/28/21 | 18.75 | NO |
| Food (Eating Out) | Starbucks | 6/1/21 | 6.82 | NO |
| Food (Eating Out) | Starbucks | 6/5/21 | 4.85 | NO |
| Food (Eating Out) | Starbucks | 6/17/21 | 12.45 | NO |
| Food (Eating Out) | Starbucks | 6/26/21 | 6.45 | NO |
| Food (Eating Out) | Dutch Bros | 6/12/21 | 5.85 | NO |
| Food (Eating Out) | Dutch Bros | 6/20/21 | 4.15 | NO |
| Food (Eating Out) | Dutch Bros | 6/28/21 | 7.15 | NO |
| Food (Eating Out) | Dutch Bros | 6/28/21 | 2.25 | NO |
Collepals.com Plagiarism Free Papers
Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.
Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS
Why Hire Collepals.com writers to do your paper?
Quality- We are experienced and have access to ample research materials.
We write plagiarism Free Content
Confidential- We never share or sell your personal information to third parties.
Support-Chat with us today! We are always waiting to answer all your questions.
