Here, the Years and Months are in the Row area, and the Descriptions are in the Column area, filtered for Total Cost and Total Revenue. To give a visual overview of the data, Jim create a pivot chart from the multiple consolidation range pivot table. For % Change, Jim uses the % Difference From custom calculation. To show the Change, Jim changes the custom calculation to Difference From. In another version of his pivot table report, Jim puts 3 copies of the Value field in the Values area. Or, the Column field can be moved to the Row area, to show the dates at the left, in a vertical report. Here, the dates are grouped by year and month, and again, the grand totals are automatically calculated. This gives him even more flexibility in his pivot table reports. Revenue A produces 60% of the revenue, and 40% is from Revenue B.īecause the multiple consolidation ranges pivot table puts the Dates in a single field, Jim is able to group those dates. This is a quick way to see the revenue breakdown, for the two revenue streams. In the pivot table shown below, the Values are shown as % of Column. For a different view of the data, Jim changes the way the Values are shown. When you create a pivot table, the values are summarized by Sum or Count, and shown as simple totals. The Row and Column grand totals automatically update, to show the totals for the filtered pivot table.
Then, filter the Columns, to show the December 2010 dates. For example, you could filter the Rows to show only the Revenue items (but not the Revenue Total). NOTE: If we had created a normal pivot table from the data, each date would be a separate field, and there would be no grand total.Īfter creating the pivot table, Jim filters the Row and Column fields, to focus on specific data. This multiple consolidation ranges pivot table shows a grand total for the columns, because all the dates are in the Column field. The dates are the Column items, and the descriptions are the Row items. This creates a pivot table with 3 fields – Row, Column and Value. Pivot Table from Bi-Weekly DataĪfter setting up his bi-weekly financial data, Jim creates a pivot table, using multiple consolidation ranges. Don’t create an Excel Table if you want to use the technique that Jim is sharing today. NOTE: Usually, Jim would create an Excel Table from this data, but that changes the headings to text. Every column has a heading, and there are no blank rows or columns within the data. In his file, Jim has dates in the heading row – not just the month names. Thanks to Jim LaBarr, who sent me a sample Excel file, with his solution to creating reports from multi-column financial data. Today we’ll look at a different approach to using multi-column data in a pivot table. We used a multiple consolidation ranges pivot table to change this 13-column table into a 3-column data source. Last week, you saw a technique for changing monthly data into a better layout, when creating an Excel pivot table.
0 kommentar(er)
