Tutorial on Rivera Engineering excel project on Mac in excel

Learning Excel
20 Jan 202408:58
EducationalLearning
32 Likes 10 Comments

TLDRThis tutorial video guides viewers through an Excel project for Vivia Engineering, aiming to enhance clarity and interpretability of project tracking. The presenter, BR Cerman, instructs on formatting the 'Employees for Project' worksheet with an Office theme, adjusting font size and color, centering text, and adding borders. Further steps include changing date formats, applying number formats for clarity, and using formulas to calculate estimates and actuals. The tutorial also covers conditional formatting for highlighting values and creating data bars, concluding with the use of functions to find average, minimum, and maximum hours worked.

Takeaways
  • 📊 The video is a tutorial on creating and formatting an Excel workbook for a project management scenario.
  • 👤 BR German is the senior director of projects for Vivia Engineering, which provides engineering products for public utilities and energy companies.
  • 🛠️ The Excel workbook is being used to track estimated, actual hours, and billing amounts for various projects.
  • 🎨 The tutorial instructs to change the worksheet theme to 'Office' for clarity and aesthetic appeal.
  • 🔠 In cell A1, the font size is decreased to 18, and the font color is changed to 'Blue Accent 1, Lighter 80%'.
  • 📐 The values in the range A3 to B9 are centered to improve readability.
  • 🔲 Outside borders are added to the range A1 to C9 to enhance the visual structure of the worksheet.
  • 🎨 The color of the 'Employees for Project' worksheet tab is changed to 'Blue Accent 1'.
  • 📅 In the 'Project Tracking' worksheet, the date format in cell B2 is changed to 'Short Date', and column B is adjusted to best fit.
  • 🔢 The values in the range D4 to D13 are formatted to display with one decimal place for consistency.
  • 🧮 A formula is inserted in cell F4 to manually calculate the product of estimated hours and pay rate without using a function.
  • 💲 The range F4 to F13 is formatted to display values with a currency format, using the dollar sign and two decimal places.
  • 📊 Conditional formatting is applied to the range K4 to K13 to display values as percentages and highlight cells with values greater than 10%.
  • 📊 Data bar rules with a gradient blue bar option are applied to the range F4 to F13 for visual representation of data.
  • 📊 In cell G14, an average function formula is used to calculate the average amount of actual hours worked in the range G4 to G13.
  • 📊 In cell G15, a minimum function formula calculates the minimum amount of actual hours worked within the specified range.
  • 📊 In cell G16, a maximum function formula determines the maximum amount of actual hours worked in the range G4 to G13.
