Join Dennis Taylor for an in-depth discussion in this video Switch rows and columns for a different view of the data, part of Excel 2016: Charts in Depth.
- View Offline
- When you select a chart, on the Design tab is a choice called Switch Row/Column. The description doesn't quite do it justice. It says: swap the data over the axis. That doesn't mean a whole lot until you actually try it. I strongly recommend using this button almost every time you create a chart. I'm going to use it right now on the chart we see below. Notice how across the bottom, we see the months. As I click the button, Switch Row/Column, now we see the regions: Domestic, Europe and Asia.
I have a certain preference on this one, it's the previous chart, but nothing lost here by making a choice. The chart to the lower left. Same general idea: Switch Row/Column. How's that going to look? Maybe better, maybe not. The reason you want to do this, is sometimes you'll get ideas about different ways to display the data. Something else happens at times, and you'll be a little bit mystified. I'm going to highlight some data here, say for the first six months. I'll quickly create a chart with Alt F1.
You see the orientation there. We see months across the bottom. I'm going to shorten that chart just a little bit, make it a little bit smaller. Now, I'm going to highlight just half of that data, just this portion right here, and press Alt F1. Notice what happened here. Instead of putting the months across the bottom, it puts the regions. Based on the shape of the data that you selected to go into a chart, you're likely to get a different layout when you press Alt F1 or F11 to get a chart immediately.
No matter how that looks, consider the other options, Switch Row/Column. It's not to say that you're going to get a better choice all of the time; in fact, maybe it's roughly 50/50. But what if it happens that you like both of these? You can easily create a chart and then simply press Control D to duplicate it. For the moment, you'd have two charts identical. Just Switch Row/Column on one or the other, and then decide which ones you're going to keep. Move them around in different ways. You'll get ideas sometimes about how data looks, how it doesn't look.
On the next sheet over called Line Chart, we see a chart that's already there. When you switch line charts, usually it's pretty obvious that one of these choices is much better than the other. Line charts in general should have a timeframe across the bottom: that's why we see the data here. On this chart here, if I were to click there, go to the Design tab and Switch Row/Column, I think that makes almost no sense at all. Data doesn't really flow from state to state, but it certainly flows across time periods, so switch that one back.
I think you'll find over time as you try this button, you get some interesting results at times. If it is a line chart, usually it's pretty obvious. One's going to be different than another. Here's another example on this sheet called City Sales. We're looking at a stacked column chart. How might this look if we go to the Design tab and Switch Row/Column? Not so bad, but again, consider what you prefer and possibly if you want both, simply duplicate the chart, switch them back and forth in different ways to show both. At any time, just click a chart.
On the Design tab, there's that choice: Switch Row/Column. Consider the options, pick one or the other, or save both.
The training wraps up with lesson on changing data sources for charts and printing and sharing charts.
- Identifying chart elements
- Selecting the right chart type
- Creating basic charts
- Creating sparklines
- Styling charts
- Moving and resizing charts
- Modifying axes
- Adding labels and gridlines
- Analyzing data with trendlines
- Inserting pictures, shapes, and text boxes
- Customizing column, bar, line, and pie charts
- Creating Gantt charts and other specialized Excel charts
- Changing data sources
- Printing and sharing charts