Learn Excel from Bill Jelen with this daily 2 minute video podcast.
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
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
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
Another improvement in Excel 2010 is in the pivot table calculation options. You can now calculate the rank of an item, or the % of the parent row! Episode 1079 shows you how.
Labels: 2010, Pivot Table
posted by Bill at 9:00 AM
Excel 2010 finally gives you an option to fill in the blank spots in the outermost row fields. Episode 1078 shows you how.
Labels: 2010, Pivot Table
posted by Bill at 9:00 AM
One of the great new features in Excel 2010 is the Slicer functionality. Slicers are a great visual way to filter your pivot table. Episode 1067 shows you how.
Labels: 2010, Pivot Table, slicers
posted by Bill at 9:00 AM
How to use COUNTIF to count survey answers for a subset of rows. Bill and Mike show many methods in Episode 1065.
Labels: COUNTIF, COUNTIFS, formula, Pivot Table
posted by Bill at 9:00 AM
Lisa from Chicago sends in today's question. Why can't you sort the page field in a pivot table? Episode 1059 talks about several ways to sort the row fields in a pivot table and then a method for sorting the filter field.
Labels: Filter, Pivot Table, Sorting
posted by Bill at 9:00 AM
Michael sends in a great tip today about pivot tables. While Excel 2007 replaced the drag and drop feature with drop zones, there is an obscure setting in Excel 2007 that will let you go back to the old drag and drop method. Episode 1058 shows you how.
Labels: Pivot Table, WIIW
posted by Bill at 9:00 AM
While yesterday's podcast was created with the macro recorder, you can switch over to the VBA editor to change 4 lines of the macro. This will create a macro that will work without requiring you to rename the pivot table. Episode 1028 shows you how.
Labels: Pivot Table, VBA
posted by Bill at 9:00 AM
Cheryl asks how to format all new pivot tables that she creates. The macro recorder can handle this, if you change the name of the pivot table before recording the macro. Episode 1027 shows you how.
Labels: Macro, Pivot Table, VBA
posted by Bill at 9:00 AM
Episode 1021 required that the survey data be set up vertically. Most survey tools like Survey Monkey would provide the data in a table format with questions going across the columns. In Episode 1022, learn how to quickly re-orient survey monkey data in a proper format for pivot tables.
Labels: Pivot Table
posted by Bill at 9:00 AM
Patricia asks how to replicate a frequency distribution for many questions in a survey. If your data is set up correctly, this is a snap using the Show Report Filter Pages feature in a pivot table. Episode 1021 shows you how.
Labels: Pivot Table, Show Pages
posted by Bill at 9:00 AM
Rod sends in today's question. How can his pivot table indicate if ALL of the records for a certain level are marked as complete? Episode 989 will show you a use for the PRODUCT calculation in a pivot table. Also - the arcane custom number format to display positive, negative, zero values in different manners.
Labels: Pivot Table
posted by Bill at 9:00 AM
Awesome. There is a video on the 'net with Microsoft's Donald Farmer showing off a new add-in called Gemini. This is an amazing product. In Episode 975, I point out where you can get a sneak peak at Excel 2010 pivot tables in the Donald Farmer video.
Labels: Pivot Table
posted by Bill at 9:00 AM
Michael from Shanghai asks about missing data in a pivot table. If some customers have data from every month and other customers have data from only a few months, the pivot table will change shape as you select new customers from the filter. Episode 970 will show you the hidden setting to prevent this problem.
Labels: Pivot Table
posted by Bill at 9:00 AM
Jason has five pivot tables based on the same data. All five have a page field holding the month. After changing the month on the first pivot table, he would like the other four pivot tables to also change. Episode 961 shows Mike Alexander's trick for solving this problem.
Labels: Pivot Table
posted by Bill at 9:00 AM
A big car company offered a boat load of money to put an annoying ad in this podcast for the next two months. Rather than sell out, a reminder that the podcast is sponsored by the MrExcel.com store. Then; a fast way to group age data using a pivot table. Episode #950 shows you how.
Labels: Grouping, Pivot Table
posted by Bill at 9:00 AM
Create a running total within months in a pivot table. Episode 939 shows you how.
Labels: Pivot Table, running total
posted by Bill at 9:00 AM
Another solution to matching data on two worksheets is the Multiple Consolidation Ranges option in the pivot table. Unfortunately, it is much harder to find in Excel 2007. Episode 928 shows you how.
Labels: Pivot Table
posted by Bill at 9:00 AM
Match two worksheets using a pivot table. We continue on this week with Jim's question about matching two worksheets with a common column. Rather than using VLOOKUP, you can use a pivot table to do the comparison. Episode 927 shows you how.
Labels: matching, Pivot Table
posted by Bill at 9:00 AM
Yesterday, I talked about Jason's request to format a grouped pivot field in the row area of a pivot table. There is a solution, but it requires a bit of work in the original data set. Episode 913 will show you about the TEXT function and the other type of VLOOKUP.
Labels: Pivot Table
posted by Bill at 9:00 AM
Jason from Texas asks about formatting percentages in a pivot table. I thought this would be an easy question, but Jason wants to format the grouped percentages in the row area of the pivot table. Episode 912 talks about grouping in a pivot table but doesn't come up with a good solution to the problem.
Labels: Grouping, Pivot Table
posted by Bill at 9:00 AM
In Episode 898, I show off some of the deep features in Excel 2007 pivot tables and offer a sincere thank you to everyone at MrExcel.com.
Labels: Pivot Table
posted by Bill at 9:00 AM
Nancy sends in a cool tip to solve some of the frustrations from Episode 875 -- Excel changing the column widths of a pivot table as you choose new values from a page field. In Episode 887, I will take a look at how to solve this problem.
Labels: Formatting, Pivot Table
posted by Bill at 9:00 AM
Can you use a function in an Excel calculated field? Well, Bryony from the U.K. says yes you can, and sure enough...it works. Episode 878 shows an example of using a function to create a new field in a pivot cache. Fairly cool.
Labels: calculation, Calculations, Functions, Pivot Table
posted by Bill at 9:00 AM
Neville from Germany sends in a good suggestion for overcoming pivot formatting problems from Episode 865. By using conditional formatting on the underlying worksheet, Neville comes up with a way to make sure that the total rows are always highlighted in a pivot table. Episode 875 shows you how.
Labels: conditional formatting, Formatting, Pivot Table
posted by Bill at 9:00 AM
You could solve yesterday's podcast using the often-cursed-at, but seldom-understood GETPIVOTDATA function. In Episode 870, how to use GETPIVOTDATA to return the grand total and also using =IF(ISBLANK to clean up stray results below the pivot table.
Labels: GetPivotData, ISBLANK, Pivot Table
posted by Bill at 9:00 AM
Taylor from Orange County California calls in to ask how you can get the formatting of a pivot table to stick? Every time that you refresh a pivot table, the formatting reverts back to a default. The bad news is that you can not make all formatting stick, but Episode 865 will show you a couple of methods to use.
Labels: Pivot Table
posted by Bill at 9:00 AM
Say that you have a pivot table and need to send the file via e-mail. Use the trick in Episode 857 to dramatically reduce the size of your Excel workbook.
Labels: Pivot Table
posted by Bill at 9:00 AM
Creating your first pivot table in Excel 2007.
Labels: Excel 2007, Pivot Table
posted by Bill at 9:00 AM
Vaibhav sends in a question about creating a particular pivot table. There are a number of challenges in this project. In Episode 840, you will see how to re-order items along a pivot field, how to remove subtotals from one field, how to arrange multiple date fields to go across the page, changing the headings from Sum of X to just X, and filling in blanks with zeroes.
Labels: Pivot Table
posted by Bill at 9:00 AM
Joe sends in a tip about converting your pivot table source data to a dynamic range. While this used to mean using the OFFSET function, now it simply means using Ctrl+L (in Excel 2003) or Ctrl+T (in Excel 2007). Episode 825 shows you how.
Labels: Dynamic Range, List, Pivot Table, Table
posted by Bill at 9:00 AM
Brian from Bentonville provides the idea for this podcast. Episode 824 shows you much faster way to remove subtotals from all of the outer row fields in a pivot table.
Labels: Pivot Table
posted by Bill at 9:00 AM
Normally, you can not use AutoFilter within a pivot table. Today, Dan from Philly shares an amazing hack that allows you to filter the results of the pivot table. Episode 793 shows you how.
Labels: Filter, Pivot Table
posted by Bill at 9:00 AM
Can you use a pivot table to measure how many unique combinations of customer and product appear in a data set? Well, you can't do it natively, but in Episode 758, we take a look at a formula such as =IF(SUMPRODUCT(($B$2:$B2=B2)*($D$2:$D2=D2))>1,0,1) which will allow the calculation to work. Also - a look at the Amazon Kindle.
Labels: Pivot Table, unique
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
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
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
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
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
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
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 Table
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
It is difficult to create a pivot table when you have a time component stretching across the columns in your data set. In today's episode, an amazing trick in Excel 2007 to change four quarterly columns into four rows. Episode 705 will show the multiple consolidation ranges trick.
Labels: Excel 2007, Multiple Consolidation Range, Pivot Table
posted by Bill at 9:00 AM
Microsoft changed the pivot table interface in Excel 2007. While the new interface is much better, in this lesson, we will take a look at how to bring back the old interface. Episode 699 shows you how.
Labels: Excel 2007, Pivot Table
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
Today’s question is a follow-up to yesterday’s question. How can you make sure that a pivot table shows you one row per day, even if there were no sales on a given day? Episode 592 shows two annoying solutions. If you have a better solution, please send in your ideas to bill @ MrExcel.com.
Labels: dates, Pivot Table
posted by Bill at 9:00 AM
Rivky writes in to ask how to do multiple consolidation ranges in Excel 2007 pivot tables. This feature has been on the pivot table wizard for the last 10 years, but it is not at all obvious in Excel 2007. In Episode 556, you will learn how to find this and all of the other commands hidden in Excel 2007.
Labels: Pivot Table
posted by Bill at 9:00 AM
Say that you have a pivot table with region and product in the row area. If you would like a blank row between each region, or even a page break between each region, change the Layout properties for the field. Episode 503 shows you how.
Labels: Pivot Table
posted by Bill at 9:00 AM
There is a cool but subtle way to select all of the subtotal rows in your pivot table in order to format them with a different color. Episode 502 shows you how. This tip came from an audience member at the 33rd annual Meonske Accounting Conference at Kent State. Thanks to Dr. Norm for inviting me back as a speaker at this excellent conference.
Labels: color, Pivot Table, Subtotals
posted by Bill at 9:00 AM
In yesterday’s podcast, I proposed a formula to solve Jonathan’s dilemma of analyzing information by hourly buckets. An easier solution is to use the Group by Hour feature in a pivot table. Episode 495 shows you how.
Labels: Grouping, Pivot Table, Time
posted by Bill at 9:00 AM
In just about every pivot table tutorial, the writer tells you to start with a nice clean transactional data set. No blank rows, no blank columns, and no months going across the columns. David from Pennsylvania writes in to ask what to do if your dataset already is in a table – with headings in the rows and columns. In Episode 493 I borrow a trick from Mike Alexander to solve this problem and make your dataset appropriate for use in a pivot table.
Labels: Data wrangling, Pivot Table
posted by Bill at 9:00 AM
After Scott’s question for tomorrow’s podcast, I thought it would be cool to see how a pivot table could analyze the most popular lottery numbers. The MegaMillions jackpot in several US states is up to $216 million and today’s podcast will select the most popular numbers. Episode 453 shows you how.
Labels: Pivot Table
posted by Bill at 9:00 AM
In Excel 2002+, try to create a formula next to your pivot table. When you copy this formula down to other cells, the formula keeps pointing at the original cells! Is this a bug? No, it's a feature! Well, whatever it is, in Episode 377 I will show you how to stop the functionality so that things continue to work like they did back in Excel 2000.
Labels: formula, GetPivotData, Pivot Table
posted by Bill at 9:00 AM
In episode 358, a great new trick from Adam in the UK to solve the problem from Episode 273. I had previously lamented that when you use the Top 10 AutoShow feature of a pivot table, there is no good way to show one line with all of the other customers. Adam's trick shows how to use the Group feature to solve the problem. Episode 358 shows you how.
Labels: Grouping, Pivot Table
posted by Bill at 9:00 AM
