The Easiest Way to Simplify a Complex Spreadsheet with Functions

Spreadsheets are a fantastic way to track all kinds of information. If there is something you want to track, there is often a great way to accomplish this by organizing the data in a spreadsheet. Unfortunately, because there are so many things a spreadsheet application can do, what began as a simple spreadsheet may expand into something that would be challenging for someone else to understand.

This post shows how you can quickly and easily simplify a complex spreadsheet that has functions, without removing or altering those functions.

Below is a video demonstrating the how to simplify a spreadsheet.

The Easiest Way to Simplify a Complex Spreadsheet with Functions

The trick to simplifying spreadsheets will work with any type of spreadsheet, regardless of the type of data in them. This post specifically discusses how to simplify the complexity of spreadsheets with functions because of the dependencies created by functions.

For example, if you want to track the progress of something over time, this often includes comparisons and formulas that are dependent upon several rows or columns of data. Over time, and with the addition of more information and formulas, a spreadsheet can grow and become overwhelmingly complex.

If you have a spreadsheet that you use, and want or need to share it with someone else, the extra information is likely to confuse someone less familiar with it. This is partially because everyone has a different approach to organizing. Consider every home you have ever entered - if you were the one arranging the furniture, you would likely do it differently. Data is very much the same way. Each person setting up the data has a different idea of how to do it best.

In our example we will use a spreadsheet set up to be a two year budget planning sheet.

Example of a budget planning sheet with lots of data entered into it.

Like a document with too much data, you can remove information from a spreadsheet. However, when you remove columns or rows that contain data used in functions, the dependency on the deleted data causes the functions to break. For example, if you have a column that sums several other columns and one is removed, the function will break because it is looking for data it cannot find.

Once a column was removed, all formulas using that column then broke.

Luckily, there is an easy way to simplify a spreadsheet with functions without causing any issues to them. The way to do this is by using the hide feature on any number of rows or columns. This allows you to hide them and the data in them without disturbing the actual data. This works because the information is still there, it just is not being displayed, but when a function references those cells it can find the data which is why those functions still work.

There are a couple of things to consider when hiding rows and columns. First, using this feature is most helpful with spreadsheets that have data scrolling off the screen, is slightly too big to fit onto a page when printing, to hide confidential or proprietary information, or when there is so much data most recipients would not understand the data presented.

Second, hiding the data does not remove or alter it in any way. However, it is important to note that if you hide rows or columns and send the spreadsheet to others, they can unhide the data. This means you do not want to use the hide data feature to share a spreadsheet with confidential or proprietary information of any type. Instead, hide rows and columns and save the file as a .pdf file so the end user only sees what you want them to see.

Lastly, when you need to enter data again, or if you want someone else to enter data, they can quickly unhide the previously hidden rows and columns to make the data viewable again.

To simplify the spreadsheet without breaking any functions or losing any data:

Right-click on a row or column and select "Hide" from the popup menu. NOTE: Be sure to click on the row number or column letter to select the entire row or column which allows you to access the hide/unhide menu.
Continue to hide rows or columns as desired.
If you need to keep any of the hidden data private, save the spreadsheet as a .pdf file.

Three columns have been hidden and yet all the functions still work.

From here you can print the spreadsheet without all of the extra data showing or save it and share with someone who can more quickly understand what each piece of data means.

To restore hidden rows or columns move your pointer between two rows or columns that surround a hidden row or column. You should see three lines separating the rows or columns rather than a single line. Right-click and select "Unhide". Keep in mind it can be a little challenging to figure out where to click at first, but once you figure out where to right-click it is quick.

Hiding rows or columns is a feature that can be used with any spreadsheet. However, it is an extremely helpful tool when you are working with a spreadsheet with functions in it. This is because if you remove a row or column from a spreadsheet that is part of a formula, that formula and any other dependent formulas break. Instead of changing the data, simplify your spreadsheet by hiding some of the rows or columns as this feature maintains all of your original data and therefore does not affect functions!

As always, too much data can be overwhelming and knowing how best to present it is important!