Learn Excel from Bill Jelen with this daily 2 minute video podcast.
Someone put a bunch of mathematical calculations in column A as text and we want to evaluate those formulas. Mike and Bill provide dueling ways to solve this problem in Episode 1149.
posted by Bill at 9:00 AM
Ram asks how to split a word into individual letters, and then count how many times each letter appears. Episode 1148 shows you how.
Labels: COUNTIF, MID, SUMPRODUCT
posted by Bill at 9:00 AM
Wang asks how to change part of one data label to be red. Unfortunately, the label is then broken. A macro should solve this, but it does not. Episode 1147 discusses this problem.
posted by Bill at 9:00 AM
Mark asks how to fill blanks in with the data from above. Now...if you've watched this podcast, you've seen the trick for this, but Mark's data has something preventing Go To Special Blanks from working! So - another way to go. Episode 1146 shows you how.
Labels: AutoFilter, Blank Cells, Blanks, GoTo Special Blanks
posted by Bill at 9:00 AM
Diane from Michigan wants to split a column of names into two columns. Episode 1145 shows two approaches and the problems with each.
Labels: LEFT, MID, Names, text to columns
posted by Bill at 9:00 AM
Martin from Sweden sends in a question about doing a range VLOOKUP where the lookup table is not sorted. Mike and Bill duel it out in today's Episode 1144.
posted by Bill at 9:00 AM
Michael from Kansas City asks how to build a floating quartile chart. Episode 1143 shows you how.
Labels: chart
posted by Bill at 9:00 AM
You can specify up to eight colors in the custom number format box. But wait...you can actually do all 56 colors from the Excel 2003 color palette! Episode 1142 shows you how.
Labels: custom format, Formatting
posted by Bill at 9:00 AM
Rob from Cambridge needs to divide nine cells by nine other cells and sum them up. In Episode 1141, we look at an array formula to solve this problem.
posted by Bill at 9:00 AM
A cool way to streamline a VBA loop using SpecialCells. Episode 1140 shows you how.
Labels: formula, Go To Special, VBA
posted by Bill at 9:00 AM
A viewer from Portugal sends in a question about finding which row contains the match. Episode 1139 shows you a few ways to go!
posted by Bill at 9:00 AM
Rod from Cincinati asks how to solve the problem of trailing spaces in the lookup table. I had never tried it before, but you can apply a TRIM to the entire lookup table. Episode 1138 shows you how.
posted by Bill at 9:00 AM
In Episode 1137, a cool way to remove all hyperlinks from a spreadsheet.
posted by Bill at 9:00 AM
Mary from Louisiana passes along a cool tip for applying AutoFilter dropdowns to a subset of your records. Episode 1136 shows you how.
Labels: AutoFilter, Filters, shortcuts
posted by Bill at 9:00 AM
Nick asks how to do a pretty lengthy bonus calculation. It might be too complex for the Excel 2003 nested IF limit, so I decided to use the range version of VLOOKUP. But...as a twist, I embed the table right in the formula. Episode 1135 shows you how.
posted by Bill at 9:00 AM
Today's Dueling Excel Podcast is a question sent in by Hassan. Hassan wants to enter a name and a through-month and then add up that person's sales from January through the selected month. Mike and Bill show various methods in Episode 1134.
posted by Bill at 9:00 AM
Patricia called the other day with a problem which will be familiar to everyone who works in a public school district. The state pays the school for every student who is enrolled on the magic date of October 1. Given a start date and end date, how can you tell if the date is in that range? Episode 1133 shows you how.
posted by Bill at 9:00 AM
The Excel fill handle is really cool with quarters. But wait....it can not do quarters and years. But wait...it can do them but in a hideous format. Episode 1132 asks - "Does anyone have a better way?"
posted by Bill at 9:00 AM
Roger from Columbus Indiana passes along a cool trick for formatting all of the text in a chart at once. Episode 1131 shows you how.
Labels: charts, Formatting, Text
posted by Bill at 9:00 AM
Greg wants to set up dynamic validation, but he has a wrinkle....Once someone chooses from the first data set, he needs to append the word My before that answer to get to the name of the second data set. Episode 1130 shows you how.
Labels: INDIRECT, Validation
posted by Bill at 9:00 AM
A dueling podcast...how to find the first week of the month. Mike and Bill show you various ways in Episode 1129.
posted by Bill at 9:00 AM
The last in my series of podcasts about GetPivotData. Today, we see how to use GetPivotData to overcome all the bad stuff in a pivot table. Episode 1128 shows you how.
Labels: GetPivotData, Pivot Table
posted by Bill at 9:00 AM
Rather than turn off the evil GetPivotData like we did yesterday, how about trying to understand what GetPivotData actually does? Episode 1127 shows you how.
Labels: GetPivotData, Pivot Table
posted by Bill at 9:00 AM
99% of the people in my seminars hate GetPivotData. Most people outside of Microsoft hate GetPivotData. Today, in Episode 1126, as we've done before, we will take a look at how to turn off GetPivotData.
Labels: GetPivotData, Pivot Table
posted by Bill at 9:00 AM
Don't use HLOOKUP...just turn the table back to a vertical table using the two methods shown in Episode 1125.
posted by Bill at 9:00 AM
Mike & Bill take a look at various ways to do a 2-way lookup in this dueling Excel Episode 1124.
posted by Bill at 9:00 AM
VLOOKUP week continues.... Today, you have to lookup an account number and return the results from four columns in the lookup table. Episode 1123 shows you four possible solutions.
posted by Bill at 9:00 AM
What happens when you enter the perfect VLOOKUP formula and everything returns #N/A? Episode 1122 shows you some of the sneaky reasons why VLOOKUPs fail and what to do about it.
Labels: VLOOKUP
posted by Bill at 9:00 AM
The most common error from your VLOOKUP formula is the #N/A error. Episode 1121 looks at how to find the #N/A and then three ways of adding those new values to your lookup table.
Labels: VLOOKUP
posted by Bill at 9:00 AM
VLOOKUP is my favorite function in Excel. In Episode 1120, we will take a look at the basics of VLOOKUP.
posted by Bill at 9:00 AM
External data pasted into Excel shows up with 1 row in columns A & C, but multiple rows in B. Today's dueling Excel Episode 1119 shows how to deal with this data.
posted by Bill at 9:00 AM
Roy asks how to prevent a workbook from being saved until the workbook is complete. Episode 1118 will show you the four lines of VBA needed to make this happen.
posted by Bill at 9:00 AM
Michael wonders what happened to the old Excel 2003 Natural Language Formulas feature. It is out of Excel 2007, but replaced with something similar. Episode 1117 shows you now.
Labels: formulas
posted by Bill at 9:00 AM
Michael asks why when you add up three cells with 1/3, you don't get 0.99? Episode 1116 takes a look at how to solve this.
posted by Bill at 9:00 AM
Brett from Australia asks how to format a date to include the words Opening Balance so that he can do a VLOOKUP into a report. The format is easy, the VLOOKUP is tougher. Episode 1115 shows you how.
Labels: DATE, dates, Formatting, Text, VLOOKUP
posted by Bill at 9:00 AM
You need $273 in cash. How many of each currency amount will you need? This Dueling Episode 1114 shows a few different ways to go.
Labels: formula
posted by Bill at 9:00 AM
Med asks how to calculate Years, Months, Days between two dates. Episode 1113 discusses the elusive DATEDIF function.
posted by Bill at 9:00 AM
David asks why his total formulas are getting #VALUE errors. Episode 1112 shows this common problem and a solution.
Labels: formulas
posted by Bill at 9:00 AM
Priscilla from Plano Texas offers up yet another way to have the Fill Handle automatically extend 1 to 1, 2, 3. Episode 1111 shows you how.
Labels: shortcuts
posted by Bill at 9:00 AM
Erik has a combination lock and has forgotten the combination. He wonders how many possible combinations there are for the lock. Episode 1110 suggests some solutions.
posted by Bill at 9:00 AM
BackIn2Shape at YouTube has two worksheets; one of customers, and one of prospects. If a prospect is on the customer list, he would like to delete it. Episode 1109 show you how.
posted by Bill at 9:00 AM
John asks if there is a way to automate the process from Episode 322 of adding pop-up pictures to a cell. Today's Episode 1108 takes a look at the VBA code to add pop-up pictures to many cells.
posted by Bill at 9:00 AM
John asks how to take 10 years worth of stock data and convert it to a chart showing months across the bottom and a different line for each year. Episode 1107 shows you how.
Labels: charts, Pivot Table, stock
posted by Bill at 9:00 AM
David from Australia sends in a cool trick for adding a timestamp to a spreadsheet. Episode 1106 shows you how.
posted by Bill at 9:00 AM
Fabien sends in an intriguing pivot table question. I show one mildly acceptable way to solve the problem using the existing data and then a way to spin the data to make the problem easier to solve. Episode 1105 shows you how.
Labels: Pivot Table
posted by Bill at 9:00 AM
|
0 comments
In this Dueling Excel podcast, how to find the earliest date for records that match two criteria. Episode 1104 shows you how.
Labels: array formula, AutoFilter, Filter
posted by Bill at 9:00 AM
Sam from Vienna sends in today's question. In a database of events, how can he add new blank rows to represent every missing day? Episode 1103 shows you one method.
posted by Bill at 9:00 AM
Dak asks how to take a long cell entry and to split it into 20 character entries going down a column without using wrap text. Episode 1102 shows a couple of ways to solve the problem.
Labels: array formula, formula, MID
posted by Bill at 9:00 AM
Gary, Jamie, Monish and Michael all check in with ideas on why to use the Split command. Episode 1101 is a follow up to last Wednesday's podcast 1098.
Labels: Split
posted by Bill at 9:30 AM
DRG asks how to use COUNTIF to find records that match a range. Unfortunately, it can not be done with COUNTIF. In Episode 1100, Bill and Mike compare six solutions.
posted by Bill at 9:00 AM
