MrExcel Excel Videos

Learn Excel from MrExcel - Excel tips and tricks from Bill Jelen.
Learn Excel - Magnifier in Excel - Podcast2224
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
Learn Excel - Text Instead of Numbers in Pivot Table - Podcast 2223
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
Learn Excel - Commas Stop Working in Formulas - Podcast 2222
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...
Learn Excel - Fill Merged Cells Down - Podcast 2221
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
Learn Excel - Power Map 3D Map 2 Data Sets - Podcast 2218
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
Learn Excel - Place People on Bell Curve - Podcast 2217
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...
Learn Excel - Combine Workbooks With Common Column - Podcast 2216
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...

Forum statistics

Threads
1,226,693
Messages
6,192,464
Members
453,725
Latest member
cvsdatreas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top