Ultimate Excel Training Course - Intro to Advanced Pro

Name: Ultimate Excel Training Course - Intro to Advanced Pro
Rating: 4.6 (1653 reviews)

Accelerate Your Career. Navigation Shortcuts, Formatting Tips, Functions, Formula, Charts, Graphs, Pivot Tables, Macros!

Created byCommerce Curve

Last updated 6/2020

English

What you'll learn

Microsoft Excel - From Intro to Advanced Pro Knowledge
Financial Modeling in Excel
100+ Navigation Shortcuts and Formatting Tips In Excel
Over 100+ Formula and Functions In Excel
Charts and Graphs In Excel
Create Beautiful Dashboards Including Sales Funnels
Pivot Tables and Slicers in Excel
Macros In Excel
All of our lessons include real life examples so you'll know how to apply the formula, functions, charts, graphs, pivot tables, macros and more!

Course content

6 sections • 124 lectures • 15h 14m total length

Introduction and Course Overview3:01
This video provides a description and overview of the course structure. I built this course to include both video and excel workbooks which are downloadable and can be referenced to throughout the course and thereafter. I recommend following along using the workbooks as you progress throughout the course so that you can open up the cell formulas and practice performing the tasks that I illustrate in the formula.
Watching the video and following along will be beneficial but I believe that in order to fully grasp the shortcuts, function, formula, charts, graphs, and macros, you should practice during each lesson using the workbook provided. The workbooks I created include an explanation of the formula in detail, an explanation of the typical use cases for the formula, and examples for each lesson.
Start to integrate the knowledge from these lessons into your daily Excel work so that it becomes second nature to you and so that you gain efficiency in being able to perform the activities.
Toggle Between Excel Files1:11
The Control + TAB shortcut can be used to toggle between open Excel files. If you have multiple applications open and use this shortcut it will avoid toggling between other applications and only toggle between the open excel files. This is a useful shortcut if you work in finance or accounting and often have multiple excel files open amongst other applications.
Copy, Cut, Paste Special6:25
Cut, Copy and Paste Special are some of the most common functions within Microsoft Excel. In this lesson, we will review the shortcut that is used for applying each of them. Understanding these shortcuts will save a lot of time on a daily basis and make your procedures more efficient since they are often used in a lot of Excel analyses. The "cut" shortcut is Control + X. The "copy" shortcut is Control + C. The "paste special" shortcut is ALT + E + S and then you have the option of selecting what type of paste special function to use. Make sure you download the two workbooks: Excel Course Part 1 - Navigation & Formatting, Excel Course Part 1.1 - Navigation & Formatting - Data Set Example
Navigation Tips And Highlighting Data4:07
To save time and work efficiently, these main shortcuts are essential for navigating data within a worksheet in Excel:
1) The Control + Arrow keys can be used to move up, down, left, and right within cells. It is used to quickly move between cells within the worksheet.
2) The Control + Shift Arrow keys are used to highlight rows and columns depending on the sign of the arrows. Alternatively, the Control + Back shortcut is the best way to navigate back to the top without removing the highlighted selection.
3) The Control + A key shortcut is used to highlight all of the data within the table. Further, the Control + A key (hit twice) is used as a shortcut to highlight the entire workbook.
4) The Shift + Space bar is used to highlight a row within a worksheet, while the Control + Space bar is used to highlight a column within a worksheet.
5) To navigate to the end of the worksheet, the shortcut is Control + End. To navigate back to the top of the workbook and at the start of the worksheet, the shortcut used is Control + Home key.
Auto-Sum Adjacent Rows/Columns1:04
The Alt + = key shortcut is used to auto-sum adjacent rows and columns. This is used to sum for both the values to the left and values of the top.
Create Multiple Rows In A Cell0:55
The Alt + Enter key can be used to create multiple rows in a cell. It is typically used as a better way to organize data and information, and have multiple sentences or points within a single cell.
Insert, Delete, Hide, Move Columns2:45
The quickest way to insert a column on Excel is by highlighting a row below where you like to insert the new row using the Control + Space bar. Then press Control + Shift = (+/= button) to insert a column. Similarly, to delete a row, highlight it and press the Control + - (negative) button.
The Control + 0 shortcut is used to hiding a row. To identify if there are any hidden rows in a worksheet, simply press and hold the Alt + ; (semicolon) button to highlight the hidden rows and columns in the sheet. To undo this action, press Control + Z.
To move a row, simply highlight it by using the Control + Space bar key, hover to the edge of the row and then click/hold/drag it wherever in the sheet. By holding the Shift key, the row will then move locations and shift the other row that was previously there beside it (moves the other column to the right). The purpose is to not delete or override any data in the worksheet.
Insert, Delete, Hide, Move Rows2:16
The quickest way to insert a row on Excel is by highlighting a row below where you like to insert the new row using the Shift + Space bar. Then press Control + Shift = to insert a row. Similarly, to delete a row, highlight it and press the Control + - button.
The Control + 9 key is used to hiding a row. To identify if there are any hidden rows in a worksheet, simply press and hold the Alt + ; button to highlight the hidden rows and columns in the sheet. To undo this action, press Control + Z.
To move a row, simply highlight it by using the Shift + Space bar key, hover to the edge of the row and then click/hold/drag it wherever in the sheet. The row will then move locations and shift the other row that was previously there below it.
Datestamp And Timestamp0:16
The Control + ; (semicolon) key shortcut can be used to insert a Datestamp into a spreadsheet.
The " Control + Shift ; " is used to insert a Timestamp in a spreadsheet.
Show Formulas Shortcut1:33
The (Control + ' ) key can be used to toggle a view between formula and non-formula view mode. It is typically used when writing formula and can be useful for auditing data, determining how formula was written, and understanding where the numbers are coming from. Another way is to go on a cell and hit "F2", it will open up the formula associated with that cell.
Quick Save Shortcut0:21
The "Control + S" key can be used to save a file on Excel. It is typically used as a shortcut instead of going to 'File' and selecting 'Save'.
Quick Undo Shortcut0:20
The Control + Z button can be used as a shortcut to undo previous action on Excel. It is typically used as a quicker method to undo actions rather than going to the top navigation bar and clicking the undo button.
Turn Off & On Formula Calculations2:28
The manual calculation option can be used in Excel for workbooks with large data that lags each time. Going to the "Formula" section in Excel, then clicking "Calculation" options and then selecting the manual mode. Press "F9" to recalculate the workbook to save time having to wait on the workbook to recalculate, update and refresh the numbers.
Hide Excel Sheets & Make Very Hidden2:40
To make a sheet very hidden instead of just hidden, press and hold "Alt + F11" to open up VBA, then click on the sheet you want hidden on the "Properties" section, and then on the 'Visible' section on VBA change the dropdown to "Very Hidden" (Sheet Visible, Sheet Hidden, Sheet Very Hidden).
Change Window Size1:41
If you have multiple files open and you want to do a side by side comparison between worksheets and/or documents, you may need to shrink the size of the workbook's view. You can modify the size of the active workbook and its window by pressing and holding the 'Windows' key followed by the 'up', 'down', 'left', and 'right' buttons.
Windows key + Up: Makes the window fullscreen. Windows key + Down: Makes the window shrink in size. *If done twice, it will minimize the file. Windows key + Left: Justify the window to the left in a half screen view. Windows key + Right: Justify the window to the right in a half screen view.
Open Duplicate Window1:36
Opening up a duplicate window gives you the ability to make a comparison between 2 different areas of a workbook and navigate them both simultaneously.
To do this, simply navigate to the 'View' tab on the top ribbon, and click on the "New Window" button which will open up the new window. Make sure you close that second window and then save the workbook once you are finished working on it to avoid both windows from reappearing.
Formatting Text1:48
The most common ways to format texts in Excel is to use the Bold, Underline, and Italics functions or to change the font colour, text style, or text size. A few common shortcuts which are easy to remember relate to Bold, Underline, and Italics.
To bold a text on Excel: Press "Control + B" on the selected cell.
To Underline a text: Press "Control + U" on the selected cell.
To Italicize a text on Excel: Press "Control + I" on the selected cell.
Formatting Cells1:56
The shortcut to opening up formatting options in Excel is "Control + 1". For example if we change the dollar sign to 2 decimal places, we would change the category to number and change the decimals to 2 places.
Copy Cell Above Shortcut0:39
A quick shortcut to copy down the shortcuts to the above cell, is to click on the cell under it, and then hold "Control + D". This will copy down the contents of the cell instead of having to copy and paste the data from above it.
Find Keywords1:14
If you want to do a quick search for a key word in Excel, press "Control + F" and then simply search the key word in the dialog box and click 'Find'. You can search in either the tabs within the worksheet, or the entire workbook.
Navigating Excel Sheet Tabs2:14
There are multiple ways to navigate the tabs throughout an Excel workbook.
- To navigate quickly, the first option is by right clicking on the section at the bottom of the screen where it shows arrows beside the tabs. This will show a pop up a list of tasks to navigate through.
- The second option is to press "F6" and then using the arrow keys to navigate left or right on tabs.
- The third option is by using the "Control + PageUp" (to navigate to the left tabs) and "Control + Page Down" (to navigate to the right tabs) functions. This toggles you through the different tabs of the workbook one at a time.
Modify Multiple Tab Zoom Levels1:27
For presentation purposes, it is visually pleasing to keep the same zoom levels for each worksheet.
To achieve this, simply go the first worksheet and hold the Shift button, then scroll over to the last tab of the workbook and click on it. Finally, modify the zoom level on the bottom right corner for the selected worksheets.
Navigate To A Specific Cell1:44
The Active Cell Reference function is used to navigate to a specific cell location in an Excel Spreadsheet and can be found in the top left corner beside the formula bar.
This quick function comes in handy when jumping between different areas of a worksheet. The purpose of this function is that it more efficient for large amounts of data.
Show The Desktop Shortcut0:41
When you have multiple tabs open and want to navigate to your desktop to find a file, instead of clicking on the "Show Desktop" button, you can simply use the "Windows key + D". To switch back to your last active window, repeat the action by pressing and holding the "Windows key + D"
Add & Remove Filters0:53
Press "Control + Shift + L" to add a filter in your data set. Once you have your data sets with filters ON, you can click on the 'Filter' button on the top row and you'll be able to filter in for example product name, id, #, etc. Then if you click "Control + Shift + L" again, it will remove the filter and the data set appears unfiltered again.
20+ Formatting Tips19:50
Good presentation is crucial for a good impression on top management and provides an easier way to look at your work. These 19 formatting tips will help make things easier on the eye!
1) Page Layout/Disable View Gridlines: Hiding the gridlines is a good way to make the worksheet look more smooth. It can be done by going on "View" and checking on the "Gridlines" button.
2) Format Painter: To make a cell the same format as another, click the paint brush button on the top left ribbon.
3) Clear Formats: Clear formats (font, size, grid lines, etc) to clarify and cleanse data to its raw format. Click on the "Home" button on the top ribbon, navigate to the "Clear" button on the right side and then click the "Clear Formats" button.
4) =iferror(),0): To Remove formula errors from results.
5) Use the ' to Insert a 0 at the beginning of a cell. This is useful since 0's generally disappear from cells.
6) Freeze panes: Freeze panes so that the viewer doesn't have to guess or scroll back up to the header to view the header name or date.
7) Click on date cells and format cells as dates and make a selection: Choose a date format and be consistent with it throughout the financial model.
8) Locking Cells (Tip - Click F2 to enter cell): Lock cells to avoid movement when copying formula.
9) Home / word wrap: Word wrap text to avoid text going into subsequent cell.
10) Review/ spelling: Go to the "Review" section and click on "Spelling" to check for any spelling errors.
11) Present subtotal data properly without inserting spaces so that formula can still be used on the cells. This can be done using the Align signs for left and right from the "Alignment" section from "Home" tab.
12) To Highlight data table, click control + E. The purpose of flash filling is that it will automatically infer the first and last names from a list of emails if presented by that order.
13) Setting up a P&L for presentation
14) Instructions tab: Have a tab dedicated with instructions on how to update the financial model.
15) Assumptions tab: Have a tab dedicated with key assumptions built into the financial model and make them variables.
16) Place dates along the columns, and summarize data into fiscal quarters and years.
17) Highlighting Cells: Best practice is to have standardized formats for the meaning of cells so it is well understood by the user.
18) Input cells - for example can be set in as blue background or blue font.
19) Cells with issues or which require updating highlighted in yellow for example.
20+ Date Formula33:43
The following 20+ Date Formulas are typically used for better navigation and formatting and they are as follows:

