Home
Videos uploaded by user “Leila Gharani”
How to use Excel Index Match (the right way)
 
11:32
Quickly learn all you need to know about INDEX & MATCH to get a quick start. Complete Excel Dashboard course: https://courses.xelplus.com/p/professional-excel-dashboards Check out my other courses below. Download workbook here: http://www.xelplus.com/excel-index-and-match How to do Index Match in Excel: The basics. Watch this video tutorial to find out how you can use Index & Match for more complex lookup problems. This video shows you how Index Match works with simple and to the point explanation. It first shows you how Index works on it's own and then how Match works and then puts the two Excel functions together. Why index match is better than vlookup & why use index match instead of vlookup? Here are 3 reasons why Excel experts generally substitute VLOOKUP with INDEX and MATCH. 1. Unlike VLOOKUP, which searches only to the right, INDEX and MATCH can look in both directions – left and right. 2. INDEX & MATCH can perform two-way lookups by both looking along the rows and along the columns to find the intersection within a matrix. 3. INDEX & MATCH is less prone to errors. Assume you have a VLOOKUP where the final value you want returned is in column N. Your lookup value is in column A. You need to highlight the entire A to N range and then provide your index number to be 14. If you happen to delete any of the in-between columns, you would have to update that index number. You don’t need to worry about this when you use INDEX & MATCH. All in all, INDEX and MATCH is more flexible than VLOOKUP. Here are some of the reasons why Excel experts generally substitute VLOOKUP with INDEX and MATCH. Note: Index match is not case sensitive Index explained: The first argument of INDEX is to give it an array. This array (range) should include your answer. You then need to specify how many rows to go down and how many columns to move to find the correct value. You cannot move outside the INDEX range. The syntax of INDEX is: • The range where the return value resides. • Number of rows to move down (the row index) • Number of columns to move to the right (the column Index). This argument is optional. If you only have one column, you can leave the column argument empty, otherwise, you need to specify the number of columns to move over, in the range. The MATCH function’s syntax is as follows: • Like VLOOKUP, Match needs a lookup (target) value. The user can reference a cell or directly type the value into the formula. • Lookup_array: The “list” (range) where the return values are located. • Match_type: 0 for an “exact” match. Index MATCH used together: When INDEX and MATCH are used together, the MATCH function finds the look up value’s row / column index and then hands this value off to the INDEX function to get the lookup value. ★ My Online Excel Courses ► https://courses.xelplus.com/ ✉ Subscribe & get my TOP 10 Excel formulas e-book for free https://www.xelplus.com/free-ebook/ EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/ Get Office 365: https://microsoft.msafflnk.net/15OEg Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593 GEAR Camera: https://amzn.to/2FLiFho Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Microphone: https://amzn.to/2DVKstA Lights: http://amzn.to/2eJKg1U More resources on my Amazon page: https://www.amazon.com/shop/leilagharani Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support! #MsExcel
Views: 444393 Leila Gharani
Excel VLOOKUP: Basics of VLOOKUP and HLOOKUP explained with examples
 
10:37
Find out how to do a VLOOKUP in Excel and for what situations you can use Excel's Vlookup and Hlookup formulas for. Download the free Excel Vlookup workbook here: http://www.xelplus.com/basics-vlookup-hlookup/ Get the full course: https://courses.xelplus.com/ The Excel VLOOKUP formula can search for a specific category or value in an Excel data table and return the “opposing” value from adjacent columns. HLOOKUP function works exactly in the same way except that it looks horizontally rather than vertically. “V” in VLOOKUP refers to vertical and “H” in HLOOKUP refers to horizontal. If the data in your table has column headers, use the VLOOKUP function. If the data table has row headers and you need to search horizontally across the rows for a “match” then use HLOOKUP formula. Vlookup formulas do not need to have sorted lists. If your Excel table is unsorted make sure you look for an exact match by selecting "false" in the last argument of Vlookup. One major limitation of VLOOKUP is that it cannot look to the left. The values to lookup must always be on the left-most column of the range and the values to return must be on the right hand side. For two-way lookups - If you need to look up values across rows and also columns or multiple rows and columns the best function to use is the Index and Match function. Watch this video to learn more: https://youtu.be/F264FpBDX28 ★ My Online Excel Courses ► https://courses.xelplus.com/ ✉ Subscribe & get my TOP 10 Excel formulas e-book for free https://www.xelplus.com/free-ebook/ EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/ Get Office 365: https://microsoft.msafflnk.net/15OEg Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593 GEAR Camera: https://amzn.to/2FLiFho Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Microphone: https://amzn.to/2DVKstA Lights: http://amzn.to/2eJKg1U More resources on my Amazon page: https://www.amazon.com/shop/leilagharani Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support! #MsExcel
Views: 465662 Leila Gharani
Excel Dynamic Chart with Drop down List (column graph with average line)
 
08:09
Learn how to create an interactive chart in Excel that switches views depending on the selection from the drop-down list. In addition to creating dynamic chart ranges, I also show you how to create combo or combination charts in Excel. In this specific example, I show you how to add a horizontal average line to your column chart. This makes it simple to compare the values of the bars not just with one another but also with the average. The key to dynamic charts is to create a data preparation table that sits between your raw data and your chart. Smart Excel formulas help you do this dynamically. In this example, I use the Index and Match formula for data preparation. 🡻 Download the workbook here: http://www.xelplus.com/excel-dynamic-chart-with-drop-down/ Links to related videos: Index Match Basics: https://youtu.be/F264FpBDX28 Excel Charts Basics: https://youtu.be/DAU0qqh_I-A ★ My Online Excel Courses ► https://courses.xelplus.com/ ✉ Subscribe & get my TOP 10 Excel formulas e-book for free https://www.xelplus.com/free-ebook/ EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/ Get Office 365: https://microsoft.msafflnk.net/15OEg Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593 GEAR Camera: https://amzn.to/2FLiFho Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Microphone: https://amzn.to/2DVKstA Lights: http://amzn.to/2eJKg1U Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support! #MsExcel
Views: 58294 Leila Gharani
Excel Picture Lookup: 5 easy steps for dynamic images
 
09:43
Learn to quickly lookup different pictures based on the value of a cell. Some people call this VLOOKUP on images. You can use this technique for company logos, employee pictures, flags - basically anywhere where you need to show different images based on the value of a cell. You'll also learn a few other techniques here. For example: 1. How to work with Excel's picture tools (Crop images, align etc) 2. How to use Name Manager to assign names to cells and how to do it quickly with "Create from Selection" option 3. A little bit about the INDIRECT function (find out more with the below link) 4. How to link pictures to cells 🡻 Download the workbook here 🡻 The steps are explained in 5 simple steps in this blog post: http://www.xelplus.com/excel-lookup-on-pictures/ Scroll down to the bottom to get the Excel file. ★ Links to related videos: ★ World Cup 2018 Sweepstake (with free template link): https://youtu.be/a2IXn1FQU3A INDIRECT function: https://youtu.be/GUClkvJ7Gag Data validation basics: https://youtu.be/FRiFfKb_B_A Custom Data validation: https://youtu.be/bDXQy60BcT4 ★ My Online Excel Courses ★ Courses ► https://courses.xelplus.com/ ✉ Subscribe & get my TOP 10 Excel formulas e-book for free https://www.xelplus.com/free-ebook/ EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/ Get Office 365: https://microsoft.msafflnk.net/15OEg Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593 GEAR Camera: https://amzn.to/2FLiFho Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Microphone: https://amzn.to/2DVKstA Lights: http://amzn.to/2eJKg1U Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support! #MsExcel
Views: 50187 Leila Gharani
Excel Dynamic Arrays (How they will change EVERYTHING!)
 
07:01
Excel Dynamic Array formulas are new to Excel Office 365 (Insider fast for now - GA to come). After this, everything will change! Complex tasks will be done with simple Excel formulas. Everything you wished Excel could do, can now be done! In this video, I take two of my popular videos which use traditional Excel complex formulas and use Dynamic array formulas instead. I also use a timer to test the time needed for the new formulas and compare it to the other videos. Here's what happens: We create a dynamic dependent drop-down list based on a messy data set. The first list includes unique values from cells and the second dependent data validation list filters the values to the ones that belong to the category selected from the first list. I also make sure the list is dynamic so if we add more data to the table, the drop-down list will automatically update (here we use the new dynamic array spill range reference #) I also use the sort function to get sorted names in the drop-down list. We use 3 Excel dynamic array functions: Unique, Sort & filter. LINKS to related videos: Excel: Extract unique items for dynamic data validation drop down list: https://youtu.be/7fYlWeMQ6L8 Excel Find Multiple Matches & Dependent Drop Down List (Unsorted Tabular Data Set): https://youtu.be/gu4xJWAIal8 Digital Ticking Clock from DrLex1: https://youtu.be/Lsq0FiXjGHg ★ My Online Excel Courses ★ Courses ► https://courses.xelplus.com/ ► ✉ Subscribe & get my TOP 10 Excel formulas e-book for free https://www.xelplus.com/free-ebook/ ★ Excel Resources I Recommend https://www.xelplus.com/resources/ Get Office 365: https://microsoft.msafflnk.net/15OEg Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593 GEAR Camera: https://amzn.to/2FLiFho Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Microphone: https://amzn.to/2DVKstA Lights: http://amzn.to/2eJKg1U Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support! #MsExcel
Views: 111329 Leila Gharani
Excel Charts: Total Values for Stacked Charts in Excel
 
