Learn Excel from Bill Jelen with this daily 2 minute video podcast.
In a second attempt to solve Odelia's question from yesterday, I use a bar of pie chart to automatically move all of the pie wedges less than 4% to a secondary chart. Episode 684 shows you how.
Labels: bar of pie chart, charting, charts
posted by Bill at 9:00 AM
Odelia is trying to create a pie chart with only the values greater than 4% of the total. Episode 683 shows one method for solving this problem.
Labels: charts
posted by Bill at 9:00 AM
Ross is heading back to university and was hoping to use Excel to quiz himself. In Episode 682, I will show you how to use conditional formatting to hide the correct answer until an answer is filled in.
Labels: conditional formatting, formula is, practice tests
posted by Bill at 9:00 AM
Kambiz sends in a question. He has a Summary worksheet that points to Jan, Feb, Mar worksheets in his workbook. When he copies the worksheet to a new workbook, the formulas keep pointing to the Jan worksheet on the original workbook. In Episode 681, we take a look at using the INDIRECT function to solve this problem.
Labels: INDIRECT, summary worksheet
posted by Bill at 9:00 AM
Florian wants to time stamp a record every time someone types a new name in column A, but the NOW function is not working. In Episode 680, I will show you a few lines of VBA code to solve this problem.
Labels: Event Handler, NOW, timestamp, VBA, Worksheet_Change
posted by Bill at 9:00 AM
Dan asks another Excel 2007 question. How can you re-specify the source data for a pivot table in Excel? It used to be simple to do this in the pivot table wizard, but it is not obvious how to find the pivot table wizard in Excel 2007. In Episode 679, I will show you how to solve the problem in Excel 2007.
Labels: 2007, Change Data Source, Pivot Table
posted by Bill at 9:00 AM
Dan asks how to find Page Break Preview in Excel 2007. In Episode 678, I will show you how to find Page Break Preview, but also the improved Page Layout View.
Labels: 2007, Page Break Preview, Page Layout View
posted by Bill at 9:00 AM
David from Spain sends in today's question. When you have a long text cell, it will tend to spill and show up in adjacent blank cells. David notes that you can add an apostrophe to the blank cell to prevent this. In Episode 677, we take a look at a faster way to solve this problem.
Labels: Blank Cells, GoTo Special Blanks, Wrap Text
posted by Bill at 9:00 AM
Paul uses the macro recorder to record the action of naming the current region. The macro recorder never gets this correct. There is a far simpler version of code to create named ranges. Episode 676 will show you how to replace the recorded code.
Labels: Named Ranges, VBA
posted by Bill at 9:00 AM
Today we revisit Episode 672. Rather than using the difficult syntax from that episode, Paul from Darby suggests using a named range. Episode 675 shows you how.
Labels: formulas, Named Ranges
posted by Bill at 9:00 AM
Ute from Germany sends in a better way to show negative points in a column chart in a red color. His cool trick works for Excel 97-2003. Episode 674 will show you how.
Labels: accounting, business, charting, charts, Excel, MrExcel, spreadsheets, technology, tutorial
posted by Bill at 9:00 AM
Hamideh sends in an interesting question. He set up a really cool custom number format to prevent people from having to enter 4 dashes in a part number. However... now Find appears not to work. In Episode 673, I will show you the hidden setting to assist with the Find.
Labels: accounting, business, Excel, MrExcel, spreadsheets, technology, tutorial
posted by Bill at 9:00 AM
How can you do a VLOOKUP from one worksheet to another worksheet? It is simple to do, but hard to remember the syntax. In Episode 672, I'll show you the easy way to enter these formulas.
Labels: formulas
posted by Bill at 9:00 AM
How can you change names to proper case, with the first letter of each name capitalized? Episode 671 shows you a few different ways to solve this problem.
posted by Bill at 9:00 AM
OK. Yes. Sorry. I spent two days on insanely hard formulas. Thanks for the letters, phone calls, and e-mails. There is a simple way to total five cells diagonally. Episode 670 shows you how.
posted by Bill at 9:00 AM
Kambiz sends in a question today that is far more difficult than the example from podcast 668. Kambiz needs running diagonal totals. In Episode 669, we take a look at Chip’s formula from Episode 668 and figure out how to modify it to solve this tricky problem.
posted by Bill at 9:00 AM
Today, we look at how to total a data set diagonally. Episode 668 shows you how.
posted by Bill at 9:00 AM
Mustafa asks a question of how to see the totals from only the visible rows in a filtered data set. There is an easy way to do this, but it is not completely obvious. Episode 667 shows you how.
posted by Bill at 9:00 AM
Rodney asks how Excel can make a clustered column chart. While it is not immediately obvious, it is relatively easy to do. Episode 666 shows you how.
Labels: charts
posted by Bill at 9:00 AM
In today’s netcast, I will show you how to use a simple loop and some recorded macro code to pull all of the data from many web pages. Episode 665 shows you how.
posted by Bill at 9:00 AM
When you paste web data to Excel, many hyperlinks are created. In today’s episode, I create a couple of lines of code to extract all of the links and write them to a new column in Excel. Episode 664 also shows you how to use the Watch Window to discover property names.
posted by Bill at 9:00 AM
When you copy data from a web page and paste into Excel, you often end up with a whole series of tiny graphical icons. Rather than deleting those one at a time, Episode 663 will show you how to get rid of many objects at once.
posted by Bill at 9:00 AM