1) Drag down numbers and dates to auto populate them in sequence to easily populate sequential data.
2) Subtract one value from the other to count the days between two dates.
3) To insert today's date enter: =today()
4) To Count items in a table if they are more recent than a particular date, enter: =COUNTIF(REFERENCE TABLE,"greater sign"&REFERENCE CELL)
5) To Count items if greater than a date and less than a date: =COUNTIFS(REFERENCE TABLE,"less than"&REFERENCE CELL,REFERENCE TABLE,"greater sign"&REFERENCE CELL)
6) To Count items if greater than or equal to a date and less than a date: =COUNTIFS(REFERENCE TABLE,"lesser sign"&REFERENCE CELL,REFERENCE TABLE,"greater sign="&REFERENCE CELL)
7) Sum items if greater than a date by: =SUMIFS(DATA TO SUM,REFERENCE TABLE,"greater sign"&REFERENCE CELL)
8) Sum items if greater than a date and less than a date by: =SUMIFS(DATA TO SUM,REFERENCE TABLE,"greater sign"&REFERENCE CELL,REFERENCE TABLE,"lesser sign"&REFERENCE CELL)
9) Equation to determine whether one date is greater or less than another: =IF(E59 greater than DATE(yyyy,m,dd),value if true,value if false)
10) =EOMONTH(D44,0) The zero is used if you want to obtain the end of month date for the current month. Add 1 for each additional month prior to or after the current month. For example, previous month would be -1 and subsequent month would be 1.
11) To Insert quarter end text of referenced cell: ="Q"&ROUNDUP(MONTH(REFERENCE CELL)/3,0)&" "&YEAR(REFERENCE CELL) This is very useful if you work in accounting / finance and often work with large data sets across multiple months, quarters, and years and you often need to summarize the data by quarter.
13) =day() To Return the day of a date reference cell.
14) =month() To Return the month of a date reference cell.
15) =year() To Return the year of a date reference cell.
16) =TEXT(REFERENCE CELL,"dddd") To Return the text day of the week of a reference date.
17) =TEXT(REFERENCE CELL,"mmmm") To Return the text month of a reference date.
18) =TEXT(REFERENCE CELL,"mm") To Extract the 2 digit month from date cell
19) =TEXT(REFERENCE CELL,"mmm") To Extract the 3 letter abbreviation month from date cell
20) =TEXT(REFERENCE CELL,"YY") To Extract the 2 digit year from date cell
21) =TEXT(REFERENCE CELL,"YYYY") To Extract the 4 digit year from date cell
22) =EDATE(CELL,months) To Return another date based on number of months in future or past
This is different from the =EOMONTH formula which looked at the month end date, not the exact date 1 month prior or before like this formula for EDATE is able to achieve.
Workday Formula7:57
The "WORKDAY" formula is used to find the nearest working day in the past or future when you reference a particular date. You can offset the number of workdays you would like to return as the result. You can use the "WORKDAY" function to calculate things due dates, shipment and delivery dates, and working day deadlines all of which take into consideration that there will be holidays and weekends that need to be considered in the timeline.
=WORKDAY(start_date, days, [holidays])
start_date: The date from which to start.
days: The number of non-weekend and non-holiday days before or after start_date. A positive number represents the future and a negative number represents the past.
holidays [optional]: A list of dates that should be considered non-work days e.g. statutory or federal holidays. You can either list the dates in the formula, or highlight a range of cells where you have the holidays listed.
Common Excel Errors11:34
Common formula errors you can encounter (contains problems and how to fix them):
######: The column is not wide enough to show all of the contents in the cell. Expand the width of the column.
# Div/0! The number was divided by zero or a blank cell. Update the formula so that the number is not being divided by zero or a blank cell.
#NAME? The text of the function name is not recognized by Excel. This is caused by misspelling the function names. Fix the spelling of the function name.
#VALUE! The formula includes cells that contain different data types. Correct the inputs of the formula so that they all use a common data type.
#REF! A cell reference is not valid or no longer exists. E.g. deleting cells that were referred by other formula. Update the formula to remove the #REF error and to change the input location.
#NUM! The formula or function contains invalid numeric values or functions. Review the formula to make the numeric values or formula valid. If required, remove the $ or # signs.
#NULL: The intersection of two areas do not intersect (cross). Update the formula to ensure that there is crossover between the data for the formula.
Copy Vs. Cut3:13
Copy: Used if you want to copy the contents of a cell to another location.
Cut: Used if you want to move the contents of a cell to another location and have it deleted from the previous locations. Also useful if you are moving a formula.

