MrExcel Excel Videos

Kristin asks if you can add an icon set for Text cells? There are two workarounds in this episode. The first is for up to three icons. The outtake shows another way for five icons. Can you apply Conditional Formatting Icon Sets to Text? No But, if you have three (or less) ratings, you can use the trick in this episode. Convert your text ratings to 1, 0, and -1 Apply the Icon set Use a custom number format of "positive";"negative";"zero" This will get words to appear instead of numbers

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