Stack multiple columns into one with Transform Range Stack multiple columns into one with formula 1. Select the range you want to stack, and go to the Name Box to give this range a name, and press Enter key. Excel Stacked Bar Chart (Table of Contents) Stacked Bar Chart in Excel; How to Create a Stacked Bar Chart in Excel? Stacked Bar Chart in Excel. A stacked bar chart is a type of bar chart used in excel for the graphical representation of part-to-whole comparison over time. This helps you to represent data in a stacked manner. Select the data and insert a 100% stacked bar chart. Since there were more columns in the data set than rows, Excel didn’t plot the data the way we wanted it. No matter, it’s easy enough to click the Switch Rows and Columns button on the ribbon. Now let’s format the data. Excel for Mac doesn't support Power Pivot and thereby doesn't have distinct count feature. What is the best workaround to get distinct count in such cases? Sample Excel Columns: Period Criteria1 Criteria2 Criteria3 Data. Sample Pivot table: Different values in 'Period' will be pivot columns. 'Criteria1' can be a filter to pivot table. Hi, I have managed to do two sets of data side by side for a stacked column. Is there any way to split the legend? I would ideally like to give each legend its own title of which group it is referring to. Attached graph - I essentially want two legends, One each for abcd and efg.

• How To Stack Columns In Excel
• Sorting Data In Excel

How To Stack Columns In Excel

Problem: I need to create two stacked columns clustered with a third column.

1. This is harder than it looks.

   Strategy: This chart uses two rogue series and a hidden secondary axis. Follow these steps carefully.

   1. Add two blank series between Series 1b and Series 2. Fill with zeroes.
2. Two extra series.
   2. Create a stacked column chart from all five series.
   3. If you are plotting quarters, Excel will put the wrong data along the horizontal axis. Click the Switch Row/Column icon to move the Series 1a, Series 1b, and so on to the legend.
   4. Go to the Layout tab in the ribbon. Use the leftmost dropdown to choose Series 2.
   5. Click Format Selection to open the Format Dialog box.
   6. Choose Secondary Axis. Don't close the Format dialog box.
   7. Go back to the dropdown and choose Series Blank 1.
   8. In the Format dialog box, choose Secondary Axis.
   9. Go back to the dropdown and choose Series Blank 2.
   10. In the Format dialog box, choose Secondary Axis.
   11. Go back to the dropdown and choose Series 2.
   12. Go to the Design tab of the ribbon. Choose Change Chart Type. Choose the first column chart, known as a Clustered Column Chart. This changes all three of the series that use the secondary axis.

   At this point, you finally have something that looks almost correct. There are still several things to fix:

   • The left vertical axis is using a different scale than the first.
   • The stacked column is wider than the clustered column.
   • There are two extra entries in the legend.
   • You really don't need to show the secondary axis once you make them have the same scale.
3. You are starting to get close.

   By the way, those two extra blank series are there to move Series 2 to the right. If you entered 100 and 200 in those series, you would see how they are pushing Series 2 over to the right of the stacked column.

4. Here, the two blank series are moving Series 2 to the right.

   The remaining steps assume the Format dialog box is still open.

   13. Click on the right vertical axis. In the Format dialog, change the first three settings from Auto to Fixed. Make a note of the settings in those three boxes.
   14. Click on the left vertical axis. Make six changes in the Format dialog box. Change the first three settings from Auto to Manual. Click in the box next to manual. Type the same values from step 13 into the boxes next to manual. This will make sure that both axis have the same scale.
   15. Click on one of the stacked series to select it. In the Format dialog box, change the gap width to 300%. This will make the stacked column less wide and about the same size as the third column.
   16. In the legend, click once on Blank 1, then do a second single click on Blank 1 to select only that item in the legend. Press Delete to Delete that entry.
   17. Do two single clicks on Blank 2 in the legend. Press Delete.
   18. In the Layout tab, choose Legend, Show Legend at Top.
   19. Click on the right vertical axis. Press Delete.

   This whole set of steps is demonstrated in Learn Excel Podcast Episode 1091.

   Gotcha: This only works with one stacked column and one non-stacked column. If you need both columns to be stacked, it will not work. Jon Peltier sells a cool utility to solve this.

Sorting Data In Excel

For more resources for Microsoft Excel