MrExcel Excel Videos

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

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