Excel: 3 Features to Handle a Spreadsheet Full of Data

When using Microsoft Excel, you often have to deal with huge tables full of data, and things can get pretty hard to manage. In this short tutorial we'll see 3 Excel features that can help you better handle large spreadsheets.

Here's the big table that we are going to work with:

a huge tables full of data in excel

Take a deep breath, and let's go!

Freeze Panes

Once you start to scroll vertically or horizontally in your dataset, it's really easy to get lost. One simple solution is to freeze the most important rows and columns so that they are always visible on the screen.

First we have to decide which rows/columns we want to freeze. In our example we might want to see the top row and the first 2 columns. To do so, you have to select the first cell that should not be freezed (in this case it's the cell C2), then click on the "freeze panes" button in the "view" tab.

how to use freeze pane in excel

And now when we move around, the most important information is always visible.

a table with freezed rows and columns in excel

Hide Columns

The table we are working with has 11 columns. Do we really need all of them? Probably not. But we don't want to delete them either, they can become useful later. That's why there's a cool feature in Excel to temporary hide columns.

Simply select all the columns you want to remove by clicking on them while pressing ctrl (or on a Mac). Then right click to select "hide".

hide columns in a excel table

To undo this, select the columns right before and after the ones that are hidden, then right click to select "unhide".

unhide columns in a excel table

Filter Rows

The last thing we should do is to filter all this information, so we can focus on the rows we actually need.

Make sure the active cell is somewhere in the table, and go in the "data" tab to click the "filter" button. You'll see all the cells in the top row get a small arrow next to them.

how to filter rows in excel

When you click on the small arrow, you can select exactly what you want to see. In the example below I'm interested only in customers from France who ordered more than $75 of goods.

a table with filtered rows in excel

Conclusion

In this tutorial we managed to transform a huge spreadsheet into a small table containing exactly the information we needed. So this is going to be much easier to parse and analyze. Pretty cool, huh?

result of transforming a huge spreadsheet into a small table

Here's a quick summary of what we covered:

Other articles you might like on ExcelFrog.com