Excel: Filter a Pie PivotChart and Maintain Percent of Whole (A Different Take on Progress Charts)

A fantastic question came up during last week’s open lab. Is it possible to create a PivotChart pie chart that can be filtered to show only the percent of the item selected?  Here is one solution for that question (and spoiler alert, it is technically not a PivotChart).

Donut Filtered to show percent remaining

A few things before we get started…

  • First, thank you to Marsha for having this question and inspiring this Byte!
  • Second, this article assumes you have some knowledge of Tables, PivotTables, and Charts in Excel. If you don’t, please come attend a session… times are listed in myTraining.
  • Lastly, this is basically a different take on the idea of a progress pie chart… and I am by no means the creative genius behind this overall idea! If you have a minute, look at some of the tutorials out there for the different use cases for these. They are mighty handy.

Background

Normally when you select a slicer or filter a Pie chart by one item, each item shows as 100%. Here is an example:

Below is a visual of a PivotChart Pie Chart…

Pie Chart PivotChart

Looks great… but when I add my slicers and filter by one item, it always shows as 100% of the total, because it is 100% of what is displayed.

As described above, showing 100%

What if we want to use those  slicers, but also maintain the visual of the item’s percent of the grand total… in other words, in this case, to still show as 36% of the whole? Some finagling is in order.

Exercise

Rather than an exercise, I am going to try something different. Here is the final document… and below will be the steps I took to create it:  Example Document

In the document, one sheet is the source data, and the other sheet is the final chart with slicers that will adjust the pie chart accordingly.

Click on a few slicer buttons to test it out. Pretty cool!

Donut Filtered to show percent remaining

Starting Point

We originally started with source data (on the Source Data sheet in the document) that consisted of two columns: item and a number.

Starting List, animal names and random numbers

Format as a Table

This first step might be optional, depending on your use case. Since data would be fluctuating in our case (e.g. new rows of data added), it was best to format as a table.

We learn about tables in Excel Essentials, and the advantages of formatting as tables in Excel Pivot Tables, so please come to a session if you are interested in learning more!

1. You can either go to the Home tab, Format as Table, or use the command Ctrl + T.

Format as Table circled in ribbon

2. Pick a design if you would like (in this case, yellow for WSU).Original data, formatted as a table

Create a Helper Column for Percent

We need a cheat column that would keep a percent constant when filtered, so a helper column was in order. You have probably noticed that formulas look different in a table… you still want to build them in the first cell of a column to carry them down.

We want the column to take the figures in column B and divide them by the total. The formula ends up looking like this: =[@Number]/SUM([Number])

Helper Column with formula for percent as described

This looks complicated, but it is very automatic. Here were my keystrokes/mouse clicks:

[click into the first cell of Column C], Type  = [click on B1] / SUM [Select B2 through B7].

Create a Pivot Table

  1. Click on the table to activate Table Design tab.
  2. On Table Design tab, Select Summarize with PivotTable.Table design tab, summarize with pivot table circled
  3. Location of Pivot Table: on a new sheet, titled Pivot.
  4. Build the table with Item as rows, Helper Column as Values.Pivot Table5. Insert Slicer for Item (on the PivotTable Analyze tab).

Create Helper Cells with GETPIVOTDATA

So here is the twist… Instead of creating a PivotChart, we are actually going to create a chart from helper cells that reference information from the PivotTable.

1. The first helper cell references the Grand Total of the PivotTable:

[Click in B1], type  =  [click on the Grand Total cell of the PivotTable].

Notice how this appears in the formula bar as a GETPIVOTDATA formula in the formula bar. This cell will always return whatever Pivot Grand Total is Displayed.

Helper cell with GetPivotData formula

2. The second helper cell, in B2, contains the formula =1-B1. So it will always read the difference of 100% from whatever the GETPIVOTDATA result shows.Formula in a different helper cell as described

Create Chart from Helper Cells

Finally, we can make the chart!

1. Select the two Helper Cells in B1 and B2.

2. Go to the Insert tab, and select the Pie Chart dropdown.