05:01
Download the workbook here: http://bit.ly/2pnDt5F Learn how to add total values to stacked charts in Excel. Stacked charts are great for when you want to compare different categories with one another and also show how much each category contributes to the whole. The problem is that you can show the data labels for each of the stacks, but you cannot immediately show the total value of all the stacks on top of each bar. To do this, you need to follow the steps I show in the video. ★ My Online Excel Courses ► https://courses.xelplus.com/ ✉ Subscribe & get my TOP 10 Excel formulas e-book for free https://www.xelplus.com/free-ebook/ EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/ Get Office 365: https://microsoft.msafflnk.net/15OEg Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593 GEAR Camera: https://amzn.to/2FLiFho Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Microphone: https://amzn.to/2DVKstA Lights: http://amzn.to/2eJKg1U Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support! #MsExcel
Views: 22019 Leila Gharani
3 Ways to Transpose Excel Data (Rotate data from Horizontal to Vertical or Vice Versa)
 
04:51
Check out 3 ways to Transpose your data in Excel. These are useful when you want to switch the orientation of your data from columns to rows or vice versa. I show you how to: 1. Use a static approach - copy and paste as transpose 2. Use the Transpose function to rotate data 3. Use a simple formula trick to transpose - this way you can transpose without an array formula and also transpose without copying and pasting. Note: Once you get dynamic arrays, writing the Transpose function becomes super simple. You no longer need to highlight the result area first, nor press control + shift + enter. DOWNLOAD the workbook: https://www.xelplus.com/transpose-excel-data/ ★ Links to related videos: Dynamic Arrays video: https://youtu.be/2USJsIyIzvo ★ My Online Excel Courses ★ Courses ► https://courses.xelplus.com/ ✉ Subscribe & get my TOP 10 Excel formulas e-book for free https://www.xelplus.com/free-ebook/ ★ Excel Resources I Recommend https://www.xelplus.com/resources/ Get Office 365: https://microsoft.msafflnk.net/15OEg Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593 GEAR Camera: https://amzn.to/2FLiFho Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Microphone: https://amzn.to/2DVKstA Lights: http://amzn.to/2eJKg1U Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support! #MsExcel
Views: 17864 Leila Gharani
Excel OFFSET Function for Dynamic Calculations - Explained in Simple Steps
 
14:15
OFFSET tells Excel to “fetch” a cell location (address) from within a data range. Download the Workbook here: http://www.xelplus.com/excel-offset-function-for-dynamic-calculations/ Get the full course: https://courses.xelplus.com/ Excel OFFSET Function Tutorial Summary: The offset formula looks like: =OFFSET(starting point, rows to move, columns to move, height, width) Here are practical uses of OFFSET: • Dynamic Calculations: The OFFSET formula can be included in other Excel functions to get dynamic ranges. For example, calculating the average sales of the last 3 months based on a selection • Flexible Chart Ranges: In conjunction with charts it can be used in the NAME manager to add flexibility to chart data. The OFFSET function behaves like an Excel table where the data range automatically expands and contracts when chart data is updated. • Dynamic dashboards: It can be used together with Excel’s Form Options to allow for dynamic analysis and reports based on user-selection. You can tell the OFFSET function to return one cell if you specify height and width as 1. It can return an array of cells when the height and width are greater than 1. Height and/or width CANNOT be zero, because you either need 1 cell or a range of cells. In the illustration above, the height and width are 2. This video shows how you can use the Excel OFFSET function to calculate the average value for the last 6 months. You will also learn how to use the OFFSET formula to calculate the average value based on the date selected by the user. (You will see formula combinations such as OFFSET, COUNTA and MATCH functions) In general Excel OFFSET function is an interesting formula and it’s one that can do so much more than meets the eye. It can used inside many formulas to provide them with flexible ranges instead of fixed ranges. This makes it a perfect candidate for dynamic reports and dashboards. ★ My Online Excel Courses ► https://courses.xelplus.com/ ✉ Subscribe & get my TOP 10 Excel formulas e-book for free https://www.xelplus.com/free-ebook/ EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/ More resources on my Amazon page: https://www.amazon.com/shop/leilagharani
Views: 138859 Leila Gharani
Excel Goal Seek Explained in Simple Steps
 
05:55
Curious what goal seek does? Find out now, because it can save you time. Read the article: https://www.xelplus.com/excel-goal-seek/ Excel's Goal Seek tool is in the Data Tools Tab as a part of What-If Analysis in the Excel Ribbon. Goal seek can be described as follows: • It is the opposite of a formula: instead of finding the end result, you give it the end result and it gives you the input • It eliminates the trial and error process • It is like guessing on speed. It’s useful for situations where you want quick answers • It is a simplified Solver The main advantage of Goal seek is that you don’t have to change your Excel formulas or turn a formula field to an input field. Solver video: https://youtu.be/dRm5MEoA3OI ★ My Online Excel Courses ► https://courses.xelplus.com/ ✉ Subscribe & get my TOP 10 Excel formulas e-book for free https://www.xelplus.com/free-ebook/ EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/ Get Office 365: https://microsoft.msafflnk.net/15OEg Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593 GEAR Camera: https://amzn.to/2FLiFho Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Microphone: https://amzn.to/2DVKstA Lights: http://amzn.to/2eJKg1U Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support! #MsExcel
Views: 48085 Leila Gharani
Advanced Excel Filter (And, Or, Between conditions for criteria range)
 
07:24
How does Advanced Filter work in Excel? In this quick tutorial I show you many examples that explain: 1. How to setup advanced filter 2. How to use the criteria range in advanced filter 3. How to use wildcards 4. How to setup your criteria to filter for OR / AND conditions 5. How to filter between two dates or two numbers with advanced filter 6. How to get a list of unique values 🡻 Download the workbook here 🡻 http://www.xelplus.com/excel-filter-basic-to-advanced/ ★★ Links to related videos: ★★ Excel Filter Basics: https://youtu.be/wMlTDXPEjag ★ My Online Excel Courses ★ Courses ► https://courses.xelplus.com/ ✉ Subscribe & get my TOP 10 Excel formulas e-book for free https://www.xelplus.com/free-ebook/ EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/ Get Office 365: https://microsoft.msafflnk.net/15OEg Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593 GEAR Camera: https://amzn.to/2FLiFho Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Microphone: https://amzn.to/2DVKstA Lights: http://amzn.to/2eJKg1U Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support! #MsExcel
Views: 38649 Leila Gharani
Quick Excel Trick to Unstack Data from one Column to Multiple Columns
 
05:04
This trick is the easiest (or laziest) method to unstack data from one column to multiple columns. You might come across a case where you need to move data (or transpose data) from one column to multiple columns. If you Google this, you'll find different ways of doing this such as: 1. Using Excel array functions or the INDEX function 2. Power Query to unstack data 3. Excel macros for unstacking data 4. Excel add-ins that can unstack. In this Excel tutorial, we'll take a look at the easiest method of unstacking data. The good thing with this method is that it's dynamic so if your source data change, the stacked version also updates automatically. I also show you another quick trick at 3:18 - to hide the zeros out of view in Excel using custom number formatting. ⯆ DOWNLOAD the workbook here: https://www.xelplus.com/unstack-data-to-multiple-columns/ You'll find a similar trick used in these videos: Transpose data: https://youtu.be/yYVokk0NdiI Hack formula copy: https://youtu.be/zWhGVZ-R-6g ★ My Online Excel Courses ► https://courses.xelplus.com ✉ Subscribe & get my TOP 10 Excel formulas e-book for free https://www.xelplus.com/free-ebook/ EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/ Get Office 365: https://microsoft.msafflnk.net/15OEg Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593 GEAR Camera: https://amzn.to/2FLiFho Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Microphone: https://amzn.to/2DVKstA Lights: http://amzn.to/2eJKg1U Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support! #MsExcel
Views: 19684 Leila Gharani
Excel Hack: Copy Formula Without Changing Cell References (or Without File References)
 
04:26
Would you like to copy your Excel formulas from one place to another without the cell references changing? In this video I show you a simple trick that does that. Excel default behavior is to adjust the cell references when you copy a cell and paste to another one. You can choose to fix the cell reference by using the dollar sign ($). A $ for the row number, fixes the row and a $ on the column number fixes the column. You might however need to copy a set of formulas that have relative references from one location to another or from one file to another and keep the existing cell references. This video shows a very simple trick you can use. ★ My Online Excel Courses ★ Courses ► https://courses.xelplus.com/ ✉ Subscribe & get my TOP 10 Excel formulas e-book for free https://www.xelplus.com/free-ebook/ EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/ Get Office 365: https://microsoft.msafflnk.net/15OEg Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593 GEAR Camera: https://amzn.to/2FLiFho Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Microphone: https://amzn.to/2DVKstA Lights: http://amzn.to/2eJKg1U Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support! #MsExcel
Views: 34574 Leila Gharani
Realistic Case: Dependent Drop-Down Lists in Excel with Multiple Words & Spaces in  1st List
 
07:54
Download the Workbook here: http://www.xelplus.com/excel-dependent-drop-down-lists-multiple-words-spaces/ This video builds on the methods shown in a previous video: https://youtu.be/CIJbcBBH88M which showed how to create an Excel dynamic drop-down data validation list that was dependent on the selection of another drop-down. In that video the first list contained numbers whereas in this video we take a look at a more realistic case where the first list contains text with multiple words and spaces. This approach works if your drop downs are in different worksheets as well. Check out this video to create drop-down lists on tabular data sets: https://youtu.be/avVLznHODVA Index Match basics explained: https://youtu.be/F264FpBDX28 ★ My Online Excel Courses ► https://courses.xelplus.com/ ✉ Subscribe & get my TOP 10 Excel formulas e-book for free https://www.xelplus.com/free-ebook/ EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/ Get Office 365: https://microsoft.msafflnk.net/15OEg Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593 GEAR Camera: https://amzn.to/2FLiFho Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Microphone: https://amzn.to/2DVKstA Lights: http://amzn.to/2eJKg1U Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support! #MsExcel
Views: 31588 Leila Gharani
Excel INDIRECT Function: Lookup Values in Different Sheets / Excel Tabs
 
11:40
Easy explanation of Excel's INDIRECT Function - How to use it and when to use it. Also what to watch out for when you're using it. Download the workbook here: http://www.xelplus.com/excel-indirect-function/ Get the full Excel Dashboard course here: https://courses.xelplus.com/p/professional-excel-dashboards Indirect can be a confusing function. It takes a little bit time to get the hang of it. What Indirect does, is it returns an address. So for example, if you type in =indirect(A1) and inside A1, you have written A10 - then your formula returns what is inside A10. Why would you need this? Watch the full video and download the workbook to practice along. ★ My Online Excel Courses ► https://courses.xelplus.com/ ✉ Subscribe & get my TOP 10 Excel formulas e-book for free https://www.xelplus.com/free-ebook/ EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/ Get Office 365: https://microsoft.msafflnk.net/15OEg Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593 GEAR Camera: https://amzn.to/2FLiFho Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Microphone: https://amzn.to/2DVKstA Lights: http://amzn.to/2eJKg1U Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support! #MsExcel
Views: 95957 Leila Gharani
Excel SUMIFS (better version of SUMIF), COUNTIFS & AVERAGEIFS (Multiple Criteria)
 
14:04
Quickly learn how SUMIFS & COUNTIFS formulas work - and why they are better than SUMIF & COUNTIF formulas. Download the workbook here: https://www.xelplus.com/sumifs-countifs-averageifs/ Get the full course: https://www.xelplus.com/courses The inclusion of the "IFS" formulas since Excel 2007 was a major gain for Excel users. Why? Because they allow you to easily add exceptions to your sum, average and count calculations. Don’t make your life more difficult by trying to use excel filters to get your sum. Use the SUMIFS formula instead. You can SUM based on multiple criteria. You can also use SUMIFS to sum if numbers are greater than a certain value. In this video I will show you a SUMIFS example and explanation. AVERAGEIFS works in the same way. COUNTIFS function is shorter than SUMIFS and AVERAGEIFS because it doesn’t need the additional sum range. It only needs the criteria range. It counts the number of times the criteria is met. Not sure if you should use SUMIF or SUMIFS? Stick to SUMIFS. This way you're on the safe side. In case you ever need to add more criteria, you can just expand on the arguments inside the formula. If you used SUMIF, you actually have to replace it with SUMIFS. ★ My Online Excel Courses ► https://courses.xelplus.com/ ✉ Subscribe & get my TOP 10 Excel formulas e-book for free https://www.xelplus.com/free-ebook/ EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/ More resources on my Amazon page: https://www.amazon.com/shop/leilagharani
Views: 159704 Leila Gharani
Excel Pivot Chart with Slicers for Months to Show Values by Weekday Names
 
11:48
This type of interactive chart is great for impressive dashboards. Quickly learn how to create an Excel Pivot chart that's driven by pivot slicers. Download the workbook here: http://www.xelplus.com/pivot-slicer-excel/ In this example I cover two topics: 1. How to get Weekday name from a given date in Excel 2. How to create a Pivot Chart controlled by a Slicer In addition I show you how you can customize slicers in Excel to better match your report layout. ★ My Online Excel Courses ► https://courses.xelplus.com/ ✉ Subscribe & get my TOP 10 Excel formulas e-book for free https://www.xelplus.com/free-ebook/ EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/ Get Office 365: https://microsoft.msafflnk.net/15OEg Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593 GEAR Camera: https://amzn.to/2FLiFho Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Microphone: https://amzn.to/2DVKstA Lights: http://amzn.to/2eJKg1U Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support! #MsExcel
Views: 48244 Leila Gharani
Master Excel's SUMPRODUCT Formula
 
14:56
Quickly learn how Excel's SUMPRODUCT formulas works. Download the workbook: http://www.xelplus.com/excel-sumproduct-formula-easy-explanation/ Get the full course: https://courses.xelplus.com Advanced Excel: Master Excel's SUMPRODUCT Formula This video is part of a series that belongs to my Advanced Training course called "Advanced Excel: Top tips and formulas". The classical use of Excel's SUMPRODUCT function is to do a sum of multiplications. The good thing about SUMPRODUCT is that it can work on arrays of values and it doesn’t have to be entered as an array function so no need to do control + shift + enter (CSE). The great thing about SUMPRODUCT, is that it can help solve many more problems in non-obvious ways. It can act as a conditional SUMIFS function or work as a lookup function as INDEX and MATCH does. It can also act as a COUNTIF function. SUMPRODUCT is also a better SUMIFS, because it can check for OR conditions - which the SUMIFS can't. SUMIFS can only check for AND conditions. In this video you will see different examples of the SUMPRODUCT function and you will learn how it can save you from unnecessary "workarounds" in your Excel files. It will also show you the SUMPRODUCT formula in slow motion. ★ My Online Excel Courses ► https://courses.xelplus.com ✉ Subscribe & get my TOP 10 Excel formulas e-book for free https://www.xelplus.com/free-ebook/ EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/
Views: 84063 Leila Gharani
Excel Solver example and step-by-step explanation
 
09:57
Excel’s Solver tool is an optimization package. It finds the optimal solution to a problem by changing multiple variables. Download the workbook here: http://www.xelplus.com/excel-solver-example It can solve for more complex “what-if-analysis” which Goal Seek can’t. Goal seek is restricted to changing one variable, whereas with solver you can have many variables. Calling Solver an “advanced goal seek” doesn’t do it any justice. Solver can handle complex data models and solve for the optimal solution. But; it can also easily solve for the more simpler problems that we might face at work. In this video, I show you two examples: 1 - How to set income to a specific value by changing prices which adding constraints for the price and also full rounded number for units sold. 2. Distribute the remaining budget dollars among your different projects based on certain cost constraints. Goal seek Video: https://youtu.be/OhnkuBVTcg8 ★ My Online Excel Courses ► https://courses.xelplus.com/ ✉ Subscribe & get my TOP 10 Excel formulas e-book for free https://www.xelplus.com/free-ebook/ EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/ Get Office 365: https://microsoft.msafflnk.net/15OEg Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593 GEAR Camera: https://amzn.to/2FLiFho Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Microphone: https://amzn.to/2DVKstA Lights: http://amzn.to/2eJKg1U Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support! #MsExcel
Views: 86901 Leila Gharani
Excel Sum(ifs) between dates, sum month & sum with multiple criteria
 
08:44
Use the Excel SUMIFS formula to sum values between two dates. Here you'll need to use the greater than & less than operators inside the SUMIFS formula. You'll also learn how to sum values within a month. For example, your data set contains dates and you'd like to create a monthly report where you show the total value for the month, you can use the SUMIFS function together with the EOMONTH (end of the month Excel formula) Last, you'll learn how to add more criteria to your sum range. For example, you'd like to sum values between two dates and also account for one or more additional criteria. Happy learning! 🡻 Download the workbook here 🡻 https://www.xelplus.com/excel-sum-between-dates/ ★★ Links to related videos: ★★ SUMIFS, COUNTIFS & AVERAGEIFS Basic introduction: https://youtu.be/AZuBNWMh7VM Formulas playlist: https://www.youtube.com/playlist?list=PLmHVyfmcRKyxvxehq3fiGjKDsEyy6d4Tz ★ My Online Excel Courses ★ Courses ► https://courses.xelplus.com/ ✉ Subscribe & get my TOP 10 Excel formulas e-book for free https://www.xelplus.com/free-ebook/ EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/ Get Office 365: https://microsoft.msafflnk.net/15OEg Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593 GEAR Camera: https://amzn.to/2FLiFho Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Microphone: https://amzn.to/2DVKstA Lights: http://amzn.to/2eJKg1U Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support! #MsExcel
Views: 24286 Leila Gharani
Advanced Excel Index Match (3 Most Effective Formulas for Multiple Criteria)
 
