Excel - Show AutoFilter Selected Items - Episode 1827 MrExcel.com 2026-09-29 | Excel - Show AutoFilter Selected Items - Episode 1827
As You Celebrate Spreadsheet Day Tonight, Not Too Many PowerPivotinis! #spreadsheetday #excel MrExcel.com 2023-10-17 | October 17 is Spreadsheet Day! 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!
Do NOT ask your Boss for Time Off to Celebrate Spreadsheet Day! #OITNB #SpreadsheetDay #Excel #nsfw MrExcel.com 2023-10-17 | I don't care how good you are at spreadsheets, do NOT try to ask your boss for time off to celebrate Spreadsheet Day. It won't go well! 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.
Naked Cowboy Sings for Spreadsheet Day from Times Square #excel #spreadsheetday #nsfw MrExcel.com 2023-10-17 | 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.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.
D’Angel of Dancehall Rhymes for Spreadsheet Day 2023 MrExcel.com 2023-10-17 | Thanks to Jamaican Dancehall legend D'Angel for going along with my crazy songs for spreadsheet day 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 - Never Merge Cells - PSA from Sheriff Buford T. Justice #excel #spreadsheetday MrExcel.com 2023-10-17 | Even Buford T. Justice knows that Center Across Selection is better than Merge Cells in Excel!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.
Happy Spreadsheet Day from Roy Stride (Scouting for Girls) Original Song #spreadsheetday #excel MrExcel.com 2023-10-17 | To celebrate Spreadsheet Day (October 17 2023), I convinced Roy Stride, frontman for Scouting for Girls to sing this little song for all spreadsheet fans everywhere!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.
Excel - How to Remove Lines, Borders, Gridlines, or Strikethrough #shorts #excel #walkthrough MrExcel.com 2023-10-09 | Microsoft Excel Tutorial: Remove lines from Excel, whether they are borders, gridlines, or strikethrough.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#exceltricksThis video answers these common search terms:excel how to completely remove bordershow do i delete a line in excelhow do i remove a line in excelhow do you delete a line in excelhow do you delete a line in excel within a cellhow to delete a line to excell cellhow to delete an entire border in excelhow to delete black line in excelhow to delete borders from excelhow to delete borders in a excel spreadsheethow to delete borders in excelhow to delete cell borders excelhow to delete cell borders in excelhow to delete line in excelhow to remove a line inside an excel cellhow to remove all borders excelhow to remove all borders in excelhow to remove all cell borders in excelhow to remove blank line in excelhow to remove borders from an excel cellhow to remove borders from cells in excelhow to remove borders in excelhow to remove borders on excel tablehow to remove cell borders excelhow to remove cell borders in excelhow to remove divider line in excelhow to remove excel bordershow to remove grey border in excelhow to remove text line in excelhow to remove the line in excelyoutube remove border in excelhow to do a strikethrough line in excelhow to strikethrough a line in excelwhere is the strikethrough line in excel?did excel remove strikethroughexcel how to remove strikethroughhoe to add remove strikethroughs excelhow do draw a strikethrough in excelhow do you remove a strikethrough in excelhow do you remove a strikethrough in excel?how to add or remove strikethrough in excel?how to remove default cell borders in excelhow to remove diagonal line in excel cellhow to remove grid line from excellhow to remove gridline in excelhow to remove light borders in excelhow to remove line in excelhow to remove line on excelhow to remove strike line in excelTable 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
Excel VBA Macro To Flip All Checkboxes In Excel - Episode 2629 #excelhacks MrExcel.com 2023-10-06 | Microsoft Excel Tutorial: Multi-select all checkboxes and flip from true to false or back.To download the workbook from today: mrexcel.com/youtube/mpQ7IrPzenMCopy 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 0End SubSub AllTrueCheckboxes() On Error Resume Next For Each cell In Selection.SpecialCells(xlCellTypeConstants, 4) cell.Value = True Next cell On Error GoTo 0End SubSub AllFalseCheckboxes() On Error Resume Next For Each cell In Selection.SpecialCells(xlCellTypeConstants, 4) cell.Value = False Next cell On Error GoTo 0End Sub🔀 Excel VBA Macro To Flip All Checkboxes In ExcelAre 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#excelvbaThis video answers these common search terms:how to change the checkbox in excelhow to toggle all checkboxes in excelhow to flip all checkboxes in excelhow to use checkbox macro in excelhow to use checkboxes in excelhow to create and use a checkbox in excel?excel how to add and use checkboxesTable 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
Excel - Better Checkbox Style in Excel - Quick Tips #shorts #excelhacks MrExcel.com 2023-10-04 | Microsoft Excel Tutorial: Better looking checkboxes in Excel.📊 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#excelnewThis video answers these common search terms:check box excel how toadd check boxes in excel to cellhow to add boxes to check in excelhow to put box check in excelmake check boxes in excelhow to make boxes check boxes excel
Excel - Exciting! Checkboxes Arrive In Excel - Episode 2628 MrExcel.com 2023-10-03 | Microsoft Excel Tutorial: Checkbox New Feature arrives in Excel October 3, 2023.To download the workbook from today: mrexcel.com/youtube/kunbe45v7-EIn 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#excelnewThis video answers these common search terms:how to do checkbox in excel formulacan you check a box with excel macrohow to use conditional formatting in excel and check boxeshow to apply conditional formatting to a check box in excelhow to add checkbox and text in excelhow to check box in excel with vbacheck box excel how toadd check boxes in excel to cellhow to add boxes to check in excelhow to put box check in excelmake check boxes in excelhow to make boxes check boxes exceladding check boxes in excelcreate check boxes in excelhow add checkbox excelhow add checkbox in excelhow do check boxes in excelhow to add a box to check in excelhow to check box exceladding a checkbox on excelhow add check box to exceladding a check box to excelhow add a checkbox in excelhow to check a box excelhow to check a box in excelhow to do checkbox in excelhow to do checkboxes in excelcan i do a checkbox in excelcan i put check boxes in excelcan you do checkboxes in excelhow insert checkbox in excelhow to add checkbox at excelhow to add checkbox in cell excelhow to add checkbox in cell for excelhow to add checkbox in excelhow to add checkbox in excelTable 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
Excel - Uncover Vehicle Repair Cost Secrets - Episode 2627 MrExcel.com 2023-09-29 | Microsoft Excel Tutorial: Calculate Average Cost per Year in Excel.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/latestYou can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtubeTo download the workbook from today: mrexcel.com/youtube/lfn5FGRA5wgThis 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/latestYou 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 excelhow to get monthly average excelhow to average monthly data in excelhow to find daily average in excelhow to calculate daily average in excelhow to calculate daily average in excel formulahow to calculate average daily sales in excelhow to calculate average age in excel formulahow do you calculate average age in excelhow to average multiple columns in excelhow to average multiple data in excelhow to calculate average in excelhow does excel calculate averagehow to use minifs function in excelhow to use minifs in excelhow to use minifs excelexcel formula to find the earliest datehow to use datediff in excelhow to do a datedif function excelhow to use datedif function excelhow to use datedif in excelhow to use the datedif excel functionwhere is datedif in excelwhere is datedif in excelhow to use excel formula datedifhow to use a datedif function in excelhow to use datedif function in excelhow to use the datedif function in excelTable 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
Still In Hunt For MLS Supporters Shield As Of Sept 22 2023 Using Excel - Episode 2626 #mls MrExcel.com 2023-09-23 | Microsoft Excel Tutorial: Modeling the rest of the MLS Season 100,000 times.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/1VLFg7VeW7MWelcome 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/latestYou can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtubeBuy Bill Jelen's latest Excel book: mrexcel.com/products/latest
Excel Adds Python Editor 27 Days After Python Debuts - Episode 2625 MrExcel.com 2023-09-19 | Microsoft Excel Tutorial: Microsoft Labs in Cambridge releases a great Python editor for Excel.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/UKpmNAMjyggWelcome 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/latestYou can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtubeThis 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#walkthroughBuy Bill Jelen's latest Excel book: mrexcel.com/products/latestTable 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
Excel - Oracle Sending Dates as Text MMM-YY Episode 2624 #excel #pivot_table #excelhacks MrExcel.com 2023-09-14 | Microsoft Excel Tutorial: Sorting months in a pivot table when they are alphabetic.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/BpgjOMlh39EWelcome 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/latestYou can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtubeThere are two ways to solve it:1) A convolunted method from Sam Radakovitz that you only have to do once2) 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#pivottableTable 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
Excel Hack Use Filter Search Box to Remove Items from Filter! - Episode 2623 #excel #excelhacks MrExcel.com 2023-09-13 | Microsoft Excel Tutorial: How to use Excel's Filter Search Box to remove items from Filter.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/Od5IwswzWnIWe 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 Apple2. Uncheck Select All Items3. Check Add Current Selection to FilterThis 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/latestYou can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtubeTable 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
Excel Sum Across Sheets When Rows Do Not Line Up - Episode 2622 MrExcel.com 2023-09-08 | Microsoft Excel Tutorial: Sum across worksheets when rows do not line up?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_YsThis 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/latestYou can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtubeTable 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
Excel Python Appending Data Frames From Multiple Worksheets - Episode 2621 MrExcel.com 2023-09-01 | Microsoft Excel Tutorial: Appending data from multiple worksheets using Python in ExcelTo download today's workbook: mrexcel.com/youtube/3rAJPcjO6OkToday, 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/latestYou can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtubeTable 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
Excel Python 3D Scatterplot - Episode 2620 MrExcel.com 2023-08-31 | Microsoft Excel Tutorial - Drawing a 3D Scatterplot using Python in ExcelDownload the workbook: mrexcel.com/youtube/TMyAAxbUU7sWelcome 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/latestYou can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtubeHow to plot a circle in an Excel XY ChartConverting degrees to radians in ExcelBuilding X, Y, Z values for a series of circles in ExcelUsing Chat-GPT for Python Code for a 3D Scatter PlotAdapting the copied codeMarker choices for Python ChartsTable 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
Excel Python XLOOKUP 2619 MrExcel.com 2023-08-30 | Microsoft Excel Tutorial: VLOOKUP in Python for ExcelHow to do a VLOOKUP or XLOOKUP in Python.To download the examples in this workbook: mrexcel.com/youtube/h6__fB7vo4kWelcome 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/latestYou can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtubeAlong the way, you will see:101: Doing a VLOOKUPNot 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
Excel Display Only Last 4 Of Social Security Number - Episode 2618 MrExcel.com 2023-08-29 | Microsoft Excel Tutorial: Hiding part of Social Security NumberDownload this workbook from: mrexcel.com/youtube/MeCptGsHsYYWelcome 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/latestYou can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtubeSomeone 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
Excel Python Custom Function - 2617 MrExcel.com 2023-08-28 | Microsoft Excel Tutorial: Using a function in Python for Excel calculations.The problem today: Count how many times a word occurs in a cell in Excel.To download this workbook: mrexcel.com/youtube/6Ydb6rIls6MWelcome 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/latestYou can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtubeThe 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 cellAdding Text as line line of a Python script to appear in the cell.Printing to the Python ConsoleRemoving the Index column using two sets of square bracketsIn 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
Excel Python Pivot Tables In Excel -Episode 2616 MrExcel.com 2023-08-25 | Microsoft Excel Tutorial: Using Python in Excel to create Excel-like pivot 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_Nyo9OKxKAThe Python Pivot Table is missing a few things:1. Row Fields are called Index2. Defaults to Average instead of Sum3. Empty cells show as errors. Use fill_value4. No Grand Totals by default! Turn on with margins=True5. When you add Grand Totals, they are called "All" Unless you change them with Margins_Name6. When you group by dates, you can't have grand totals7. Does not sort by Custom Lists8. Odd arrangement of headings when 2 row fieldsBut Python pivot tables have some advantages over Excel pivot tables:Advantages over Excel1. Automatic Recalc without Refresh2. Date Grouping Offers some amazing optionsIn 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 tableWelcome 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/latestYou can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtubeTable 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
Gaining Confidence With Python In Excel - 2615 MrExcel.com 2023-08-24 | Microsoft Excel Tutorial: Python in ExcelAfter 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 productsTo download the first workbook: mrexcel.com/youtube/GMF1E0dmjWsThe 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
Python In Excel - Getting Started - 2614 MrExcel.com 2023-08-22 | Microsoft Excel Tutorial - Using Python 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/KIhDQDtvZPgWelcome 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/latestYou can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtubeIn this video: a getting started with Python in Excel tutorial.How to open the Python editor in ExcelCtrl+Enter versus Enter in the Python Editor in ExcelReturning a Value or a Python ObjectReferring to an Excel range in PythonUsing Variables in Python in ExcelPlotting Data Using Python in ExcelWhich Python Libraries are loaded by default?K-Means Clustering for customer segmentationTable 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?
Excel Stale Value Formatting - Episode 2613 MrExcel.com 2023-08-18 | Microsoft Excel Tutorial: Which values are not yet calculated in Manual Calculation Mode?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/LPyYLqq72wIWelcome 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/latestYou can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtubeMore details on the feature: techcommunity.microsoft.com/t5/excel-blog/stale-value-formatting/ba-p/3887098Table 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?
Excel Add-In To Streamline Bill Of Material BOM Analysis - Episode 2612 MrExcel.com 2023-08-09 | Microsoft Excel Tutorial - BOM Tool Suite from Main Sheet SolutionsDo 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.comDownload the two data sets that I used in this video: mrexcel.com/youtube/k4A1lg9pPlcWelcome 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/latestYou can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtubeTable 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
Excel Fill Handle To Copy Formula Rules Deep Dive - Episode 2611 MrExcel.com 2023-08-07 | Microsoft Excel Tutorial - Troubleshooting when you double-click fill handle to copy a formula.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 columnb) 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_BDx3Lx9wWelcome 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/latestYou can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtubeTable 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
Excel Battle on ESPN - Sketch Comedy - 3 Dimes on Diarmuid Early MrExcel.com 2023-08-03 | Microsoft Excel Tutorial: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
ESPN showing Excel battle Friday at 7AM - At Least The Sun Is Up! MrExcel.com 2023-08-03 | Microsoft Excel Tutorial: Watch the Excel Elimination Battle on ESPN: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/Ireland1 PM Amsterdam / Berlin / Paris4:30 PM New Delhi7 PM Hong Kong9 PM MelbourneTable 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
Excel Esports Returns on ESPN8: The Ocho! on August 4, 2023 MrExcel.com 2023-07-28 | Microsoft Excel Tutorial: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-5Table 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
Leagues Cup Tie Probability / Will Messi Play in Orlando in August - Excel episode 2610 MrExcel.com 2023-07-17 | Microsoft Excel Tutorial: Tiebreakers in Leagues Cup Soccer Group Stage.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/M6xbdRFASnIWelcome 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/latestYou can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtubeTable 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
Embedding Excel Range in a Word Document - Strategic Finance Magazine July 2023 MrExcel.com 2023-07-11 | Microsoft Excel Tutorial: Embedding Excel Range in Microsoft WordHow 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-documentIn 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/latestYou can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtubeTable 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 WordBuy Bill Jelen's latest Excel book: mrexcel.com/products/latest
Excel Improvements To Picture In Cell - Episode 2608 MrExcel.com 2023-07-09 | Microsoft Excel Tutorial: Adding Filterable Text to a Picture in CellThe 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_XkkWelcome 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/latestYou can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtubeTable 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
Excel For The Infection Preventionist - Preview of APIC Pre-Conference MrExcel.com 2023-06-16 | Microsoft Excel Tutorial: Using Excel to Map Data from the CDCThe 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/latestYou can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtubeTable 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
Excel Place Local Pictures In Cell Using Formula and a VBA Hack - Episode 2607a MrExcel.com 2023-06-08 | Microsoft Excel Tutorial: Use Excel VBA to replicate the IMAGE function for images stored locally.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/0NArag0pV7IWelcome 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/latestYou can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtubeTable 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
Excel - Picture Place In Cell - Episode 2606 MrExcel.com 2023-06-06 | Microsoft Excel Tutorial - new feature to place a picture in a cell in Excel.Can the Excel IMAGE function use a local hard drive? Not yet.New Excel VBA method: InsertPictureInCellInsert Many Pictures in Excel in one command.Feature Requests:Expose a VBA property to show original source of the imageAllow IMAGE function to point to local hard drive.Allow Power Query to load in-cell images.To download this workbook: mrexcel.com/youtube/mFd8HsHmfpAWelcome 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/latestYou can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtubeTable 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
Excel - Indicator if Macro Recorder is Relative or Not - #shorts MrExcel.com 2023-06-01 | Microsoft Excel Tutorial - is there a way to know at a glance if the macro recorder is in Relative Recording Mode or not?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
Excel - Draw a Line through Text (Strikethrough) #shorts MrExcel.com 2023-05-31 | Microsoft Excel Tutorial - How to draw a line through text.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/PmQpp46p3lsTable 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-upCommon Search Terms? This video answers these search terms:how do draw a strikethrough in excelhow do i draw a line through text in excelhow to draw line text in excelhow to draw line through text excelhow to draw line through text on excelhow to draw line through word in excel
Excel - Draw Arrows or Dotted Line in Excel - #shorts MrExcel.com 2023-05-30 | Microsoft Excel Tutorial - How to Draw Arrows or Line in Excel.How to draw an arrow in Excel?To download this workbook: mrexcel.com/youtube/ojM42vamBQYTable 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 upCommon Search Terms:how do i draw arrows in excelhow do you draw arrows in excelhow to draw a arrow in excelhow to draw an arrow in excelhow to draw an arrow in excel 2010how to draw an arrow line in excelhow to draw arrow in excelhow to draw arrow in excel sheethow to draw arrows in excelhow to draw arrows on excelhow to draw red arrow in excelhow to draw straight arrow in excelwhere do i find abilitty to draw arrows in excelhow to draw dotted line in excelhow to draw dotted line in excel charthow to draw a line in excel cellhow to draw a straight line in excelhow to draw line in excel sheethow to draw lines in excel sheethow to draw lines in microsoft excelhow to draw straight lines in excelhow do i draw a straight line in excelhow do you draw a straight line in excelhow do you draw lines in excel
Excel Variance Inside The Pivot Table - Episode 2605 MrExcel.com 2023-05-30 | Microsoft Excel Tutorial: Adding a Variance inside of a pivot table.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 Total3. 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-yRp9N59pwWelcome 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/latestYou can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtubeTable 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
Excel All Combinations One To Six in Four Columns - Episode 2604 MrExcel.com 2023-05-26 | Microsoft Excel Tutorial: Generating all combinations of N outcomes for K games.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/wLs8U2RMi7IWelcome 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/latestYou can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtubeTable 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
Excel - Two Easy Ways to Circle Invalid Data - #shorts #excel #exceltricks MrExcel.com 2023-05-25 | Microsoft Excel Tutorial - Circle invalid data in ExcelThis 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/OpVReJuDvWsWelcome 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/latestYou can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtube#excel#microsoft#exceltutorial#exceltips#microsoftexcel#exceltricks#excelhacksTable 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 ExcelThis 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
Excel VBA Window is All Grey Or Panes Will Not Redock Correctly - Episode 2603 MrExcel.com 2023-05-25 | Microsoft Excel Tutorial: The VBA Editor Panes are missing or not docked correctly.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/C4T9CDfVA5gWelcome 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/latestYou can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtubeTable 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
Excel - How to Draw a Circle in Excel #shorts MrExcel.com 2023-05-24 | Microsoft Excel Tutorial - Draw a Circle in ExcelThe 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/vUtrQeqxqRAWelcome 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/latestYou can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtubeTable 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 circleThis video answers these common search terms:how do you draw in a circle in excelhow to draw a circle in excelhow to draw circle in excelhow to draw circles in excelhow to make a circle in excelhow to make circles in excelin excel how to draw shapeshow to draw on an excel sheethow to draw on an excel spreadsheethow to draw on excel spreadhseet
Jeopardy Masters Semi-Finals Tie! What Are The Odds - Episode 2602 MrExcel.com 2023-05-24 | Microsoft Excel Tutorial: What are the odds of a tie in Jeopardy Masters?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/SUXTNAp4XgUWelcome 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/latestYou can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtubeTable 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
Excel - Draw a Semi-Circle #shorts MrExcel.com 2023-05-23 | Microsoft Excel Tutorial - how to draw a semi-circle in Excel.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/U2gvZtUW0vkThis 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/latestYou can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtubeTable of Contents(0:00) Draw a semi-circle in Excel(0:15) Yellow handle(0:27) Rotation handleThis video answers these common search terms:How to draw a semi-circle in ExcelHow to draw a half circle in ExcelHow to draw a quarter circle in Excel
Excel - Embed Growing Range In Word Document - 2601 MrExcel.com 2023-05-23 | Microsoft Office Tutorial - live link to Excel range in Word.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/latestYou can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtubeTable of Contents(0:00) Problem Statement
Excel VLOOKUP Fails At Strange Hyphen - 2600 MrExcel.com 2023-05-22 | Microsoft Excel Tutorial - Deep Dive on diagnosing VLOOKUP errors.To download the workbook: mrexcel.com/youtube/_q0VdM1ES1gThis 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/latestYou can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtubeTable 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
Excel Labs Creates A LAMBDA From Existing Worksheet Logic - Episode 2599 MrExcel.com 2023-05-15 | Microsoft Excel Tutorial - Easiest Way to Create a Lambda function in Excel.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/latestYou can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtubeTable 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
Excel for Mac New AddIn Brings More Data To Power Query - 2598 MrExcel.com 2023-05-08 | Microsoft Excel Tutorial: Replace the missing connectors in Mac Power Query.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-gapsRead more at this article: mrexcel.com/board/excel-articles/morequery-for-mac.68The 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/latestYou can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtubeTable 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
