MrExcel Excel Videos

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

You want VBA to write several copies of the current Excel workbook. SaveAs causes problems, because the original workbook is no longer open Instead use .SaveAsCopy to save a copy of the workbook As far as I can tell, SaveAsCopy does not exist in the Excel interface. It allows you to keep the current workbook open with the original name and path, but save extra copies with a new name. This is particularly handy if you need to remove macros from a workbook while saving. Download the code from the video at the link below. To download this workbook: https://www.mrexcel.com/download-center/2018/06/save-as-keeping-original-open.xlsm List of upcoming seminars: Excel Seminar Schedule

Sam in Nashville is filtering a pivot table If you use Date Filters or Label Filters, Excel remembers the setting and will re-apply the filter after a Refresh But… if you use the Search box to filter, Excel will not re-apply the search at a Refresh Instead, use the Label Filter for "Contains" To download this workbook: https://www.mrexcel.com/download-center/2018/05/pivot-table-search-bug.xlsx List of upcoming seminars: Excel Seminar Schedule

Why does our employee May keep sorting to the top of the pivot table? By default, pivot tables will follow the sort pattern of any custom lists. If you employ people named June or May or Wednesday, they will sort to the top of the list. File, Options, Advanced. Scroll all the way down. Edit Custom Lists. The names in the first four series can not be removed. I used to joke that one solution is to fire all the people named April or June. Right-click the pivot table and choose Options. Go to the Totals & Filters tab. Uncheck Use Custom Lists when sorting. Download the file to work along: https://www.mrexcel.com/download-center/2018/05/pivot-table-no-custom-sort.xlsx Read the article: Pivot Table No Custom Sort

A company wants to bill in quarter hours and they always want to round up to the next quarter hour. 1:42 There are 24 hours in a day. There are 24x4 or 96 quarter hours in a day. Thus, 15 minutes is equal to 1/96 2:07 Use CEILING(A2,1/96) to round up to the next higher quarter hour. 2:45 There are 24 hours in a day. There are 24x12 or 288 five-minute periods in a day. Use 1/288 to round to nearest five minutes 3:02 To round to the nearest six minutes, use 1/240 3:10 Use 1/1440 to round to nearest minute 3:33 Use FLOOR(A2,1/96) to round to the lower quarter hour. 3:47 Use MROUND(A2,1/96) to round to the nearest quarter hour. 5:26 Why a column of times do not sum correctly 6:28 Bonus tip: Which number format? 13:30 or 13:30:55 or 37:30:55...

Question from the Gulf South Council of the IMA seminar in Atlanta Hey! I have data formatted with every other row in Grey When I insert two rows, both rows are grey. I have a great solution with conditional formatting, but it requires 3rd grade math Divide the row number by 2. What is the remainder? It is going to be 0 or 1. Set up conditional formatting to check to see if =MOD(ROW(),2)=1. If it is, fill that row. It works awesomely. Feel free to download the workbook and set up a MOD function to highlight 3 rows in orange and one row in Teal It is all in the MOD function. To download this workbook: https://www.mrexcel.com/download-center/2018/05/inserting-alternating-rows.xlsx