3. Select the Doughnut (it could be any of the pies, but this is the route I took for reasons you will see).

Insert Pie Chart, donut4. If desired, you can pick special colors by right clicking on any data point and selecting Fill. I picked some WSU colors.

Right click menu on chert

Finishing Touches

The number you see in the center of the doughnut is actually a title. Remember how we learn in Excel Pivot Tables, Charts and Pictures training that a chart title can reference a specific cell? This will come in handy here.

1. Title: Click on the line of the title and press = , then click on B1

So this Title will always reference this cell, which will always reference the grand total displayed on the PivotTable.

This means you will always have a number corresponding to the colors on the chart.

Header with formula directing bavk to helper cell

2. Text Box: This may be optional depending on your situation, but I inserted a Text Box at the top of the chart to read Percent of Total: Insert tab, Text Box.

3. Position Slicers: Final step was moving the Slicers and resizing them so they fit neatly on top of the Pivot Table. This was only cosmetic, and is also optional. Really there are infinite possibilities for how you could play around with final formatting with a chart like this.

Donut Filtered to show percent remaining

A Simple Alternative

It is worth mentioning that, depending on your situation, you may be able to skip the PivotTable step by incorporating a total row in your Table. This would apply if you didn’t need to sum multiple entries of specific items.

You would still need to incorporate helper columns, but instead of linking to the Grand Total of the PivotTable, they could link to the Total Row sum.

So many possibilities!

Thoughts?

What do you think? Has this sparked any ideas? Have you used pie/doughnut charts for this type of scenario before?

Congratulations, Power Users!

Congratulations to our newest Power Users! For the full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser

 

Robin Mishler

Robin Mishler

Megan Nold (not pictured)

Kylie Sharpe

Kylie Sharpe

Excel: Don’t Merge Those Cells! Here is an Alternative.

Friends don’t let friends merge cells! This is something you hear often among Excel enthusiasts. People usually merge cells in an attempt to make a spreadsheet look nicer. That being said… not only is the beauty of a spreadsheet less important than its functionality, which is definitely adversely affected by merged cells… but there is actually a way to alter the appearance identically to merging cells without all of the many disadvantages that come with merged cells. Let’s check it out.

Excel screen with merged cells

Exercise

If you would like to follow along with my demonstration below, here is an Exercise file: MergedCells

This is a fictional list of students and grades, with some merged cells at the top.

Merged Cells

The first row of data contains 3 sets of merged cells: A1 with B1; C1 with D1; and E1 with F1. If you select any of these, notice in your Alignment group that Merge and Center is selected.

Excel screen with merged cells

Perhaps someone did this in an attempt to make their spreadsheet look less cluttered? Let’s see why this might have been a bad idea.

Why Merged Cells are Problematic

There are actually quite a few ways that merged cells can be problematic. Here are just a few.

Sort and Filter

Let’s say I would like to use the custom Filter buttons we play with in Excel Essentials.  You want to filter by all students with an A.

1. Click anywhere in the top row, and on the right side of the Home tab, select Sort and Filter, and Filter.

Sort and Filter2. Now, Go to the dropdown created next to Grade.

Sort and filter by grade

Normally I would have the opportunity to filter by letter grade, but because E1 and F1 were merged cells, Excel instead only offers for you to filter by grade %. Not very helpful.

PivotTables

Maybe instead, we can make a PivotTable from the data, and pivot by the letter grade? Go to the Insert tab, and select PivotTable.

Pivot Table errors

What is this? We are receiving an error because we don’t have true column labels (header row) when cells are merged like they are in our top row.. this means that Excel doesn’t know what our categories are to create a PivotTable.

(Shameless plug: come to an Excel: Pivot Tables training if you would like to learn more.)

Formulas

This is probably the biggest one for me. Let’s say I want to count the number of 22 year old students in my class. No problem! Let’s do a CountIf formula.

