Tutorial of First Mittagong community bank in excel on Mac

Learning Excel
14 Jan 202410:50
EducationalLearning
32 Likes 10 Comments

TLDRThis instructional video script guides viewers through enhancing the usability and appearance of a workbook used by a loan officer at First Mong Community Bank. It covers changing the workbook theme to 'Office', formatting cells with various styles like 'FM Heading' and 'FM Subheading', and adjusting text attributes such as italics and alignment. The tutorial also includes steps for setting number formats for dates, percentages, and currencies, applying background colors, and adding borders. The process is demonstrated for both mortgage and money market worksheets, ensuring a consistent and professional presentation of financial data.

Takeaways
  • 📘 The tutorial is about enhancing the usability of a workbook used by a loan officer named Titus from First Mong Community Bank.
  • 🖌️ The first step involves changing the workbook's theme to 'Office' to improve its appearance.
  • 📊 In step two, the instructions guide on how to format and center the headings in the range A1 to D1.
  • 🎨 Applying custom cell styles such as 'FM heading' and 'FM subheading' to specific merged cells is part of the process.
  • 🗓️ Formatting the effective data and date information in the range A4 to B4 with a 'Note' cell style and specific text formatting is covered.
  • 📈 The mortgage rate table's headings from A6 to D6 are formatted with center alignment and bold text, with a change in font color to 'Blue Gray'.
  • 🔢 Steps include applying percentage number formats with two decimal places to certain ranges and copying these formats to other cells.
  • 📊 The number format for ranges like C7 to C18 is adjusted to show three decimal places.
  • 🖼️ Background colors are changed for specific ranges, such as A7 to D9, using themes like 'Green Accent 6, Lighter 80%'.
  • 🔗 Formatting is copied and pasted between different ranges to maintain consistency across the workbook.
  • 📑 Borders are added around all cells in a specific range to enhance the visual structure of the table.
  • 🏦 The process is also applied to the 'Money Market' worksheet, including merging and centering contents, and applying custom cell styles.
Q & A

  • What is the role of Allison in the script?

    -Allison is a loan officer with First Mong Community Bank who is working on formatting a workbook to track interest rates.

  • What is the first step Allison takes to increase the usability of the workbook?

    -The first step is to change the theme of the workbook to 'Office'.

  • How does Allison switch to the mortgage sheet in the workbook?

    -Allison switches to the mortgage sheet by clicking on the 'mortgage' tab at the bottom of the workbook.

  • What does Allison do to format the heading in the mortgage sheet?

    -Allison merges and centers the range A1 to D1 and applies the 'FM Heading' cell style to the merged cell.

  • What is the purpose of applying the 'Note' cell style to the range A4 to B4?

    -The 'Note' cell style is applied to format the effective data and date information in the workbook.

  • How does Allison change the date format in cell B4?

    -Allison changes the date format to 'dd-mmm-yy' by selecting 'More Number Formats' and choosing the '14-Mar-12' format.

  • What formatting does Allison apply to the mortgage rate table in the range A6 to D6?

    -Allison centers and bolds the headings, changes the font to 'Blue Gray Text 2', and merges and centers the range A7 to A9.

  • How does Allison format the percentage in the range B7 to B18?

    -She applies a percentage number format with two decimal places and copies this format to the range D7 to D18.

  • What changes does Allison make to the number format in the range C7 to C18?

    -Allison applies a number format and increases the decimal places to show three decimal places.

  • What background color does Allison change for the range A7 to D9?

    -Allison changes the background color to 'Green Accent 6, Lighter 80%'.

  • How does Allison add a border to all cells in the range A6 to D18?

    -She uses the 'All Borders' option from the border button with four boxes and clicks on it.

  • What custom cell style does Allison apply to the money market worksheet for the range A1 to D1?

    -Allison applies the custom cell style 'FM Heading' to the range A1 to D1 in the money market worksheet.

  • How does Allison format the money market rate table in the ranges B7 to B11 and C7 to C10?

    -She applies a currency number format with two decimal places to the ranges B7 to B11 and C7 to C10.

  • What color does Allison change the tab color of the mortgage sheet to?

    -Allison changes the tab color of the mortgage sheet to 'Purple 10'.

  • What color does Allison change the tab color of the money market worksheet to?

    -Allison changes the tab color of the money market worksheet to 'Dark Red'.

Outlines
00:00
📊 Excel Workbook Formatting: Step-by-Step Guide

This paragraph provides a step-by-step tutorial on formatting an Excel workbook to track interest rates for a bank officer named Titus. The process begins with changing the workbook's theme to 'Office' and continues with various formatting tasks such as merging and centering cells, applying custom cell styles like 'FM heading' and 'FM subheading', and adjusting the text and date formats. The instructions also include applying specific cell styles to data ranges, changing the font color and size, and copying and pasting formatting to maintain consistency across different sections of the workbook.

05:02
🔢 Advanced Excel Formatting Techniques

The second paragraph delves into more advanced Excel formatting techniques. It covers how to apply percentage and currency number formats with specific decimal places to various ranges within the workbook. The tutorial also explains how to change the background color of certain cells to 'green accent 6 lighter 80%' and how to copy and paste formatting to other ranges to streamline the process. Additionally, it describes how to add borders around cells and switch between different worksheets while maintaining the formatting consistency, such as applying the 'FM heading' style to the 'Money Market' worksheet.

10:02
🎨 Customizing Excel Worksheet Colors and Tab Appearance

The final paragraph focuses on customizing the visual aspects of an Excel workbook. It instructs how to change the tab color of the 'Mortgage' worksheet to 'purple' and the 'Money Market' worksheet to 'dark red' using the standard color palette. The paragraph also provides guidance for users on a MacBook, detailing the process of accessing the tab color settings through the bottom right corner of the keyboard. The tutorial concludes with a thank you note to the viewers, wrapping up the Excel formatting session.

