Leila Gharani

Feb 28, 2017 Excel Charts This is part of my Advanced Online Data Visualization Course: https://courses.xelplus.com/ In this lecture you will learn how you can avoid conditional formatting in Excel and use symbols with formulas instead. This is a good method for cases when your file size is already big and you can't afford to use conditional formatting. Using symbols in Excel instead of conditional formatting is a practical way to include conditional formatting in your spreadsheets without really using the conditional formatting feature. This video also gives you an idea on how to use symbols and special characters in your Excel spreadsheets. ★ 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

Feb 19, 2017 Excel Infographic Chart Templates 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. Note: This technique is a part of my online Dashboard course. ★ Complete Excel Dashboard course: https://courses.xelplus.com/p/professional-excel-dashboards Timestamps 00:00 Why Create a Non-Standard Column Chart in Excel 01:48 Start with Basic Column Chart 02:44 Conditional Formatting of Excel Column Chart for MAX-Value 05:51 Create Round Shape for Excel Column Chart 07:45 Prevent Stretching of Round Shape in Column Chart 10:41 Add Data Labels to Excel Chart 12:45 Add Conditional Formatting to Rounded Column Chart ★ Get the Official XelPlus MERCH: https://xelplus.creator-spring.com/ ⯆ Download the workbook here: http://bit.ly/2rCXXrE 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://www.xelplus.com/courses/ ✉ 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

Feb 13, 2017 Excel Lookup Formulas Check the Excel Essentials Course: https://courses.xelplus.com/p/learn-excel-essentials Three powerful Excel Formulas that lookup values in ranges, i.e. ranges that include lower bound and upper bound values. Method 1: First method uses the SUMIFS formula with greater than and smaller than as criteria. Method 2: SUMPRODUCT formula works great here too as it can work well with exceptions (and condition) Method 3: The next method uses INDEX and Match function to find the category that coincides with the defined boundary values. Instead of looking for the exact match, we will use the option "less than" in the match type argument. Method 4: A fourth option that came to my mind when I was creating the thumbnail for the video was the LOOKUP formula: =LOOKUP(B2,$H$2:$H$7,$G$2:$G$7) Blogpost: http://www.xelplus.com/lookup-within-boundaries/ ★ 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/

February 10, 2017 Excel Intermediate Functions & Features (For ALL Excel Versions) Join 400,000+ professionals in our courses: https://www.xelplus.com/courses/ Quickly learn how SUMIFS & COUNTIFS formulas work - and why they are better than SUMIF & COUNTIF formulas. ★ 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. ★ Get the Official XelPlus MERCH: https://xelplus.creator-spring.com/ Timestamps: 00:00 Why use Excel SUMIFS instead of SUMIF 01:20 Excel SUMIF function 03:10 Excel SUMIFS function 06:06 SUMIFS with wildcards 06:47 SUMIFS with comparison operators 09:17 Excel AVERAGIFS function 09:43 Excel COUNTIF function 10:19 Excel COUNTIFS function 11:50 How to use Excel Tables for dynamic referencing 13:31 Wrap Up ⯆ Download the workbook here: https://www.xelplus.com/sumifs-countifs-averageifs/ 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://www.xelplus.com/courses/ ✉ 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 #Excel #LeilaGharani

Feb 6, 2017 Excel Advanced Formulas & Features Original video from Mike Girvin provided here: https://youtu.be/n09E8VfwWFc Written Instructions: http://www.xelplus.com/index-dynamic-range/ In his video, Mike Girvin at ExcelisFun provides a very elegant and neat solution from Bill Szysz to the following problem lookup problem: . The end result is to come up with a description column in Excel, that includes the number of sub-items within a main item and also provides the description of the main item. This video builds on the existing video by providing an alternate method using the COUNTA, INDEX and MATCH functions. The formula shown here uses Excel's COUNTA function together with the INDEX function to create a dynamic range to count. The index function is used to provide a cell reference to the COUNTA function for the position of the top range as well as the position of the bottom range for each subset. Excel's match function is used to look for or match for the first non empty cell (non blank cell) to provide the location of the top cell address and then the match function is used again to match for the first empty cell to provide that address for the bottom of the range to the COUNTA function. ★ 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/

