MrExcel Excel Videos

Go To Special, Formulas - but it is not "live" There is a new function… HASFORMULA? No! Great new feature in Office 365 to find a function by typing part of the name The function is ISFORMULA Select the range Home, Conditional Formatting, New Rule, Use a Formula The formula should point to the top left corner cell of your selection =ISFORMULA(B2)

Asad asks for a formula to calculate a progressive income tax rate. Set up a table in ascending order with the amounts from your income tax card You will do three Approximate Match VLOOKUPS to get the base tax, the percentage and the start of the level Subtract the Start Level from the income for the period. Multiply by the percentage. Used Ctrl+' to copy a formula down one cell without changing the references Using the F9 trick from Mike Girvin to embed the lookup tables in the formula.

There is an odd bug in Office 365 - when you insert a map chart, it won't recognize the "Democratic Republic of the Congo". You have to misspell it as "Democratic Republic of the Cong". In this temporary episode, a bizarre workaround to get the final o back into the cell.

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.