Excel Module 1 Project 2: Retail Pro

Linda Sessions
28 Sept 202108:05
EducationalLearning
32 Likes 10 Comments

TLDRIn this tutorial, Linda guides viewers through completing a project in Excel for 'Sam's Excel Chapter 1 - The Retail Pro.' She begins by ensuring the documentation is personalized and saved correctly. Linda then demonstrates merging cells, entering dates, applying cell styles, and using formulas to calculate mileage expenses. She also covers formatting numbers, summing expenses, changing fonts, and adjusting chart styles. The video concludes with a reminder to submit the completed work for grading, with a note that the Franklin Gothic Book Body font may not be available on all systems.

Takeaways
  • 📝 The video is a tutorial on working with 'Sam's Excel Chapter 1 Project 2: The Retail Pro'.
  • 🔍 The instructor starts by ensuring the documentation is correct and personalized with the user's name.
  • 💾 The first step involves saving the starter document in a specific folder for easy access later.
  • 🔑 The tutorial instructs to merge cells in the 'Travelers Expense Worksheet' in the range B2 through I2.
  • 📅 The date '5/3/2021' is entered in cell B13 as part of the project requirements.
  • 🎨 The 'Accent 3' cell style is applied to the range B10 through I10 to enhance the appearance of the worksheet.
  • 📝 The complete entry 'Retail Conference in Houston' is added to cell C16.
  • 🚗 In cell D11, 65 is entered as the number of miles driven.
  • 🔢 A formula is created in E11 to calculate mileage expense by multiplying the miles driven (D11) by the rate 0.55, with an absolute reference to cell I3.
  • 📊 The accounting number format is applied to the range E11 to I16 to align with financial reporting standards.
  • ✅ The SUM function is used in E17 to calculate the total mileage expenses from the range E11 to E16.
  • 🖌 The font 'Franklin Gothic Book' is attempted to be changed in B21, but the instructor notes the absence of 'Franklin Gothic Book Body' on their system.
  • 📈 The style of the pie chart in the range V23 to E36 is changed to 'Style Eight' for a more polished presentation.
  • 👍 The video concludes with an invitation to subscribe to the instructor's channel for more content.
Q & A
  • What is the project Linda is working on?

    -Linda is working on 'Sam's Excel Chapter 1 Project 2: The Retail Pro'.

  • What does Linda do to ensure the documentation is correct for herself?

    -Linda checks that her name is listed in the assignment and saves the document in a specific folder for future reference.

  • Where does Linda save her file?

    -Linda saves her file in her 'Documents' folder, specifically in a subfolder named 'Linda's Tape'.

  • What is the first task Linda performs in Excel?

    -The first task Linda performs is merging cells in the range B2 through I2 on the Travelers Expense Worksheet.

  • What date does Linda enter in cell B13?

    -Linda enters the date '5/3/2021' in cell B13.

  • What cell style is applied to the range B10 through I10?

    -Linda applies the 'Accent 3' cell style to the range B10 through I10.

  • What complete entry does Linda add to cell C16?

    -Linda adds 'Retail Conference in Houston' as the complete entry for cell C16.

  • How does Linda calculate the mileage expense in cell E11?

    -Linda enters a formula in E11 that multiplies the number of miles driven in D11 by the current mileage rate of 0.55.

  • Why does Linda use an absolute value for the current mileage rate in the formula?

    -Linda uses an absolute value to ensure that when the formula is copied down, it always refers to the number in I3.

  • What format does Linda apply to the range E11 to I16?

    -Linda applies the 'Accounting Number Format' to the range E11 to I16.

  • What function does Linda use to sum the mileage expenses in cell E17?

    -Linda uses the 'SUM' function to sum the mileage expenses in the range E11 to E16.

  • What font does Linda attempt to change in cell B21?

    -Linda attempts to change the font to 'Franklin Gothic Book Body' in cell B21.

  • What does Linda do with the value in cell C5?

    -Linda copies the value in cell C5, which is the employee ID number, and pastes it into cell D21.

  • How does Linda change the style of the pie chart?

    -Linda changes the style of the pie chart in the range V23 to E36 to 'Style Eight'.

  • What does Linda acknowledge about her work in B21?

    -Linda acknowledges that for herself, the work in B21 will be incorrect because she does not have the 'Franklin Gothic Book Body' font on her machine.

Outlines
00:00
📝 Excel Project Setup and Basic Editing

The video script begins with the presenter, Linda, introducing the Excel project for Sam's Excel Chapter 1, Project 2, titled 'The Retail Pro'. She starts by verifying the documentation, ensuring her name is listed in the assignment, and saving the file in a specific folder on her computer. The initial task involves merging cells in the 'Travelers Expense Worksheet' and entering a specific date. Linda then proceeds to apply the 'Accent 3' cell style to a range and enters 'Retail Conference in Houston' into a designated cell. She also demonstrates how to input a formula for mileage expense without using a function, ensuring the formula references an absolute value for the mileage rate. The paragraph concludes with instructions on copying the formula down and applying an accounting number format to a range of cells.