10:22
Download workbook here: http://www.xelplus.com/excel-index-and-match-2/ Complete Excel Dashboard course: https://courses.xelplus.com/p/professional-excel-dashboards How to do Index Match in Excel: To learn the basics behind Excel's Index and Match, watch this video: https://www.youtube.com/watch?v=F264FpBDX28 This INDEX MATCH Advanced video shows you how you can use Index & Match to lookup multiple criteria. For example when you have more than one header in a column or more than one row header - i.e. you don't have unique fields to match. You might be tempted to do multiple index match formulas inside one another, or use an IF function here. The solution is actually quite simple. In the video I show you 3 different ways to write the INDEX MATCH formula in Excel. One method uses helper cells to create unique keys for the match function to find. Method 2, uses array formulas with CSE (Control Shift Enter) to convert the MATCH function to an array function. Method 3, avoids CSE and also avoids using helper cells in Excel. It does this by using a second INDEX formula inside the match function (INDEX does not require Control Shift Enter). Don't forget to download the free workbook to practice along. ★ My Online Excel Courses ► https://courses.xelplus.com/ ✉ Subscribe & get my TOP 10 Excel formulas e-book for free https://www.xelplus.com/free-ebook/ EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/ Get Office 365: https://microsoft.msafflnk.net/15OEg Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593 GEAR Camera: https://amzn.to/2FLiFho Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Microphone: https://amzn.to/2DVKstA Lights: http://amzn.to/2eJKg1U More resources on my Amazon page: https://www.amazon.com/shop/leilagharani Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support! #MsExcel
Views: 164954 Leila Gharani
Return Multiple Match Results in Excel (2 methods)
 
14:13
Quickly learn how to lookup one value in Excel and return multiple matches. VLOOKUP and INDEX & MATCH formulas always return the first match they find inside a data table. What if you wanted to return multiple matches? I'll show you two different methods: Method 1: Uses INDEX & AGGREGATE formulas. This doesn't require CSE (control shift enter) and it works on older Excel versions. Beginning: 1:31, End: 10:20 Method 2: Uses the relatively new TEXTJOIN formula (available in Excel 2016 Office 365) and requires CSE. Beginning: 11:40, End: 13:25 Pick the method you find easier to maintain. 🡻 Download the workbook here 🡻 https://www.xelplus.com/return-multiple-match-values-in-excel/ ★ Links to related videos: ★ VLOOKUP basics: https://youtu.be/E7gQ-PgYkMc INDEX MATCH basics: https://youtu.be/F264FpBDX28 Get Unique List: https://youtu.be/7fYlWeMQ6L8 TEXTJOIN: https://youtu.be/TMZEUlFGp1U ★★ My Online Excel Courses ★★ Courses ► https://courses.xelplus.com/ 🕮 Excel Resources I Recommend 🕮 https://www.xelplus.com/resources/ ✉ Subscribe & get my Excel top 10 tips and formulas e-book for free https://www.xelplus.com/free-ebook/
Views: 37325 Leila Gharani
5 Design Tips for Better Excel Reports & Dashboards
 
04:44
I take you through 5 design tips that help create better Excel Reports and Dashboards. Link to Excel Dashboard course: https://courses.xelplus.com/p/professional-excel-dashboards Full article is here: https://www.xelplus.com/5-design-tips-for-excel-dashboards-reports/ 5 tips to create the best Excel dashboard: 1. Create emphasis: Add a strong contrast to headings to show at a glance the KPIs on display in your Excel dashboard. 2. Connect different elements: Show relationships with alignment. 3. Draw parallels and create associations: Two things that should be repeated consistently in your dashboard are abbreviations and color usage. 4. Organise content and create focal points: Think about proximity. What will you choose to group or separate? 5. Balance large elements with smaller elements ★ My Online Excel Courses ► https://courses.xelplus.com/ ✉ Subscribe & get my TOP 10 Excel formulas e-book for free https://www.xelplus.com/free-ebook/ EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/ Get Office 365: https://microsoft.msafflnk.net/15OEg Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593 GEAR Camera: https://amzn.to/2FLiFho Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Microphone: https://amzn.to/2DVKstA Lights: http://amzn.to/2eJKg1U Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support! #MsExcel
Views: 91890 Leila Gharani
Excel Complex Lookup: Find Header based on Lookup Criteria in Matrix with INDEX & SUMPRODUCT
 
10:44
Download the workbook here: http://www.xelplus.com/complex-lookup-index-sumproduct/ In this video I show you the answer to one of the questions that was asked some time ago on the Mr. Excel message board (the sample data was changed for the video) - The question is a complex LOOKUP problem - task is to find the header of the lookup criteria which is in a matrix. The lookup value could be in any column and any row - that's what makes the problem complex. How to get the header based on a lookup value that could be in any cell? In the video you'll find my approach; It uses the INDEX function together with SUMPRODUCT. What is your approach? Video for Index Match Basics: https://youtu.be/F264FpBDX28 To understand Sumproduct: https://youtu.be/lzjc_eEISe8 ✔ Alternate Solutions ✔ Watch Kevin's video to see 4 different approaches of solving this 2 with array formulas, 1 with helper columns and last one with a pivot table to normalize the date set: https://youtu.be/u4IcqcQP1FI Watch Oz's Video from Excel on Fire here which shows how to solve this with a COUNTIFS function and the Power Query method: https://youtu.be/IwBYEXaOSOk To solve for multiple matches - i.e. non-unique data set, check out this video: https://youtu.be/ULa7nQrMvoc ★ My Online Excel Courses ► https://courses.xelplus.com/ ✉ Subscribe & get my TOP 10 Excel formulas e-book for free https://www.xelplus.com/free-ebook/ EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/ Get Office 365: https://microsoft.msafflnk.net/15OEg Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593 GEAR Camera: https://amzn.to/2FLiFho Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Microphone: https://amzn.to/2DVKstA Lights: http://amzn.to/2eJKg1U Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support! #MsExcel
Views: 26070 Leila Gharani
Excel IF Formula: Simple to Advanced (multiple criteria, nested IF, AND, OR functions)
 
15:23
Learn how to use Excel's IF Function in your reports. We cover a few examples in this tutorial including: 1. Simple IF function (also with greater than or less than checks). 2. IF Formula together with AND logical test to decide outcome if more than one condition is met. 3. Nested IF formula (an IF inside another IF) to check for multiple criteria. 4. IF formula with OR function (to check if "either" condition is met and then decide the outcome). 5. We also take a look at using formulas (other calculations) inside the IF function and finally: 6. How to use symbols as the result of the IF formula Your IF formula isn't working? One of the main mistakes is the "else" argument. That's the last argument inside the IF function. Make sure you haven't forgotten to specify what should happen "if" the condition isn't met. In case you have more than one condition, here's the place to add the second "if" statement. An IF formula inside another IF formula is called a nested if function. Download the workbook here: http://www.xelplus.com/excel-if-formula-simple-to-advanced/ Links to related videos: Conditional formatting symbols: https://youtu.be/875eK_x4nyQ Custom formatting symbols: https://youtu.be/tGY70sdpaLc ★ My Online Excel Courses ► https://courses.xelplus.com/ ✉ Subscribe & get my TOP 10 Excel formulas e-book for free https://www.xelplus.com/free-ebook/ EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/ Get Office 365: https://microsoft.msafflnk.net/15OEg Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593 GEAR Camera: https://amzn.to/2FLiFho Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Microphone: https://amzn.to/2DVKstA Lights: http://amzn.to/2eJKg1U More resources on my Amazon page: https://www.amazon.com/shop/leilagharani Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support! #MsExcel
Views: 420923 Leila Gharani
4 Smart Ways to use Custom Formatting instead of Conditional Formatting in Excel - Part 1
 
16:12
Custom formatting is really easy to use once you understand the rule behind it. Download the workbook here: http://www.xelplus.com/smart-uses-of-custom-formatting/ For Part 2 watch: https://youtu.be/faPSXNkU1Y0 We normally use custom formatting to show numbers with thousand separator, as percentage or even to show green for positive and red for negative values. Basically to format the numbers in our reports. But we hardly ever use custom formatting to show deviations with symbols. Or to disguise our numbers to look like symbols instead. The trick is super simple. In this video, I show you a simple way of showing variances using symbols - up/down arrows or any symbol of your choice - with Excel's custom formatting. All you need to know is the rule behind custom formatting - i.e. first argument is how positive numbers should be formatted, then how negative numbers should be formatted, followed by how zero values should be shown and last text. Custom formatting can be confusing, but if you remember this simple rule, you're all set. I cover this also in my free PDF eBook which you can get from my website (see link below) Why would you want to use custom instead of conditional formatting? 1. Your files will process faster. 2. It's super easy to setup! This video belongs to a 2 part series. This is part 1 of 2. In the next video, I will show you how you can add thresholds to your deviations using custom formatting. VBA code from Excel MVP Charles Williams to time your formulas is included here: http://bit.ly/2q1fpSK ★ My Online Excel Courses ► https://courses.xelplus.com/ ✉ Subscribe & get my TOP 10 Excel formulas e-book for free https://www.xelplus.com/free-ebook/ EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/ Get Office 365: https://microsoft.msafflnk.net/15OEg Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593 GEAR Camera: https://amzn.to/2FLiFho Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Microphone: https://amzn.to/2DVKstA Lights: http://amzn.to/2eJKg1U More resources on my Amazon page: https://www.amazon.com/shop/leilagharani Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support! #MsExcel
Views: 132048 Leila Gharani
3 Handy Excel Chart Tips You Might Have Missed
 
