MrExcel Excel Videos

Seckin set up a worksheet for his co-workers with great conditional formatting. Unfortunately, the co-worker uses Ctrl+V instead of Paste Special Values, which destroys the conditional formatting and/or data validation. In this episode, record a simple macro that changes Ctrl+V to Paste Special Values.

Back to the Microsoft Ad: can you select a cell and not have the value appear in the formula bar? Yes. Also, a Microsoft PR guy claims the ad is *correct*, that they have not yet pressed Enter, hence the total hasn't updated. But you will see, frame-by-frame, that the chart updates as quickly as the AutoSum. Bottom line: It is a bad formula. Or Photoshop. Never do math with Photoshop! Links in the video: David Yanofsky http://qz.com/143541/microsoft-botched-the-excel-spreadsheet-in-its-surface-tablet-ad/#143541/microsoft-botched-the-excel-spreadsheet-in-its-surface-tablet-ad/ Brad Sams Alex Wilhelm at TechCrunch http://techcrunch.com/2013/11/11/microsoft-didnt-get-its-math-wrong-in-that-excel-billboard/ David Shaw Twitter...

Mark G has an AutoFilter outside the print range and wants to show what is selected in the AutoFilter inside the print range. Excel 2013 Table Slicers could do this, but would take up a lot of space. Episode 1827 shows the VBA Macro used to return items selected from the AutoFilter to cells in Excel.

Two similar questions: (1) Can I count how many rows contain certain text in any column? (2) Can you filter to only rows that have the selected text in any of 5 columns. Similar solutions, although the second requires a bit of VBA macro code.

An addendum to episode 1825 - several easier ways to add a line at the Mean of a bell curve: Drawing object, Error Bar, High-Low Line, Dropline.

Several comments on episode 1663 asked how to add a mean to the bell curve chart. This podcast shows how to create a 2x2 range to represent a single XY line and how to Paste Special that series to the chart. To watch the original video that shows how to set up the formulas for the bell curve, watch episode 1663:

Abhijeet took INDIRECT to the extreme - 1200 formulas, each looking at 5600 records and the recalc time is taking too long. Two ways to build those formulas without INDIRECT.

Good news: The new Surface ads feature Excel. Bad news: there is a spreadsheet error in the ad. Read the original blog post from David Yanofsky here: Microsoft botched the Excel spreadsheet in its Surface tablet ad This episode of the podcast looks at how the spreadsheet could be totaling incorrectly and teaches you why you should not use =B1+B2+B3+B4+B5. Also - how did they get the gaps in the pie chart?

David from Fort Myers has one worksheet for each expense category. The total row appears on a different row in each worksheet. On a Summary worksheet, he would like to grab the totals from each worksheet. In today's episode, how to use INDIRECT as the 2nd argument in VLOOKUP to solve the problem.