How To Stack Text In Excel For Mac

by

In Chapter 5 of Cole Nussbaumer’s new book, Storytelling with Data, she shows a revised version of a stacked column chart that highlights three segments with labels off to the left and two summary numbers to the right. It looks something like this:

First, we’ll center text across cells by merging cells. To do this, select the cells you want to merge. In the “Alignment” section of the “Home” tab, click the “Merge & Center” button. The cells are merged and the text is centered in the single resulting cell. If you don’t want to merge the cells, you can still center text. STEP 3: Go to Home Alignment Wrap Text to show the text in multiple lines and you now have all of results! How to Use the Concatenate Formula in Excel.

Cole writes, “Being smart with color, aligning objects, and leveraging white space brings a sense of visual organization to your design. This attention to aesthetics shows a general respect for your work and your audience.” I couldn’t agree more.

So, I thought I might take an opportunity to show you how I would make this graph in Excel. If you follow the Excel tutorials on this blog, you’ll know that I always try to use data to add labels, markers, and other chart elements. I try to avoid adding text boxes or drawing lines when building charts in Excel because I find they rarely end up exactly where I want them, it’s harder to move them over to PowerPoint, and, most importantly, I will have to manually move things around when I update the data. (Note: I’m using the Mac version of Excel 2011 for this tutorial. It works the same way on the Excel 2010 Windows version, but will be a bit different on the 2013 or 2016 versions of Excel.)

The basic data include two values for seven different Segments. The default chart with data labels on each slide looks something like this:

What Cole does is to highlight just the three segments that are of particular interest. She also adds labels to the left of the left-hand column. Importantly—and what I think is particularly great about her redesign—is the “30%” and “50%” labels on the right of each column. Those labels aggregate the three slices so the reader can see both the parts and the whole of each column.

One way to implement the Segment labels is to add another series that sits to the left of the original two. I then label each slice with the Segment name and set the Fill of each section to No Fill. The primary problem with this approach is that the labels are too separated from the rest of the chart and the horizontal axis extends all the way to the left, which is not the case in Cole’s remake.

So how did I make Cole’s chart in Excel? I added eight data points to the chart and changed each of them to a scatterplot.

The first pair of points (under the “Number Scatters” header) are used to add the “30%” and “50%” labels. I add the vertical lines next to these labels by using vertical error bars (using the “PosError” and “NegError” data values). The x-values for these two series (1.22 and 2.22) were chosen so the points sit just off to the right of each column. The y-values sit right in the middle of the three highlighted series (and constructed via a formula in the cell). The two have the value names (30% and 50%) and are entered in those cells via formula.

For the seven “Segment” labels to the left of the chart (under the “Label Scatters” header), the x-values are set to sit in the position to the left of the chart, and the y-values are equal to the Segments values.

You can see the scatterplot points in this version. I add the data points and then the labels, positioning them to the right or left of the point, as desired. I then go back and set the Marker Style to No Marker to hide the marker leaving just the label.

So that’s it, really. Nothing too spectacular. I just used scatterplots to encode the data on the chart.

Do you have an alternative way to make this chart? Please let me know on Twitter or in the comment box below.

Oh, and be sure to listen to my interview with Cole about her new book on Episode #24 of The PolicyViz Podcast.

4 Comments
-->

Note

Office 365 ProPlus is being renamed to Microsoft 365 Apps for enterprise. For more information about this change, read this blog post.

Symptoms

One or more toolbars are missing and cannot be added in Microsoft Excel for Mac.

Cause

There are two possible causes of this behavior:

  • The oval button in the upper-right corner of the document was clicked. This button 'toggles' the display of toolbars on and off.
  • There is an issue with Excel preferences.

Resolution

To resolve this issue, use the following methods in order.

Method 1: Make sure that toolbar display is not turned off

  1. In the upper-right corner of the Excel window, click the oval button.

    Note

    When this button is clicked, the toolbars are hidden (in any Microsoft Office for Mac application). A second click causes the toolbars to be displayed.

  2. If the toolbars reappear, quit Excel, and then restart Excel to make sure that the appropriate toolbars are displayed.

If Method 1 did not resolve the problem, try Method 2.

Method 2: Remove the Excel preferences

Step 1: Quit all applications

To quit active applications, follow these steps:

  1. On the Apple menu, click Force Quit.
  2. Select an application in the 'Force Quit Applications' window.
  3. Click Force Quit.
  4. Repeate the previous steps until you quit all active applications.

Warning Smackdown vs raw 2007 ps2 torrent iso images.

When an application is force quit, any unsaved changes to open documents are not saved.

Step 2: Remove the Excel Preferences

To remove the Excel preferences, follow these steps.

  1. Quit all Microsoft Office for Mac applications.

  2. On the Go menu, click Home.

  3. Open Library.

    Note

    The Library folder is hidden in MAC OS X Lion. To display this folder, hold down the OPTION key while you click the Go menu.

  4. Open the Preferences folder. Click View, click Arrange by, and then select Name.

  5. Look for a file that is named com.microsoft.Excel.plist.

  6. If you locate the file, drag the file to the desktop. If you cannot locate the file, the application is using the default preferences.

  7. If you locate the file and move it to the desktop, start Excel, and check whether the problem still occurs. If the problem still occurs, quit Excel, and restore the file to its original location. Then, go to the next step. If the problem seems to be resolved, you can move the com.microsoft.Excel.plist file to the trash.

  8. Quit all Office for Mac applications.

    Assistant

  9. On the Go menu, click Home.

  10. Open Library.

    Note

    The Library folder is hidden in MAC OS X Lion. To display this folder, hold down the OPTION key while you click the Go menu.

  11. Open the Preferences folder.

  12. Open the Microsoft Folder.

  13. Look for a file that is named com.microsoft.Excel.prefs.plist.

  14. If you locate the file, move it to the desktop. If cannot locate the file, the application is using the default preferences.

  15. If you locate the file and move it to the desktop, start Excel, and then check whether the problem still occurs. If the problem still occurs, quit Excel, and restore the file to its original location. Then, go to the next step. If the problem seems to be resolved, you can move the com.microsoft.Excel.prefs.plist file to the trash.

  16. Close all Office applications.

  17. On the Go menu, click Home.

  18. Open Library.

    Note

    The Library folder is hidden in MAC OS X Lion. To display this folder, hold down the OPTION key while you click the Go menu.

  19. Open the Preferences folder.

  20. Open the Microsoft Folder.

  21. Open the Office 2008 or Office 2011 folder.

  22. Look for a file that is named Excel Toolbars (12) or Microsoft Excel Toolbars.

  23. If you locate the file, move it to the desktop. If you cannot locate the file, the application is using the default preferences.

If you locate the file and move it to the desktop, start Excel, and check whether the problem still occurs. If the problem still occurs, quit Excel, and restore the file to its original location. If the problem seems to be resolved, you can move the Excel Toolbars (12) file or the Microsoft Excel Toolbars to the trash.

Note

If the problem still occurs after you follow these steps, the problem is not related to these files. If the problem no longer occurs, one of these files was causing the problem. If this is the case, restore the files to their original location one at a time. Test the application after you restore each file. Continue to do this until the problem occurs again. When the problem recurs, you can then assume that it is caused by the last file that you restored. Drag that file to the trash.