05:53
These are the 3 most common Excel chart questions I get. In this video you'll find the answers to these. Some of these can be quite frustrating and not easy to figure out. For example: 1. How to show your chart but hide your data - basically how do you keep your chart from disappearing if you hide or group your columns where the chart data is in. 2. How do you snap the chart into the gridlines? Is there a shortcut key for this? 3. Can you avoid grouping charts and objects together if you add in text boxes and other shapes to your chart? ★ Links to related videos: ★ Chart basics: https://youtu.be/DAU0qqh_I-A ★ My Online Excel Courses ★ Courses ► https://courses.xelplus.com/ ✉ Subscribe & get my TOP 10 Excel formulas e-book for free https://www.xelplus.com/free-ebook/ EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/ Get Office 365: https://microsoft.msafflnk.net/15OEg Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593 GEAR Camera: https://amzn.to/2FLiFho Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Microphone: https://amzn.to/2DVKstA Lights: http://amzn.to/2eJKg1U Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support! #MsExcel
Views: 10149 Leila Gharani
Lookup values across multiple worksheets: VLOOKUP / INDEX MATCH in Excel
 
13:10
In this Excel tutorial I show you how to lookup values from multiple Excel Sheets or tabs. In Excel it's relatively easy to lookup values in one specific tab. But how do you lookup values in multiple tabs using just one single formula? In the video, I show you different versions of getting this done. One version uses VLOOKUP and direct worksheet and cell references. The other version uses INDEX & MATCH and Excel table names and references. The key here is the INDIRECT function which acts as the messenger which returns the correct address to the different lookup formulas. Of course, you can apply this (indirect) method inside other formulas. 🡻 Download the workbook here: http://www.xelplus.com/lookup-values-across-multiple-worksheets-excel/ Links to related videos: VLOOKUP explained ► https://youtu.be/E7gQ-PgYkMc INDEX MATCH Basics ► https://youtu.be/F264FpBDX28 INDIRECT explained ► https://youtu.be/GUClkvJ7Gag ★ My Online Excel Courses ► https://courses.xelplus.com/ ✉ Subscribe & get my TOP 10 Excel formulas e-book for free https://www.xelplus.com/free-ebook/ EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/ Get Office 365: https://microsoft.msafflnk.net/15OEg Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593 GEAR Camera: https://amzn.to/2FLiFho Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Microphone: https://amzn.to/2DVKstA Lights: http://amzn.to/2eJKg1U More resources on my Amazon page: https://www.amazon.com/shop/leilagharani Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support! #MsExcel
Views: 101987 Leila Gharani
Excel: Extract unique items for dynamic data validation drop down list
 
14:49
Your original data set contains multiple instances of the same entry but you'd like to have a drop-down list that only includes unique items in Excel? And you'd like to do this with an Excel formula? This video is for you. As a first step I use formulas to extract the list of unique items to separate cells. My method includes a combination of the Index function together with Match and the Countif function. I'll show you a way to also avoid CSE (control shift enter) as well. In the second part of the video, I use the Offset formula to create a dynamic list that expands depending on the number of items that should be included in the list. The solution provided is dynamic so if you add new data or change data, everything updates automatically (we use Excel's Table feature here) 🡻 Download the workbook here: http://www.xelplus.com/extract-unique-items-for-dynamic-data-validation-drop-down-list/ Links to related videos: Dependent-Drop Down List (Followup): https://youtu.be/gu4xJWAIal8 Index Match: https://youtu.be/F264FpBDX28 Offset: https://youtu.be/RPTQjbk2qy4 This video from Kevin Lehrbass summarizes all the different techniques you can use to get the job done: https://youtu.be/BFojgCsKNjw https://microsoft.msafflnk.net/15OEg
Views: 72878 Leila Gharani
Excel: Dependent Drop Down Lists with OFFSET, CHOOSE, INDEX & INDIRECT
 
11:44
Learn how to create conditional drop-down Excel Validation lists using the INDEX function, OFFSET, CHOOSE and INDIRECT Functions. One aim, many ways to get there. Which one do you like? Download the free workbook here: http://www.xelplus.com/excel-dependent-drop-down-lists Complete Excel Dashboard course: https://courses.xelplus.com/p/professional-excel-dashboards How to create a dependent drop-down when first list contains text with multiple words? Check out this video: https://youtu.be/w7cHgsFirLk Link to Drop-down lists using INDEX and area numbers: https://youtu.be/8hAT18U8qWs Link to Excel OFFSET video - Learn the basics of the OFFSET formula: https://youtu.be/RPTQjbk2qy4 Lecture summary: In this video, I'll show you how you can use the OFFSET formula, CHOOSE formula and INDIRECT function to create drop-down lists that are dependent on another drop down list. ★ My Online Excel Courses ► https://courses.xelplus.com/ ✉ Subscribe & get my TOP 10 Excel formulas e-book for free https://www.xelplus.com/free-ebook/ EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/ Get Office 365: https://microsoft.msafflnk.net/15OEg Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593 GEAR Camera: https://amzn.to/2FLiFho Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Microphone: https://amzn.to/2DVKstA Lights: http://amzn.to/2eJKg1U Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support! #MsExcel
Views: 84468 Leila Gharani
116 Excel Tutorials in 1 Video
 
04:58
This is a special Excel summer video - I'll show you how I come up with Excel video topics for my YouTube channel and I also show you how to search 116 Excel tutorials in one place. I also have accompanying articles as well as downloadable workbooks for most of the videos you'll watch. You'll find out how to get to them in the video. You can learn Excel basics, Excel formulas, tips & tricks, charts, visualization & dashboard techniques as well as Excel VBA & macros on this channel. Make sure you check out the palylists on the channel as well. Wish you a nice summer! 🡻 Download the workbook here: https://www.xelplus.com/free-excel-tutorials/ Links to related videos: If you'd like to start from the basics, check out my Excel basics playlist here: https://www.youtube.com/playlist?list=PLmHVyfmcRKyx1KSoobwukzf1Nf-Y97Rw0 ★ My Online Excel Courses ★ Courses ► https://courses.xelplus.com/ ► ✉ Subscribe & get my TOP 10 Excel formulas e-book for free https://www.xelplus.com/free-ebook/ ★ Excel Resources I Recommend https://www.xelplus.com/resources/ Get Office 365: https://microsoft.msafflnk.net/15OEg Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593 GEAR Camera: https://amzn.to/2FLiFho Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Microphone: https://amzn.to/2DVKstA Lights: http://amzn.to/2eJKg1U Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support! #MsExcel
Views: 35117 Leila Gharani
Excel Combo Box without VBA - How to create a drop-down list
 
05:12
Get the full course: https://courses.xelplus.com/p/professional-excel-dashboards Find out how you can insert and use the combo box form control in Excel. This way you can easily insert a drop down box in Excel to add more interactivity to your Excel reports. Your Excel reports will also become more user-friendly. Just make sure you have the developer tab enabled by going to the ribbon options and putting a check mark for the developer tab. This course is another video from my online Excel 10 hour Dashboard course! ★ My Online Excel Courses ► https://courses.xelplus.com/ ✉ Subscribe & get my TOP 10 Excel formulas e-book for free https://www.xelplus.com/free-ebook/ EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/ Get Office 365: https://microsoft.msafflnk.net/15OEg Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593 GEAR Camera: https://amzn.to/2FLiFho Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Microphone: https://amzn.to/2DVKstA Lights: http://amzn.to/2eJKg1U Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support! #MsExcel
Views: 31997 Leila Gharani
Great Excel Power Query, Power Pivot & Power BI Resources
 
