This summary of the video was created by an AI. It might contain some inaccuracies.
00:00:00 – 00:14:03
The video provides a comprehensive guide on performing various advanced tasks in an Office application, particularly Excel. It starts with steps for enabling editing, updating Office, and creating range names using the 'Create from Selection' button. The instructor then explains how to configure HLOOKUP and VLOOKUP functions, set up formulas for calculations, and format cells for percentages and currencies. The video also covers the use of absolute references and the NOW function to display the current date. Further, it demonstrates creating logical arguments with IF functions for conditional checks, copying these functions down a column, and formatting text alignment. Finally, the presenter saves the document after pasting range names into a new sheet. Key takeaways include the use of Excel functions like HLOOKUP, VLOOKUP, SUMIF, and IF, along with formatting techniques and efficient data organization practices.
00:00:00
In this segment of the video, the instructor outlines several steps to perform specific tasks in an Office application. First, editing is enabled, and there is an option to update Office which can be accessed by clicking ‘update now’ or using the side arrow. Next, users are directed to select the ‘tables’ sheet and highlight cells A6 to B10 to create range names using the ‘Create from Selection’ button found in the ‘Formulas’ tab. Then, cells B1 to F2 are selected, and the range is named ‘h rates’ with the first row in descending order. Following this, an HLOOKUP function is set up to display the bonus rate by selecting the ‘commission’ sheet and cell F5, using cell E5 as the lookup value. The HLOOKUP function is then configured with ‘h rates’ as the table array and the second row as the row index number.
00:03:00
In this part of the video, the instructor formats cells to display results as a percentage with two decimal places and demonstrates copying formulas within a range of cells. The process includes setting the order of operations to calculate totals and commissions, building a formula in cell G5, and copying it downward. The instructor then explains how to create and copy a SUMIF function to calculate total earnings by branch office, specifying the appropriate range, criteria, and sum range with absolute and relative references.
00:06:00
In this part of the video, the speaker demonstrates using absolute references in Excel by incorporating the dollar sign before both the letter and number in a cell reference. They then copy a formula down to cell E20 and format cells E18 to E21 as currency. Next, they total the earnings in E21 by summing up the values from E18 to E20.
The speaker then shows how to create and format the current date using the NOW function in cell G23, and formats this cell to display the date with the month spelled out, followed by the day and the four-digit year. After formatting the cell, the speaker uses the ‘Control + Home’ shortcut to return to the top of the sheet.
Finally, the speaker begins to explain creating and copying a VLOOKUP function to display goals for each funding source by navigating to the “Family Day” sheet tab.
00:09:00
In this segment of the video, the instructor demonstrates how to use the VLOOKUP function in Excel. They begin by selecting cell F6 and using cell E6 as the lookup value. They specify the table array from cells A6 to E10, adjusting the references to be absolute. They use the second column as the column index number and leave the range lookup empty. The instructor then copies the VLOOKUP formula down to cells F7 to F20 and formats the results as currency with no decimal places. Finally, they introduce a step to create and copy an IF function, starting by selecting cell H6.
00:12:00
In this part of the video, the presenter demonstrates creating a logical argument in Excel to check if the value in cell G6 is greater than or equal to the value in cell F6, returning “yes” for true and “no” for false. They then copy the formula down the column and verify the results. Next, they center-align cells in a specific range (H6 to G20). Finally, the presenter inserts a new sheet named “range names”, pastes the range names starting in cell A1 using the F3 key, and saves the document.