Microsoft Excel 2013

  1. Download the Unformatted Excel workbook attached, and change the name of the first worksheet tab to Expenses instead of “Sheet 1” and change the tab color to red.
  2. On theExpensessheetinsert 2 new rows after row 1.
  3. Merge and Center A1:M1 and apply the Heading1 style and use the Accent 6 themed cell style.
  4. Select column A to Mand set the column width to 10.Now Autofit columns F and L.
  5. Using the NOW function enter the current date and time in cell A2. (You need to change the column width for column A to 15 to see the data).
  6. Select cells A5:M5 and in the Alignment group use the Orientation feature to change to text alignment to Angle Counterclockwise.
  7. Select cells O5:P29 and apply all bordersto this range.
  8. Select cells A5:F29 and name this range “Alone”. Select cells H5:M29 and name this range “Roommate”.
  9. Enter a Simple Formula in cell M6 that will calculate the difference between the Total Expenses from the Living Alone Tableand Total Expenses from Living with Roommate Table. Now copy the formula in cell M6 to cells M7 through M29.
  10. Use Formula Auto Complete to enter a formula in cell M30 to count the number of cells between A6 to M29 that are blank (do not contain text or numbers).
  11. Wrap the text in cell A31 and A32 and format the text to Bold, Italics and color Red.
  12. In cell B31, use the MAX function to determine the maximum in column L.
  13. Format cellB31 andB32 to Comma style.
  14. Select cells B6 to M29and change the format for these cells toCurrencyand to show no decimals.
  15. In cell O6 type a formula that will calculate if the Total Expensesfrom the Living Alone tableare over or under the Maximum Budget. (Hint:Total Expenses - absolute reference to cell B32) Copy the formula in cell O6 to range O7:O29.
  16. Select cell range A5:F29 and insert a table with headers. Repeat the previous steps for cell range H5:M29. Apply the “Table Style – Medium 1” table style to both tables.
  17. Select cells B6 to F29 and apply Conditional Formatting using the Highlight Cells Rules to highlight cells greater than 3000 using Green Fill with Dark Green Text.
  18. Select cells H6 to M29 and create a 3DPie Chart. Move the chart to a new sheet and name it “Budget”. Change the fill color of the chart area to Blue, Accent 1.
  19. Using the drag and drop method, move the Budget sheet so it is the last sheet in the workbook.
  20. Delete Sheet 2.
  21. Return to the Expenses sheet.Set the print area to include only cells A5 to F32 and change the orientation to Landscape.
  22. Using the Find and Replacefunction, replace all the occurrences of the word “Feb” with the word “February”
  23. Using the IF function, type a formula in P6 to write the word “Under” when the budget in cell O6 is negative, and “Over” when it’sa positive number. Copy the formula in cell P6 to cells P7 through P29.