Learn Excel from Bill Jelen with this daily 2 minute video podcast.
Today an idea from Tim in NH. While I frequently use RANDBETWEEN() to do a door prize drawing, Tim adds some bells and whistles to create a nice spreadsheet used to award the prize. Episode 726 shows you how.
Labels: Door prizes, formulas, Random
posted by Bill at 9:00 AM
While I've talked about Go To Special many times, I usually use Blanks, Visible Cells Only, or Formulas. Someone asked how to use the Row Differences option. This is a good way to compare if anything changed from one column to another, but it is limited. Episode 725 explores this option.
Labels: Go To Special, Row Differences
posted by Bill at 9:00 AM
I used an old custom number formatting trick in yesterday's podcast. Today, we take a look at more possibilities using custom number formats. Episode 724 shows you how.
Labels: Formatting, number formatting
posted by Bill at 9:00 AM
After summarizing data in a pivot table, you want to replace the numbers with a simple Yes/No text. Yes if they met the condition, No if they did not.
Labels: conditional formatting, Grouping, Pivot Table
posted by Bill at 9:00 AM
The PROPER function never manages to capitalize the interior C in McCartney. In today's podcast, an interesting way to solve this problem with a formula. Episode 722 shows you how.
posted by Bill at 9:00 AM
Many people were missing from the map on last Monday's podcast. Did I miss their entries? No! I use a common Excel trick to keep leading zeroes, but this confused MapPoint. In today's podcast, we take a look at other ways to keep leading zeroes in Excel. Episode 721 shows you how.
Labels: Concatenation, Leading Zeroes, Maps, Number Formats
posted by Bill at 9:00 AM
Jonathan is frustrated that his validation scrollbars start in the midst of several blank cells that he set up in his validation list. He was doing this to allow room for future growth in the list.
Labels: Dynamic Range, Offset, Validation
posted by Bill at 9:00 AM
Wale asks how to randomly choose 20 invoices from a population of 500 invoices. Episode 719 will show you two methods for solving this problem.
posted by Bill at 9:00 AM
Laura, Erik, and Howard all sent in similar workarounds to podcast 713. Today in Episode 718, we will take a look at how to offer a validation dropdown that will allow keyboard afficianados to enter values quickly.
Labels: AutoComplete, Validation
posted by Bill at 9:00 AM
Matthew from Washington wants to take an event database and create a visual calendar that shows availability.
Labels: Calendar, conditional formatting, Named Ranges
posted by Bill at 9:00 AM
Today, in Episode 716 we take a look at the entries for the MapPoint contest, and also a chance to enter the MrExcel NCAA basketball game.
Labels: RandBetween, Random numbers
posted by Bill at 9:00 AM
March 14 (3-14) is Pi day and we will take a look at some calculations with the PI and SQRTPI functions in Episode 715.
posted by Bill at 9:00 AM
In yesterday's podcast, we were frustrated that the Validation dropdown would not allow us to type a few letters of the entry. In Episode 714, we will take a look at a tiny userform and macro to replace the validation dropdown.
Labels: Select a value
posted by Bill at 9:00 AM
In Episode 713, a link to http:/www.mrexcel.com/excel_video_training.html, another link to http://www.mrexcel.com/video2003.html. Then, how to set up a dropdown list in Excel, but some frustration that the dropdown list won't let you type the first few letters of the value.
Labels: Validation
posted by Bill at 9:00 AM
Dick sends in a cool question today. First, he figured an amazing way to grab the first value from a subtotaled group. But, Episode 712 shows three lines of VBA code to finish the process.
Labels: array formula, formula, Macro
posted by Bill at 9:00 AM
Do you ever need to produce a report for every customer? This is a snap with pivot tables in Excel 2007. In Episode 711, we take a look at how to use Show Report Filter Pages to replicate a report for every customer.
Labels: Excel 2007, Pivot Table, Show Pages
posted by Bill at 9:00 AM
Learn how to filter a pivot table to only items from this week, last quarter, or next month. Episode 710 shows you how.
Labels: dates, Excel 2007, Filters, Pivot Table
posted by Bill at 9:00 AM
Excel offers amazing tools to group daily dates to months, quarters, years, or weeks. Episode 709 will show you how.
Labels: dates, Excel 2007, Grouping, Pivot Table
posted by Bill at 9:00 AM
Episode 708 shows how Excel offers sorting rules that allow your pivot tables to be sorted in a certain manner. The old AutoSort option is now hidden behind a dropdown in the pivot table field list.
Labels: Excel 2007, Pivot Table, Sorting
posted by Bill at 9:00 AM
To rearrange a pivot table in Excel 2007, simply drag fields to a new drop zone in the pivot table field list. Episode 707 shows you how.
Labels: Excel 2007, Pivot Tables
posted by Bill at 9:00 AM
Create your first pivot table in Excel 2007. Episode 706 shows you how.
Labels: Excel 2007, Pivot Table
posted by Bill at 9:00 AM
