MrExcel Excel Videos

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...

Use conditional formatting to mark formula cells New Rule, Use a Formula, =ISFORMULA(A1) Thanks to Craig Brody: mark formula cells that are links to other sheets New Rule, Use a Formula, =ISNUMBER(FIND("!"),FormulaText(A1))) Extending this: To mark cells pointing to external workbooks, use this formula: =ISNUMBER(FIND("]",FORMULATEXT(A1))) You can use AutoSum in Several Cells at Once When you are editing a formula in conditional formatting box and press the arrow to move, you will get cell references. To prevent this, use F2 to change Enter mode to Edit mode

Quickbooks loves to insert tiny column between the columns To speed up the process, delete first column, then use F4 for Fast Re-Do Better: Select a row. Go to Special, Blanks, Ctrl+-, Entire Column, OK Thanks to Carmella in Green Bay

The goal is to build a Year-over-Year report in a pivot table and show the % Growth I almost always do the percentage growth calculation as a formula outside of the pivot table But that has issues, such as GetPivotData and the formula needs to handle if the pivot table shrinks or grows Using a technique that I learned from Tobias Ljung, you can build the calculation in to the pivot table. Add revenue a second time to the pivot table. The calculation is % Change from, Years, Previous Item Hide the % change for the first year (they are all blank)

A viewer downloads data from a system where each item is separated by Alt+Enter Need to VLOOKUP each item in the cell Use Power Query to split the items out, do VLOOKUP, then join them back together This is like doing a ConcatenateX in DAX (which does not exist) or a TEXTJOIN(CHAR(10) in Office 365 Excel Built a solution using Power Query, including the Structured Column tool of Extract As That feature only works on a list, not a table, so I used the Table.Column function to convert the table to a list.

A viewer downloads data from a system where each item is separated by Alt+Enter Bill: Why are you doing this? Viewer: It is how I inherit the data. I want to keep it that way. Bill: What do you want to do with the 40% of values not in the table? Viewer: No answer Bill: There is a complicated way to solve this if you have the latest Power Query tools. Instead, a VBA Macro to solve it - the macro should work all the way back to Excel 2007 Instead of doing VLOOKUP, do a series of Find & Replace with VBA

When you make your data into a table using Ctrl+T, any charts, pivot tables, VLOOKUP using that range will automatically expand.