MrExcel Excel Videos

For a special holiday-week treat, change the color of the gridlines in Excel to red. It could drive your co-workers insane. Actually, as time goes by, the gridlines are becoming more faint. Changing the gridline color from automatic to black might make the Excel gridlines easier to see.

Today, a quick tip for sorting left to right in Excel. With the correct headings, it is a faster way to rearrange columns. Check out all 12 expert tricks at:

A question from the Las Vegas IMA: How do you do date rollups in Excel when the company is on a fiscal year that does not end on December 31? This video shows how to use Power Query to link to a Calendar table. The pivot table uses the Data Model and you create a relationship.

A challenging puzzle from FMWC - Financial Modeling World Cup. How to solve a Word Maze or a Word Search using Excel. This maze includes 20000 letters! You have 30 minutes to score 1000 points using Excel formulas or VBA. If you have any solution for finding words diagonally, leave a comment. If this looks like something you would be good at solving, consider joining the competition. Excel as esports: sign-up for an FMWC Open - a competition where every Excel user gets a chance to show off their skills and compete for $10,000 prize fund. Sign up here: FMWC Open – FMWC Save $25 on your registration by answering three questions: Microsoft Forms Table of Contents (0:00) Welcome (1:19) Strategy (3:38) Skip the easy ones first (4:07)...

How to create your own custom data types in Excel using ScriptLab and the Javascript API. Important Links: Try out the workbook with my custom data types: TestTheseCustomDataTypes.xlsx Github for the example using a Custom Function: https://gist.github.com/smozgur/a34ad495fb75e19c3e21904875ef0170 Github for the example using an add-in: https://gist.github.com/smozgur/bf5276622820b1fbbec6690c1689762c Table of Contents (0:00) The quest for a perfect custom data type (1:50) Download a workbook with working custom data types (3:54) The back-end API (5:17) ScriptLab add-in (8:33) ScriptLab Function (10:49) Visual Studio version (12:00) Nerdvana check

The data types gallery with Geography, Stocks, Currency, and Wolfram is mildly interesting. But I think the real goal is to get your own company data in the Data Type. I want it to be a very rich experience, just like Wolfram Data Types: 1) Type plain text and convert to a data type 2) From the card for the data type, return an array of entities (such as all the people in Bob Marley and the Wailers music act. 3) Have those fields returned from the Data Card be Data Types as well, so you can drill down and get more information about the sub-entity 4) The data type should be able to return an image from an Image URL 5) The data type should be able to return a clickable hyperlink. 6) It would be nice if the little icon for the data type...

The Excel team snuck a new feature into Excel and I didn't realize it for a while. Thanks to Excel MVP Abiola David for realizing that you can left-click any number in the status bar to copy that number to the clipboard. You can then paste to an e-mail or a text file or, I guess, even to Excel. This also has a throw-back reference to the 2012 book, Don't Fear the Spreadsheet, available here: Don't Fear the Spreadsheet

Jon wants to generate 6-letter sequences using only the letters B and P. So, for example: BBBBBB, PPPPPP, BPBPPB, BBBPPP, and so on. There are 64 such combinations and Bill shows you one way to solve this use BASE and SUBSTITUTE. Formulas used for one word: =SUBSTITUTE(SUBSTITUTE(BASE(RANDBETWEEN(0,63),2,6),"0","B"),"1","P") The red box suggested a shorter formula of: =CONCAT(CHAR(66+14*RANDARRAY(6,1,0,1,TRUE))) Formula for all 64 words, in sequence =SUBSTITUTE(SUBSTITUTE(BASE(SEQUENCE(64,1,0),2,6),"0","B"),"1","P") Formula for those 64 words sorted randomly =SORTBY(SUBSTITUTE(SUBSTITUTE(BASE(SEQUENCE(64,1,0),2,6),"0","B"),"1","P"),RANDARRAY(64)) If you have the number of letters in A3, then generate all words in sequence...

December 4th 2021 is a red-letter day for Microsoft Excel. It is the first day that an Excel competition was carried live on the ESPN family of networks. Watch again on Wednesday December 8: Schedule | Watch ESPN On on December 11 2021: https://www.espn.com/watch/schedule/_/type/upcoming/categoryId/634d383d-6f26-39cd-9efa-feb4eff23547/channel/3e99c57a-516c-385d-9c22-2e40aebc7129/startDate/20211211 In this video, I take a stab at getting 310 points on the Feeding Excelopolis case study. Thanks to the new FILTER function, LARGE with SEQUENCE, and a What-If Analysis Data Table, I have a way to get to 310 points.