04:23
In this video I share my favorite resources to learn and improve my Power Query, Power Pivot and Power BI knowledge. A lot of my students have asked about courses and resources in this area. ★★ Intro to Power Query, Power Pivot & DAX by Chris Dutton ★★ http://bit.ly/MavenPQLeila Link to free preview: http://bit.ly/MavenPQPreview ★★ Interested in POWER BI? ★★ New course from Chris: Up & Running with POWER BI: http://bit.ly/MavenBILeila Chris's other courses: http://bit.ly/MavenAll ★★ Excel 2016: Power Query (Get & Transform) by Oz dz Soleil★★ http://bit.ly/OzLinkedIn Oz's YouTube Channel: https://www.youtube.com/user/WalrusCandy Pelican video (Import Excel files from a folder): https://youtu.be/h6vq9NooiHE ★★ Power Query, Pivot , BI Playlists by Mike Girvin (ExcelisFn) ★★ Power Query: http://bit.ly/MikeQuery New Power Tools Course: https://www.youtube.com/watch?v=tmTJEdRtFlA&list=PLrRPvpgDmw0ks5W7U5NmDCU2ydSnNZA_1 Mike's Channel: https://www.youtube.com/user/ExcelIsFun ★★ Power Pivot and Power BI by Rob Collie & Avi Singh ★★ https://amzn.to/2Ms7wRB ★★ More Power Excel Books ★★ M is for (Data) Monkey by Ken Puls & Miguel Escobar: https://amzn.to/2JC6yEJ Learn to Write DAX by Matt Allington: https://amzn.to/2JPdf2q Complete list of resources recommended: http://www.xelplus.com/resources/#power (Full disclosure: Some of these links are affiliate links, which means if you use the link provided, I earn a small commission, at no extra cost to you. Thanks in advance if you choose to go through the link above. This helps support my channel) ★ My Online Excel Courses ★ Courses ► https://courses.xelplus.com/ ✉ Subscribe & get my TOP 10 Excel formulas e-book for free https://www.xelplus.com/free-ebook/ Get Office 365: https://microsoft.msafflnk.net/15OEg Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593 GEAR Camera: https://amzn.to/2FLiFho Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Microphone: https://amzn.to/2DVKstA Lights: http://amzn.to/2eJKg1U #MsExcel
Views: 12423 Leila Gharani
Build Impressive Charts: It's NOT your usual Bar Chart (Infographics in Excel)
 
16:25
Create better Excel reports by improving your existing charts with the smart use of Icons. In this video you learn how to create a bar chart that includes the male/female icon in a dynamic way (I will also show you how you can add shapes inside charts - this way, you can change your series to look like any shape of your choice) - you can of course replace these icons with any icon of your choice. You could for example use images of your products. This is an example of how you can use some chart tricks that allow you to create infographics or infocharts in Excel. You don't need to stick to the default chart types, you can improve on these to fit your corporate reporting style. 🡻 Download the workbook here 🡻 http://www.xelplus.com/infographics-in-excel-bar-chart-icons-symbols/ ★ Links to related videos: ★ Special Excel Charts: https://youtu.be/C9beIaL-cBA ★ My Online Excel Courses ★ Courses ► https://courses.xelplus.com/ ✉ Subscribe & get my TOP 10 Excel formulas e-book for free https://www.xelplus.com/free-ebook/ EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/ Get Office 365: https://microsoft.msafflnk.net/15OEg Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593 GEAR Camera: https://amzn.to/2FLiFho Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Microphone: https://amzn.to/2DVKstA Lights: http://amzn.to/2eJKg1U Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support! #MsExcel
Views: 71700 Leila Gharani
Count Day of Week Between Dates in Excel (How many Fridays?)
 
06:27
Find out how you can quickly count the number of Fridays between two dates in Excel. You can use this technique to also count the number of weekends, Mondays, Tuesdays or any day of the week between two dates. The Excel formula is simple: It uses networkdays.intl function to get the answer. The difference between NetWorkDays function and the Networkdays.Intl function is that you can choose which days you'd like excluded. Excel Networkdays automatically excludes Saturdays and Sundays. ⯆ DOWNLOAD the workbook here: https://www.xelplus.com/count-how-many-fridays/ LINKS to related videos: More about Excel NetWorkDays: https://youtu.be/uJKFObU0Dug ★ My Online Excel Courses ► https://courses.xelplus.com ✉ Subscribe & get my TOP 10 Excel formulas e-book for free https://www.xelplus.com/free-ebook/ EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/ Get Office 365: https://microsoft.msafflnk.net/15OEg Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593 GEAR Camera: https://amzn.to/2FLiFho Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Microphone: https://amzn.to/2DVKstA Lights: http://amzn.to/2eJKg1U Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support! #MsExcel
Views: 7900 Leila Gharani
Excel Charts: Sorted Bar Chart as Alternative to the Pie Chart
 
11:37
The reputation of pie charts has majorly dropped in the past years. For good reason too. How can you make better graphs than the pie chart? Watch the full video View my complete course here: https://courses.xelplus.com Unfortunately, all the coverage by reputable media and books by visualization experts, still haven’t persuaded companies to stop using the pie chart. Asking, is not enough. Convincing, is not enough. Provide them with a good tool, and they might say yes. This video shows you one smart alternative. ★ My Online Excel Courses ► https://courses.xelplus.com ✉ Subscribe & get my TOP 10 Excel formulas e-book for free https://www.xelplus.com/free-ebook/ EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/
Views: 39032 Leila Gharani
VLOOKUP EXPLAINED - 2 Practical Excel Lookup Examples
 
12:16
Quickly learn the ins and outs of Excel's VLOOKUP formula. I show 2 practical examples of the VLOOKUP formula. I also explain why your VLOOKUP formula might not be working correctly. Summary: The Excel VLOOKUP formula looks up a value in an Excel table and returns a value from an adjacent column. "V" stands for vertical lookup. This is one of most common lookup functions in Excel. If your data table is not sorted, it's important to look for an exact match - this means you can't omit the last argument of vlookup. You need to set that argument to "false" or to 0. 🡻 Download the workbook here 🡻 https://www.xelplus.com/basics-vlookup-hlookup/ ★ Links to related videos: ★ For reverse or 2-way lookup problems use INDEX & MATCH: https://youtu.be/F264FpBDX28 Relative & Absolute referencing: https://youtu.be/FRu48zy-Djk ★★ My Online Excel Courses ★★ Courses ► https://courses.xelplus.com/ 🕮 Excel Resources I Recommend 🕮 https://www.xelplus.com/resources/ ✉ Subscribe & get my Excel top 10 tips and formulas e-book for free https://www.xelplus.com/free-ebook/
Views: 37052 Leila Gharani
This Excel Chart will grab your attention (Infographic template included)
 
17:11
Learn how to create Excel graphs that are different to the norm but still visually effective. They are great to include in special reports and dashboards. Download the workbook here: http://bit.ly/2rCXXrE Complete Excel Dashboard course: https://courses.xelplus.com/p/professional-excel-dashboards Note: This techniques is a part of my online Dashboard course. The link to the original report I mention in the video - https://business.udemy.com/blog/learning-predictions-2017/ This video shows you how to create special (non-standard) Excel graphs or info-charts in Excel. Not only that but it also shows you how you can conditionally format your graph to highlight specific categories. In this example, I show you how to highlight the week day that has the most users logged on to a system, but you can apply the same technique to any other logic as well. After that, I show you how you can add shapes to charts. This attracts the attention of your readers because it's different to what they are used to seeing. The problem with this technique is that it stretches out the shape for the bigger data sets so depending on the shape you are using, they are no longer harmonized. As a last step, I show you a trick you can use to create harmonized shapes in your Excel graphs. It's all a matter of proper data preparation before you draw out the chart. ★ My Online Excel Courses ► https://courses.xelplus.com/ ✉ Subscribe & get my TOP 10 Excel formulas e-book for free https://www.xelplus.com/free-ebook/ EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/ Get Office 365: https://microsoft.msafflnk.net/15OEg Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593 GEAR Camera: https://amzn.to/2FLiFho Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Microphone: https://amzn.to/2DVKstA Lights: http://amzn.to/2eJKg1U More resources on my Amazon page: https://www.amazon.com/shop/leilagharani Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support! #MsExcel
Views: 260231 Leila Gharani
3 Tips for Impressive Excel Charts
 
05:01
Complete Advanced Excel Chart Course: https://courses.xelplus.com Advanced Excel Charts: 3 Steps you need to follow to make good charts Summary: Are you unsure how to improve the charts and tables in your Excel reports? How to visualize your data for a clear message? Not sure what is easy, effective and possible in Excel? Learn all of this and more at your own pace with my online training. In this lecture I provide an overview of what makes a chart effective – by effective I mean the reader is able to clearly understand the message that you are trying to communicate - Charts should be "SCC" - Simple, Clear & consistent. Detail: Now lets have a look at each parameter for Simple, Clear & Consistent For Simple: - Strip your Chart: take away components, colors, images and anything else that does not matter a. Take away background color b. Lighten the grid lines or take them away entirely c. You can remove the Y axis if you show data labels or only show beginning & end values - Stick to simple fonts. They shouldn’t be too small and difficult to read - Stick to 2D For clear: As noted by Edward Tufte “Above all else show the data”. In his book the Visual Display of Quantitative information he talks about the concept of the data-ink ratio. Data-ink ratio is the amount of ink that shows the information as compared to the total amount of ink. Basically what it comes down to is don’t waste ink on things that don’t matter. Use your ink to show and highlight what matters. The way you highlight, is either you reduce the ink from the surroundings, or you increase the ink of the element you like to highlight. Simplified charts ensure your attention is driven to the data: to what matters. - Have a title – Every chart should have a title. Your readers shouldn’t have to figure it out. The title should be on top of the Chart. Reports are read from top to bottom and in the western world from left to right. The top left corner has a great impact but up middle is also good. - Tell the story – the truth and nothing but the truth. Don’t have breaks in the axis such as a gap between the values – i.e. have consistent scaling. Start form zero. Sometimes you want to concentrate attention to the pattern of a section of the graph and you might not do your story justice if you start from zero. In this case you can add a second graph where the y axis doesn’t start from zero. If you do start your axis other than zero, make sure you inform the readers. If you are using a column chart though, always start from zero. -If you have more than one series you need legends. These are your series labels. Whenever you can – such as for line graphs, place the legends by the series. If you can't place it by the series, then place them either on top, below the title or on the right hand-side. - Boost elements: do emphasize the most important part if there is a certain point you want to make and you want your readers to find it fast. Consistent: Use the same colors throughout your reports. Actual series should always stand out and have a consistent color throughout the report. Keep consistent colors for Budget and Outlook series. The colors that work well are different shades of grey & blue. It also depends on the logo and color mix of your organization. If your organization has a brown logo, you might want to use a shade of Brown for Actual series, if it is blue, then blue might work better. Whatever color you choose, you should stick with it throughout your report. That’s SCC - Simple, Clear & consistent. Every time you design a chart, make sure it’s as SCC as it can get. Then go beyond SCC and ensure it is as dynamic as it can get. If you have a chart that you need to update every month by dragging or updating the input range then implement the techniques that I will show you in the online excel training. This is specially beneficial for controllers and financial analysts looking to enhance their corporate reports to include better & improved charts. ★ My Online Excel Courses ► https://courses.xelplus.com ✉ Subscribe & get my TOP 10 Excel formulas e-book for free https://www.xelplus.com/free-ebook/ EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/
Views: 15526 Leila Gharani
Data Validation in Excel - should you include these in your files?
 
