Change Pivot Chart Layout

Fix Pivot Chart Column Colors

Recently, someone asked me why all the columns in their pivot chart were the same colour. The chart showed several months of data, for multiple regions, and every column was blue.

In the screen shot below, I've created a similar pivot chart, from fake sales data.

(adsbygoogle = window.adsbygoogle || []).push({});

Continue reading Change Pivot Chart Layout

Show Rank in Excel Pivot Table

Add Rank to Excel Pivot Table

A pivot table is a great way to quickly summarize number, to see how your business is doing. But sometimes those numbers are too large to analyze in your head, so let the pivot table give you a bit of help, with the pivot table Rank calculation.

(adsbygoogle = window.adsbygoogle || []).push({});

Continue reading Show Rank in Excel Pivot Table

Move Pivot Fields Without Dragging

Move Pivot Fields Without Dragging

Last week, someone asked me if there is a menu command for moving the fields in a pivot table layout. For example, if the Region field is in the Columns area, can you use a command to move it to the Rows area, or do you have to drag it with the mouse, in the PivotTable Field List?

Continue reading Move Pivot Fields Without Dragging

Pivot Table Source Data Articles

Pivot Table Data Source Tips

The key to success with Excel pivot tables is having good source data. I've written many articles with pivot table data source tips, and this list will help you find the information that you need. Some of the articles are on my Contextures website, and others are on my Pivot Table blog.

Continue reading Pivot Table Source Data Articles

Pivot Table Running Total Percent

Use Excel Pivot Table Running Total Percent to Analyze Sales

At the beginning of every month, I download the previous month's statistics for my web site, to see which pages and files were the most popular. After the data is imported to Excel, I use pivot tables to get a quick overview of the activity.

If a page is getting lots of hits, I might add a new section to it, to make the content even better. And, seeing which sample files get downloaded most frequently, gives me ideas for creating new examples.

I'm sure you do something similar for your month end, to see how things are going. And

Continue reading Pivot Table Running Total Percent

Find and Fix Pivot Table Source Data

alt text: Find and fix pivot table data source problems

After you create a pivot table, you might add or change records in the source data. Sometimes the pivot table does not update correctly, to show the new data. Or if you've inherited the pivot table file from someone else, you might not even be able to find the source data, in a large workbook.

To help with these problems, I've added a new page on my Contextures site, with tips for finding and fixing the source data for a pivot table.

Continue reading Find and Fix Pivot Table Source Data

Hide Pivot Table Subtotals

Remove pivot table subtotals VBA

Sometimes the Excel macro recorder creates code that gets you off to a good start. Other times, it's not so helpful.

This week, I was working on a pivot table macro, and wanted to turn off all the Row Field subtotals. Fortunately, there is a handy command for this on the Ribbon, on the Design tab, under PivotTable Tools. One click, and the subtotals disappear, or reappear.

Let's get the code for that, which I'm sure will be equally clean and simple. Ha!

Continue reading Hide Pivot Table Subtotals

Combine Data on Two Worksheets

Combine tables with Power Query

Have you been experimenting with the Power BI tools that are available in the newer versions of Excel. I've done some work with Power Pivot, and was impressed by what could be done with that add-in. But, despite its capabilities, I haven't been using it for big projects, or client work.

Recently though, I've been testing Power Query, and the things that it can do are very exciting. I'm just getting started with this add-in, but was amazed by how easy it is to combine data on two different worksheets.

Once the data is combined, you can filter and sort

Continue reading Combine Data on Two Worksheets

Create Custom Reports From Pivot Tables

custom reports from pivot tables

If you're a pivot table fan, like I am, you know how quick and easy it is to summarize a massive amount of data, with just a few clicks. You can show sums, counts, averages, and other totals, without using any fancy formulas.

In the screen shot below, the pivot table is summarizing income and expenses, and there is a Slicer at the top left, for quick filtering.

Continue reading Create Custom Reports From Pivot Tables

Excel Report Diagnostic Display

excel diagnostic report

Did you ever print and distribute pivot table reports in Excel, only to discover – too late – that they weren’t showing the correct information? To help save you from wasted time and paper, and possible embarrassment, AlexJ is sharing his Report Diagnostic Display technique today.

You might remember some of his other handy tips, such as

Set Row Height With REPT Function Use Slicers to Filter a Table in Excel 2010

Before you read the rest of this article, remember that the 20% discount ends today, Oct. 16th, for Mynda Treacy’s Excel Dashboard course. To get a bonus of

Continue reading Excel Report Diagnostic Display