Use AI in Excel to Analyze Data with ONE Click
9 April 2020 Excel Dashboards & Reports
Analyze data in Microsoft Excel will provide you with immediate insights about your data. It uses artificial intelligence to quickly create summary reports and high-level visualizations. You can even directly ask it questions about your data using "normal" conversational language. Analyze data, is the new artificial intelligence feature built into Excel. It does a really good job in analyzing your data and giving you a summary.
Analyze data/Ideas feature is available in Excel for Office 365.
Simply click a cell inside your data range, and then click the Ideas button on the Home tab. Ideas in Excel will analyze your data, and return interesting visuals about it in a separate task pane.
Save time and narrow down your Ideas by selecting only the fields you want to see. You can also provide feedback about which fields are helpful to you and which are not. This way the system learns and will present fewer, more targeted suggestions in the future.
⯆ Read the full article here: https://www.xelplus.com/ideas-in-excel
Excel Office 365 Dynamic arrays: https://www.youtube.com/playlist?list=PLmHVyfmcRKyyPFY31LldHWcJdLzGUPTSP
★ My Online Excel Courses ► https://www.xelplus.com/courses/
✉ Not sure which of my Excel courses fits best for you? Take the quiz: https://www.xelplus.com/course-quiz/
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 #Excel #LeilaGharani
24
views
Uncover Windows 10 Most Useful Features Today
2 April 2020 #Office365 #windows10
The first 500 people who click the link will get 2 free months of Skillshare Premium: https://skl.sh/xelplus9
In this video I'll share my favorite Windows 10 tips and tricks to make the most of Microsoft Windows. Some of these are what I consider hidden Windows features and they make a big difference in my daily work. Below is a quick summary:
00:00 Top 10 Tips and Tricks for Windows 10
00:56 Clipboard History: Enjoy next level pasting that's not limited to the last copied item
03:15 Sandbox: Safely test applications and websites in a separate virtual testing environment
05:35 Virtual Desktop. Keep business and private separate with virtual desktop
07:18 Hide Desktop Icons: Clean up and hide your desktop icons with one click
07:53 Focus Assist: Eliminate distractions and get in the zone
09:30 Quick Assist: Easily give and get assistance by creating a remote connection
10:47 Aero Shake: Quickly minimize opened windows except the one you want to focus on
11:34 Snap Your Windows: Look at multiple windows with split screen
12:26 Night Light: Improve your sleep by reducing blue light
13:32 Snip and Sketch: Capture your screen in various ways and add annotations
★ Get the Official XelPlus MERCH: https://xelplus.creator-spring.com/
⯆ Read the full Windows 10 top tips and tricks Article here: https://www.xelplus.com/windows-10-top-tips-tricks
★ My Online Excel Courses ► https://www.xelplus.com/courses/
✉ Not sure which of my Excel courses fits best for you? Take the quiz: https://www.xelplus.com/course-quiz/
RESOURCES I Recommend: https://www.xelplus.com/resources/
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
Many thanks to Skillshare for sponsoring this video.
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!
#Windows10 #Office365 #MsExcel #Excel #LeilaGharani
124
views
Create Multiple Dependent Drop-Down Lists in Excel (on Every Row)
26 March 2020 Drop Down Lists in Excel (Dependent, Unique Lists and other data validation lists)
Join 400,000+ professionals in our courses: https://www.xelplus.com/courses/
Learn how you can create MULTIPLE dependent data validation lists in Excel on the SAME sheet. In this video I'll show you a solution that requires ONE single formula. This formula also excludes blank cells on the bottom of the data validation list.
★ My Online Excel Courses ► https://www.xelplus.com/courses/
In our example the selection of a division will drive the available choices for an app in an Excel data validation list. We'll use the Excel OFFSET function and the MATCH function for the dependent drop down selection. This way you can have as many categories as you like.
★ Get the Official XelPlus MERCH: https://xelplus.creator-spring.com/
00:00 Multiple Dependent Drop Down Lists in Microsoft Excel
05:55 Fixed Number of Choices in the Dependent Drop-Down
07:15 Dynamic number of Choices in Drop-Down
The challenge we have is that the number of choices inside the dependent drop down may vary depending on the division. I'll show you 2 solutions for this:
Fixed number of Choices in the dependent drop-down
If you do not mind empty spaces (blank cells) at the bottom of the drop down you can fix the height argument in the OFFSET function to a specific (maximum) number of choices.
Dynamic number of Choices in Drop-Down
If we want to restrict the number of visible cell values inside the dependent drop-down we can expand the formula to get rid of the blank cells on the bottom. This way we can eliminate empty spaces in the drop down selection. We will use another OFFSET formula inside a COUNTA formula to dynamically count the number of available choices.
⯆ DOWNLOAD the workbook here: https://www.xelplus.com/excel-dependent-drop-down-every-row
LINK to related OFFSET video: https://youtu.be/RPTQjbk2qy4
Complete Excel Data Validation Playlist: https://www.youtube.com/playlist?list=PLmHVyfmcRKywYhC1Q9eZqR7D-_cdiwl6y
✉ Not sure which of my Excel courses fits best for you? Take the quiz: https://www.xelplus.com/course-quiz/
RESOURCES I Recommend: https://www.xelplus.com/resources/
More resources on my Amazon page: https://www.amazon.com/shop/leilagharani
Time Stamps:
00:00 Dependent Drop-Down Lists in Excel on EVERY Row
01:20 Creating the First Drop Down
01:58 Creating the Dependent Drop Down List
07:16 Eliminate Empty Rows in Drop Down List
10:21 Copy Down Data Validation
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
110
views
How to Create an Excel Interactive Chart with Dynamic Arrays
19 March 2020 Excel Dashboards & Reports
Quickly create an automatically sorted Excel bar chart that ALSO lets you hide and show categories on the chart based on a flag in the cell. It's super easy with Excel dynamic arrays - specifically with the new Excel FILTER and SORTBY functions.
This is especially useful for Excel dashboards to include interactive charts depending on a selection.
The solution will be fully dynamic, i.e. we will not use manual filter or manual hiding. We will even take it another step further and automatically sort the Excel chart based on the visible sales data.
To get that setup we will use Excel's new Dynamic Array functions exclusively available in Office
365.
To summarize what you learn:
1. How to use Excel Dynamic Array referencing in charts
2. How to use Excel FILTER & SORTBY functions
3. How to Sort categories based on their values
Step 1: Data Preparation Table (01:26)
Whenever the chart has a different setup than the source data, a data preparation table is needed in between. We will use the new Excel FILTER function for that. In order to get the chart properly sorted we will apply the new Excel SORTBY function. This function is used when the sorting criteria is not in the result. The tricky part is to ensure that the array for the sorting (by_array1) has the same size and order as the array for the visible sales data. We will solve this by nesting another FILTER function in the formula. To get the respective sales data we'll use a simple VLOOKUP.
Step 2: Excel Chart (06:30)
For the visualization we'll use a column chart that's connected to the data preparation table. However, we want the Excel chart area to update automatically based on the number of data points in the data preparation table. In other words, we want the chart to be dynamic and to include any new selections from the data preparation table and exclude any blank areas.
Excel Chart references can either take direct references or they can take names. Therefore, to keep the chart dynamic we'll use names in the Name Manager and we'll use the "#" for the reference to spill. Then we'll update the Series Values and the Axis Labels of the chart with the names we created.
Step 3: Dynamic Dashboard (09:38)
This technique can easily be used to create dynamic dashboards. Based on a dropdown selection the chart is automatically updated. I used the Excel IF and SUMPRODUCT functions to help with that. In order to make sure that new data is added to the chart I converted the data into an official Excel table.
By combining the power of Excel tables and the new Excel Dynamic Arrays such dashboards can be created really fast.
00:00 Intro
01:26 Prepare Data Table
06:30 Create Excel Chart
09:38 Setup Dynamic Excel Dashboard
⯆ DOWNLOAD the workbook here: https://www.xelplus.com/excel-da-dynamic-sorted-chart
LINKS to Excel Dynamic Arrays Playlist: https://www.youtube.com/playlist?list=PLmHVyfmcRKyyPFY31LldHWcJdLzGUPTSP
Excel Dashboards & Reports Playlist: https://www.youtube.com/playlist?list=PLmHVyfmcRKyz_4TwSfxzN_gsRtg3ODzyx
★ My Online Excel Courses ► https://www.xelplus.com/courses/
✉ Not sure which of my Excel courses fits best for you? Take the quiz: https://www.xelplus.com/course-quiz/
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 #Excel #LeilaGharani
86
views
Complete: Excel Hidden SHORTCUTS to Select Data Column including Blanks
17 March 2020 Excel Hacks: Tips & Tricks
Here is the Complete Version: Select the correct shortcut combination based on how your data is organized. Thank YOU for your contribution.
Topic: Different methods to select a column of data in Excel including blanks.
Link to Part 1: https://youtu.be/bI2SbywH8yU
★ My Online Excel Courses ► https://www.xelplus.com/courses/
✉ Not sure which of my Excel courses fits best for you? Take the quiz: https://www.xelplus.com/course-quiz/
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 #Excel #LeilaGharani
27
views
Excel Hidden Shortcut to Select Data Column including Blanks
12 March 2020 Excel Hacks: Tips & Tricks
The first 500 people who click the link will get 2 free months of Skillshare Premium: https://skl.sh/xelplus8
You probably know the quickest way to select a column of data in Excel is with the shortcut CTRL + Shift + ⯆. But what do you do if that column includes blank cells and you'd like to highlight the entire column of data? CTRL + Shift + down arrow key will stop the moment it comes across a blank cell.
Check out this video for the update: https://youtu.be/zo4l-G3Qgyk
This is often a very annoying problem especially if it's a large data set or there are a lot of blank cells. In this video I'm going to show you a trick which EASILY gets this done.
A shortcut which is often used is CTRL + End which jumps to the end of the data set. However, that may not always work if there is some additional input in other cells outside of the data set.
Here's the Excel Keyboard Shortcut(s) to Select Column with Blank Cells:
A better alternative is to use the Name Box in Excel. Just put in the range you want and use a random, very high "TO" value where you know you will not have any data anymore, e.g. B1:B10000. Press Enter. This will highlight the entire range. Then use the short cut CTRL + SHIFT + ⯅. This will highlight the data set including the blank cells.
The same thing can also be done with just the keyboard, no mouse. Press CTRL + G to get to the Name Manager. In the reference field type in the range, e.g. B1:B10000 and press Enter. Then use the short cut CTRL + SHIFT + ⯅. This will highlight the data set including the blank cells.
Detailed article: https://www.xelplus.com/excel-shortcut-select-column-with-blanks
This video was sponsored by Skillshare.
★ My Online Excel Courses ► https://www.xelplus.com/courses/
✉ Not sure which of my Excel courses fits best for you? Take the quiz: https://www.xelplus.com/course-quiz/
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 #Excel #LeilaGharani
42
views
Mysterious Excel Formula Syntax to Count Text Cells (as NEVER Seen Before)
5 March 2020 Excel Hacks: Tips & Tricks
It's safe to say this Excel formula syntax has NEVER been seen before. My team and I, did an extensive search on this mysterious Excel formula syntax, but found no information about it. It's a super short formula in Excel to count cells with text and ignore blank cells, numbers and also formula cells that result in blank cells or empty strings.
This is one of a kind Excel formula hack.
I received this special syntax for the Excel COUNTIF function from Jack, one of our community members. It works perfectly although I have no idea why. Because it looks like we are applying a wrong Excel syntax (angry eyes). If we were to write the formula "correctly" it would only exclude the truly blank cells. But cells that include formulas which result in blank cells would still be included in the count.
Alternatively, we can use the Excel COUNTIF function with the placeholders ? and *. This Excel count formula which uses wildcards, will also also just count non blank cells and ignore formulas that result in blank cells as well as ignore zeros and other numbers.
In a situation when we want to count all cells that include either text or numbers we can use SUMPRODUCT in combination with the LEN function. This way we can check if the length of the content in the cells is at least 1 character. You can replace SUMPRODUCT with SUM if you have the new Dynamic Array Excel - if you have legacy Excel and use SUM instead of SUMPRODUCT you need to press Control+shift+enter.
As a summary - In this Excel tutorial we take a look at different methods to Count in Excel:
1. Excel formula hack to count text cells: 2:07
2. Alternate Excel formula to count text cells (with wildcards): 2:52
3. Count all cells except the truly blank cells in Excel: 3:49
4. Count all text cells and numbers in Excel and exclude blank cells and formulas that result in blank cells: 4:30
⯆ DOWNLOAD the workbook here: https://www.xelplus.com/excel-formula-hack-count-text
Excel hacks and tricks playlist: https://www.youtube.com/playlist?list=PLmHVyfmcRKyxVeel8DFjewBSlZYdfd38A
★ My Online Excel Courses ► https://www.xelplus.com/courses/
✉ Not sure which of my Excel courses fits best for you? Take the quiz: https://www.xelplus.com/course-quiz/
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 #Excel #LeilaGharani
93
views
Why You Need to know the Time Value of Money Formula (Excel NPV)
27 February 2020 Unleash your inner Financial Pro
The first 500 people who click the link will get 2 free months of Skillshare Premium: https://skl.sh/xelplus7
Get Access to My Course "Fundamentals of Financial Analysis" HERE: https://www.xelplus.com/course/learn-financial-analysis/
Time Value of Money is an important concept for making good investment decisions. It's based on the principle that the timing of payments is important. You'll need this at work if you're a project manager, proposing a new idea to your boss that costs money. You'll also need this for evaluating different options or choices of investments. We can calculate this "time value of money" easily in Excel. In fact in this video, I'll show you an Excel template you can use to calculate the time value of money for any investment decisions.
Question: If a customer asks you if instead of paying $10,000 in cash now he could pay $10,800 over 4 years. Is that a good deal for you?
The answer depends on several factors:
1. Opportunity Costs - Money has an earning capacity. So, instead of waiting for the money, you could invest it and hopefully make more out of it. By getting the money later, you lose the opportunity to make an additional income.
2. Inflation - Prices increase over time. So, the same item will probably cost more in a couple of years.
3. Risk - Payments in the future always carry a certain risk. Will you really get the money then?
The combination of these factors defines the time value of money and is expressed as a discount rate. For instance a discount rate of 5% means that in each successive year the same amount of money is going to be worth 5% less than in the previous year.
You'll learn how to calculate Present Value (PV) manually and then we'll use the Excel NPV formula to calculate the Net Present Value in one go. Input values is the discount rate and the future cash flows.
In order to compare alternatives with different timing of payments we need to calculate their present value. In other words we need to discount future payments.
Companies often use their Weighted Average Cost of Capital (WACC) which is their average cost to get equity and debt. By applying their WACC as discount rate, any project that results in a NPV greater than zero is worth doing.
Key Takeaways:
- The further in the future the payments are, the lower their present value will be
- Think of the discount rate as a hurdle. The higher the hurdle, the more difficult it will be to get a high present value
⯆ DOWNLOAD the workbook here: https://www.xelplus.com/time-value-of-money-excel-npv
Advanced Excel Formulas Playlist: https://www.youtube.com/playlist?list=PLmHVyfmcRKyxvxehq3fiGjKDsEyy6d4Tz
Finance & Accounting playlist: https://www.youtube.com/playlist?list=PLmHVyfmcRKywV8Bk6euT02xxtv8YVDkZs
★ My Online Excel Courses ► https://www.xelplus.com/courses/
✉ Not sure which of my Excel courses fits best for you? Take the quiz: https://www.xelplus.com/course-quiz/
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!
#Finance #MsExcel #Excel #LeilaGharani
61
views
How to Create an Organizational Chart Linked to Data in Excel (Easy & Dynamic)
20 February 2020 Excel Infographic Chart Templates
Creating an organization chart in Excel has never been easier! The best part is it's dynamic because it's linked to cells.
Organizational Charts (Org Charts) or Organigrams are used in companies to graphically show the internal structure of the organization. The employees are usually represented by boxes and the chart also shows the relation of the positions to another. This is a great way to visualize an organization.
Very often these Org Charts are created by using Excel Smart Art or Excel Shapes. But this is not a dynamic solution. Every time anything changes, the chart has to be manually updated. This is very time consuming and error prone. In this video I'll show you an easier and dynamic alternative using Excel and a free Visio add-in from in Office 365 (no Visio subscription is required). With this, we can create automatically create an organization chart from employee data. The org Chart will be dynamic too, because it will be linked to cell values.
The basis for the organizational chart is an Excel table containing the required information like departments, employee names, employee IDs, job titles, who the position is reporting to, and so on. The organization chart is created by using the Microsoft Visio Data Visualizer add-in which is a free tool in Office 365.
After selecting the chart type in the Visio Data Visualizer it creates a draft org chart and a connected Excel data template. All we have to do is to bring over the actual organizational information into this data template. Then hit Refresh in the chart area and the organization chart is automatically updated.
Every time there is a change in the organization just update the data table and hit Refresh. The changes will be automatically reflected in the chart. The tool even includes a data checker that will inform you about data inconsistencies like empty or duplicate cell information.
In addition to organization charts the Visio Data Visualizer can also create different diagrams like Basic Flowcharts or Cross-Functional Flowcharts. This way, process steps and the required process flow can be visualized based on Excel data.
The final Organizational Chart can easily be printed or saved as PDF. In the chart area just click on More Options and select Open in Web. From there you can directly send the chart to the printer or Download as PDF.
Please note that in order to edit the appearance of the diagram in the Visio Data Visualizer you need to have an Office 365 subscription with a license for Visio.
The Microsoft Visio team was very kind to provide an email address to contact if you run into issues with the add-in. Send an email describing your problem to: tellvisio@microsoft.com
Timestamps:
00:00 Organization Chart in Excel
01:39 Install Microsoft Visio Data Visualizer Add-in
02:25 Create Organizational Chart with Automatic Refresh
06:40 Create a Flow Chart in Excel
*******************************************************************
⯆ DOWNLOAD the workbook here: https://www.xelplus.com/excel-dynamic-organizational-chart
Excel infographic chart templates: https://www.youtube.com/playlist?list=PLmHVyfmcRKyw08u-MZm4ROnB3J0gRlsop
Excel Advanced charts playlist: https://www.youtube.com/playlist?list=PLmHVyfmcRKyyEj7oQkCf7TL9yQQWXbGOQ
*****************************************************************
★ 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
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 #Excel #LeilaGharani
94
views
Calculating Returns On a Rental Property (ROI with Excel Template)
13 Febuary 2020 Unleash your inner Financial Pro
The first 500 people who click this link will get 2 free months of Skillshare Premium: https://skl.sh/xelplus6
Get Access to My Course "Fundamentals of Financial Analysis" HERE: https://www.xelplus.com/course/learn-financial-analysis/
Rental property investing can be tricky. If you invest in real estate you need to make sure you maximize your returns! For that you need to properly calculate returns on your rental property.
Learn how to analyze a rental property and how to calculate the numbers to make sure your next rental property is a cash cow!
You'll learn the step by step real estate investing method for determining the monthly cash flow and cash on cash return for any rental property investment. This is a must-watch for anyone investing in real estate.
Summary of what you learn:
Learn how to analyze if a rental property is worth buying by calculating the Cash-On-Cash Return and the Return on Investment (ROI) of the rental property. We'll setup an Excel template, i.e, a rental property calculator which can also be adjusted for all kinds of investment decisions. Use this free Excel workbook to calculate the return on your investment so that your next investment will be a success. Focus on the numbers and make sure you plug in realistic assumptions!
In this video we'll go through all the required steps to calculate the monthly rental property income by identifying all related revenue and expenses. We will also use the Excel PMT formula to quickly calculate the monthly debt service for the externally financed part of the investment.
In a first step we'll calculate the Cash-On-Cash Return. This is the ratio (expressed as a percentage) of annual before-tax Cash Flow divided by the equity or own money invested in the property. This figure is useful when consistent monthly cash flows are the most important consideration for the investment. But because it's based on cash flows it considers the full debt service. So, both the interest and the principal pay down are deducted from the rental revenue. But most investors do not consider the principal payment as an expense. Because by reducing the outstanding principal balance we create equity.
Therefore, we'll also calculate the Return on Investment or RoI of the property. For this we'll exclude the principal pay down from the expenses. To do that we'll use the Excel formulas CUMIPMT and CUMPRINC to split the monthly annuity. The resulting cash flow we divide again by the equity invested in the property.
************************************************************
⯆ DOWNLOAD the Excel workbook here (scroll to the bottom of the blog post): https://www.xelplus.com/rental-property-roi-calculator-excel
More finance & accounting videos: https://www.youtube.com/playlist?list=PLmHVyfmcRKywV8Bk6euT02xxtv8YVDkZs
Excel advanced formulas: https://www.youtube.com/playlist?list=PLmHVyfmcRKyxvxehq3fiGjKDsEyy6d4Tz
************************************************************
★ 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
This video is sponsored by Skillshare.
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
76
views
How to create barcodes in Excel that WORK!
6 February 2020 Excel Infographic Chart Templates
Get access to the full course here: https://www.xelplus.com/course/excel-vba-excel-macros/
Find out how you can create UPC-A barcodes in Excel. I tested the barcode with a scanner and it works! You can download the Excel file and use it to generate your own barcode or use it to learn Excel (thank you Pete for sharing).
The solution is a good mix of Excel formulas such as the MOD function, Excel Text functions like LEFT, RIGHT & MID functions as well as Conditional formatting and some Excel VBA. The final result, i.e. the Excel barcode is recognized by the barcode scanner.
If you'd like to learn more about VBA, check out my playlist here: https://www.youtube.com/playlist?list=PLmHVyfmcRKyzmbDy6QoBuUDrU5D-jD-Se
Source of the image used in the workbook is from this video - In One Lesson - "How barcodes Work": https://youtu.be/e6aR1k-ympo
Thank you Pete for sharing this awesome barcode tool with us!
⯆ DOWNLOAD the workbook from here (scroll to the bottom): https://www.xelplus.com/generate-barcodes-excel
LINK to VBA arrays video: https://youtu.be/7uIZg9p-IZQ
Pete's other video contribution to converts numbers to words: https://youtu.be/1HVLSuAZEtQ
00:00 Why Barcodes in Excel?
01:03 How It Works
05:16 How to Setup a Barcode Generator in Excel
★ 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
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 #Excel #LeilaGharani
26
views
Remove ALL Unwanted Spaces in Excel (TRIM ALL Function?)
30 January 2020 Excel Advanced Formulas & Features
Stubborn spaces can become a headache in Excel. You'll come across extra spaces when you copy data from your email or import data from another system. Each cell value ends up having trailing or leading spaces. Sometimes they can be removed easily with the TRIM function but sometimes TRIM doesn't work! In this video I'll show you how you can TRIM ALL Spaces - also the stubborn ones.
TRIM can only remove spaces with an ASCII character code 32. But there is another character code that represents a space. It's commonly referred to as a Non-Breaking Space and its ASCII character code is 160. Such spaces commonly occur when we copy text from Outlook or a website to Excel. With the Excel function CODE() we can identify which character code we are dealing with.
To remove a non-breaking space in Excel we can use the SUBSTITUTE function. With this function we can look for character code 160 in a cell and replace it with a regular space.
For numbers that are not recognized as numbers by Excel we can use the VALUE function. If the number contains non-breaking spaces use it in combination with the SUBSTITUTE function.
00:00 What To Do When VLOOKUP Does NOT Work
01:01 What to Do When TRIM Does Not Remove Spaces
03:36 Use SUBSTITUTE to Remove Spaces Between Words
04:49 What To Do When Excel Does Not Recognize Numbers
07:20 Wrap Up
⯆ DOWNLOAD the workbook here: https://www.xelplus.com/excel-trim-all-spaces
Check out other Excel tips and tricks in this playlist: https://www.youtube.com/playlist?list=PLmHVyfmcRKyxVeel8DFjewBSlZYdfd38A
★ 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
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 #Excel #LeilaGharani
114
views
Cash Flow Statement Basics Explained
28 January 2020 Unleash your inner Financial Pro
RESOURCES I Recommend: https://www.xelplus.com/resources/
The Cash Flow Statement is one of the 3 main Financial Statements. It will show you how effective a business is in managing its cash.
Most companies apply Accrual Accounting and it's important to understand that under this method revenue does not always equal cash in, and incurred expenses do not equal cash out. Therefore, don't make the mistake to just look at the income statement and the balance sheet. Focus on the Cash Flow Statement as well because it will show you where the company makes and spends its money.
The Cash Flow Statement consists of 3 main parts:
Cash Flow from Operations
Here we can find out how much cash a business is able to generate by selling its products and services. To calculate the Cash Flow from Operations there are 2 methods in use. In this video we will focus on the indirect method because it's used by most companies. It's linked to the income statement and starts with the Net Income. But in Accrual Accounting revenue does not equal cash in, and expenses do not equal cash out. Therefore, the net income from the Income statement must be adjusted to see the actual cash flows.
The most common adjustments are for non-cash transactions (depreciation, amortization, gains/losses for sales of non-current assets), and for working capital.
Cash Flow from Investing Activities
This shows the cash spent on investments or cash received from sales of investments. Here we can see the full cash inflow or outflow when a company purchases or sells property, equipment or other investments. We can also find out if the business acquired a company to expand its activities.
Cash Flow from Financing Activities
This section summarizes cash transactions that involve raising, borrowing, and repaying capital. When a company gets a bank loan or issues new shares it will receive additional cash. Therefore, this cash inflow will be reported with a positive figure. On the other hand, if the company repays the principal portion of a loan, pays dividends to its owners, or purchases its own shares it spends cash and reduces the cash balance. Therefore, this cash outflow will be reported with a negative figure.
At the very bottom of the Cash Flow Statement you will find the reconciliation to the cash balance in the Balance Sheet. The cash ending balance from the last period, plus the Cash Flows from Operations, Investing and Financing Activities must equal the cash ending balance for the current period. Sometimes, you will find an additional position for effects from foreign exchange rates caused by differences in average and spot rates.
00:00 How to Read a Cash Flow Statement
02:00 Cash Flow Statement - Overview
03:57 Cash Flow from Operating Activities
10:33 Cash Flow from Investing Activities
11:17 Cash Flow from Financing Activities
Check out the full article here: https://www.xelplus.com/cash-flow-statement-explained
In the next video of our accounting basics series, we'll see how to calculate the return for an investment.
★ 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/
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
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!
#Accounting #MsExcel #Excel #LeilaGharani
166
views
Excel 3D Formulas Explained (Includes a Bonus Excel Hack!)
23 January 2020 Excel Hacks: Tips & Tricks
Excel 3D formulas can be used to consolidate data from multiple worksheets into a summary sheet. 3D formulas aren't that well known except to advanced Excel users.
If you ever needed to reference the same cell or the same range on multiple Excel worksheets you'll know this can be quite a tedious process. Plus the reference is not dynamic. So, if another Excel sheet gets added in the middle, the formula will not update.
It doesn't have to be this way! In this video I'll show you an alternative approach to gather data from multiple Excel sheets to one sheet. With Excel 3-d reference formulas you can replace this cumbersome process with a short and flexible formula.
In addition, I'll show you a bonus Excel trick I received from Bob Umlas. With this hack we can even add cells or ranges on worksheets that aren't beside one another. This works as long as the sheets have a common name. It's like using wildcards in the 3d reference.
00:47 Excel 3D Formula explained
06:21 Bob's Bonus Trick for using 3d formulas in Excel
⯆ DOWNLOAD the workbook here: https://www.xelplus.com/excel-3d-formulas
Excel Tips & tricks playlist: https://www.youtube.com/playlist?list=PLmHVyfmcRKyxVeel8DFjewBSlZYdfd38A
★ 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
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 #Excel #LeilaGharani
113
views
Cash vs Accrual Accounting Explained With A Story
21 January 2020 Unleash your inner Financial Pro
Get Access to My Course "Fundamentals of Financial Analysis" HERE: https://www.xelplus.com/course/learn-financial-analysis/
There are 2 different accounting methods: Cash Accounting and Accrual Accounting. In this video I will show you the difference between accrual basis versus cash basis of accounting by telling you a story. You will get a quick overview how each of these methods impacts the timing of recording a company's transactions.
In Cash Accounting companies record transactions only when cash is exchanged. This means that revenue is recorded only when cash is received. And expenses are recorded only when cash is paid. Therefore, this method is intuitive and simple to understand. It helps you keep track of your cash balance, easy to apply, and an accepted accounting method also for tax purposes. Cash accounting method can be your accounting method of choice especially if your business is small and simple, and mainly cash based. On the other hand, cash accounting may not provide a full picture of the business at a certain point in time.
In Accrual Accounting companies record transactions when they happen rather than when cash changes hands. Revenue is recorded when earned, meaning when a sale is made and the products or services are delivered to the customer. Even if the customer hasn't paid yet. Expenses are recorded when incurred regardless even if they've not been paid to the supplier yet. Accrual Accounting provides a much better picture of what is happening in a business. If the company reports under GAAP or IFRS then Accrual Accounting is the accepted method. However, it does not focus on cash flows which is why reported income under this method does not equal cash-in.
00:56 What is Cash Accounting
02:38 Accrual Accounting Explained
03:27 Cash and Accrual Accounting explained with a Story
09:13 Advantages and Disadvantages for both methods
Check out the full article here: https://www.xelplus.com/cash-vs-accrual-accounting
In the next video of our accounting basics series, we'll take a look at the Cash Flow Statement.
★ 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!
#Accounting #MsExcel #Excel #LeilaGharani
150
views
Excel DGET Function Solves 2 of Your VLOOKUP Problems
16 Jan 2020 Excel Lookup Formulas
Join 400,000+ professionals in our courses: https://www.xelplus.com/courses/
Excel DGET Function is one of Excel's "forgotten" database functions that can do a Left lookup (unlike VLOOKUP) and ALSO easily look for multiple criteria.
★ My Online Excel Courses ► https://www.xelplus.com/courses/
One limitation of DGET is that you can't apply it to multiple cells. Basically you can't pull down the formula to lookup different values. If that's something you need, then you'll need to use INDEX MATCH, VLOOKUP, Excel FILTER function or FILTER feature or XLOOKUP. Check out the links under Related Videos below to find these videos.
★ Get the Official XelPlus MERCH: https://xelplus.creator-spring.com/
00:00 Excel DGET Function
01:44 Excel DGET Explained
06:03 DGET With Approximate Match
06:28 DGET With OR & AND Conditions
08:18 DGET With Multiple Criteria
DGET can lookup values to the left and act like an automatic filter.
Excel DGET disadvantages:
1. DGET formula can't be pulled down - you'll need to include the header for each lookup value
2. DGET returns an error (#Num error - 7:25) if there are duplicate lookup values in the range (as opposed to VLOOKUP or Index & Match which return the first match).
⯆ DOWNLOAD the workbook at the bottom of the blog post here: https://www.xelplus.com/excel-dget-function
------ Related Videos -------
Excel Index Match basics: https://youtu.be/F264FpBDX28
Excel VLOOKUP explained: https://youtu.be/hwL6KKJP-_I
Excel XLOOKUP: https://youtu.be/4c0CLUER6nw
Excel FILTER function: https://youtu.be/Eehk6PC0oGs
Check out the complete Excel Lookup Formula Playlist: https://www.youtube.com/playlist?list=PLmHVyfmcRKyxpMnh_KKfAgp5DF9ydawmi
★ 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/
RESOURCES I Recommend: https://www.xelplus.com/resources/
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!
#excel
25
views
Excel Lookup to Return Multiple Values with FILTER Function
9 Jan 2020 Excel for Office 365 & Dynamic Arrays
Check out the complete Excel Course covering the brand new functions: https://courses.xelplus.com/p/excel-dynamic-array-formulas
You'd like to do a VLOOKUP but return ALL match results instead of just the first match? You can use Excel's new FILTER function.
What if you'd like to lookup multiple criteria and return ALL match results? You can use the new Excel Dynamic Array FILTER Formula! It's like the dynamic or automatic version of Excel's advanced Filter feature. You can return Filtered results based on AND as well as OR conditions.
The Excel Filter function filters your data based on criteria, and returns all the matching records. This can be one or more records. If more than one match is found, the results will "spill" vertically. If there is a blockage (i.e. your results are spilling into already filled cells) then you will get the #SPILL error. You can easily get rid of this error by removing the cells that are blocking the FILTER formula.
If FILTER doesn't find any matching records it will return a #CALC error. You can suppress this error with the text or value of your choice by adding this to the last argument of the FILTER formula.
* Availability of Excel FILTER function: This feature is available on Excel for Office 365 - also for Excel on the Web and Mobile.
00:00 How to use Excel's FILTER Function
00:45 Advantages of Excel FILTER Function
01:46 Excel FILTER Function Explained
03:15 Excel FILTER #CALC Error
05:40 #SPILL Error and How to Overcome It
06:13 FILTER Function with Table References
07:14 FILTER Formula with Multiple Lookup Criteria
⯆ DOWNLOAD the workbook here: https://www.xelplus.com/excel-filter-function
Return Multiple Match Results (Excel Legacy Method): https://youtu.be/fDB1Ktyhp3Y
More about Excel Dynamic Arrays: https://youtu.be/2USJsIyIzvo
Excel Dynamic Arrays Playlist: https://www.youtube.com/playlist?list=PLmHVyfmcRKyyPFY31LldHWcJdLzGUPTSP
★ My Online Excel Courses ► https://www.xelplus.com/courses/
✉ Take this Quiz to choose the right Excel course: https://www.xelplus.com/course-quiz/
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 #Excel
63
views
Properly Print a Large Spreadsheet on Multiple Pages in Excel (Repeat Headers)
2 Jan 2020 Excel Introduction - Learn Excel Basics
Complete Excel Essentials Course: https://courses.xelplus.com/p/learn-excel-essentials
For larger Excel tables make sure you repeat the table row headers and column headers on every printed sheet. This is important when you print a large Excel spreadsheet on multiple pages.
This important Excel Printing option can be found in the Page Layout tab under more options for "page setup". In the Sheet tab, you'll find an option called "Rows to repeat at top" - select the specific rows on your Excel sheet that you'd like to see on every printed page.
In a similar manner you can use "Columns to repeat at left". Select the columns you'd like repeated on every single printed Excel sheet.
⯆ Complete blog post on printing large spreadsheets: https://www.xelplus.com/excel-printing-repeat-header-rows
More Excel printing tips and tricks: https://youtu.be/SZBRFRoGSFY
Complete Excel basics playlist: https://www.youtube.com/playlist?list=PLmHVyfmcRKyx1KSoobwukzf1Nf-Y97Rw0
★ 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
21
views
Last Video in 2019
26 Dec 2019 #MsExcel
This is the last video in 2019! Thank you for your support during the past year. For watching, for your feedback and interaction.
I hope you enjoyed our Excel, PowerPoint, Power Query, and Accounting videos. We have a lot more videos coming in 2020.
Me and my team wish you a wonderful start to the new year and we hope to see you next year.
It would be great to see you in April 2020 at the Global Excel Summit. You can register here: https://globalexcelsummit.com/ - use Leila_10% to get a 10% discount.
Faraz's Youtube Channel (fshaikh Excel Master): https://www.youtube.com/channel/UCw-2Ggv2ilZPHTWvp_7E7CQ
Video for Microsoft Office Training Session at KMES School, INDIA shown: https://youtu.be/S7zFoSqZ3uE
Sign up to my newsletter to get updates on what me and my team are up to: https://www.xelplus.com/subscribe/
Get over 100 Excel tutorials in one place: https://www.xelplus.com/free-excel-tutorials/
★ 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
17
views
Your Excel Printing Problems, Solved!
19 Dec 2019 Excel Introduction - Learn Excel Basics
Complete Excel Essentials Course: https://courses.xelplus.com/p/learn-excel-essentials
When you share an Excel file with others, you never know who’d like to print it. There might be times, you intentionally format your Excel sheets for printing – for example when creating handouts for your presentation.
There might be times you create Excel files that you don’t intend to be printed but if you’re sharing files with others, specially your boss, make sure you format it for printing. You never know who prefers a printed version and you’ll get extra points from your boss if you make their lives easier.
00:00 Solve Excel Printing Problems
02:00 How to print an Excel sheet
02:30 How to fit an Excel sheet to be printed on one page
03:25 Excel Printing Options on Page Layout tab
03:46 How to center selection on printed page in Excel
04:24 Print Excel spreadsheet with grid lines
04:34 Adjusting scale for printed sheet in case printed Excel sheet is too small
05:55 Printing selected areas on Excel sheet
06:33 Setting Excel print area to print specific areas
07:25 Clear print area
07:30 Add page break to Excel sheets
⯆ Blog post on Excel Printing tips and tricks: https://www.xelplus.com/excel-printing-tips
Excel Basics Complete Tutorial list: https://www.youtube.com/playlist?list=PLmHVyfmcRKywV8Bk6euT02xxtv8YVDkZs
★ 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
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
21
views
Easily Import Data from Web to Excel (2 Practical Examples)
12 Dec 2019 Excel Power Query (Get & Transform)
Join 400,000+ professionals in our courses: https://www.xelplus.com/courses/
You'd like to bring data from a web page into Excel? Instead of copying and pasting, use "Get and Transform" (Power Query) from the "Data" tab. This way you can import website data properly into Excel. Once you create a web query you can automatically get the latest data in Excel. You just have to refresh your query and update the query settings so it runs a refresh at specific intervals defined by you.
Getting data from a web page has never been easier. Just Open Excel, go to the "Data" tab and click on "From Web". Enter the web address of the external page you'd like to import. Power Query opens and tries to find the html tables on that web page. Select the correct table and transform your data. This step gives you the ability to customize your output. You can for example, fill in the blanks by automatically copying down the top values. You can remove the columns you don't need and clean up the data. Once done, you can view the data in an Excel table, as an Excel Pivot Table or create a connection to the query.
Note: Importing web data with Power Query this way requires the tables on the web pages to be in HTML format - otherwise they're not recognized in the Navigator view.
00:00 How to Import Data from Websites to Excel
01:15 Importing Crude Oil Spot Prices to Excel
04:59 Getting the Latest Weather Data into Excel
07:25 Bonus - Using Emojis in Power Query
______ Check out _______
Frédéric Le Guen's blog post on adding emojis to your reports: https://www.excel-exercise.com/add-emojis-to-your-charts-formulas/
Oz's video - Emojis, Excel, Power Query & Dynamic Arrays: https://youtu.be/c-tnA1zfrzs
⯆ DOWNLOAD the workbook (scroll to the bottom of the blog post): https://www.xelplus.com/import-data-from-web-to-excel/
Check out the Power Query Playlist: https://www.youtube.com/playlist?list=PLmHVyfmcRKyyKV86N7i0q9TfYNN8bBjX-
★ 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/
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
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!
#excel
21
views
Excel Dashboard with 4 Strange Features (Excel Hash Episode)
11 Dec 2019 #MsExcel
If you'd like to learn about the new Excel functions like FILTER check out the complete course here: https://courses.xelplus.com/p/excel-dynamic-array-formulas
⯆ DOWNLOAD the workbook (scroll to the bottom of the blog post): https://www.xelplus.com/excel-hash-2019/
VOTE for your Favorite solution: http://bit.ly/VoteHash2019 (UPDATE: Voting has ended)
All Excel Hash 2019 Videos: https://www.youtube.com/playlist?list=PLmHVyfmcRKyxfOLvOtx76xw8bbKgDvUwp
The ingredients for this year's Excel Hash are:
1. Excel XOR Function (1:17)
2. At least one Excel Dynamic Array Function (2:05)
3. At least one Icon (3:11)
4. Linked Picture in Excel (3:29)
A lot is going on in this video that I didn't get to cover in detail. Please check out these videos for a step by step approach:
How to create Bar-in-Bar Chart: https://youtu.be/6iZT74sKi50
Linked Pictures step by step: https://youtu.be/wlW2UKml9CY
Link to XLOOKUP Video: https://youtu.be/4c0CLUER6nw
Excel Hash 2018 video: https://youtu.be/N0MesGk0VpI
To learn more about Excel Dynamic Arrays - check out the full course: https://courses.xelplus.com/p/excel-dynamic-array-formulas
Video summary:
This is my Excel Hash entry for 2019. I decided to create an interactive Excel dashboard based on the 4 ingredients we were given:
- The XOR Excel function was used to derive if either cell resulted in a TRUE. I also explain a little about Excel's logical test formulas and what Excel sees as a TRUE in a cell.
- Excel FILTER function together with XLOOKUP were used to return multiple match results from Excel with a simple formula (2:05)
- The icons and pictures were used to add dynamic visualization to the Excel dashboard report
- Name manger and the Indirect formula were used to create a dynamic linked picture
As a bonus I added in a bar-in-bar column chart to compare the count for each selection to the total count.
Other Excel MVPs in the Excel Hash challenge are:
Bill Jellen - MrExcel
Mynda Treacy - MyOnlineTrainingHub
Jon Acampor - Excel Campus
Oz du Soleil - Excel on Fire
Unfortunately Mike Girvin - Excel Is Fun couldn't attend this year
★ My Online Excel Courses ► https://www.xelplus.com/courses/
✉ Take this Quiz to choose the right Excel course: https://www.xelplus.com/course-quiz/
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
#ExcelHash
97
views
Impressive Slide Design: Create Custom Shapes and Typography in PowerPoint
5 Dec 2019 PowerPoint & Presentation Tips
Get two FREE months of unlimited learning on Skillshare: https://skl.sh/xelplus5
In this PowerPoint tips and trick tutorial learn how to merge shapes correctly in PowerPoint. This way you can create your own custom shape or typography (Font style) in PowerPoint.
Understanding how to use merge shapes can be a bit tricky. Depending on whether you select Union, Combine, Fragment, Intersect or Subtract merge shape option, you will get different results. It also matters which shape you click on first before going to merge shapes options.
Combining shapes properly and using a custom background image inside the merged shape will allow you to create more professional PowerPoint presentations. You can create custom fonts or typography like the "Walking Dead" title.
Aside from creating custom shapes with the merge shapes feature, you also learn how to edit shapes by adjusting the points. You can also add points to each side of the shape you'd like to adjust. This allows you to alter any existing PowerPoint shape and create your own custom shape library in PowerPoint.
We'll cover some useful PowerPoint shortcuts like copying shapes by holding down the control and the shift keys to make sure they are copied on the same level.
Jump to the right place:
00:00 Custom Shapes in PowerPoint
02:55 PowerPoint Insert Shapes
03:00 PowerPoint Shortcuts for Copying Shapes
03:30 PowerPoint Custom Shapes by Editing Points
03:46 Merge Shapes as Union in PowerPoint
04:07 PowerPoint Insert a Picture into a Shape
04:26 Merge Shapes with Intersect
04:53 Crop Image to Custom Shape in PowerPoint
05:33 PowerPoint Custom Typography with Merge Shapes
06:40 Difference between Merging Shaping and Grouping
07:14 Custom PowerPoint Fonts with Merge Shapes Intersect
07:53 PowerPoint Merge Shapes Subtract
08:14 PowerPoint Merge Shapes Fragment
⯆ DOWNLOAD the PowerPoint here: https://www.xelplus.com/powerpoint-merge-shapes
More PowerPoint Tutorials: https://www.youtube.com/playlist?list=PLmHVyfmcRKywJTPy2_OBZEaKeGGdFXlX3
★ 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!
Many thanks to Skillshare for sponsoring this video!
#PowerPoint
55
views
PROPERLY Record Debits and Credits with Examples (EASIEST Method)
3 December 2019 Unleash your inner Financial Pro
Get Access to My Course "Fundamentals of Financial Analysis" HERE: https://www.xelplus.com/course/learn-financial-analysis/
Use our secret weapon ADEx LER (Accountants Don't Expect Low Earning Rates) to apply Debits and Credits to actual transactions of a business. We will learn how double-entry bookkeeping takes everyday activities and turns them into data that can be processed.
First, we will visualize transactions with T-Accounts (3:57), later with journal entries (8:38). Finally, we will create the resulting income statement and balance sheet to see how the accounts flow into these statements.
This video will answer common questions like:
- How do your write and record debits and credits? 1:22
- What is a credit with example? 2:09
- How is a journal entry recorded? 8:40
- What are COGS? 10:50
- Is expense a debit or a credit? 11:00
Overview:
Debits and Credits are a fundamental part in accounting. To memorize the rules easily just remember: Accountants Don't Expect Low Earning Rates. The process of recording transactions with debits and credits is referred to as double-entry bookkeeping, because there are ALWAYS at least two accounts involved.
We will follow Claudio's beach business for one day and record the transactions that happened during this day. We will start out with Claudio investing his own, private money into the business. Then, record the transaction for buying inventory and finally, the sales of the merchandise to tourists.
Last step is to create the resulting income statement and balance sheet based on these transactions.
Chapters:
Owner putting Equity in company - 2:09
Buying Inventory with Cash - 5:42
Recording Sales - 7:20
Recording COGS - 10:03
Summary of Transactions - 12:02
Income Statement - 13:15
Balance Sheet - 13:35
★ 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!
#Accounting #MsExcel #Excel #LeilaGharani
15
views
How to Use the NEW & IMPROVED Excel XLOOKUP (with 5 Examples)
28 November 2019 Excel for Office 365 & Dynamic Arrays
Join 400,000+ professionals in our courses: https://www.xelplus.com/courses/
Excel XLOOKUP will soon become the ONLY Excel LOOKUP Function you’ll ever need to solve any lookup problem you come across. If you'd like to learn about the other brand new Excel functions like FILTER & UNIQUE check out the complete course: https://courses.xelplus.com/p/excel-dynamic-array-formulas
In this video we cover 5 common Excel lookup problems:
1. Lookup returns an error (#NA) - This is a common problem with VLOOKUP. If the VLOOKUP function returns #NA it is likely because the item you're looking up does not exist on the lookup table. The solution so far has been to wrap the VLOOKUP function inside IFNA or IFERROR functions. With XLOOKUP however, you no longer need to do that. You just need to use the 4th argument of XLOOKUP [If_not_found] optional argument and specify the value you'd like to get back if the value does not exist on the lookup array.
2. Horizontal lookup - In legacy Excel when we needed to lookup a value in a table that was structured horizontally instead of vertically we would use the HLOOKUP function. The new XLOOkUP function can do horizontal lookups as well. You just have to select the horizontal array instead of the vertical array.
3. Look for a partial match instead of an exact match in Excel: There might be times you need to look for a partial match. In this case you can combine wildcards such as the asterisk or question mark characters and combine them together with the 5th argument of XLOOKUP which is the optional [match mode] argument.
4. Two way Excel lookup: To look up a value on the rows as well as columns we generally combined VLOOKUP with Excel Match function or used the famous INDEX & MATCH functions. We no longer need to use two separate functions to do a two way lookup. We can use XLOOKUP inside another XLOOKUP to get the job done. The advantage is we just need to know one function well, instead of combining different Excel lookup functions with one another.
5. Excel approximate match lookup: There are times you'd like to look for a value inside a data table and return the closest match. This is specially used for cases where you have thresholds for example for bonus schemes. If a number falls between a specific threshold you'd like to return a corresponding value. This is where you can use XLOOKUP's 5th argument [match mode] to look for "exact match or next smaller item" or "exact match or next larger item".
00:00 What is Excel's New XLOOKUP Function
01:15 Lookup Value Not Found with XLOOKUP
04:06 Horizontal Lookup with XLOOKUP in Excel
05:44 Look For Partial Match with XLOOKUP
07:51 Two-Way Lookup with XLOOKUP
10:29 Approximate Match with XLOOKUP
*** AVAILABILITY *** The XLOOKUP function is available to Office 365 and 2021 users.
⯆ DOWNLOAD the workbook here: https://www.xelplus.com/excel-xlookup-5-examples
LINKS to related videos - First XLOOKUP video: https://rumble.com/v2uqp14
Excel left lookup with XLOOKUP: https://rumble.com/v2us6fy
★ 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/
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 #Excel #LeilaGharani
39
views