Tutorial on Narbonne Suite on Mac in excel

Learning Excel
18 Feb 202420:54
32 Likes 10 Comments

TLDRThis instructional video script guides users through various Excel tasks for the Narbone Suites Hotel chain. The tutorial covers breaking links in worksheets, removing hyperlinks, creating new hyperlinks for emails and locations, applying consistent formatting across worksheets, and manipulating data with formulas. It also demonstrates how to group and ungroup worksheets, consolidate sales data, create and apply defined names for ranges, and compare sales totals between years. The detailed steps ensure that viewers can follow along and enhance their Excel skills for managing sales data efficiently.

  • πŸ“Š The video is a tutorial on how to work with Excel to manage sales data for the Narbone Suites Hotel chain.
  • πŸ”— It covers breaking external links in worksheets to replace formulas with static values for the manager information.
  • βœ‚οΈ The process of removing hyperlinks and creating new ones within cells is demonstrated, including setting up email hyperlinks and screen tip text.
  • πŸ”— The tutorial explains how to create hyperlinks to files and locations, and how to edit existing hyperlinks for different purposes.
  • 🎨 It shows how to apply consistent formatting to multiple worksheets, including font size adjustments, cell styles, and column widths.
  • πŸ“ˆ The script includes instructions for creating formulas to total sales data and copying these formulas across ranges without formatting.
  • πŸ“‹ The process of creating a copy of a formatted worksheet for a different region and updating it accordingly is detailed.
  • πŸ“ The tutorial guides through consolidating data from different regions into a single worksheet and creating references to external workbooks.
  • πŸ”’ It explains how to use defined names for ranges and apply them to formulas to streamline the data consolidation process.
  • ➑️ The final part of the script involves comparing sales totals between 2021 and 2020 by linking cells from an external workbook and creating a total formula.
  • πŸ“š The video concludes with a reminder to subscribe, indicating it is likely part of a series or educational channel.
Q & A
  • What is the purpose of breaking the external link in the Excel worksheet?

    -The purpose of breaking the external link is to replace the formulas in the specified ranges with static values, which allows the user to have control over the data without relying on the source file.

  • Who is Eda Kinsman in the context of this script?

    -Eda Kinsman is the sales manager for the Narbone Suites Hotel chain, which operates in Washington, Oregon, Idaho, and Montana.

  • What is the first step to break the external link in the Excel worksheet as per the script?

    -The first step is to go to the range B4 to B7, then go to the 'Data' tab, click on the 'Edit Links' button which has a paper and link icon, and then click 'Edit Links', followed by 'Break Link'.

  • How can you remove a hyperlink in an Excel cell while keeping the text?

    -You can remove a hyperlink by right-clicking the cell, selecting 'Remove Hyperlink', which leaves the text intact in the cell.

  • What is the process to create a hyperlink to an email address in Excel?

    -To create a hyperlink to an email address, right-click the cell, select 'Hyperlink', choose 'Email Address', type in the email, set the display text, and add a screen tip if desired, then click 'OK'.

  • What is the significance of using 'Narone Suites locations' as the text to display for a hyperlink?

    -Using 'Narone Suites locations' as the display text for a hyperlink makes it clear to users that clicking on the link will direct them to the location listing for Narone Suites.

  • How can you apply consistent formatting to a group of worksheets in Excel?

    -You can group the worksheets you want to format consistently, then make the desired formatting changes to one worksheet, and the changes will be applied to all grouped worksheets.

  • What is the purpose of using 3D references in a formula when consolidating data?

    -3D references in a formula allow you to create a formula that automatically adjusts to the sheet it is being copied to, ensuring that the data from the correct sheet is referenced.

  • How can you create a defined name for a range in Excel?

    -You can create a defined name for a range by going to the 'Formulas' tab, selecting 'Define Name', entering the range address, and then clicking 'OK'.

  • What is the final step in the script for comparing 2021 sales totals to 2020 sales totals?

    -The final step is to enter a formula in cell G10 that totals the values in the range named 'totals 2020' using the SUM function and the defined range name.

πŸ“Š Data Consolidation and Link Management

This paragraph outlines the process of managing sales data for the Narbone Suites Hotel chain. Eda Kinsman, the sales manager, uses multiple worksheets to summarize sales by state. The task involves breaking external links in worksheets so that the formulas in the specified range are replaced with static values. It also covers how to navigate between different worksheets and how to edit links to replace them with static data. The focus is on ensuring that the manager information worksheets are correctly updated and linked.

πŸ”— Hyperlink Creation and Text Editing

The second paragraph details the steps for creating and managing hyperlinks within a spreadsheet. It starts with removing a hyperlink from a cell while retaining the text. Then, it proceeds to instruct on creating a new hyperlink to an email address, including setting up a screen tip for clarity. Following that, another hyperlink is created, this time linking to a location listing, with specific instructions on setting the display text and screen tip. The paragraph also covers editing an existing hyperlink to the Narbone Suites website, emphasizing the importance of accurate display text and screen tips.

🎨 Formatting Consistency Across Worksheets

This section describes the process of applying consistent formatting to a group of worksheets. It starts with grouping the Idaho, Montana, and Oregon worksheets and then making several formatting updates. These include changing the font size, applying cell styles, bolding values, applying a specific number format, and resizing columns. The instructions also mention not to ungroup the worksheets after making these changes, ensuring that the formatting is uniform across the grouped sheets.

πŸ“‹ Formula Application and Worksheet Management