February 1, 2017 Download the Workbook here: http://bit.ly/2l28CWK Visit Part 1 here: Excel Array Formula: Count Rows with OR condition - SUMPRODUCT & FREQUENCY Functions (Part 1/3) https://rumble.com/v2skesm And Part 2 here: Excel Array Formula: Count Rows with OR Condition - Excel MMULT Function (Part 2/3) https://rumble.com/v2skevq In this video I build on the Matrix Multiplication (MMULT) formula - from Part 2. In the MMULT formula, I used the TRANSPOSE function to be able to dynamically add new columns to the original list. The problem with TRANSPOSE is that it requires Control shift Enter (CSE). In the best case, I'd rather have a formula that doesn't require CSE. In Part 3, I replace the TRANSPOSE part of the MMULT formula with alternate formulas. The idea is to use the ROW function, but somehow get the rows to be dependent on the columns. I first set out to use the ROW and INDIRECT functions. Then I use the ROW, INDIRECT and ADDRESS Function and finally I don't use INDIRECT at all, but instead the ROW and INDEX function (This one became my favorite). The great thing about the INDEX function is that it returns a cell reference instead of a cell value if you use it after the : sign in a formula. (INDEX is an amazing function!). Do look into it in more detail if you don't know it well. ★ 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 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 #Excel #LeilaGharani

Jan 23, 2017 #value In this video I introduce Excel's Matrix Multiplication formula - the MMULT function. In simple steps I explain how you can use MMULT to solve complex problems. Download the Workbook here: https://www.xelplus.com/excel-arrays-count-unique-items-with-mmult-function/ Visit Part 1 here: Excel Array Formula: Count Rows with OR condition - SUMPRODUCT & FREQUENCY Functions (Part 1/3) https://rumble.com/v2skesm And Part 3 here: Excel Array Formula: Count Rows based on OR condition - Excel MMULT, INDIRECT Functions (Part 3/3) https://rumble.com/v2skgg4 In this example I use the MMULT formula to get the number of rows (workshops in this case) that contain at least one of the criteria (companies in this case). The analysis involves multiple criteria with an OR condition. I first explain the basics and the rules you need to follow to use the MMULT formula correctly. If you don't, you formula result will be #value. I then use the SUMPRODUCT, together with MMULT to get unique count by rows. To make it more dynamic, I use the COLUMN function together with TRANSPOSE. In the last video, I showed you how you can use helper cells first and then how to combine SUMPRODUCT with FREQUENCY function to get a one formula array solution. ★ 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/

Jan 18, 2017 Excel Array functions can be difficult to get the hang of. This video explains the logic behind arrays in simple steps while attempting to solve a problem (counting rows based on OR condition). It also shows you a simple method you can use when you need to come up with other complex formulas. Download the workbook here: http://bit.ly/2k3Bo80 Part 2 Video: Excel Array Formula: Count Rows with OR Condition - Excel MMULT Function (Part 2/3) https://rumble.com/v2skevq Part 3 Video: Excel Array Formula: Count Rows based on OR condition - Excel MMULT, INDIRECT Functions (Part 3/3) https://rumble.com/v2skgg4 This video is part 1 of a 3 video series. In each of the videos of the series I use a different Excel array formula to get to the same result. In this video I show you how you can come up with an array formula that counts the rows if at least one of the lookup fields from the criteria list is in that row. It's an OR logical test involving multiple criteria. It shows a great use of the FREQUENCY function, the SUMPRODUCT formula and the ROW function. In addition to solving this problem, I take you through the steps you can take to break down complex array formulas and show you tricks you can use to "follow" your formulas' results which helps you come up with other functions that can help you get to your answer. Series 2 of 3, will introduce you to Excel's MMULT function (Matrix Multipication). ★ 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/

Jan 2, 2017 Excel Advanced Formulas & Features Written Instructions & Workbook Download: http://www.xelplus.com/excel-mod-function/ Simplest explanation of Excel's MOD formula and how it can be used to solve real businesses cases. It explains the MOD function in simple terms and shows you three different practical cases. Excel MOD is shown as follows: 1. Grouping participants into groups of 6, 3 or 4. Which is better? i.e. has less people left over. 2. Reporting quarterly data 3. Controlling the occurrences of data labels in charts. For example, you have a line series and you'd like to show the data label for every 2nd, 3rd or 4th occurrence. Some details about MOD: What does the MOD function do? By definition, it returns the remainder after one number is divided by another. We can classify whole numbers into certain groups. For example, Odd and Even, or numbers that are divisible by two, by three, four, and so on. This is what the MOD function does. It checks if a number is divisible by another number and if not, how far away is it from being divisible. The key here, is that MOD looks backwards - i.e. to the next smaller number that was divisible and then count. MOD works the same with negative numbers. The video gives an example. ★ 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/

