MrExcel Excel Videos

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

How can you build a Power Map with two different data sets? Step 1: Get both tables into the Data Model. You can do this one of three ways: 1a) Power Query with Load To, Add this data to the data model 1b) Power Pivot - add this data to the data model 1c) Create a pivot table, choosing the box, add this data to the data model When you launch Power Map, you can build the first map, then Add Layer and use the other data set. To download this workbook: https://www.mrexcel.com/download-center/2018/06/power-map-from-two-data-sets.xlsx

Jimmy from Huntsville wants to arrange people on a bell curve Use a pivot table to figure out the average score. Sort the pivot table so the scores are arranged low to high. These scores will be the X values. In an extra cell, calculate the AVERAGE() and STDEV() of the scores. Use formulas to copy the data from the pivot table to a new range Calculate a Y value for each person with =NORM.DIST(A2,Mean,StDev,False) Create an XY Scatter chart with smooth lines In Excel 2010 or Earlier, use Rob Bovey's Chart Labeler add-in (Google it…) In Excel 2013, Add Data Labels, From Cells, Specify the names Micro-adjust the chart to change the scale and move the labels that overset each other To download this workbook...

David from Florida has two workbooks that he wants to combine. Both workbooks have the same field in column A, but then different data in the remaining columns. One workbook might have extra items that are not in the other and David wants to see those. There are no duplicates in either file You can use Power Query to solve this. In David's case, each data set is in a separate workbook. Start in a new blank workbook on a blank worksheet. Step 1: Data, From File, Workbook. Close & Load to… Only Create Connection Step 2: Data, from File, Workbook. Close & Load to… Only Create Connection Step 3: Data, Get Data, Merge. Select the two connections. Select the column common in both. For the join type: All from larger file, matching from smaller...

Why does the Comma icon in Excel do so much more than a comma? 75% of the things that it does are not advertised, not expected, and not wanted: 1. Adds two decimal places 2. Shows zero as hyphen 3. Moves the numbers away from the right edge of the cell 4. Adds a comma Greg in Fort Wayne suggests using Cell Styles Comma 0 & this does remove one annoyance for a few extra clicks Dustin suggests some code for your Personal Macro Workbook Sub FixCommaStyle() ActiveWorkbook.Styles("Comma [0]").NumberFormat = "#,##0" End Sub Vote: https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/33545875-number-format-thousand-separator-and-no-decimals To download this workbook...

Bill's problem today: I recorded code to insert a picture, and it is creating a link to the picture Don't use the recorded code of ActiveSheet.Pictures.Insert Instead use updated code of ActiveSheet.Shapes.AddPicture(Filename:=NewFN, _ LinkToFile:=msoFalse, _ SaveWithDocument:=msoTrue, _ To download this workbook: https://www.mrexcel.com/download-center/2018/06/vba-insert-picture-bug.xlsm List of upcoming seminars: Excel Seminar Schedule