10 Excel Tips You Should Know
The HBR article, 10 Excel Functions Everyone Should Know, inspired this list of 10 of the most useful features for creating Mekko Graphics charts in Excel.
1. Format All Labels
The default chart size in Excel is smaller than in PowerPoint, so you may have less space for labels. You can make the chart larger, but sometimes you want both the data and chart to be visible on the screen. To easily make your labels fit in the limited space, right click in the white space of the chart and choose Select All Labels. Then, right click on one of the labels and choose a smaller font size, like 8 or 9.
2. Switch Bars and Series
There’s no need to transpose the data in your spreadsheet to make an Excel chart. One of the most frequent use cases of a cascade/waterfall chart is to show the line items on an income statement. In this case, the line items are rows, but you want those to be bars in the cascade chart. Simply click on Switch Bars and Series in the Mekko Graphics ribbon and the chart will make the adjustment but your data stays the same.
3. Data Scaling
Use this to create a cleaner looking chart. For example, in this horizontal stacked bar chart, the data is shown in detail, but the bar totals are shown in billions. To scale your data, choose Chart from the Mekko Graphics ribbon and click on the Values tab. Scroll down to Data Scale and choose a value, in this case, the value is 9.
4. Create a Table Next to Your Chart
Use data columns to create a table with totals next to your chart. Choose Bars and Series from the Mekko Graphics ribbon and click on the Bars tab. Uncheck the boxes in the Chart column and check the boxes in the Data column to show your data in 2 columns. In the Total Labels column, add the labels you want to show for your data columns. In this case, we used Total and 5%.
5. Make a Series a Line, Net Line or Data Row
You can easily change a bar to a line, net line or data row in your chart. In this chart, the operating income series is shown as a net line (the net between the positive and negative values in the stacked bar) and the gross margin and operating margin are shown as data rows. The gross margin and operating margin series could have been shown as lines plotted on a 2Y axis. Under Bars and Series on the Series tab, you can select how you want each series to appear on the chart.
6. Reduce The Number of Bars
If you have a large dataset for a bar chart, you may want to limit the number of bars shown to make the chart readable. You may also want to reduce the number of bars shown if you're trying to emphasize changes between bars. In this bar chart, I chose to show every other year to highlight the mix change in smartphone sales. Under Bars and Series on the Bars tab, I unchecked the box under the Chart column for 2009 and 2011.
7. Reduce the Number of Labels
If you want to show all of your data on the chart, but do not have space for all bar labels, you can use the check boxes in the Bar Labels column under Bars and Series to choose which labels to show. Or, you can select a value from the Show Every Nth Bar Label dropdown. In the stock price line chart below, I chose to show every 10th label and I also checked the Bar Label box for the last series.
8. Make a Stacked Cluster Bar Chart
If you'd like to combine a stacked bar and a cluster bar chart, you can do it using Mekko Graphics. First, insert a stacked bar chart into your worksheet. If you want labels for the cluster, add them in a row of the data. Then, make this series as a data row under Bars and Series. To cluster the bars, go to Bars and Series, Bars tab, Options tab and select a value for Cluster By under Bar Gaps.
9. Use Conditional Formatting for Chart Colors
You can use the conditional formatting features in Excel and then apply these colors to your chart. Cascade charts often use a coloring method where positive bars are green and negative bars are red. In the income statement cascade below, I set the fill colors of the cells using conditional formatting. I set 2 rules under Conditional Formatting, Highlight Cell Rules. The first was greater than 0 (green cell fill) and the second was less than 0 (red cell fill). To apply these colors to the cascade chart, I chose Chart from the Mekko Graphics ribbon. I clicked on the Style tab of the Format Chart task pane and checked the box "Apply Cell Colors from Spreadsheet" under Color.
In a change cascade chart, you may want to use conditional formatting for just the change bars. In the chart below, I applied the conditional formatting rules to the change bars, but not the first and last bars (total bars). Then, I applied these colors to the chart under Chart, Style tab, Apply Cell Colors from Spreadsheet.
10. Set Your Formats Once and Use Them For All Charts
To save time when creating charts in Excel, set your preferences, including colors, fonts and margins in the Preference Manager. The Preference Manager controls the formatting for charts made in using Mekko Graphics in both PowerPoint and Excel. Open PowerPoint and choose Preference Manager from the Mekko Graphics ribbon. On the Themes (Color Palettes) tab, choose your default color palette. Select Edit to add or remove colors from the palette and set the draw order. On the Settings tab, you can set a wide range of style preferences to apply to your charts. Hit OK to save your settings. When you create a new chart, it will reflect these preferences.
If you have an old chart or a chart from a colleague, you can update it using your Preference Manager settings. First, choose Color from the Mekko Graphics ribbon and select Reset Colors. Then, select Apply PM Settings from the Mekko Graphics ribbon to apply your colors and other preferences to the chart.
If you have used these tips to create an awesome Excel chart, you can re-use that chart with different data. Read How do I replicate an Excel chart but change the data? to learn more.
