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:
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:
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:
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:
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:
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.
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!