MrExcel Excel Videos

Upcoming seminars: Excel Seminar Schedule Today's question from Knoxville: Can Excel fill only the weekend dates for use in planning a co-parenting schedule for the year? Right-click the fill handle and drag to Fill Weekdays But there is no similar option to fill weekends. This could be useful for planning co-parenting schedules Use a secret form of WorkDay.Intl 7-digit binary string specifies which days should appear on the schedule. Left to right, the digits represent Monday through Sunday 1 means don't count this day. 0 means do include this day (backwards!?)

Buy the book: Power Excel With MrExcel - 2017 Edition Book Ken is creating some rainfall statistics. Which months had the most or least rainfall or rain days? This episode shows how to use a pivot table in Excel to create the reports. Pivot tables created in 2013 can't be refreshed in 2007 You need to create the pivot table in 2007 to allow it to be refreshable Goal is to find the five months with the least rainfall Create a large pivot table with rainfall by month Sort by rainfall ascending Change to Tabular Form Use the Value Filters, Top 10, to get the bottom 5! Remove the Grand Total row Note that a tie might cause this report to give you 6 or more rows Once you have the first pivot table, copy it into place and create next pivot...

It is easy to quantify survey data when it is multiple choice You can use a pivot table to figure out what percentage each answer has But what about free-form text answers? These are hard to process if you have hundreds or thousands of them. Sentiment Analysis is a machine-based method for predicting if an answer is positive or negative. Microsoft offers a tool that does Sentiment Analysis in Excel - Azure Machine Learning. Traditional sentiment analysis requires a human to analyze and categorize 5% of the statements. Traditional sentiment analysis is not flexible - you will rebuild the dictionary for each industry. Excel uses MPQA Subjectivity Lexicon (read about that at http://bit. ly/1SRNevt) This generic dictionary includes 5,097...

Shop at: Holiday Gifts Thank you for supporting MrExcel during my 18 years in business. Today, three very unique limited edition products and a tip on using INDEX/MATCH instead of VLOOKUP.

The November 2016 update of Office 365 includes map charts. November 2016 brings a new Map Chart feature to Office 365 This feature is marketed as Office 365 Exclusive Different than Power Map - because it can be embedded in your worksheet Only a shape map! Not a point map… Data can be plotted by Country, State, County, Zip Code In other countries, use "Regions" instead of "Region" How to change the color for value charts For Category charts, edit the legend entries to change the color. How to change the label How to change the projection Value maps versus Category Maps Like the other new Ivy Charts - can not have the chart title come from a cell Can not base a map directly on a pivot table - use VLOOKUP TEXTJOIN function

Track changes in Excel is a little bizarre. Goal is to track what formula cells in Excel change. Save As to save workbook as XLSM. Change Macro Security. Record a macro to figure out the code to set up conditional formatting for numbers not equal to 2. Choose the Formatting that you want. Record another macro to learn how to remove CF from worksheet. In the macro, add a loop for each worksheet. Add an IF statement to prevent it from running on Title. Add a loop to check each formula cell. Add Conditional Formatting to see if cell is not equal to the value at time macro runs. Go back to Excel. Add a shape. Assign the macro to the shape. Click the Shape to Run the Macro. Bonus Tip: Dragging a VBA module to a new workbook.

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