The bigger and uglier your Excel spreadsheet gets, the more you need to use certain features or tricks to keep a handle on the data. The seven features covered here will help you navigate, organize, and readjust your spreadsheet with as little hassle as possible.
1. Split Screen
One of the most helpful features for large spreadsheets is the Split Screen command. The Split Screen allows you to view two, three, or four windows of your spreadsheet. Use this feature to work on one section of your spreadsheet while you view another section; or use it to compare (side by side) two sections of the spreadsheet. Once you try it, you’ll find lots of reasons to use it.
a. First, position your cursor where you’d like the screen to split. For example, if you want to divide the screen into four equal sections, position the cursor in the center of the spreadsheet.
b. Next, select View > Split. Notice the screen splits into four equal sections.
c. If you want to move the split, position your cursor at the apex of the split bars. When the cross with arrow points appears, click and hold, then drag the cross with arrow points across the screen until the screen is divided to your satisfaction.
d. To remove the Split, click View > Split (again).
JD Sartain Use View > Split to split or unsplit a screen in Excel.
2. Freeze Frames
The other great feature for large spreadsheets is Freeze Frames. People generally freeze frames so they can see the column headers as they scroll down the page, or the first row as they scroll across, as they usually contain the spreadsheet’s unique fields such as client name, part number, or item number. Use the following instructions to freeze columns A and B (first and last name) and row 1, the field names (column headers).
a. Position your cursor on cell C2.
b. Click View > Freeze Frames > Freeze Frames. Notice that Excel inserts a thin line below row 1 and to the right of column B.
c. Cursor down, and all the rows scroll up except row 1. Cursor right, and columns A and B are stationary, while the remaining columns move to the left.
d. Now when you update the fees in column K, you can see the names of the individuals who owe those fees.
Note: You can also choose to just Freeze First Row or Freeze First Column.
e. To unfreeze frames, click View > Freeze Frames > Unfreeze Frames.
JD Sartain Use Freeze Frames to keep key columns and rows in view.
3. Quickly insert or delete columns and rows
Generally, if you want to insert or delete columns and rows, you position your cursor to the right of the column or just below the row where you want the new column or row inserted (columns insert to the left, and rows insert above, the cursor). For deleting, obviously, you place the cursor on the column or row for deletion.
a. Next you select Home > Insert > Rows (or Columns). You can also insert cells or additional sheets here. Deleting works the same way. Home > Delete > Rows (or Columns, Cells, Sheet).
b. Another, quicker way to perform this same function: Position the cursor on the column letter (such as column D) or the row letter (such as row 5), and when the black arrow appears, right-click.
c. From the drop-down menu select Insert or Delete, and the columns or rows drop in or disappear instantly.
JD Sartain Excel offers some shortcuts for inserting or deleting columns and rows.
4. Quick Cut and Paste
a. Move your cursor to one or several cells that contain data you’d like to move, and select those cells.
b. Position the cursor over the top-left corner of the first cell in the range (or the top-right, or bottom-left). When the cursor changes to a cross with arrow points, hold down the left mouse button and drag the cells to the new location, then release the mouse button.
Note: You cannot use the bottom-right cell, which has a tiny green square over the corner. This is an access link to the Quick Analysis Tool (notice the icon just below).
JD Sartain Cut and Paste data quickly with these tricks.
5. Format Painter
Format Painter is a great little treasure. You know what a hassle it is to constantly format and reformat cells. Use this feature to do it automatically in seconds.
a. Move to a cell that contains the format you want to use (such as K2).
b. Click the Format Painter icon (it looks like a paintbrush) in the Clipboard group under the Home tab.
c. Cursor to the cell, cells, column, or row that you want to copy that format to—in this case, currency.
d. Place your cursor—which becomes a fat plus sign with a paintbrush beside it—on the first cell in column L (L2), then drag the highlight down through L22 and release the mouse button.
Note that all the cells in this column range are now formatted for currency.
JD Satain Format Painter takes the pain out of formatting large blocks of a spreadsheet.
6. One corner click
a. Another fast and easy tip that selects the entire spreadsheet, not just the cells that contain data, is the small green arrow in the top-left corner, between the row numbers and column letters. Click this arrow once, and Excel selects everything, from cell A1 to cell XFD1048576.
b. Typing Ctrl+A also selects all but, in this case, all means just the cells that contain data. Press Ctrl+A twice, however, and it selects the entire spreadsheet.
JD Sartain 06 One Corner Click to Select Entire Spreadsheet
7. Bonus tip: Leading zeroes
And last, for our bonus tip: how to enter numbers with leading zeros, such as zip codes that begin with zeros. Just enter an apostrophe in front of the number; for example: ‘02120.
Notice that now there’s a green triangle in the top-left corner of every cell where you’ve made this change. That’s because introducing the apostrophe could create problems if you try to use that number in a function or formula, so Excel wants to confirm your intent.
a. To find out your options, click the green triangle. A warning-sign icon (a yellow diamond with an exclamation point) will appear next to the cell.
b. Place your cursor on the warning icon, then click the down-arrow that appears on the right side.
c. Choose Ignore Error from the drop-down menu. Note; however, that this removes the mark only from that specific cell.
d. To remove all the green triangles in this column, select File > Options > Formulas. Scroll down to the Error Checking section and uncheck the checkmark in the box that says Enable Background Error Checking. And just like that, all the green triangles are gone.
JD Sartain How to handle leading zeroes in Excel