Dec 1, 2016 Excel Advanced Formulas & Features This video shows you how to write dynamic YTD formulas in Excel using SUMPRODUCT, OFFSET & SUM functions. Blogpost: http://www.xelplus.com/sumproduct-year-to-date/ Get the full course: https://courses.xelplus.com/ Lecture Summary: In this video, I'll show you a way of writing YTD formulas (in this specific case for volume and price) when your data set goes over a few years. i.e. each month occurs more than once. If you were doing this manually, you'll need to revise your formula in the middle of your data set to make your range start from January of the next year. OFFSET allows you to create dynamic ranges - when you use this with the Month() and SUM() functions, you've created a smart formula that recognizes when the data goes over to the next year and revises your YTD calculation. Calculating, YTD price can be a bit trickier. To get the volume weighted average price, you need to multiply the volume by the price and add each month to the next up to the current month. Then divide that value by the YTD volume. This is when knowing how to use Excel's advanced formulas together can become helpful. Here the SUMPRODUCT function together with OFFSET can come to the rescue. ★ 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 5, 2016 Excel Advanced Formulas & Features OFFSET tells Microsoft 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/ ★ My Online Excel Courses ► https://www.xelplus.com/courses/ 00:00 How to Use the OFFSET Function in Excel 00:46 OFFSET Formula for Dynamic Calculation for the Last 6 Months 10:41 OFFSET Formula for Dynamic Calculation Based on User Selected Date ★ Get the Official XelPlus MERCH: https://xelplus.creator-spring.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://www.xelplus.com/courses/ ✉ 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

Oct 27, 2016 For the complete course click here: https://courses.xelplus.com/ Excel Waterfall Chart Tutorials show you how to design any Waterfall Chart in Excel - In ANY version of Excel too. Ultimate Excel Waterfall Chart course summary: A waterfall or a bridge chart is a way of visualizing data that explains the movement from one balance to another balance. They are common in corporate reports – because they help to easily visualize the factors that are responsible for the change – for example how you got from gross sales to net income or how your profit developed from last year to this year. But they are also a good story teller for non-financial data. In this course you will learn to create different impressive variations of the waterfall from scratch. By the end of this course, you will be able to create your own customized variation of the waterfall chart which you can include in your corporate reports. You’re also going to learn a handful of useful tricks which you can use to create other amazing charts. Your colleagues and management will certainly be impressed. Here is an overview of the topics covered: - Shortcomings of Excel 2016 in-built Waterfall Chart - How to create the easiest Waterfall Chart EVER – (No stacked columns - works with negative values as well) - How to add sub-totals to the waterfall chart - Real business examples of Waterfall charts - How to overcome real-world issues such as - Long category labels on the horizontal axis - Adding dynamic connectors to improve readability - How to deal with very small delta values - Vertical Waterfall chart (bar version of the Waterfall) and creating them in impressive ways - Over 4 hours of Waterfall variations - Workbook included and Cheat sheets to be uploaded soon In this training I’m going to cover different alternatives for creating the waterfall chart. One alternative is obviously the new Excel 2016 standard waterfall, but as we dig deeper we will come across many limitations which will lead us to create it from scratch. That’s why you can take this course, no matter which Excel version you have. The target audience for this course is anyone who is interested in creating different variations of the waterfall in Excel. You might be a controller, financial analyst, someone responsible for creating corporate reports or you might just be interested to improve your Excel charting skills and want to learn advanced tricks to create impressive graphs. Other related videos: https://youtu.be/JOhlazCSo6Q https://youtu.be/a122Aa_PPZ4 https://youtu.be/8hXSAbalZuw Other Chart videos: https://youtu.be/9y2OubgWhpE https://youtu.be/cYHOezDL8SQ https://youtu.be/FPxCKs4OlWE https://youtu.be/5nf8rucx80E https://youtu.be/_U2gbLmEwec