Q & A

  • Who is BR Cerman and what is his role at Vivian Engineering?

    -BR Cerman is the Senior Director of Projects for Vivian Engineering, a company based in Miami, Florida. He oversees the engineering projects that the company performs for public utilities and energy companies.

  • What is the purpose of the Excel workbook that BR Cerman has started to create?

    -The Excel workbook is intended to track estimated hours, actual hours, and billing amounts for each project undertaken by Vivian Engineering.

  • What is the first task mentioned in the script for formatting the workbook?

    -The first task is to change the worksheet theme to 'Office' for clarity and easier interpretation of the information.

  • How should the font size and color be adjusted in cell A1 of the 'Employees for Project' worksheet?

    -In cell A1, the font size should be decreased to 18, and the font color should be changed to 'Blue Accent 1, Lighter 80%'.

  • What is the instruction for centering the values in the range A3 to B9?

    -Select the range A3 to B9 and apply the 'Center' alignment to the text within these cells.

  • What is the step for adding outside borders to the range A1 to C9?

    -Select the range A1 to C9 and apply the 'Outside Borders' formatting option to add borders around the selected cells.

  • How can the color of the 'Employees for Project' worksheet tab be changed to 'Blue Accent 1'?

    -Right-click on the 'Employees for Project' tab, select 'Tab Color', and then choose 'Blue Accent 1' to change the color.

  • What is the process for changing the date format in cell B2 of the 'Project Tracking' worksheet?

    -In cell B2, select the date, click the arrow to open the format options, and choose 'Short Date' from the dropdown menu.

  • How should the values in the range D4 to D13 be formatted to display with one decimal place?

    -Select the range D4 to D13, go to the 'Number' format options, click 'More Number Formats', choose 'Number', and set the decimal places to '1'.

  • What formula is used in cell F4 to calculate the estimated hours multiplied by the pay rate?

    -In cell F4, the formula used is D4 multiplied by E4 (D4*E4), which calculates the product of the estimated hours and the pay rate.

  • How can the currency number format be applied to the range F4 to F13 with two decimal places?

    -Select the range F4 to F13, go to the 'Number' format options, and choose the 'Currency' format with two decimal places.

  • What is the purpose of using conditional formatting in the ranges K4 to K13 and H4 to H13?

    -Conditional formatting is used in K4 to K13 to display values as percentages with specific formatting for values greater than 10%. In H4 to H13, it is used to create data bar rules with a gradient blue bar option to visually represent the data.

  • What functions are used in steps 12, 13, and 14 to calculate average, minimum, and maximum actual hours worked?

    -The 'AVERAGE' function is used in step 12 to calculate the average of actual hours worked in the range G4 to G13. The 'MIN' function is used in step 13 to find the minimum amount of actual hours worked in the same range. The 'MAX' function is used in step 14 to determine the maximum amount of actual hours worked.

Outlines
00:00
📊 Excel Workbook Formatting and Data Representation

This paragraph details the process of enhancing the clarity and interpretability of an Excel workbook for a project management scenario. It involves setting up the 'Employees for Project' worksheet with a specific theme, adjusting font size and color, centering values, adding borders, and changing the tab color. It also includes formatting the 'Project Tracking' worksheet by adjusting date formats and column widths, and setting number formats for estimated hours and pay rates with a formula to calculate estimated earnings. The instructions are step-by-step, guiding the user through each formatting and data entry task.

05:01
🔢 Advanced Excel Functions and Conditional Formatting

The second paragraph focuses on applying advanced Excel functions and conditional formatting to enhance data visualization and analysis. It starts with filling a range with a formula for calculating estimated earnings, applying currency formatting, and using conditional formatting to highlight values based on certain criteria. The paragraph also covers using data bars for visual representation of values and inserting formulas to calculate average, minimum, and maximum values within a specified range. Each step is explained in detail, ensuring the user can follow along and apply these functions to their own Excel projects.