Example: The Finance Analyst generates an export of the raw data of the BMW sales for the current year from it's ERP system.
Q1: Car sales are summarized in a table for the Financial Analyst at BMW below. The sum of the car sales for January are in cell D22 but the Analyst wants to move it to cell D23.
Solution: The solution is to cut the formula to move it to the new location. If you copy it, it will remain in the prior location and the formula will not be accurate if copied.
Precedents And Dependents3:58
The precedents and dependents function is used to graphically display and trace the relationships between cells and formulas with tracer arrows as shown in this figure below:
This is helpful in a couple of ways:
1) Audit purposes to determine where a value is being derived from (precedents) and mapping it through its touch points in a workbook and ...
2) Determining whether a cell has dependents which rely on it before you delete or move the cell as that would cause a breakage of other areas of the workbook.
Precedent cells - These are cells that are referred to by a formula in another cell.
Dependent cells - These are cells which contain formulas that refer to other cells.
Follow these steps to display formula relationships amongst cells:
1. Select a cell to check precedents/dependents.
2. After selecting a cell, go to "Formulas tab" and select trace precedents.
3. After clicking Trace Precedents a blue tracer arrow displays where the cell E9 was referred.
4. For Trace Dependents a blue tracer arrow points to another cell that depends on the cell.
Group And Ungroup Rows And Columns6:53
The grouping function in excel is useful if you'd like to group together a cluster of rows or columns. This works well for structuring worksheets which are large and you want to have a summary view for presentation purposes.
For example, if a spreadsheet is organized such that there are multiple monthly columns, you could group together 3 at a time and have a summary view for each quarter. Additionally, you could group together 12 months as one year. Rows can also be grouped together which is similarly handy if you are working with a large dataset and there is a logical way to summarize the data into categories with subtotals.
How to Group Rows in Excel:
1. Select the set of rows which you'd like to group together. Note that you should not select the row that you want to remain visible.
2. After selecting a rows, go to "Data tab" and select Group.
3. After clicking Group an outline will show appear beside those rows indicating that the rows selected has been grouped. The + and - button on the left of the rows will allow you to collapse and expand the grouping at any time. To ungroup the rows, highlight the rows and then go to the "Data" tab and select ungroup.
4. You can also create an Outer Group for larger subset of data. This will create two collapsable and expandable groups of data (see below). The outer group contains the inner group of data. If you collapse the outer group it will hide the inner group of data.
Protect Ranges & Worksheets5:31
The protect range function is a useful way to prevent other users of a workbook from altering data within a worksheet. You can thereby prevent the accidental or intentional modification or deletion of worksheets or cells within a worksheet through password protection.
As the owner of the workbook, you can set the rules on what worksheets and cells are off limits for modification. This can be a valuable function if you have created a workbook that you will be sharing with a third party and you want to ensure that they do not modify certain aspects of it. Without this feature used, you would have to manually check to see if they made modifications to the worksheet areas which you did not want modified.
Steps to Enable Worksheet Protection:
1. In the Excel file, select the worksheet tab that you want to protect. Then go to the "Review" tab, and click "Protect Sheet".
2. A prompt message will appear showing elements you want people to be able to change along with the requirement to enter a password. There are several options available here with respect to what you would like to protect in the worksheet. For instance, you may want to protect the worksheet but still allow the user to be able to select the cells of the worksheet to navigate it, format the cells, sort the data, and use PivotTable and PivotChart features while not modifying the data itself. In that case, you would multi-select those features in the below list and click "OK".
3. After pressing OK, the worksheet will now be protected. If you try to modify a protected worksheet, the below message notification will appear.
4. You can also set range where you would like to allow someone to edit specific cells rather than the whole worksheet. To do this, go to the "Review" tab, then click "Allow Edit Ranges" and click "New." The below popup will appear. When this appears, you can give the editable range a title, and then in the "Refers to cells:" section, highlight the specific cells in the worksheet you would like to make editable.
5. You can also protect the entire workbook with a password instead of locking each individual tab.
Screenshots In Excel3:16
If you're doing tutorials, walkthroughs or need to paste a screenshot In the Excel file to show a reference to something or support for something. This could be helpful for bank reconciliations where you'd like to show the end of month bank balance per the online website.
This is occasionally more helpful than using the snipping tool because that tool occasionally closes an open window. SCREENSHOT: The operate this function, you need to navigate the home screen and go to the Insert section. Click "Screenshot".
Sorting Data9:02
Sorting is beneficial when you are using a large table of data with headers and multiple columns of data exist. Excel's sorting function allows for multiple layers of sorting to be used in sequence i.e. sort by column X, then by column Y, then by column Z.
Steps:
1. Select the data table.
2. "Home" -- "Sort & Filter" -- "Custom sort"
3. Using the example table below, sort column data according to Manufacturer, sort on Cell Values and set the Order as A to Z.
4. Using the "Add Level" button you can also add an additional level of filtering (e.g. by product name).
Remove Duplicate Values5:02
This is a commonly used function if you need to find only unique values of a characteristic of data within a table. If a data table contains an abundance of data and you want to make a list of the unique items this formula is often handy.
Data / Remove Duplicates: This function is used to remove duplicate values from a series of data in a table.
Steps:
1. Highlight the column in the table for car dealer branch locations.
2. Click "Remove Duplicates".
3. Select which column of data to remove duplicate values from.
Text To Columns5:43
Exported data from a system contains a mix of data attributes. You need the data to be split up so that you can only the relevant part of it.
Text to Columns Function: This function is used to split cell contents into multiple columns depending on the criteria set.
Example: The HR Analyst received data exported from the company's HR database containing employee name and phone number in the same cell. The HR Analyst needs to make a list of the employee name in a column separate from their phone number.
Steps:
1. Highlight data in table. Click the Data tab.
2. Click Text to Columns.
3. Select delimited if you'd like to separate data that has characters or a space between the data.
4. Indicate the defining criteria for splitting the data. E.g. in the example below, there is a colon which can be used to separate the name and phone number.
Conditional Formatting29:10
Conditional formatting in Excel enables you to highlight cells with a certain colour depending on the cell's value. This is a beneficial function when you want to draw attention to data that meets certain value criteria.

Goal Seek15:44
The Goal Seek function is used in FP&A to determine the amount of an outcome when there is only one lever to pull and all other variables are kept constant.
Examples include determining how much money is required to spend to be able to bring in a specific number of customers and we know the funnel conversion metrics.
In this example the variable is the spend we have a goal for the specific number of customers to acquire.
SET CELL: This input is the cell which will contain the outcome you're looking for.
TO VALUE: This input is the outcome you're looking for.
BY CHANGING CELL: This input is the variable cell reference.
Steps:
1. Go to data / What-If Analysis / Goal Seek
2. Input the Goal Seek Values
Data Validation26:19
Conditional formatting in Excel enables you to highlight cells with a certain colour depending on the cell's value. This is a beneficial function when you want to draw attention to data that meets certain value criteria.
1. The first step in creating a picklist cell is to make a list somewhere in the workbook of the items that will be contained in the picklist as options.
2. Select the cell(s) where you would like the picklist to appear in the spreadsheet.
3. In the top menu, go to "Data" and then click "Data validation".
4. Select 'List' option from Allow drop down menu, and for Source, click on the range of cells containing the picklist.
5. If you'd like to insert the picklist into an existing table, you just need to highlight the column of cells where you would like to insert the picklist and go through the above steps.
Scenario Manager15:46
Scenario Manager is a what-if analysis tool, which can be used to analyze the impact of variables on a certain output in a data set. For example, while creating a budgeted profit and loss, different scenarios can be generated by assigning different values to certain variable expenses. Changing different variables will impact the budgeted profit and loss under each scenario.
When using Scenario Manager, we name a scenario, specify the cells whose values we will be changing and then specify the values to change.
Steps to create scenarios, using scenario manager, and execute the following steps:
1. On the data, click what-if analysis and select Scenario Manager.
2. Click on the add button: a. Type a name for first scenario b. In changing cells sections, specify the cells whose content we will change during our analysis. c. a new dialogue box "Scenario Values" will open. It is in this box that we will type our values for the cells indicated.
3. Displaying Scenarios side by side for analysis:
a. Open the scenario Manager and click the Summary Button.
b. In result cells, select the cell showing monthly lease payment and click OK.
c. Excel will display the changing cell and the result cell for each scenario in a side by side comparison on a new sheet. This provides a great overview of the different results by scenario.
Name Manager7:17
The name manager function allows us to give a cell or a group of cells a name of our choice. These names can then be used in formulas and dialogue boxes. If you have a financial model or workbook with lots of formula, some of which can get complex or lengthy, it can be helpful to assign a name to a particular formula or reference to assist with continued modelling or for auditing purposes.
Example use cases:
- Naming a cell as "total" or "COGS" or "Profit" is easy to remember and makes it easier to work with when handling data across multiple sheets.
- Creating names for cells, formula makes them more understandable e.g =sum(Expenses) as compared to =sum(D12:D20).
- Named cells are absolute. This helps avoiding copy and paste errors when working with formula.
Advanced Filter5:36
Advanced filtering is used to filter data when there is complex criteria.
Steps:
1. Set up your criteria.
2. Select your entire data table.
3. Go to Data - Filter - Advanced
4. Enter the List range, Criteria range, and Copy to location.
Dependable Drop Down Lists15:09
Creating dependable drop down lists allows for an efficient workflow to be created. In turn this sets rules for what can ultimately be chosen in a picklist and can act to strengthen the accuracy of the selection made to ensure erroneous errors are not made. This can be useful when working with lots of data, categories, sub-categories. It can also be particularly useful when sharing a worksheet with others and asking them to complete certain drop down forms or providing them with a sheet that has the drop downs setup to drive different results.

Steps:
1. Create your list of names according to their criteria as the reference.
2. Convert every list into table (go to insert/table) and change their name in the table design tab.
3. Select all the headers together, go to Formula - Name Manager and create a new name for it without spaces.
4. Go to Formulas/Name Manager/New to create the names of subordinate data (each individual column of category).
5. Insert two columns in the table "Account Name" (which will be the picklist of the categories) and "Account Type" (sub-category picklist).
6. Select all the blank cells of Account Name column, then go to "Data" -- "Data Validation". Select "List" from Allow, and put "=accounttype1" in the source field since that's what name we gave to the categories.
7. Select the cell that is at right of the first account type cell. Select "List" from Allow, and put "=INDIRECT(ref)" in the source cell. *Note "ref" should be the reference cell to the left of it. In the source cell do not lock the row number. You want to drag down the drop down cell and have it reference the cells beside it in the table.
8. Copy the the cell and paste on rest of the following cell.
Indirect14:06
The INDIRECT formula is used to find out the value of a cell which resides in a different sheet by making an indirect reference. This is useful if you have data spread out across multiple sheets and want to summarize the data on another tab. It is especially useful if the data that is contained on the tabs is consistent and easy to locate and the only change is the sheet name.

