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).

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…

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.

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!

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.

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.

2. Pick a design if you would like (in this case, yellow for WSU).

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])

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.
3. Location of Pivot Table: on a new sheet, titled Pivot.
4. Build the table with Item as rows, Helper Column as Values.5. 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.

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.

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).

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

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.

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.

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

Megan Nold (not pictured)

Kylie Sharpe

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.

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.

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.

2. Now, Go to the dropdown created next to 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.

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:

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.

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

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

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

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!)

We all occasionally find ourselves navigating the rough terrain of ugly data. Power Query is an excellent tool to have in your back pocket for this eventuality. But here is the deal, Power Query is just that… powerful, and because of this, potentially complicated. In fact, I know a lot of people who have been interested in learning about this beast, only to get quickly overwhelmed in the complexities. So here is my aim with this Byte: a simple introduction to Power Query. Let’s check it out.

What is Power Query?

Power Query is a feature in the PC version of Excel 2016 or later, also known as Get & Transform. There are a multitude of uses for this feature, but it really shines to take on ugly data like this:

… and transform it into something more workable.

Furthermore, since the result is a query that is connected to your data, it can be updated with a simple click of a button.

Exercise File

If you would like to follow along with my steps below, here is an Exercise File:

A Couple Notes:

• You will need the PC desktop version of Excel 2016, 365, or 2019 to use this feature.
• For this Byte I am assuming you have worked with Tables and Pivot Tables in Excel before. If you haven’t, please come to my Excel Essentials and Excel Pivot Tables Sessions and learn about them!
• Needless to say, this is all fictional data.

The Scenario

The scenario is we have some ugly data that we have exported from a different source. The source has thrown lots of data into one column of a spreadsheet. Monthly, we are asked to create a pivot table of fees owed by class and area code, but this will be rough going, given how the data looks:

Format as a Table

I have mentioned before that formatting your data as a table has many advantages, and here is another excellent example. There are many data sources you can use for Power Query, but let’s start here for now.

1. Click on any cell inside the data. Do not preselect the entire column.

2. In the ribbon, select Format as Table, and select any style.

3. Make sure that all the data is encompassed in your range, make sure My table has headers is checked, and press OK.

More details about tables are discussed in the Excel Essentials training, so check out one of those sessions if you have not already.

Create a Query

1. Go to the Data tab, and in the Get & Transform Data group, select From Table/Range.

(note: you can also access this from the Get Data dropdown if it is not readily visible)

2. You will be taken to the Power Query Editor.

This is a little like an alien abduction from your comfortable Excel home… the look and feel are kind of the same as the rest of Excel, but also kind of different. Power Query uses a different type of code than the rest of Excel, so some things will not be intuitive. I don’t want to get too far in the weeds, but for now note that:

• There is no undo, but you can always delete a step on the right,under Applied Steps, where coincidentally, every step of the query will appear forevermore.
• There is a ribbon with Transform and Add Column option. Although options will look similar on both tabs, items on the Transform tab will change an existing column, and items on the Add Column tabwill create a new one
• A lot of options are also accessible on a right click menu.

Transform the Data

In the query, there is only one column with a lot of ugly data, but I notice most of the data is separated by a colon “:”. Let’s begin by splitting up the columns by this delimiter.

1. Click at the top of the column to select all of the data in the column.

2. In the ribbon, or on a right click menu, select Split Column –> By Delimiter.

3. Make sure Colon is selected, and Each ocurrence of the delimiter is selected. Press OK.

Your data should split into separate columns at each occurrence of a colon.

4. Let’s work on splitting the phone number. Select the phone number column, and Split Column –> By Number of Characters.

5. We want to split after 3 characters, Once, as far left as possible. Click OK.

Cool, now we have a separate column for the area code!

6. At the top of each column, double click on the title and give each column a name: Name, Area, Phone, Grade, Class, Fees.

Load the Transformed Data

1. You have the option to name your query on the right side of the screen, under Query Settings –> Properties. If you will be doing multiple queries, this might not be a bad idea.

2. After this, the final step is to press the Close & Load button in the Home tab.

Like magic! Our data loaded to a new tab in a new table.

Good News Part One: Data is Easier to Work With

Okay, this data is going to be so much easier to work with. Remember, my original goal was to make a pivot table showing fees owed by area code. Now that this data is in a table, in a few clicks, I have exactly the information I need. Beautiful!

Good News Part Two: Updating Data

We could have fixed up that data in a variety of other ways in Excel (hello, Flash Fill!), but the especially cool part about Power Query is the ability to refresh data in the future with one click. Remember I mentioned this was a monthly report I had to create… so here is what I would do next month… rather than recreate all the steps to make the data readable again:

