FC2Video

A YouTube viewer asks if there is a way to add the current date to the right of any 'Yes' entries in a spreadsheet. Using a simple 'Event Handler' macro, Bill creates a means to answer the question.

Today in Episode #1473, Dueling Excel looks at Scores and Tie Breaking. In this example, When there is a tie, we want to award the Highest Rank to the Largest Weight. Quite simply, after ranking a column with ties, we want to break the tie.

Herbie has a Calendar spreadsheet being populated by a huge concatination formula. He asks if there is a way to combine Wrap Cells and Shrink to Fit as he works in the calendar. In Episode #1472 Bill shows us one way to accomplish this.

Choose random weeks from the Data Set, but do it without repeating a week. In a revisiting #1469, how will Bill assure that there are no repeats? Follow along with Episode #1471 today to learn the answer and solve the challenge.

Patrick has a weekly Expense Ledger Spreadsheet set up; he wants to create a Macro to copy the Totals from Report 1 to a corresponding Week Number Row on the Summary worksheet. This Macro has to use the FIND command in VBA to...

A YouTube viewer wants to find a way to generate random weeks over the next 6 months. In Episode #1469, Bill – using TODAY, WEEKDAY, and RANDBETWEEN Functions – shows us a Formula to calculate random weeks for the next 6 months or year.

Shaun has a beautiful spreadsheet to calculate staffing levels and expense.He needs to multiply shift length by the number of staff and the answer that he is getting is . . . immaterial to the task at hand.

Formulas are ‘changed’ and that the Alpha Columns of the worksheet have been changed to Numbers. What happened? “R1C1″ is the answer. Learn Excel 2010 from MrExcel!

Can you embed a PDF in a worksheet? Yes - Using the 'Insert Object' Menu Tab. Follow along with Bill, in Episode #1466, as he shows us how to embed a PDF in our Worksheet.

Gary points out that the TRIM Function in VBA isn't working like =TRIM() Function in the worksheet; the spaces in the middle remain after a Macro run. Bill shows us, In episode #1465, how to solve this issue with the 'Application.WorksheetFunction'.

Tony needs to keep the last duplicate record and throw out the earlier ones. In Episode #1464 Mike “ExcelisFun” Girvin and Bill “MrExcel” Jelen demonstrate competing methods to get this issue solved. Duel #90 - Learn Excel 2010 from MrExcel!

Shrikant needs to join the left 26 characters of column A with column B. But...even if A2 only has 10 characters, he needs it to fill with spaces each and every time. In Episode #1463, Bill shows us how to maintain this positioning of data.

