Graders Instructions Excel 2019 Project Exp19 Excel Ch09 Cap

Grader Instructionsexcel 2019 Projectexp19 Excel Ch09 Capassessment

Complete this project by applying consistent formatting across multiple worksheets, creating hyperlinks, inserting 3-D cell references, linking to an external rates workbook, fixing circular references, setting data validation rules, unlocking cells, and protecting sheets without passwords. Finalize the workbook by adding footers with your name, sheet name codes, and filename codes, then mark the workbook as final and save it for submission.

Paper For Above instruction

The project involves comprehensive Excel skills to manage and analyze data related to restaurant customer transactions, with the aim of finalizing a professional and functional workbook suitable for operational use. The task includes formatting, formula creation, hyperlinking, external data linking, error fixing, data validation, worksheet protection, and footer customization, culminating in marking the document as final and submitting it.

Initially, the project requires opening the specified Excel file named "Exp19_Excel_Ch09_Cap_Assessment_Tips.xlsx" and addressing a circular reference error that appears upon opening. Circular references can cause calculation problems, and fixing them involves inspecting formulas and adjusting them to prevent self-referencing loops. The next step involves copying formatting from the Friday worksheet, specifically the "Tip Left" column's fill color and number format, across worksheets for Saturday through Monday. This ensures visual consistency across the weekly sheets.

Subsequently, the task directs inserting total sums in ranges B25:E25 on each daily worksheet, applying the "Totals" style, then ungrouping sheets to work independently. On the summary "Week" worksheet, hyperlinks are to be inserted in cells A5 to A9 directing to total cells in respective daily sheets, with screen tips indicating each weekday. Testing hyperlinks confirms functionality, essential for quick navigation within the workbook.

The next phase involves creating cell references that aggregate daily totals into the weekly summary. Using 3-D references, formulas in cells B5 to E5 and their copies in subsequent rows will connect to cell B25 in each specific day's sheet. These references are extended to create cumulative totals in cells B10 to E10, summarizing the week's food purchases across all days.

An external rates workbook, "Exp_Excel_Ch09_Cap_Assessment_Rates.xlsx," is then linked to the main workbook to calculate tips for beverage workers and assistants. Formulas in the Week worksheet multiply rates from the external file with relevant totals or subtotals, formatted as accounting numbers for clarity. Fixing the circular reference involved understanding and correcting the interplay of formula dependencies, ensuring accurate tip calculations.

Moreover, data validation rules are to be established in the Friday worksheet's tips range (E5:E24), allowing only decimal values greater than or equal to zero, with appropriate input and error messages. These validation rules are copied to other daily sheets to restrict invalid data entry. Cells designated for data entry are to be unlocked to permit user modifications, while maintaining overall worksheet protection to prevent unintended edits in other areas.

Finally, footer sections are added to all sheets, including the user’s name aligned left, sheet name code centered, and filename code aligned right, fostering professional documentation. Each worksheet is individually protected with default permissions, and the entire workbook is marked as final to indicate completion. The file is then saved and closed for submission, completing the project requirements while demonstrating proficiency in advanced Excel features.

References

  • Alexander, M. (2020). Practical Excel 2019. Packt Publishing.
  • Clark, E. (2019). Microsoft Excel Data Analysis and Business Modeling. Microsoft Press.
  • Higgins, R. (2021). Mastering Microsoft Excel 2019. John Wiley & Sons.
  • Walkenbach, J. (2018). Excel 2019 Bible. Wiley Publishing.
  • ExcelJet. (2022). How to create 3D references in Excel. https://exceljet.net
  • Microsoft Support. (2023). Data validation in Excel. https://support.microsoft.com
  • Excel Campus. (2021). How to create hyperlinks in Excel. https://www.excelcampus.com
  • Sharma, P. (2020). Automation with Excel VBA. Apress.
  • Gaskin, J. (2019). Data Analysis Using Microsoft Excel. McGraw-Hill Education.
  • MyExcelOnline. (2022). Protect and unprotect sheets and workbooks. https://www.myexcelonline.com

Related Assignments