Learn Excel from Bill Jelen with this daily 2 minute video podcast.
Yet another solution to matching data from two different worksheets...the Data Consolidate command. Episode 929 shows you how.
Labels: consolidate, Data
posted by Bill at 9:00 AM
Tom from Cincinnati asks how to create a chart which can show either monthly or quarterly data. Episode 881 shows how to manually set up group and outline symbols to achieve the result.
posted by Bill at 9:00 AM
After adding subtotals by Customer, Excel adds three group and outline buttons. Most people use the #2 and #3 buttons but rarely use the #1 button. Could you simply remove the #1 button? Podcast 849 shows you how.
posted by Bill at 9:00 AM
During a recent seminar, someone asked me to show the audience how to use the Consolidate command. I launched into the technique shown in Episode 831 - using consolidate to collapse a single range down to one line per customer. In tomorrow's podcast, we will look at alternate uses for consolidate.
posted by Bill at 9:00 AM
It is frustrating when section headers contain data that applies to all records in that section. You really want to get that header data down onto every row in the section. Episode 826 shows you one method for doing this.
Labels: Data, Data wrangling, header
posted by Bill at 9:00 AM
Travis has a spreadsheet where you can quickly hide and unhide detail rows using the tiny "1" and "2" buttons. In Episode 799, I will show how to add these Group and Outline symbols to your worksheet.
posted by Bill at 9:00 AM
A question from a seminar... how can you use the consolidate command to subtract one data set from another data set? Episode 787 shows you the workaround.
Labels: consolidate, Data, Paste Special, subtract
posted by Bill at 9:00 AM
An introduction to using Data Consolidate to add two data sets. Episode 786 introduces the concept. Tomorrows episode will throw in an interesting twist.
Labels: consolidate, Data
posted by Bill at 9:00 AM
Hamilton sends in a question about parsing data that has leader......lines between the columns. This ends up being trickier than you might think. Episode 782 shows you how I approached the problem.
Labels: Data
posted by Bill at 9:00 AM
Pau asks how to take a CSV file from one website and convert it for importing to Google Calendar. In Episode 773, I turn on the macro recorder and attempt to fix the file.
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
In yesterday’s podcast, I showed how to rearrange one column. But – what if you need to rearrange all of the columns? Episode 544 shows how to use a little known sorting trick to sort the columns back to the original sequence.
Labels: Column Widths, Data, shortcuts, Sorting
posted by Bill at 9:00 AM
Do you need to turn data on its side in Excel 2007? PowerPoint MVP Kathy Jacobs shares her favorite new feature in Excel 2007. Episode 472 show you how.
Labels: Data
posted by Bill at 9:00 AM
You are invited on Monday to download MrExcel’s NCAA bracket and enter his free NCAA pool. The winner will get their choice of books from the MrExcel store. In today’s Episode 461, we will take a look at the Data – Validation commands used to make the NCAA bracket selection worksheet work.
Labels: Data, Validation
posted by Bill at 9:00 AM
Setting up data validation lists, where the second list is dependent upon the answer given in the first dropdown box. Episode 383 shows you how.
Labels: Data, Validation
posted by Bill at 9:00 AM
Sometimes you want to have cell validation, but you need a way for the person using the spreadsheet to override the validation list, especially in the case where there is a brand new value that you have not yet added to the list. Episode 381 shows you how to have the Validation dropdown ease up.
Labels: Data, List, Validation
posted by Bill at 9:00 AM
A great call from Georgia - how to ease the data entry when you need to enter cells in an unusual pattern. Even if your data entry cells are in a circle or other strange path, you can define a named range that will remember the sequence for entering cells. Episode 359 shows you how.
Labels: Data, Named Ranges
posted by Bill at 9:00 AM
