On January 30, I led a free, one-hour webinar, High Impact Excel: Pivot Table Edition. If you missed the presentation, it’s too late to get CPE credit, but you can watch an on-demand recording. After the webinar, I held a one-hour Q&A session during which attendees asked me questions about the presentation and Excel. In this article, I’ll share three of the best questions asked by the audience.
1. Lyle, president of an accounting firm, asked: “Rather than using a text file as my raw data, could I connect to a SQL database table or tables instead?”
During the presentation, I showed how to convert a rather ugly text file into an analysis-ready format. The answer to Lyle’s question was a resounding "yes", as shown in Figure 1.
Figure 1: Excel pivot tables can be based on connections to an SQL server and other data sources.
2. Lisa, a financial analyst, asked: “You showed us a way to change the look of the pivot table to mirror the old Excel 2003 format (displaying subtotals at the bottom of the pivot table data rather than at the top). Is there a way to set newer versions of Excel to default to this setting so it doesn't have to be changed every time?”
Unfortunately, you cannot force subtotals to always appear at the bottom of each group unless you create the pivot table in an .XLS workbook. This is the old file format that is compatible with Excel 97-2003. Figure 2 shows the default format for pivot tables in Excel 2007 and later, which is known as "compact form", as well as the manual steps required to reposition subtotals.
Figure 2: Subtotals appear at the top of each group by default in Excel 2007 and later, but you can manually reposition subtotals to the bottom.
3. Jack, an actuary, asked me a question I couldn’t answer on the spot. During the presentation, I showed how to group date-based fields by month, quarter, and/or year. Jack wanted to apply the same technique to group text items and e-mailed me the solution. Jack clicked on an item in the "Product" column, and then held down the Ctrl key on a second item. He then chose the "Group" command via the right-click menu to group apples and oranges together. He then created a group of the remaining products.
As shown in Figure 3, he added a subtotal and was free to reword Group 1 and Group 2 to his liking. Thank you, Jack, for adding another technique to my Excel toolbox.
Figure 3: You can group and subtotal unrelated items within a pivot table.
High Impact Excel: VLOOKUP Edition is scheduled for 2 p.m. Eastern time on February 27. Don’t miss out, register for one hour of free CPE today!