MrExcel Excel Videos

Rick asks how to get started with VBA. In Episode 873, I show a horribly formatted file that someone in my Power Excel audience has to deal with daily. A macro seems like a good way to deal with the file. I will show you how to turn on the macro recorder, turn on relative reference, and record your first macro.

Janet wonders why her VLOOKUPs are not working in certain cases. If your key field contains a mix of numbers and text that looks like numbers, then the VLOOKUP won't work. Episode 872 shows how to use the TEXT function to intermittently solve the problem and another method to reliably solve the problem.

Someone tried to record a macro to find the records from a certain date. Since finding dates requires the date format to match exactly, a better way to go would be using Advanced Filter. In Episode 871, I show how to use AdvancedFilter in a macro to extract records from a certain date.

You could solve yesterday's podcast using the often-cursed-at, but seldom-understood GETPIVOTDATA function. In Episode 870, how to use GETPIVOTDATA to return the grand total and also using =IF(ISBLANK to clean up stray results below the pivot table.

Richard is building a formula outside of the pivot table that needs to divide by the grand total of the pivot table. In Episode 869, I show two solutions, one of which is an incredibly geeky use of the other type of VLOOKUP.

Donna from Colorado asks how to clear all of the input cells from a model. In today's Where Is It Wednesday Episode 868, we take a look at how to select only the numeric constants. Also - following up on 858 - Copy as Picture, 860 Resizing Header Logo, 863 Protect Sheet.

If you need to make an exact copy of formulas, you can use the method from episode 866 or this Fill Across Worksheets solution. Episode 867 shows you how.

Markus from Germany sends in this tip. Say that you have to make an exact copy of a range of formulas. It is like you need to do a CUT and PASTE, but also leave the original cells where they were. Normally, a Copy and Paste will rewrite the formulas. Using this trick from Markus, you can achieve the desired result. Episode 866 shows you how.

Taylor from Orange County California calls in to ask how you can get the formatting of a pivot table to stick? Every time that you refresh a pivot table, the formatting reverts back to a default. The bad news is that you can not make all formatting stick, but Episode 865 will show you a couple of methods to use.