Microsoft Office Tips for Word and Excel

Excel Basics – Navigation and Freeze Panes

What is Microsoft Excel?

This is the first of three posts of knowledgeable information for working with Microsoft Excel. Excel is a spreadsheet program which is included with even the most basic versions of Microsoft Office. Excel is used for calculations, storing and working with financial data, and working with any sort of numbers in a home or office setting. Budgets, depreciation schedules, profit-and-loss statements, accounting statements, account balances, are all typical uses for Excel. I also use it for any information that looks best in a row and column format. I keep an up-to-date copy of the Doctor Who episode list in Excel, for example. Originally, I used the list to keep track of the DVD releases, but now that the entire Classic and New Series are available – I don’t need that primary purpose anymore, but it’s still good to have. Excel is also useful, for example, for lists of comics you have or need. Essentially, Excel is good for lists of highly changeable data.

One thing Excel isn’t is a true relational database. Although you can filter and sort information in Excel, that is not the same thing as being able to run queries a number of different ways, on tables that hold information separately. Microsoft Access is still a good, medium-sized relational database that is included in some versions of Microsoft Office, such as Professional 2010.

Excel Navigation

There are many common ways to move around in an Excel spreadsheet. First, you can simply click on any cell in the spreadsheet to select it. This includes blank cells.

You can use the tab key or arrow keys on your keyboard to move between cells.

To edit a cell, click, then move the mouse to the formula bar and edit in the formula bar.

Highlighted formula bar in Excel

Copy and paste is very easy in Excel, simply click the cell or cells you want to copy, press control and the letter C together (CTRL +C), click where you want to paste the information and press Enter. You do not need to use CTRL + V to paste (as in a word processing program or any other place). You can also drag and fill by clicking on the little box in the lower left corner of the cell, dragging it down several rows then releasing the mouse (this can be turned on or off in Options on the File tab).

Autofill is something you want to be careful of – and you may want to turn it on or off depending on an individual spreadsheet you are working on. Autofill will fill the contents of the cell based on what you have typed previously in the spreadsheet. This means, for example, if you have a sales spreadsheet for red, blue, and yellow balloons – and you are recording the sales, once you’ve typed a few rows of data, the second you type an “r” the cell will fill with “red”, and when you type a “b” the cell will fill with “blue” etc. Which seems all well and good, however, what if you are also recording the salespeople who sell the balloons (to calculate a bonus or commission, perhaps?) and your sales people are: Jane, Joan, Jon, Jim, John, Jill, Justin, Bob, and Mary. Every time you type a “J” Excel will try to autofill the result – without knowing the difference between Joan and Jon or Jim and Jill. Since Excel can autofill based on a single letter, your “J”-heavy sales department might see inaccurate results. It might be best to turn off autofill in this case, and manually type everything. Or use copy and paste. Or even use drag and fill. I find that I use “Autofill” only about half the time, and the rest of the time I have it turned off (again, in “Options” on the “File” tab) – it really doesn’t save that much time.

Quick Tip: Navigation

Having worked as both a temporary and full-time accounting assistant, and a temporary secretary or admin asst, I’ve spent a lot of time working with Excel. For the best and speediest results when editing and updating spreadsheets – use a variety of methods to move around a spreadsheet, especially a long or complicated one; click directly on a cell you need to change, edit in the formula bar, move from cell to cell with the tab key, move around the spreadsheet with the arrow keys.

Freeze Panes

Freeze Panes is a handy feature in Excel that allows you to set part of the spreadsheet (typically the top row or left-most column or columns to not move, even when the rest of the spreadsheet does move. By default, Freeze Panes is found on the View tab in Excel 2010 and later.

Freeze Panes on Excel View Tab

Because I find freezing the top row (or occasionally the first column) to be a very useful feature in Excel, I added the shortcut button to my Excel Quick Access Toolbar. The process to Add Any Command to the Quick Access Toolbar is the same for Excel or Word in Office 2010 or later.

How exactly do you Freeze Panes? And Why?

If you have a long spreadsheet, chances are you have a row at the very top that describes what is in each column (the pictures in this post have used a cleaned-up Access Export of my film list, the top row includes the Director Name, Title, Edition, etc.). But if you are editing or adding data, and the spreadsheet is very long, the top row will disappear quickly. How do you know what goes where?

Sure, it might seem obvious in a spreadsheet like the film list I’ve used as an example – but I’ve worked with extremely complicated, precise spreadsheets, where it’s vital to have information in the correct place. Spreadsheets such as Profit-and-Loss statements, Zero-balance accounting spreadsheets, even checklists with pass/fail tests consisting of five or six (or more) separate items one needs to record “yes” or “no” after looking-up information. It’s much easier to have the guide, in terms of the first row, visible. Freeze panes does that.

Simply place your cursor at the top of the spreadsheet in the first cell under the row to freeze, click the “freeze panes” button, then click “freeze top row”. If you have a spreadsheet set up where the first column doesn’t change and new information in recorded in columns and rows to the right of the first column, choose, “freeze first column”. You can tweak the two presets by using “freeze panes” – just make sure you place your cursor in the correct place. This is helpful if you need to freeze the first column and the top row, or if you have two or more “top rows” that you want to freeze.

 

Freeze Panes in Excel

Freeze Panes is an essential tool for working with Microsoft Excel.

This blog post introduced basic navigation in Microsoft Excel and how to freeze panes to make navigation of long spreadsheets easier. Future blog posts with tips and tricks for using Microsoft Excel will include how to format cells, and how to use sorting and filtering tools.

What would you like to learn about using Microsoft Excel? Feel free to leave me questions in the comments.

Advertisements