Leila Gharani

Jan 3, 2018 Excel Dashboards & Reports Easy steps to create a clustered stacked column chart in Excel - this chart is both stacked and clustered. This type of chart is not available in the standard Excel chart selection. With a few steps, you'll learn how to create this from scratch. One way of creating a clustered and stacked column chart combination is to use both the primary and secondary Y-axis. One problem you'll run into is that they will likely have a different minimum and maximum which will show the wrong visualization. You need to ensure that both the primary and secondary y-axis have the same minimum and maximum in a dynamic way (without manually fixing these values). I show you the trick on how you can set this up. 🡻 Download the workbook here http://www.xelplus.com/excel-clustered-column-and-stacked-chart/ Links to related videos: Charts Basics: https://youtu.be/DAU0qqh_I-A Dynamic Charts based on drop-down selection: https://youtu.be/22jcw5slQJk ★ 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 Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Main Camera: https://amzn.to/3a5ldBs Backup Camera: https://amzn.to/2FLiFho Main Lens: https://amzn.to/39apgeD Zoom Lens: https://amzn.to/3bd5pN7 Audio Recorder: https://amzn.to/2Uo5rLm Microphone: https://amzn.to/2xYy9em 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

Dec 20, 2017 Excel Charts Easy steps to create a clustered stacked column chart in Excel - this chart is both stacked and clustered. This type of chart is not available in the standard Excel chart selection. With a few steps, you'll learn how to create this from scratch. One way of creating a clustered and stacked column chart combination is to use both the primary and secondary Y-axis. One problem you'll run into is that they will likely have a different minimum and maximum which will show the wrong visualization. You need to ensure that both the primary and secondary y-axis have the same minimum and maximum in a dynamic way (without manually fixing these values). I show you the trick on how you can set this up. 🡻 Download the workbook here http://www.xelplus.com/excel-clustered-column-and-stacked-chart/ Links to related videos: Charts Basics: https://youtu.be/DAU0qqh_I-A Dynamic Charts based on drop-down selection: https://youtu.be/22jcw5slQJk ★ 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 Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Main Camera: https://amzn.to/3a5ldBs Backup Camera: https://amzn.to/2FLiFho Main Lens: https://amzn.to/39apgeD Zoom Lens: https://amzn.to/3bd5pN7 Audio Recorder: https://amzn.to/2Uo5rLm Microphone: https://amzn.to/2xYy9em 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

Dec 14, 2017 Excel Advanced Formulas & Features In this video I show you two ways to reset the week number to start from 1 at the beginning of every month. 1st case: We use Excel's WEEKNUM function to start from 1 every time we reach the 1st of the next month. 2nd case: We reset the week number to 1 every time we reach the first Monday of the next month. Here we use Excel's WEEKDAY function as the main formula driver. In addition you will learn how to: 1. Get Week Number from a date with the WEEKNUM formula 2. How to Get the Day name from a date using the WeekDay function 3. How to use the DATE function (Year, Month, Day) 🡻 Download the workbook here: http://www.xelplus.com/reset-week-number-every-month-excel/ Links to related videos: Mike's Magic Trick 783: Date Functions & Formulas: https://youtu.be/VvIvINcn79I Workday function explained: https://youtu.be/76BRqJHH4Mk NetWorkDays explained: https://youtu.be/uJKFObU0Dug Quick Gantt Chart in Excel: https://youtu.be/QZd3iXcueaI ★ 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

Dec 7, 2017 Excel Charts 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://rumble.com/v2skkko Excel Charts Basics: https://rumble.com/v2tul8o ★ 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 Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Main Camera: https://amzn.to/3a5ldBs Backup Camera: https://amzn.to/2FLiFho Main Lens: https://amzn.to/39apgeD Zoom Lens: https://amzn.to/3bd5pN7 Audio Recorder: https://amzn.to/2Uo5rLm Microphone: https://amzn.to/2xYy9em 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

