Spend Less Time Preparing Your Data
Do you spend a lot of time cleaning up your data before you create a chart? You can save time by utilizing the chart data sheet more effectively.
Horizontal Stacked Bar Chart Example
This chart from our Chart of the Week series shows the largest over-budget construction projects. It's a good example of how to use the data sheet to reduce the time you spend on data manipulation.
The data sheet for this chart includes the Original Budget and Final Cost columns which are used to calculate % Over Budget. It also includes project timing data (Start, Deadline and End columns) which are used to calculate the Years Late column. The calculations are done in the data sheet. So, the columns are necessary but we do not want them to appear on the chart.
You can see below that the data for this chart is transposed in the data sheet. The project names (e.g. Sochi Olympics) are shown as rows, but we want them to be bars on the chart. You do not need to manually transpose the data. Simply click on the Switch Bars and Series icon (highlighted in blue in the upper right corner) and Mekko Graphics will make the switch without altering the data sheet.
To remove the data used in the calculations from the chart, click on Bars and Series in the Mekko Graphics ribbon. Uncheck the boxes under the Chart column for the data you want to exclude from the chart. Check the boxes under the Data column for the data you want to show in columns next to the chart (% Over Budget and Years Late). Format the numbers in the data columns by using the dropdown menu in the Number Format column.
Key Takeaways
- Do your calculations directly in the chart data sheet to save time
- You do not need to remove data from the data sheet to exclude it from your chart
- Use Switch Bars and Series in the data sheet to quickly modify your data structure
Bonus Tip
Notice that the bar totals on the chart are shown in billions but the data sheet is in actual dollars. Choose Chart from the Mekko Graphics ribbon and go to the Values tab. Under Data Scale, choose a value of 9 to convert actual dollars to billions on the chart. This makes the chart easier to read, but it does not change the underlying data.
Bar Mekko Chart in Excel Example
Leveraging the data sheet also comes in handy when you have more data than you can practically plot on the chart. In this bar mekko chart in Excel, the data was copied from a National Highway and Traffic Safety Administration (NHSTA) report. It was easier to include all of the cities shown in the report. There are 34 cities included in the data, but a bar mekko chart with all cities would be unreadable. Again, we used Switch Bars and Series (shown in the Mekko Graphics Edit Chart ribbon) to create this chart.
To remove cities from the chart, go to Bars and Series. Uncheck the box under the Chart column for the cities you want to exclude.
Bonus Tip
Sort the bars (highest on left) by choosing Chart from the Mekko Graphics ribbon and going to the Sort tab. The chart will change but the underlying data remains the same.
