MrExcel Excel Videos

Geography and Stocks are the first types of Linked Data Types in Excel This feature is exclusive to Office 365 since it requires downloading online data Type some cities, select the cities, mark as Geography Hover over icon to see data card New =A2.Population syntax or wrap in square brackets: =A2.[National anthem] New FIELDVALUE function New #FIELD! error Cool trick: Sort table by a column that is not there Field error during refresh How to Recalc using Data, Refresh

Insert Forms appeared in Excel recently Build a form online at Forms.Microsoft.com Share the form with a QR Code or a link The results will appear in your Excel workbook! To download this workbook: https://www.mrexcel.com/download-center/2018/07/surveys-and-forms-in-excel.xlsx List of upcoming seminars: Excel Seminar Schedule New Data Analytics Course: https://mrx.cl/imadatacourse

Easy checkmarks in Excel from Jack Whipple Rather than use P in Wingdings 2 font, you can use Icon Sets 1 = Green Checkmark: Done 0 = Empty Circle: Still to Do -1 = Yellow Dash: Not required this year, but keep it on the list for next year To start, Home, Conditional Formatting, Icon Sets, 3 Symbols (Uncircled) Home, Conditional Formatting, Manage Rules. Change second symbol to a round circle. 3rd symbol to dash Bonus tip from Debra Dalgleish at Contextures.com (subscribe to her weekly newsletter!) http://www.contextures.com/newsletter/ctxnewsindexlist.html Conditional Formatting in A: Use Formula. If B = 1, then strikethrough Why is Ctrl+5 strikethrough? Because when people used to take inventory with hash marks |||| the fifth would...

Ctrl+Wheel Mouse will increase the size of the grid in Excel But it does not increase the size of the dialog boxes When I am presenting in a big room with a screen that is too small, the magnifier helps Press Windows key and Plus Sign Three types of magnifier: Full Screen is the default. Docked creates a part at the top of the screen. I prefer Lens, but change the size of the lens Important: To turn it off: Windows Key and Escape

Susan in Melbourne wants to create a pivot table that shows text in the values area. Typically, this can not be done. But it is possible with the DAX formula language. Format as Table using Ctrl+T Insert, Pivot Table, Add This Data to the Data Model Build the Row & Column Areas Right-click the Table name in the Fields list, choose Add Measure Formula is =CONCATENATEX(Table1,Table1[Code],", ") To download this workbook: https://www.mrexcel.com/download-center/2018/07/pivot-table-with-text-in-values-area.xlsx

Commas stop working in formulas in Excel. As soon as you type a comma and move on to the next argument, the formula gives you an error. Clever: Build the formula with the Function Arguments dialog, and you will see =VLOOKUP(A2|Table|2|False) The | is a pipe. It is above the backslash on the US keyboard Go to Control Panel, Region & Language Settings, Additional Settings, List Separator Change the List Separator back to a comma If you changed the list separator because some system is giving you PSV instead of CSV, watch: Windows 7, Control Panel, Regional Settings, Additional Settings, List Separator Windows 10, Control Panel, Clock, Language and Region, Region: Change date, time or number formats, Additional Settings, Numbers...

Lisbeth in Calumet: How do I get rid of vertical merged cells? My HQ keeps sending me files with these every day? Plan: Save the workbook in a reliable place with a reliable name Create a blank reporting workbook Data, Get Data, From File, From Workbook, Specify Sheet1 Presto! Merged cells are gone. Select the column and Fill Down. Close and Load. Every time you get a new workbook, Save As in the reliable place with the reliable name Open the reporting workbook and refresh To download this workbook: https://www.mrexcel.com/download-center/2018/07/fill-merged-cells-down.xlsx

Mystery in Excel: How can you AutoSum at the top of a column of numbers? Does anyone have a great solution? Note it in the YouTube comments. Win a prize. My lame solution is a macro To download this workbook: https://www.mrexcel.com/download-center/2018/06/autosum-from-top.xlsx

Suddenly, all buttons, shapes, SmartArt, WordArt is missing from the workbook. All of the Insert, Illustrations is greyed out. What happened to all of my shapes in Excel? What happened to all of my images in Excel? Where did it go? What happened? There is a good chance that someone clicked Ctrl+6 to toggle Show/Hide Shapes