Credit Card Expenses
You started recording every credit card transaction so that you can analyze your monthly expenses. You used an Excel worksheet to track dates, places, categories, and amounts. Because you are a consultant who travels periodically, you also have business expenses. You included a column to indicate the business-related transactions. Now you need to convert the data to a table, sort and filter the data to analyze it, and apply conditional formatting to apply a visual effect for further analysis.
For the purpose of grading the project you are required to perform the following tasks:
Start Excel. Open the downloaded Excel file namedexploring_e04_grader_a1.xlsx.
In the Dining Out worksheet, convert the data to a table. Apply Table Style Light 14.
Remove duplicate rows in the Dining Out worksheet.
Sort the table by Description in alphabetical order, then by Store in alphabetical order, and then by Amount from the smallest to the largest.
Filter the records to show personal (i.e., non-business) lunch and dinner expenses.
Display the Total Row. In cell D48, select the function to total the Amount values. In cell E48, do not show a total.
In the June Expenses worksheet, apply the conditional formatting style that displays a Green Data Bar Gradient Fill in the range D2:D47.
Create a conditional formatting rule with these specifications to the range A2:C47: • Formula to determine which cells to format • Formula: =(AND(argument1,argument2)) where argument1 compares $D2 to see if it is greater than or equal to 100 and argument 2 compares $E2 to see if the text is"Yes". • Light Green (Standard) fill color • Green border color • Outline preset border style