Operations Management & Spreadsheets - Segregating Data


When they create a spreadsheet most people tend to put as much information as possible on one spreadsheet tab.


The result is a user interface that is cluttered and confusing. Naturally, the user will feel resistant to looking at the spreadsheet.


Putting all information on one spreadsheet tab also forces the user to scroll vertically or horizontally to see all the information. This makes it harder for users to focus on the analysis because they have to worry about remembering key data as they scroll all over the spreadsheet.


So you end up with a spreadsheet that is harder to use, harder to understand, and thus less useful as a tool.


Also, keep in mind that increasing the amount of information on a spreadsheet tab increases the complexity of the tab. There is a direct correlation between the risk of error and the complexity of a spreadsheet.


To minimize the complexity of the spreadsheet tab, reduce the risk of error and ensure the spreadsheet is actually a useful management tool, it is best to segregate the data. This means each component that is used to create to create the management report should be on its own spreadsheet tab.


What are the components that go into creating a management report? Here's a list:


  • Data

  • Calculations

  • Assumptions

  • Variables

  • Constants

  • Charts

  • Detailed data

  • Summary data


To provide additional clarity, it is a good idea to arrange the tabs in a logical order:

  • Data

  • Assumptions

  • Variables

  • Constants

  • Calculations

  • Detailed report

  • Summary report

  • Charts


Arranging the tabs in this way has several advantages:


  1. It is easier for the user to view the data. Breaking down the information into components means there is less data on each spreadsheet tab, so it is not necessary for the user to scroll vertically or horizontally to see all of the data.

  2. Combining all similar elements on the same worksheet makes it easier for the user to understand the data.

  3. Arranging the tabs in sequence provides an insight into how you reached your conclusions. This makes your job easier if you need to modify the spreadsheet in the future. It also gives the user insight into the logic behind the spreadsheet.

0 views