Oct 27, 2016 Ultimate Excel Waterfall Chart course: https://courses.xelplus.com/p/excel-waterfall-chart-course Written Instructions: http://www.xelplus.com/waterfall-chart-business-examples/ Lecture summary: Excel Waterfall Charts: Real Business Examples of Waterfall Charts used in Corporate Reports & When to use Waterfall Charts. i.e. for Which visualization purposes are Waterfall Charts good for. The Waterfall chart is common in finance, but you can use it for any variable or statistic that goes through changes which can be positive or negative depending on the change. Where the waterfall is not so effective is when you have parts-to-whole relationships. (see this video for a better option: http://www.xelplus.com/use-this-instead-of-the-pie-chart/) For which cases is the waterfall chart good for? Two cases: 1. You need to explain a change that occurred from one time period to another. For example how did the earnings change (e.g. EBIT) from last year to this year and which factors where responsible for this change 2. You want to explain how you got from point A to point B within the same time period. For example how you went from gross sales to net income in the same month. The Waterfall is also not just financial figures. You can use it to explain changes in statistical data, for example changes in headcount – how many new employees were hired, how many resignations or retirements your company had during the year. One feature that makes the Waterfall a good story teller is the connectors. These connectors, connect the bars – the delta bars to one another – they have the effect of grouping the bars – which visually indicate that they belong together – they are all a part of the same story. In this video you will see a few different Waterfall charts used in the real business world. Each of these Waterfall charts were created in Excel. You can create these in ANY version of Excel you have. ★ 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!

Oct 18, 2016 #MsExcel For the complete course with different variations of the waterfall chart click here: https://courses.xelplus.com/p/excel-waterfall-chart-course Read the full post here and download the workbook: https://www.xelplus.com/easiest-waterfall-chart-in-excel-from-scratch/ Excel Waterfall Chart from Scratch - This video shows you an easy and flexible method to create the waterfall chart in ANY Excel version. Details: In this specific lecture I will give you an overview of the three important techniques used to generate this chart. The reason I call this the most flexible waterfall chart is because you have control over a lot of features that you don’t have control over if you use the new Excel 2016 waterfall chart. For example, you can change the color of the delta bars to whichever color you like, independent of the color scheme. – You can decide where you want to have the data labels, you can include new data series to add your own customization. It also has no problem to show values that go below the horizontal axis, i.e. if you have negative cumulative values. The data preparation table is also quite simple. This technique does not use the general stacked column chart with invisible base approach, instead it uses error bars and up/down bars to get a very flexible, yet simple waterfall graph. If you’re interested to learn more about the different ways you can use error bars, up/down bars, how to fully control your data labels or even your series labels - and other tricks and techniques in charts, I recommend you take a look at my online excel visualization/chart 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 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

Sep 27, 2016 For the complete course click here: https://courses.xelplus.com/p/excel-waterfall-chart-course For the full blog post refer to this link: http://www.xelplus.com/waterfall-chart-in-excel-2016-advantages-limitations/ Excel 2016 Waterfall Chart - Advantages and Limitations (Disadvantages) - All you need to know about Excel 2016 Waterfall Graph and how to create the waterfall chart from scratch. Details: A waterfall chart or a bridge chart is a way of visualizing your data that helps you understand how you got from one balance to another balance. They are common for financial figures – they allow you to see the profit development – how to you get from gross sales to net income or how your EBIT or earnings developed from last year to this year. But they are also a good story teller for non-financial data. The waterfall chart is also a very liked chart by management. For good reason too. It’s a neat way of presenting your data and explaining the story. But it’s a very difficult chart for people create and maintain. It’s one of the most common charts for which people use additional third-party add-ins for. That was the reason Excel 2016 introduced a built-in waterfall. Extremely user-friendly but not flexible. Same problem you have with many add-ins. Hence the reason for this course. Microsoft Excel 2016 came with a built-in waterfall chart. It made a lot of people happy because it made their life simpler. I was super excited when I heard about it and really impressed how simple it is to create a chart. But then when I started to make adjustments to the chart I realized it’s not that flexible. Basically ease of use came at the cost of flexibility. In this lecture I’m going to show you how to use the Excel 2016 standard waterfall chart. How simple and great it is. I am also gonna go through it’s restrictions and why I still prefer to create the waterfall chart from scratch. Even in Excel 2016. It obviously involves more steps but it comes with great flexibility. The advantages of the Excel 2016 waterfall chart are: Very Easy to Create the new Excel 2016 Waterfall Does not require any special data preparation The disadvantages of Excel 2016 Waterfall Graph are: Chart title cannot reference cells - formula can't be used here Colors can only be controlled via global color scheme Waterfall Connector color cannot be adjusted Data Label position is restricted (not possible to have negative data labels to show above) No other series can be added to the chart - not visible Incorrect display of values in Waterfall when in the negative vertical axis (below horizontal axis) Not all chart functionalities are available Only compatible for anyone using Excel 2016 ★ 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!