1. Load the new data into the source table.

2. Right click on the query table and select Refresh

All the query steps we did in the previous section will happen automatically with my new data.

More Types of Queries

This example involved a query from a table within a document, but you can query tons of different sources: Excel documents in seperate locations, Access databases, Azure databases, online sources… seriously, check out all the options on the Data tab, Get Data dropdown.

Interested in a Power Query Session?

This was a very basic introduction to Power Query. There is a lot more to cover about this amazing feature. WSU friends, I would like to poll the audience here… I think it would be fun to offer a Power Query Session to delve more deeply into its various abilities, but would like to hear from you all if this is something that would interest you. If you are interested, please send me an email!

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

Christina Covey

Brandon Whiteside

Have you ever gone through a list row by row to highlight rows based on one value in the row? Here is one example: you have a list of students, and you want to highlight their entire entry if they have an “A” in your class… so you hand-select an entire row, fill in the desired color, and repeat this process 100 or so times per semester. Never do that again! Let’s check out another versatility of Conditional Formatting: the ability to highlight an entire row based on one value in a specific column.

Before we jump in… big thank you to Samantha and Amanda for inspiring this Byte with this excellent question in (two different) Excel Advanced Formulas sessions!

Exercises

If you would like to follow along, here is a fictional class list to work with. Our goal with this exercise is to make an entire row yellow if a student has an A in the class.

A Word About Conditional Formatting

In my Advanced Formulas training we touch on formatting specific cells based on their value with Conditional Formatting, but this will be a bit different… in this case, we would like the entire row to be highlighted based on the value of one cell in the row (the grade).

If you haven’t already, I hope you will consider checking out an Excel Advanced Formulas session… for one, we cover Conditional Formatting in more detail, and for two we talk about concepts like absolute references, which I reference later in this Byte.

Create a New Rule: Crafting a Formula

1. Start by selecting all the data you would like the formatting to apply to: A2 through H77.

2. In the center of the Home tab, select Conditional Formatting, New Rule.

3. In the popup screen, select Use a formula to determine which cells to format.

4. Click into the box underneath the text Format values where this formula is true.

• In your data, click on the cell with the first grade, in H2.
• Excel will bring in its own language for this cell “$H$2”.
• Type an equals sign ( = ) and (with quotes) “A”.
• Your formula at this point will look like : =$H$2=”A”

Sidebar: We hit this in more depth in Excel Advanced, but as a reminder…

• The “$” means an absolute reference, so a stagnant location in Excel. We see an example of this in class when we try to use autofill to carry down a formula.
• The A has to have “” around it, since it is text.
• If you want to learn more about these two points, please come to one of my Excel Advanced Formulas trainings.

Okay, we are not done with the formula yet… if we leave the formula like this, it will highlight the entire selection range (A2 through H77) if H2 has an “A” in it… this is not what we had in mind. To correct this, we are going to remove one dollar sign…

5. Since we realized that the row should not remain absolute, let’s delete one dollar sign (absolute reference). We are going to delete the dollar sign in front of the number 2. This will tell Excel that the row is relative; but the column will remain absolute.

• Here is what your formula should look like now:

=$H2=”A”

We are not done yet, though… we still have to set up formatting…

Creating a New Rule: Formatting

It is easy to get so caught up in the formula that you forget to set a formatting rule. Right now the formula looks great, but Excel won’t make it look any different until we pick some formatting.

1. Select the Format button in the New Formatting Rule popup underneath where you placed your formula.

2. In the popup that appears, select the Fill tab. Select a color, then press OK.

3. Alright, this looks pretty good! We have a formula and a formatting. Press OK to see the results.

If all goes well, you should have something like this.

To sum up,Excel is looking through column H (which remained absolute with the “$”) for an “A”, and if it finds an “A” there, the entire row is highlighted all the way down (because the “2” was not absolute, thanks to removing the “$”)

Managing Conditional Formatting Rules

A few more things that might be helpful:

• If you want to see the conditional formatting rules you have created, click on the Conditional Formatting dropdown, and select Manage Rules.

• The default view shows you the rules present for any cells selected. So if you would like to see all the rules for a sheet, you can alter the Show formatting rules for dropdown accordingly.

• Here is where you can also make adjustments to your rules: edit, delete, alter the cells it applies to (helpful if you selected too many or too few cells to begin with).

Thoughts?

What do you think, will you try out this type of conditional formatting in your documents? I would love to hear how you use this!

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

• Kaylee Nungesser

• Andrea Glessner