Nov 30, 2017 Excel Charts In this Excel Line Chart tutorial, you learn four tricks: 1. How to prevent your line series to drop to zero 2. How to create dynamic charts based on a Point of View (in this case month selection) 3. How to integrate your legend inside the chart - i.e. at the end of the line series for improved readability and... 4. How to hide or make error values in cells, invisible with conditional formatting. 🡻 Download the workbook here: http://www.xelplus.com/excel-line-charts-prevent-drop-to-zero-dynamic-legend/ Links to related videos: Basics of Excel Charts: https://youtu.be/DAU0qqh_I-A Matrix charts in Excel: https://youtu.be/5nf8rucx80E Fully Playlist on Charts: https://www.youtube.com/playlist?list=PLmHVyfmcRKyyEj7oQkCf7TL9yQQWXbGOQ ★ 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

Nov 23, 2017 Excel Charts Learn the basics of Excel charts to be able to quickly create graphs for your Excel reports. The type of Excel charts covered are column, bar, line and a combination of column and line chart. Use these charts to make comparisons or show trends. You'll also learn how to create combination charts to show more than one variable or multiple data sets. ★ My Online Excel Courses ► https://www.xelplus.com/courses/ 00:00 How to Create Charts in Microsoft Excel 00:28 How to insert an Excel Chart 03:30 How to Adjust / Edit the Chart 06:28 Tips to Create Professional Excel Charts 07:50 How to Add More Series to an Excel Chart 10:10 How to Create Combination Charts (Column with Line Chart) ★ Get the Official XelPlus MERCH: https://xelplus.creator-spring.com/ ⯆ Written instructions: http://www.xelplus.com/charts-basics/ Links to related videos: Chart Playlist on YouTube ► https://www.youtube.com/playlist?list=PLmHVyfmcRKyyEj7oQkCf7TL9yQQWXbGOQ Using shapes in charts (non-standard graphs): https://youtu.be/C9beIaL-cBA ✉ 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/ GEAR I use: https://www.xelplus.com/resources/#gear More resources on my Amazon page: https://www.amazon.com/shop/leilagharani Let’s connect on social: Instagram: https://www.instagram.com/lgharani Twitter: https://twitter.com/leilagharani LinkedIn: https://at.linkedin.com/in/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

Nov 17, 2017 Excel Lookup Formulas 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? ★ My Online Excel Courses ► https://www.xelplus.com/courses/ 00:00 Lookup Values in Multiple Worksheets in Excel 02:15 VLOOKUP Across Multiple Worksheets 07:43 INDEX & MATCH Across Multiple Worksheets 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. ★ Get the Official XelPlus MERCH: https://xelplus.creator-spring.com/ 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 ✉ 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/ GEAR I use: https://www.xelplus.com/resources/#gear More resources on my Amazon page: https://www.amazon.com/shop/leilagharani Let’s connect on social: Instagram: https://www.instagram.com/lgharani Twitter: https://twitter.com/leilagharani LinkedIn: https://at.linkedin.com/in/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

Nov 16, 2017 #MsExcel In this video I share some recent news and updates regarding my videos and courses. Please leave any video suggestions in the comments below. Links to Resources that helped me become Better in Excel ExcelIsFun - https://www.youtube.com/user/ExcelIsFun Bill Jelen - https://www.youtube.com/user/bjele123 Chandoo - https://chandoo.org/ MyOnlineTraining Hub - https://www.myonlinetraininghub.com/ TheSpreadSheetGuru - http://www.thespreadsheetguru.com Contextures - http://www.contextures.com Trump Excel - http://trumpexcel.com Exceljet: https://exceljet.net Mr Excel - http://www.mrexcel.com Oz du Soleil - https://www.youtube.com/user/WalrusCandy Kevin Lehrbass - https://www.youtube.com/user/MySpreadsheetLab Bill Szysz - https://www.youtube.com/channel/UCx_joCOEjU1KdTpPvu4baYA I have probably forgotten some great websites. If you have a resource to share, let me know and I will add to this list. Excel / VBA Books: Control + Shift + Enter - http://amzn.to/2yI5aXv Excel 2013 Power Programming with VBA: http://amzn.to/2jtVtJN Power Excel with Mr. Excel : http://amzn.to/2hAN8Dl ★ 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

Nov 9, 2017 Excel Dashboards & Reports 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

