|
|
By Debra Dalgleish, on February 28th, 2013 A couple of months ago, I shared an example with a scroll bar that selects the dates for an Excel report. There is a pivot table on a hidden sheet, and a summary report uses GetPivotData formulas to pull data from that pivot table.
This technique works well when there are a few static row headings, like the East and West region names, in this example. If the row headings will change every month, or if you have a larger number of categories, my technique won’t work as well.
Filter the Data With a Scroll Bar
AlexJ has created
Continue reading Update Pivot Table With Scroll Bar
By Debra Dalgleish, on February 19th, 2013 A few months ago, I shared my code for listing all the formulas in an Excel workbook. The code creates a new worksheet, with details on each formula’s worksheet name, cell address, the formula and the formula in R1C1 format.
Pivot Table Formulas
If you create calculated fields or calculated items in a pivot table, those formulas are not included in the list of worksheet formulas.
However, you can manually create a formula list, for any pivot table, by using a built-in pivot table command.
List the Formulas in Excel 2010 Select any cell in the pivot table. On
Continue reading List All Pivot Table Formulas
By Debra Dalgleish, on December 13th, 2012 Slicers were introduced in Excel 2010, and they make it easy to change multiple pivot tables with a single click. In earlier versions, you can use programming to change the report filters in multiples pivot tables.
AlexJ has shared a nice technique for using a single Report Filter to update multiple pivot tables. This technique uses slicers, but they are stored on a different worksheet, so they don’t take up room on the dashboard.
Single Report Filter
Here is a screen shot of AlexJ’s dashboard, with two filters at the top of the sheet. The pivot charts are all
Continue reading Change All Pivot Charts With One Filter
By Debra Dalgleish, on November 6th, 2012 Recently, I enrolled in an online Infographics and data visualization course, and the classes started last week.
The instructor is Alberto Cairo, who wrote The Functional Art: An Introduction to Information Graphics and Visualization.
He uploaded the first two chapters of his book for us to read during week one, and I really enjoyed it. There was some history, some theory, and plenty of graphics, to illustrate the text.
Improve the Infographic
One of the assignments this week was to suggest improvements to an infographic on Social Web Involvement. There are 16 countries in the infographic, overlaid on a
Continue reading Link Pivot Chart Title to Report Filter
By Debra Dalgleish, on September 20th, 2012 Do you use the free PivotPower add-in that's available on my Contextures website? I created PivotPower to make my own work easier to do, and shared it on my website to help you with your pivot table tasks.
It automates some of the features that aren't built in to an Excel pivot table, and makes some of the buried Excel pivot table features easier to access. For example, there is a command that changes all the data fields to SUM, which is handy when Excel defaults to COUNT.
PivotPower Premium Now Available
The free version is still available, and
Continue reading PivotPower Premium Excel Add-in
By Debra Dalgleish, on September 20th, 2012 A pivot table is a great way to summarize data, and most of the time you probably use a Sum or Count function for the values. For example, in the pivot table shown below, the regional sales are totaled for each week.
Add Custom Calculations
Instead of settling for a basic sum or count, you can get fancier results by using the built-in Custom Calculations.
Right-click on a value cell in a pivot table Then click Show Values As, to see a list of the custom calculations that you can use. This list is from Excel 2010, and
Continue reading Calculate Differences in a Pivot Table
By Debra Dalgleish, on August 28th, 2012 In Excel 2010, you can use Slicers to change multiple pivot tables. However, you might be working in an earlier version of Excel, or you don't have room for Slicers on your worksheets.
Instead of Slicers, you can use programming to update multiple pivot tables automatically. In previous posts, I've shown how you can select items in one pivot table's Report Filter fields, and the Report Filter fields for pivot tables on the other worksheets will change to the same selections.
Specific Sheet and Pivot Tables
Jeff Weir has written an updated version of the code, which runs
Continue reading Update Specific Pivot Tables Automatically
By Debra Dalgleish, on June 12th, 2012 In a pivot table, you can choose to show or hide the grand totals, but you can't change their position. However, with a quick and easy workaround (no programming required), you can show the grand total for columns at the top of the pivot table.
Unfortunately, there's nothing similar that will create a grand total for rows at the left side of the pivot table.
Read the Instructions
To see the written instructions, please visit the Contextures website, and go to the Pivot Table Grand Totals page. There is also a sample file on that page, that
Continue reading Show Grand Total at Top of Pivot Table
By Debra Dalgleish, on June 5th, 2012 An Excel pivot table is a great way to summarize a large amount of data, and with its Top 10 filter, you can compare the top values to the bottom values. But don't limit yourself to the Top 10 versus the Bottom 10 – dig deeper by using the other options in the filter.
Summarize the Data
With a few mouse clicks, you can summarize thousands of rows of data into a concise and informative pivot table. In this example, there is a list of product, and their total sales over two years.
Instead of viewing the products alphabetically,
Continue reading Compare Top and Bottom Sales in Pivot Table
By Debra Dalgleish, on May 15th, 2012 To format a pivot table, you can select a specific section, such as one of the fields, or a grand total. When you point to a field heading, a black arrow will appear, if the Enable Selection setting is turned on.
In the screen shot below, you can see the black arrow at the top of the Product field. Click in that spot, and all the Product item labels are selected.
Click in that spot again, and the Product heading is selected, instead of the item labels.
Pivot Table Field Setting Quick Tip
Instead of a
Continue reading Excel Pivot Table Selection Quick Tip
|
|
Recent Comments