My job involves teaching people how to use Microsoft Office. Mostly, I teach classes on Excel, since when we ask what people want to learn, the answer is usually, “Excel, Excel and more Excel.” So we give the people what they want. Now, I don’t know everything about Excel, but I do know a fair amount. And I’m always amazed at which features users are most excited about. Turns out, it’s usually not the most advanced or complex features. In fact, some of Excel’s simplest features get the biggest reaction in our classes. So here I’ve explained how to do 3 things that have the biggest “Wow!” factor for our users. Freeze Panes a.k.a. How do I make my headings stay in place when I scroll through my spreadsheet? Let’s say you’ve set up your spreadsheet so that you have a title at the top of the page, headers at the top of each column, and important names down the first column (see image below). If you have a lot of data, as soon as you start moving down your spreadsheet, you lose the headers at the top. And now it’s so confusing – is the data in cell D145 sales, or cost of sales or what? To lock headers in place, you’ll turn on Freeze Panes. But in order for it to work correctly, you have to highlight the correct cell: 1. To lock only column headers in place, select the cell in column A directly below the headings. In our example below, you would select the cell circled in red, A4, then go to Window > Freeze Panes to lock rows 1 – 3 in place, leaving the column headers in place while you scroll down through your data. 2. To lock only row headers in place, select the cell in row 1 directly to the right of the headings. In our example, select the cell in blue, B1, then go to Window > Freeze Panes to lock column A in place, leaving the Names column in place while you scroll through your data to the right. 3. To lock both column headers and row headers in place, select the cell below and to the right of the headers. In our example, select the cell in green, B4, then go to Window > Freeze Panes to lock column A and rows 1 – 3 in place. When you move right, column A will remain locked. When you move down, rows 1 – 3 will remained locked. If you need to turn Freeze Panes off, just go back to Window > Unfreeze Panes. The keyboard shortcut both to turn on and off Freeze Panes is Alt+W, F. Rotate Text a.k.a. How can I place my headers on an angle?

Using the same data set as above, let’s say that you want to make your table look a little fancier. Or maybe your column headers are waaaaaay wider than the rest of the data in that column. For either issue, you can rotate your text so that it's on an angle, or even completely vertical! Here’s how:

1. Select the cells with the text you want to rotate. 2. Go to Format > Cells (keyboard shortcut: Ctrl+1). 3. Move to the Alignment tab. 4. See the Orientation area on the right? Grab the little red diamond and move it so that the text is oriented the way you want your text to be oriented. 5. Click OK. Voila! AutoFill a.k.a. How can I easily copy a formula down a column? a.k.a. How can I easily complete my series of months, days, dates, etc.? Using the above example again, you’ve got Sales in one column and Cost of Sales in another. Now you want to figure out Gross Margin, which is just Sales – Cost of Sales. If have you have 300 rows of data, you do not want to retype this formula for each row! We often see folks who know how to copy the formula down using the fill handle (below), but did you know that if you double-click the fill handle, it will automatically copy the formula down to the last row of the data to the left? (This is where we get a “Cool!” response.) If you want to learn the keyboard shortcut for copying a formula down a column, check out my article The Best Keyboard Shortcuts You’re Not Using. But wait, there’s more! We’ve all had a time when we needed to enter consecutive months, days of the week, or even just numbers (1,2,3 or 2,4,6) down a column or across a row. For days or months, just type in your first entry, then grab the fill handle and drag. If you have data in the column to the left, double-clicking the fill handle will fill the series all the way down your column. For numbers, enter the start of your number series down your cells. Then select all these cells before dragging. Neat-o. Keyboard shortcut for filling a series 1. Enter the start of your series in cells. 2. Select the cells and all the blank cells to fill (you can use Shift + down arrow). 3. Press Alt+E, I, S. 4. Press Alt+F and then Enter. It’s not as fast as double-clicking the fill handle to start with, but if you combine it with the tricks I show in the other Keyboard Shortcuts article, you can do it with a quickness.