19 Aug 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 |
Our website has a team of professional writers who can help you write any of your homework. They will write your papers from scratch. We also have a team of editors just to make sure all papers are of HIGH QUALITY & PLAGIARISM FREE. To make an Order you only need to click Ask A Question and we will direct you to our Order Page at WriteEdu. Then fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.
Fill in all the assignment paper details that are required in the order form with the standard information being the page count, deadline, academic level and type of paper. It is advisable to have this information at hand so that you can quickly fill in the necessary information needed in the form for the essay writer to be immediately assigned to your writing project. Make payment for the custom essay order to enable us to assign a suitable writer to your order. Payments are made through Paypal on a secured billing page. Finally, sit back and relax.
Do you need help with this question?
Get assignment help from WriteEdu.com Paper Writing Website and forget about your problems.
WriteEdu provides custom & cheap essay writing 100% original, plagiarism free essays, assignments & dissertations.
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.
Chat with us today! We are always waiting to answer all your questions.