MrExcel Excel Videos

The Advanced Filter is more "advanced" than regular filter because: 1 It can copy to a new range 2 You can build more complex criteria such as Field 1 is A or Field 2 is A 3 It is fast Mort is trying to process 100K rows in VBA by looping through records or using an array It will always be faster to use built-in Excel features than writing your own code. You need an Input range, and then a Criteria Range and/or an Output Range For the input range: single row of headings above the data Add a temporary row for headings For the output range: a row of headings for the columns you want to extract For the criteria range: headings in row 1, values starting in row 2 Complication: Older versions of Excel would not allow the output range to be on...

Goal: Print category running total and % of Category at bottom of each printed page Problem: nothing in the Excel user interface can let a formula know you are at the bottom of a printed page Yes, you can "see" the page breaks, but formulas can not see them Possible solution: Use a macro Strategy: Add the running total and % of category for each row. Hide on all rows. Running Total for Category Formula: =IF(A6=A5,SUM(F6,G5),SUM(F6)) % of Category Formula: =G6/SUMIF($A$6:$A$2844,A6,$F$6:$F$2844) If your workbook is saved as XLSX, do a Save As to save as XLSM If you've never used macros, change macro security If you've never used macros, show Developer tab Switch to VBA Insert a module Type the code Assign that macro to a shape As the...

Ever since I saw Nate "Moneyball" Silver present at a Microsoft conference, I've been a junkie for his election statistics at FiveThirtyEight. But Nate has baseball predictions, and as a long-suffering Cleveland Indians fan, I live and die by Nate's predictions for Cleveland winning the world series. But his model swings wildly after each game, first saying the Cubs would win, then the Tribe, then the Cubs, and now the Tribe. How can this be? Today, I use an Excel spreadsheet to play the World Series 150,000 times and come incredibly close to Nate Silver's conclusion. Thanks to Excel MVP Jon Peltier for his awesome chart utilities at: Peltier Tech Charts for Excel 3.0

Write one query to load one web page. Then, turn that query into a function and use Power Query to load many web pages. Today's trick is adapted from the M is for Data Monkey book Build a query to get data from one web page Edit the query to change it into a function with VariableName before Let Change the hard-coded URL to VariableName Rename the query to fxWeather Close & Load. The data will disappear. Use Excel Trickery to create a table of all URL's Create a query from that table. Add a new column of Weather = fxWeather Expand the column. Uncheck Prefix Amazing!

More than you ever wanted to know about Shapes in Excel! Today's trick from Sam Radakovitz on the Excel team Shapes in Excel can be formatted with special formats Rotate Change inflection points Power Point offers a few tricks that Excel does not offer You can merge shapes in Power Point You can subtract shapes in Power Point You can use the eye dropper to color shapes

Today's trick is from Katie Sullivan on the Word team There are a few cases where Microsoft Word can do things better than Excel It is possible in Excel to format part of a cell by selecting a word in Edit mode and applying formatting. But, if you use Find & Replace and anything is changed, that formatting is wiped out. Copy from Excel. Paste to Word. Do the Replace there. Copy from Word and Paste Back to Excel. The formatting stays.

You want to get data from a table in a PDF file to Excel. Exporting Excel data as a PDF Today's trick is from Katie Sullivan on the Word team There are a few cases where Microsoft Word can do things better than Excel One of those is pasting data from PDF files Paste to Word Copy the data from Word Paste to Excel In the recap, a way in Acrobat to Copy with Formatting and it will paste half-correctly to Excel

Today's trick is from Katie Sullivan on the Word team There are a few cases where Microsoft Word can do things better than Excel One of those is adding bullets Sure - Excel can add a bullet character, using Alt+0149 This would be tedious to manually type this before each line Or, you can add a custom number format of • @ But for a wide variety of bullets, copy the data to Word, apply the bullet, and then copy back to Excel. You might use the Excel Reduce Indent button a few times

Today's trick is from Katie Sullivan on the Word team There are a few cases where Microsoft Word can do things better than Excel One of those is changing case. If you needed to convert all of this text to Upper case, you could add a helper column and use UPPER, plus then Copy & Paste Values. The video shows how to right-drag a selection and choose Copy Here as Values Only But, you can also round-trip the data to Word In Word, there is a choice to convert text to UPPER, lower, Sentence, tOGGLE cASE, or Proper Case