Nov 1, 2017 Excel Dashboards & Reports 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 Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Main Camera: https://amzn.to/3a5ldBs Backup Camera: https://amzn.to/2FLiFho Main Lens: https://amzn.to/39apgeD Zoom Lens: https://amzn.to/3bd5pN7 Audio Recorder: https://amzn.to/2Uo5rLm Microphone: https://amzn.to/2xYy9em 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

Oct 26, 2017 #MsExcel Check the Excel Essentials Course: https://courses.xelplus.com/p/learn-excel-essentials In this case study we take a look at the 2015 Annual Report from BMW and look at ways we could improve on some of the tables and graphs used in the report. In this specific video we take a look at presenting data in Excel Tables and how we can better organize the data, headers, distance etc to create tables that are easy to read. We also take a look at using conditional formatting in our report such as data-bars to bring attention to deviations - especially the bigger changes. An alternative approach is to use custom formatting and use symbols and color to bring attention to the positive and negative deviations. You'll see some neat tricks here that allow you to better organize your data in tables so that attention is directed to areas that need attention most. This makes the entire report easier to read and digest. ⯆ Download the workbook here: http://www.xelplus.com/report-makeover-excel-better-excel-tables/ Link to BMW 2015 Annual report: http://bit.ly/BMWPDF2015 Link to Video 1 - Table or Chart? https://rumble.com/v2tnkxm Link to Video 2 - Alternative to Doughnut Charts: https://rumble.com/v2tnky0 More on Custom Formatting: https://rumble.com/v2tnek0 ★ 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

Oct 18, 2017 #MsExcel Check the Excel Essentials Course: https://courses.xelplus.com/p/learn-excel-essentials In the 2nd part of our corporate report makeover in Excel, I show you a better alternative to Doughnut (Donut) charts in Excel. Doughnut graphs are very similar to Pie Charts except they use less ink. A better alternative to the Doughnut chart is a sorted bar chart. In this video I show you how you can set up your data preparation table in Excel using the Rank Function to automatically sort the data and categories in descending order. I also show you how to hard-proof your formulas to avoid duplicate ranks - i.e. to ensure we have unique ranks in the data set. ⯆ Download the workbook here: http://www.xelplus.com/excel-report-makeover-alternative-for-doughnut-charts/ Link to Part 1: https://rumble.com/v2tnkxm Link to Part 3: https://rumble.com/v2tujke Link to Learn Index & Match: https://rumble.com/v2skkko Link to BMW 2015 Annual report: http://bit.ly/BMWPDF2015 ★ 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

Oct 13, 2017 #MsExcel Check the Excel Essentials Course: https://courses.xelplus.com/p/learn-excel-essentials In this case study we take a look at the 2015 Annual Report from BMW and look at ways we could improve on some of the tables and graphs used in the report. Of course I will use Microsoft Excel for this. It's sometimes difficult to decide which visualization to use for which data sets. Specially when it comes to visualizing financial numbers in corporate reports. Sometimes it helps to test different versions - which you can easily do in Excel and then put them side by side. Or give the different versions as handouts to your team and decide which is the easiest and fastest to read. ⯆ Download the workbook here: http://www.xelplus.com/bmw-report-makeover-graph-versus-table/ View Part 2: Alternative to Donut charts https://rumble.com/v2tnky0 View Part 3: Better Excel Tables https://rumble.com/v2tujke In this video we take a look at a specific table which compares sales volume of 2015 by Mini model with previous year's values. In addition it shows the proportion of sales volume for each Mini Model as compared to the total. There is a lot of information in the table. Could we show this better with a chart? or should we stick to a table? Link to BMW 2015 Annual report: http://bit.ly/BMWPDF2015 ★ 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

Oct 4, 2017 Excel Dashboards & Reports Download the workbook here: http://www.xelplus.com/symbols-in-pivot-slicers In this video, you'll learn a new Excel Pivot Slicer trick. It'll show you how to customize the look of some of your slicers to show symbols instead of text. It'll also show you how to group the contents in your slicer. We use a sample HR data that has information on salaries for male and female employees by cost center. Our task is to create a dynamic dashboard report that uses symbols for gender and also for part time and full-time employees. ★ 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