Sep 23, 2016 Excel Charts Complete Advanced Excel Chart Course: https://courses.xelplus.com/ Excel Tables: Best Practice to Create Tables for Reporting in Excel Summary: Use Excel data tables when you’d like the reader to lookup individual values and do exact one to one comparisons. Follow these best practices for optimum data table design in Excel: 1. Organize – arrange your categories into groups whenever it makes sense. Items should have a clear sequence. If you're not sure, put yourself in your readers shoes. If you have ratios presented, these should come right after your variables that feed the ratio. If you are showing aggregated values, you should visually separate this from the rest of the data by either adding borders or color to distinguish it from the rest. 2. Focus on data – Keep the focus on your data by keeping the table borders and grids very subtle. Or simply use white space for the grids between data. Use very subtle fill color to assist the reader with horizontal scanning of values for larger data tables. 3. Formatting – Numbers should be right aligned. This way the reader can compare the values with more ease than when they are centered or left aligned. Text on the other hand should generally be left-aligned. Sometimes though when the text is short, you might prefer to center or right align it to fit your table better – so use your judgment here. How you present large numbers, negative and positive values really depend on your country and the organization you work for – i.e. what your readers are used to seeing. Text font should be easy to read, such as Arial or Verdana. 4. Emphasis – If you’d like to bring the readers attention to a certain number, you can use color or change it’s format to bold. If you’d like to bring the attention to a certain section of the table, especially when doing comparisons between one scenario to another, such as budget against actual, you can use symbols. Find more videos on charts here: https://youtu.be/9y2OubgWhpE https://youtu.be/cYHOezDL8SQ https://youtu.be/FPxCKs4OlWE https://youtu.be/5nf8rucx80E ★ 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/

Sep 7, 2016 Excel Advanced Formulas & Features Complete Excel Power Query course: https://courses.xelplus.com/p/excel-power-query *** Note: On XelPlus you will get additional bonuses that are not available on other platforms. Check the course landing page for more info. If you'd like to learn the newest version of this feature check out Power Query. Time Stamps 00:00 How To Properly Import Data to Excel 01:16 How To Use Text to Columns in Excel 03:27 How To Import Data from CSV / TXT files to Excel 09:52 How To Import Data from The Web to Excel Excel has various tools that can help you easily clean up your raw data for further analysis. Apart from using formulas and formatting, there are two useful Excel features that let you easily clean up. One method is text to columns feature and the other to import external data. The Get External Data tool can be used to import and analyse data from other systems. This tool is also useful for importing data to run your dashboards or do further analysis using pivot tables, etc. Usually you can get data extracts from any system (e.g. “.dat” or “.csv” from Oracle, “.xml” files from SAP, etc.). These can be imported into Excel. Importing External Data also gives you control over the decimals and thousand separator, especially if you’re dealing with international companies. In the US, the decimal is dot and the comma is a thousand separator and in most European countries it’s the other way round, where the comma is a decimal and the dot a thousand separator. It’s always safest to Import it properly with the Excel Data Import wizard! After importing press Ctrl + T to create a table out of it. You can create a pivot table off this one, and when you do, even if you add more data to it, the pivot reflects the new data. Importing Data from the Web / Internet into Excel – creating web queries that refresh automatically: In Excel you can also import data directly from the web. You basically create a web query which you set to refresh automatically based on your preference. ★ My Online Excel Courses ► https://www.xelplus.com/courses/ ✉ 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 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