15:42
Quickly learn how to make a drop down list in Excel or add other data validation checks. By the end of the this tutorial you will learn: 1. How to create dynamic drop-down lists that grow as new categories are added 2. How to restrict cell input to dates that fall between two values or dates that are greater than today's date 3. How to restrict cell input to values that have specific length (or are greater or less than set total characters) 4. How to restrict numbers input to fall between predefined values 5. How to copy and paste data validation to other cells. Full Article: https://www.xelplus.com/data-validation-in-excel/ ★★ Links to related videos: ★★ Indirect Function: https://youtu.be/GUClkvJ7Gag Dependent data validation lists: https://youtu.be/w7cHgsFirLk ➹ ➹ ➹ Online Excel Courses ➹ ➹ ➹ Excel VBA & Macros ► http://bit.ly/LeilaVBA Excel Dashboards ► http://bit.ly/LeilaDashboards Excel Charts ► http://bit.ly/LeilaCharts Advanced Excel ► http://bit.ly/LeilaAdvancedExcel Excel Waterfall Charts ► http://bit.ly/LeilaWaterfall 🕮 Recommend Excel Resources 🕮 https://www.xelplus.com/resources/ ✉ Subscribe to my Newsletter ✉ Stay informed and get discounts when new courses come out: http://www.xelplus.com/
Views: 26237 Leila Gharani
Excel Cell Reference: Absolute, Relative or Mixed?
 
07:46
Reference cells correctly when writing Excel formulas. If your formulas are not working correctly when you copy them to other cells, you've probably made a mistake with your cell referencing. Some cell references might need to be fully fixed, some partially fixed. This is called absolute versus relative cell referencing in Excel. Understanding this concept really simplifies your Excel work. ★ Links to related videos: ★ Check out the full Excel Basics playlist here: https://www.youtube.com/playlist?list=PLmHVyfmcRKyx1KSoobwukzf1Nf-Y97Rw0 ★ My Online Excel Courses ★ Courses ► https://courses.xelplus.com/ ✉ Subscribe & get my TOP 10 Excel formulas e-book for free https://www.xelplus.com/free-ebook/ EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/ Get Office 365: https://microsoft.msafflnk.net/15OEg Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593 GEAR Camera: https://amzn.to/2FLiFho Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Microphone: https://amzn.to/2DVKstA Lights: http://amzn.to/2eJKg1U Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support! #MsExcel
Views: 9711 Leila Gharani
Excel SUMIFS: Sum Alternate Columns based on Criteria and Header
 
10:54
Learn how to sum columns based on column header and on criteria. Download the workbook here: http://bit.ly/2osWAXD To solve this with SUMPRODUCT: https://youtu.be/9ICMErVqsJs This example shows you a set of HR data. The aim is to create a report where the user inputs the cost center and selects which information they want to see for the cost center - for example the total salary, benefits or bonus information. The challenge is to SUM based on criteria (cost center) AND based on column header selection. The solution shown in the video uses SUMIFS together with INDEX MATCH to provide a moving sum. The 2nd argument of the INDEX function using area numbers is also used to show an alternate solution. To learn more about the SUMIFS function, check out this video: https://youtu.be/AZuBNWMh7VM ★ My Online Excel Courses ► https://courses.xelplus.com/ ✉ Subscribe & get my TOP 10 Excel formulas e-book for free https://www.xelplus.com/free-ebook/ EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/ Get Office 365: https://microsoft.msafflnk.net/15OEg Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593 GEAR Camera: https://amzn.to/2FLiFho Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Microphone: https://amzn.to/2DVKstA Lights: http://amzn.to/2eJKg1U Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support! #MsExcel
Views: 57273 Leila Gharani
Excel VBA basics: Data types, DIM & SET (Learn to use them correctly)
 
07:32
It’s best practice to declare your Excel VBA variables before you use them. Declaring variables means that you give VBA a heads up that it needs to free up some memory space for your variables which will in most cases be less than the default. That's why it's important to understand the concept of data types and how much memory space each of these types will take. The less memory you allocate to a variable, the faster your code will run. I show the range associated to each data type - such as numbers, text, date, currency etc in the video. By default VBA assigns the data type “variant” to each variable. Variant means it changes depending on the data type so is it text, number or date. It will allocate around 16 bytes of memory if it’s a number and 22 bytes + length of string. ★★ Get the complete Excel VBA course here: https://courses.xelplus.com/p/excel-vba-excel-macros ★ Links to related videos: ★ Learn about Visual Basic Editor: https://youtu.be/NpvvwrdDcQk Properties & methods in VBA: https://youtu.be/uYHrzo2gzbo Blog Post: https://www.xelplus.com/excel-vba-data-types-dim-set/ ★ My Online Excel Courses ★ Courses ► https://courses.xelplus.com/ ✉ Subscribe & get my TOP 10 Excel formulas e-book for free https://www.xelplus.com/free-ebook/ EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/ Get Office 365: https://microsoft.msafflnk.net/15OEg Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593 GEAR Camera: https://amzn.to/2FLiFho Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Microphone: https://amzn.to/2DVKstA Lights: http://amzn.to/2eJKg1U Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support! #ExcelVBA
Views: 13537 Leila Gharani
Smart Dep. Drop-Down Lists in Excel: Expandable & Exclude Blank Cells
 
15:55
Create drop-down lists in Excel that are dynamic - #1 They expand as new data is added to the list, #2, they are dependent on another list and #3, they exclude blank/empty cells on the bottom of each list. Basically they are only restricted to the cells that have values in them. In this video you'll learn how to use the OFFSET function to get fully dynamic dependent drop down lists. You'll then see another version which uses INDEX & MATCH as well as Excel Tables. We plug in the formula we create inside Name Manager and then use the new name inside our custom data validation formula box. 🡻 Download the workbook here 🡻 https://www.xelplus.com/excel-dynamic-dependent-drop-down-lists-exclude-blanks/ ★ Links to related videos: ★ Data Validation drop down Playlist: https://www.youtube.com/playlist?list=PLmHVyfmcRKywYhC1Q9eZqR7D-_cdiwl6y Learn OFFSET: https://youtu.be/RPTQjbk2qy4 Learn INDEX & Match: https://youtu.be/F264FpBDX28 ★ My Online Excel Courses ★ Courses ► https://courses.xelplus.com/ ✉ Subscribe & get my TOP 10 Excel formulas e-book for free https://www.xelplus.com/free-ebook/ EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/ Get Office 365: https://microsoft.msafflnk.net/15OEg Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593 GEAR Camera: https://amzn.to/2FLiFho Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Microphone: https://amzn.to/2DVKstA Lights: http://amzn.to/2eJKg1U Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support! #MsExcel
Views: 48712 Leila Gharani
How to create a dynamic map chart with drop-down (works with ANY Excel version)
 
17:34
Create a simple but effective Excel Dynamic Map Chart with drop-down. Also Conditionally formats the largest values on the map for improved readability. Learn how to create a simple but impressive dynamic map chart that enables you to visualize specific KPI's by geography. This method doesn't use the relatively new Bing map feature, so it is works on all Excel versions. The technique shown uses a scatter plot first to set up the respective points on the map and then turns it into a bubble chart to be able to visualize the actual KPI. I also show you how you can conditionally format specific data points to bring the attention to certain categories. In this case I conditionally format the largest data point in the chart in a different color than the rest. This technique is really simple but has a very powerful effect. It makes the difference between a "nice" Excel dashboard to a "great" Excel dashboard. All it takes is 3 minutes of your time. Download the workbook here: https://www.xelplus.com/simple-excel-dynamic-map-chart-with-drop-down/ Links to related videos: SumIFS formula: https://youtu.be/AZuBNWMh7VM ★ My Online Excel Courses ► https://courses.xelplus.com/ ✉ Subscribe & get my TOP 10 Excel formulas e-book for free https://www.xelplus.com/free-ebook/ EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/ Get Office 365: https://microsoft.msafflnk.net/15OEg Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593 GEAR Camera: https://amzn.to/2FLiFho Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Microphone: https://amzn.to/2DVKstA Lights: http://amzn.to/2eJKg1U More resources on my Amazon page: https://www.amazon.com/shop/leilagharani Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support! #MsExcel
Views: 132515 Leila Gharani
Excel Dynamic Chart Range based on Cell (drop-down) Value for dashboards
 