September 28, 2017 Excel Advanced Formulas & Features Excel’s Solver tool is an optimization package. It finds the optimal solution to a problem by changing multiple variables. 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. ★ My Online Excel Courses ► https://www.xelplus.com/courses/ 00:00 Define and Solve a Problem by Using Excel Solver 00:15 Solve Problems in Excel with 2 or More Variables 06:56 Solve What-If Problems with Constraints ► Download the workbook here: http://www.xelplus.com/excel-solver-example 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://rumble.com/v2tnkui ✉ 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 Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Main Camera: https://amzn.to/3a5ldBs Backup Camera: https://amzn.to/2FLiFho Main Lens: https://amzn.to/39apgeD Zoom Lens: https://amzn.to/3bd5pN7 Audio Recorder: https://amzn.to/2Uo5rLm Microphone: https://amzn.to/2xYy9em Lights: http://amzn.to/2eJKg1U More resources on my Amazon page: https://www.amazon.com/shop/leilagharani Let’s connect on social: Instagram: https://www.instagram.com/lgharani Twitter: https://twitter.com/leilagharani LinkedIn: https://at.linkedin.com/in/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

September 21, 2017 Excel Advanced Formulas & Features 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://rumble.com/v2tnkva ★ 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 Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Main Camera: https://amzn.to/3a5ldBs Backup Camera: https://amzn.to/2FLiFho Main Lens: https://amzn.to/39apgeD Zoom Lens: https://amzn.to/3bd5pN7 Audio Recorder: https://amzn.to/2Uo5rLm Microphone: https://amzn.to/2xYy9em 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

September 15, 2017 Excel Introduction - Learn Excel Basics Check the Excel Essentials Course: https://courses.xelplus.com/p/learn-excel-essentials Watch to find out how you can set your default Excel theme to the theme of your choice. An Excel theme is defined by the combination of color, font and effects. You can define combinations of these and save the combination as a theme. These themes are available throughout the Office applications. In Excel it's not as easy as in Word to set a theme as default, so that every time you open a new Excel Workbook, it takes your own theme instead of the default Office theme. You just have to save an empty spreadsheet with your theme as an Excel template and save it in the XLSTART folder. Blog post: http://www.xelplus.com/default-theme-in-excel/ ★ 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 Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Main Camera: https://amzn.to/3a5ldBs Backup Camera: https://amzn.to/2FLiFho Main Lens: https://amzn.to/39apgeD Zoom Lens: https://amzn.to/3bd5pN7 Audio Recorder: https://amzn.to/2Uo5rLm Microphone: https://amzn.to/2xYy9em 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

September 12, 2017 Excel Charts Download the workbook here: http://www.xelplus.com/skip-dates-in-excel-charts/ In this video I show you how to dynamically ignore blank dates in charts. This trick doesn't just apply to dates, but to any data set that has gaps in it and you'd like Excel to completely ignore the gaps, even for cases where you have values in front of the gaps. This video shows you how to use the Aggregate functions, Index & Match and Name Manager to get the desired result automatically. Links to related videos: Part 1: Manual approach to removing gaps in charts: https://rumble.com/v2tni3q Index & Match basics: https://rumble.com/v2skkko Index & Match advanced: https://rumble.com/v2skkns Changing Chart Ranges with Index: https://rumble.com/v2tncdg ★ 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

Sep 7, 2017 Excel Charts Download the workbook here: http://www.xelplus.com/skip-dates-in-excel-charts/ For Part 2: Dynamic approach using formulas: https://rumble.com/v2tni5o In this video I show you how to create gaps on the horizontal axis of a line chart when the data values are empty - i.e. contain blank cells. Excel normally fills the empty dates on the x-axis and connects the line (where the gap should be). How do you avoid this? How do you skip dates in Excel chart axis? Here is one method that shows you that. ★ 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

August 31, 2017 Excel Lookup Formulas Download the workbook here: http://www.xelplus.com/complex-lookup-index-sumproduct/ Watch this video to find out the method used by Bill Szysz to get multiple lookup results in Excel. This example builds on the example shown in the previous videos which has a matrix of apps which belong to different divisions. The apps are the values inside the matrix and the divisions are the headers. The aim is to find the header value based on the matrix value. Since the data in this example is not unique and an app can belong to more than one division, the aim is to come up with a formula that finds all the divisions one app could belong to. Excel's TEXTJOIN function is a great solution to this problem. On top, when combined with the Find & Isnumber function, it can help us avoid duplicate results. Watch this video as I take you through Bill's formula solution. ✔ Alternate Solutions ✔ My version with Index & Sumproduct: https://rumble.com/v2tnep2 My with Index & Aggregate: https://rumble.com/v2tneqm 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 ★ 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

