MrExcel.com
Excel - Show AutoFilter Selected Items - Episode 1827
updated
My dad's favorite actor, Max Gail, who played Det. Stan "Wojo" Wojciehowicz for eight seasons on Barney Miller reflects on how far Excel has come in one lifetime.
Also - if you are going out to celebrate tonight, take it easy on the Powerpivotini's or you might end up down at the 12th Precinct!
This is the last of my 2023 Spreadsheet Day Cameos. Have a great evening!
Spreadsheet Day is October 17, 2023 and marks the anniversary of when Dan Bricklin and Bob Frankston first released VisiCalc in 1979. The holiday was created by Debra Dalgleish of Contextures.com.
Many thanks to Alysia Reiner, the amazing actress who plays Fig on Orange is the New Black. I asked if she could improv, as Fig, her reaction to my request for time off for Spreadsheet Day. Awesome. My new favorite spreadsheet day skit.
World-famous street performer Robert Burck takes a minute out of his busy schedule entertaining tourists in New York City's Times Square to sing this original spreadsheet day song for 2023.
Spreadsheet Day is October 17, 2023 and marks the anniversary of when Dan Bricklin and Bob Frankston first released VisiCalc in 1979. The holiday was created by Debra Dalgleish of Contextures.com.
Spreadsheet Day is October 17, 2023 and marks the anniversary of when Dan Bricklin and Bob Frankston first released VisiCalc in 1979. The holiday was created by Debra Dalgleish of Contextures.com.
Spreadsheet Day is October 17, 2023 and marks the anniversary of when Dan Bricklin and Bob Frankston first released VisiCalc in 1979. The holiday was created by Debra Dalgleish of Contextures.com.
In this quick and helpful Excel tutorial, we're going to show you exactly how to clean up your spreadsheet and get rid of those pesky lines and gridlines that can clutter your view. Whether you're dealing with borders, gridlines, or even strikethrough text, we've got you covered!
π΅ Borders Be Gone!
If you're dealing with border lines that you want to remove, it's as easy as 1-2-3. Just select the data with borders, head to the Home tab, open the Borders drop-down, and choose "No Border." Your spreadsheet will instantly look cleaner and more professional.
π΄ Gridlines Be Gone!
Those light grey gridlines that seem to always be there can be a distraction. To make them disappear, simply go to the View tab and uncheck "Gridlines." Your eyes will thank you for the clarity.
βͺ Selective Gridline Removal!
But what if you only want to get rid of gridlines in a specific part of your spreadsheet? No problem! Use the Home tab, grab the Paint Bucket, and choose white. Apply it to the area where you want gridlines gone, and voilΓ ! You've got a clear section.
π Strikethrough Shortcut!
And for those lines striking through your words, press Ctrl+5 to toggle Strikethrough on and off. It's a neat trick to keep your text looking clean and precise.
If you found this Excel tutorial helpful, don't forget to show your support by giving us a thumbs up, hitting that subscribe button, and ringing the notification bell. We're here to help you excel in Excel! Feel free to drop any questions or comments down below. Your feedback drives us to create more valuable content for you. Happy spreadsheeting! πΌππ
#excel
#microsoft
#exceltutorial
#exceltips
#microsoftexcel
#exceltricks
This video answers these common search terms:
excel how to completely remove borders
how do i delete a line in excel
how do i remove a line in excel
how do you delete a line in excel
how do you delete a line in excel within a cell
how to delete a line to excell cell
how to delete an entire border in excel
how to delete black line in excel
how to delete borders from excel
how to delete borders in a excel spreadsheet
how to delete borders in excel
how to delete cell borders excel
how to delete cell borders in excel
how to delete line in excel
how to remove a line inside an excel cell
how to remove all borders excel
how to remove all borders in excel
how to remove all cell borders in excel
how to remove blank line in excel
how to remove borders from an excel cell
how to remove borders from cells in excel
how to remove borders in excel
how to remove borders on excel table
how to remove cell borders excel
how to remove cell borders in excel
how to remove divider line in excel
how to remove excel borders
how to remove grey border in excel
how to remove text line in excel
how to remove the line in excel
youtube remove border in excel
how to do a strikethrough line in excel
how to strikethrough a line in excel
where is the strikethrough line in excel?
did excel remove strikethrough
excel how to remove strikethrough
hoe to add remove strikethroughs excel
how do draw a strikethrough in excel
how do you remove a strikethrough in excel
how do you remove a strikethrough in excel?
how to add or remove strikethrough in excel?
how to remove default cell borders in excel
how to remove diagonal line in excel cell
how to remove grid line from excell
how to remove gridline in excel
how to remove light borders in excel
how to remove line in excel
how to remove line on excel
how to remove strike line in excel
Table of Contents
(0:00) Remove gridlines in Excel
(0:10) Remove borders in Excel
(0:20) Remove gridlines from one range
(0:30) Remove strike through
(0:40) Clicking Like really helps the algorithm
To download the workbook from today: mrexcel.com/youtube/mpQ7IrPzenM
Copy this code and put it in your personal macro workbook:
Sub FlipCheckboxes()
On Error Resume Next
For Each cell In Selection.SpecialCells(xlCellTypeConstants, 4)
cell.Value = Not (cell.Value)
Next cell
On Error GoTo 0
End Sub
Sub AllTrueCheckboxes()
On Error Resume Next
For Each cell In Selection.SpecialCells(xlCellTypeConstants, 4)
cell.Value = True
Next cell
On Error GoTo 0
End Sub
Sub AllFalseCheckboxes()
On Error Resume Next
For Each cell In Selection.SpecialCells(xlCellTypeConstants, 4)
cell.Value = False
Next cell
On Error GoTo 0
End Sub
π Excel VBA Macro To Flip All Checkboxes In Excel
Are you tired of manually toggling checkboxes one by one in your Excel spreadsheet? We've got a game-changing Excel VBA macro that will make your life so much easier! πβ¨
π Video Highlights:
π±οΈ Multi-Select Magic: Learn a cool trick to multi-select checkboxes and control their state.
π Flipping the Script: Discover why the default checkbox behavior might not be what you want and how to fix it.
π οΈ Macro Solutions: See how to use macros to flip all checkboxes at once, set them all to true, or set them all to false.
π§© Diving into the Code: We'll walk you through the code step by step, so you understand how it works and can customize it to your needs.
π Boost Your Productivity: Add these macros to your personal macro workbook and quick access toolbar for instant access to these powerful tools.
π‘ Why This Matters:
Manually changing checkbox states can be time-consuming and frustrating. With this macro, you can effortlessly manage checkboxes in your Excel spreadsheets, saving you valuable time and effort.
π©βπ» Code Simplified:
We'll break down the VBA code into easy-to-understand steps, so you don't need to be a programming expert to implement these macros.
π Stay in the Loop:
Don't miss out on more Excel tips and tricks! If you found this video helpful, make sure to give it a thumbs up, hit that subscribe button, and ring the notification bell. We're here to empower you with Excel expertise.
π¬ Got Questions or Comments?
Feel free to share your thoughts, questions, or suggestions in the comments section below. We value your feedback and are here to help!
Thanks for joining us on this Excel journey. We'll see you in the next video for more Excel insights and hacks. Happy spreadsheeting! πππ΅
Buy Bill Jelen's latest Excel book: mrexcel.com/products/latest
#excel
#microsoftexcel
#exceltricks
#excelhacks
#excelvba
This video answers these common search terms:
how to change the checkbox in excel
how to toggle all checkboxes in excel
how to flip all checkboxes in excel
how to use checkbox macro in excel
how to use checkboxes in excel
how to create and use a checkbox in excel?
excel how to add and use checkboxes
Table of Contents
(0:00) Reverse active cell and propagate
(0:20) Multi-select checkboxes and press space
(0:35) VBA Macro to Flip checkbox state
(0:50) VBA Code is in YouTube Description
(1:19) Clicking Like really helps the algorithm
π Want to upgrade the look of your Excel checkboxes? In October 2023, a new Checkbox icon was introduced on the Insert tab. While it offers a convenient way to toggle checkboxes using your mouse or the spacebar, it can feel a bit cluttered.
π In this quick tip, discover a sleeker alternative! Learn how to add checkboxes and then make them disappear with a simple press of the Delete key. They'll reappear when you hover over the cells, and with a click or spacebar press, you can easily turn them on.
π However, keep in mind that when you turn them off later, the unchecked box remains visible. If you found this tip helpful, be sure to show your support by giving this video a Like π, Subscribe π, and Ringing the Bell π down below!
Got questions or comments? Feel free to share them in the comments section. We're here to help you excel with Excel! π
Table of Contents
(0:00) New checkbox feature in Excel is busy
(0:18) Insert checkbox then delete for hover feature
(0:31) Clicking Like really helps the algorithm
#excel
#microsoft
#exceltips
#exceltricks
#excelhacks
#excelnew
This video answers these common search terms:
check box excel how to
add check boxes in excel to cell
how to add boxes to check in excel
how to put box check in excel
make check boxes in excel
how to make boxes check boxes excel
To download the workbook from today: mrexcel.com/youtube/kunbe45v7-E
In this YouTube video, Bill Jelen enthusiastically announces the arrival of checkboxes in Microsoft Excel. They reminisce about the challenges they faced with checkboxes in the past, particularly when working on a report card system for a school district.
Jelen demonstrates how to use the new checkbox feature, emphasizing its location on the "Insert" tab. They show how to insert checkboxes into cells, toggle them on and off, and even use the space bar for quick toggling. The video also explores the underlying true or false values associated with checkboxes and how to remove them to revert to regular true and false values.
Some limitations are discussed, such as the inability to use checkboxes in data validation or within pivot tables. The video also highlights a peculiar behavior where hovering near existing checkboxes seems to automatically insert and activate new ones, though the exact rules for this behavior remain unclear.
The speaker attempts to address the question of displaying words next to checkboxes and mentions that some methods like number formatting didn't seem to work, inviting viewers to share their insights in the comments.
The video concludes by noting that if someone without the feature opens a workbook containing checkboxes, they will see the values as true and false. The speaker expresses gratitude to the Excel team for the feature and thanks viewers for watching, leaving them with the promise of more content in the future.
Buy Bill Jelen's latest Excel book: mrexcel.com/products/latest
#excel
#microsoft
#microsoftexcel
#walkthrough
#excelnew
This video answers these common search terms:
how to do checkbox in excel formula
can you check a box with excel macro
how to use conditional formatting in excel and check boxes
how to apply conditional formatting to a check box in excel
how to add checkbox and text in excel
how to check box in excel with vba
check box excel how to
add check boxes in excel to cell
how to add boxes to check in excel
how to put box check in excel
make check boxes in excel
how to make boxes check boxes excel
adding check boxes in excel
create check boxes in excel
how add checkbox excel
how add checkbox in excel
how do check boxes in excel
how to add a box to check in excel
how to check box excel
adding a checkbox on excel
how add check box to excel
adding a check box to excel
how add a checkbox in excel
how to check a box excel
how to check a box in excel
how to do checkbox in excel
how to do checkboxes in excel
can i do a checkbox in excel
can i put check boxes in excel
can you do checkboxes in excel
how insert checkbox in excel
how to add checkbox at excel
how to add checkbox in cell excel
how to add checkbox in cell for excel
how to add checkbox in excel
how to add checkbox in excel
Table of Contents
(0:00) Walkthrough: Checkboxes in Excel
(0:22) Checkbox icon on Insert Tab
(0:40) Spacebar or mouse to Toggle checkbox
(0:48) Stores True/False in the cell
(0:58) Clear formats to remove checkbox but leave True/False
(1:13) Convert formula results to checkboxes
(1:30) Use conditional formatting to change color
(1:50) Validation drop-down does not show checkbox
(2:09) Will not work in pivot table
(2:40) Empty cells near checkboxes have interesting feature
(3:23) Adding a word next to checkbox?
(3:33) If workbook opened on Excel without feature - True/False
(3:56) Clicking Like really helps the algorithm
Welcome to episode 2627 of MrExcel's YouTube channel! In this video, we will be discussing a question sent in by Derek about calculating annual repair costs by age of vehicle. Derek has a fleet of vehicles that he needs to account for and wants to know the average cost to maintain and repair each vehicle. So, in this episode, we will uncover the secrets to calculating these costs using Excel.
To begin, we will be working with a data set similar to Derek's, with a list of all the repairs made on the fleet including the vehicle, date, and cost. Our first step is to determine the date each vehicle was placed in service. This can be done by using the MINIFS function to find the earliest service date for each vehicle. We will also approximate the date in service for new vehicles by subtracting a certain number of days from the first service date.
Next, we will use the VLOOKUP function to retrieve the date placed in service for each vehicle. Then, using the DATEDIF function, we will calculate the age of the vehicle at the time of each repair. This will give us a table showing the vehicle and its age at the time of each repair.
To get the total spent in each year for each vehicle, we will create a pivot table. This will allow us to see the average cost to repair each vehicle based on its age. It is interesting to note that there is a period where the average cost decreases, which could be due to a small sample size or fake data. However, with real data, this method can provide a good approximation of the average cost to repair a vehicle based on its age.
I want to thank Derek for sending in this great question and I hope this video has helped you uncover the secrets to calculating vehicle repair costs using Excel. If you enjoyed this video, please don't forget to like, subscribe, and ring the bell to be notified of future episodes. And as always, feel free to leave any questions or comments in the section below. Thank you for watching and we'll see you next time for another netcast from MrExcel.
Buy Bill Jelen's latest Excel book: mrexcel.com/products/latest
You can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtube
To download the workbook from today: mrexcel.com/youtube/lfn5FGRA5wg
This video includes the DATEDIF function in Excel.
Creating a pivot table in Excel.
VLOOKUP in Excel.
Buy Bill Jelen's latest Excel book: mrexcel.com/products/latest
You can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtube
#excel
#microsoft
#exceltips
#microsoftexcel
#exceltricks
#walkthrough
#pivottable
#pivot_table
#excelpivot
#excelpivottablestutorial
This video answers these common search terms:
how to calculate yearly average in excel
how to get monthly average excel
how to average monthly data in excel
how to find daily average in excel
how to calculate daily average in excel
how to calculate daily average in excel formula
how to calculate average daily sales in excel
how to calculate average age in excel formula
how do you calculate average age in excel
how to average multiple columns in excel
how to average multiple data in excel
how to calculate average in excel
how does excel calculate average
how to use minifs function in excel
how to use minifs in excel
how to use minifs excel
excel formula to find the earliest date
how to use datediff in excel
how to do a datedif function excel
how to use datedif function excel
how to use datedif in excel
how to use the datedif excel function
where is datedif in excel
where is datedif in excel
how to use excel formula datedif
how to use a datedif function in excel
how to use datedif function in excel
how to use the datedif function in excel
Table of Contents
(0:00) Problem Statement: Average cost to maintain vehicle based on age of vehicle
(0:22) Data has Vehicle ID, Repair Date, Repair Cost
(0:50) Find earliest service date per vehicle using MINIFS
(1:50) Approximate Date Placed in Service from First Repair Date
(2:32) Use VLOOKUP in original data to get In-Service date
(3:05) Use DATEDIF to find age in Years in Excel
(3:57) Building a pivot table
(4:13) My pivot table defaults are wrong for this analysis
(4:31) Displaying blank cells in a pivot table for AVERAGE function
(5:00) AVERAGE function ignores blank cells
(5:50) Clicking Like really helps with the algorithm
Which teams are still in the hunt for the MLS Supporters Shield?
Update from September 25, 2023: Over the weekend, Cinci won their match. This leaves only Cinci, New England, Orlando City, and Philly in the hunt. Cinci's percentage has improved to 99.292%. All that Cinci has to do to wrap up the Supporters Shield is win one match or draw one match and have New England and Philly draw or lose.
To download this workbook: mrexcel.com/youtube/1VLFg7VeW7M
Welcome to episode 2626 of MrExcel's netcast, where we tackle the burning question of who is still in the hunt for the MLS Supporters' Shield as of September 22, 2023. In this video, we will use Excel to perform Monte Carlo analysis and simulate the remaining matches to determine the potential winners of the Shield.
As of September 23rd, 2023, Cincinnati has been the clear favorite for the Supporters' Shield, with a 15-point lead since June or July. However, their recent winless streak in September has allowed other teams to close the gap. In this video, we will explore the possible outcomes if Cincinnati were to lose all remaining matches and determine which teams have a chance to surpass their current 59 points.
Using the RAND function in Excel, we will simulate the remaining matches and calculate the points gained by each team. We will also utilize the new VSTACK function to stack the home and away team points into a single array. Then, using the SUMIF function, we will determine the total points gained by each team and their final points tally.
Initially, I made a critical math error and calculated 39 matches left instead of 39 to the third power, which is an insanely large number. To overcome this, we will run the simulation 100,000 times using the SEQUENCE function and the data table feature in Excel. This will give us a better understanding of the possible outcomes and the percentage chance of each team winning the Supporters' Shield.
In the end, Cincinnati still has a 96.7% chance of winning the Shield, but there is a 1.3% chance for Orlando City to overtake them and a 0.3% chance for Philadelphia to win outright. There are also some interesting scenarios where tiebreakers may come into play, such as the game between New England and Philly on Decision Day.
Even if your team is not currently in the running, there is still a chance for them to win the Supporters' Shield. We could run this simulation another 100,000 times and have a completely different set of results. So, good luck to all the teams still in the hunt and thank you for watching this episode of MrExcel's netcast. Don't forget to Like, Subscribe, and Ring the Bell for more Excel tips and tricks. Feel free to leave any questions or comments down below. See you next time!
Buy Bill Jelen's latest Excel book: mrexcel.com/products/latest
You can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtube
Buy Bill Jelen's latest Excel book: mrexcel.com/products/latest
Just 27 days after Python preview appears in Excel, Microsoft has added a dramatically better Python editor.
To download the workbook from today: mrexcel.com/youtube/UKpmNAMjygg
Welcome to episode 2625 of the MrExcel netcast, where we explore all things Excel. In today's episode, we're diving into the exciting news that Excel has added a Python editor just 27 days after Python's debut in the program. This is a game-changing addition for all Excel users, and we can't wait to show you all the amazing features it has to offer.
But before we get into the details, I want to give a shout out to Waldemar on Unsplash.com. If you're ever in need of silo photos as a metaphor for corporate environments, this guy has some truly beautiful shots. Now, let's get back to the main event - the Python editor in Excel.
There are a few theories as to why the editor was added just 27 days after Python's debut. One possibility is that the editor simply took longer to develop. But another theory is that the team at Excel Labs in Cambridge saw the debut of Python and thought, "We can make this even better." If that's the case, then kudos to them for developing and testing the editor in just 27 days.
To access the Python editor, simply go to the Get Add-Ins option, which can be found on either the Insert or Home tab, depending on your version of Excel. From there, search for Excel Labs and click on the Advanced Formula Environment. If you already have this add-in, you may need to update it to access the Python editor.
Once you have the editor open, you'll notice that it has a similar layout to the formula bar, but with some major improvements. For starters, you can choose to work in either one specific cell or all Python cells in your workbook. The code is also color-coded, making it much easier to read and navigate. And perhaps the most exciting feature - auto-complete is now available when writing Python code in Excel.
But that's not all - the Python editor also has a scratch pad feature, allowing you to save your code in the task pane and come back to it later. You can even switch to manual calculation mode to only run specific Python cells, saving you time and effort. Overall, this is a huge improvement from the Excel Labs team and we can't believe we've been writing Python without it.
Thank you for tuning in to today's episode of the MrExcel netcast. If you enjoyed this video, please don't forget to like, subscribe, and ring the bell for notifications on future episodes. And as always, feel free to leave any questions or comments down below. See you next time!
Buy Bill Jelen's latest Excel book: mrexcel.com/products/latest
You can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtube
This new editor in the task pane offers AutoComplete, Intellisense, and automatic code coloring. Take a walkthrough the editor in today's video.
#excel
#microsoft
#microsoftlabs
#microsoftexcel
#exceltricks
#excelpython
#microsoft365
#walkthrough
Buy Bill Jelen's latest Excel book: mrexcel.com/products/latest
Table of Contents
(0:00) Python Editor Added to Excel
(0:10) Free Silo Photos
(0:20) Theories on backstory
(0:59) Get Add-Ins moving from Insert to Home tab
(1:22) Update Excel Labs add-in twice
(1:49) Open Excel Labs & Python Editor
(2:00) Contrast Formula Bar editor to Python Editor
(2:17) Expand Editor
(2:33) Pane shows preview of data frame
(2:45) Color coded
(3:00) Images won't render in editor
(3:10) Adding a new Python cell
(3:24) AutoComplete
(3:45) Choose Python object or Excel values
(3:56) Easier to use XL function in formula bar than python editor
(4:50) Sorting in Python
(5:40) Scratchpad without committing the code
(6:03) In Manual Calculation mode, only one cell calculates when committing code
(6:34) Wrap-up
I met people who are downloading data from Oracle through Analysis Services. Their dates are coming in as text in the format of Sep-20 for September 2020. When they create pivot tables, the months are alphabetic instead of sequential.
To download the workbook from today: mrexcel.com/youtube/BpgjOMlh39E
Welcome to episode 2624 of MrExcel's Netcast, where we explore all things Excel. In today's video, we will be discussing a common issue faced by many Excel users - Oracle sending dates as text in the format MMM-YY. This can cause chaos when creating pivot tables, as the dates are sorted alphabetically instead of chronologically. But don't worry, we have some Excel hacks that will help you solve this problem in no time.
In this video, I will be showing you two ways to solve this issue. The first method is a bit convoluted, but it only needs to be done once and never again. The second method is super easy, but unfortunately, it didn't work for me. So, I am calling out to all the Power Query and Formula experts out there to share their solutions in the comments below. Let's work together to find the best and most efficient way to handle this problem.
So, let's dive into the issue. We have a refreshable query from Oracle that sends dates in the format SEP-23. This is causing problems when creating pivot tables, as the dates are not in the correct chronological order. To solve this, we will be using a trick that was shown to me by Sam Radakovitz many years ago. We will create a custom list of dates and use a formula to convert the text dates into the desired format. However, there is a catch - custom lists do not allow formulas. But don't worry, we have a workaround for that too. Just follow along with the video and you'll have your dates sorted in no time.
But what if your data is not in Excel and is coming from SQL Server Analysis Services? Don't worry, we have a solution for that too. We will be using the Data Model and a few extra steps to get the dates in the correct order. And for those of you who are wondering how far back you can go with this solution, the answer is three years. But if you're young and have a long Excel career ahead of you, make sure to put a reminder in your calendar for January 2041 to refresh your memory on how to do this.
I hope you found this video helpful and learned some new Excel hacks. If you did, don't forget to hit the like button and subscribe to our channel for more Excel tips and tricks. Also, make sure to ring the bell icon to get notified every time we upload a new video. And as always, feel free to leave your questions and comments down below. Thank you for watching and we'll see you in the next episode of MrExcel's Netcast.
Buy Bill Jelen's latest Excel book: mrexcel.com/products/latest
You can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtube
There are two ways to solve it:
1) A convolunted method from Sam Radakovitz that you only have to do once
2) A simple method that you will have to do 1000 times a year.
In this episode, I show Sam Rad's method of setting up a custom list with the text months in the correct sequence. If you use this method with a pivot cache pivot table (any pivot table where the data is a regular Excel range), the months will start sorting correctly.
But if your data is a cube or coming from external sources, then you have an extra eight steps in each pivot table to correct the month sequence. However, this is still faster than manually rearranging fields in the pivot table.
In the outtake, I show my fastest method for changing text months in Excel to real dates and ask if you have anything faster.
Buy Bill Jelen's latest Excel book: mrexcel.com/products/latest
#excel
#microsoft
#exceltutorial
#exceltips
#microsoftexcel
#exceltricks
#pivottable
Table of Contents
(0:00) Pre-roll ask for help
(0:36) Problem Statement: Text Dates show up as MMM-YY
(1:01) Pivot table is sorting months alphabetically
(1:17) Setting up a text custom list of months
(1:39) Converting dates to text using TEXT()
(2:06) Importing the custom list
(2:49) Regular pivot table automatically works
(3:12) If pivot table based on external data, does not work
(3:37) Sorting pivot table on custom list if based on data model
(4:03) Wrap up
(4:16) Outtake: Convert with Text to Columns
(5:08) Outtake: Convert with Ctrl+H & then Text to Columns
(5:48) Another Ctrl+H solution that works faster
(6:11) Power Query Column from Examples
Melvin from Orlando shared a great Excel trick with me during yesterday's live Power Excel seminar in Daytona Beach.
To download the workbook from today: mrexcel.com/youtube/Od5IwswzWnI
We all know you can use the Filter drop-down Search Box to find all cells that contain Apple. But what if you want everything that does not contain Apple? Rather than use the old Text Filters for Does Not Contain, you can use this great trick from Melvin:
1. Search for Apple
2. Uncheck Select All Items
3. Check Add Current Selection to Filter
This applies a "Negative" filter to the current filter, removing items.
Buy Bill Jelen's latest Excel book: mrexcel.com/products/latest
#microsoftexcel #microsoft #exceltutorial #excelfilter
Welcome to another episode of Excel Hacks! In today's video, we're going to learn a new trick that I'm pretty sure you've never seen before. We'll be using the Filter Search box to remove items from our filter. This hack was shared with me by Melvin from Orlando during a seminar in Daytona Beach, and I have to say, it's a game changer.
As we all know, using the Filter box to find specific items is a common practice. But what if we wanted to get rid of a whole bunch of items? For example, let's say we want to remove all the items that start with the letter C in our Category column. Manually unchecking each item would be a tedious task. But with Melvin's trick, we can do it in just a few clicks.
Here's how it works. First, we type in the letter C in the Filter Search box, which gives us a list of all the items starting with C. Then, we select all the search results and click on "Select All Search Results". This effectively unchecks all the items starting with C. Finally, we click on "Add Current Selection To Filter" and voila! All the items starting with C disappear from our filter. It's that simple!
Buy Bill Jelen's latest Excel book: mrexcel.com/products/latest
You can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtube
Table of Contents
(0:00) Problem Statement: Remove anything that contains Apple from Filter in Excel
(0:15) Search Excel Filter for Apple
(0:30) Filter out anything starting with C
(1:18) Does Not Contain Text Filter
(1:46) Filter out anything starting with B and Apple
(2:02) Remove apple or berry from same column
(2:34) Did you know this?
(2:45) Wrap-up, Subscribe, Like, Ring the Bell
Today's question from Rebaz on podcast 1984 - Excel Sum Across Worksheets: "if we have different cell in different sheet, how can I Sum?"
Download the workbook from today: mrexcel.com/youtube/UO11Ase1_Ys
This video shows you an easy way to build a 3-D reference in Excel, also known as a spearing formula. Excel functions include SUM, XLOOKUP, FILTER, SUMIFS, TEXTJOIN, TEXTSPLIT, SUMPRODUCT, Helper Arrays, LET, Python in Excel, and VSTACK.
Welcome to episode 2622 of the MrExcel Netcast. Today, we have a great question from Rebaz about summing across sheets when the rows are not lined up. This can be a tricky task, but fear not, we have some solutions for you.
In the past, it was simple to sum across sheets when the rows were in the same order. However, with each sheet now being sorted differently, we can't rely on the fact that the data will be in the same place each time. So, we need to find a way to sum across sheets without knowing the exact location of the data.
We start with a simple 3-D formula, using the SUM function. By clicking on the first sheet and then shift-clicking on the last sheet, we can select the cells we want to sum. However, this method may not work for all functions. So, we explore other options such as XLOOKUP, FILTER, and SUMIFS, but unfortunately, these do not work with 3-D references.
But fear not, there are still some functions that do work with 3-D references. One of them is TEXTJOIN, which can combine data from multiple sheets into one long string. We can then use TEXTSPLIT to split this string and get the data we need. Another option is VSTACK, which allows us to stack data from multiple sheets into one array.
If you're using Microsoft 365, you can also use Power Query to sum across sheets. And if you have any other suggestions or solutions, please share them in the comments below. It would also be great to have a comprehensive list of functions that work with 3-D references, so if anyone knows of one, please share it with us.
Thank you for tuning in to this episode of the MrExcel Netcast. If you enjoyed this video, please like, subscribe, and ring the bell to be notified of future episodes. And don't forget to leave your questions and comments down below. See you next time!
Buy Bill Jelen's latest Excel book: mrexcel.com/products/latest
You can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtube
Table of Contents
(0:00) Problem: Adding Across Sheets that are not lined up
(0:43) How to build a 3-D Reference
(1:19) Does XLOOKUP work with 3-D?
(1:50) Does FILTER work with 3-D?
(2:09) Does SUMIFS work with 3-D?
(2:38) TEXTJOIN works with 3-D References
(3:23) TEXTSPLIT of TEXTJOIN
(3:48) Building out TEXTJOIN solution
(5:36) Using Python in Excel Fails with a Formula
(6:01) VSTACK works with 3-D
(6:45) Could use Power Query
(7:04) How would you do this?
(7:09) Is there a list of Excel functions that work with 3-D?
(7:36) Wrap-up / Nancy Faust
(7:45) Please subscribe
To download today's workbook: mrexcel.com/youtube/3rAJPcjO6Ok
Today, a question about creating a Python data frame from multiple Excel sheets. I use the CONCAT function in Python but then realize that the headings are repeated.
So I show how to use .tail(-1) to remove the top row from each data frame except the first.
Welcome to another episode of the MrExcel podcast, where we tackle your toughest Excel questions. Today's question comes from a viewer who wants to know if it's possible to define a data frame from multiple worksheets with the same column titles. The answer is yes, and in this video, we'll show you how to do it using Python.
First, we'll open up our Excel workbook and take a look at the three sheets we'll be working with: "One Year," "Other Year," and "Part of Next Year." Then, we'll jump into Python by pressing Control + Alt + Shift + P and extend the formula bar by pressing Control + Shift + U. From there, we'll create a data frame for each sheet by selecting the data and using the control and shift keys to highlight the entire table.
Next, we'll create a list of these data frames and use the pandas function "Concat" to join them together. This function is specifically designed for combining data frames with the same column titles, making it perfect for our situation. After pressing Control + Enter to execute the code, we'll see the combined data frame with all of the rows from each sheet.
But wait, there's a problem. The headings from each sheet have been included in the data frame, which is not what we want. In Excel, we could simply use the "Drop" function to get rid of these headings, but in Python, we have a few different options. In this video, we'll show you how to use the "Tail" function to drop the top row of each data frame, effectively removing the headings. And just like that, we have a clean, combined data frame with no extra rows.
So there you have it, a simple and efficient way to append data frames from multiple worksheets using Python. If you enjoyed this video, be sure to like, subscribe, and ring the bell to be notified of future episodes. And as always, feel free to leave any questions or comments down below. Thanks for watching and we'll see you next time on the MrExcel podcast.
Buy Bill Jelen's latest Excel book: mrexcel.com/products/latest
You can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtube
Table of Contents
(0:00) Problem Statement
(0:29) Defining 3 data frames
(1:32) Python CONCAT function
(2:20) Python Tail Function
(3:10) Wrap-up
Download the workbook: mrexcel.com/youtube/TMyAAxbUU7s
Welcome to episode 2620 of MrExcel's netcast, where we explore the world of Excel and Python. In this episode, we will be diving into the world of 3D scatterplots in Python. In one of our earlier videos, we showed a cool scatter chart and someone asked, "How do we make a graph like that?" Well, that's what we'll be exploring today.
This topic takes me back to my very first programming class in 11th grade, where I was trying to do something cool on a TRS-80 computer. I wanted to draw a circle using the radius times the SIN and COSINE of the degrees, but it didn't work. My teacher then told me to use radians, which I still don't fully understand to this day. So, in this video, we will be using the RADIANS function to convert degrees to radians and create a perfect circle.
To create a fake data set, I decided to draw a bunch of circles using the MOD function to ensure that the degrees stay between zero and 360. Then, I used the SIN and COS of the RADIANs of the degrees to determine the radius and height of each circle. To make it more interesting, I also added a color variable that goes from zero to 18 and slowly expands the radius of each circle. This resulted in a dataset with X, Y, Z, and color values that we will be using to create our 3D scatterplot in Python.
To create the scatterplot, we will be using the documentation from matplotlib, which has worked well for us in the past. However, in this case, it didn't work, so we turned to ChatGPT for help. They provided us with the Python code to create a 3D scatterplot from a DataFrame with columns for X, Y, and Z. We simply had to copy and paste the code, make a few adjustments, and voila! We had our 3D scatterplot with various marker choices, including circles, points, and even pixels.
If you want to try this out for yourself, the link to download the workbook is in the YouTube description. And don't forget to Like, Subscribe, and Ring the Bell to stay updated on our latest netcasts. If you have any questions or comments, feel free to leave them down below. Thank you for watching and we'll see you next time for another netcast from MrExcel.
Buy Bill Jelen's latest Excel book: mrexcel.com/products/latest
You can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtube
How to plot a circle in an Excel XY Chart
Converting degrees to radians in Excel
Building X, Y, Z values for a series of circles in Excel
Using Chat-GPT for Python Code for a 3D Scatter Plot
Adapting the copied code
Marker choices for Python Charts
Table of Contents
(0:00) Problem Statement - 3D Scatterplot in Python
(0:14) Excel formulas to plot a circle in Excel
(0:45) Convert degrees to Radians in Excel
(1:15) Formulas to Create a 3D Spiral in Excel
(2:45) Finding Python code from Chat-GPT
(3:10) Adapting Python code for Excel
(4:45) Making a tiny chart larger with a reference
(5:25) Marker choices in Python
(5:35) Wrap-up
How to do a VLOOKUP or XLOOKUP in Python.
To download the examples in this workbook: mrexcel.com/youtube/h6__fB7vo4k
Welcome to another video on Excel Python XLOOKUP! In this video, we will be exploring how to do an XLOOKUP or a VLOOKUP in Python, and it's remarkably easy. We will start with a simple VLOOKUP and then move on to something more complex, like not specifying the key field. We will also cover what to do when a customer is missing from the lookup table, which is essentially the IFERROR equivalent. Additionally, we will learn how to limit which fields are returned, something that we don't have to worry about with VLOOKUP. We will also address common issues such as mismatched headings and duplicated customers.
Before we dive into the code, let's talk about the comment indicator. The hash symbol allows you to comment a line and explain what the next line is. In this video, I have used this to demonstrate a problem and then provide the solution. This allows me to show the problem and then quickly switch to the solution without having to run the code again. Now, let's get started!
We have a large data set on the left-hand side and a small lookup table on the right. Our goal is to get the sector field from the lookup table and add it to the data set. To do this, we will use the pd.merge function, which is similar to a database join. We will specify the left table, the lookup table, and the column that is in common between them. We can also choose the join type, which is similar to Power Query. In this example, we will use Left join. The result will be our original fields, along with the new field from the lookup table - Sector. And the best part? We don't even have to specify the "on" parameter because the fields have the same name.
But what happens if a customer is missing from the lookup table? We will use the .fillna method to get rid of the #HUM! error and replace it with blanks. Additionally, we will learn how to remove unnecessary fields from the lookup table using the .drop method. And for those of you who are used to Excel's VLOOKUP, we will address the issue of duplicated customers and how to handle them in Python.
But what if we have two keys? In this case, we will use the left_on and right_on parameters to specify the fields that are in common between the two tables. And just like that, we have successfully joined two tables in Python using the pd.merge function. This is a great building block for more complex tasks, and I hope to eventually build a beautiful mansion of code using these building blocks.
Thank you for watching this video on Excel Python XLOOKUP. If you enjoyed it, please don't forget to Like, Subscribe, and Ring the Bell. And as always, feel free to leave any questions or comments down below. See you next time for another netcast with MrExcel!
Buy Bill Jelen's latest Excel book: mrexcel.com/products/latest
You can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtube
Along the way, you will see:
101: Doing a VLOOKUP
Not specifying the key field!
What if a customer is missing from lookup table? How to IFERROR()
Limiting which fields are returned.
What if headings don't match?
What if a customer is duplicated?
Lookup on two fields.
Table of Contents
(0:00) Python lookup overview
(0:25) Comment indicator in Python
(1:00) VLOOKUP 101 in Python using pd.merge
(2:36) Leaving off the On field
(2:50) IFERROR when customer missing with .FillNA
(3:23) Limiting lookup table to needed fields
(4:04) Headings don't match left_on and right_on!
(4:50) .drop method to remove a column from Python
(5:12) If duplicate in lookup table
(6:00) drop_duplicates to remove duplicates
(7:06) What is the right way to show duplicates
(7:29) Lookup on two fields
(8:11) Final thoughts on pd.merge
(8:50) Building blocks in Python
(9:28) Nancy Faust
Download this workbook from: mrexcel.com/youtube/MeCptGsHsYY
Welcome to Episode 2618 of MrExcel's netcast, where we tackle the question of how to display only the last four digits of a Social Security number in Excel. This is a common issue for those of us in the United States, where we all have a Social Security number with three digits, two digits, and four digits. The VA has figured out that the last four digits, combined with your last name, are enough to verify your identity. But what if we only need to see the last four digits in our Excel report, while keeping the full number available just in case? In this episode, we explore two solutions to this problem.
The first solution involves using VBA to create a macro that will obscure the Social Security numbers in the selected column. This macro will replace the original number with a formula that displays only the last four digits, while still retaining the full number in the formula bar. This solution requires VBA and will only work on Windows or Mac versions of Excel. However, it does not require any additional data or changes to the original data set.
The second solution utilizes the data types feature introduced in Excel 2018. By creating a data type for the last four digits of the Social Security number, we can display only those digits in our report while still having access to the full number if needed. This solution does not require VBA and can be used on Windows, Mac, or Excel Online. However, it does require some additional steps in Power Query to split the original data and create the data type.
I understand that these solutions may not be ideal for everyone, and I am always open to hearing about better ways to solve this problem. If you have a different solution or any questions or comments, please leave them in the comments section below. And as always, if you enjoy these videos, please like, subscribe, and ring the bell to be notified of future episodes. Thank you for watching, and I'll see you next time for another netcast from MrExcel.
Buy Bill Jelen's latest Excel book: mrexcel.com/products/latest
You can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtube
Someone from the Veterans Administration is getting data downloaded that includes the entire social security number (SSN). They only want to display the last four of the SSN. But, sometimes, they need to be able to go back and see the entire SSN.
I have two solutions today, but I bet you have something better.
First, it would be nice if Excel offered a custom number formatting code that said "There is a digit here, but we don't want to display it".
My first solution is a pair of VBA macros that embed the original SSN in the N() function in Excel.
The second solution runs the data through Power Query, creating a data type that displays the Last 4 of SSN, but offers a card with the full SSN.
Table of Contents
(0:00) Problem Statement Display last 4 of SSN
(1:08) Using RANDBETWEEN for SSN
(1:35) Excel Custom Number Format Idea to obscure a character
(2:06) Special number format for Social Security Number
(2:25) @@@@ Number Format repeats text four times!
(2:57) VBA Solution
(3:30) Excel N() function for including a comment in a cell
(4:30) Macro to bring back SSN
(5:13) Including quoted text in Excel VBA
(5:35) INSTR function in Excel VBA to find text
(6:15) Testing the Reveal Macro
(6:30) Data Types in Power Query
(9:30) Wrap-up
The problem today: Count how many times a word occurs in a cell in Excel.
To download this workbook: mrexcel.com/youtube/6Ydb6rIls6M
Welcome to another episode of the MrExcel podcast, where we dive into all things Excel. In today's episode, we will be discussing a couple of different titles, including "How many times does this word occur in that long transcript?" and "Excel Labs can already do this better than Python." Our question for today comes from Fred, who wants to know how to count the number of times a word appears in a long transcript of Seinfeld scripts. Can we do this in Excel? Let's find out.
In this video, Bill Jelen, also known as MrExcel, walks us through the process of counting the occurrences of a specific word in a phrase using Excel. He breaks it down into five simple steps and shows us how to apply this formula to a long transcript of over 12,000 characters. But then, he poses the question, can we do this more efficiently using Python? He demonstrates how to use the Python function "count" to achieve the same result in just one line of code. This leads to the idea of creating a custom Python function for this task.
Bill then takes us through the process of creating a custom Python function that can be used in Excel. He explains the code and shows us how to call the function and specify the parameters. He also shares some tips and tricks he learned along the way, such as using two sets of brackets to create a data frame instead of a series. He also shows us how to use the Excel Labs add-in to create a lambda function that can be used in place of the custom Python function.
But why go through all this trouble when we can just use the formulas in Excel? Bill addresses this question and explains the benefits of using Python for certain tasks. He also shares some outtakes that demonstrate why using tables in Excel can cause issues with these formulas. So, if you're looking to improve your Excel skills and learn how to use Python in Excel, this video is a must-watch. Don't forget to like, subscribe, and ring the bell for more Excel tips and tricks. And as always, feel free to leave any questions or comments down below. Thanks for watching!
Buy Bill Jelen's latest Excel book: mrexcel.com/products/latest
You can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtube
The first solution is a series of six formulas in Excel, including SUBSTITUTE, LEN, and more. While it is complicated in Excel, there is a much easier way in Python, using the .Count function. So, Python has a simpler version but how do you call the function from Excel?
After the Python solution, I used the Excel Labs add-in to convert the original six formulas to a LAMBDA.
Other topics here:
Saving Python function in a cell
Adding Text as line line of a Python script to appear in the cell.
Printing to the Python Console
Removing the Index column using two sets of square brackets
In the Out take, the Python Function can not be part of a table.
Table of Contents
(0:00) Python functions in Excel
(0:17) Problem Statement Count ThisWord in Phrase
(0:54) SUBSTITUTE function in Excel
(1:16) LEN function in Excel
(2:29) Python count function
(2:54) Storing Python function in A1
(3:29) Adding Text to Python to appear in cell
(4:15) Calling the Python function using Excel data
(4:45) Call per row or whole frame
(5:10) Printing to Python Console in Excel
(5:35) Does not work next to Ctrl+T tables
(5:56) Removing Index returned by Python in Excel
(6:52) Returning one column? Use double square brackets to prevent Index
(7:39) Writing a LAMBDA using Excel Labs add-in
(8:11) Add Function from Grid
(8:48) Testing the LAMBDA version
(9:33) Wrap-up
(9:59) Like, Subscribe, Ring the Bell
(10:06) Outtake Does not work with Tables
I love pivot tables in Excel. In fact, I've written an entire book on Pivot Tables. So when I saw that Python has a function to generate "Excel-like" Pivot Tables in a new data frame, I wanted to try it out.
Download the workbook from this episode: mrexcel.com/youtube/X_Nyo9OKxKA
The Python Pivot Table is missing a few things:
1. Row Fields are called Index
2. Defaults to Average instead of Sum
3. Empty cells show as errors. Use fill_value
4. No Grand Totals by default! Turn on with margins=True
5. When you add Grand Totals, they are called "All" Unless you change them with Margins_Name
6. When you group by dates, you can't have grand totals
7. Does not sort by Custom Lists
8. Odd arrangement of headings when 2 row fields
But Python pivot tables have some advantages over Excel pivot tables:
Advantages over Excel
1. Automatic Recalc without Refresh
2. Date Grouping Offers some amazing options
In this episode, using Python in Excel to build data frames that look like pivot tables. You will see:
β’ Basic pivot table
β’ Adding Grand Totals
β’ Filling empty cells with zero
β’ Sorting or not sorting
β’ multiple row fields, column fields
β’ multiple value fields
β’ Sum of one field, mean of another
β’ Grouping dates by Month, Week, Quarter, Semi-Monthly, 3 days, 14 days, 2 weeks
β’ Crazy Excel formulas to reformat the top 2 rows of the pivot table
Welcome to episode 2616 of our Python series! Today, we will be diving into the world of pivot tables in Python. As I update my book, "Microsoft Excel Pivot Table Data Crunching," I realized that I needed to add a chapter on Python pivot tables. So, let's get started!
Before we jump into the technical aspects, let's learn a new word for the day - "craw." It refers to the neck or throat of a bird and is often used to describe something that is stuck or bothersome. Now, let's move on to the main topic - pivot tables in Python.
The first thing to note is that the df.pivot_table function creates an Excel-like pivot table in a data frame. However, there are a few differences in the arguments. For example, instead of using "columns" for column fields, we use "index" for row fields. Additionally, the aggregate function defaults to mean instead of sum, and empty cells show as errors unless we use the "fill_value" argument.
One of the most exciting features of pivot tables in Python is the ability to group by dates. However, we need to be careful when using the grouper function as it can cause errors if we have grand totals turned on. Another limitation is that Python cannot sort by custom lists, unlike Excel. But, on the bright side, pivot tables in Python automatically recalculate without the need for a refresh, and the date grouping options are quite impressive.
In this video, we will be using a data set with 563 rows and various columns such as region, product, date, sector, customer, quantity, revenue, cost of goods sold, and profit. Our Python code will be displayed on the screen as we go through different examples of pivot tables. We will cover basic pivot tables, multiple fields, and even mixing calculations for different fields. We will also explore the various grouping options, including some unique ones like semi-monthly and 14-day periods.
While pivot tables in Python have some limitations compared to Excel, they also offer some advantages. For instance, they automatically recalculate, and the date grouping options are more diverse. However, we do miss some features like grand totals and the ability to sort by custom lists. Overall, pivot tables in Python are a powerful tool for data analysis, and I hope this video has helped you understand them better. Thank you for watching, and don't forget to like, subscribe, and leave a comment below. See you in the next episode of our Python series!
Buy Bill Jelen's latest Excel book: mrexcel.com/products/latest
You can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtube
Table of Contents
(0:00) Podcast Words of the Day: Craw
(0:32) Syntax of Python Pivot_Table
(1:06) Contrast Python and Excel Pivot Tables
(1:58) Python better than Excel pivot tables
(2:18) Source data
(2:33) First Python Pivot Table
(2:44) #NUM! errors with Fill_Value
(3:03) Change Average to Sum
(3:30) Why blank 2nd row
(3:49) Adding Grand Totals with Margins=True
(4:35) Sorting a python pivot table in Excel
(5:07) Adding second row field
(5:50) Second column field
(6:23) Adding second Values field
(6:48) Sum Revenue and Average Profit
(7:10) Grouping Dates by Month
(8:20) Group by Weeks & More
(9:44) Blank Row 2
(10:19) Fixing the Python Pivot Table Formatting
(10:57) Can't combine Python formula with Excel formula
(11:21) Drunk kid on Christmas
(12:15) Wrap-up
After using Python in Excel for 2 days, I am gaining confidence. Topics today:
β’ Python Tips from Day 2
β’ Slow rollout of Python
β’ Paying for Anaconda
β’ Python Libraries to explore
β’ How to write Python results back to Excel grid
β’ Real-life K-Means clustering with 18K customers, 30K transactions, 300 products
To download the first workbook: mrexcel.com/youtube/GMF1E0dmjWs
The second workbook contains actual customer data and I am not sharing it at this time.
Table of Contents
(0:00) Welcome
(0:45) Keyboard shortcuts for Python
(1:22) Leila tip DF.Customer eliminates square bracket notation
(2:14) Seaborn Library of Charts as shown by Mynda
(2:49) Initialization of 5 Python libraries automatically
(3:25) Partial Calculation Mode in Excel
(3:48) Use .Mean for AVERAGE()
(4:10) Chances you have Python
(4:40) Eventual cost is Anaconda which adds security
(5:15) Python libraries supported
(5:35) InDesign and Ctrl+Alt+Shift shortcuts
(6:37) Writing Python results back to Excel!
(8:21) Python examples start with fake random data
(8:39) Real data example with Python
(8:52) K-Means clustering based on 100's of products
(9:48) Using Power Query with Python in Excel
(11:20) 3D Scatter Chart in Python
(11:52) Choosing Number of Clusters
(12:13) Reset Python after 30 minutes inactivity
(13:00) Loading data frame back to Excel grid
(13:55) Data-Sciencey
(14:15) Tomorrow: Python pivot tables in Excel
Today, August 22, 2023, Microsoft will release a preview of Python in Excel. It is a big day for me... I've been trying unsuccessfully to learn Python for ten years. Once Microsoft added it to Excel, I finally have some cool things working.
To download this workbook: mrexcel.com/youtube/KIhDQDtvZPg
Welcome to the exciting world of Python in Excel! This is a big day as we explore the new feature that allows us to insert Python code directly into Excel. It's currently in preview mode, but it's already making a huge impact. As someone who has struggled to learn Python in the past, I am thrilled to share this Getting Started guide with you. Trust me, if I can do it, so can you!
I remember buying a book on Python back in 2013, but I just couldn't get the hang of it. The prerequisites and setup were too overwhelming. But now, with the new Insiders Beta, we can easily insert Python code into Excel. In this video, I will be your guide as we dive into the basics of Python. Don't worry, I knew nothing about Python an hour ago, so this is truly a Python 101 lesson.
To get started, simply type =PY into a cell and open the parentheses. You'll notice that the formula bar turns green, indicating that you are now in the Python code editor. Unlike regular formulas, pressing Enter will not accept the code, it will simply move to a new line. To commit the code, you'll need to press Control + Enter. This may take some getting used to, but it's a small price to pay for the power of Python in Excel.
One of the most useful features of Python in Excel is the ability to refer to Excel ranges. Simply type =PY( and use your mouse to select the desired range. You can also choose to return the answer as a value or as a data frame by using the dropdown next to the formula bar or by pressing Ctrl+Alt+Shift+M. This allows for even more flexibility in your code. And don't worry, regular Excel formulas will continue to work as they always have.
Variables are another important aspect of Python in Excel. By creating a variable, you can easily refer to a data frame or range multiple times without having to type it out each time. Just remember to define the variable before using it in your code. And don't forget, the initialization pane has preloaded import statements for commonly used libraries, so you don't have to worry about importing them yourself.
In this video, we also explore the exciting world of data visualization with Python. We use the popular K means clustering algorithm to group customers based on their data points. This is something that was previously difficult to do in Excel, but with Python, it's a breeze. And don't worry, even if you're not familiar with Python, you can easily copy and paste code from the internet and it will work seamlessly in Excel.
I am truly excited about the possibilities that Python in Excel brings. It's currently only available in the Insider's Beta, so make sure to sign up and give it a try. And while there may be a fee for writing Python code in the future, for now, it's completely free. So why not give it a try and see how it can enhance your Excel experience? Thank you for watching and don't forget to like, subscribe, and ring the bell for more Excel tips and tricks. See you next time!
Buy Bill Jelen's latest Excel book: mrexcel.com/products/latest
You can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtube
In this video: a getting started with Python in Excel tutorial.
How to open the Python editor in Excel
Ctrl+Enter versus Enter in the Python Editor in Excel
Returning a Value or a Python Object
Referring to an Excel range in Python
Using Variables in Python in Excel
Plotting Data Using Python in Excel
Which Python Libraries are loaded by default?
K-Means Clustering for customer segmentation
Table of Contents:
(0:00) Python in Excel is in preview
(0:29) Python Excel 101
(0:53) =PY( to Open Python Editor
(1:05) Enter is New Line. Ctrl+Enter commits the code.
(1:29) Return as Value or Object with Ctrl+Alt+Shift+M
(2:08) Loading Excel range to Python
(2:33) Show Card of Data Frame
(2:47) Referring to Data Frame
(3:19) Changing Data Frame to Excel Value
(3:37) Using Variables in Excel Python
(4:05) Cell order for variable use
(4:59) Plotting data in Excel with Python
(5:30) Python libraries always loaded
(5:51) K-Means Clustering in Excel Python
(8:15) Recap of Important Excel Python
(9:48) Cost for Python in Excel
(10:42) What are your thoughts?
In August 2023, Microsoft is adding a new feature to Microsoft 365 Excel. The Format Stale Values will alert you when a cell needs to be recalculated. This is an application-level setting. When you turn it on, it will work for all open workbooks.
To download this workbook: mrexcel.com/youtube/LPyYLqq72wI
Welcome to another episode of MrExcel's netcast! Today, we're diving into a new feature called Stale Value Formatting in Excel. This feature just arrived in my Insider's Beta and I'm excited to share it with you. It's currently being rolled out slowly, but I have it here in my spreadsheet and I can't wait to hear your thoughts on it.
So, what exactly is Stale Value Formatting? Well, imagine you have a large spreadsheet with multiple input cells and formulas. Now, if you're working in Manual Calculation Mode and you make a change to one of the input cells, the formulas won't automatically recalculate. This is where Stale Value Formatting comes in. It will remind you that some values have not been recalculated by marking them with a strikethrough. This way, you can easily identify which values may need to be updated.
To enable this feature, simply go to Calculation Options and toggle on Format Stale Values. Now, when you make a change to an input cell, the corresponding formulas will be marked with a strikethrough. You can then choose to either calculate now or switch to automatic calculation. And don't worry, if you don't like the strikethrough formatting, you can always turn it off. However, keep in mind that this is a version one feature and Microsoft may add more customization options in the future.
But here's where things get a little confusing. I noticed that even when the values are correct, they still get marked as stale. For example, when I use the auto sum function, the resulting value is correct, but it still gets marked as stale. I'm not sure if this is a bug or by design, so I'll have to check with the Excel team for clarification. However, I do appreciate the fact that this feature can also be used in automatic calculation mode, as it can be helpful in preventing calculation errors.
I want to give a big thank you to Joe and the entire Excel team for introducing this useful feature. And as always, I want to thank you for tuning in to MrExcel's netcast. If you have any questions or comments, please leave them down below in the YouTube comments section. And if you enjoy these videos, don't forget to like, subscribe, and ring the bell for notifications. See you next time!
Buy Bill Jelen's latest Excel book: mrexcel.com/products/latest
You can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtube
More details on the feature: techcommunity.microsoft.com/t5/excel-blog/stale-value-formatting/ba-p/3887098
Table of Contents
(0:00) Excel Stale Value Formatting
(0:23) Excel Manual Calculation Mode
(0:36) Stale Value Markers
(0:56) On-Grid UI choices
(1:09) Customize Stale Value Formatting?
(1:47) Why are new values stale?
(3:14) Stale Value in Automatic Mode
(3:40) Excel interrupt calculation with Esc
(4:25) What's your opinion?
Do you deal with Bills of Material? Also known as BOMs. Any Structured List in Excel can make use of this add-in from Main Sheet Solutions:
Download a trial from mainsheetsolutions.com
Download the two data sets that I used in this video: mrexcel.com/youtube/k4A1lg9pPlc
Welcome to episode 2612 of the MrExcel netcast! In today's video, we're going to be talking about an amazing Excel add-in that is designed to streamline Bill of Material (BOM) analysis. This utility from Main Sheet Solutions is not just limited to BOMs, but can be used for any structured list.
During my 12 years of working at a company, I didn't personally deal with BOMs, but I had two colleagues, Bob and Patty, who dealt with them on a daily basis. When Dan, the creator of this utility, sent it to me, I immediately thought of Bob and Patty and wondered if this would be useful to them. And let me tell you, Bob was one of the most interesting people I've ever worked with. He was a Texas A&M Aggie, a former military guy, and a true Texan. He was always super nice to me, but if he noticed something that was BS, he would pull out the most colorful Texas vocabulary. Even now, 23 years later, I still use some of the terms he taught me. Sadly, I recently learned that Bob has passed away, but I know he would have loved this BOM Tool Suite from Main Sheet Solutions.
Now, even though I don't deal with BOMs on a regular basis, I know enough about them to be dangerous. And let me tell you, this set of tools is impressive. With 20 time-saving functions and over 250 use cases for BOMs, it's a must-have for anyone dealing with BOMs in engineering or down in the manufacturing plant. But even if you're not directly involved with BOMs, there are still plenty of use cases for this add-in.
BOMs are typically created in ERP, CAD, or PDM systems, but they are often exported to Excel for further analysis. This is where the BOM Tool Suite comes in. With just a click of a button, you can group rows, highlight data, indent rows, and even use roll-up equations to quickly calculate totals. And the best part? You can customize the colors and preferences to your liking, making it even easier to use.
But the BOM Tool Suite isn't just limited to BOM analysis. In this video, I'll also show you how I used it to analyze the table of contents for my book, "Microsoft 365 Excel: The Only App That Matters". With just a few clicks, I was able to group rows, indent data, and even use roll-up equations to calculate the number of pages per chapter. It's truly a versatile tool that can be used for a variety of tasks.
So if you or someone you know deals with BOMs, I highly recommend checking out the BOM Tool Suite from Main Sheet Solutions. It's a game-changer and will make your life a lot easier. And don't forget to like, subscribe, and ring the bell to stay updated on all our latest videos. Thank you for watching and we'll see you next time for another netcast from MrExcel.
Buy Bill Jelen's latest Excel book: mrexcel.com/products/latest
You can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtube
Table of Contents
(0:00) Bills of Material in Excel
(2:02) Data in a typical BOM
(2:21) Ribbon for BOM
(2:29) Adding Grouping
(2:46) Choosing highlight colors
(3:17) Highlight rows
(3:27) Indent BOM
(3:36) BOM Rollup Calculations
(4:30) Analyzing Mike Girvin Book Table of Contents
(6:03) Adding Structure ID and UID
(6:44) Applying colors to group levels
(7:06) Check out the add-in
(7:31) Wrap-up
A great question this morning: Is there a bug in double-click the fill handle to copy a formula? Someone was convinced that Excel was being fooled by the formatting.
The behavior of copying a formula by double-clicking the fill handle changed in Excel 2010. Excel now looks at all columns to the left and the right when figuring out how far to copy down. Imagine if Excel does Ctrl+* to select the current region. Excel will copy down to the last row in the current region.
There are two exceptions:
a) If the cell immediately below the formula is non-blank, then Excel will only copy down to the cell above the blank cell in that column
b) If the cell below the formula is blank, but any other cell in that column is non-blank, Excel will stop copying to prevent overwriting any cells in the current column.
To download this workbook: mrexcel.com/youtube/MR_BDx3Lx9w
Welcome to episode 2611 of the MrExcel NetCast, where we dive deep into the Excel fill handle and its ability to copy formulas. In this video, we will explore the various rules and exceptions that come into play when using the fill handle, and how to make the most out of this powerful tool.
Have you ever encountered a situation where the fill handle seemed to be using formatting from cells below, rather than copying the desired formula? This is a common issue that many Excel users face, and in this video, we will address this problem and provide a solution. We will also discuss the improvements made to the fill handle in Excel 2010, and how it now takes into account all columns to the left, making it more efficient and accurate.
But what about blank cells? Can they affect the functioning of the fill handle? The answer is yes, and in this video, we will explain how a blank cell in the column to the left can cause the fill handle to stop at a different row than expected. We will also demonstrate how the fill handle works with diagonal connections and how it can be overridden by non-blank cells in certain situations.
One important rule to keep in mind is that the cell immediately below the selected cell is the first thing Excel looks at when using the fill handle. If this cell is non-blank, all other rules and exceptions are disregarded. We will also discuss another way to override the rules, by inserting a non-blank cell in the column to the right, which will cause the fill handle to stop at a different row.
In conclusion, the fill handle is a powerful tool in Excel, but it is important to understand its rules and exceptions in order to use it effectively. We hope this video has provided you with a deeper understanding of how the fill handle works and how to troubleshoot any issues that may arise. Don't forget to like, subscribe, and ring the bell for more helpful Excel tips and tricks. And as always, feel free to leave any questions or comments down below. Thank you for watching and we'll see you in the next episode of the MrExcel NetCast.
Buy Bill Jelen's latest Excel book: mrexcel.com/products/latest
You can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtube
Table of Contents
(0:00) Problem Statement: Is Fill Handle broken?
(0:12) Does formatting fool the fill handle in Excel?
(0:37) Fill Handle Blank Cells in Excel 2007 & Earlier
(1:07) Excel 2010 Improvements with Double-Click Fill Handle
(1:29) It is like Current Region
(1:50) Excel 2010 looks to right as well
(2:08) Diagonal connections work too
(2:33) Exception if cell below formula is non-blank
(3:23) Do not overwrite if cell below formula is blank
(3:50) Solution to original problem
(4:18) Make use of second exception
(4:45) Wrap-up
The second ever Excel Battle to air on ESPN will happen on Friday August 4, 2023 at 7 AM Eastern time. This short is a bit of sketch comedy ... wouldn't it be great if Excel on ESPN were so commonplace that bookies would start to allow betting on the competition? That's when you know that you've made it to the big time.
This fictitious sketch imagines a day far in the future when you can just call up a bookie and place some action on Diarmuid Early or your favorite Excel competitor.
For 2023, set your alarm and tune in to ESPN on Friday August 4, 2023 at 7AM Eastern for the Excel Knockout Battle!
Table of Contents
(0:00) Excel Battle Returning to ESPN
(0:04) Julie calls her bookie
(0:14) Taking bets on Excel on ESPN?
(0:22) Excel on ESPN Friday August 4 2023 at 8AM
(0:37) $3K bet on Dim Early to win
(0:43) She broke her leg
(0:52) Say hi to Diane
Don't forget to tune into the second ever Microsoft Excel competition to air on ESPN.
Friday, August 4, 2023 at 7AM Eastern.
That is Noon in the UK/Ireland
1 PM Amsterdam / Berlin / Paris
4:30 PM New Delhi
7 PM Hong Kong
9 PM Melbourne
Table of Contents
(0:00) Fred Introduction
(0:15) Shout out to ESPN Excel Contestants
(0:24) 7 AM At least the sun is out
(0:35) Got something to do
Mark your calendars for 7 AM (ET), August 4, 2023, as we gear up for an adrenaline-pumping Excel Esports Elimination Battle - a completely new Excel Esports game format will be revealed!
8 Excel enthusiasts will be competing in a fierce 30-minute battle, but every 5 minutes one of them will be eliminated: Andrew Ngai, Isaac Lee, Diarmuid Early, David Brown, Alana Reid, Brittany Deaton, Michael Holmes, and Emilie Williams.
Hosted by Excel rock stars Leila Gharani, Bill Jelen, and Oz du Soleil!
From precision plays to amazing strategies, the Excel world's finest content is being brought to your screens.
Whether you're a die-hard Excel Esports fan or a newcomer, this event is a canβt-miss!
Tune in and join the excitement as Excel Esports sets the stage ablaze on βESPN8: The Ochoβ: espnpressroom.com/us/press-releases/2023/07/mas-ocho-espns-biggest-boldest-edition-of-espn8-the-ocho-returns-with-43-straight-hours-of-seldom-seen-sports-august-3-5
Table of Contents
(0:00) Bill Jelen Great to Watch
(0:04) Oz du Soleil Looking Forward
(0:08) Leila Gharani is super nervous
(0:16) Be careful!
(0:19) David Brown is disappointed
(0:23) Dim Early: Andrew Ngai is a machine
(0:46) Leila Gharani major workout
(0:56) Wrap-up
Lionel Messi's debut in the US for Miami is in the Leagues Cup. This four-week tournament includes 47 clubs from across the USA, Canada, and Mexico. What are the odds of a tiebreaker in group play for the Leagues Cup? If Miami finishes first or second in their group, who will they play in the knockout stage?
To download this workbook: mrexcel.com/youtube/M6xbdRFASnI
Welcome to another episode of Excel with Bill Jelen! In this video, we will be discussing the upcoming Leagues Cup tie and the probability of seeing the legendary Lionel Messi play in Orlando this August. As always, we will be using Excel to analyze the data and make predictions.
The Leagues Cup is a relatively new soccer tournament that features teams from Major League Soccer (MLS) and Liga MX, the top professional leagues in the United States and Mexico, respectively. In this episode, we will be looking at the upcoming match between Orlando City SC and Tigres UANL, two powerhouse teams in their respective leagues. Using Excel, we will analyze their past performances and current standings to determine the probability of each team winning the tie.
But the real question on everyone's mind is whether or not we will see Lionel Messi, one of the greatest soccer players of all time, play in Orlando this August. Rumors have been circulating that Messi may join Inter Miami CF, a new MLS team, and potentially play in the Leagues Cup. We will use Excel to analyze the likelihood of this happening and what it could mean for the tournament and the sport as a whole.
So join me as we dive into the world of soccer and Excel to predict the outcome of the Leagues Cup tie and the possibility of seeing Messi on the field in Orlando. Don't forget to like and subscribe to our channel for more Excel tips and sports analysis. Let's get started!
Buy Bill Jelen's latest Excel book: mrexcel.com/products/latest
You can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtube
Table of Contents
(0:00) Problem Statement: Odds of tiebreaker
(0:21) Orlando City Soccer two home matches in Leagues Cup
(0:48) Lionel Messi's US debut will be in Leagues Cup
(1:00) Leagues Cup Overview
(1:26) Orlando City in South Group 2
(1:40) 2 Automatic Berths in knockout round
(2:00) Who would play Messi in Knockout round?
(2:22) Scenarios in Group Play
(2:57) Group Play Draw
(3:15) 64 Scenarios
(3:30) SEQUENCE in Excel
(3:45) BASE function in Excel
(4:15) SWITCH function in Excel
(4:37) Ranking with LARGE function in Excel
(4:50) CONCAT function in Excel
(5:00) Checking for Ties
(5:16) 25% chance of a tie in Leagues Cup Group Play
(6:22) Tiebreakers in Leagues Cup Group Play
(7:15) Six possible teams to play Messi in knockout round
(7:40) Wrap-up
How to create a live link from an Excel workbook to a Word document.
This video is to accompany the article in the July 2023 issue of Strategic Finance Magazine: sfmagazine.com/articles/2023/july/excel-embedding-a-range-in-a-word-document
In this video, we will learn how to embed an Excel range into a Word document and create a live link between the two. This feature is especially useful when working with long Word documents that require a table, which would be better suited as an Excel table. By embedding the Excel range, any updates made to the data in Excel will automatically reflect in the Word document.
To begin, we need to select the entire range that we want to embed in Word. This includes any data outside the range, as well as any filters or V stacks within the range. Next, we need to name the range by clicking on the name box and typing in a name without any spaces. This name needs to be saved in the Excel workbook and accessible to the Word document.
Once the range is named and saved, we can copy it and paste it into the Word document. However, we need to make sure to select the option "Link and keep source formatting" to establish the live link between the two documents. This allows for any changes made in Excel to be immediately reflected in Word. However, it is important to note that this link can easily be broken if the Excel file is not saved or if the Word document is closed and reopened.
To fix this, we can use the F9 key to recalculate the link in Word. However, this may only work once. To ensure that the live link remains intact, we can save and close the Word document and then reopen it, selecting "yes" when prompted to update the links. This will bring us back to the perfect state where any changes made in Excel will be instantly reflected in Word.
This feature is extremely useful when creating annual or quarterly reports, or when working with large amounts of data in Excel. It allows for a seamless integration between the two programs and saves time and effort in manually updating the data in both documents. So the next time you need to embed an Excel range in a Word document, remember these simple steps to create a live link and make your work more efficient. Thank you for watching!
Buy Bill Jelen's latest Excel book: mrexcel.com/products/latest
You can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtube
Table of Contents
(0:00) Problem Statement: Excel Table in Word Document as Live Link
(0:22) Name the range to be embedded
(1:32) Save as East, Change to West
(1:45) Copy from Excel & Paste as Link in Word
(2:20) Live link between Excel and Word
(3:01) After you close Excel workbook
(3:35) Re-opening Excel
(4:10) Re-calc Word with F9
(4:40) Re-create the immediate link between Excel and Word
Buy Bill Jelen's latest Excel book: mrexcel.com/products/latest
The new Picture in Cell feature would not work with the Filter drop-downs. A new feature allows you to add text to each picture using the Alt Text panel. This Alt Text will appear in the Filter drop-downs, in XLOOKUP results, in the Pivot Table Filter, and in Pivot Table Slicers.
To download this workbook: mrexcel.com/youtube/xZxKhm9_Xkk
Welcome to another exciting video on Excel improvements! In this video, we will be discussing the latest updates to the picture in cell feature in Excel. These changes have made it even more convenient and user-friendly to work with images in your spreadsheets.
One of the major improvements is the ability to change the formula property using the Alt Text panel. This not only appears in the formula bar, but also in the filter dropdown and as the result of an Xlookup. This makes it easier to manage and filter through your data.
Another great enhancement is the improved UI for converting pictures over cells to picture in cells. This feature was recently added to Insiders Fast and is now available for all users. Under the Insert Pictures option, you can now choose to place the image over cells or in cells from your device. However, when multiple pictures are selected, they are added in a column which can make it difficult to differentiate between them.
But don't worry, the latest update has solved this issue. By simply right-clicking on the image and selecting "view Alt Text", a panel will appear where you can add Alt Text for each image. This Alt Text will then appear in the filter dropdown, making it easier to filter through your data. It also shows up in the formula bar, making it more visible and accessible.
To demonstrate the usefulness of this feature, we have a simple table with images and Alt Text. We can use an Xlookup to search for a specific word and return the corresponding image. And with the Alt Text, we can filter through the images based on their descriptions. This is extremely helpful when working with large amounts of data.
But that's not all, the Alt Text also works in pivot tables and slicers. You can now filter through images in the pivot table by their Alt Text, making it easier to analyze and organize your data. And with the ability to add images directly into the pivot table, the possibilities are endless.
We want to thank the Excel team for these amazing improvements to the picture in cell feature. They have also fixed some issues that were previously documented, making this feature even more efficient. So don't wait any longer, try out these new updates and let us know what you think in the comments below. And if you enjoy our videos, don't forget to like, subscribe, and ring the bell for more Excel tips and tricks. Thank you for watching and we'll see you in the next video!
Buy Bill Jelen's latest Excel book: mrexcel.com/products/latest
You can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtube
Table of Contents
(0:00) Problem Statement: Filtering by Picture
(0:53) Use Alt Text to add Text to Picture in cell
(1:17) Alt Text appears in Filter drop-down
(1:44) Getting Picture from XLOOKUP
(2:15) Use Image in Excel Pivot Table Filter
(3:23) Image Alt Text in Pivot Table Slicer
(3:40) New Right-click options for Image in Cell
(5:02) Wrap up
The Association for Professionals in Infection Control and Epidemiology is holding their annual conference in Orlando June 26-28, 2023. I will be presenting a half-day pre-conference session about using Excel for Infection Prevention on Sunday June 25, 2023 from 8 AM to 1 PM.
My goal in the session is to make you more comfortable with Microsoft Excel. In this preview, I download data from the CDC, do some simple clean-up. I use a few Excel formulas to add some useful metrics. I then display the results on a Map in Microsoft Excel.
Everyone who attends my session receives a copy of my book, MrExcel 2021 - Unmasking Excel. If you missed the session, buy my latest Excel book: mrexcel.com/products/latest
"Hello APIC! Are you ready for your annual conference in Orlando this year? Well, before the conference officially kicks off, I'll be hosting a pre-conference session on Excel for the Infection Preventionist. My name is Bill Jelen, and I've been entertaining and informing conference audiences for 17 years through my website, MrExcel. I am thrilled to share my Excel tips and tricks with you, so get ready to take your data analysis skills to the next level.
In this session, I'll be focusing on cleaning up data, summarizing and graphing data, and finding trends using Microsoft Excel. We all know there is a wealth of data out there, but it can be overwhelming and messy. My goal is to make you more comfortable with Excel and show you how to make the most of this powerful tool. By the end of the session, you'll be able to confidently use Excel to analyze data and create visualizations like the one I'm about to show you.
During the session, we'll be using real data from the CDC's FluView Interactive tool. We'll be looking at state-level data and using some simple formulas to create a new metric for comparing flu rates across different states. And the best part? We'll be visualizing this data on a map using Excel's 3D mapping tools. But don't worry, I'll also show you how to apply these techniques to your own facility's data for a more localized analysis.
But wait, there's more! Everyone who attends the session will receive a copy of my Excel book, as well as breakfast and lunch. And don't worry, we've increased the room size to accommodate more attendees, but there are still a few spots left. So if you haven't signed up yet, there's still time to do so. Trust me, you don't want to miss out on this opportunity to level up your Excel skills.
Now, I have a question for you. Have you ever encountered anomalies in your data, like the one we see in the flu rates during the pandemic? As a data guy, I'm always looking for ways to handle these anomalies and make accurate projections for the future. So if you have any insights or strategies for dealing with these anomalies, please leave a comment down below. I'd love to hear your thoughts and learn from your expertise.
So, if you're coming to Orlando for the APIC conference, make sure to check out my pre-conference session on Excel for the Infection Preventionist. And if you're already signed up, I can't wait to see you there! Let's have some fun and take our Excel skills to the next level. And if you enjoy my videos, don't forget to like, subscribe, and ring the bell for notifications. See you in Orlando!"
Buy Bill Jelen's latest Excel book: mrexcel.com/products/latest
You can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtube
Table of Contents
(0:00) APIC in Orlando June 2023
(0:30) Session description
(1:15) Download infection data from CDC to Excel
(1:37) CDC uses data with Week Number
(1:55) Excel formula for Total Flu
(2:00) Population by State
(2:15) Flu per Million
(3:00) 3D Map Feature in Excel
(4:00) Animating the map over time in Excel
(4:57) Invite to session
(5:25) Flu seasonality data anomoly during CoVid-19
(6:35) Wrap-up
In episode 2606, I showed the new Insert Pictures in Cells and lamented that they did not change the IMAGE function to allow images stored on the local hard drive. Using the VBA Macro in this workbook, you can do it. The VBA is actually better because it remembers the image path and displays it in the formula bar.
To download the workbook or copy the VBA: mrexcel.com/youtube/0NArag0pV7I
Welcome to another episode of MrExcel! In this video, we're going to tackle a frustrating feature in Excel - placing local pictures in a cell using a formula. Microsoft has implemented this feature, but it's not quite what we were hoping for. But fear not, because I have a VBA hack that will allow us to use a formula to insert an image from our local hard drive into a cell. Let's dive in!
In a previous video, I used Power Query to pull images from a folder into Excel. However, when I tried to insert these pictures into cells using the "From this device" option, I ran into some issues. The order of the pictures didn't match the order in which they were pulled in by Power Query, and the formula bar only showed the word "picture" for each image. I also discovered that using a VLOOKUP or XLOOKUP table with the pictures worked, but it wasn't a perfect solution.
So, I set out to find a way to use a formula to insert images from our local hard drive into cells. And I did just that with the help of some VBA code. I've provided the code for you to use in your own personal macro workbook, so be sure to check out the link in the top right corner of the video. With this code, we can now use the "Insert picture in cell" method to insert images from our local hard drive into cells. And the best part? The formula bar now shows the full path and file name of the image, making it easier to search for specific images.
But wait, there's more! I also have a second macro that allows you to add the image to the right of the URL, giving you even more flexibility. And while these macros may not be as cool as the image function, they get us pretty close to where we want to be. Plus, the inserted images are considered a rich data type, so you can use the "Control + Shift + F5" shortcut to view a larger version of the image in a card format.
Now, you may be wondering where this new feature from Microsoft will end up. Currently, it's only available in the insider's beta and there are still some kinks to work out. But I have high hopes that it will eventually become a fully polished feature. In the meantime, I want to thank Microsoft for giving us this functionality and for allowing us to use VBA to enhance it. And as always, thank you for watching and be sure to like, subscribe, and ring the bell for more helpful Excel tips and tricks. Don't forget to leave any questions or comments down below. See you next time on MrExcel!
Buy Bill Jelen's latest Excel book: mrexcel.com/products/latest
You can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtube
Table of Contents
(0:00) Enhancing Picture In Cells to use a formula
(0:30) Behind Scenes of 2606
(1:00) Sort Order is different
(1:33) Formula Bar shows only "Picture"
(1:40) Using XLOOKUP with Pictures In Cell
(2:08) Filtering Excel Picture in Cell
(2:33) Excel smart lookup on Picture in Cell
(2:56) Formula to point to picture
(3:57) IMAGE() function fails with local image
(4:05) Using VBA to solve problem
(4:51) VBA adds image path to formula bar!
(5:13) Filtering Pictures added by VBA works in Excel
(5:39) Add Image to Right
(6:28) Image is Rich Data Type - Show Card
(6:50) In VBA, use Selection.Value to get image path
(7:10) Inaccuracies in Help Topic
(7:43) Premature release?
(7:57) Wrap-up
Can the Excel IMAGE function use a local hard drive? Not yet.
New Excel VBA method: InsertPictureInCell
Insert Many Pictures in Excel in one command.
Feature Requests:
Expose a VBA property to show original source of the image
Allow IMAGE function to point to local hard drive.
Allow Power Query to load in-cell images.
To download this workbook: mrexcel.com/youtube/mFd8HsHmfpA
Welcome to episode 2606 of MrExcel's YouTube channel! In this video, we will be discussing a new feature in Excel called "Pictures Place in Cell From This Device". This feature allows us to insert pictures directly into a cell, rather than having them float on a drawing layer above the grid.
Many of us have been waiting for a way to insert pictures from our local hard drive, and while this feature does not currently support that, it does offer some exciting new possibilities. For example, we can now insert multiple pictures at once using VBA, and the pictures will automatically align with the cell's alignment.
In this video, we will explore the various ways to use this new feature, including inserting pictures from a folder, using VBA to insert multiple pictures, and even using Power Query to insert images. We will also discuss some potential limitations, such as the fact that the pictures are saved at full size, which can result in large file sizes.
While this feature is a step in the right direction, I can't help but think back to a phone call I received from my friend Jerry in 2002. He asked me to import a sales report into Excel and display the top-selling items with their corresponding pictures. At the time, I thought Excel was for numbers, not pictures. However, Jerry showed me how to use VBA to insert pictures, and it opened up a whole new world of possibilities for me.
I was excited when the image function was introduced, but it was limited to images from a website. Now, with "Pictures Place in Cell From This Device", we have even more options, but I can't help but hope that in the future, we will be able to use a formula to point to a specific image on our local hard drive. This would be incredibly useful for companies with a shared file system, as everyone would have access to the same images.
In this video, we will also discuss some potential workarounds for this limitation, such as using VBA to retrieve the path and file name of the image. We will also explore the possibility of using Power Query to insert images directly into Excel. While these may not be perfect solutions, they offer some potential for those of us who have been waiting for a way to insert local images into Excel.
Thank you for watching this video on "Pictures Place in Cell From This Device". If you enjoyed it, please like, subscribe, and ring the bell to be notified of future videos. And as always, feel free to leave any questions or comments down below. We appreciate your support and look forward to bringing you more helpful Excel tips and tricks in the future.
Buy Bill Jelen's latest Excel book: mrexcel.com/products/latest
You can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtube
Table of Contents
(0:00) New Picture In Cell feature
(0:33) Pictures on drawing layer
(0:47) Inserting image in cell in Excel
(1:05) Fitting in Cell
(1:20) Alignment
(1:35) No apparent way to rotate image
(2:02) Use Case from Jerry
(3:48) Using VBA Macro Recorder
(4:11) Inserting 400 images
(4:36) VBA InsertPictureInCell
(4:53) Is there a VBA Property?
(5:07) Sorting images with cells
(5:39) Using a formula to refer to image
(6:00) File Size concerns
(6:30) Really need IMAGE formula for local drive
(7:04) Feature Request: Power Query support for images
(7:25) Wrap-up
Table of Contents
(0:00) Excel VBA Macro Record in Relative or not?
(0:29) Add Relative Reference button to QAT in Excel
(0:40) Visual indicator if relative recording is on or off in Excel
(0:51) Wrap-up and Subscribe
Some people call it Strikethrough. Others call it crossing out text.
How can you do it in Microsoft Excel.
This short video starts with a memory aid about taking inventory with a mechanical pencil and making hashmarks. You might start with |||| to mean 4. After the 4th vertical hashmark, you cross out the lines to indicate 5. It is the 5th line that crosses the others out. This is how I remember that Ctrl+5 is Strikethrough!
Download the workbook from mrexcel.com/youtube/PmQpp46p3ls
Table of Contents
(0:00) Counting with Hashmarks
(0:12) Cross out text with Ctrl+5 in Excel
(0:22) Strike through one word in a cell in Excel
(0:33) Wrap-up
Common Search Terms? This video answers these search terms:
how do draw a strikethrough in excel
how do i draw a line through text in excel
how to draw line text in excel
how to draw line through text excel
how to draw line through text on excel
how to draw line through word in excel
How to draw an arrow in Excel?
To download this workbook: mrexcel.com/youtube/ojM42vamBQY
Table of Contents
(0:00) Draw arrow in Excel
(0:08) Change Arrow Color, Weight, Arrow Heads
(0:21) Make line dotted in Excel
(0:24) Curve arrow around an obstacle in Excel
(0:51) Wrap up
Common Search Terms:
how do i draw arrows in excel
how do you draw arrows in excel
how to draw a arrow in excel
how to draw an arrow in excel
how to draw an arrow in excel 2010
how to draw an arrow line in excel
how to draw arrow in excel
how to draw arrow in excel sheet
how to draw arrows in excel
how to draw arrows on excel
how to draw red arrow in excel
how to draw straight arrow in excel
where do i find abilitty to draw arrows in excel
how to draw dotted line in excel
how to draw dotted line in excel chart
how to draw a line in excel cell
how to draw a straight line in excel
how to draw line in excel sheet
how to draw lines in excel sheet
how to draw lines in microsoft excel
how to draw straight lines in excel
how do i draw a straight line in excel
how do you draw a straight line in excel
how do you draw lines in excel
Keani has a pivot table with current year and last year across the top. She wants a variance. Her current solution is a formula outside of the pivot table which points inside of the pivot table.
This video shows three methods for adding a variance inside the pivot table.
1. Regular pivot table, add Revenue twice. Change calculation to Difference From, Years, and (Previous Item)
2. Regular pivot table. Remove Grand Total. Use 2 Excel Calculated Items to calculate Variance and Total
3. Data Model Pivot Table. Add four DAX Measures to calculate last year, this year, total, and variance.
To download this workbook: mrexcel.com/youtube/T-yRp9N59pw
Welcome to episode 2605 of MrExcel's YouTube channel! In today's video, we will be discussing how to put a year-over-year variance inside the pivot table. This question comes from Keani, who attended one of my seminars at UCF a couple of weeks ago. She wants to know how to add the variance inside the pivot table instead of adding a formula to the right of the table. In this video, we will explore three different methods to achieve this, including using show values as difference from the previous item, adding a year to the source data, and using a data model pivot table with DAX measures.
The first method we will look at is using the show values as difference from the previous item option. This method involves adding the revenue field a second time and choosing the difference from the previous period. However, this method requires hiding columns and does not show the variance for the first year. The advantage of this method is that it is part of the pivot table and can be easily adjusted by changing the fields.
The second method involves adding a year to the source data and using a calculated item in the pivot table. This method requires updating the formula every year and removing the grand total to get the correct result. It is not the most efficient method, but it gets the job done.
The third and most efficient method is using a data model pivot table with DAX measures. This method involves adding a year to the data and creating calculated fields for sales, sales in 2020, sales in 2021, and the variance. This method allows for easy updates and provides accurate results. It is also the only method that shows the grand total correctly.
I want to thank Keani for her question and for attending my seminar at UCF. I hope this video was helpful in understanding how to add a year-over-year variance inside the pivot table. If you enjoyed this video, please like, subscribe, and ring the bell to be notified of future episodes. Don't forget to leave any questions or comments in the comment section below. Thank you for watching and see you next time for another netcast from MrExcel!
Buy Bill Jelen's latest Excel book: mrexcel.com/products/latest
You can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtube
Table of Contents
(0:00) Problem Statement Variance in Excel pivot table
(0:37) Show Values as Difference From
(1:52) Calculated Item in Regular Pivot Table
(3:20) DAX Measures in Data Model Pivot Table
(6:04) Wrap up
In episode 2602, I had to generate all possible combinations of 4 games with 6 possible outcomes in each game. I used my very convoluted "binary count up, but not binary because it is base 6, but not base 6 because I need the digits 1 to 6 instead of 0 to 5" method. This involves a lot of typing and two different formulas.
Today, a much easier way from Kyle Freistedt. Kyle DOES use Base 6. He does use a SEQUENCE function but starts at 0 instead of 1. And then at the end, he uses +1111 to convert the 0 to 5 to 1 to 6.
In this video, I show Kyle's method for the Jeopardy Masters problem and then I generalize the steps for any values of N and K.
To download this workbook: mrexcel.com/youtube/wLs8U2RMi7I
Welcome back to another episode of MrExcel NetCast! In this video, we will be discussing how to generate all combinations from one to six in four columns. This is a common task that many Excel users encounter, and I'm excited to share with you a much easier and more efficient method than the one I previously used.
A few days ago, I was solving a problem involving the odds of a tie in the "Jeopardy Masters" game. To do this, I needed to generate all combinations from one to six in four different columns. However, my previous method involved a complicated formula using binary and base 6, which I'm not particularly proud of. But thanks to Kyle Freistedt, I have now learned a much simpler and more effective way to do this task.
The first step is to create a list of numbers from zero to 1,295. Then, using the BASE function, we can convert these numbers to base six, with a minimum length of four digits. However, this will give us a list of numbers starting with zero, so we need to add 1111 to each number to get the desired result. This may seem like a strange solution, but trust me, it works perfectly.
Now, instead of having all the combinations in one column, we can use some tricks to break them out into four columns. This is where Kyle's genius formula comes in. By using the TEXTSPLIT function, we can split the numbers at the dashes and then use the MID function to extract the individual digits. This may seem a bit complicated, but once you see it in action, you'll understand how powerful and efficient it is.
I have to admit, I had seen this formula from Kyle before when I was working on the World Cup, but I couldn't remember it when I needed it again. So, I'm making this video as a reminder for myself and for all of you who may need it in the future. I highly recommend bookmarking this video so you can easily refer back to it whenever you need to generate all combinations from one to six in four columns.
I want to give a huge thank you to Kyle for sharing this formula with me not once, but twice. And thank you to all of you for watching and supporting the MrExcel NetCast. If you enjoyed this video, please don't forget to Like, Subscribe, and Ring the Bell to be notified of future episodes. And as always, feel free to leave any questions or comments down below. Thanks for watching!
Buy Bill Jelen's latest Excel book: mrexcel.com/products/latest
You can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtube
Table of Contents
(0:00) Problem Statement
(0:24) Bill's Convoluted Method
(0:43) Kyle's Formula for Combinations using BASE
(1:00) Start the SEQUENCE at 0
(1:12) BASE function in Excel
(1:32) Clever: Add 1111
(2:02) One formula
(2:13) General steps for any N and K
(3:30) Break with MID and SEQUENCE?
(4:00) Break with TEXT and TEXTSPLIT?
(4:55) Thanks to Kyle
This video shows a cool use for Data Validation: Circling Invalid Data.
Also: manually circling a cell in Excel using the drawing tools. This method creates a filled circle. This video shows you how to remove the fill so you have a circle that does not cover the data.
To download this workbook: mrexcel.com/youtube/OpVReJuDvWs
Welcome to another #shorts video on Excel! In this video, we'll be showing you how to easily circle invalid data in Excel. There are two ways to do this, but we'll start with the "nerdy" way.
Let's say you have a data set and you want to make sure that all the values fall between 60 and 100. To do this, we'll use the data validation feature. Simply go to the Data tab, click on Data Validation, and set the criteria to allow a whole number between 60 and 100. Once you click okay, Excel will automatically highlight any values that do not meet this criteria.
But what if you just want to draw a circle around a specific data point? No problem! Simply go to Insert, choose Shapes, and select the oval shape. Draw the circle around the data point and then go to Shape Fill and select "No Fill" to make the circle transparent. You can also change the color if you'd like.
Now, here's a pro tip: if you need to circle multiple data points, simply hold down the control key and drag the circle around each point. This makes it quick and easy to circle all the invalid data in your spreadsheet. And there you have it, two simple ways to circle invalid data in Excel.
If you found this video helpful, please consider giving it a thumbs up and subscribing to our channel for more Excel tips and tricks. And don't forget to hit the bell icon to be notified whenever we post a new video. As always, feel free to leave any questions or comments down below. Thanks for watching!
Buy Bill Jelen's latest Excel book: mrexcel.com/products/latest
You can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtube
#excel
#microsoft
#exceltutorial
#exceltips
#microsoftexcel
#exceltricks
#excelhacks
Table of Contents
(0:00) Circle invalid data in Excel
(0:10) Define valid data in Data Validation first
(0:28) Manually drawing circles
(0:38) Making filled circle an outline
(0:48) Ctrl+Drag to copy circles in Excel
This video answers these search terms:
how do you circle invalid data in excel,
how do you circle something in excel,
how to circle on excel,
how to circle something in excel,
,how to circle something on excel,
how to make circle number excel,
how to put letters in a circle in excel,
how to type a with a circle on excel
Chris writes in. His VBA Editor is completely grey. How does he get back to the classic view with the Project Explorer in the Top Left, the Properties window in the bottom left and the Code on the right?
To download this workbook: mrexcel.com/youtube/C4T9CDfVA5g
Welcome to episode 2603 of the MrExcel netcast! In today's video, we're going to tackle a common issue that many Excel users face - the VBA window appearing all grey or the panes not docking correctly. This can be frustrating and can disrupt your workflow, but fear not, we have a solution for you.
Our viewer, Chris, wrote in with this problem and our goal is to get the classic view of the VBA window - Project Explorer on the top left, Properties window on the bottom left, and the Code window on the right. However, Chris is only seeing a grey window and we're going to fix that.
The first solution is to use the keyboard shortcut Alt+F11 to access the VBA window. From there, go to View and select Project Explorer, Properties Window, and Code. However, sometimes this doesn't work and that's where our second solution comes in.
If the panes are not docking correctly, it's possible that they have been accidentally undocked. To fix this, simply grab the title bar of the pane and drag it back to its original position. Then, when redocking, make sure to drag the pane until the mouse pointer is just outside the window, and a ghost image will appear. This ensures that the pane will dock correctly.
But what if the panes are still not docking correctly? This could be due to accidentally dropping the pane too high or too low. In this case, the best solution is to undock the Project Explorer and resize it to about two-thirds of the screen. Then, undock the Properties window and carefully dock it to the Project Explorer. Finally, resize the two docked windows to your desired size and carefully drag the whole set off to the side until you see the vertical ghosting. This should fix the issue and the Code window should now appear in the correct spot.
I hope this video was helpful in solving the issue of the grey VBA window or panes not docking correctly. If you found this video useful, please don't forget to Like, Subscribe, and Ring the Bell down below. And as always, feel free to leave any questions or comments in the comment section below. Thank you for watching and we'll see you in the next episode of the MrExcel netcast.
Buy Bill Jelen's latest Excel book: mrexcel.com/products/latest
You can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtube
Table of Contents
(0:00) Program Note
(0:11) Excel VBA Editor is all Grey
(0:50) VBA Panes not docking correctly
(0:55) Undocking VBA Pane
(1:08) Look for "Ghost" panel to redock
(1:28) Excel VBA Pane redocks full width
(1:46) VBA Editor no room for code pane
(1:56) Undock the Project Explorer
(2:08) Undock Properties Pane
(2:15) Dock Properties to Project Explorer
(2:30) Change Split Window in VBA
(2:44) Re-dock both windows
(3:00) View Code Pane in Excel VBA
The drawing tools in Microsoft Excel offer an oval. There is a secret trick to making the oval into a perfect circle: Hold down the Shift key while drawing.
To download this workbook: mrexcel.com/youtube/vUtrQeqxqRA
Welcome to my #shorts video on how to draw a circle in Excel! In this quick tutorial, I'll show you a simple trick to create a perfect circle using the oval shape tool. No more struggling to find a circle shape in the insert tab - just follow this easy tip and you'll be drawing circles in no time.
First, go to the insert tab and select the oval shape. Hold down the shift key while drawing the oval to create a perfect circle. It's that simple! But here's the real secret - right click on the circle and select "lock drawing mode". Now, you can hold down the shift key and draw multiple circles without having to select the oval shape each time.
But what if you need to circle something on a chart? No problem! Just select the chart first, then go to insert shapes and choose the oval. You can then draw a circle around the desired area. To remove the fill, go to shape fill and select "no fill". You can also change the color of the circle by selecting "shape outline" and choosing a different color.
If you found this tip helpful, please give this video a thumbs up and don't forget to subscribe and ring the bell for more Excel tutorials. And as always, feel free to leave any questions or comments down below. Thanks for watching!
Buy Bill Jelen's latest Excel book: mrexcel.com/products/latest
You can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtube
Table of Contents
(0:00) Draw a Circle in Excel using Shift Key
(0:22) Draw Many Circles in Excel
(0:33) Circle something on a chart in Excel
(0:44) Remove Fill in Excel circle
This video answers these common search terms:
how do you draw in a circle in excel
how to draw a circle in excel
how to draw circle in excel
how to draw circles in excel
how to make a circle in excel
how to make circles in excel
in excel how to draw shapes
how to draw on an excel sheet
how to draw on an excel spreadsheet
how to draw on excel spreadhseet
Mattea Roach and Andrew He finished the Jeopardy Masters Semi-Finals in a tie. Mattea Roach advanced thanks to a complex tie-breaker. What are the odds that the contestants who advanced to the finals would be decided by a tie-breaker?
It turns out that 18.5% of the possible outcomes would have required using a tie-breaker.
This video explains the math behind it all and the Microsoft Excel model used to make the calculation. To download the workbook: mrexcel.com/youtube/SUXTNAp4XgU
Welcome to another episode of "What Are The Odds" with Mr. Excel. In this episode, we will be discussing the recent Jeopardy Masters semi-finals tie that left viewers on the edge of their seats. If you haven't watched it yet, be warned, there will be spoilers ahead.
Mattea Roach and Andrew He battled it out in a tie to advance to the finals, leaving many wondering what are the odds of such a rare occurrence happening. In the United States, ties are often seen as unsatisfying and we have a saying that "a tie game is like kissing your sister." However, in other parts of the world, ties are more common and even beneficial, as we will see in this episode.
Before we dive into the Excel behind this tie, let's take a look at the possibilities. With 1,296 possible scenarios, the chance of a tie happening was 18.5%. But it could have been worse, with a 2% chance of a three-way or four-way tie. The tiebreaker rules state that the number of wins and correct responses in the semi-finals, including Final Jeopardy, will determine the winner. In this case, Mattea Roach came out on top with 50 correct answers, while Andrew He had 45.
But what led to this tie? As we saw in the episode, Mattea had a quick run of correct answers in her earlier semi-final game, which ultimately made the difference in the tiebreaker. If it had still been tied after that, the cumulative scores would have been the next tiebreaker. It's interesting to note that the tiebreaker rules are different in the finals, where the number of wins is the first tiebreaker.
Now, let's take a look at the Excel behind this model. With 19 formulas, we were able to solve for one of the 1,296 possible outcomes. But thanks to the amazing data table feature, we were able to quickly and easily calculate all possible outcomes and determine the chances of a tie. And with a 14.8% chance of a three-way or four-way tie, it's safe to say that we were lucky to only have a two-way tie in this semi-final.
In conclusion, the odds of a tie happening in the Jeopardy Masters semi-finals were almost one in five, making it a rare but not impossible occurrence. And with the help of Excel, we were able to break down the possibilities and determine the chances of a tie. Thank you for watching and don't forget to like, subscribe, and ring the bell for more Excel tips and tricks from Mr. Excel. And as always, feel free to leave any questions or comments down below. See you next time!
Buy Bill Jelen's latest Excel book: mrexcel.com/products/latest
You can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtube
Table of Contents
(0:00) Spoiler Alert
(0:21) Tie game is like kissing your sister
(1:06) Odds of a tie in Jeopardy Masters Semi-Finals
(1:33) Jeopardy Masters Tie-Breaker Rules
(1:50) Mattea Montage was foreshadowing the tie
(2:36) Bookmark MrExcel.com for future Excel questions
(2:53) The Excel model
(3:05) COMBIN(4,3) games
(3:20) Pairings easy way
(3:43) Six ways a 3-player game can finish
(4:05) Full outer Join
(4:28) 6^4 or 1296 outcomes
(4:43) Base-6 count up
(5:19) Solving for 1 outcome
(6:15) HSTACK and TRANSPOSE
(7:05) Go To Special Formulas
(7:19) What-If, Data Table to repeat logic for all scenarios
(8:05) Sorting columns high to low with SORT
(8:37) All possible outcomes with UNIQUE
(8:45) Finding Ties
(9:10) Converting percentage to Fraction in Excel
(10:24) Almost 20% chance of Tie
The Insert Shapes drop-down menu in Excel offers an oval, but not a semi-circle. How can you draw a semi-circle? Most of those shapes include a yellow handle for changing the inflection point. You can use this handle to make the partial circle into a half-circle or quarter circle.
Download the workbook from today: mrexcel.com/youtube/U2gvZtUW0vk
This short video shows you how.
Welcome to my #shorts video on how to draw a semicircle in Excel! In this quick tutorial, I'll show you how to create a semicircle shape using the insert shapes feature in Excel.
First, go to the insert tab and select shapes. You'll notice that there isn't a semicircle option, but don't worry, we can easily modify one of the existing shapes to create a semicircle.
Next, draw a partial circle by holding down the shift key while you draw. Then, look for the yellow handle on the shape and drag it to the edge of the cell border. This will serve as a guide for creating a perfect semicircle.
Once you have the shape in place, use the rotation angle to adjust the orientation of the semicircle. This will allow you to position it exactly how you want it.
If you found this tutorial helpful, please consider giving it a thumbs up and subscribing to my channel for more Excel tips and tricks. And don't forget to hit the bell icon to be notified of new videos. I love hearing from my viewers, so feel free to leave any questions or comments down below. Thanks for watching!
Buy Bill Jelen's latest Excel book: mrexcel.com/products/latest
You can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtube
Table of Contents
(0:00) Draw a semi-circle in Excel
(0:15) Yellow handle
(0:27) Rotation handle
This video answers these common search terms:
How to draw a semi-circle in Excel
How to draw a half circle in Excel
How to draw a quarter circle in Excel
Ashley wants to embed a range of Excel data in a Word document.
If the data in Excel changes (including adding more rows), she wants the Word document to update.
Annoying: If you use Insert, Object in Word, it captures the wrong range and truncates if you add more data.
My method: (1) Set the Print Range in Excel, (2) Select the Print range and Copy. (3) Paste to Word, (4) Open the paste drop-down menu and choose Linked Keep Source Formatting. This reliably works.
How do you solve this? Let me know down in the YouTube comments below.
Welcome to another episode of MrExcel's netcast! In this video, we will be discussing how to embed an expanding Excel range into a Word document with live updates. This is a question that was brought up by Ashley, who attended my seminar at UCF a week and a half ago. She was having trouble getting her changing Excel ranges into Word without the data being updated.
After some trial and error, I have found a set of steps that successfully solves this issue. The first step is to select the range in Excel and go to Page Layout, Print Area, and then Set Print Area. Make sure to save the file with a path and file name. Then, copy the entire area using Control+C and switch over to Word. In Word, paste the range using Control+V, but make sure to choose the third item in the dropdown menu, which is Link, and keep the source formatting. Save the file again.
Now comes the big test - can we insert more rows and change the numbers outside of the range and have the formula update? To test this, we will switch back to Excel and insert a few rows and change some numbers. As you can see, all the changes have been reflected in the Word document without even saving the file. This is a great solution, but it can be frustrating to have to follow these steps every time.
Ashley believes that there may be a simpler way to do this, so we will explore another method. In a different workbook with just one sheet and no named ranges, we will try to insert the range using Insert Object. However, this method does not respect the print range and includes columns that we do not want. After some more testing, we find that setting the print range in Excel before inserting the range in Word does not make a difference.
If you are an expert in this and know of a bulletproof way to embed an expanding Excel range into Word, please let us know in the comments below. We would love to hear your thoughts and suggestions. And a big thank you to Ashley for bringing up this question and to all of you for watching. Don't forget to Like, Subscribe, and Ring the Bell for more helpful Excel tips and tricks. And feel free to leave any questions or comments down below. See you next time on MrExcel's netcast!
Buy Bill Jelen's latest Excel book: mrexcel.com/products/latest
You can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtube
Table of Contents
(0:00) Problem Statement
To download the workbook: mrexcel.com/youtube/_q0VdM1ES1g
This is an advanced look at troubleshooting hard VLOOKUP errors. After using TRIM, CLEAN, and making sure there aren't numbers stored as text, then I use this method to look at the ASCII code, character by character to figure out why the two values do not match.
But today, the vendor part number is reporting that a hyphen is ASCII CODE 63 instead of 45. What is up with this?
I had to modify my workflow to add UNICODE functions in order to discover that the hyphen - which the CODE function reports as ASCII 63 is really Unicode 8208. Why did the vendor decide to use Unicode 8208?
The solution is to SUBSTITUTE( , UNICHAR(8208) with "-".
Also in this video:
Which characters are actually removed by the Excel CLEAN() function.
This might be our 2500th episode.
Welcome to another episode of MrExcel's NetCast! In today's video, we're going to tackle a common issue that many Excel users face - VLOOKUP failures due to a strange hyphen. This problem can be frustrating and time-consuming to troubleshoot, but fear not, as I will guide you through the steps to triage this issue character by character.
Our question for today comes from Michelle, who has been struggling with VLOOKUPs not working in a large file, even after using the TRIM() and CLEAN() functions. Whenever I come across this issue, I always ask for the file to see what's going on. In this video, I will walk you through the steps I take to identify the problem and find a solution.
First, I check the length of both the lookup value and the value in the table. If they are the same, I move on to the next step, which is to look at the characters one by one. To do this, I use the SEQUENCE() function to generate a list of numbers and the MID() function to extract each character. Then, I use the CODE() function to find the ASCII code for each character. This is where things get interesting.
As many of you may know, Excel uses the ASCII character set to represent characters. However, there is another character set called UNICODE, which has over 149,000 characters. This is where the strange hyphen comes into play. The hyphen in the lookup value has an ASCII code of 45, while the one in the table has a UNICODE code of 8208. This is why the VLOOKUP is failing - because Excel sees them as two different characters.
To solve this issue, we need to use the UNICODE() and UNICHAR() functions instead of the CODE() and CHAR() functions. We also need to add an extra column to our lookup table to check for UNICODE codes. In this video, I will show you how to use the SUBSTITUTE() function to replace the strange hyphen with a regular one, making our VLOOKUPs work again.
But that's not all. In this video, I also discuss the limitations of the CLEAN() function when it comes to handling UNICODE characters. While it can clean some of them, it fails to clean many others, causing VLOOKUPs to fail. This raises questions about the functionality of the CLEAN() function and the need for a UNICODE version of it.
As a side note, this may be our 2500th episode of MrExcel's NetCast. It's amazing to think that we've come this far, and I want to thank all of you for your support and for watching these videos. If you enjoyed this video, please don't forget to like, subscribe, and ring the bell to be notified of future episodes. And as always, feel free to leave any questions or comments down below. Thank you for watching, and I'll see you in the next episode of MrExcel's NetCast.
Buy Bill Jelen's latest Excel book: mrexcel.com/products/latest
You can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtube
Table of Contents
(0:00) Welcome
(0:21) VLOOKUP fails
(0:42) Character-by-Character Compare
(1:39) Explaining ASCII
(1:58) Using CODE() or CHAR() in Excel
(3:03) The hyphens are different
(3:34) Explaining UNICODE
(4:05) Klingon is not in Unicode
(4:43) Using =UNICODE() in Excel
(5:03) Unicode 8208 is a hyphen
(5:18) 28 dashes in Unicode
(5:40) Using SUBSTITUTE to replace Unicode 8208 with a Hyphen
(6:20) What does CLEAN() function really remove
(7:35) Open Questions
(7:55) Why does Excel CODE return ASCII 63 instead of a question mark
(8:20) Thanks for watching 2500 episodes!
(9:02) Like, Subscribe, Ring the Bell
A new version of the Advanced Formula Environment from Excel Labs has been released. It has an amazing new functionality called Import from Grid.
There are many times where I will build a complicated Excel formula in sub-formulas. I finally combine all the sub-formulas into one mega-formula by scooping the subformulas out of the formula bar and pasting in to the final formula.
The new Advanced Formula Envionment, found in the Excel Labs add-in, offers to create a LAMBDA function from existing worksheet logic in the grid.
Welcome to episode 2599 of MrExcel's netcast. In this video, we will be exploring an amazing tool that I just discovered last week - Excel Labs. This add-in allows you to create a LAMBDA from existing worksheet logic, making it easier than ever to build complex formulas.
We've all been there - faced with a problem that requires a lengthy formula, and it's just easier to break it down into smaller sub formulas. But then, when it's time to put it all together, we end up with a long, convoluted formula that is nearly impossible to explain to our coworkers. Well, Excel Labs has come to the rescue with their advanced formula environment and the ability to automatically create a LAMBDA from existing worksheet logic.
To get started, simply go to the Insert tab, click on Get Add-ins, and search for Excel Labs. This add-in, created by Microsoft Garage, is a game-changer for anyone who works with complex formulas. Once installed, you'll see the advanced formula environment and a new feature called Import from Grid. This is where the magic happens.
Simply provide the range of cells that contain your calculation, select the input cell and output cell, and click preview. Excel Labs will then encapsulate your existing worksheet logic into a LAMBDA with the LET function already built in. This is a much more efficient and easier to explain method than the traditional way of scooping out sub formulas from the formula bar.
But that's not all - Excel Labs goes above and beyond by using the headings in the row above your calculation to name the variables in the LET function. This attention to detail is what sets this add-in apart and makes it a must-have for anyone who works with LAMBDAs.
So if you're tired of struggling with long, complicated formulas, I highly recommend giving Excel Labs a try. It's a game-changer for anyone who works with complex calculations. And while you're at it, don't forget to like, subscribe, and ring the bell to stay updated on all our latest videos. Thank you for watching and we'll see you next time for another netcast from MrExcel.
Buy Bill Jelen's latest Excel book: mrexcel.com/products/latest
You can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtube
Table of Contents
(0:00) Welcome
(0:11) Build sub-formulas in Excel
(0:29) Scooping sub-formulas into mega-formula
(0:55) Advanced Formula Environment replaced by Excel Labs
(1:27) Getting the Excel Labs add-in
(1:45) Two apps in the Pane
(2:01) Import from Grid
(2:47) Named the Lambda & use in grid
(3:14) Headings in grid used in LET function
(3:58) Wrap-up
(4:21) Outtake: TEXTBEFORE and TEXTAFTER
Power Query for Mac Excel is missing From Table/Range.
Power Query for Mac Excel is missing From Folder.
Power Query for Mac Excel is missing From XML.
Power Query for Mac Excel is missing From JSON.
A new More Data add-in from Suat Ozgur at MrExcel adds all four of these functionalities to Excel for Mac! Download the add-in from this article: mrexcel.com/excel-tips/power-query-debuts-for-excel-for-mac-but-with-significant-gaps
Read more at this article: mrexcel.com/board/excel-articles/morequery-for-mac.68
The FREE, open-source add-in adds From Table/Range to Power Query for Mac. It adds From Folder. It adds From XML. It adds from JSON.
In this video, Bill Jelen introduces a new add-in for Mac Excel users created by his friend Suat Ozgur. The add-in, called "Get More Data", allows users to easily bring items back to Power Query from various sources such as tables, folders, JSON, and XML. This is a game changer for Mac users who previously had to use complicated methods to achieve the same result.
Suat demonstrates how the add-in works by showing how to create a Power Query query from a table or range. The add-in also has the ability to create a table if one does not already exist, making the process even smoother. Users can also add custom columns and perform any other transformations they desire, just like on Windows.
The add-in also allows users to easily import data from folders. Suat shows how simple it is to copy and paste the folder path and click "OK" to access the files. The add-in also has the ability to combine multiple Excel files into one, just like on Windows. However, Mac users may notice some additional worksheets created, but these can easily be deleted without affecting the refreshability of the data.
One limitation of Power Query for Mac is the inability to import data from web pages using HTML. However, the add-in covers this by providing connectors for JSON, XML, and API. Suat demonstrates how to use the API connector to retrieve data from a website and expand it in the Power Query editor. The add-in also has a lot of technical details and is available for free and open source, allowing users to improve and customize it to their needs.
Bill and Suat hope that this add-in will make the lives of Mac users easier and encourage them to download it and give it a try. The link to the article where the add-in can be downloaded for free is provided in the video description. Bill thanks Suat for his hard work and dedication in creating this add-in and encourages viewers to stay tuned for more helpful tips and tricks in future videos.
Buy Bill Jelen's latest Excel book: mrexcel.com/products/latest
You can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtube
Table of Contents:
(0:00) More Data Add-In fixes Power Query for Mac
(0:37) Get Data From Table/Range on Mac Power Query
(1:18) Get Data From Folder on Mac Power Query
(2:43) Get Data from JSON or XML on Mac Power Query
(3:27) Wrap-up