12:25
Improve your Excel dashboards by adding in dynamic charts that allow the user to control what they want to see. Link to complete course: https://courses.xelplus.com/p/professional-excel-dashboards Downloadable workbook available inside the course. In this lecture you will learn how to create a dynamic chart ranges - assume your raw data table is quite big and you'd like the user to specify from which category or date - and to which category & date, they'd like to view the chart data from. Excel Offset function is one formula that you can use to create dynamic ranges, but Excel's Index formula is a great alternative to the OFFSET function AND it's not a volatile function. Once we've figured out the right INDEX formula, we will copy and paste this into name manager and use the name for our chart range. This will provide us with a dynamic chart that only shows the user the range they'd like to view. More Index Match videos: Basics of Index Match: https://youtu.be/F264FpBDX28 Advanced Index Match: https://youtu.be/ontXHp9cwOQ Index Dynamic Ranges: https://youtu.be/YBehCWjcizg ★ My Online Excel Courses ► https://courses.xelplus.com/ ✉ Subscribe & get my TOP 10 Excel formulas e-book for free https://www.xelplus.com/free-ebook/ EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/ Get Office 365: https://microsoft.msafflnk.net/15OEg Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593 GEAR Camera: https://amzn.to/2FLiFho Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Microphone: https://amzn.to/2DVKstA Lights: http://amzn.to/2eJKg1U Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support! #MsExcel
Views: 38601 Leila Gharani
Excel Filter Basics (for quick data analysis)
 
10:46
Find out how to filter your data in Excel. In this filter basics tutorial you'll learn the following: 1. The shortcut key to turn filter on and off in Excel 2. How to do approximate matches (filter for words that contain a specific word) 3. How to filter multiple columns 4. How to filter for "or" conditions 5. How to copy a filtered range to another sheet 6. How to use formulas (such as subtotal) to sum a filtered range 7. How to filter for values between 2 dates 8. How to filter based on color 9. How to turn your data set into an Excel table (shortcut key) 10. How to add totals to your Excel table 🡻 Download the workbook here 🡻 http://www.xelplus.com/excel-filter-basic-to-advanced/ ★★ Links to related videos: ★★ Check out my Excel Basics Playlist here: https://www.youtube.com/playlist?list=PLmHVyfmcRKyx1KSoobwukzf1Nf-Y97Rw0 ★ My Online Excel Courses ★ Courses ► https://courses.xelplus.com/ ✉ Subscribe & get my TOP 10 Excel formulas e-book for free https://www.xelplus.com/free-ebook/ EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/ Get Office 365: https://microsoft.msafflnk.net/15OEg Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593 GEAR Camera: https://amzn.to/2FLiFho Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Microphone: https://amzn.to/2DVKstA Lights: http://amzn.to/2eJKg1U Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support! #MsExcel
Views: 16809 Leila Gharani
Excel Conditional Formatting the Lookup result in data table (Index Match)
 
09:25
Find out how you can highlight values inside your data set based on a condition. In this example, we are going to conditionally format the result of our lookup formula (I use INDEX Match here) to conditionally format that same value that we find inside the original data set. The tricky part is to get the conditional formatting formula correct. Just remember, one reason why your conditional formatting formulas might not work can be due to the wrong usage of the dollar sign for fixing the cell references (i.e. absolute, versus relative referencing - and sometimes you need a mix of the two). Download the workbook here: http://www.xelplus.com/conditional-format-index-match-result/ Links to related videos: Index Match basics: https://youtu.be/F264FpBDX28 More on Conditional Formatting: https://youtu.be/875eK_x4nyQ ★ My Online Excel Courses ► https://courses.xelplus.com/ ✉ Subscribe & get my TOP 10 Excel formulas e-book for free https://www.xelplus.com/free-ebook/ EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/ Get Office 365: https://microsoft.msafflnk.net/15OEg Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593 GEAR Camera: https://amzn.to/2FLiFho Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Microphone: https://amzn.to/2DVKstA Lights: http://amzn.to/2eJKg1U Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support! #MsExcel
Views: 9931 Leila Gharani
Excel SUM based on Partial Text Match (SUMIFS with wildcards)
 
08:37
Learn how to SUM values in Excel based on a partial text match. This is great for cases when you need to sum a column based on "criteria contains" a specific value or text. For these cases you can use Excel's SUMIF or SUMIFS function together with wildcards. Examples of wildcards are the asterisk "*" sign or the Question mark "?". The asterisk is a placeholder for zero or more characters. The ? mark is a fixed placeholder in terms of the numbers of characters. If you use the question mark once, this means only one character can be variable. If you use it two times, then two characters can be "anything". You can combine the question mark together with the asterisk sign as well. I show different examples on how you can do that in this video. I also show you how you can sum based on two or more conditions. Here the SUMIFS formula comes in really handy. ⯆ DOWNLOAD the workbook here: https://www.xelplus.com/excel-sum-partial-text-match/ LINKS to related videos: Absolute Relative referencing: https://youtu.be/FRu48zy-Djk Excel SUMIFS Basics: https://youtu.be/AZuBNWMh7VM SUMIFS between Dates: https://youtu.be/XIhbL20jTHc ★ My Online Excel Courses ► https://courses.xelplus.com/ ✉ Subscribe & get my TOP 10 Excel formulas e-book for free https://www.xelplus.com/free-ebook/ EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/ Get Office 365: https://microsoft.msafflnk.net/15OEg Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593 GEAR Camera: https://amzn.to/2FLiFho Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Microphone: https://amzn.to/2DVKstA Lights: http://amzn.to/2eJKg1U Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support! #MsExcel
Views: 10278 Leila Gharani
Excel Dependent Drop Down List with Tabular Data without Named Ranges
 
07:24
Download the Workbook: http://www.xelplus.com/excel-dependent-drop-down-list-tabular-data/ Find out how to create a dependent drop down list in Excel based on a Tabular data set. Instead of having the contents of each drop-down beside each other as we had in the previous tutorial, we now have the contents of each drop-down right below one another. To distinguish which category belongs to which parent, the parent is listed right beside the category. This provides us with a table that has two columns. One for the parent and the other for the content. We would like to come up with a dynamic formula so that when the user selects a category from the first drop-down (i.e. the parent list), Excel figures out which part of the tabular data-set to include in the 2nd drop-down. This means we need a dynamic formula that always shows the part of the table that has a match for the parent category. Link to extracting unique items for drop-down: https://youtu.be/7fYlWeMQ6L8 Link to OFFSET video: https://youtu.be/RPTQjbk2qy4 Link to the other Dependent Drop-down List video: https://youtu.be/w7cHgsFirLk ★ My Online Excel Courses ► https://courses.xelplus.com/ ✉ Subscribe & get my TOP 10 Excel formulas e-book for free https://www.xelplus.com/free-ebook/ EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/ Get Office 365: https://microsoft.msafflnk.net/15OEg Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593 GEAR Camera: https://amzn.to/2FLiFho Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Microphone: https://amzn.to/2DVKstA Lights: http://amzn.to/2eJKg1U Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support! #MsExcel
Views: 21764 Leila Gharani
Excel Simple Dependent Drop down List based on numbers (Index without match)
 
08:46
Download the free Excel INDEX MATCH workbook here: http://www.xelplus.com/excel-index-and-match-2/ Complete Excel Dashboard course: https://courses.xelplus.com/p/professional-excel-dashboards This video shows another side of the Index function. It uses INDEX to create dynamic lists based on criteria. It also uses the second argument or way of writing of the Index function which uses references as well as area numbers. The index function is then used to create dynamic drop down lists that change based on selected criteria. Name manger is used to keep the formula required for the data validation list. The given name is then directly used inside the data validation. To learn more about Index and match, check out the basic video here: https://youtu.be/F264FpBDX28 and the more advanced video here: https://youtu.be/ontXHp9cwOQ Don't forget to download the free workbook to practice along. ★ My Online Excel Courses ► https://courses.xelplus.com/ ✉ Subscribe & get my TOP 10 Excel formulas e-book for free https://www.xelplus.com/free-ebook/ EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/ Get Office 365: https://microsoft.msafflnk.net/15OEg Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593 GEAR Camera: https://amzn.to/2FLiFho Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Microphone: https://amzn.to/2DVKstA Lights: http://amzn.to/2eJKg1U Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support! #MsExcel
Views: 53965 Leila Gharani

Tenormin 25 mg torrinomedica gentamicina
Bruzol albendazole tabletas de 200mg ibuprofen
Bula capox 25 mg benadryl
Lisinopril 20 mg tabs lupin phrm
Hytrin 2 mg capsule