Aug 24, 2016 Excel Advanced Formulas & Features Blogpost: http://www.xelplus.com/excel-debug-formula/ Get the full course: https://courses.xelplus.com/ Video Summary: Once you have mastered Excel functions and you start to use more complex formulas and build more robust spreadsheet, you are likely to run into two issues: 1. You have mistakes in your formulas but because they are too long and complex you can’t find where the problem is 2. The result of your formula throws an error for some cells and you’re not sure why. In this video I will give you a tour of the formula audit tools that are available and how they can help you track and debug your formulas. You will also find out how you can use the "watch window" to keep you eyes on the value of certain cells while you adjust your input values elsewhere. This feature can come in really handy and avoids you having to jump between different Excel tabs and workbooks. ★ 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/

Aug 16, 2016 www.XelPlus.com Master Excel Charts and other advanced Excel features to become better and faster in Excel - you'll benefit, specially if you're in finance - you create corporate reports or you use Excel on the job to report and analyse data. ★ 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/

Aug 9, 2016 Excel Charts Complete Advanced Excel Chart Course: http://www.xelplus.com/advancedexcelchartcourse Download the Excel Workbook Here: http://www.xelplus.com/excel-panel-charts-matrix-excel-charts/ Excel Advanced Charts & Graphs Tutorials: Matrix or Excel Panel Charts - Showing many variables at once Video Summary: When you have too much to show in an Excel Chart don’t do it in a single graph. It would create a color collage and take too much energy from your readers to try to understand the message. What you can do is create what looks like multiple charts and show them horizontally. You can also create chart matrices. Just make sure that you are consistent by using a common axis, size, color, font, spacing etc. Each Excel chart should look like the identical twin of the other, except that it will show a different series or variable. In the example in the video, we have four companies and we’d like to compare the actual profit and the budgeted profit for each company, against one another. We can do this best by showing them as a panel chart. Because the companies are right beside one another and they share a common axis it is very easy to compare. Sequencing is also an important parameter to consider when designing panel charts. If you have a specific sequence in the organization that you’d like to keep, you might want to keep that order. However, if that doesn’t apply you can also consider sequencing your charts in ascending or descending order based on the total yearly profit. It really depends on your audience and your message. To design your panel charts as one chart you will need to have your data in a specific order. It's actually quite easy. Watch the video to find out how. ➹ ➹ ➹ My Online Excel Courses ➹ ➹ ➹ 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 ✉ Subscribe to my Newsletter ✉ Stay informed and get discounts when new courses come out: http://www.xelplus.com/

Jul 15, 2016 Excel Charts 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/ 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

May 31, 2016 Let's take a look at what Excel is good at and what not. Just like with any program, the user should know both Excel’s potential and limitations. Read the blog post here: http://www.xelplus.com/the-limitations-of-excel-versus-bi/ Bigger companies need more complex solutions to handle databases and consolidations but many still rely heavily on Excel as a data reporting and analysis tool. That's why Microsoft introduced it's own self-service BI tools. This short videos shows you Excel's other faces. It's not just a fancy calculator, but can be used to generate professional reports, dynamic dashboards, effective data visualization, process optimization through VBA and super fast analysis. ★ 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/

May 28, 2016 Excel Charts Complete Advanced Excel Chart Course: https://courses.xelplus.com Excel Advanced Charts & Graphs Tutorials: In this lecture you learn how to create a chart that shows your KPI (can be any key figure your are tracking) by geography on a map. The best part is that you can do this on any version of Excel and you can use any map you like. Just make sure you follow these simple steps. Excel 2016 comes with a new feature called 3D maps. It's a fancy new addition to Excel that allows you to present your data on a map and even create a mini movie out of it. But sometimes a simple 2D graphic is the best messenger. This lecture shows you how you can easily show your values (KPIs) on any map. ★ 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/

Apr 13, 2016 Excel Advanced Formulas & Features 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://www.xelplus.com/courses/ ✉ 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 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

Apr 6, 2016 Excel Advanced Formulas & Features Blog post: http://www.xelplus.com/excel-hidden-information/ Get the full course: https://courses.xelplus.com Microsoft Excel: Inspecting Excel Spreadsheets for hidden information In most cases when you start to work at a new company you take over the existing Excel workbooks of your predecessor and continue to update and expand on the workbook. Normally these Excel files are quite large and contain many tabs and it’s not always clear if there is hidden information such as comments, personal information, hidden rows etc in the file. Some hidden information can be deliberate whereas some might be unintentional. To find all this out manually can be time consuming. That’s where you can use the Inspect document feature. A great tool to use before distributing workbooks. ★ 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/