Mindmap
Keywords
💡Loan Officer
A loan officer is a professional who works in a financial institution and is responsible for evaluating, approving, or rejecting loan applications. In the video's context, Allison is a loan officer at First Mong Community Bank, which implies that she plays a crucial role in the bank's lending operations. The script mentions her name in the beginning, indicating that the instructions provided are likely for her to follow as part of her duties.
💡Workbook
A workbook in the context of this video refers to a collection of spreadsheets in a Microsoft Excel application or similar software. It is used to track and organize data systematically. The script discusses formatting and modifying a workbook to track interest rates, which is essential for financial analysis and decision-making in a banking environment.
💡Formatting
Formatting in the context of spreadsheets involves changing the appearance of cells, rows, columns, or the entire sheet to make the data more readable and visually appealing. The script provides several steps related to formatting, such as changing themes, merging cells, and applying different styles, to enhance the usability and presentation of the workbook.
💡Theme
In the context of the video, a theme refers to a pre-designed combination of colors, fonts, and effects that can be applied to a workbook to give it a consistent and professional look. The script instructs the user to change the theme of the workbook to 'Office', which suggests a standard, business-like appearance.
💡Merge and Center
Merging and centering is a formatting technique used in spreadsheets to combine two or more cells horizontally or vertically and then center-align the text or content within the merged cell. The script mentions this action multiple times, indicating it as a common practice to create headings and subheadings that are visually balanced and easy to read.
💡Cell Style
A cell style in spreadsheet software is a predefined set of formatting options that can be applied to cells to maintain consistency throughout the workbook. The script refers to 'FM heading' and 'FM subheading' as custom cell styles, which are applied to different ranges to give them a uniform appearance related to their function within the workbook.
💡Note Cell Style
The 'Note' cell style, as mentioned in the script, is likely a formatting option used to highlight or differentiate certain types of information, such as notes or comments. It is applied to the range A4 to B4, indicating that this area of the workbook contains important information that requires a distinct visual treatment.
💡Number Format
Number formatting in spreadsheets allows users to control how numbers are displayed, including the number of decimal places, the use of currency symbols, or percentage representation. The script provides instructions for applying different number formats, such as percentage with two decimal places and currency format, to various cells to ensure that financial data is presented accurately.
💡Background Color
Setting a background color is a way to differentiate or highlight specific cells or ranges within a spreadsheet. The script instructs the user to change the background color of a range to 'green accent 6, lighter 80%', which serves as a visual cue to distinguish this section of the workbook from others.
💡Border
Borders in spreadsheets are lines that can be added around cells to create a frame or separate sections of data. The script mentions adding a border around all cells in a specified range using the 'All Borders' option, which helps in organizing the data and making the workbook more visually structured.
💡Worksheet
A worksheet in a workbook is an individual spreadsheet within the larger collection. The script refers to switching between different worksheets, such as 'Mortgage' and 'Money Market', indicating that the workbook contains multiple sections, each dedicated to a specific area of financial data tracking.
Highlights

Introduction of a project involving Titus, a loan officer, and Allison from First Mong Community Bank.

Titus is enhancing the workbook's usability by adding formatting to track interest rates.

Changing the workbook's theme to 'Office' to improve its appearance.

Instructions to switch to the 'Mortgage' sheet and format the heading for cells A1 to D1.

Applying the 'FM Heading' cell style to the merged cell for a consistent look.

Merging and centering the range A2 to D2 for a cleaner layout.

Applying the 'FM Subheading' style to the merged cell in the range A2 to D2.

Formatting the effective data and date information for the range A4 to B4 with the 'Note' cell style.

Adding italic text and right alignment to cell A4 for emphasis.

Changing the date format in cell B4 to 'date month year' for clarity.

Formatting the mortgage rate table with headings A6 to D6, centering, and bolding.

Copying and pasting formatting from the merged cell in range A7 to A9 to A10 to A18 for consistency.

Applying a percentage number format with two decimal places to range B7 to B18.

Copying the percentage format to range D7 to D18 for uniformity.

Applying a number format with three decimal places to range C7 to C18 for precision.

Changing the background color of range A7 to D9 to 'green accent 6 lighter 80%' for visual distinction.

Copying the formatting from A7 to D9 and pasting it to range A13 to D15 for a cohesive design.

Adding borders around all cells in the range A6 to D18 using the 'All Borders' option for a finished look.

Switching to the 'Money Market' worksheet and applying the 'FM Heading' style to A1 to D1.

Merging and centering contents and applying 'FM Subheading' style to range A2 to D2 in the 'Money Market' sheet.

Copying and pasting the format of range A4 to B4 from the 'Mortgage' sheet to the 'Money Market' sheet.

Applying a currency number format with two decimal places to ranges B7 to B11 and C7 to C10 in the 'Money Market' sheet.

Applying a percentage number format with two decimal places to range D7 to D11 for consistency.

Changing the tab color of the 'Mortgage' sheet to 'purple' and the 'Money Market' sheet to 'dark red' for easy identification.

Completion of the workbook formatting project with a demonstration of the final result.

Transcripts

Browse More Related Video

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

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

Excel Module 2: Rivera Engineering

Tutorial on Rivera Engineering excel project on Mac in excel

Make Your Google Sheets Look PRO in Under 10 Minutes!

Excel Module 1 Project 2: Retail Pro

Rate This

5.0 / 5 (0 votes)

Thanks for rating:

Related Tags
Excel TutorialBankingFormattingWorkbookInterest RateUsabilityLoan OfficerData PresentationFinancial AnalysisSpreadsheet Design