In I1 I entered =Countif( … then I tried to select my range, column D…. look what happens:

Formula attempting to select full column

Excel doesn’t want to allow me to include column D alone… it wants to include Column C as well. How annoying! We could probably find our way around this formula issue, but even then, I guarantee these merged cells will get in your way with a future formula.

Macros

It is worth mentioning that there are macros that can be interfered with when you use merged cells; it depends on what type of macro you are building.

All in all, merged cells are just not worth the trouble.

Another Option: Center Across Selection

If you are truly attached to the look of merged cells, there is another option. It is called Center Across Selection.

  1. First, let’s undo the merged cells. Select the merged areas, then go to the Home tab, Alignment group, select the dropdown for Merge and Center, and select Unmerge cells.

Unmerge Cells

2. Select A1 and B1, and Right Click on top of them. Select Format Cells.

right click, format cells screen

3. In the popup screen, go to the Alignment tab, and click on the dropdown next to Horizontal. Select Center Across Selection. Click OK.

Alignment tab, center across

4. Repeat this step with C1 and D1 selected, then E1 and F1 selected. Appearance wise, it will look just like merged cells.

Center across looks the same as merge and center

This still will be somewhat limiting; for instance, you may still have difficulty with a PivotTable unless you convert this to a Table first, but you will not experience nearly as many drawbacks as merged cells.

A Question for You

Whether you choose to center across or merge cells, I think it is an important question to ask yourself, why are you wanting to do this? Is it truly necessary? When at all possible, I would recommend avoiding either of these practices. I understand the desire to beautify a workbook, but clearly labeled columns with long lines of uninterrupted data are the truly beautiful spreadsheets. Their beauty is in their functionality; and when functionality is lost, nobody will really care much about how the top row looks. Just a thought, from someone who has “unmerged” many cells in many peoples’ spreadsheets over the years.

Thoughts?

What do you think? Has this convinced you to unmerge and never merge again? Either way, I will be here to help you.

Congratulations, Power Users!

Congratulations to our newest Power Users! For the full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser

Sheree Smith
(First Power User of the decade!)

Sheree Smith

Outlook: Disabling Automatic Schedule View in Calendar

The Automatic Schedule View feature in Outlook is generally regarded as a gnawing frustration for most Outlook users. It is not a debilitating occurence, but something that requires constant correction. Here is the good news… you can turn it off so it never bothers you again.

More arrow in arrange group

Before we jump in, big thank you to Megan for having this question in the last Outlook Advanced session.

How is Schedule View Triggered?

Most people, when viewing multiple calendars at a time, are used to viewing them side by side like this:

Regular calendar view

But, by default, if you open more than 5 calendars at a time, instead your view automatically changes to something like this:

Schedule view

This horizontal orientation is Schedule View. Not the end of the world… most people just mosey up to the Arrange group in the ribbon, and flip back to Work Week or Week view (depending on their preference).

Arrange group, Schedule View selected

That being said, some find it a bit jarring when you are browsing through 4 calendars, then open up one more and suddenly the whole view is changed. Sort of interrupts your train of thought…

Who knows, maybe you even like Schedule View (no judgement), but would like to activate it yourself, rather than have it flip on automatically. Here is how you can make that change.

Disabling Automatic Schedule View

  1. Go to the Calendar module.
  2. In the Home tab, Arrange group, click on the arrow in the lower right of the group.Arrange group, more arrow
  3. Scroll down to Display Options, and either uncheck “Automatically switch…to Schedule View”, or change the minimum number to automatically switch from 5 to a number you prefer.

Display options, box unchecked next to automatically switch to Schedule View feature

4. Click OK.

Some of this is personal preference. I turned this feature off completely. For me, I would rather change my view by hand rather than have it automatically change when I am in the middle of browsing calendars.

You may decide that you want it to automatically change for you, but just after a larger number of calendars are opened. Either way, the power is now in your hands!

Thoughts?

What do you think, do you think you will make an adjustment to your Schedule View defaults?

Microsoft PowerUp!

Hey are you all signed up and ready for one of my upcoming PowerUp sessions? I hope to see you there! More information can be found at wichita.edu/powerup.

Powerup Session poster. visit wichita.edu/powerup

Congratulations, Power Users!

Congratulations to our newest Power Users! For the full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser

Arlene Thomsen

Arlene Thomsen