MrExcel Excel Videos

If you know someone who uses Photoshop, Illustrator or InDesign, learn more about the conference at CreativeProWeek.com. In today's episode: I want to check the quality of an index that I created in InDesign. Were there any sections where I was not as attentive as I needed to be? Copy the Index from InDesign and paste in Excel. Use Flash Fill to separate the page numbers. Insert, Pivot Table, OK. Put Page in Rows area and Heading in Values area (this seems backwards, but in this situation, it is correct) Select the first page number. On the Analyze tab, Group Field. Group into 10 unit buckets. Select all numbers in the pivot table except the Grand Total. Home, Conditional Formatting, Data Bars

Today's question is how to use VBA to sort by color. The easiest way to do this is to turn on the macro recorder, do the sort, turn off the macro recorder, and view the code. View, Macros, Record New Macro. After you are done, click Stop Recording (lower left near Ready) Alt+F8 to see a list of macros. Select your macro and click Edit When you see C2:C99, change to C2 When they specify the range to sort, use Range("A1").CurrentRegion

This is a first look at the preview of the new Ideas feature in Excel. Insights is a new Artificial Intelligence feature coming to Office 365 in late 2018 It is slowly rolling out in preview to Office Insiders Select your data. Go to Home, Ideas. Excel will analyze your data and attempt to find trends or interesting things in your data If you agree it is interesting, you can add a chart. Troubleshooting: pay attention to data types Caveat: by the time this hits general release, it might look different.

Ron asks: How can you sum all VLOOKUPs Many people are familiar with VLOOKUP(B4,Table,2,True) If you are doing the True version of VLOOKUP, you can also do LOOKUP Lookup is odd because it returns the last column in the table. You don't specify a column number. You don't specify True/False as the fourth argument like VLOOKUP. Lookup has a special trick: You can lookup all of the values at once and it will sum them. Remember to press Ctrl+Shift+Enter after typing =SUM(LOOKUP(B4:B17,E4:E8)) LOOKUP can also do the equivalent of HLOOKUP if it senses the table is wider than tall

Excel Recovery Link: http://mrx.cl/2rkppvk How can you recover data from a corrupt Excel Workbook? #1 best way: Open the workbook in a newer version of Excel 2. If you can open the file, copy and Paste to a new workbook Save the new workbook before you try to close the old one. The method shown in this video: Power Query! Power Query is a free add-in from Microsoft for Excel 2010 and Excel 2013 It is built in to the Get & Transform group on the Data tab in Excel 2016+ Get Data, From File, From Excel Workbook Browse to your file Choose one worksheet Click Load The data comes in as a table. Copy & Paste Special Values to a new workbook. This method won't preserve your formulas or formatting, but it will get the values back. Step #4...

Selina: Can you hyperlink from Word to a specific sheet and cell in Excel? Yes! If you are careful with the hyperlink format. One way: C:\FolderName\FileName.xlsx#'Sheet1'!R99 or, you can use a named range: C:\FolderName\FileName.xlsx#JumpHere From the outtake: To prevent the annoying message, you can tell Word that the folder containing the Excel file is a trusted location. You have to exit Word and re-start for the message to go away. Title Card Photo Credit: geralt / pixabay

Bill and Mike take a look at how to do a reverse lookup in Episode #1000. This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Manfred from Germany has an interesting problem. Each record takes up six rows in his spreadsheet. Formulas need to point to the first row of the company. Those formulas need to be copyable. But those formulas are in conditonal formatting rules. Episode 1075 tries to solve this problem. This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Do a picture lookup in Excel. Today's dueling Excel podcast shows how to do a picture lookup in Excel.