Aug 24, 2017 Excel Lookup Formulas Download the workbook here: http://www.xelplus.com/complex-lookup-index-sumproduct/ Watch this video to find out my method of getting multiple lookup results in Excel. This example builds on the example shown in the previous video which has a matrix of apps which belong to different divisions. The apps are the values inside the matrix and the divisions are the headers. The aim is to find the header value based on the matrix value. Since the data in this example is not unique and an app can belong to more than one division, the aim is to come up with a formula that finds all the divisions one app could belong to. ✔ 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 Another option that was suggested by Bill Szysz is: =TEXTJOIN(",", ,IF(H5=$B$5:$D$45,$B$4:$D$4,"")) ★ 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

August 18, 2017 Excel Lookup Formulas 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://rumble.com/v2skkko To understand Sumproduct: https://rumble.com/v2sd9km ✔ 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://rumble.com/v2tneqm ★ 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 Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Main Camera: https://amzn.to/3a5ldBs Backup Camera: https://amzn.to/2FLiFho Main Lens: https://amzn.to/39apgeD Zoom Lens: https://amzn.to/3bd5pN7 Audio Recorder: https://amzn.to/2Uo5rLm Microphone: https://amzn.to/2xYy9em 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

Aug 8, 2017 Excel Dashboards & Reports In this video I share two more ways you can use custom formatting instead of conditional formatting in Excel. Download the workbook here: http://www.xelplus.com/smart-uses-of-custom-formatting-part-2/ Method #3: Use any symbol of your choice to show deviations and conditionally format these using custom formatting Method #4: Use custom formatting to only show higher or lower deviations, i.e. use thresholds in custom formatting. The general custom formatting syntax that most are familiar with, is to format positive, negative, zero and text values. But you can also use custom formatting to format higher, lower and medium boundaries. In this video you'll see how that works. For part 1 watch: https://rumble.com/v2tnek0 For part 3 watch: https://youtu.be/qKpxEl-_IQ8 - you'll find out how you can account for 4 conditional thresholds. For symbols with conditional formatting watch: https://rumble.com/v2tmujc ★ 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 Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161 Main Camera: https://amzn.to/3a5ldBs Backup Camera: https://amzn.to/2FLiFho Main Lens: https://amzn.to/39apgeD Zoom Lens: https://amzn.to/3bd5pN7 Audio Recorder: https://amzn.to/2Uo5rLm Microphone: https://amzn.to/2xYy9em 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

Aug 2, 2017 Excel Dashboards & Reports Custom formatting is really easy to use once you understand the rule behind it. 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). ★ My Online Excel Courses ► https://www.xelplus.com/courses/ Timestamps: 00:00 Why Use Excel Custom Formatting over Conditional Formatting 04:02 Basics of Excel Custom Formatting 08:05 Up/Down Arrows for Deviations with Excel Custom Formatting 12:32 Up/Down Arrows and Percentage Deviations with Excel Custom Formatting ★ Get the Official XelPlus MERCH: https://xelplus.creator-spring.com/ ⯆ Download the workbook here: http://www.xelplus.com/smart-uses-of-custom-formatting/ For Part 2 watch: https://rumble.com/v2tnemq 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. 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 ✉ 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/ GEAR I use: https://www.xelplus.com/resources/#gear More resources on my Amazon page: https://www.amazon.com/shop/leilagharani Let’s connect on social: Instagram: https://www.instagram.com/lgharani Twitter: https://twitter.com/leilagharani LinkedIn: https://at.linkedin.com/in/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

Jul 27, 2017 Excel Charts Download the workbook here: http://www.xelplus.com/stacked-column-chart-with-connectors/ In this video you will learn how to add series lines to stacked column charts. Instead of series lines, you might want to add a shaded background to each of the stacked sets. We can do that using stacked area chart and combine it with our stacked column chart in Excel. ★ 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