Learn Excel from Bill Jelen with this daily 2 minute video podcast.
A dueling Excel podcast where Mike and Bill take a look at how to sort using a formula. While most people would use RANK and COUNTIF, Episode 1025 shows you how to use LARGE and ROW or ROWS.
posted by Bill at 9:00 AM
Todd has lottery data in a single column and needs to break the data into various columns. Episode 1024 shows you how.
Labels: text to columns
posted by Bill at 9:00 AM
Add a background picture to a worksheet that is printable and semi-transparent. Episode 1023 will show you how.
Labels: Pictures
posted by Bill at 9:00 AM
Episode 1021 required that the survey data be set up vertically. Most survey tools like Survey Monkey would provide the data in a table format with questions going across the columns. In Episode 1022, learn how to quickly re-orient survey monkey data in a proper format for pivot tables.
Labels: Pivot Table
posted by Bill at 9:00 AM
Patricia asks how to replicate a frequency distribution for many questions in a survey. If your data is set up correctly, this is a snap using the Show Report Filter Pages feature in a pivot table. Episode 1021 shows you how.
Labels: Pivot Table, Show Pages
posted by Bill at 9:00 AM
Dave wants to know how to choose a random name, but he does not want the complete list of names in the cells in the worksheet! Episode 1020 shows you how.
Labels: array formula, Random
posted by Bill at 9:00 AM
You can solve the MWF problem from episode 1018 using an incredible array formula from the book Excel Gurus Gone Wild. Episode 1019 takes a look at how to coerce an array of dates from a start date and end date cell.
posted by Bill at 9:00 AM
Figure out the number of billable days between two dates. Episode 1018 looks at ways to count the number of days, number of workdays, or number of Monday-Wednesday-Friday dates between two dates.
posted by Bill at 9:00 AM
Shawn sends in today's question. The spreadsheet has billing data to audit. For each record, there is a location, a start date, and an end date. Shawn wants to find any records where the same date and location is double-billed. A quick two-loop macro solves this problem. Episode 1017 shows you how.
Labels: VBA
posted by Bill at 9:00 AM
How to highlight part of a line chart using a second area chart series. This trick works in Excel 2007, but not 2003. Episode 1016 shows you how.
Labels: charting
posted by Bill at 9:00 AM
In today's dueling podcast, how to turn a range upside down. Episode #1015 shows you how.
Labels: array formula, Dynamic Range, formulas
posted by Bill at 9:00 AM
In our second look at Formula Recalc, a 750x improvement in formula recalc times by using some helper cells. Episode 1014 shows you how.
Labels: calculation speed
posted by Bill at 9:00 AM
Andre provides a set of formulas to chart the last rolling six months of data. Episode 1013 shows you how.
Labels: Dynamic Range
posted by Bill at 9:00 AM
Vaibhav asks how to group a pivot table up to fiscal quarters. While this is easy if your fiscal years ends on December 31, it is not so easy for other year-ending dates. Episode 1012 shows you how.
Labels: dates
posted by Bill at 9:00 AM
Mike Girven pointed me to an excellent whitepaper by Charles Williams about calculation speed. In Episode 1011, an example contrasting different ways of entering running totals. One calculates 100 times faster than the other!
Labels: calculation speed, Calculations
posted by Bill at 9:00 AM
A dueling podcast: how to unwind a rectangular selection to a single column. Mike & Bill weigh in with differing methods in Episode 1010.
Labels: format
posted by Bill at 9:00 AM
3 fast tips from my recent seminar in Louisville Kentucky: Different way to select current region; different way to Undo; How to overline and underline subtotals. Episode 1009 shows you how.
Labels: shortcut keys
posted by Bill at 9:04 AM
Marcelo asks how to replicate all of the formatting on an existing chart. In Excel 2003, this was Chart, Custom Types, User Defined, Add. In Episode 1008, we take a look at the equivalent method in Excel 2007.
Labels: charting
posted by Bill at 9:00 AM
William asks how to base his conditional formats on a start date in column A and a duration in column B. This calls for the Formula version of conditional formatting. Episode 1007 shows you how.
Labels: conditional formatting, format
posted by Bill at 9:00 AM
Ileane asks how to get only the .edu e-mail addresses from a database of 20,000 e-mails. Episode 1006 shows you how to use advanced filter with a criteria range to solve this problem.
Labels: Filter
posted by Bill at 9:00 AM
Another Dueling podcast: How to filter to the top 5 golf scores for one golfer? The AutoFilter won't let you do it. But Mike and Bill show you how in Episode 1005.
Labels: Filter
posted by Bill at 9:00 AM
