Learn Excel from Bill Jelen with this daily 2 minute video podcast.
Michael asks how to calculate a sliding commission rate. Episode 757 shows two approaches, both of which use the sorted version of VLOOKUP.
Labels: commission, formulas, VLOOKUP
posted by Bill at 9:00 AM
A question from a recent seminar involved calculating how many unused meals occurred during a month. The person had to rewrite several formulas every month depending on the total number of days in the month. In Episode 756, we'll take a look at some changes to allow that formula to work for every month.
posted by Bill at 9:00 AM
You need to sort a detailed data set so that the records for the largest customer are at the top. Episode 755 will show you a quick way to accomplish this task.
Labels: Sorting
posted by Bill at 9:00 AM
Rene from Norway asks how to modify the code from podcast 730 in order to create a new workbook for every department. Episode 754 shows you how.
posted by Bill at 9:00 AM
When Romas sent in the question for podcast 751, I couldn't tell from the screenshot if his data actually was date/time or text that looked like a date/time. In Episode 753, I take a look at how to tell if the cell contains a date or something that looks like a date. You will also see how to use the TEXT, LEFT, MID, DATE functions to convert the text date/time to a real date.
posted by Bill at 9:00 AM
In today's podcast, we take a look at breaking Romas' date/time column into a date column using the INT function. Episode 752 shows you how.
posted by Bill at 9:00 AM
Romas has data with both date and time in column A...but needs to summarize the data by date. In Episode 751, we take a look at using a pivot table to solve this problem.
Labels: dates, Pivot Table
posted by Bill at 9:00 AM
Do you need to transfer custom lists and other settings from one Office installation to another computer? The Save My Settings wizard will make this a snap. Episode 750 will show you how.
Labels: Configuration
posted by Bill at 9:00 AM
Rob has a spreadsheet showing install dates for several batteries. He needs to sum all of the batteries that are overdue for being replaced. This requires a tricky variation of the SUMIF formula. Episode 749 shows you how.
posted by Bill at 9:00 AM
Terry called in with a question where she wanted to use a pivot table to show text fields from a database. While this question initially threw me for a loop, we did end up with a workable pivot table that showed off text. Episode 748 shows you how.
Labels: Pivot Table, Text
posted by Bill at 9:00 AM
Lucy sends in a question. She has built a table of sales by rep by day. She used the MAX and MIN function to find the largest and smallest value. However, her boss then wants her to identify who had the largest and smallest value. This requires the use of INDEX, MATCH, and a couple of hidden columns. Episode 747 will show you how.
posted by Bill at 9:00 AM
Jon has designed a worksheet in Excel that he wants others to use. However, he does not want them to realize they are using Excel. How can he obscure the fact that we are in Excel? Episode 746 will show you how.
posted by Bill at 9:00 AM
If you want the macro from Episode 744 to always be available, you can save it as an add-in and install the add-in. Episode 745 will show you how.
posted by Bill at 9:00 AM
Continuing the information from podcasts 742 and 743, I record a simple macro today to open the Invoice.xlt file and then add a couple lines of code to update the invoice number and the date. Episode 744 shows you how.
Labels: Macro
posted by Bill at 9:00 AM
Continuing the series from podcast 742, once you've downloaded and customized the invoice from Office Online, learn how to save it as a template and add it to the New Workbook task pane. Episode 743 shows you how.
Labels: Templates
posted by Bill at 9:00 AM
Rather than create an invoice worksheet from scratch, download a free one from Office Online. Episode 742 will show you how.
Labels: invoice, office online, template
posted by Bill at 9:00 AM
Daniel from Quebec sends in a better way to compare columns using GoTo Special Column Differences. Episode 741 shows you how.
Labels: GoTo Special, Row Differences
posted by Bill at 9:00 AM
Jerry from Georgia sends in a great alternate solution to podcast 730, inserting one worksheet per customer. Gerry uses the Show Pages feature of pivot tables. Episode 740 shows you how.
Labels: Pivot Table
posted by Bill at 9:00 AM
Two tips for finding an Excel 2003 command on the Excel 2007 ribbon. One is to use the Customize QAT dialog. Pam sends in a cool website from Microsoft that will help you to find the command. The website is here: http://office.microsoft.com/en-us/help/HA101491511033.aspx. Episode 739 shows both methods.
Labels: Excel 2007, Ribbon
posted by Bill at 9:00 AM
Matthew sends in a cool technique today to find a unique list of serial numbers for every model from a database. Matthew's trick uses about five tricks that you probably rarely use. Episode 738 walks you through Matthew's technique. You will see pivot table calculated fields, paste values, replace, and deleting all zero cells.
Labels: Pivot Table
posted by Bill at 9:00 AM
Lorin asks if there is anything new in Excel 2007 charts. While Microsoft rewrote the charting engine in Office 2007, you still have the same basic chart types. However, there are new data visualization tools in conditional formatting that allow you to add bar charts to every cell. Episode 737 discusses these new features.
Labels: charting, Excel 2007
posted by Bill at 9:00 AM
Jon has a follow-up question about his baseball spreadsheet. After calculating the win and loss streaks, he would like to find the longest streak, including the start and end dates. This seemingly simple question requires two completely useless functions. Once these functions are used in conjunction, they become very powerful. Episode 736 shows you how.
posted by Bill at 9:00 AM
Jon asks how to calculate a winning or losing streak from baseball data. Episode 735 will show you how to build the formulas to calculate a win streak. Also, how to build a column showing Home or Away based on finding an @ sign in the opponent column.
posted by Bill at 9:00 AM
If you want to randomly choose from a list and never have duplicates, you can use the method discussed in this podcast. Episode 734 shows you how.
posted by Bill at 9:00 AM
Hamilton asks how to generate a column of bingo numbers, one at a time. In Episode 733, a tiny macro will add random numbers down column A.
posted by Bill at 9:00 AM
Jonathan notices a problem when he uses the Text to Columns wizard. Baseball scores such as 4-3 are converted to dates. In Episode 732, we'll take a look at how to keep those scores from being converted.
Labels: dates, text to columns
posted by Bill at 9:00 AM
Kerry asks: after adding automatic subtotals, how can you copy only the subtotal rows? Episode 731 will show you how.
posted by Bill at 9:00 AM
J.B. needs to create a new worksheet for every customer listed on the summary worksheet. He asks if there is an Insert Worksheets command. While there isn't, you can create one with a few lines of VBA code. Episode 730 shows you how.
Labels: VBA
posted by Bill at 9:00 AM
Chris from Scotts sends in a cool trick today...how to set up an icon set in Excel 2003. Episode 729 takes a look at how to set up this trick.
Labels: Formatting, Icon Sets
posted by Bill at 9:00 AM
Excel 2007's table functionality is cool, but viewer Jim is wondering why Excel is changing his formulas. In Episode 728, we will take a look at how to control this behavior,
Labels: Excel 2007, Pivot Table
posted by Bill at 9:00 AM
Bill has a worksheet where he's building text references and then hopes to evaluate the resulting SUM function. Instead, I suggest using INDIRECT to add up the three individual cells. Episode 727 shows you how.
Labels: Concatenation, formulas
posted by Bill at 9:00 AM
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