The fourth paragraph explains how to work with formulas and manage worksheets. It begins with creating a formula to total sales for a specific quarter and then copying this formula to other cells. The paragraph also covers ungrouping worksheets and verifying that the formatting and formulas are consistent across all worksheets. It proceeds with creating a copy of the Oregon worksheet for the Washington section, updating the worksheet name, and editing text and cell content as required.

πŸ“ˆ Consolidating Regional Data and Formula Manipulation

This paragraph focuses on consolidating data from different regions into a single worksheet. It starts with switching to the Consolidated Sales worksheet and entering a formula that refers to a specific cell in another worksheet. The instructions include copying the formula without the formatting to other cells. It also involves using 3D references to total values from specific cells across worksheets and copying these formulas to other ranges. The paragraph concludes with creating defined names for certain ranges and applying these names to formulas in the Consolidated Sales worksheet.

πŸ“Š Yearly Sales Data Comparison and External Reference Setup

The final paragraph describes the process of comparing yearly sales data and setting up external references. It begins with changing a defined name for a range to reflect 'Totals 2021'. The instructions then guide through opening a file with 2020 sales data and linking specific cells in the Consolidated Sales worksheet to cells in the 2020 data file. The process includes entering formulas to total values from the 2020 data and ensuring that the links are correctly established without breaking them. The paragraph concludes with a prompt to close the 2020 sales data file.

πŸ’‘Sales Manager
A sales manager is a professional responsible for overseeing a company's sales operations and team. In the video, Eda Kinsman is the sales manager for the Narbone Suit Hotel chain, indicating her role in managing sales data and strategies across multiple locations.
A worksheet in the context of Excel refers to a single page within an Excel workbook where data can be organized and manipulated. The script mentions multiple worksheets being used to summarize sales data by state, showing the utility of Excel in handling and segmenting information.
πŸ’‘External Link
An external link in Excel is a connection to data in another workbook or file. The script instructs to break the external link so that formulas in the manager information worksheets are replaced with static values, illustrating a step to ensure data independence and accuracy.
A hyperlink in Excel is a clickable reference that can direct users to a webpage, file, or another location within the workbook. The script details creating hyperlinks for email addresses and locations, showcasing how Excel can facilitate quick access to related information.
Formatting in Excel pertains to the aesthetic and stylistic presentation of cells, rows, or entire worksheets. The script describes applying consistent formatting to worksheets, such as changing font sizes and applying cell styles, to maintain a professional and uniform appearance across different workbooks.
πŸ’‘Merge Range
Merging range in Excel involves combining two or more adjacent cells into a single cell. The script refers to changing text in a merge range, which is a common task for creating clean and professional-looking headers or titles in a worksheet.
πŸ’‘Sum Function
The SUM function in Excel is used to add up a series of numbers in a specified range. The script instructs to use the SUM function to total sales for Q1, demonstrating a basic yet essential function for financial data aggregation.
πŸ’‘3D References
3D references in Excel allow formulas to automatically adjust when they are copied to other parts of the workbook, ensuring that the referenced cells maintain their relative position to the formula. The script mentions using 3D references for summing values, which is crucial for dynamic data consolidation.
πŸ’‘Name Manager
The Name Manager in Excel is a tool used to create, edit, and delete named ranges, which are labels given to cells or groups of cells for easier reference. The script describes using the Name Manager to define and apply names to ranges, streamlining the process of working with large datasets.
Consolidation in the context of Excel refers to the process of combining data from multiple sources into a single, unified view. The script outlines steps to consolidate sales data from different regions, which is essential for comprehensive reporting and analysis.
πŸ’‘Screen Tip
A screen tip in Excel is a small pop-up that appears when hovering over a hyperlink or button, providing additional information or instructions. The script mentions setting screen tips for hyperlinks, which can enhance user interaction by offering context or guidance.

Eda Kinsman, the sales manager for Narbone Suit Hotel chain, is using multiple worksheets to summarize sales data by state.

Instructions on breaking the external link in the worksheet to replace formulas with static values in the manager information worksheets.

Guidance on removing hyperlinks and retaining unlinked text in specific cells.

Creating a hyperlink to an email address in cell D7 with detailed steps.

Adding a screen tip for the email hyperlink to indicate the purpose of the link.

Creating a hyperlink in cell B9 to the Narone Suites location listing with specific display text and screen tip.

Editing the hyperlink in cell B10 to change display text and screen tip to reflect the Narbone Suit website.

Applying consistent formatting to worksheets from Idaho, Montana, and Oregon with specific font size and cell style instructions.

Instructions on bolding values and applying accounting number formats to specific ranges in the worksheets.

Resizing columns to a uniform width without ungrouping the worksheets.

Updating text in grouped worksheets to reflect changes for the Idaho, Montana, and Oregon sections.

Creating a formula in cell B10 to total sales for Q1 and copying it to other cells for different quarters.

Ungrouping worksheets and verifying that formatting and formulas are consistent across all three worksheets.

Creating a copy of the Oregon worksheet for the Washington section and updating the worksheet accordingly.

Consolidating data from each region in the Consolidated Sales worksheet with specific formula instructions.

Creating defined names for ranges in the worksheet to streamline referencing.

Applying defined names to formulas in the Consolidated Sales worksheet for different quarters.

Changing a defined name to 'totals 2021' for clarity and updating the references accordingly.

Comparing 2021 sales totals to 2020 by adding the 2020 data to the Consolidated Sales worksheet.

Creating external references to link cells in the Consolidated Sales worksheet to the 2020 sales data.

Entering a formula to total the values in the 'totals 2020' range using the SUM function and defined range name.

Rate This

5.0 / 5 (0 votes)

Thanks for rating: