Working Faster and Better in Microsoft Excel

Microsoft Excel is a powerful tool, and using a few shortcuts and tips can make your work move faster.  Today we’ll look at a few Excel tools to improve your experience in working with data.

Microsoft Excel is a powerful tool for analyzing data, and using a few shortcuts and tips can make your work move faster.  Today we’ll look at a few Excel tools to improve your experience in working with data.

ADJUSTING CELL SIZES is almost always necessary when working with data that has been imported from other sources.  The very top left box above the first row and to the left of the first column is called the crux.  Clicking in the crux selects the entire worksheet.  Hover your mouse between rows or between columns until it changes to bi-directional arrows, then double-click.  All the rows and all the columns will auto size to fit the contents of the rows and columns.

FREEZE PANES allow you to keep certain data visible when scrolling through large worksheets. In the View tab, click on the icon labeled “Freeze Panes.”  You can custom select which cells to freeze, but since labels are normally across the top row and in the first column, those will be the most useful items.

DATA ALIGNMENT allows you to “wrap” text, or to use multiple lines of text within one cell.  If a column contains a long string of text, it can run the rest of your columns off of your print area, or it can make it more cumbersome to read your worksheet.  Highlight a column or row, right-click and select “Format Cells…”, select the Alignment tab, and in the middle section under Text control, select Wrap text, then click OK.  You can then manually size the column to fit your needs.  However, Excel won’t automatically adjust the row height after expanding a column, so to avoid having white space at the top of the cell, double-click on the bottom of its row, and the row will right-size itself.

COPYING AND PASTING sound easy enough, using CTRL+C to copy a cell and CTRL+V to paste its contents, but immediately after the paste has completed, Excel presents a set of formatting options.  Additionally, the lower right corner of the cell is a heavy square.  Hover your cursor over that square and it turns into a “+”.  Clicking and dragging on that + will highlight the selected cells, and releasing the mouse button presents a small box at the lower right of the last cell selected.  This box offers AutoFill options, and using one of the selections can help you quickly extend a data series.  This is particularly useful for establishing a range of dates or other numbers that are sequentially ordered.

The F8 key extends the selection range using the keyboard instead of dragging with the mouse. Hit the F8 key and then use your arrow keys to extend your selection from the currently selected cell.  If you need to select a large range, you can use the page down key.  You are only able to select one vertical and one horizontal direction, though.  In other words, you can’t select a cell and then select cells both up and down from it using the F8 extension key, or both left and right, but you can go up and right, for example.

You can also select non-contiguous data as well.  Select your first cell, then hold the CTRL key while selecting more cells.  This works with whole rows and columns as well.

BEFORE YOU PRINT, you can see how many pages your data will require using Page Break Preview.  Select the View tab from the ribbon, and select Page Break Preview from the leftmost section.  You can see which data will appear on each printed page.  The power in this view is that you can drag the page breaks.  Excel will resize the worksheet to fit, and you can see the effect immediately.  Hover your mouse over one of the break lines until the cursor turns into bi-directional arrows.  Click and drag the break to where you want it.  This is helpful when you need to keep data together, and to prevent a single row or column from printing onto a new page by itself.

In the Page Layout tab, the second section from the left, Page Setup, has a little arrow in the lower right corner.  Clicking that arrow brings up a dialog box with all the options you see in that section of the ribbon, but also much more.  You can play around with sizes, but it is more useful to understand and tell Excel how you want your worksheet to look when you print it.  You can select one page wide, which means that your data won’t break up over two sheets.  However, Excel may not automatically adjust the vertical sizing.  You will want to allow Excel to move down onto as many pages as it takes, so you can set the “tall” number to several pages more than you think you will need.

You can create a header to appear at the top of each page, a footer to appear at the bottom of each page, or both, in the Header/Footer tab of that same dialog box.  Both the header and footer options offer prepopulated options in the pull-down box, or you can create a custom header or footer.  Even selecting the custom option offers several automated choices, or you can type your own text into the left, center, or right sections.  If you want something to appear only on the first page, checking the “Different First Page” box adds a First Page Header (or Footer) tab to the Custom Header (or Footer) dialog box, and you’ll specify what should appear on the first page, and what, if anything should appear on all other pages.

The Sheet tab presents options to repeat rows and columns.  Click the icon to the right of “Rows to repeat at top”, select your label row.  If your data is going to span more than one page horizontally, you’ll want to click the icon to the right of “Columns to repeat at left” and select your label column.

Finally, Excel defaults to a nice clean presentation of your data, but large datasets can be difficult to follow, especially when the print is small.  In the Sheet tab, in the Print section, you’ll find a checkbox for Gridlines.  This simply presents your data as it looks in the spreadsheet, with the lines for the rows and columns.

These are just a few tips to make your data easier to read, work with, and print.  Using these ideas as a starting point, you can explore the other options in the ribbon to discover more of Excel’s power.