=INDIRECT(""&sheet&"!"&"reference")

1) INDIRECT: Bring value from a different sheet.
2) sheet: The sheet name from which we want to find the result.
3) reference: The set of values in the sheet which is selected in the formula.
Fuzzy Lookup5:34
The Fuzzy Lookup Add-In for Excel was developed by Microsoft and uses advanced mathematics and probability to perform fuzzy matching of textual data in Microsoft Excel. It can be used to identify duplicate rows within a single table or to compare two different tables and match their similarity. The matching is done keeping in view the possible wide variety of errors including spelling mistakes, abbreviations, synonyms and added/missing data. For instance, it might detect that the rows “Mr. Chris Lazarte ”, “Lazarte, Chris” and “Chris Lazarte” all refer to the same underlying entity, returning a similarity score along with each match.

UPPER, LOWER, PROPER FORMULA4:07
Upper: Change the text to become capitalized.
Lower: Change the text to become lowercase.
Proper: Change the text to capitalize only the first letter and lowercase the rest of the word.
TEXT: This formula only has one syntax: text.
The process is to use either the Upper, Lower, or Proper formula and click on the cell containing the text which you would like to modify.
TRANSPOSE FORMULA5:40
This formula is useful if you are working with data that is in a vertical or horizontal view and you need it in the opposite direction.
ARRAY: This syntax is used to reference the data to be transposed.
Setup the transpose formula. Select the array table. Use "Control" + "Shift"+ "Enter".
SUMIF FORMULA4:24
The SUMIF formula is a function used to sum the values in a range that meet criteria that you specify.
=SUMIF(range, criteria, sum_range)
Range (required): The range of cells that you want evaluated by your criteria.
Criteria (required): The criteria is the item you would like to look up in the range. It can be a number, expression, a cell reference, text, or a function that defines which cells will be added together in your SUMIF formula.
Sum_Range (optional): The actual cells to add, if you want to add cells other than those specified in the range argument. If the sum_range argument is omitted, Excel adds the cells that are specified in the range argument (the same cells to which the criteria is applied).
COUNTIF FORMULA4:37
The COUNTIF formula is typically used to count the number of cells that meet a criterion.
=COUNTIF(range, criteria)
Range (required): The range of cells that you want evaluated by the criterion to perform the count.
Criteria (required): This is the criteria in the form of a number, expression, a cell reference, text, or a function that defines which cells will be counted.
AVERAGEIF FORMULA4:13
The AVERAGEIF Formula is used to return the average (arithmetic mean) of all the cells in a range that meet a given criteria.
=AVERAGEIF(range, criteria, average_range)
Range (required) - The range of cells which contain the criteria which you want to analyze. This range can include numbers or names, arrays, or references that contain numbers.
Criteria (required) - The criteria is used to lookup in the range and determine what is to be averaged. It can be in the form of a number, expression, cell reference, or text.
Average_range - The actual set of cells to average.
SUMIFS FORMULA6:30
The SUMIFS Formula is used to add the numbers which apply when multiple criteria are met. This is different from the SUMIF formula which only relies on one condition.
=SUMIFS(sum_range, criteria_range, criteria1, [criteria_range2, criteria2], ...)
Sum_range (required) - This is the range of cells which contain the numerical values to added together.
Criteria_range1 (required) - The range that is tested using Criteria1. Criteria_range1 and Criteria1 are considered a pair (criteria1 searches within criteria_range1). Once items in the range are found, their corresponding values in Sum_range are added.
Criteria1 (required) - The criteria that determines which cells in Criteria_range1 will be added up.
Criteria_range2, criteria2, … (optional) - This refers to additional ranges and their related criteria. You can enter up to 127 range/criteria pairs.
In the example shown:
i. Sum_range formula was used for determining the branch's sales revenue.
ii. Criteria_range1 formula was used for determining the data range of a specific branch location.
iii. Criteria1 formula was used for determining a specific branch location.
iv. Criteria_range2 formula was used for determining the data range of what type of product description should be summed in a specified branch location.
v. Criteria2 formula was used for filtering the specified product description.
COUNTIFS FORMULA6:39
The SUMIFS Formula is used to add the numbers which apply when multiple criteria are met. This is different from the SUMIF formula which only relies on one condition.
=SUMIFS(sum_range, criteria_range, criteria1, [criteria_range2, criteria2], ...)
Sum_range (required) - This is the range of cells which contain the numerical values to added together.
Criteria_range1 (required) - The range that is tested using Criteria1. Criteria_range1 and Criteria1 are considered a pair (criteria1 searches within criteria_range1). Once items in the range are found, their corresponding values in Sum_range are added.
Criteria1 (required) - The criteria that determines which cells in Criteria_range1 will be added up.
Criteria_range2, criteria2, … (optional) - This refers to additional ranges and their related criteria. You can enter up to 127 range/criteria pairs.
In the example shown:
i. Sum_range formula was used for determining the branch's sales revenue.
ii. Criteria_range1 formula was used for determining the data range of a specific branch location.
iii. Criteria1 formula was used for determining a specific branch location.
iv. Criteria_range2 formula was used for determining the data range of what type of product description should be summed in a specified branch location.
v. Criteria2 formula was used for filtering the specified product description.
AVERAGEIFS FORMULA5:45
The AVERAGEIFS Formula returns the average (arithmetic mean) of all cells that meet multiple criteria.
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Average_range (required) - One or more cells to average, including numbers or names, arrays, or references that contain numbers.
Criteria_range1, criteria2, … (required) - Criteria_range1 is required, subsequent criteria_ranges are optional. 1 to 127 ranges in which to evaluate the related criteria.
Criteria1, Criteria2 (required) - Criteria1 is required, subsequent criteria are optional. 1 to 127 criteria in the form of a number, expression, cell reference, or text that define which cells will be averaged.
i. Average_range formula was used for determining the branch's sales revenue.
ii. Criteria_Range1 formula was used for determining the data range of a specific branch location.
iii. Criteria1 formula was used for determining a specific branch location.
iv. Criteria_range2 formula was used for determining the data range of what type of product should be averaged in a specified branch location.
v. Criteria2 formula was used for filtering the specified product description.
SUMPRODUCT FORMULA34:16
The SUMPRODUCT formula returns the sum of the products of corresponding ranges. The default operation is multiplication, but addition, subtraction, and division are also possible. The SUMPRODUCT formula was used more frequently in versions of Microsoft Excel prior to 2007 before the SUMIFS formula was introduced. However, it still has benefit in its flexibility compared to SUMIFS as we will see in this lesson.
=SUMPRODUCT(array1, [array2], [array3], ...)
Array1 (required) - The first array argument whose components you want to multiply and then add.
[array2], [array3],... (optional) - Array arguments 2 to 255 whose components you want to multiply and then add.
INDEX FORMULA13:01
The Index formula is typically used to return the value of an item in a table depending on the row and column number chosen to be indexed in the formula.
Array (required) - This is a range of cells.
- If the array contains only one row or column, the corresponding row_num or column_num argument is optional.
- If array has more than one row and more than one column, and only row_num or column_num is used, INDEX returns an array of the entire row or column in array.
row_num (required) - This selects the row in the array from which to return a value. If row_num is omitted, column_num is required. column_num (optional) - This selects the column in the array from which to return a value. If column_num is omitted, row_num is required.
This is a two step process:
1. Create an index formula based on the below tables.
2. Create picklists (above) which perform VLookups against a reference table which specify the location of the data within the array table below.
MATCH FORMULA5:13
The MATCH formula looks for an item within a range and returns relative position of that item in the range.
Lookup_value (required): The value that you want to search within the range i.e. the lookup_array.
Lookup_array (required): The range of cells being searched to find the lookup_value.
Match_type (optional): This is a number being -1, 0, or 1. The match_type argument specifies how Excel matches the lookup_value with values in the lookup_array range. The default value for this argument is 1.
SUMIF WITH INDEX(MATCH) FORMULA9:52
The SUMIF with an embedded INDEX MATCH formula is powerful when you need to summarize the data contained in a table and you have unique headers in the columns and the rows contain duplicates. This is the combination of 3 formula (SUMIF, INDEX, AND MATCH) each of which are described below.
- The SUMIF formula uses conditional logic to calculate the sum of amounts in an array. - The INDEX formula performs a lookup of an array of data.
- The MATCH formula performs a comparison of data to a selected cell to determine whether they are equal to each other.
When used in combination, we can INDEX an array, find a MATCH of data and take the SUMIF depending on whether the conditions are met.
INDEX(MATCH) and DGET FORMULA13:05
The index match is a good alternative to a nested if formula or using the Ifs formula.
=index(dataset, MATCH(lookup_value, lookup_array, [match_type]))
This formula combines both the Index and Match formulas together in one equation. When using the Index and Match formula together, the Match formula is inserted where the row syntax is located within the Index formula. The first part of the formula references the "array" which is the table upon which you are referencing.
The DGET formula is a database lookup function which references an array, and returns a result (i.e. the field referenced), with the option of setting
=DGET(database, field, criteria)
DGET is useful for quick lookups but not as flexible as INDEX(MATCH).
The benefit of this formula is mainly in cases where you need to perform a lookup using multiple criteria and a VLookup is not appropriate since a VLookup only works with one criterion. Therefore, either you use a SUMPRODUCT formula, INDEX(MATCH) or DGET.
-- DGET is short and can be simple to use in many cases.
TEXTJOIN FORMULA14:05
The TextJoin formula is used for concatenating cells together using a delimiter to separate them. This is especially more efficient to use versus a manual method of concatenating cells together if you are working with a large data set and just need to concatenate them together with a common delimiter.
=TEXTJOIN(delimiter, ignore_empty, text1, ...)
Delimiter (required): The delimiter is the character that separates the text joined together. For instance, the cat could be separated as the-cat with a hyphen.
Ignore_empty (required): There is an option as to whether you'd like to ignore empty cells or still join them.
Text1 (required): The text syntax refers to the text cell that you'd like to join. There are the option of joining multiple cells.
MIN, MAX, MEDIAN FORMULA3:04
You can use MIN and MAX formula to determine the smallest and largest values in a field based on the specified aggregation or grouping.
The MEDIAN is the number in the middle of a set of numbers.

