MrExcel Excel Videos

Rekha viewed episode 655 where I used Group Field in a pivot table to create a year-over-year report. But Rekha needs current month from last year, current month from this year, variance, then YTD through current month from last year, YTD this year, variance. Urgently. In Excel 2010. While this would be a perfect report for Power Pivot, Rekha hasn't come up the Power Pivot learning curve yet. And we need the report urgently. I am calling this an impossible pivot table because once you group daily dates to months and years, you are not allowed to add a calculated item inside the pivot table to show a variance. Many ideas flashed through my mind: Run the data through PowerPivot and used Named Sets. Make two pivot tables side by side...

You have a list of debuts and credits. For each amount, match it with the next matching negative amount. After doing all of the matches, find the items without a match. Bill uses VBA and Mike uses some clever math.

Catherine is building a risk assessment tool in Excel. She needs to add 200 checkboxes to a worksheet, but this is a daunting task to do manually. Today, episode 1871 shows a way to do checkboxes without using Controls. A little Wingdings and 8 lines of VBA are all you need.

Sunnee asks if there is a formula for identifying people who were tardy more than three times within an eight week period. Provided the data is sorted from earliest to latest, the formula in today's episode will solve the problem.

Trying to write an insane formula to solve another poorly formatted spreadsheet.

Enter a product on Sheet 1. You want a VLOOKUP formula to grab the description from a lookup table on Sheet 2. However, if the product is missing from Sheet 2, you want Excel to stop, ask for the correct description, and then add that to the Lookup table so future entries for this product will get the proper description. Today's episode uses a VBA Macro to achieve this result. The video is based on the question originally posted here: How to Create a dynamic Unique list that updates automatically You can copy the VBA code from that link so you do not have to type the code.

Every once in a while, you run into ugly data sets and this qualifies. Someone decided it would be brilliant to put the text category and the sales amount in a single cell such as "WXYZ123". Today's duel is how to get the total sales for each category. Mike and Bill go back and forth 4 times today, offering a solution with Text to Columns, a Pivot Table, Array Formulas, and a VBA UDF.

Mike asks how he can hide 100 lines of text from Word in a single Excel cell. When the cell is selected, the text will appear. This episode 1866 shows one way to do this using cell comments. There are likely other ways - please post how you would solve this in the YouTube comments.

Dale needs to upload listings to eBay. One of the tough fields is the Gallery field, where the names of several images need to be in a single cell separated by semi-colons (Bill1.jpg;Bill2.jpg;Bill3.jpg). Given two cells that contain the image prefix and the number of images, can an Excel formula generate this gallery list? It feels like there should be an array formula to do this, but I had to resort to a VBA user defined function to solve the problem.