MrExcel Excel Videos

You need to copy formulas but not screw up the formatting in cells My Method: Ctrl+C to copy, Paste Special, Formulas, OK Adam in Raleigh: Right-drag the formulas, choose Fill without Formatting

A question from a seminar in Raleigh: Need to fill the first and 15th of each month Or, need to fill the 15th and the last of each month Enter the first 2 dates. Select both dates. Right-drag the fill handle When you finish dragging, choose Fill Months

If we make a lump-sum payment against our mortgage, how will the number of payments change? First step: plug in current values from your mortage statement. Then: Use NPER function to figure out how many payments are left Use a one-variable Data Table to see the impact on future payments. Using the DATE function with a number of months Using DateDIF function to figure out the number of months between two dates

Steve has to sum numbers that have been entered in a text column There are multiple lines in each cell, separated by Alt Enter Need to split those lines to rows, then parse the dollar amount from the middle of each cell Summarize by Cost Center Build a lookup table Get totals from the lookup table, using IFNA to ignore the errors in the blank row Bonus: Add an Event macro to update the worksheet when they change a cell.

Check a column to see if there are any digits in the code Bill Method 1: Flash Fill Mike Method: Use the SUBSTITUTE function with an Array Constant. You will not have to use Ctrl+Shift+Enter because it is an array constant This will remove one digit at a time Use the AND function to see if every item in the resultant array is equal to the original item Bill Method 3: Use a VBA Function to check for digits

Janet wants to insert two rows below each customer's records How to insert multiple rows in one command in Excel Use an IF OR formula to figure out if this is two rows below. Use "A" or 1 Go To Special to select formulas that result in numeric Issue one Insert Row command

How can two people edit the same Excel workbook at the same time? This feature has been introduced in 2017 and is available to anyone using Office 365, Excel online, or Excel on the iPad. In order to co-author, the workbook has to be stored on the OneDrive or SharePoint. For regular features, like entering data and formulas, the co-authoring experience should be great. A colored box shows which cells are being updated. These boxes update quickly. Changes to other endpoints are shown in a few seconds. There are ways to break it - using Pens on the Draw tab of the Ribbon is one way. Excel might ask you to refresh the workbook on each endpoint if you hit one of these errors. The message telling you that the workbook could not be uploaded...

Do you have a new AutoSave icon in the top left corner of Excel? When this lights up, watch out, because you will be destroying data soon. The feature to allow multiple people to edit a workbook at the same time is called CoAuthoring. I need to Co-Author in 0.00001% of my Excel sessions. Co-Authoring was hard to implement, but it is finally done. So I am thrilled 0.000001% of the time. But 99.9999999% of the time, we have to deal with AutoSave, which is horrible. You have to change your workflow. Don't count on Save As after you make changes. You have to Save a Copy before making changes. When you forget, (and you will forget), use the dropdown in the title bar to go back. Please vote: AutoSave Master Switch rather than an "I Told YOu...

Goal is to create sample data with product;product;product;product Goal is to always have 2 or more products, up to a max of 12 Store a list of products in a custom list so you can easily generate a column of single products Using RANDBETWEEN() might return duplicate items in the list Use RAND() function to decide if this product is included or not Use TEXTJOIN() to concatenate the non-blanks with semi-colons in between Now that you have one result, how do you make many results Surprising that one Copy and multiple Paste Values will paste the current result of the formula Speed the Paste Values by using F4 to Re do But - a super fast way: Use What-If Tools and a Data Table with an empty cell as the Column Input Cell Thanks to Professor...