=MAX(number1, [number2], ...)
=MIN(number1, [number2], ...)
=MEDIAN(number1, [number2], ...)

Number1… (required): Number range in which you want to find the maximum, minimum or median value.
MINIFS, MAXIFS FORMULA7:24
The MINIFS, MAXIFS formulas are helpful in determining the minimum or maximum values when you have certain conditions that need to be applied in a scenario.
=MAXIFS(max_range, criteria_range1, criteria1, ...)
=MINIFS(min_range, criteria_range1, criteria1, ...)
Max_range or Min_range: The range where we are searching for the minimum or maximum value.
Criteria_range1: The range of the criteria where we want to search for the result.
Criteria1: The item we are looking for in the criteria range which meets the minimum or maximum value.
IF FORMULA4:20
The IF function is used to make a logical comparison between two values either within a formula or by comparison the values / arguments of two cells. The IF function then determines whether the argument is TRUE or FALSE.
=IF(logical test, [value_if_true], [value_if_false])
=IF (E29 greater than F29, "Reject Bid", "Accept Offer")
Logical_test (required): The condition you want to test the logic of.
Value_if_true (required): The value that you want returned if the result of logical_test is TRUE.
Value_if_false (optional): The value that you want returned if the result of logical_test is FALSE.
IF(AND) FORMULA7:40
The IF function can be used to perform a logical comparison between two values by testing for a condition and returning the result based on whether that condition is TRUE or FALSE. You can also use the AND function, one of the logical functions, in combination with the IF function to determine if multiple conditions have been met and return the value TRUE or FALSE accordingly.
=IF(AND(logical_test, [value_if_true], [value_if_false]) =IF(AND(logical1, [logical2], ...)
Logical_test (required): The condition you want to test.
Value_if_true (required): The value that you want returned if the result of logical_test is TRUE.
Value_if_false (optional): The value that you want returned if the result of logical_test is FALSE.
IF(OR) FORMULA6:00
The IF function can be used to perform a logical comparison between two values by testing for a condition and returning the result based on whether that condition is TRUE or FALSE. You can also use the OR function, one of the logical functions, in combination with the IF function to determine if any conditions in a test are TRUE or FALSE.
=IF(OR(logical_test, [value_if_true], [value_if_false])
=IF(OR(logical1, [logical2], ...)
Logical_test (required): The condition you want to test.
Value_if_true (required): The value that you want returned if the result of logical_test is TRUE.
Value_if_false (optional): The value that you want returned if the result of logical_test is FALSE.
ABSOLUTE FORMULA3:09
This formula returns the absolute value of a number. The absolute value of a number is the number without its positive or negative integer signs. This can be useful when you are working with debits and credits in a trial balance or general ledger and just need the number to appear in one direction to find the sum of the numbers, or for other purposes e.g. presentations.

=ABS(number)

Number1… (required): The cell containing the number which you want to perform the absolute formula on.
-- ABS(number) formula was used to removed any negative signs from the Company's Trial Balance.
ROUND FORMULA9:17
The round function is used to round numbers to the a whole number or decimal.
=ROUND(number, num_digits)
=ROUNDUP (number, num_digits)
=ROUNDDOWN (number, num_digits)
Number (required): The number that you want to round.
Num_digits (required): The number of digits to which you want to round the number.
i. Round formula was used to make a number with decimal simple by rounding it to the nearest ten.
ii. Roundup formula was used to make a number with decimal point higher or equal to .5 to be rounded to the next ten.
ii. Rounddown formula was used to make a number with decimal point lower or equal to .4 to be rounded to the prior of ten.
NPV FORMULA8:42
The NPV formula is used to determine the present value of future cash flows on an investment and thereby how much money it will return over the term for decision making purposes.
=NPV(rate, value1, [value2], ...)

Rate: The discount rate to be used in the calculation to discount future cash flows
Value: The amount of cash flows that will be returned by the investment

Using simple math, let's understand how the NPV formula works:

NPV = - (initial cash outflow) + (sum of discounted future cash flows) NPV = - (initial cash outflow) + ( (cash inflow year 1) / (1 + discount rate) ) + ( (cash inflow year 2) / (1 + discount rate) )…etc, depending on number of years of future cash inflows
IRR FORMULA20:14
To determine the discount rate which makes the NPV equal to zero. If you calculate the IRR and it is greater than your expected rate of return (discount rate) then you should accept the project as an investment. Higher IRR projects have higher growth potential.

- Rate: In the IRR calculation, the discount rate is not known and is a variable that is being solved for.
- Value: The amount of cash flows that will be returned by the investment. IRR can be calculated in Excel using the XIRR formula.

XIRR (extended internal rate of return) is more accurate than the IRR function in Excel. IRR in Excel considers all time periods equal whereas XIRR allows for specific dates to be considered.
=XIRR(values, dates, [guess])
Values (required): Amount of cash flows returned
Dates (required): The dates the cash flow will be realized
Guess (optional): Guess means the assumption of users about the possible internal rate of return.
NPV VS. IRR FORMULA2:45
To determine the discount rate which makes the NPV equal to zero. If you calculate the IRR and it is greater than your expected rate of return (discount rate) then you should accept the project as an investment. Higher IRR projects have higher growth potential.
- Rate: In the IRR calculation, the discount rate is not known and is a variable that is being solved for.
- Value: The amount of cash flows that will be returned by the investment. IRR can be calculated in Excel using the XIRR formula.
XIRR (extended internal rate of return) is more accurate than the IRR function in Excel. IRR in Excel considers all time periods equal whereas XIRR allows for specific dates to be considered.

=XIRR(values, dates, [guess])

Values (required): Amount of cash flows returned
Dates (required): The dates the cash flow will be realized
Guess (optional): Guess means the assumption of users about the possible internal rate of return.
PMT FORMULA11:40
The PMT formula is typically used to calculate the payment for a loan based on constant payments and a constant interest rate.
=PMT(rate, nper, pv, [fv], [type])
Rate (required): The interest rate for the loan.
Nper (required): The total number of payments for the loan.
PV (required): The present value, or the total amount that a series of future payments is worth now; also known as the principal.
FV (optional): The future value, or a cash balance you want to attain after the last payment is made. If FV is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.
Type (optional): The number 0 (zero) or 1 and indicates when payments are due. 0 (zero) for when "at the end of the period" and 1 (one) for "at the beginning of the period"
PRINCIPAL PAYMENT (PPMT) FORMULA8:01
The PPMT function calculates the principal payment of a given period for an investment or loan based on periodic, constant payments and a constant interest rate.
=PPMT(rate, per, nper, pv, [fv], [type])
Rate (required): This is the interest rate per period.
Per (required): The period for which you want to find the principal and must be in the range 1 to nper.
Nper (required): The total number of payment periods in the annuity.
Pv (required): The present value is the amount the amount that the sum of the future payments is worth today.
Fv (optional): The future value, or a cash balance you want to attain, after the last payment is made. If fv is omitted, it is assumed to be 0.
Type (optional): The number 0 (zero) or 1 and indicates when payments are due. 0 (zero) for when "at the end of the period" and 1 (one) for "at the beginning of the period".
APPLICATION OF PMT, IPMT & PPMT FORMULA14:11
Using the PMT, IPMT and PPMT formula in excel, we can create an amortization table for loan options as an example.
PPMT calculates the principal, IPMT calculates the interest, and PMT calculates the blended payment.
PMT Formula: Calculates the payment for a loan based on constant payments and a constant interest rate.
IPMT Formula: The IPMT formula returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate.
PPMT Formula: The PPMT function calculates the principal payment of a given period for an investment or loan based on periodic, constant payments and a constant interest rate.
IPMT FORMULA6:33
The IPMT formula returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate.
=IPMT(rate, per, nper, pv, [fv], [type])
Rate (required): The interest rate per period
Per (required): The period number for which you want to find the interest.
Nper (required): The total number of payment periods in the annuity.
Pv (required): The present value, or the lump-sum amount that a series of future payments is worth right now. Also, this is the loan amount.
Fv (optional): The future value, or a cash balance after the last payment is made. If fv is omitted, it is assumed to be 0.
Type (optional): The number 0 (zero) or 1 and indicates when payments are due. 0 (zero) for when "at the end of the period" and 1 (one) for "at the beginning of the period"
EFFECT FORMULA6:14
Returns the effective annual interest rate, given the nominal annual interest rate and the number of compounding periods per year.
=EFFECT(nominal_rate, npery)
Nominal_rate (required): The nominal interest rate.
Npery (required): The number of compounding periods per year. The effective annual interest rate is the interest rate that is earned or paid over the life of an investment.
It differs from nominal interest rate because it takes into consideration compounding while the nominal interest rate does not. Therefore effective interest rate is higher than the nominal interest rate.
DEPRECIATION FORMULA8:00
This formula returns the depreciation of an asset for a specified period using the fixed-declining balance method.
=DB(cost, salvage, life, period, [month])
Cost (required): The historical cost of the asset.
Salvage (required): The value of the asset at the end of the depreciation period (salvage value of the asset).
Life (required): The number of periods over which the asset is being depreciated (i.e. this is the useful life of the asset).
Period (required): The period for which you want to calculate the depreciation. Period must use the same units as life. E.g. if you use years for life, you should use years for period.
Month (optional): The number of months in the first year. If month is omitted, it is assumed to be 12 months.
RATE FORMULA8:19
The RATE formula is used to calculate the interest rate per period of an annuity.
=RATE(nper, pmt, pv, [fv], [type], [guess])
Nper (required): The total number of payment periods in an annuity.
Pmt (required): The payment made each period and cannot change over the life of the annuity. This includes principal and interest but no other fees or taxes.
Pv (required): The present value is the total amount that a series of future payments is worth today.
Fv (optional): The future value is the value of the annuity after the last payment is made. If FV is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.
Type (optional): The number 0 (zero) or 1 and indicates when payments are due. 0 (zero) for when "at the end of the period" and 1 (one) for "the beginning of the period".
PV FORMULA7:07
The PV formula is used to calculates the present value of a loan or an investment based on a constant interest rate.
=PV(rate, nper, pmt, [fv], [type])
Rate (required): The interest rate per period.
Nper (required): The total number of payment periods in an annuity.
Pmt (required): The payment made each period and cannot change over the life of the annuity. Typically, pmt includes principal and interest but no other fees or taxes.
FV (optional): The future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0).
Type (optional): The number 0 or 1 and indicates when payments are due (0 end of period, 1 beginning). If type is omitted, it is assumed to be 0.
FUTURE VALUE (FV) FORMULA6:02
The FV formula calculates the future value of an investment based on a constant interest rate and takes into consideration compounding of interest.
=FV(rate, nper, pmt, [pv], [type])
Rate (required): The interest rate per period.
Nper (required): The total number of payments for the loan.
Pmt (required): The payment amount made each period; it cannot change over the life of the annuity. Typically, pmt contains principal and interest but no other fees or taxes.
Pv (optional): The present value, or the lump-sum amount that a series of future payments is worth right now. If pv is omitted, it is assumed to be 0 (zero), and you must include the pmt argument.
Type (optional): Optional. The number 0 or 1 and indicates when payments are due (0 end of period, 1 beginning). If type is omitted, it is assumed to be 0.
SLOPE FORMULA7:33
The SLOPE formula is a statistical function in excel that can be used to return the slope of the linear regression line through data points. The SLOPE formula is the vertical distance divided by the horizontal distance between any two points on the line, which is the rate of change along the regression line. This gives you the rise (y axis) over run (x axis).
=SLOPE(known_ys, known_xs)
Known_y's (required) An array or cell range of numeric values for the y axis data points.
Known_x's (required) An array or cell range of numeric values for the x axis data points.

In mathematical terms, slope = y2 - y1 / x2 - x1
VLOOKUP FORMULA10:52
The VLOOKUP function is typically used when you have a specific item you'd like to lookup in a table and there is only one of that item in the table i.e. it is unique.
=VLOOKUP(lookup_value, table,_array, row_index_number, [range_lookup])
Lookup value: Cell which you would like to reference and lookup in the table.
Table array: This is the range/array of data from which we want to lookup our lookup value. Note that the lookup value must be in the left most part of the table array.
Col_Index_num: It is the column number from where we want to return the result.
Range_lookup: This refers to either wanting an exact match or close match.
HLOOKUP FORMULA13:28
The VLOOKUP function is typically used when you have a specific item you'd like to lookup in a table and there is only one of that item in the table i.e. it is unique.
=VLOOKUP(lookup_value, table,_array, row_index_number, [range_lookup])
Lookup value: Cell which you would like to reference and lookup in the table.
Table array: This is the range/array of data from which we want to lookup our lookup value. Note that the lookup value must be in the left most part of the table array.
Col_Index_num: It is the column number from where we want to return the result.
Range_lookup: This refers to either wanting an exact match or close match.
OFFSET FORMULA13:12
The OFFSET formula is used to return a range that is a specified number of rows and columns from a reference cell or range.
=OFFSET(reference, rows, cols, [height], [width])
Reference: The starting point reference. This is a cell reference.
Rows: The number of rows to offset below or above (if a negative value is used) the starting reference.
Cols: The number of columns to offset to the right or left (if a negative value is used) of the starting reference.
Height: [optional] The height in rows of the returned reference.
Width: [optional] The width in columns of the returned reference. Note that a negative value can be used in the formula. If a negative is used, then instead of having the OFFSET lookup a value below (row) or to the right (column), a negative value will lookup above (row) or to the left (column).
LEFT, RIGHT FORMULA8:16
The Left, right formula displays a specified number of characters from the left/right hand side of a piece of text. They are useful if you want to extract part of the contents of a cell.
=LEFT(text, [num_chars]) =RIGHT(text, [num_chars])
Text: Original text from which we need the characters
[num_chars]: Number of characters required.
COLUMN FORMULA1:58
The Column formula is used to find out the number of a column where a reference cell is located in the worksheet.
=COLUMN([reference])
Reference: The cell which is selected to find out the position of it.
ROW FORMULA2:11
The row formula is used to find out the number of a row where a reference cell is located in the worksheet.
=ROW([reference])
Reference: The cell which is selected to find out the position of it.
COLUMNS FORMULA2:15
The columns formula is used to count the number of columns which are selected in a range.
=COLUMNS([array])
Array: The cells which are selected to find out the number of columns within the selected area.
ROWS FORMULA1:36
The rows formula is used to count the number of rows which are selected in a range.
=ROWS(array)
Array: The cells which are selected to find out the number of rows within the selected area.
FORMULA TEXT FORMULA3:48
The formula text function is used to return a formula as text.
=FORMULATEXT(reference)
Reference: The cell which contains formula for which we want to retrieve the text.
COUNTBLANK FORMULA1:54
The COUNTBLANK formula is used to Count the blank cells in the selected range.
=COUNTBLANK(range)
Range: The range from which you want to count the blank cells.
DMIN FORMULA5:19
The DMIN formula is used to find the smallest number in the database that matches the specific condition set.
=DMIN(database, field, criteria)
Database: Range of data within which we want to perform the analysis.
Field: The item which we want to find within the database (e.g. price, category, etc.).
Criteria: The given criteria that have to be matched with.
DMAX FORMULA4:23
Find the largest number in the database that matches the specific condition set.
=DMAX(database, field, criteria)
Database: Range of data within which we want to perform the analysis.
Field: The item which we want to find within the database (e.g. price, category, etc.).
Criteria: The given criteria that have to be matched with.
FIXED FORMULA3:44
The FIXED formula is used when you want to modify the value of a cell to remove the decimals or commas.
=FIXED(number, [decimals], [no_commas])
Number : The value or cell which you want to fix.
Decimals : The number of decimal you want to fix.
No commas : Refers to whether you want commas in the value or not (i.e. nothing or FALSE = with comma, and TRUE = no comma).
FIND FORMULA6:09
The Find formula is used to to find any text from a selected range of texts. The FIND formula will return the relative position of where the text being searched is located in the cell it is being searched in.
=FIND(find_text, within_text, [start_num])
Find_text: The text which we are attempting to find.
Within_text : The text which we want to search.
Start_num : The numerical position of the text which we are beginning to start the search. If omitted start_num=1.
RAND FORMULA3:00
The RAND formula is used to insert random numeric values which are greater than 0 but less than 1.
This can be used if you are trying to insert random percentages in a worksheet because decimals in excel can also be treated as percentages. Note that every time you refresh the screen, the data will randomize.
Therefore, if you are looking to randomize data once and then store it then the best practice is to randomize the data, copy it, and paste special the values somewhere else in the worksheet.
=RAND()
RAND: There is no required value to use in this formula. You just need to type =RAND() to use the formula.
RANDBETWEEN FORMULA2:43
The RANDBETWEEN formula is used to insert random numeric values according to your specification between a bottom and top threshold. Note that every time you refresh the screen, the data will randomize.
Therefore, if you are looking to randomize data once and then store it then the best practice is to randomize the data, copy it, and paste special the values somewhere else in the worksheet.
=RANDBETWEEN(bottom, top)
Bottom: The minimum value of our requirement.
Top: The maximum value of our requirement.
TRIM FORMULA2:15
The TRIM formula is used to remove all unnecessary and unwanted spaces from the selected text.
=TRIM(text)
Text: The cell that containing text which you want to modify.
RANK FORMULA4:06
The RANK formula is normally used to show ranking (high to low, or low to high) of any set of values.
=RANK(number, reference, [order])
Number: It means the number for which you want find the rank. You can reference a cell here which contains a number.
Reference: It is an array of, or reference to, a list of numbers. The reference must be numeric.
Order: Order means the order about ranking either it will be highest to lowest ("0", descending), or lowest to highest ("1", ascending).
CHOOSE FORMULA6:01
The CHOOSE formula is used to index a number and return a desired comment based on the number.
=CHOOSE(index_num, value1, [value2], ...)
Index number: This specifies which value argument is selected. It must be a number between 1 and 254, or a formula or a reference to a number between 1 and 254.
Value 1, Value 2…: Value 1, value 2, etc can be cell references, defined names, formulas, functions, or text arguments from which CHOOSE selects. They must be in sequential order.
CORREL FORMULA6:03
The CORREL formula is used to find the coefficient correlation between two data sets.
=CORREL(array1, array2) In the syntax the arguments refer to:
Array 1: The first set of data for analysis, for example the set of study hours.
Array 2: The second set of data for analysis, for example the set of test result percentages.
FORECAST FORMULA5:47
The FORECAST formula is used to create a prediction about an item (e.g. sales, profit, etc.) by using previous results as a basis. There is a limitation in the simplicity of this forecast formula.
This formula provides a prediction based on historical results and trends. It cannot be taken as an accurate forecast if other variables may influence the outcome.
=FORECAST(x, known_ys, known_xs)
x: It is the data point for which you want to predict a value and must be numeric value.
known_ys: It is the dependant array or range of numeric data.
known_xs: It is the independent array or range of numeric data. The variance of known_xs must not be zero.
FREQUENCY FORMULA4:26
The FREQUENCY formula in excel is used to count the number of times an item appears in an array. It is useful to count the number of times a numerical value appears in a range. An alternative to this is to use a COUNTIFS function.
=FREQUENCY(data_array, bins_array)
data_array: This is an array of data where we want count frequencies. Note that blanks and text are ignored.
bin_array: This is an array of data where we want to group the values in data_array.
ISNUMBER FORMULA3:45
The ISNUMBER formula is used to determine whether a cell contains a number or not. This can be useful if you want to write conditional logic based on a column which contains data but you want to only apply the formula to cells which have numbers.
=ISNUMBER(value)
value: This is the cell in which we want to analyze for whether it is a number or not.
CONVERT COLUMN TO NUMBER FORMULA4:22
This formula is used to find out the number of any column letter (e.g. A,B,C,D, etc).
This can have many purposes. For instance, in the OFFSET formula, we make reference to a specific cell and then the lookup to that cell can be offset by the number of rows or columns from it. If you use the COLUMN formula, you can count how many columns away a particular cell is.
There are two ways in which we can use the COLUMN formula:
1) =COLUMN(REFERENCE CELL) This formula will return the column number of the reference cell.
2) =COLUMN(INDIRECT(ref_text, [a1])) This formula will allow you enter a column letter in the ref_text part of the formula to return the column number of that column letter.
COLUMN: To get the column number.
INDIRECT: To find the ref_text.
ref_text: Cell containing the column letter for which we are finding it's number.
a1: Logical value that helps to specify the reference.
STRIP NON-NUMERIC CHARACTERS FORMULA2:47
There is a string of formula that can be written to strip out only the non-numerical values from a cell and only return the numerical values. This is useful in situations where you need to calculate the sum of data contained in a cell which has non-numeric values or if you need only numeric portions of a text string. e.g. for serial numbers or other purposes.
=TEXTJOIN("",TRUE,IFERROR(MID(A1,ROW(INDIRECT("1:100")),1)+0,""))
In the above formula, every item except A1 will be same for every situation.
Instead of typing A1 you would select the cell containing the value you would like to strip. This formula uses the MID formula which works from the inside out to remove non-numeric characters. The ROW and INDIRECT functions act to reference an array of the numbers between 1 and 100.
SUM OF BOTTON N VALUES FORMULA4:41
The sum of bottom N values formula is used to calculate the sum of the smallest or largest set of numbers in an array.
=SUM(SMALL(array,{1,2,n})) or SUMPRODUCT(SMALL(array,{1,2,n})) -- both of these formula achieve the same result.
=SUM(LARGE(array,{1,2,n})) or SUMPRODUCT(LARGE(array,{1,2,n})) -- both of these formula achieve the same result.
1,2,n: The chronological order of a particulars number of values you'd like to calculate the summation of.
array: The set of values from which we want to find out the SUM.
SMALL: Find the smallest values from the array.
LARGE: Find the largest values from the array.
SUM or SUMPRODUCT: Make the total of selected orders of values.

Line Chart4:34
Line charts are used to display trends in data over a particular time period. Line charts are best used when the data is arranged in such a way that text labels, dates or a few numeric labels are on the horizontal axis.
Steps:
1. Select the data range including headers.
2. On the Insert tab, in the Charts group, click on the Line symbol.
3. Click the type of chart you want, in this example we selected Line with Markers.
Column Chart4:48
Column charts are used to compare values between categories by using vertical bars for presentation. Column charts are effective when there are two characteristics to support the analysis such as the timeframe of the data and the category of the data.
1. Select the data range of the entire table above.
2. On the "Insert" tab, in the Charts group, click on the "Column symbol".
3. Click the type of chart you want, in this example we selected "Clustered Column" which is best suited to our requirement, as we want a comparative view of vehicle sales by vehicle category.
Bar Chart5:05
A bar chart is the horizontal version of a column chart. Using a bar chart is recommended when we have large text labels.
1. Select the data range of the table above.
2. On the Insert tab, in the Charts group, click on the Column symbol.
3. Click the type of chart you want, in this example we selected Clustered bar.
Timeline Chart6:37
A timeline chart shows the milestones of a project in a graphical presentation. Excel doesn't yet offer timeline as a chart type. Instead, in Excel we are able to create a timeline chart by customizing the scattered chart type. To create a timeline from a given dataset we need to execute the steps noted below.
Steps:
1. Select the data range containing date as X-axis and plotting point as Y-axis.
2. On the insert tab, in the Charts group, click the scatter chart symbol.
3. Click "Scatter chart".
4. The result is a scatter chart, which we'll customize to create a timeline for the project.
5. Enter the title by clicking on the Chart Title and typing a new name.
6. Click on the + sign adjacent to chart and un-check the gridlines option.
7. In the same menu, go to Axes and un-check the primary vertical axis, as we don't want the plotting points to show on our timeline. Also check the "Data Labels" & "Error Bars".
8. We don't want the horizontal error bar lines. So we remove them by following these steps. Right click on the dates (x-axis) and select format axis. In the Format Axis menu, open the dropdown options on "Axis Options" & Select "Series 1 X Error Bars".
9. Instead of showing the plotting points as milestones, we want actual text of milestone to show in our timeline. In order to do this: Go to Axis Options again and select "Series 1 Data Labels". Click on the Columns symbol. Uncheck Y value.
10. Customize the colour scheme and date range to make timeline look more professional. This can be achieved by double clicking on the bars and using a pre-set template from the top menu which appears under chart design. Additional customization can be done with the right navigation pane.
Check value from cells and give the desired range.
Timeline Gantt Chart10:30
A Gantt chart breakdowns the structure of a project into different milestones and presents in a graphical form, including the the start and finish dates. Excel doesn't yet offer Gantt as a chart type. In Excel, we are able to create a Gantt chart by customizing the stack bar chart type.
Steps:
1. Select the data range, which is C16:E24 in our examples.
2. On the insert tab, in the Charts group, click the Column symbol.
3. Click "Stacked Bar".
4. The result is a bar chart, that we'll customize to get a Gantt chart.
5. Enter the title by clicking on the Chart Title and renaming it.
6. Click the legend at the bottom and press "Delete".
7. The milestones are in reverse order. To correct the order, right click the milestones on the chart, click Format Axis and check "Categories in reverse order".
Win/Loss Sparklines Chart4:30
A win loss chart shows positive and negative values with different colours in single cell. The win loss chart assists with the visualization of trends in data. It shows an upward facing marker for positive values and a downward facing marker for negative values. Zero values get no marker.
1. On the Insert tab, in the sparklines group, click on the win/loss symbol.
2. A "Create Sparklines" dialog box is open. For the data range, select the data from the table that you'd like to create the chart based on. For the location range, choose the cells where you'd like the chart to be placed.
Sales Funnel Chart12:36
The sales funnel chart is a common chart to illustrate sales pipeline and it's conversion metrics from initial lead to the closed/won stage.
Steps:
1. Insert a column named 'Space' between Sales stage and Quantity in order to get the funnel shape on chart.
Make the space formula equal to: (Funnel high end quantity - funnel reference quantity) / 2.
2. Select the entire table and insert a Stacked Bar Chart.
3. Select the categories, then select format axis, and convert them into reverse order.
4. Select the Space (blue) portion in the chart, and change the colour into "No fill".
5. Select the data series, and decrease the Gap Width to 20%.
6. Make, copy, and paste four corner flowchart shape of different colour in the first five data series and a reverse triangle flowchart shape in the last data series.
Geo Heat Map7:04
Heat maps are available for different countries. It provides a good demonstration of where the concentration of sales data is geographically located.
Steps:
1. Navigate to "Developer", "Add-Ins", then install the "Geographic Heat Map".
2. Once installed go to the Add-In and click "Add" to bring the heatmap i-frame into the Excel.
3. Click "Get Started".
4. Configure settings by choosing the data and map type (e.g. USA). Select a colour scheme.
Dashboards1:13:43
Learn to build integrated dashboards in Excel by weaving pivot tables, pivot charts, slicers, and maps into a centralized KPI view, with weekly or monthly updates.

Pivot Tables9:07
Pivot tables are useful when you have information in a table and you'd like to quickly rearrange the data without the use of formula based on filters or present it differently. Pivot tables can provide a flexible analysis. However it does not automatically refresh and if you modify the source table data it may break.
Steps:
1. Click any blank cell in the worksheet.
2. From the INSERT tab click on "Pivot Table".
3. Then organize the data as you'd like it presented.
- In the Pivot table field list, there are 4 boxes.
Report filter: Report filter is used to display conveniently a subset of data in a PivotTable report or PivotChart report.
Row label: Fields put in the row label are listed on the left column of the table.
Column label: Fields put in the column label have their values listed across the top row of the table.
Values: Fields put here are summarized and added in the table.
Slicers4:18
Slicers come in handy after you've created a PivotTable and want to further analyze the data with ease. For example, if you are depending on the results of the PivotTable for ongoing analysis, it would be beneficial to setup a dashboard which contained a slicer that enabled the flexibility to view the results of different scenarios.
Steps:
1. Click anywhere in the Pivot table report for which you want to create a slicer.
2. Click "PivotTable Analyze" in the top nav bar then click "Insert Slicer"
3. Tick the field that you want to show on the slicer.
4. When the slicer is open, there is an option to multi-select the options.
Pivot Tables - Combining Data From Multiple Sheets9:42
If you are in a situation where you have data tables spread across multiple sheets and want to run a Pivot Table on the data there is a way to do so if each of the tables have a common denominator.
Follow these steps:
1. Select the data on each tab and create a table in each sheet.
2. Select any of the tables, and go to "Insert" on Pivot Table. Check on "Add this data to the Data Model".
3. Go to "Analyze" then click "Relationship".
4. Select "New" from Manage Relationships.
5. Select "tblSales" on Table, and "tblProducts" on Related Table. Then, Select "Product ID" on both Column and Related Column.
6. Select "New" again.
7. Select "tblSales" on Table, and "tblCustomers" on Related Table. Then, Select "Customer ID" on both Column and Related Column.
8. Once the Pivot Table is created, select "All" from PivotTable Fields.
9. Put "Product Type" from tblProducts in column, "Gender" from tblCustomers into Rows, and "Quantity" from tblSales into Values.
Pivot Tables - Create Report Filter Pages7:29
This function available for pivot tables allows you to automatically create separate tabs summarizing the pivot table data and split up by a specific category.
Steps:
1. Select the table, and go to "Insert" then "PivotTable".
2. Go to "PivotTable Options", and select the classic Pivot Table layout from "Display" option.
3. Put "Designation" into Rows, "Department" into Filter, and "present Basic", "House Rent", "Medical","Transport", "Special", and "Gross Pay" into Values.
4. Now, go to "Analyze" on "PivotTable". Click the down icon of "Option", and select the "Show Report Filter Pages…."
5. Excel will create individual sheet for each department automatically after selecting "Show Report Filter Pages…".

Table Of Contents With Macro5:02
If you have multiple tabs in a workbook and want to create an easy reference to each of them, a top level table of contents tab is appropriate. A table of contents tab has a summary of all the sheets and each sheet name is clickable to bring the user to that specific tab.
Steps:
1. Open any excel sheet and click Alt+F11, or go to "Developer" then "Visual Basic".
2. Save as your excel file as Macro enable excel file.
3. Go to "Insert" then "Module".
4. Copy the text from the below, and Paste it on Macro Visual Basic notepad.
Sub CreateTOC() Dim i As Byte Const SheetName = "Table of Contents" With Application .ScreenUpdating = False .DisplayAlerts = False End With If Sheets(1).Name = SheetName Then Sheets(SheetName).Delete End If Sheets.Add Before:=Sheets(1) Sheets(1).Name = SheetName Range("B2").Value = SheetName With Range("B2").Font .Name = "Calibri" .Size = 14 .Underline = xlUnderlineStyleSingle .Bold = True End With Range("B4").Select 'Loop through each sheet and create a table of contents using each sheet name For i = 2 To Sheets.Count ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=Sheets(i).Name & "!A1", TextToDisplay:=i - 1 & ". " & Sheets(i).Name ActiveCell.Offset(2, 0).Select Next Range("B4:B" & ActiveCell.Row).Font.Underline = xlUnderlineStyleNone With Application .ScreenUpdating = True .DisplayAlerts = True End With End Sub
5. Press F5 to Run the task.
6. The table of content is ready. Let's check by clicking on any of the contents whether it works or not.
Automate Database Reports (Formula Method)7:33
If you export database reports in excel for your everyday reporting purposes e.g. preparing inventory reports or conducting sales analysis, there is a certain repeated steps that you have to always follow to prepare the report. If you have identified the steps that are repeated all the time, you can be easily automate them to save time. In this scenario we have a sample database file from which we want to extract total units sold against each representative. We will take the short cut route in this lesson by pasting the code in the VBA editor. You can refer to Lesson "Automate Data Base Report with VBA-Recording Macro" which will take you to step by step procedure to record your own Macro.
Automate Database Reports (Record Macro)12:55
If you export database reports in excel for your everyday reporting purposes e.g. preparing inventory reports or conducting sales analysis, there is a certain repeated steps that you have to always follow to prepare the report. If you have identified the steps that are repeated all the time, you can be easily automate them to save time. In this scenario we have a sample database file from which we want to extract total units sold against each representative. Here we will use "Record Macro" technique to generate a VBA code based on our instructions on the sample database workbook. The code generated can be modified to suit our specific needs to automate the repetitive processes. You can also take the short cut route by pasting the code provided in Lesson "Automate Data Base Report with VBA-Formula Method".
Remove Foreign Language Characters7:57
When working for a Multinational Company, there are instances where Excel reports are shared that contain both English and foreign language text. You are often required to remove the foreign text if you want to further work on these Excel files. To do that, you have to manually select each cell and remove the foreign text to be able to use the reports. This makes the whole process lengthy. We can remove the foreign text using the VBA code provided below. We have also provided explanation of what the code Syntax means and how the Excel uses the code to differentiate between English and Foreign language text. We will work on a sample file to show the results of the code. We are using a sample Chinese menu containing both English and Chinese Text and we want the sheet to be updated to show only the English Text.
Combine Multiple Workbooks Into A Single Workbook8:14
Instead of manually moving every workbook sheet in a master workbook, we can simply use the VBA code and the procedure provided in this lesson to combine all workbooks into a single workbook. We have also added another rule in the code that renames the worksheets of the workbooks combined with their relevant file name. This can save a lot of time. At the end, we have explained how the code works in order to complete the task instructed.

Requirements

No specific requirements. Our lessons are taught with step by step instructions.

Description

15+ Hours of Ultimate Microsoft Excel Training from beginner to advanced. Take this Excel course and become proficient in Excel overnight. Taught hands on from a Silicon Valley CFO with advanced Excel financial modeling knowledge. Over 115+ lessons and examples are taught in this course to provide a practical and hands-on approach.

In this course you'll learn 100+ navigation shortcuts and formatting tips, how to write over 60+ formula in Excel, use functions, learn Excel macros, learn VBA, charts and graphs, and develop beautiful and advanced dashboards. As part of this course you will receive access to the full course materials including over 7 complete workbooks with over 115+ Excel worksheet lessons which accompany each lesson and contain the shortcuts, formula, and course explanations so that you can follow along on your own time. Each course section has it's own exercises so that you can test your knowledge.

Take this course if you're looking for a complete course with end-to-end Excel training. You can pick and choose which lessons you'd like to watch as each lesson contains its own worksheet and video to follow along.

There are 6 parts and a total of 8 workbooks as follows:

Excel Course Part 1 - Navigation & Formatting

Excel Course Part 1.1 - Navigation & Formatting - Data Set Example

Excel Course Part 2 - Functions

Excel Course Part 3 - Formula

Excel Course Part 4 - Charts & Graphs

Excel Course Part 4.1 - Charts & Graphs - Dashboards

Excel Course Part 5 - Pivot Tables

Excel Course Part 6 - Macros

Accelerate your career with our Ultimate Excel Training course today!

Who this course is for:

Business Professionals in Finance, Accounting, Investment Banking, and FP&A Roles
Finance and Accounting Professionals and Students
Entrepreneurs
Anyone looking to accelerate their career by becoming a financial modeling pro in Excel

Ultimate Excel Training Course - Intro to Advanced Pro

What you'll learn

Explore related topics

Course content

NAVIGATION AND FORMATTING TIPS IN EXCEL38 lectures • 3hr 10min

FUNCTIONS IN EXCEL8 lectures • 1hr 46min

FORMULA IN EXCEL60 lectures • 6hr 57min

CHARTS AND GRAPHS IN EXCEL9 lectures • 2hr 9min

PIVOT TABLES IN EXCEL4 lectures • 31min

MACROS IN EXCEL5 lectures • 42min

Requirements

Description

Who this course is for: