Excel Module 2: Rivera Engineering

Linda Sessions
2 Sept 202016:54
EducationalLearning
32 Likes 10 Comments

TLDRThis tutorial guides users through completing an Excel module project, focusing on enhancing clarity and interpretability of an Excel workbook tracking estimated and actual hours, and billing amounts for projects. Key steps include changing the workbook theme, adjusting font sizes, applying fill colors, adding borders, and formatting dates and numbers. The instructor demonstrates how to use formulas to calculate estimated hours and apply conditional formatting to highlight key data. The video concludes with submitting the project for assessment, aiming for a perfect score by paying attention to details and making necessary adjustments.

Takeaways
  • 📚 Start by downloading the Excel module instructions and starter file for the end of module project.
  • 🖊️ Enable editing in the Excel document and ensure your name is included in the file.
  • 📁 Save the Excel file with a specific name (e.g., change '1' to '2') in a designated folder on your desktop.
  • 🎨 Change the worksheet theme to 'Office' for a more professional appearance.
  • 🔢 Format the workbook to make the information clearer, including changing font sizes, colors, and adding borders.
  • 📊 Center values in a specific range and apply outside borders to enhance readability.
  • 🖌️ Customize the color of the worksheet tab to 'Blue Accent 1' for better visual distinction.
  • 📅 Adjust the date format in the 'Project Tracking' worksheet to 'Short Date' and resize the column to fit the content.
  • 🔢 Format numerical values to display with one decimal place and use formulas to calculate estimated hours multiplied by the pay rate.
  • 💰 Apply currency formatting to a range of cells, adding a dollar sign and two decimal places.
  • 📈 Use conditional formatting to highlight cells with values greater than a certain percentage and data bars to represent actual billing amounts.
  • 📊 Insert formulas to calculate the average, minimum, and maximum of actual hours worked for a quick summary of project data.
  • 💯 After completing the project, save the workbook, submit it through Blackboard, and check for a perfect score or review errors for resubmission.
Q & A

  • What is the main objective of the Excel module two project?

    -The main objective of the Excel module two project is to format an Excel workbook to track estimated and actual hours and billing amounts for each project, making the information clearer and easier to interpret.

  • What are the initial steps to begin working on the Excel module two project?

    -The initial steps include downloading the instructions and the starter file, opening the Excel document, enabling editing, and saving the file with the student's name in the document title.

  • How does one change the name of the Excel file to include their own name?

    -To change the name, one should go to 'File', then 'Save As', navigate to the desired folder (e.g., an 'Excel folder for Fall 2020' on the desktop), and rename the file by changing the number to reflect their own assignment version, then save it.

  • What is the first worksheet theme change requested in the project?

    -The first worksheet theme change requested is to change the 'Employees for Projects' worksheet theme to 'Office' to alter its appearance from orange to blue.

  • What font size and fill color are specified for cell A1 in the project?

    -The font size specified for cell A1 is 18 points, and the fill color is 'Blue Accent 1, Lighter 80%'.

  • How are the values in the range B3 to B9 to be formatted in the project?

    -The values in the range B3 to B9 are to be centered using the alignment tab in Excel.

  • What type of borders are added to the range A1 through C9 in the project?

    -Outside borders are added to the range A1 through C9 to frame the project information.

  • How is the color of the 'Employees for Projects' worksheet tab changed in the project?

    -The color of the worksheet tab is changed by right-clicking on the tab, selecting 'Tab Color', and choosing 'Blue Accent 1'.

  • What changes are made to the 'Project Tracking' worksheet in terms of date format and column resizing?

    -In the 'Project Tracking' worksheet, the date format is changed to 'Short Date', and the column is resized to its best fit by double-clicking between columns B and C.

  • How is the value in cell F4 calculated in the project?

    -The value in cell F4 is calculated by inserting a formula that multiplies Aubrey Irwin's estimated hours (D4) by the pay rate (E4).

  • What number format is applied to the range F4 to F13 in the project?

    -The currency number format is applied to the range F4 to F13, using the dollar sign and adding two decimal places.

  • How are the values in the range K4 to K13 displayed in the project?

    -The values in the range K4 to K13 are displayed as percentages with no decimal places.

  • What conditional formatting is used in the project to highlight specific information?

    -Conditional formatting is used to highlight cells containing a value greater than 100 percent with a light red fill and dark red text. Additionally, data bars with a gradient fill blue option are used in H4 to H13 to visually represent actual billing amounts.

  • How are the average, minimum, and maximum hours calculated in the project?

    -The average, minimum, and maximum hours are calculated using the 'Average', 'Minimum', and 'Maximum' functions from the 'AutoSum' dropdown in Excel, with the correct range specified around cells G4 to G13.

  • What is the final step for submitting the completed Excel module two project?

    -The final step is to save the workbook, navigate back to the learning platform (e.g., Blackboard), upload the file, submit it, and then view the report to check the score.

Outlines
00:00
📊 Excel Module Two: Starting the End of Module Project

The video begins with an introduction to Excel Module Two, instructing viewers to navigate to their respective week and start the end of module project one. The presenter downloads the project instructions and the starter file, then opens the Excel document on an external monitor. The first task is to enable editing and personalize the document by saving it with the user's name. The presenter guides the viewer through changing the file name and saving it on the desktop in an 'Excel Folder for Fall 2020'. The main objective is to help Brad Kaufman, Senior Director of Projects for Rivera Engineering, create an Excel workbook to track estimated and actual hours and billing amounts for projects, with a focus on making the workbook clearer and easier to interpret.

05:01
🎨 Customizing Excel Workbook Appearance

The presenter proceeds with customizing the Excel workbook's appearance by changing the worksheet theme to 'Office' for aesthetic purposes. The font size of the title in cell A1 is decreased to 18 points, and the fill color is changed to 'Blue Accent 1, Lighter 80%'. The values in the range B3 to B9 are centered, and outside borders are added to the range A1 through C9 to enhance the workbook's visual structure. The color of the 'Employees for Projects' worksheet tab is changed to 'Blue Accent 1' to maintain a cohesive look. The presenter emphasizes the importance of being on the correct worksheet tab when working in Excel.

10:03
📅 Formatting Dates and Displaying Values with Precision

The video continues with instructions on how to format the date in cell B2 to 'Short Date' and resize the column to best fit the content. The presenter then instructs viewers to display values in the range D4 to D13 with one decimal place, using the 'Number' tab to adjust the decimal settings. In cell F4, a formula is inserted to multiply Aubrey Irwin's estimated hours (D4) by the pay rate (E4), and this formula is copied down to F13. The range F4 to F13 is then formatted to display values using the currency format with two decimal places, indicating a clear presentation of monetary values.

15:07
📊 Advanced Excel Features: Conditional Formatting and Formulas

The presenter demonstrates how to use conditional formatting to highlight cells with values greater than 100 percent in a light red fill with dark red text. Data bars with a gradient fill blue option are applied to the range H4 to H13 to visually represent actual billing amounts. In G14, an average formula is used to calculate the average of actual hours worked, while in G15 and G16, minimum and maximum functions are applied to find the least and most hours worked, respectively. The presenter emphasizes the importance of selecting the correct range for these formulas to ensure accurate calculations.

🏆 Completing and Submitting the Excel Project

The final part of the video shows the presenter ensuring that the workbook matches the final workbook as depicted in the instructions. The presenter saves the file and navigates back to the platform (presumably Blackboard) to submit the assignment. The file is uploaded from the desktop's Excel folder, and after confirming the correct file with a green checkmark, it is submitted. The presenter then views the report and proudly announces a perfect score of 100 out of 100. The video concludes with advice for viewers who scored less than perfect, encouraging them to review the feedback, make necessary changes, save the file, and resubmit for a higher grade, wishing them a great day.

