5 Tips To Simplify Your Excel Models For Organizational Data Analysis

By John Dillard

Figure 1 Organizational Data AnalysisWhen you’re conducting organizational data analysis, working with spreadsheets in a program like Microsoft Excel is often a hassle and source of dread. Fortunately, it’s surprisingly simple to create data visualization models in Excel that are much more aesthetically pleasing – and easier to use – in the data analysis process

1. Use Hyperlinked Cells

You and your colleagues waste minutes on every spreadsheet searching for the correct cell or sheet that you need to reference or edit. Yet, it doesn’t have to be that way: Excel makes it easy to create more intuitive navigation mechanisms for your data analysis tools. 

Figure 2 Organizational Data Analysis

Save yourself time in the future by getting into the habit of naming important cells on your Excel spreadsheet (see Figure 2), then create hyperlinks within your sheets that guide you (and other users) in an intuitive manner through your data.

2. Hide Data You Don’t Need To See

The worst spreadsheets bog down your organizational data analysis by presenting too much information at once, making it difficult to focus your attention on any one area. When you find yourself getting lost in the numbers, review your Excel spreadsheet, component by component, and judge whether every area actually needs to be visible at all times. 

Then, use Excel’s built-in functions to hide the rows, columns or sheets that don’t need to be seen. Common information to hide from your data visualization tools includes any lookup tables or assumption numbers that feed your models, but don’t ever change.

3. Highlight Data You Want To See

As with any data management tool, the most important information in your Excel workbook should appear first when you open the file. One easy way to execute this is to include a dashboard sheet that summarizes your key data points and trends (see Figure 3).

Figure 3 Organizational Data Analysis

Also, remember to keep your key information in a large, easy-to-read font, and use conditional formatting to assign highlights and color scales to important cells. By employing presentation tactics like these, your organizational data analysis goes much more smoothly.

4. Use A Template

Don’t waste time recreating your Excel spreadsheets from the ground up every single time you need to analyze your organizational data. Instead, use one of the thousands of professional and user-developed templates already built into Microsoft Excel. Just navigate to File > New > New From Template to explore the different data analysis tools and templates available (see Figure 4).

Figure 4 Organizational Data AnalysisWhile you may not find a template that is a perfect fit for your data analysis process, you are still able to easily copy components and formatting from a given template to make your workbook better looking and more functional.

5. Build It Right The First Time

Excel spreadsheets have a tendency to expand and evolve over time, meaning your time investment in adding to them – or trying to extract meaningful data analysis from them – easily gets out of hand. But, with the proper upfront time investment to complete your data visualization models correctly the first time, you save yourself time and effort down the road.

Here are a few best practices to keep in mind when formatting your organizational data analysis tools in the future:

  • Use relative and absolute cell references most effectively (see Figure 5). Use the <$> sign to denote absolute cells you want to reference in the same row, column or both (e.g., a lookup table, or a number used in more than one formula). And use relative cell references when creating a formula you may want to copy into another section or spreadsheet.
  • Use Microsoft Excel’s “Format as table” functionality to add rows and columns to a table automatically. This functionality provides easy sorting and filtering for efficient organizational data analysis.
  • Write raw numbers only once. If you need the same number more than once, reference the original cell. This practice updates the number in every location across the spreadsheet and ensures that your data remains consistent.

Figure 5 Organizational Data AnalysisWhile Excel spreadsheets may not be your favorite data analysis tool, these five simple tips vastly improve your data visualization models with minimal effort. With the right investment of time and energy, your organizational data analysis has never been easier.

Need more ideas on how to harness your organizational data for in-depth, decision-driving data analysis? Click below to download a free tip sheet from Big Sky Associates and learn to leverage your data to drive organizational success.

5 Tips for Security Data Analysis