Episode 644 - Previous Banking Day

A great question from one of my Excel seminars started out as a simple question: how can I convert a column of dates to show if the day is Monday, Tuesday, etc.? However, upon further examination, they were trying to figure out the banking day before the date shown. This could have been an ugly combination of IF functions to locate Mondays and Bank Holidays. However, one obscure function solves this problem in a short formula. Episode 644 shows you how.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Episode 643 - Excluding Zero Values From Pie Charts

Patrick wrote in to address my answer from podcast 641. In that podcast, I used a SUMIF to create a pie chart of hours spent by department. Patrick notes that early in the month, many of the departments have 0 values and would like those excluded from the chart. In Episode 643, I set up a series of formulas to sort the data and then a named range to exclude the zeroes from the chart.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Episode 642 - Pulling Web Data Into Excel

Maxwell asks how best to pull web data into Excel. He notes that data pasted from a webpage often ends up only in column A. Episode 642 will show you how to solve this problem using a web query.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Episode 641 - Conditionally Summing

Patrick asks how he can summarize his monthly time sheet to create a pie chart by department. Using the SUMIF function provides the step to make this relatively easy. Episode 641 shows you how.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Episode 640 - Filtered Greenbar

Rod from Cincinnati notes that the trick used in Podcast 470 to apply greenbar formatting fails when you use the AutoFilter to hide certain rows. There is an interesting workaround. Episode 640 shows you how.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Episode 639 - Recent Records

How can you find the most recent record for each customer? The method I proposed in Episode 614 is not the fastest way. Richard from Jaguar Cars Ltd sends in a faster method. Episode 639 shows you how. The podcast will take off a couple of days for the U.S. Thanksgiving holiday and will resume on Monday.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Episode 638 - AutoFilter Navigation

Joe Marten sends in today’s tip; how to quickly navigate through the AutoFilter list when there are far too many entries in the list. Episode 638 shows you how.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Episode 637 - Validation Unique

Ben asks today’s question: Can I set up Validation Dropdowns where the list of items requires a unique entry? For example, once I choose ABC in a column, it should no longer be offered in that column. Episode 637 shows you how.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Episode 636 - Combining Worksheets

James asks how he can combine data from all rows of all worksheets into a single worksheet. 11 Lines of VBA code will do the trick. Episode 636 shows you how.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Episode 635 - Four Conditions

Today’s question deals with conditional formatting. How can you have four rules? How can you have the color of column A be based on values in Column D? Episode 635 answers all.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Episode 634 - Dragging Chart Points

Did you know that you can change numbers in the underlying worksheet by dragging points on a chart? This might be an interesting trick to allow someone to adjust their forecast projections. Episode 634 shows you how.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Episode 633 - Keyboard Shortcuts

Vincent from Montreal shares a tip today to print all Excel keyboard shortcuts at once. Episode 633 shows you how.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Episode 632 - Array Function Arguments

Back in podcast 621, I wondered why the function arguments box won’t work with an array formula. It turns out that it works fine… unless you switch applications while the dialog box is shown! In Episode 632, we take a look at why the function did not work, when it does work, and a cool workaround.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Episode 631 - Realllly Long Text

Excel can allegedly handle 4096 characters in a cell, but in today’s podcast nothing past character 1130 is displaying in the cell. In Episode 631, a convoluted solution with a textbox solves the problem, but there must be a better way.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Episode 630 - Customer Templates

Jennifer from Illinois asks if you can use the trick from podcast 612 in order to create standard proposal workbooks for each customer. Episode 630 shows you how.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Episode 629 - Column Function

I want to revisit a topic from podcast 613; using the COLUMN() function in place of putting a 2 as the third argument in a VLOOKUP function. Several questions came in about that technique, and in Episode 629, I will try to clear up the confusion.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Episode 628 - Pivot Rearrangement

Sometimes, you might need to rearrange the headings in a pivot table. You can easily do this with dragging and dropping or simply by typing a new name in a new location. Episode 628 shows you how.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Episode 627 - Relative Recording

When recording a macro to enter a SUM formula, you might need to write a formula that freezes one half of the reference for the summed range. Episode 627 shows you why.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Episode 626 - VBA Naming Ranges

The macro recorder uses a confusing way to name ranges. There is a much simpler way, as podcast 626 shows.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Episode 625 - Formula Bar

Excel 2007 features an amazing expanding formula bar. Today’s podcast takes a look at how to handle extremely long formulas in Excel 2007. Episode 625 shows you how.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!