Keep Rows In Place With Freeze Panes

When you have a lot of data in your spreadsheet, it can be difficult to keep track of what you’re looking at. If you have ten different columns, for example, and you scroll down, you might not remember which column is which. So you have to scroll back up, remind yourself of the column order, and then scroll back down to where you were (if you can remember).

Freezing panes lets you keep a few rows in place while you scroll, making this process much easier.

To freeze a row (or as many rows as you like), open the View tab and click Freeze Panes. You can freeze based on your selection (just highlight the rows of columns you want to freeze) or freeze the top row or leftmost column:

Excel Freeze Panes

Now, when you scroll, you can keep some information locked in place so you don’t lose it.

Use Function References

The number of functions available in Excel is absolutely mind-boggling. There are the basics, like SUM, AVERAGE, and COUNT, but there are also advanced financial, statistical, and logical functions as well.

How do you keep them all straight? Or, more to the point for beginners, learn them in the first place?

When you need information on a function, Excel’s built-in function references are your best friend.

Open the Formula tab, and you’ll see a number of dropdown menus with category names. Click the function you want, and Excel will insert it into a cell and tell you what sort of information you’ll need to populate it with:

Excel Funtion Help

This is especially useful when a function takes many different arguments. As you can see in the image above, Excel’s prompts are useful in helping you figure out which pieces of information go where.

Quickly Sort And Filter Data

Excel’s ability to sort and filter data is one of its most useful features. If you have dozens, hundreds, or even thousands of pieces of information in a spreadsheet, limiting what you see to a smaller subset makes working with it much easier.

To enable filtering, go to the Data tab and click Filter. You’ll see arrows show up at the top of the columns in your spreadsheet. To sort or filter your data, just click one of those arrows. You’ll be presented with a number of options:

Excel Filter Options

The first few options let you sort smallest to largest or largest to smallest (Excel will change these to A-to-Z and Z-to-A alphabetization if you have text data in your column). That will sort the entire spreadsheet based on the values in a specific column.

To view only rows that contain a specific value in the selected column, remove the checkmark next to the values you don’t want to see and click OK.

Any value that has a checkmark next to it when you click OK will remain visible, and the others will be hidden.

You can do a lot of other things with Excel’s filtering capabilities, but this is a great place to start.

Use Conditional Formatting

Highlighting cells, changing the text color, adding borders, and other formatting tweaks can help certain pieces of information stand out. But adding those formats manually can take a long time. Conditional formatting lets you automatically apply formats to certain cells.

For example, you might want to highlight every cell with a value above $60,000 in green. With conditional formatting, you can do that.

In the Home tab of the Ribbon, click Conditional Formatting, and you’ll be presented with a number of options, including Greater Than, Less Than, Between, Top 10%, Bottom 10%, Above Average, and many more:

Excel Conditional Formatting

You can do a lot of customization and define more specific conditions from the New Rule dialog, accessible at the bottom of the Conditional Formatting menu.

But in the beginning, the predefined conditions will likely get you the formatting you want.

Customize The Status Bar

The status bar runs across the bottom of the Excel window, and it’s easy to overlook. But it can actually save you a ton of time.

When you highlight a selection of data, you’ll see that the status bar shows you some information about it:

Excel Status Bar

In our example, it displays the average, count, and sum of the selected cells. That’s a lot of information that you no longer need to use functions for.

If you right-click the status bar, you can customize which pieces of information are displayed. There are a lot of options, and you can actually display a lot of information in a small space. If you often make a specific calculation on your data, you can have it immediately displayed in the status bar.

Keep Experimenting

These five tips will help you work more efficiently when you’re just getting started with Excel, but with the app’s power, you’ll always be learning something new. Whenever you have a problem, run a search online or start pushing buttons to see what happens. You just might accidentally find a very elegant solution to what you thought was a difficult problem!

About the author

TechDissected

I'm the guy who makes the site look good with my amazing looks. I'm a very curious individual and I'm always looking to learn new things when it comes to anything and everything tech related. I might look like a bot, but always remember, looks can be and probably are deceiving. Posts by this user/account are not their original work. They are work from past authors who are no longer with the site or possibly even guest posts or sponsored posts.