Mindmap
Keywords
💡River Engineering
River Engineering refers to the practice of designing and constructing projects related to rivers, such as bridges, dams, and riverbanks, to manage water flow and prevent flooding. In the context of the video, it is the company's name and represents the organization for which the Excel project is being formatted. The video script does not directly mention any specific river engineering projects but focuses on Excel workbook management for tracking various projects.
💡Excel Workbook
An Excel workbook is a file in Microsoft Excel that can contain multiple worksheets for organizing and analyzing data. The video's theme revolves around creating and formatting an Excel workbook to track estimated hours, actual hours, and billing amounts for various projects. The script provides step-by-step instructions on how to enhance the workbook's clarity and functionality.
💡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 script provides detailed instructions on formatting aspects such as changing the theme, font size, and color, as well as adding borders and adjusting the layout to improve the workbook's presentation.
💡Worksheet Theme
A worksheet theme in Excel is a pre-designed combination of colors, fonts, and effects that can be applied to a worksheet to give it a consistent and professional look. In the script, the theme is set to 'Office' to standardize the appearance of the 'Employees for Project' worksheet, which is a key step in enhancing the workbook's overall aesthetics.
💡Font Size
Font size is a setting in Excel that determines the size of the text in a cell. The script instructs viewers to decrease the font size in cell A1 to 18, which is part of the formatting process to make the title of the worksheet more prominent and improve the layout's balance.
💡Conditional Formatting
Conditional formatting in Excel is a feature that allows cells to automatically change their formatting based on rules that the user defines, such as data being above or below a certain value. The script describes how to use conditional formatting to highlight cells with values greater than 10% in light red with dark red text, which helps in visualizing data thresholds within the workbook.
💡Data Bars
Data bars are a type of conditional formatting in Excel that visually represent the values in a range of cells with horizontal bars. The script mentions creating data bar rules with a gradient blue bar option for the range F4 to F13, which provides a visual comparison of the values in the cells, making it easier to identify trends or patterns.
💡Formulas
Formulas in Excel are mathematical expressions used to perform calculations and manipulate data. The script includes several instances where formulas are used without functions (e.g., multiplying estimated hours and pay rate) and with functions (e.g., AVERAGE, MIN, MAX) to calculate various metrics such as total estimated hours, average, minimum, and maximum actual hours worked.
💡Number Formats
Number formats in Excel define how numbers are displayed in cells, including the use of currency symbols, decimal places, and percentage signs. The script provides instructions on changing the number format to display values with one decimal place, as currency with two decimal places, and as percentages with no decimal places, which is crucial for accurate data representation and interpretation.
💡Cell References
Cell references in Excel are addresses used to identify the location of a cell or range of cells. The script frequently uses cell references (e.g., A1, B2, D4, E4, F4, etc.) to indicate where specific actions should be taken or where formulas should be entered. Understanding cell references is fundamental to following the instructions and manipulating data within the workbook.
💡Project Tracking
Project tracking involves monitoring the progress and performance of projects against their planned objectives. In the script, the 'Project Tracking' worksheet is where the actual tracking of project hours and billing amounts takes place. The video focuses on setting up this worksheet with appropriate formatting and formulas to facilitate the tracking process.
Highlights

Introduction to the River engineering Excel project with BR Cerman from Vivia Engineering.

Objective to create an Excel workbook to track project hours and billing for public utilities and energy companies.

Formatting the workbook for clarity and ease of interpretation.

Changing the worksheet theme to 'Office' for a professional look.

Adjusting font size in cell A1 to 18 and changing the fill color to Blue Accent 1 Lighter 80%.

Centering values in the range A3 to B9 for better readability.

Adding outside borders to the range A1 to C9 for a clean layout.

Changing the color of the 'Employees for Project' worksheet to Blue Accent 1.

Setting the date format in cell B2 to short date for clarity.

Adjusting column B to best fit for optimal viewing.

Displaying values in the ranges D4 to D13 with one decimal place for precision.

Inserting a formula in cell F4 to calculate estimated hours multiplied by pay rate.

Filling the formula across the range F4 to F13 for all relevant cells.

Applying currency format to the range F4 to F13 with dollar signs and two decimal places.

Displaying values in ranges K4 to K13 as percentages with no decimal places.

Using conditional formatting to highlight cells with values greater than 10% with red fill and text.

Creating data bar rules in the range H4 to H13 with gradient blue bars.

Inserting an average function formula in cell G14 to calculate the average actual hours worked.

Using the MIN function in cell G15 to find the minimum amount of actual hours worked.

Applying the MAX function in cell G16 to determine the maximum actual hours worked.

Conclusion and call to action for likes and subscriptions.

Transcripts

Browse More Related Video

Excel Module 2: Rivera Engineering

Tutorial of New Era Medical Excel Project in excel on Mac

Tutorial of First Mittagong community bank in excel on Mac

Excel Module 1 Project 2: Retail Pro

How to Pass Excel Assessment Test For Job Applications - Step by Step Tutorial with XLSX work files

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

Rate This

5.0 / 5 (0 votes)

Thanks for rating:

Related Tags
Excel FormattingProject TrackingEngineering GuideData AnalysisConditional FormattingSpreadsheet TipsMiami EngineeringVivia EngineeringWorkbook TutorialExcel Functions