{"id":811,"date":"2016-10-04T09:18:50","date_gmt":"2016-10-04T15:18:50","guid":{"rendered":"https:\/\/www.qimacros.com\/lean-six-sigma-blog\/?p=811"},"modified":"2021-01-27T08:21:18","modified_gmt":"2021-01-27T15:21:18","slug":"nobody-wants-read-spreadsheet","status":"publish","type":"post","link":"https:\/\/www.qimacros.com\/lean-six-sigma-blog\/nobody-wants-read-spreadsheet\/","title":{"rendered":"Nobody Wants to Read Your Spreadsheet!"},"content":{"rendered":"\r\n

Nobody wants to read your spreadsheet!<\/strong> No matter how hard you try to make it pretty, create great labels or whatever, the only person who can read your spreadsheet is a CFO or Excel spreadsheet geek. And the Excel geek is going to tell you 10 ways to make it prettier.<\/p>\r\n

The purpose of data is to provide insights for action, not just report past performance.<\/p>\r\n

How Do I Know That Most Excel Users Try to Make Their Spreadsheets Readable By People?<\/strong><\/p>\r\n

According to Renu Davi, Sr. Program Manager for Excel, Microsoft tracks how people use Excel. The vast majority of the 650 million users use it for lists and reports. How do I know they were made for people? They use Merge and Center to align headings so that people can read them.<\/p>\r\n\r\n\r\n\r\n

\"Excel<\/p>\r\n\r\n\r\n\r\n

 <\/p>\r\n

Look at the spreadsheet below. Row 1 uses merged cells. Merged cells are evil! Merged cells cause all kinds of problems when charting, calculating or pivoting, but over half of all Excel workbooks have them.<\/p>\r\n\r\n\r\n\r\n

\"merge-and-center-heading\"<\/p>\r\n\r\n\r\n\r\n

 <\/p>\r\n

What to do? Use Format Cells \u2013 Center Across Selection instead of Merge & Center:<\/p>\r\n\r\n\r\n\r\n

\"center-across-selection\"<\/p>\r\n\r\n\r\n\r\n

 <\/p>\r\n

What\u2019s Wrong With Most Spreadsheets?<\/strong> The most common mistake is putting data in horizontal rows like the example below. Then, to make it readable, each year is stacked on top of the next year. Looks pretty right?<\/p>\r\n\r\n\r\n\r\n

\"nobody-can-read-this-spreadsheet\"<\/p>\r\n\r\n\r\n\r\n

 <\/p>\r\n

But you can\u2019t chart the data; you can\u2019t Pivot the data; you can\u2019t tell if there are anomalies. And almost everyone will struggle to read it (except you). Is there any actionable data in this sheet? I can\u2019t tell.\u00a0To begin to make sense of this data, it would be nice to see a chart. But if you chart the data as shown, you get a spaghetti chart. And the series aren\u2019t named correctly because the year is in the wrong row.<\/p>\r\n\r\n\r\n\r\n

\"spaghetti-line-chart\"<\/p>\r\n\r\n\r\n\r\n

 <\/p>\r\n

To chart the data, you would need to transpose the data into a single column and convert the dates from a text field, \u201cJan\u201d, to a real date, \u201cJan 14\u201d. Then you could chart the data.<\/p>\r\n\r\n\r\n\r\n

\"nobody-can-read-your-line-chart\"<\/p>\r\n\r\n\r\n\r\n

 <\/p>\r\n

But you can\u2019t really tell if any of these data points are actual signals requiring action or just noise. If, however, you chart the data as a performance chart you can analyze performance:<\/p>\r\n\r\n\r\n\r\n

\"sales-xmr-control-chart\"<\/p>\r\n\r\n\r\n\r\n

 <\/p>\r\n

In this case, sales are flat with an average of 155.7. This process is stable and predictable. All of the points are just common variation, not signals. But sales aren\u2019t improving. Is this okay or does it need improvement? What Else Do People Do That Impacts Usability?<\/strong> They insert summaries into their data. In the example below-left, they are summing by quarter and using simple formulas instead of an Excel function \u2013 Sum(B2:B4). If you have dates, no inserted summaries and use PivotTables, Excel can group this data for you (below-right).<\/p>\r\n\r\n\r\n\r\n

\"nobody-can-read-qtrly-summaries\"<\/p>\r\n\r\n\r\n\r\n

 <\/p>\r\n

Nobody Wants to Read Your Spreadsheet!<\/strong> Trying to make a spreadsheet easier for humans to read is a waste of time. Make your data easier for Excel to pivot, analyze and chart. Turn unreadable spreadsheets into charts that provide clarity about performance and deliver insights for action. Stop creating reports that nobody reads. Start creating a business case for improvement.<\/p>\r\n\r\n\r\n\r\n