Excel BEATS Google Sheets with THESE 10 Features!
October 1, 2020 #MsExcel
In this video we'll explore 10 features of Excel where Google Sheets doesn’t compete (yet).
This is also a quick tutorial about Excel's best features. It's filled with Excel tips. Did you know all of them?
This covers the Excel Vs Google Sheets debate. Whereas in the previous video we looked at Google Sheets vs Excel. Check it out here in case you missed it: https://rumble.com/v2v92bq
With Google Sheets becoming a popular alternative to Excel, it’s important to recognize the strengths and weaknesses of each application. This also gives you an idea of what Sheets and Excel are used for and are capable of.
LINKS to related XLOOKUP videos:
https://rumble.com/v2usebg
https://rumble.com/v2us6fy
https://rumble.com/v2uqp14
Power Query: https://rumble.com/v2v8y8w
Check Out the Complete Excel Power Query course: https://courses.xelplus.com/p/excel-power-query
⯆ Check out the complete blog post about Excel vs. Google Sheets: https://www.xelplus.com/excel-vs-google-sheets
★ 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 Microsoft 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
Time Stamps:
00:00 Microsoft Excel versus Google Sheets
00:33 XLOOKUP
01:53 Excel Tables
03:30 Power Query
04:17 3D Formulas
05:36 Flash Fill
06:38 Excel's Data Model
07:44 Hash References for Dynamic Ranges
10:03 Charts with Conditional Formatting
12:06 Go To Special
13:17 Set Print Area
14:22 Pivot Slicers and Linked Shapes
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 #GoogleSheets
226
views
1
comment
Google Sheets BEATS Excel with THESE 10 Features!
September 24, 2020 Google Sheets Tips, Tricks & Functions
Go to https://expressvpn.com/xelplus and find out how you can get 3 months free.
🏫 My complete Google Sheets course: https://courses.xelplus.com/p/google-sheets
Excel has been around for decades and has been largely considered the King of all spreadsheet applications. When Google Sheets first launched in 2006, it was nowhere near dethroning Excel.
But since then it improved significantly and today the functionality between Excel and Google Sheets is very similar. In fact, it has useful features which Excel either completely lacks or hasn't implemented as neatly as Sheets.
In this video we'll explore 10 features of Google Sheets where Excel doesn’t compete (yet).
Time Stamps
00:00 The Superpowers of Google Sheets
01:22 Searchable Drop-Down Lists in Google Sheets
02:58 The SPLIT Function
04:08 Automatic Refresh for Pivot Tables
05:08 IMPORTXML and IMPORTHTML to Import Data from Websites
06:38 Lookup on Images in Sheets
07:42 Search and Replace with REGEX
08:51 Insert QR-Codes with IMAGE Function in Sheets
09:54 Translate with a Function (GOOGLETRANSLATE)
11:04 Chat Inside Your Spreadsheet
11:46 The All-Powerful QUERY Function
13:36 The GOOGLEFINANCE and SPARKLINE Function in Sheets
If you want to find out how Excel fights back check out this video: https://youtu.be/yXMo9sj0Y2Y
⯆ Check out the complete blog post about Google sheets vs. Excel: https://www.xelplus.com/google-sheets-vs-excel
★ 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 Microsoft 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!
This video was sponsored by ExpressVPN.
#googlesheets
#MsExcel #Exce; #LeilaGharani
119
views
Do you know these 5 PowerPoint Hacks?
September 17, 2020 PowerPoint & Presentation Tips
Link to Simple Slides to make better slides FAST: https://www.simpleslides.co/a/45563/yoW5RmAU
In this video I'll share some useful PowerPoint tips and tricks that will help you to create better slides in a lot less time. We'll cover how you can easily align objects in your slide automatically. We'll also check out a super-helpful feature to change different fonts in the entire PowerPoint presentation in one go. Duplicating objects in PowerPoint can be done in better ways than the standard Copy & Paste (we'll use shortcuts) and we'll see how sections in PowerPoint are an underrated feature you can use to your advantage. Finally, we'll check out the different pointer options you have in your slide show to add a more interactive, visual element during your PowerPoint presentation.
Join 300,000+ professionals in our courses: https://www.xelplus.com/courses/
00:00 Useful Features in PowerPoint
01:06 Aligning Objects in PowerPoint
02:31 Replacing Fonts in PowerPoint
04:03 Duplicating Shapes in PowerPoint
05:08 Using Sections in PowerPoint
06:37 Pointer Options in PowerPoint
08:48 Sponsor Simple Slides
⯆ Check out the full article here: https://www.xelplus.com/powerpoint-5-hacks
Create more professional PowerPoint presentations with this playlist: https://www.youtube.com/playlist?list=PLmHVyfmcRKywJTPy2_OBZEaKeGGdFXlX3
★ 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/
Get Microsoft 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!
Thanks to Simple Slides for sponsoring this video.
#PowerPoint #MsExcel #Excel #LeilaGharani
129
views
5 Tips to Unlock the Power of OneNote
September 10, 2020 OneNote Tips
The first 1000 people who click the link will get 2 free months of Skillshare Premium: https://skl.sh/leilagharani09201
In this tutorial I'll show you the best tips for OneNote that are easily missed. They will help you improve your processes, become more productive, and also keep you safe.
Leila's Tips:
00:00 OneNote Hidden Features
01:11 Password-Protect Notes in OneNote
02:59 Share Your Notes and Collaborate
04:31 OneNote WebClipper
06:01 Use Hyperlinks in OneNote
08:47 Automate Meeting Minutes with OneNote
You can download OneNote for free and and it's available for Windows and for Mac.
LINK to "how to use OneNote effectively" video: https://youtu.be/Poepoh1b_3k
★ 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 Microsoft 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!
Many thanks to SkillShare for sponsoring this video!
#OneNote #MsExcel #Excel #LeilaGharani
115
views
How To Create A Histogram in Excel (& change the bin size)
September 3, 2020 Excel Charts
An Excel histogram chart is very easy to make. However it's not always clear when to use a histogram chart or how to adjust it to your needs. Generally, histogram charts are often confused with bar charts. They're very different though. A histogram groups continuous data into ranges, also called bins. The height of each bar shows how many fall into each bin. A histogram is the most commonly used graph to show frequency distributions for data with large value ranges.
In this video I'll show you how you can easily setup a histogram chart in Excel from scratch. We'll also cover how we can customize the bins and format the chart professionally.
⯆ DOWNLOAD the workbook here: https://www.xelplus.com/excel-histogram-chart
Link to Excel Basics playlist: https://www.youtube.com/playlist?list=PLmHVyfmcRKyx1KSoobwukzf1Nf-Y97Rw0
More Excel charts & data visualization: https://www.youtube.com/playlist?list=PLmHVyfmcRKyyEj7oQkCf7TL9yQQWXbGOQ
★ 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/
Time Stamps:
00:00 When to use a histogram chart
01:16 Excel histogram from scratch
01:32 Change Excel histogram bin size
Get Microsoft 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
74
views
6 FREE Add-Ins for Excel to Start Using Now
August 27, 2020 Excel Hacks: Tips & Tricks
Check out AlchemyJ: https://www.alchemyj.io/download
In this video we'll cover the Best 6 FREE Excel Add-Ins. They will improve your reporting, data analysis and make your life a LOT easier.
QR4Office - Easily Create QR Codes in Excel
Web Video Player - Embed YouTube videos directly in Excel (or PowerPoint)
Visio Data Visualizer - Create Dynamic Flowcharts and OrgCharts without Shapes or SmartArt
People Graph - Easily Create an Infographic out of your dataset
Mini Calendar/Date Picker - Select Dates from a Calendar
Bing Maps - Plot Locations and Visualize your Data
★ My Online Excel Courses ► https://www.xelplus.com/courses/
00:00 Best FREE Excel Add-Ins
00:55 How to Install Add-Ins in Excel
01:45 QR4Office
02:47 Web Video Player
03:41 Visio Data Visualizer
07:16 People Graph
09:14 Mini Calendar and Date Picker
10:49 Bing Maps
13:08 Sponsor AlchemyJ
⯆ DOWNLOAD the workbook here: https://www.xelplus.com/free-excel-add-ins
More Excel Tips and Tricks: https://www.youtube.com/playlist?list=PLmHVyfmcRKyxVeel8DFjewBSlZYdfd38A
✉ 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/
Get Microsoft 365: https://microsoft.msafflnk.net/15OEg
Microsoft Surface: https://click.linksynergy.com/fs-bin/click?id=MvAI6newpCU&offerid=780591.10003599&type=3&subid=0
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
Thanks to AlchemyJ 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!
#MsExcel #Excel #LeilaGharani
80
views
How to Calculate Hours Worked with Excel Power Query (& Properly Sum time)
August 20, 2020 Excel Power Query (Get & Transform)
Check Out the Complete Excel Power Query Course: https://www.xelplus.com/course/excel-power-query/
Working with time in Excel or to calculate Hours Worked especially when they go past midnight can get tricky in Excel. Forget about using complex Excel formulas! In this video I'll show you how you can use Excel Power Query and its very helpful time transformation features. Working with time has never been easier! With just a click of a few buttons you can:
- Calculate the difference between two times
- How to sum time in Excel
- Subtract time
- Calculate hours worked for any shift
- Round up to the hour
- Get the hour, minute or second portion of time
⯆ DOWNLOAD the workbook here: https://www.xelplus.com/power-query-time-calculations
Link to Excel Power Query playlist: https://www.youtube.com/playlist?list=PLmHVyfmcRKyyKV86N7i0q9TfYNN8bBjX-
★ 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 Microsoft 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
Time Stamps
00:00 Working with Time in Excel
01:49 Time Transformation in Excel Power Query
04:59 Add Weekday in Excel Power Query
06:42 Aggregating Hours Worked with Excel Group By
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 #powerquery
70
views
When You Should Use the New Excel LET Function
August 13, 2020 Excel for Office 365 & Dynamic Arrays
The first 1000 people who click the link will get 2 free months of Skillshare Premium: https://skl.sh/leilagharani0820
Excel's new LET function can make your formulas easier to read and it can improve the performance of your formulas so they run faster. This applies to formulas that use the same expression (e.g. formulas, values) multiple times.
In this video you'll learn the basics of the LET function and also find out in which situation the LET function can REALLY make a difference for you. I measured the time for a full calculation on a large data set for different scenarios. You'll be surprised when you see the results!
Many thanks to Skillshare for sponsoring this video!
Note: LET function is not available in Excel 2019 or Excel 2016. Only in Excel for Microsoft 365.
Link to Fast Excel from Charles Williams: http://www.decisionmodels.com/FastExcelV4.htm
⯆ DOWNLOAD the workbook here: https://www.xelplus.com/excel-let-function
★ My Online Excel Courses ► https://www.xelplus.com/courses/
LINK to Excel New Features & Dynamic Arrays Playlist: https://www.youtube.com/playlist?list=PLmHVyfmcRKyyPFY31LldHWcJdLzGUPTSP
Advanced Excel Tips & Formulas: https://www.youtube.com/playlist?list=PLmHVyfmcRKyxvxehq3fiGjKDsEyy6d4Tz
✉ 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/
Get Microsoft 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
Time Stamps
00:00 Why use Excel's LET function
01:07 Availability and Syntax of Excel LET Function
03:12 Example of LET Function with IF Conditions
06:53 Example of LET Function with Multiple IF Conditions and Symbols
08:55 Does Excel LET Function REALLY Improve Performance?
11:03 LET Function with Excel Dynamic Arrays
14:46 Performance of Excel LET Function with Dynamic Arrays
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
82
views
Excel FILTER Function TRICK for Non Adjacent Columns
6 August 2020 Excel Hacks: Tips & Tricks
Are you up for a really cool trick with Excel's new Filter Function? With a simple trick, you can use it to get multiple match results from Non-Adjacent columns in a Simple & Dynamic way. You can also use this trick to get unique values from non-adjacent columns.
So, let's say we have a dataset with 6 columns but we're only interested in the information in column 2 and 5, i.e. in non-adjacent columns. Plus, we don't want to get "all" value from these columns but instead only records that match a certain criteria. And we want it to be in a dynamic way so whenever we change the criteria, the result updates automatically.
I'll show you an Excel trick that get's this done in record time. The secret is to broadcast array constants in our formula. And not only that: You can also use this to get a UNIQUE list of combinations that are in non-adjacent columns.
As a bonus tip I'll show you how you can SORT the result based on a value that's not in the filtered end result.
Many thanks to my student Adam Payne for sharing this method with me!
*** In case you'd like to learn more about Excel Dynamic array functions check out my complete course: https://courses.xelplus.com/p/excel-dynamic-array-formulas
⯆ DOWNLOAD the workbook here: https://www.xelplus.com/excel-filter-trick-non-adjacent-columns
LINK to FILTER video: https://youtu.be/Eehk6PC0oGs
★ 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 Microsoft 365: https://microsoft.msafflnk.net/15OEg
Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593
Time Stamps
00:00 Multiple Match Results from Non-Adjacent Columns
01:40 Excel FILTER Function
04:00 Filter Out the Columns You Do NOT need
06:09 Get UNIQUE list from Non-Adjacent Columns
09:10 Bonus Trick for Sorting
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
67
views
Consolidate & Clean Multiple Excel Sheets in One Pivot Table
30 July 2020 Excel Power Query (Get & Transform)
Join 400,000+ professionals in our courses: https://www.xelplus.com/courses/
Excel's Power Query can easily solve a common problem in Excel: Consolidating information from multiple Excel worksheets into one Pivot Table. This also works if you'd like to append data from multiple sheets into a single Excel table.
In this video we'll combine the data from multiple Excel sheets into a single Pivot table in the same workbook. And we'll do it in a dynamic way so that when new sheets are added, we just need to press Refresh on our report. We will also apply Power Query's convenient transformations to clean and prepare the data for our report (fill up empty cells in a table)
⯆ DOWNLOAD the workbook here (scroll to the bottom of the blog post): https://www.xelplus.com/combine-excel-sheets-power-query
Power Query Playlist: https://www.youtube.com/playlist?list=PLmHVyfmcRKyyKV86N7i0q9TfYNN8bBjX-
★ 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/
Get Microsoft 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
Time Stamps:
00:00 Combine Data from Multiple Sheets in one PivotTable
01:48 Send data to Power Query
02:25 Send data from all sheets in one go
03:45 Apply Power Query's Data Transformations
04:58 Create Excel Pivot Table on Appended Data
05:38 Exclude End-Report as Data Source in Power Query
07:46 Refresh Report with New Data
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 #powerquery
66
views
Simple Excel Trick to Conditionally Format Your Bar Charts
23 July 2020 Excel Charts
If you want to help your audience to make sense of your Excel chart quickly, consider color coding it. In this video I'll show you how you can apply Conditional Formatting to show different colors for positive and negative deviations. This way you can quickly create an Excel bar chart with conditional formatting based on change % or based on any logic you choose.
Comparison charts and bar graphs in Excel can be quite intimidating and force the reader to spend considerable time to make sense of them. Color can help to immediately grasp important patterns in the chart like good and bad performance. Research also shows that information that's color coded is easier understood and better retained in memory. For example if you use a column or bar chart to show deviations you can have a different color for negative values to positive values. You might choose to show the bars for negative values in red and positives values in green or any other color of your choice.
I'll show you a technique how you can apply such conditional formatting to any type of chart in Excel. As a bonus tip I'll cover how to emphasize a particular part of a chart like the maximum value by defining a logic.
⯆ DOWNLOAD the workbook at the bottom of the blog post here: https://www.xelplus.com/excel-conditional-bar-charts
Complete Excel Dashboard Playlist: https://www.youtube.com/playlist?list=PLmHVyfmcRKyz_4TwSfxzN_gsRtg3ODzyx
Excel data visualization & advanced chart tricks: https://www.youtube.com/playlist?list=PLmHVyfmcRKyyEj7oQkCf7TL9yQQWXbGOQ
★ 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 Microsoft 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
Time Stamps
00:00 Conditional formatting of charts
00:48 Setting up first bar chart
03:17 Setting up the deviation chart
05:31 Negative red, positive green
06:31 Conditional formatting of maximum value or other logic
09:32 Finalizing the charts (grouping charts together)
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
54
views
Excel Unpivot Data with Multiple Headers (Multiple Row levels into Columns with Power Query)
16 July 2020 Excel Power Query (Get & Transform)
Check Out the Complete Excel Power Query course: https://www.xelplus.com/course/excel-power-query/
Stop wasting time creating complex formulas to transpose data in columns with multiple headers to get proper tabular data. With Excel's Power Query you can easily create a dynamic solution to transform your report-style data into a dataset you can use to run Pivot Tables on or do further analysis on with formulas.
Unpivot means to create rows from columns. For example if you have a sales report and the months Jan - Dec are each reported in its own column. By Unpivoting we "shrink" this report with 12 columns for the months into a tabular format where all months are consolidated in one column and the data is shown in the rows. This way your data is properly structured for further analysis.
⯆ DOWNLOAD the workbook here: https://www.xelplus.com/excel-advanced-unpivot-power-query
LINK to Unpivot video: https://youtu.be/JMrfbv2h7p8
★ 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 Microsoft 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
Time Stamps:
00:00 Purpose of UnPivot in Excel
02:27 Prepare data for Excel Power Query
04:44 Use Power Query Editor to Unpivot Data
10:06 Test Dynamic Solution when adding new data
11:26 Remove empty cells from report
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
35
views
Properly Use Excel IFS Function
9 July 2020 Excel Intermediate Functions & Features (For ALL Excel Versions)
The IF function is one of the most popular functions in Excel. But writing nested Excel IF formulas in situations when more than 1 condition has to be checked can become cumbersome. If you have Excel 2019 or Microsoft 365 you can use the Excel IFS function instead.
In this tutorial I'll show you how easy it is to use this logical function when testing for multiple criteria. Your formulas will be much easier to read.
Since the IFS function only checks for the TRUE condition, how can you include an else condition in IFS? There is a simple way around this. Check it out at minute 02:42.
I'll also show you how you can use conditional formatting in Excel (on emojis) to visualize results better.
As a special bonus tip I'll cover how you could replace the Nested IF formulas with the VLOOKUP function.
⯆ Workbook is available here: https://www.xelplus.com/excel-ifs-function/ (scroll to the bottom of the post)
Link to Excel IF formula: https://youtu.be/KkTaQ5OjAGc
Link to VLOOKUP video: https://youtu.be/hwL6KKJP-_I
Link to XLOOKUP video: https://youtu.be/xnLvEhXWSas
★ 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 Microsoft 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
Time Stamps
00:00 Excel IFS instead of Nested IF formulas
00:37 Testing for 3 conditions with IFS
02:42 How to include "else" statement in IFS
04:40 Excel IFS with conditionally formatted emojis
09:06 Use Excel VLOOKUP instead of Nested IF
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
44
views
Excel Dynamic Filled Map Chart with Power Query
2 July 2020 Excel Power Query (Get & Transform)
Check Out the Complete Excel Power Query course: https://courses.xelplus.com/p/excel-power-query
*** Note: On XelPlus you will get additional bonuses that are NOT available on other platforms! Check the course landing page for more info.
In this video I'll show you how you can use Excel Power Query to import data from a CSV file to Excel. We'll use Power Query's data transformation features to extract the required information from a column. Then we'll use the Group By feature to summarize the data by state.
As a last step I'll show you how easy it is to create a Filled Map Chart based on this data set (available in Excel 2019 and Microsoft 365) in Excel.
⯆ Full article and csv file can be accessed here: https://www.xelplus.com/excel-filled-map-chart-using-power-query/
LINK to Power Query playlist: https://www.youtube.com/playlist?list=PLmHVyfmcRKyyKV86N7i0q9TfYNN8bBjX-
★ 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/
Time Stamps:
00:00 Import CSV file with Excel Power Query
02:03 Extract Text Between Delimiters with Power Query
02:51 Summarize Data with Group By in Power Query
04:04 Create Filled Map Chart in Excel
06:36 Refresh Query with New Data
EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/
Get Microsoft 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 #powerquery
40
views
How Power Query Will Change the Way You Use Excel
25 June 2020 Excel Power Query (Get & Transform)
Join 400,000+ professionals in our courses: https://www.xelplus.com/courses/
Power Query is like an Instant Miracle Cure to your difficult data analysis problems.
★ Get the Official XelPlus MERCH: https://xelplus.creator-spring.com/
Here's the problem...
Excel Power Query has been a part of Excel since Excel 2016! It's in the Data tab under Get and Transform. Unfortunately many people haven't explored what it has to offer! They continue doing things the old "manual" way, Or the "complex" way with difficult formulas.
And something else you may not know ...
Generally, when you learn something, the more effort you put in, the more you get out. But it's NOT the same with learning Power Query! If you just put in a little effort, you get SO much in return! This is the difference with learning Power Query to learning other skills like VBA.
And that’s why I created this course!
To give you the tools to really make a difference at your workplace. I’m going to take you from Power Query Beginner to Pro!
Check out the full article: https://www.xelplus.com/excel-power-query-course-preview/
★ 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
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 #powerquery
38
views
Excel Pivot Tables EXPLAINED in 10 Minutes (Productivity tips included!)
18 June 2020 Excel Pivot Tables
Join 400,000+ professionals in our courses: https://www.xelplus.com/courses/
Discover hidden productivity tips when working with Excel Pivot Tables. At first Pivot Tables might seem like a complex tool but they are one of Excel's EASIEST features to work with. They help you summarize data really FAST.
★ My Online Excel Courses ► https://www.xelplus.com/courses/
In this video I'll show you in about 10 minutes, how simple it is to create a pivot table and get quick insights into your data. I'll also throw in some Excel Pivot Table tips and tricks not everyone is aware of. To top it off we'll also add Pivot Slicers to get a professional visual to apply filters to our Pivot Table.
★ Get the Official XelPlus MERCH: https://xelplus.creator-spring.com/
⯆ DOWNLOAD the workbook so you can practice with this Pivot Table example data (scroll down to the bottom of the post): https://www.xelplus.com/pivot-tables-in-10-minutes/
✉ Not sure which of my Excel courses fits best for you? Take the quiz: https://www.xelplus.com/course-quiz/
Timestamps
00:00 Why use Excel Pivot Tables?
01:35 Convert Data to Excel Tables
02:46 Create Pivot Table
04:08 Change aggregation in Pivot Table to average or count
04:40 Adjust number format in Pivot Table
05:10 Add filter to Pivot Table
05:28 Add column fields to Pivot Table
05:50 Removing Grand Totals and Sub Totals in Pivot Table
06:27 Update Pivot Table layout and design
07:14 How to bring back Pivot Table Fields dialog box
07:42 Update Pivot Table column header without errors (pivot table field name not valid)
08:33 Sort values in Pivot Table
08:41 Lock column width on Pivot Table refresh
09:00 Add Value as percentage of grand total in Pivot Table
10:22 Add Pivot Table Slicers to Filter Data
11:10 Connect Slicer to multiple Pivot Tables
11:50 Refresh Pivot Tables
12:40 Benefits of Pivot Tables
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!
#MsExcel #Excel #LeilaGharani
96
views
Discover the EASY way to Transform Text in Excel (No Formulas - just Power Query)
11 Jun 2020 Excel Power Query (Get & Transform)
Join 300,000+ professionals in our courses: https://www.xelplus.com/courses/
Discover the EASIEST way to perform text transformations in Excel. Power Query is the perfect tool for you to solve these tasks that can be quite complicated with just Excel formulas.
Usually, solving these transformations in Excel are NOT an easy task. If you want to do any of the following with Excel formulas, things can get scary very fast:
- Separate words in Excel by a delimiter
- Split a column in Excel based on a certain logic in multiple columns
- Separate text from numbers in a cell
- Extract numbers from the middle of a text string
- Extract text after a specific character in Excel
But fortunately you have another option: Power Query aka Get and Transform. With this magic tool you can achieve this by just clicking a few buttons. And if this was not already enough there is another upside: Once you've setup the logic in Power Query, when new data comes in all you have to do is to click Refresh. And you're done!
In this tutorial I'll show you how you can use Power Query to perform some common text transformation tasks. See for yourself how easy these tasks become with some basic Power Query understanding.
Download the Workbook: https://www.xelplus.com/excel-text-transformation-power-query
Power Query Playlist: https://www.youtube.com/playlist?list=PLmHVyfmcRKyyKV86N7i0q9TfYNN8bBjX-
★ 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 Microsoft 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
29
views
4 Hidden Excel Dashboard Design Tips for Beautiful Reports
4 Jun 2020 Excel Dashboards & Reports
First 1000 people who use the link will get 2 free months of Skillshare premium: https://skl.sh/xelplus11
Creating Excel Dashboards can be very time consuming. You need to define the most important key performance indicators and find a reliable data source for your dashboard. But the visual representation of the data is just as important.
Don't just plaster the report with numbers! Keep enough white space and use shapes to create emphasis. Of course, finding the right balance and implementing it in your Excel Dashboard can take a LOT of time.
In this Excel tutorial, I show you 4 hidden Excel tips that can help you to create a clean, well-designed and informative dashboard without wasting a lot of time.
Leila's Hidden Dashboard Tips:
00:00 Intro
00:55 Convert SmartArt to Shapes
03:32 Hide Unused Rows and Columns
04:20 Select Objects from the Selection Pane
05:15 Use Linked Pictures
Many thanks to Skillshare for sponsoring this video!
⯆ Read the full article & download workbook: https://www.xelplus.com/excel-hidden-dashboard-tips
★ 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 Microsoft 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
37
views
Find Multiple Match Results in Excel (Easier Solution For ALL Excel versions)
28 May 2020 Excel Lookup Formulas
Finding an Excel solution to get MULTIPLE matches for a lookup value is often causing major headaches. The go-to VLOOKUP formula does not work here because it just returns the first match!
What if you wanted to use VLOOKUP but return ALL matches? You can do that now, with the new FILTER function available in Microsoft 365. But what if you don't have Excel 365? Is there an easy function that can return multiple match results?
In this tutorial I'll show you a simple(er) approach that solves this challenge in 2 simple steps. It works in all versions of Excel and is quite easy to remember (at least easier than my original Aggregate version here: https://youtu.be/fDB1Ktyhp3Y). Many thanks to Bob Umlas for sending me this solution!
In addition, I'll show you other options you can apply depending on your version of Excel:
- If you have Microsoft 365 you can take advantage of the new FILTER function
- In Excel 2019 you can use the TEXTJOIN function
- For Excel 2010 and higher you can use the AGGREGATE function
This way you have the COMPLETE guide for any situation on how to find multiple matches in your Excel data set.
00:00 How to Get Multiple Matches in Excel
01:17 Easy Solution For All Excel Versions
11:31 Solution with TEXTJOIN & AGGREGATE
11:50 Easiest Solution with FILTER
12:19 Wrap Up
⯆ DOWNLOAD the workbook here (scroll to the bottom of the post): https://www.xelplus.com/excel-multiple-match-results-complete
LINK to original video: https://youtu.be/fDB1Ktyhp3Y
FILTER: https://youtu.be/Eehk6PC0oGs
TEXTJOIN & AGGREGATE: https://youtu.be/fDB1Ktyhp3Y
★ 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
19
views
Easiest way to COMBINE Multiple Excel Files into ONE (Append data from Folder)
21 May 2020 Excel Power Query (Get & Transform)
Join 400,000+ professionals in our courses: https://www.xelplus.com/courses/
Prepare to be amazed! This is the easiest way to combine multiple Excel files from a folder into one Workbook and create a table or a a pivot table report based on the consolidated data.
★ My Online Excel Courses ► https://www.xelplus.com/courses/
00:00 Consolidate Data from Multiple Excel Files
01:28 Get & Transform Data From Multiple Files in a Folder
02:37 Merge and Append Data in Power Query
05:55 Clean Data in Power Query
07:10 Load Data as PivotTable to Excel
09:21 Load Data as Table to Excel
09:53 Wrap Up
Many of us send out templates to colleagues and are later faced with the challenge to somehow consolidate multiple Excel files. To append the data into a single Excel workbook can be quite a troublesome and lengthy process.
Sure, a solution for this has always been Excel VBA. But learning VBA takes time and it can be too difficult for a lot Excel users.
In this tutorial, I'll show you a much easier approach! We'll use Excel Get and Transform aka Power Query to combine data from multiple Excel workbooks. We will directly connect to a folder that contains the files and use the feature Combine Files in Power Query. Any time you drop an Excel file in that folder, the data will be appended and consolidated with the rest. You can then create one Excel table for the appended data or create a pivot table from the multiple Excel files.
The best part about using Power Query to merge data from Excel files is that you don't need to write any code! You just need to click a few buttons to append the data from the files.
Read the article: https://www.xelplus.com/power-query-combine-files-folder
Power Query Playlist: https://www.youtube.com/playlist?list=PLmHVyfmcRKyyKV86N7i0q9TfYNN8bBjX-
✉ 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/
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!
#MsExcel #Excel #LeilaGharani
42
views
How to Use OneNote Effectively (Stay organized with little effort!)
14 May 2020 OneNote Tips
First 1000 people who use the link will get 2 free months of Skillshare premium: https://skl.sh/xelplus10
Most of us take notes in some way or another - on a piece of paper, in a notebook, or with some app. But are your notes always readily available when you need them? And more importantly, can you easily find a specific note you're looking for?
Join 400,000+ professionals in our courses: https://www.xelplus.com/courses/
★ Get the Official XelPlus MERCH: https://xelplus.creator-spring.com/
I tried a lot of note-taking systems in the past and I can't tell you how many great ideas were lost over the years because I just couldn't find the piece of paper anymore, I scribbled some notes on. It wasn't until I found a way to use Microsoft OneNote effectively that it changed for the better. Many people don't know that OneNote is much more than just a digital notebook.
In this OneNote tutorial (in 2020), I explore how you can take full advantage of OneNote, and share my 5 personal OneNote tips and tricks on how you can become more organized.
Leila's Tips:
00:00 Why Use OneNote
01:46 Use Hierarchies to Get Organized
04:37 Use Tags to Find What You Need
07:04 Capture Ideas Anywhere With Sticky Notes
08:23 Extract Text From Images
09:35 Focus With Immersive Reader
11:04 Additional features in OneNote you may find helpful (Ink to Shape, Ink to Text, Ink to Math)
You can download OneNote for free and and it's available for Windows and for Mac.
Get David Allen's book - Getting Things Done: https://amzn.to/2SZeFOt
⯆ Read the OneNote article: https://www.xelplus.com/onenote-tips-and-features
Microsoft 365 playlist: https://www.youtube.com/playlist?list=PLmHVyfmcRKyzpZhCj37RJTyIQBssgM85f
★ 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!
#OneNote #MsExcel #Excel #LeilaGharani
69
views
Create an Interactive Top N Report in Excel (includes duplicate values) with Filter Function
7 May 2020 Excel for Office 365 & Dynamic Arrays
Add this interactive report to your Excel dashboard if you want to make an impression! It's INTERACTIVE so the user can decide which TOP N records of a data-set they'd like to see. It will also NOT have a problem with showing MULTIPLE MATCH results either! This way you get to quickly rank the data and categories automatically and be able to show duplicate values with their correct categories.
Instead of a complex formula with the LARGE function in a lookup-formula like INDEX & MATCH, VLOOKUP, or XLOOKUP we will use my new favorite Excel function: The FILTER function! Together with the SORT function we will create a simple formula to extract the data automatically. Both functions are available exclusively in Excel for Office 365 with the new Excel Dynamic Arrays features.
We will even improve the report and make sure the user cannot input a number that's exceeding the available options in the data set. This will help avoid the #Spill error. To do that we'll use a combination of the COUNT formula and the SEQUENCE function.
★ Want to learn more about the NEW Excel? Check out my course: https://courses.xelplus.com/p/excel-dynamic-array-formulas
⯆ DOWNLOAD the workbook here: https://www.xelplus.com/excel-top-values-filter-function
Check out the complete Excel for Office 365 playlist: 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
26
views
Discover the Roadmap to Engaging PowerPoint Presentations
30 Apr 2020 PowerPoint & Presentation Tips
Link to Simple Slides to make better slides FAST: https://www.simpleslides.co/a/45563/yoW5RmAU
None of us wants to be the person who delivers boring presentations that put people to sleep. In this video I'll share with you simple techniques to give your PowerPoint presentation a perfect flow to captivate your audience and keep them engaged.
Creating great PowerPoint slides doesn't have to be difficult if you follow a few simple rules and some guidelines.
01:48 Use Roadmaps instead of a table of contents in your PowerPoint presentations
Use Infographics to indicate the progress that was made in the presentation. This way the audience knows where the end is and which remaining topics they can expect to learn from you.
06:17 Put Essential text only on slide and use a supporting Infographic or Roadmap
It's practical for you as the presenter to have everything you want to say on a slide. But the audience can't read the text on a crowded slide and listen to you at the same time. This will draw their attention away from you, the presenter.
07:28 Take advantage of the Reuse Slides Feature in PowerPoint
Avoid copying and pasting slides if you want to use slides from an existing presentation. Instead apply Reuse Slides from the Home tab in PowerPoint. It's faster and safer!
Slide Zoom Video: https://youtu.be/dGqHSvs9qpE
⯆ The full article: https://www.xelplus.com/powerpoint-roadmaps
LINKS to more PowerPoint videos: https://www.youtube.com/playlist?list=PLmHVyfmcRKywJTPy2_OBZEaKeGGdFXlX3
★ 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
Time Stamps
00:00 What Makes a PowerPoint Presentation Boring?
01:48 Use Roadmaps in PowerPoint Instead of a Table of Contents
06:17 Only Put Essential Text and Use Supporting Infographics in Your Slide
07:28 Take advantage of the PowerPoint Reuse Slides Feature
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!
This video was sponsored by Simple Slides.
#PowerPoint #MsExcel #Excel #LeilaGharani
33
views
How Familiar Are You With Excel Cell Modes? (Ready, Enter, Point, Edit)
23 Apr 2020 Excel Introduction - Learn Excel Basics
This Excel topic is not talked about often: Excel Cell Modes.
Once you know which mode you're in: Ready, Enter, Point or Edit, it will be easier to decide whether to use the arrow keys or switch your mode first.
This is the reason why Excel behaves differently when you're in a certain dialogue box. For example when you go to data validation and would like to adjust the formula, Excel adds a cell reference when you try to move around with the arrow keys. This is because of the Excel Mode you're in.
You can see the active mode in the bottom left corner of your Excel spreadsheet. The normal cell mode is called Ready Mode. The moment you start to input something in a cell Excel will remove "ready" mode to Enter Mode. When we input a formula and start using the arrow keys Excel changes directly to Point Mode for formula selection.
When you want to change an existing formula in a cell Excel will change to Edit Mode. In this mode the arrow keys can be used to move around inside the formula. But if you want to change a reference in the formula you can't use the arrow keys. You first have to change to Point Mode by using the short cut key F2.
When you're inside dialogue boxes like Conditional Formatting, Data Validation, Name Manager, or Chart Ranges use F2 to change modes.
⯆ Written article about Excel Cell Modes: https://www.xelplus.com/excel-cell-modes
Excel Basics Playlist: https://www.youtube.com/playlist?list=PLmHVyfmcRKyx1KSoobwukzf1Nf-Y97Rw0
★ 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
32
views
Many SEARCHABLE Drop-Down Lists in Excel (No VBA)
16 April 2020 Excel for Office 365 & Dynamic Arrays
I already shared a video on how you can create a SEARCHABLE drop-down list in Excel. The solution was for a SINGLE row. But now we'll take this to the next level and replicate the searchable drop-down list (which is a simple Excel data validation list) to MULTIPLE Excel rows without VBA and without combo-boxes. We'll be using the new excel Dynamic array functions which are a part of Excel for Office 365. With these functions we can create a data validation list that suggests search results based on the user input. The match is made based on any part of the text - the beginning, middle or end.
We'll use the the new FILTER function together with the SORT function. We'll combine these with legacy excel formulas like the SEARCH function (2:00) and the Transpose functions (8:30).
As a bonus we'll also cover the XLOOKUP formula (10:40)
★ Link to complete Excel Dynamic Arrays Class: https://courses.xelplus.com/p/excel-dynamic-array-formulas
⯆ DOWNLOAD the workbook here: https://www.xelplus.com/excel-searchable-drop-down-every-row
Check out the complete Excel for Office 365 playlist: 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
40
views