Stefan from Sweden checks in with a much better way to do a word count of all the sentences [referring to the "Word Count from Sentences" Podcast, Episode #1460]. How? Using… a tiny little Add-In that most Excel Users already have! Learn Excel 2010!

1. Why can't we do a Pivot Table Template? 2. Why can't we turn off the annoying Compact Layout? 3. Why do I keep asking these questions when it is so easy to override them with a tiny add-in? MrExcel Labs is coming out with a tiny add-in to...

Today on Learn Excel from MrExcel, Zac has 5000 phrases in column A. He wants to figure out which words are most popular in those phrases. With Episode #1460, Bill shows us a number of steps that will solve the problem in about six minutes.

“Can you get the pivot table grand total column to appear on the left side of the pivot table?” In Episode #1459, Mike “ExcelisFun” Girvin and Bill “MrExcel” Jelen come up with differing strategies for solving this problem.

Jil used to be able to edit a Formula and use the Wheel Mouse to scroll the Spreadsheet while editing the formula. This apparently doesn’t work any more, starting in Excel 2007. In Episode #1458, Bill shows us a workaround.

Bob Umlas’ new book just arrived from the printer – “Excel Outside the Box: Unbelievable Excel Techniques”. Bill takes a look at the new book, tells you where you can buy an advanced copy, and shows you a trick from the book.

Two Pivot Table questions from my Power Excel Seminar in Detroit, MI. First, when I drill down on a pivot table in Excel 2007, I'm getting the Table Format, not just a Range. How can I get the Range? Second, when I convert a Pivot Table to Values...

Frank asks if there is a way to rewrite a formula using a scrollbar. While we can't do it with a scrollbar, I do have a way to rewrite formula with a dragging technique. Following along with Bill in Episode #1455 to see how it's done.

Today, Bill “MrExcel” Jelen and Mike “ExcelisFun” Girvin look at a question that asks: “How can I audit 70% of the book value, with samples based on the value?” Watch the Duel, learn the method, get the result with Episode #1454. Learn Excel!

Kevin wants to clear out data for yesterday’s shift worksheet any time someone opens the Workbook. Using the formula from Episode #1452, plus four tiny macros added he has added, Bill shows us the solution today in Episode #1453. "Learn Excel 2010".

“Did a new shift start between Time 1 and Time 2? It sounds like an easy formula, and it probably is, but I sure had a tough time with this formula out. Do you have a solution?” In Episode #1452, MrExcel looks at working with time and day changes.

Joe has Excel 2011 for Mac. He has found that the Excel Mac Macro Recorder uses ‘xlAutomatic’ for all colors instead of the actual color. In my first-ever podcast focusing on Excel for Mac, I will show you how to use the ‘RGB Function’ to fix the VBA

Today, in Episode #1450, Christopher and Sheila both wrote in with different problems that required the same solution. Both happened to have numbers or dates stored as text. The Alt+DEF shortcut came worked - And Bill shows us how!

Today, in Episode #1449, Mike “ExcelisFun” Girvin and Bill “MrExcel” Jelen address different methods to answer the question: “Is there a way that the Grand Totals and Subtotals would omit the Negative Subtotals?” "Learn Excel 2007 through Excel 2010"

Today, in Episode #1448, Bill re-addresses a few past podcasts with alternate solutions. Follow along, make note and improve your effectiveness with Microsoft Excel! "Learn Excel 2007 through Excel 2010 from MrExcel" by Bill Jelen. Visit MrExcel.com

Want to see immediate results? Set an area to the bottom and right of a massive spreadsheet where you can add totals and see them at a glance. In Episode #1447 Bill shows us how to do this with only a few keystrokes.

In today’s Dueling Excel Podcast, with Mike “ExcelisFun” Girvin and Bill “MrExcel” Jelen, Mike and Bill look at methods to calculate the MODE and MEDIAN from data that is arranged in a Frequency Table. Episode #1446 shows us how its done.

In a spreadsheet that keeps track of expenses, we want to mark the cell in one color if the expenses are within 25% of the budget and in another color if they are within 10% of the budget. Today, Bill shows us how to do this. Learn Excel Podcasts!

Is it possible to flag a cell if the time in the cell is more than an hour late? Today, in Episode #1444, Bill shows us a Conditional Formatting Formula to answer this question. "Learn Excel 2007 through Excel 2010 from MrExcel" by Bill Jelen.

Jack asked about centering titles above a report. I suggested “Center Across Selection”, and Jack’s coworker suggested “Merge and Center”. While it is a lot easier to find Merge and Center, there are reasons why you don’t want to use this to method.

I want to Hide some Settings right in plain view on the spreadsheet, but the person using the spreadsheet keeps inadvertently deleting those settings! In Episode #1442, Bill shows us one solution to keeping those settings from being deleted.

Amal wants to highlight an entire record in red when the date falls within two weeks and then, leave the ‘Done’ column not marked as True. In Episode #1441, Bill shows us the tricky Conditional Formatting method to get the results we want.

“Last week, I needed one Chart to use the colors from one Theme and a second Chart to use the colors from another Theme. The Chart Fill Dropdown wasn’t helping, but I found a workaround. In Episode #1440, Bill gives us the details.

Mike's son Dennis has a baseball problem from his algebra class. Bill "MrExcel" Jelen and Mike "ExcelisFun" Girvin differ over whether the problem actually requires Algebra, Goal Seek, or simply a visit with Bob Barker.

“I was in Milwaukee this week at the IIA Regional Conference when Heidi asked me about calculating a loan payment for a loan, with variable interest rates.” In Episode #1438, Bill shows us how to use Goal Seek to back into this answer.

"My friend Lyn opened his spreadsheet and he can't see anything! What a mystery. Things show up in the formula bar, but nothing's visible in the Excel window. Is the Worksheet hidden? No. Columns Hidden? No. Bill solves this mystery in Episode #1437.

Do you need to format your Excel Data Set -previously prepared as a printed report- so that it is capable of being imported into Microsoft Access or an other Database? Today, in Episode #1436, Bill shows us how to accomplish this. Learn Excel!

Viabahv asks how to replace the grand total column in a Pivot Table with a Delta Column. Bill re-introduces us to the oft hated Calulated Item feature to solve this problem today, in Episode #1435. "Learn Excel 2007 through Excel 2010" by Bill Jelen!

In today’s Dueling Podcast, Episode #1434, Mike “ExcelisFun” Girvin and Bill “MrExcel” Jelen are asked to work with a Spreadsheet with data that summarizes a list of call times… how many calls occurred in each quarter hour?

John is looking for some VBA to find and remove duplicate records in a database of 169,000 records. Today, in Episode #1433, Bill shows us how its done.

Excel 2010 introduces the ability to put your worksheets on the web and introduced PowerPivot. You could never get your PowerPivot worksheets on the web, but Rob Collie the HostedPowerPivot.com team have solved the issue. Win a PowerPivot t-shirt!

Today, Denver asks how to convert a number in Excel to words such as “three thousand four hundred sixty five dollars and thirty two cents”. In Episode #1431, Bill shows us a cool knowledge base article with the answer to solve this issue.

A write-in to Bill today to ask about methods to create a Waterfall Chart in Excel, without the aid of an Excel Add-in. In Episode #1430, Bill shows us how to accomplish the task. “The Learn Excel from MrExcel Podcast Series” Learn Excel 2010!

Dave from Boca Fire sends in today's tip: using a Formula to create a Rolling Data Validation. The Data Validation Dropdown offers 'Now', '15 Minutes from Now', '15 Minutes Ago' and so on. Follow along in Episode #1428 to learn how its done!

Today, in Episode #1427, Bill shares an interesting way to find totals in Excel ... the Read Aloud Method! This episode is another in our series for Don't Fear the Spreadsheet. Send in your best Excel Newbie stories! Learn Excel from MrExcel!

Today we will learn how to replace Numeric Chart Labels with a Formula using the method Bill gives in Episode #1426. Also learn to vary Colors by Point AND "Making Excel Columns Wider" using Gap Width. Learn Excel 2007 through Excel 2010 from MrExcel

Today, in Episode #1425, Bill takes another look at Chart Titles, how to make them Dynamic and using the TODAY() and TEXT() Functions in order to add a date to a Dynamic Chart Title. "Learn Excel 2007 through Excel 2010 from MrExcel" - by Bill Jelen.

Today, a viewer from YouTube asks how to make sure that a price entered is less than the upper limit in a lookup table. Mike and Bill compare two different ways, one using Data Validation and one using Conditional Formatting. Learn Excel from MrExcel