MrExcel Excel Videos

Summarizing Data in Excel Using Subtotals This is the first of a five-part series on Summarizing Data This week, I will cover Subtotals, Remove Duplicates, Advanced Filter, Consolidate, & Pivot Tables How to summarize with Advanced Filter 1. Copy the headings to an Output Range 2. Select the data in the customer column. 3. Data, Filter, Advanced. Copy to another Location. Specify J1 as the output. Choose Unique Items Only. OK. 4. Select K2:M17. Type a SUMIF formula. Ctrl+Enter

Summarizing Data in Excel Using Subtotals This is the first of a five-part series on Summarizing Data This week, I will cover Subtotals, Remove Duplicates, Advanced Filter, Consolidate, & Pivot Tables How to summarize with Remove Duplicates 1. Copy the data to a new location 2. Data, Remove Duplicates. Click UnSelect All. Click Customer. OK. OK. 3. Click in the first number. Ctrl+Shift+Down+Right. Enter a SUMIFS and Ctrl+Enter.

Summarizing Data in Excel Using Subtotals This is the first of a five-part series on Summarizing Data This week, I will cover Subtotals, Remove Duplicates, Advanced Filter, Consolidate, & Pivot Tables How to summarize with subtotals: 1. Sort the data by customer 2. Data, Subtotals. At each change in customer, choose four columns. OK. 3. Click #2 Group and outline button 4. From Customer heading, Ctrl+Shift+Down+Right 5. Alt+; to select visible cells. Ctrl+C to copy. Ctrl+N for New. Ctrl+V to paste

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