Learn Excel from Bill Jelen with this daily 2 minute video podcast.
Mike Girvin wonders why Joe from Episode 977 doesn't just sort the data so that all the filtered records are together? You can do this if you use an Original Sequence column so that you can sort back. Episode 982 shows you how.
Labels: Sorting
posted by Bill at 9:00 AM
Gary sends in a clever solution for Episode 977 which uses the Skip Blanks option of the Paste Special dialog. Watch for how Gary can "reverse" the filtered and hidden rows during the steps shown in Episode 981.
Labels: Filter, Paste Special
posted by Bill at 9:00 AM
Mike Girvin and Bill Jelen meet up again to offer three solutions to the problem of counting how many records match a wildcard criteria. Episode 980 shows you how.
Labels: Duel
posted by Bill at 9:00 AM
You need to chart two different orders of magnitude in a chart. I always end up creating a 2-axis combo chart, with a column series and a line series. Today in Episode 979, Bill writes in with a cool way to solve this problem with both series shown as columns.
Labels: charting
posted by Bill at 9:00 AM
Cullen sends in today's Where Is It Wednesday question: how can you change the comment shape in Excel 2007? This was a cool feature in Excel 2003, we just can't find it anymore. Episode 978 shows you how.
posted by Bill at 9:00 AM
Joe asks if there is any way to do a Filtered Paste operation. I can't figure it out, so I had to resort to a macro. Check out Episode 977 and see if you have a better way.
Labels: accounting, business, Excel, Filter, MrExcel, spreadsheets, technology, tutorial
posted by Bill at 9:00 AM
Laura from Wisconsin asks about changing the default Excel comment from light yellow to another color. While you can format an individual comment in Excel, the solution to this problem is in the control panel. Episode 976 shows you how.
Labels: comments
posted by Bill at 9:00 AM
Awesome. There is a video on the 'net with Microsoft's Donald Farmer showing off a new add-in called Gemini. This is an amazing product. In Episode 975, I point out where you can get a sneak peak at Excel 2010 pivot tables in the Donald Farmer video.
Labels: Pivot Table
posted by Bill at 9:00 AM
You may have seen how to create a spearing or 3-D formula such as =SUM(Jan:Dec!B3). In Episode 974, an amazing way to create a 3D reference to non-contiguous sheets.
posted by Bill at 9:00 AM
A question from YouTube asks how to write a macro to cut selected rows to Sheet2, adding a completion date. Episode 973 shows you how to write this short macro.
Labels: VBA
posted by Bill at 9:00 AM
The data table option in a chart can have some annoyances. Today, a question (and a solution) from the Virginia Tech seminar. Episode 972 shows yet another cool use for the camera tool.
posted by Bill at 9:00 AM
Jennifer is relatively new to Excel and asks about using the Insert Name dialog. Any time that you are in the RefEdit field and hit a left or right arrow key to move within the formula, Excel starts inserting cell addresses. Episode 971 shows you how to turn off that annoying behavior.
Labels: Names
posted by Bill at 9:00 AM
Michael from Shanghai asks about missing data in a pivot table. If some customers have data from every month and other customers have data from only a few months, the pivot table will change shape as you select new customers from the filter. Episode 970 will show you the hidden setting to prevent this problem.
Labels: Pivot Table
posted by Bill at 9:00 AM
There is a new podcast featuring my Excel tips. Learn about this podcast and also see how to repeat your title rows at the top of each printed page. Episode 969 shows you how.
Labels: Excel 2007, printing
posted by Bill at 9:00 AM
Smack83 sends in today's question via YouTube. How do you freeze specific rows and columns in Excel and have them appear without retyping. Episode 968 shows you how to you Freeze Panes to keep the headings visible on the screen.
Labels: Excel 2007, Freeze Panes
posted by Bill at 9:00 AM
Bob Umlas from the Excel Magic book passed on another amazing tip to me at the Microsoft Summit. If you need to add a total to data that was subtotaled using SUM formulas, you can do it in 1 double-click. Episode 967 shows you how.
Labels: shortcuts
posted by Bill at 9:00 AM
Another dual podcast as Bill Jelen and Mike Gel Girvin show you multiple ways to reduce a range by 35%. Shot in Seattle, Washington. Episode 966 shows you how.
Labels: formulas, Paste Special
posted by Bill at 9:00 AM
It is a game of Dueling Excel as Bill Jelen and Mike Gel Girvin bring you their own ways for doing two-way VLOOKUPS. Episode 965 shows you how.
Labels: formulas
posted by Bill at 9:00 AM
I bet you thought that I screwed up and put the date of the podcast as the title! But no... that really is the title. Tony asks how to change the long date format to show Thu Mar 5, 2009. Episode 964 shows you how.
posted by Bill at 9:00 AM
The drawing toolbar offers a circle, but no semi-circle. Or does it? Learn how to use the yellow inflection handles to make other shapes in Episode 963.
Labels: drawing toolbar
posted by Bill at 9:00 AM
One of the requests from last Friday's podcast was the ability to sort columns like you can sort rows. It's in there! In Episode 962, I will show you how to sort left to right.
Labels: Sorting
posted by Bill at 9:00 AM
Jason has five pivot tables based on the same data. All five have a page field holding the month. After changing the month on the first pivot table, he would like the other four pivot tables to also change. Episode 961 shows Mike Alexander's trick for solving this problem.
Labels: Pivot Table
posted by Bill at 9:00 AM
