Excel Reverse Lookup Problem: Find Column Header based on Value in Matrix and Row Header
Jul 20, 2017 Excel Lookup Formulas
Download the workbook here: http://www.xelplus.com/excel-reverse-matrix-lookup/
Learn advanced lookup techniques by using Index & Match to lookup a column header based on the values inside the matrix and the row header. In this example, the combination of row header and the values inside a matrix create a unique key, which we can use to find the column header. To solve this Excel problem, we will take a look at INDEX & MATCH and use two sets of INDEX MATCH to get our result.
★ 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
4
views
Excel Pivot Chart with Slicers for Months to Show Values by Weekday Names
Jul 14, 2017 Excel Dashboards & Reports
This type of interactive chart is great for impressive dashboards. Quickly learn how to create an Excel Pivot chart that's driven by pivot slicers.
Download the workbook here: http://www.xelplus.com/pivot-slicer-excel/
In this example I cover two topics:
1. How to get Weekday name from a given date in Excel
2. How to create a Pivot Chart controlled by a Slicer
In addition I show you how you can customize slicers in Excel to better match your report layout.
★ My Online Excel Courses ► https://courses.xelplus.com/
✉ Subscribe & get my TOP 10 Excel formulas e-book for free
https://www.xelplus.com/free-ebook/
EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/
Get Office 365: https://microsoft.msafflnk.net/15OEg
Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593
GEAR
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
11
views
Excel Dynamic Chart Range based on Cell (drop-down) Value for dashboards
Jul 6, 2017 Excel Dashboards & Reports
Improve your Excel dashboards by adding in dynamic charts that allow the user to control what they want to see.
Link to complete course: https://courses.xelplus.com/p/professional-excel-dashboards
Downloadable workbook available inside the course.
In this lecture you will learn how to create a dynamic chart ranges - assume your raw data table is quite big and you'd like the user to specify from which category or date - and to which category & date, they'd like to view the chart data from.
Excel Offset function is one formula that you can use to create dynamic ranges, but Excel's Index formula is a great alternative to the OFFSET function AND it's not a volatile function. Once we've figured out the right INDEX formula, we will copy and paste this into name manager and use the name for our chart range. This will provide us with a dynamic chart that only shows the user the range they'd like to view.
More Index Match videos:
Basics of Index Match: https://youtu.be/F264FpBDX28
Advanced Index Match: https://youtu.be/ontXHp9cwOQ
Index Dynamic Ranges: https://youtu.be/YBehCWjcizg
★ My Online Excel Courses ► https://courses.xelplus.com/
✉ Subscribe & get my TOP 10 Excel formulas e-book for free
https://www.xelplus.com/free-ebook/
EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/
Get Office 365: https://microsoft.msafflnk.net/15OEg
Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593
GEAR
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
8
views
Excel Conditional Formatting with Symbols and Icons (for better reports)
Jun 29, 2017 Excel Advanced Formulas & Features
This video shows you how how you can use your custom Symbols in Excel and conditionally format them to change color depending whether a condition is met or not.
Download the workbook here: http://www.xelplus.com/excel-conditional-formatting-symbols/
The video shows an example of a project timeline and how symbols can be added to notify you on weather the deadline is met in time, whether it's already over due and which tasks are still open.
You can use your own custom symbols in Excel and conditionally format them. You don’t have to choose between the icon sets available to you within Conditional Formatting, but instead choose pretty much any symbol you’d like and conditionally format this instead.
★ 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
10
views
Fully Featured Excel Gantt Chart - actual, planned & percentage completed
Jun 23, 2017 Excel Charts
In this video, learn how to create a more complete Gantt chart that not only shows percentage completed for each task and conditionally formats the progress bars but also allows you to compare actual project plan to planned project plan.
Download the free Excel Gantt chart template and edit to your own needs: https://www.xelplus.com/quick-gantt-chart/
If you want to make a quicker Gantt chart to visualize your project before you start, check out this video: https://youtu.be/QZd3iXcueaI
Gantt chart is a great visualization tool for project planning. Use the template to define the start date for each task as well as the number of working days each task will take. We will use Excel's Work day function to calculate the end date. This way we can account for weekends and holidays. We do two version. One for budget and one for actual. This allows us to switch the view of our gantt chart between actual and budget.
★ 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
44
views
Quick Gantt Chart in Excel (Unusual technique)
Jun 17, 2017 Excel Charts
You need to quickly turn your project plan into a quick Gantt Chart? This video is for you!
Download the Workbook here: http://www.xelplus.com/quick-gantt-chart/
For a more complete version of the Gantt Chart, watch this video: https://youtu.be/KtR-CVxC5qA
Find out how to create a quick gantt chart in Excel. If you're short on time, just download the Excel Gantt chart template in the link provided. Gantt charts are horizontal bar charts used in project management. They show the schedule associated with each task, i.e. when each task begins, how long the task takes and when it will be finished.
Link to Workday function video: https://youtu.be/76BRqJHH4Mk
Link to Networkdays function video: https://youtu.be/uJKFObU0Dug
★ My Online Excel Courses ► https://courses.xelplus.com/
✉ Subscribe & get my TOP 10 Excel formulas e-book for free
https://www.xelplus.com/free-ebook/
EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/
Get Office 365: https://microsoft.msafflnk.net/15OEg
Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593
GEAR
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
22
views
Excel NETWORKDAYS function: Calculate the number of working days between two dates
Jun 14, 2017 Excel Intermediate Functions & Features (For ALL Excel Versions)
Download the workbook here: http://www.xelplus.com/excel-workday-function/
Link to Workday function video: https://youtu.be/76BRqJHH4Mk
If you need to calculate the number of working days based on given start and end dates, you need to use the NETWORKDAYS function.
This is a great Excel formula for anyone planning deadlines and needs to find the number of working days available to them. This function excludes weekends as well. If you use NETWORKDAYS.INTL you can decide which days are considered weekends and should be excluded.
I also show you a way to make the selection for holidays dynamic. For example, if you are doing a project in one country and want to roll out that same project in other countries and you’d like the number of working days between the two dates to consider the holidays of the chosen country – the index function 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
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
4
views
Excel Workday function: Find end date - exclude weekends & holidays
Jun 7, 2017 Excel Intermediate Functions & Features (For ALL Excel Versions)
Download the workbook here: http://www.xelplus.com/excel-workday-function/
Excel's workday function is great when you need to calculate an end date based on a given number of working days - which means you need to exclude weekends and holidays. Some examples are
- creating a project timeline
- creating a reporting timetable
In this video I show you both examples.
Excel's workday function assumes weekends fall on Saturdays and Sundays. In case the weekends in your country fall on other days, you will need to use Workday.intl where you have an additional option to select the weekend days.
★ 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
26
views
Excel Combo Box without VBA - How to create a drop-down list
May 25, 2017 Excel Dashboards & Reports
Get the full course: https://courses.xelplus.com/p/professional-excel-dashboards
Find out how you can insert and use the combo box form control in Excel. This way you can easily insert a drop down box in Excel to add more interactivity to your Excel reports. Your Excel reports will also become more user-friendly. Just make sure you have the developer tab enabled by going to the ribbon options and putting a check mark for the developer tab.
This course is another video from my online Excel 10 hour Dashboard course!
★ My Online Excel Courses ► https://courses.xelplus.com/
✉ Subscribe & get my TOP 10 Excel formulas e-book for free
https://www.xelplus.com/free-ebook/
EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/
Get Office 365: https://microsoft.msafflnk.net/15OEg
Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593
GEAR
Camera: https://amzn.to/2FLiFho
Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161
Microphone: https://amzn.to/2DVKstA
Lights: http://amzn.to/2eJKg1U
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#MsExcel
3
views
5 Design Tips for Better Excel Reports & Dashboards
May 19, 2017 Excel Dashboards & Reports
I take you through 5 design tips that help create better Excel Reports and Dashboards.
Link to Excel Dashboard course: https://courses.xelplus.com/p/professional-excel-dashboards
Full article is here: https://www.xelplus.com/5-design-tips-for-excel-dashboards-reports/
5 tips to create the best Excel dashboard:
1. Create emphasis: Add a strong contrast to headings to show at a glance the KPIs on display in your Excel dashboard.
2. Connect different elements: Show relationships with alignment.
3. Draw parallels and create associations: Two things that should be repeated consistently in your dashboard are abbreviations and color usage.
4. Organise content and create focal points: Think about proximity. What will you choose to group or separate?
5. Balance large elements with smaller elements
★ My Online Excel Courses ► https://courses.xelplus.com/
✉ Subscribe & get my TOP 10 Excel formulas e-book for free
https://www.xelplus.com/free-ebook/
EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/
Get Office 365: https://microsoft.msafflnk.net/15OEg
Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593
GEAR
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
12
views
Excel Charts: How To Show Percentages in Stacked Charts (in addition to values)
May 11, 2017 Excel Charts
Download the workbook here: http://bit.ly/2pnDt5F
Get the full Excel Dashboard course here: https://courses.xelplus.com/p/professional-excel-dashboards
Learn how to add percentages to a stacked chart in Excel. Sometimes you might want to show values for each stack as well as the percentage of each stack - in this video I show you how you can add percentages together with values to each of the stacks.
I also show you how you can add total values to stacks - you can also watch this video that shows that: https://rumble.com/v2st54q
To further improve the readability of this chart you can add the series labels (i.e. the legend) in a dynamic way directly inside the chart. Watch this video to find out how: https://rumble.com/v2st55i
★ 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
5
views
Excel Charts: Stacked Chart Dynamic Series Label Positioning for Improved Readability
May 5, 2017 Excel Charts
Download the workbook here: http://bit.ly/2pnDt5F
Get the full Excel Dashboard course here: https://courses.xelplus.com/p/professional-excel-dashboards
Learn how to add your legend "inside" your Excel chart for improved readability - and of course in a dynamic way.
This technique works great for stacked column charts. Instead of using the default Excel legend, add a dynamic series to the chart that controls the position of the series labels.
To learn how to add total values to a stacked column chart, take a look at this video: https://rumble.com/v2st54q
In the next video, I'll show you how you can add percentages are well as total values to each stack.
Get my complete Advanced Excel Chart Course: https://courses.xelplus.com/p/excel-charts-visualization-secrets-for-impressive-charts
★ My Online Excel Courses ► https://courses.xelplus.com/
✉ Subscribe & get my TOP 10 Excel formulas e-book for free
https://www.xelplus.com/free-ebook/
EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/
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
9
views
Excel Charts: Total Values for Stacked Charts in Excel
Apr 28, 2017 Excel Charts
Download the workbook here: http://bit.ly/2pnDt5F
Learn how to add total values to stacked charts in Excel.
Stacked charts are great for when you want to compare different categories with one another and also show how much each category contributes to the whole.
The problem is that you can show the data labels for each of the stacks, but you cannot immediately show the total value of all the stacks on top of each bar. To do this, you need to follow the steps I show in the video.
★ My Online Excel Courses ► https://courses.xelplus.com/
✉ Subscribe & get my TOP 10 Excel formulas e-book for free
https://www.xelplus.com/free-ebook/
EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/
Get Office 365: https://microsoft.msafflnk.net/15OEg
Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593
GEAR
Camera: https://amzn.to/2FLiFho
Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161
Microphone: https://amzn.to/2DVKstA
Lights: http://amzn.to/2eJKg1U
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#MsExcel
4
views
Excel SUMPRODUCT with Criteria: SUM Alternate Columns based on Header and Criteria
Apr 24, 2017 #MsExcel
Download the free workbook here: http://bit.ly/2osWAXD
Learn how to sum columns based on column header and on criteria in the rows. This example shows you a set of HR data. The aim is to create a report where the user inputs the cost center and selects which information they want to see for the cost center - for example the total salary, benefits or bonus information. The challenge is to SUM based on criteria (cost center) AND based on column header selection.
In the last video I showed you how to use the SUMIFS together with INDEX and MATCH sum based on header and criteria (https://rumble.com/v2st520).
In this video I show you how you can use SUMPRODUCT to get the same result. The formula uses SUMPRODUCT with criteria in the rows and in the columns.
Learn all about the SUMPRODUCT formula here: https://rumble.com/v2sd9km
VBA code from Excel MVP Charles Williams to time your formulas is included here: http://bit.ly/2q1fpSK
★ My Online Excel Courses ► https://courses.xelplus.com/
✉ Subscribe & get my TOP 10 Excel formulas e-book for free
https://www.xelplus.com/free-ebook/
EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/
Get Office 365: https://microsoft.msafflnk.net/15OEg
Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593
GEAR
Camera: https://amzn.to/2FLiFho
Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161
Microphone: https://amzn.to/2DVKstA
Lights: http://amzn.to/2eJKg1U
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
8
views
Excel SUMIFS: Sum Alternate Columns based on Criteria and Header
Apr 21, 2017 Excel Advanced Formulas & Features
Learn how to sum columns based on column header and on criteria.
Download the workbook here: http://bit.ly/2osWAXD
To solve this with SUMPRODUCT: https://rumble.com/v2st52s
This example shows you a set of HR data. The aim is to create a report where the user inputs the cost center and selects which information they want to see for the cost center - for example the total salary, benefits or bonus information. The challenge is to SUM based on criteria (cost center) AND based on column header selection.
The solution shown in the video uses SUMIFS together with INDEX MATCH to provide a moving sum. The 2nd argument of the INDEX function using area numbers is also used to show an alternate solution.
To learn more about the SUMIFS function, check out this video:
https://rumble.com/v2skgke
★ 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
10
views
Excel Arrow Variance Chart: Dynamic Arrows in Chart to Show Change to Previous Year / Budget
Apr 14, 2017 Excel Charts
Quickly learn how to create an Excel "arrow" chart that shows variances (percentage change) using conditional arrows.
Download the free workbook to practice along: http://www.xelplus.com/excel-better-variance-charts-dynamic-columns-lines-arrows/
You can use this excel graph to show the variance of actual values to budget, variance to outlook / forecast, variance to previous year information etc....
In the first video (https://rumble.com/v2skmji), I showed you how you can use conditional formatting in Excel Column or Excel Bar charts. I also showed you how you can conditionally format the data labels in Excel graphs to show a different color if the values are positive to when the values are negative.
In the second video: (https://rumble.com/v2skmns) I showed you how you can use the Excel error bar technique to create really thin bars (or thick lines) which has a more subtle effect. The result is a visually effective chart that doesn't distract the readers attention from the main chart but also communicates the difference to previous year visually as well.
In this video, we build on the error bar technique and disguise them to look like arrows which are conditionally formatted - green to show positive change and red for negative change.
★ 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
23
views
Better Excel Variance Charts to show percentage change (Simple & uncommon technique)
Apr 6, 2017 Excel Charts
Complete Advanced Excel Chart Course: https://courses.xelplus.com/p/excel-charts-visualization-secrets-for-impressive-charts
In a previous video (https://rumble.com/v2skmji), I showed you how you can use conditional formatting in Excel Column or Excel Bar charts. I also showed you how you can conditionally format the data labels in Excel graphs to show a different color if the values are positive to when the values are negative.
What I didn't like about that chart was that it was too bulky - and it took away attention from the main column chart that showed the actual sales numbers.
In this video, I show you how you can use the Excel error bar technique to create really thin bars (or thick lines) which has a more subtle effect. The result is a visually effective chart that doesn't distract the readers attention from the main chart but also communicates the difference to previous year visually as well.
The chart doesn't just use the error bar technique but it also conditionally formats the bars so they are green if the data points are positive and red if the data points are negative.
This technique works for Excel 2007, 2010, Excel 2013 and 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!
#MsExcel
21
views
Excel Variance Charts: Actual to Previous Year or Budget Comparisons
Mar 31, 2017 Excel Charts
In this video I show you how you can use conditional formatting in Excel Column or Excel Bar charts.
Complete Advanced Excel Chart Course: https://courses.xelplus.com/p/excel-charts-visualization-secrets-for-impressive-charts
I also show you how you can conditionally format the data labels in Excel graphs to show a different color if the values are positive to when the values are negative.
The technique in the video shows a variance column chart but it works in the same way for a bar chart.
This technique works for Excel 2010, Excel 2013 and Excel 2016. For Excel 2007 and below, you need to use a different technique. You will need to create two additional series, one for positive number and another for negative numbers and format each series accordingly - and also overlap these by 100%.
Part 2 - Better Variance charts https://rumble.com/v2skmns
Part 3 - Arrow Variance Chart https://rumble.com/v2skmok
Custom formatting is covered in more detail in my online Excel advanced course here: http://www.xelplus.com/onlineAdvancedExcelCourse
★ 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
22
views
Excel INDIRECT Function: Lookup Values in Different Sheets / Excel Tabs
Mar 28, 2017 Excel Lookup Formulas
Easy explanation of Excel's INDIRECT Function - How to use it and when to use it. Also what to watch out for when you're using it.
INDIRECT can be very useful in Excel Dashboards when you need a dynamic cell reference instead of hard-coding the reference in a formula. For example, let's say you have a drop down where the user can select for which year the revenue should be shown. Depending on the selection the formula with a SUMIF or SUMIFS function should sum up different ranges of data. Instead of writing a long formula with different conditions for each year that could be chosen, you can use INDIRECT.
Get the full Excel Dashboard course here: https://courses.xelplus.com/p/professional-excel-dashboards
Indirect can be a confusing function. It takes a little bit time to get the hang of it. What Indirect does, is it returns an address. So for example, if you type in =indirect(A1) and inside A1, you have written A10 - then your formula returns what is inside A10. Why would you need this? Watch the full video and download the workbook to practice along.
Download the workbook here: http://www.xelplus.com/excel-indirect-function/
★ 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/
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
15
views
Excel: Dependent Drop Down Lists with OFFSET, CHOOSE, INDEX & INDIRECT
Mar 23, 2017 Excel Dashboards & Reports
Learn how to create conditional drop-down Excel Validation lists using the INDEX function, OFFSET, CHOOSE and INDIRECT Functions. One aim, many ways to get there. Which one do you like?
Download the free workbook here: http://www.xelplus.com/excel-dependent-drop-down-lists
Complete Excel Dashboard course: https://courses.xelplus.com/p/professional-excel-dashboards
How to create a dependent drop-down when first list contains text with multiple words? Check out this video: https://youtu.be/w7cHgsFirLk
Link to Drop-down lists using INDEX and area numbers:
https://rumble.com/v2skkqy
Link to Excel OFFSET video - Learn the basics of the OFFSET formula:
https://rumble.com/v2skd7u
Lecture summary:
In this video, I'll show you how you can use the OFFSET formula, CHOOSE formula and INDIRECT function to create drop-down lists that are dependent on another drop down list.
★ My Online Excel Courses ► https://courses.xelplus.com/
✉ Subscribe & get my TOP 10 Excel formulas e-book for free
https://www.xelplus.com/free-ebook/
EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/
Get Office 365: https://microsoft.msafflnk.net/15OEg
Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593
GEAR
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
12
views
Excel Simple Dependent Drop down List based on numbers (Index without match)
Mar 16, 2017 Drop Down Lists in Excel (Dependent, Unique Lists and other data validation lists)
Download the free Excel INDEX MATCH workbook here: http://www.xelplus.com/excel-index-and-match-2/
Complete Excel Dashboard course: https://courses.xelplus.com/p/professional-excel-dashboards
This video shows another side of the Index function. It uses INDEX to create dynamic lists based on criteria. It also uses the second argument or way of writing of the Index function which uses references as well as area numbers.
The index function is then used to create dynamic drop down lists that change based on selected criteria. Name manger is used to keep the formula required for the data validation list. The given name is then directly used inside the data validation.
To learn more about Index and match, check out the basic video here:
🎞 How to use Excel Index Match (the right way)
https://rumble.com/v2skkko
and the more advanced video here:
🎞Advanced Excel Index Match (3 Most Effective Formulas for Multiple Criteria)
https://rumble.com/v2skkns
Don't forget to download the free workbook to practice along.
★ My Online Excel Courses ► https://courses.xelplus.com/
✉ Subscribe & get my TOP 10 Excel formulas e-book for free
https://www.xelplus.com/free-ebook/
EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/
Get Office 365: https://microsoft.msafflnk.net/15OEg
Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593
GEAR
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
12
views
Advanced Excel Index Match (3 Most Effective Formulas for Multiple Criteria)
Mar 10, 2017 Excel Lookup Formulas
Join 400,000+ professionals in our courses: https://www.xelplus.com/courses/
This INDEX MATCH Advanced video shows you how you can use Index & Match to lookup multiple criteria. For example when you have more than one header in a column or more than one row header - i.e. you don't have unique fields to match. You might be tempted to do multiple index match formulas inside one another, or use an IF function here. The solution is actually quite simple.
★ Complete Excel Dashboard course: https://courses.xelplus.com/p/professional-excel-dashboards
► How to do Index Match in Excel: To learn the basics behind Excel's Index and Match, watch this video: https://www.youtube.com/watch?v=F264FpBDX28
★ Get the Official XelPlus MERCH: https://xelplus.creator-spring.com/
Timestamps
00:00 Looking Up MULTIPLE Criteria with INDEX & MATCH
04:21 Looking Up Multiple Criteria (Array Solution with CSE)
07:51 Looking Up Multiple Criteria (Array Solution without CSE)
In the video I show you 3 different ways to write the INDEX MATCH formula in Excel. One method uses helper cells to create unique keys for the match function to find.
Method 2, uses array formulas with CSE (Control Shift Enter) to convert the MATCH function to an array function.
Method 3, avoids CSE and also avoids using helper cells in Excel. It does this by using a second INDEX formula inside the match function (INDEX does not require Control Shift Enter).
⯆ Download workbook here: http://www.xelplus.com/excel-index-and-match-2/
★ 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
11
views
How to use Excel Index Match (the right way)
Mar 5, 2017 Excel Advanced Formulas & Features
Join 400,000+ professionals in our courses: https://www.xelplus.com/courses/
Quickly learn all you need to know about INDEX & MATCH to get a quick start.
Complete Excel Dashboard course: https://courses.xelplus.com/p/professional-excel-dashboards
★ Get the Official XelPlus MERCH: https://xelplus.creator-spring.com/
How to do Index Match in Excel: The basics. Watch this video tutorial to find out how you can use Index & Match for more complex lookup problems. This video shows you how Index Match works with simple and to the point explanation. It first shows you how Index works on it's own and then how Match works and then puts the two Excel functions together.
⯆ Download workbook here: http://www.xelplus.com/excel-index-and-match
Timestamps:
00:00 Is INDEX & MATCH the same as VLOOKUP?
00:52 Excel INDEX function explained
05:07 Excel MATCH function explained
06:29 Using INDEX & MATCH together
08:08 Two way lookup with INDEX & MATCH (data validation)
10:30 How to REALLY learn Excel INDEX & MATCH
Why Index Match is better than Vlookup & why use Index Match instead of Vlookup?
Here are 3 reasons why Excel experts generally substitute VLOOKUP with INDEX and MATCH.
1. Unlike VLOOKUP, which searches only to the right, INDEX and MATCH can look in both directions – left and right.
2. INDEX & MATCH can perform two-way lookups by both looking along the rows and along the columns to find the intersection within a matrix.
3. INDEX & MATCH is less prone to errors. Assume you have a VLOOKUP where the final value you want returned is in column N. Your lookup value is in column A. You need to highlight the entire A to N range and then provide your index number to be 14. If you happen to delete any of the in-between columns, you would have to update that index number. You don’t need to worry about this when you use INDEX & MATCH.
All in all, INDEX and MATCH is more flexible than VLOOKUP.
Here are some of the reasons why Excel experts generally substitute VLOOKUP with INDEX and MATCH.
Note: Index match is not case sensitive
Index explained:
The first argument of INDEX is to give it an array. This array (range) should include your answer. You then need to specify how many rows to go down and how many columns to move to find the correct value. You cannot move outside the INDEX range.
The syntax of INDEX is:
• The range where the return value resides.
• Number of rows to move down (the row index)
• Number of columns to move to the right (the column Index). This argument is optional. If you only have one column, you can leave the column argument empty, otherwise, you need to specify the number of columns to move over, in the range.
The MATCH function’s syntax is as follows:
• Like VLOOKUP, Match needs a lookup (target) value. The user can reference a cell or directly type the value into the formula.
• Lookup_array: The “list” (range) where the return values are located.
• Match_type: 0 for an “exact” match.
Index MATCH used together:
When INDEX and MATCH are used together, the MATCH function finds the look up value’s row / column index and then hands this value off to the INDEX function to get the lookup value.
★ My Online Excel Courses ► https://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
39
views
Excel VLOOKUP: Basics of VLOOKUP and HLOOKUP explained with examples
Mar 5, 2017 Excel Lookup Formulas
Join 400,000+ professionals in our courses: https://www.xelplus.com/courses/
Find out how to do a VLOOKUP in Excel and for what situations you can use Excel's Vlookup and Hlookup formulas for.
★ Get the Official XelPlus MERCH: https://xelplus.creator-spring.com/
Timestamps
00:00 Basics of Excel VLOOKUP and HLOOKUP
00:57 How to use Excel VLOOKUP
07:14 How to use Excel HLOOKUP
09:59 Limitation of Excel VLOOKUP and HLOOKUP
The Excel VLOOKUP formula can search for a specific category or value in an Excel data table and return the “opposing” value from adjacent columns. HLOOKUP function works exactly in the same way except that it looks horizontally rather than vertically.
“V” in VLOOKUP refers to vertical and “H” in HLOOKUP refers to horizontal. If the data in your table has column headers, use the VLOOKUP function. If the data table has row headers and you need to search horizontally across the rows for a “match” then use HLOOKUP formula.
Vlookup formulas do not need to have sorted lists. If your Excel table is unsorted make sure you look for an exact match by selecting "false" in the last argument of Vlookup.
One major limitation of VLOOKUP is that it cannot look to the left. The values to lookup must always be on the left-most column of the range and the values to return must be on the right hand side.
For two-way lookups - If you need to look up values across rows and also columns or multiple rows and columns the best function to use is the Index and Match function. Watch this video to learn more: https://youtu.be/F264FpBDX28
★ My Online Excel Courses ► https://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
26
views
Excel Tables: Use Smart Conditional Formatting for great table layout
Mar 2, 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 use conditional formatting smartly to highlight negative and positive differences based on a threshold. The method used here is one of my favorite conditional formatting methods in Excel.
This way you can improve readability of your Excel tables that are used for corporate reporting.
★ 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
7
views