05:02
🔢 Advanced Excel Techniques and Final Touches

In the second paragraph, Linda continues the Excel tutorial by applying an accounting number format to a range of cells and using the SUM function to calculate total mileage expenses. She then copies the calculated value to another cell. The presenter also discusses changing the font style in a cell to 'Franklin Gothic Book', but notes that she does not have the 'Body' version of the font on her machine, which will result in an error. She proceeds to copy an employee ID number from one cell to another. Lastly, Linda changes the style of a pie chart to 'Style Eight'. She wraps up the tutorial by stating that viewers are now ready to submit their work for grading and acknowledges that there will be a font error due to the unavailability of the specific font on her system. She invites viewers to subscribe to her channel and ends the video on a positive note.

Mindmap
Keywords
💡Excel
Excel is a widely used spreadsheet program developed by Microsoft for Windows, macOS, Android, and iOS. It is known for its ability to organize, analyze, and manipulate data using formulas, pivot tables, and macros. In the video's context, Excel is the primary tool used to complete the 'Retail Pro' project, with various features like merging cells, applying styles, and using formulas being demonstrated.
💡Documentation
Documentation in this context refers to the written instructions or guidelines provided for completing a task or project. It is crucial for ensuring that the work is done correctly and according to the specified requirements. In the script, the speaker emphasizes the importance of verifying the documentation for correctness, including checking personal details and saving the file properly.
💡Save As
The 'Save As' function in Excel and other programs is used to save a file with a different name, location, or format than the original. It allows users to create a new version of a document without altering the original file. In the video, the speaker uses 'Save As' to save a copy of the starter document in a specific folder for easy access and reference.
💡Merging Cells
Merging cells in Excel is the process of combining two or more adjacent cells into a single cell. This is often done to create a unified header or to present data in a more visually appealing way. The script describes merging cells in the range B2 through I2 as part of the project setup.
💡Accent 3 Cell Style
The 'Accent 3 Cell Style' is a pre-defined formatting style in Excel that can be applied to cells to give them a distinct appearance. It typically includes features like background color, font color, and borders to highlight important information. In the video, this style is applied to the range B10 through I10 to enhance the visual presentation of the data.
💡Formula
A formula in Excel is a mathematical equation or statement that performs calculations. It can include numbers, operators, and functions to manipulate data and produce results. The script mentions entering a formula in cell E11 to calculate mileage expense by multiplying the miles driven by the mileage rate, which is an example of using formulas for data analysis.
💡Absolute Value
In Excel, an absolute value refers to a cell reference that remains constant even when the formula is copied to other cells. It is indicated by dollar signs before the column and row numbers (e.g., $I$3). The script explains how to use absolute values to ensure that the formula in E11 correctly references the mileage rate in cell I3 when copied down.
💡Accounting Number Format
The 'Accounting Number Format' in Excel is a specialized number format designed for financial data. It aligns numbers to the right, includes commas as thousand separators, and displays currency symbols. In the video, this format is applied to the range E11 to I16 to present the mileage expenses in a way that is typical for financial reporting.
💡Sum Function
The SUM function in Excel is a built-in function that adds up a range of numbers. It is commonly used to calculate totals, such as the total expenses or revenues. In the script, the SUM function is used to sum the mileage expenses in the range E11 to E16, providing a quick and automated way to get the total.
💡Pie Chart
A pie chart is a type of graph that is used to display data in a circular graph divided into sectors, each representing a proportion of the whole. It is a popular way to show relative quantities or percentages. In the video, the speaker changes the style of a pie chart in the range V23 to E36, indicating a step in enhancing the visual representation of data.
Highlights

Introduction to working on Sam's Excel Chapter 1 Project 2: The Retail Pro

Ensuring documentation is correct with name listed in the assignment

Saving the starter document in a specific folder for organization

Merging cells in range B2 through I2 on the Traveler's Expense Worksheet

Entering a specific date (5/3/2021) in cell B13

Applying the Accent 3 cell style to range B10 through I10

Completing entry for cell C16 with 'Retail Conference in Houston'

Entering 65 miles driven in cell D11

Creating a formula in E11 to calculate mileage expense without using a function

Using absolute value for I3 to ensure formula references remain consistent

Copying the formula from E11 to E16

Applying the accounting number format to range E11 to I16

Using the SUM function to calculate total mileage expenses in E17

Copying the value from E17 to I17

Changing the font in B21 to Franklin Gothic Book

Copying the employee ID number from C5 to D21

Changing the style of the pie chart in range V23 to E36 to Style Eight

Final note on potential font issue with B21 and readiness for submission

Encouragement to subscribe to the channel for similar content

Transcripts
Rate This

5.0 / 5 (0 votes)

Thanks for rating: