Learn Excel from Bill Jelen with this daily 2 minute video podcast.
Paul sends in a few tricks to help deal with the Function Tooltip that appears when you edit a cell. Episode 960 shows you how.
Labels: formula
posted by Bill at 9:00 AM
Someone used Alt+Enter to enter three fields in hundreds of cells. How can this be broken into separate columns. In Episode 959, I discuss MID, CODE, CHAR, and SUBSTITUTE functions to solve the problem.
Labels: formula
posted by Bill at 9:00 AM
Dave from San Francisco suggests you should use Excel without the mouse and challenges you to convert a formula to values in three keystrokes. Episode 958 shows various attempts to solve the problem without using the mouse.
Labels: keyboard shortcuts, OnKey, VBA
posted by Bill at 9:00 AM
Episode 954 brought an avalanche of mail on three fronts. (1) SendKeys is really cool, how do you do Alt instead of Ctrl? (2) Why the macro when you could select Entire Workbook in the dialog? (3) Application.Dialogs would have worked, the dialog is misnamed. Episode 957 discusses all of these tips.
posted by Bill at 9:00 AM
Tamir asks if you can add labels between columns in a chart. Episode 956 shows a tricky way to solve this.
Labels: chart
posted by Bill at 9:00 AM
Andy wonders why the COUNT function doesn't count text entries. Episode 955 will show you how to overcome this with COUNTA.
posted by Bill at 9:00 AM
Laura asks how to display the Find dialog in a macro. The macro recorder won't do it. Application.Dialogs().Show won't do it. Episode 954 shows you a kludge to solve this problem.
posted by Bill at 9:00 AM
A question from Gary: Why does Excel 2007 open with only 65,536 rows? Wouldn't Microsoft want to show off the cool functionality available in Excel 2007 without forcing you to up convert each worksheet? Episode 953 discusses the unfortunate setting causing this problem.
Labels: Excel 2007, Settings
posted by Bill at 9:00 AM
Laura from Wisconsin provides today's cool Excel trick: Double click any edge of the active cell and Excel will jump to the edge of the data. Episode 952 shows you how.
Labels: navigate, shortcut keys
posted by Bill at 9:00 AM
Jamie shows how to use EOMonth to find the first of next month and Melanie shows how to sort paragraphs in Word. Episode 951 shows you how.
Labels: EOMONTH, Microsoft Word, Sorting
posted by Bill at 9:00 AM
A big car company offered a boat load of money to put an annoying ad in this podcast for the next two months. Rather than sell out, a reminder that the podcast is sponsored by the MrExcel.com store. Then; a fast way to group age data using a pivot table. Episode #950 shows you how.
Labels: Grouping, Pivot Table
posted by Bill at 9:00 AM
Steve asks how to group records into age ranges. In Episode 949, I show how to use the range version of VLOOKUP to solve this problem.
posted by Bill at 9:00 AM
Create a bar chart from a pivot table. If you sort the pivot table into ascending sequence, the chart sorts into descending sequence. Episode 948 will show you the obscure solution to this problem.
Labels: axis, categories, chart, Sorting
posted by Bill at 9:00 AM
You get a CSV file from an International branch of your company and the dates are in M/D/Y when you use D/M/Y or vice versa. There is an easy solution. Episode 947 shows you how.
Labels: csv, dates, international
posted by Bill at 9:00 AM
Loh from Malaysia sends in a very hard question. How can you use COUNTIF to analyze only the visible rows from a filtered data set? I knew the solution would have to involve iterating through multiple SUBTOTAL functions, but Aladin from the MrExcel Message board provides the solution. Episode 946 shows you how.
posted by Bill at 9:00 AM
Rod from Australia sends in an excellent tip for today's episode. Rod receives a CSV file every day that needs to be transposed. Rod put together an elegant combination of INDEX, COLUMN, and ROW to solve this problem. Episode 945 shows you how.
Labels: INDEX
posted by Bill at 9:00 AM
In Episode 943, I took data from Word and pasted to Excel for sorting. Now, I need to "type" that data back into Word, using the proper Style for each paragraph. Episode 944 shows you an Excel macro to automate this process.
Labels: Microsoft Word, VBA
posted by Bill at 9:00 AM
Bill (me) asks... is there any way to sort paragraphs in Word? I still don't know the answer, but in Episode 943, I show you how to take the data to Excel, add a few new columns and successfully sort groups of records in Excel.
Labels: Excel, Microsoft Word, VBA
posted by Bill at 9:00 AM
Rod has a formula in column B that calculates the earliest month in a data set. He then wants formulas in C, D, E, and so on that show the first of the next month. Although you can right-click the fill handle to do this, Episode 942 will show you how to solve this using the DATE function.
posted by Bill at 9:00 AM
Sergio from Mexico asks how to count unique combinations of two fields. Episode 941 shows three methods for solving this problem.
Labels: COUNTIFS, formula, SUMPRODUCT
posted by Bill at 9:00 AM
