MrExcel Excel Videos

In Excel, can you calculate just the selected cells? Jennifer has a huge spreadsheet that takes forever to calculate. After calculating the entire workbook, the totals on SheetA have not updated. This short VBA macro will allow her to calculate only the selected range. Table of Contents (0:00) Problem: Entire workbook does not recalc (0:55) Add Manual & Automatic Calculation to QAT (1:12) Explanation of Manual Calculation Mode (1:56) Open VBA Editor add use Selection.Calculate (2:35) Saving Personal.XSLB (2:47) Adding Macro to QAT in Excel (3:20) Calculating selection in Excel (3:50) Broken Workbooks (5:00) Wrap up with Wally & Nancy

How to Create a Personal Macro Workbook in Excel. I have a lot of other videos where I want you to put a few lines of VBA into your Personal Macro Workbook. I am always thinking that many people won't have a Personal Macro Workbook. My goal with this video is that any time a future video mentions the Personal Macro Workbook, I can ask you to watch this video to create a Personal Macro Workbook and then you can go back to the other video for the VBA code for that particular task. Table of Contents (0:00) Excel Create a Personal Macro Workbook (0:18) Do you already have Personal.xlsb? (0:41) Recording Hello macro into Personal Macro Workbook (1:42) Need to Save Personal.XLSB so do it now (2:12) Add Developer tab to Ribbon (2:10) Open...

Om asks: I have 123,413,413,1352,244 in a single cell. There are many numbers with commas in between in each cell. How to Sum all the numbers using a formula? I don't want to use Text to Column. By the way, it is Excel 2010. I use Brad Yundt's three-line VBA function to split the numbers. Then Excel formulas to sum them. Also, an easier formula if you have Microsoft 365. Table of Contents (0:00) Sum all comma-delimited numbers in cell using an Excel formula (0:19) Easy solution with TEXTSPLIT (0:32) Switch to VBA for Brad Yundt Splitter code (0:55) Using SPLITTER custom function in Excel (1:25) Wrap up with Wally & Nancy

Back in episode 2359, I needed to add a blank row after every group of names. Although I was already in Power Query, I went out to Excel to add the blank rows. Several people commented with code to add a blank row after each group in Power Query. Thanks to @BillSzysz1 @radosawpoprawskiyourfriend769 @GeertDelmulle Rico S and Suat Ozgur for guiding me on how to add one line of M code to my query. I am at that stage in my Power Query journey that I prefer to do everything in the Power Query editor. Once I switch over to the advanced editor, I try to clean up the step names. And then I add in one line of M code before heading back to the Power Query Editor to finish the task. Table of Contents (0:00) Question: Insert blank row after each...

A question from @chwang06 : Asks how to count Super Bowl Squares by the name in the squares. In this video, I will show how to get a list of players with =UNIQUE(SORT(TOCOL(C3:L12))) and then count how many times each person played using =COUNTIFS(C3:L12,C17#) Table of Contents (0:00) Question: Count Excel Super Bowl Squares by Name (0:35) Make sure your Excel has TOCOL function or use Excel Online (0:55) Excel.New in Excel Online (1:05) Unwinding Excel range to 1 column vector (1:33) SORT and UNIQUE functions (2:00) COUNTIFS with Dynamic Range as second argument (2:30) Many names for # symbol (2:51) Mention video 2385 (3:00) Wrap up with Wally & Nancy

Is there an Excel formula that can return the country name from the telephone number? This video shows you how. To make your life easier, add this worksheet with five named ranges already in it: https://1drv.ms/x/s!As7G72Sl487Jlj1AUGli1Zgx0OgW?e=0Bda0m

After episode 2363, a pair of great questions and a tip. Tip 1: When specifying the Holiday Range in Excel, make the range into a table and then name the range. That way, when you add new holidays at the bottom, the named range will automatically expand. Question 1: How many holidays fall between two dates? Question 2: What is the list of holidays between two dates? This video includes details on NETWORKDAYS.INTL and FILTER functions. You will also see how I take two sub-formulas that were in different cells and combine them into a single formula by scooping the characters out of each formula. Table of Contents (0:00) Welcome (0:17) Binary option for WEEKEND argument in Excel (1:15) Table, Named Range for Holidays (1:37) Named range...

A cool Excel trick from Geert Delmulle - you can increase decimal repeatedly by holding down Alt+1. This works if you move the Increase Decimal icon to one of the first 9 spots on the Quick Access Toolbar. Table of Contents (0:00) Fast Way to Increase Decimal in Excel (0:47) After Alt key, use 1-9 for first 9 QAT icons (1:02) Hold Alt+2 to run 2nd QAT icon repeatedly (2:30) Adding icons to the Excel QAT (2:54) Moving QAT icon to beginning (3:06) Finding icons not on Ribbon for QAT (4:06) Save icon too far left after AutoSave removed

If you need to hide some rows in an Excel pivot table, you can Create a Set Based on Row Items. This is a rarely used feature: First, it is only available if you choose Add This Data To The Data Model. Once you create a set, it is a bit strange to remove rows. And if you add data later, you have to go back in to edit the set. Excel offers to let you edit the MDX, but what is MDX? Thanks to David Jefferson who provided a comment to a YouTube video, he shows how to use .Children to refer to all of the part numbers along a dimension. This is far more flexible. Table of Contents (0:00) What is MDX? (0:37) Pivot table based on Data Model (1:40) Hiding rows using a Set (2:51) MDX Defined (3:28) Editing MDX (4:40) Adding new data & Refresh