Mindmap
Keywords
💡Excel
Excel is a widely used spreadsheet program developed by Microsoft for Windows, macOS, Android, and iOS. It is part of the Microsoft Office suite and is designed for numerical calculations, data organization, and data analysis. In the video's context, Excel is the primary tool for completing the module project, where the user is guided to perform various tasks such as formatting, calculations, and data tracking.
💡Module Project
A module project typically refers to a structured assignment or task that is part of a larger course or training program. In this video, the module project involves working with an Excel workbook to track estimated and actual hours and billing amounts for projects, which is a key part of the instructional content.
💡Worksheet
In Excel, a worksheet is a single page within a workbook, which can be used to organize and manipulate data. The video script mentions navigating to different worksheets such as 'Employees for Projects' and 'Project Tracking' to perform specific tasks, highlighting the importance of worksheets in organizing data.
💡Formatting
Formatting in Excel refers to the process of changing the appearance of cells, rows, columns, or the entire worksheet to make the data more readable and visually appealing. The video describes various formatting tasks, such as changing the theme, font size, fill color, and borders, to enhance the clarity of the workbook.
💡Conditional Formatting
Conditional formatting is an Excel feature that allows users to apply specific formats to cells based on whether they meet certain conditions. In the video, conditional formatting is used to highlight cells with values greater than 100 percent and to display data bars in a range, which helps in visually analyzing the data.
💡Formula
A formula in Excel is a mathematical equation or calculation that performs computations on values within the spreadsheet. The script describes creating a formula to multiply estimated hours by a pay rate, demonstrating how formulas are used to automate calculations and derive results.
💡Currency Number Format
The currency number format in Excel is a predefined format that displays numbers with a currency symbol, two decimal places, and a thousand separator. The video mentions applying this format to a range of cells to represent monetary values, which is crucial for financial data representation.
💡Percentage
In Excel, displaying values as percentages allows for easy comparison of proportions or ratios. The video script includes instructions to format certain cells as percentages with no decimal places, which is useful for showing relative values in a clear manner.
💡AutoSum
AutoSum is a feature in Excel that automatically inserts a formula to sum a range of cells. The video demonstrates using AutoSum to calculate the average, minimum, and maximum values, illustrating its utility in quickly performing aggregate functions.
💡Data Bars
Data bars are a type of conditional formatting in Excel that provides a visual representation of cell values through horizontal bars within the cells. The script describes applying gradient fill blue data bars to a range, which helps in visually comparing the actual billing amounts.
💡Submit
In the context of the video, submitting refers to the process of uploading and sending the completed Excel workbook to a platform, such as Blackboard, for grading. The script mentions submitting the assignment and checking the report, which is an essential step in the educational process.
Highlights

Introduction to Excel Module Two and starting the end of module project.

Downloading instructions and starter file for the Excel project.

Enabling editing and saving the Excel document with the student's name.

Formatting the workbook to make information clearer and easier to interpret.

Changing the worksheet theme to Office for aesthetic improvement.

Adjusting the font size to 18 point and changing the fill color in cell A1.

Centering values in the range B3 through B9 for better readability.

Adding outside borders to the range A1 through C9 for clarity.

Changing the color of the 'Employees for Projects' worksheet tab to blue accent one.

Navigating to the 'Project Tracking' worksheet for further formatting.

Changing the date format in cell B2 to short date and resizing the column.

Displaying values in the range D4 to D13 with one decimal place.

Inserting a formula in F4 to multiply Aubrey Irwin's estimated hours by the pay rate.

Copying the formula down to F13 to apply it across the range.

Applying currency number format to the range F4 to F13.

Displaying values in the range K4 to K13 as percentages.

Using conditional formatting to highlight cells with values greater than 100 percent.

Adding conditional formatting with data bars to the range H4 to H13.

Inserting a formula in G14 to calculate the average of actual hours worked.

Calculating the minimum and maximum actual hours worked in cells G15 and G16.

Submitting the completed Excel workbook and reviewing the score.

Advice on how to improve the score if the initial grade is less than 100.

Transcripts

Browse More Related Video

Tutorial on Rivera Engineering excel project on Mac in excel

Excel Module 2 SAM End of Module Project 1 | NP_EX19_EOM2-1 | First Mittagong Community Bank

Tutorial of First Mittagong community bank in excel on Mac

Excel Module 1 Project 2: Retail Pro

Tutorial of Ramos Family Home Purchase (part 2) on in excel on Mac

TECH-005 - Create a quick and simple Time Line (Gantt Chart) in Excel

Rate This

5.0 / 5 (0 votes)

Thanks for rating:

Related Tags
Excel TutorialWorkbook FormattingProject TrackingConditional FormattingData AnalysisEducational ContentSpreadsheet SkillsFinancial AnalysisEfficiency TipsMicrosoft Office