Excel Pro Tips for Increased Efficiency February 21, 2018
Excel® is a powerful recordkeeping program that can help you achieve a number of farm management tasks, from making personal budgets and supplemental accounting documents to calculating and recording yield and other production data. It can even help you troubleshoot your accounting software. Despite all that it offers, many of us still don’t use it to its full potential. A new series of CropWatch farm management articles will cover some of the best formulas, tables, conditional formatting, and other processes to help you use Excel more efficiently. This week we’ll look at how to use the Control, Shift, and Arrow keys for fast moving and data selection in a worksheet.
Whether it is a Cash Flow Statement, a Trucking Log Report, or another type of document in Excel, more than likely it spans multiple rows and columns. Using the scroll bar on the right and bottom of the screen can be tedious. Using the scroll wheel on a mouse is fast, but accuracy leaves a bit to be desired. The solution to moving around a spreadsheet with speed and accuracy lies in keyboard shortcuts.
Most users are familiar with using the arrow keys. Hit the Right Arrow and move one cell to the right and hit the Up Arrow and move one cell up. Using other keys with the arrow keys can help users move around a worksheet and work even faster.
Control and Arrows
This combination — holding Control and then hitting an arrow key — gives you the power of movement. It will do one of two things. The first is it will find the first cell in the selected direction with information in it. If the A1 cell was highlighted, but the table did not start until H1, you can hold Control and hit the Right Arrow to move from A1 to H1 in one action.
The second thing this combined action can do is efficiently move the cursor around a set of data. If I am working on a table that goes from A1 to N25, and A1 is my highlighted cell, it can take just two steps to get to N25. Hold Control and hit the Down Arrow to move from A1 to A25, which is the last cell in Column A with data. Then hold Control and hit the Right Arrow to move from A25 to N25, which is the last cell in row 25 with data. The Control and arrow keys make quick work of moving around a spreadsheet.
Shift and Arrows
This combination gives you the power of selection. By holding Shift and then hitting an arrow key, you can select cells in a group. If I want to select A1 through A8 I can hold Shift and then hit the arrow key to add cells to the selected highlighted group. This is a great way to grab a few cells for a copy and paste function, or do a quick and dirty average, count, or sum of those cells. After highlighting multiple cells, Excel can do one of these three calculations and show the results in the bottom right of the program near the zoom page feature.
Control, Shift, and Arrows
Combining the Control, Shift and arrow keys provides you even more opportunities. For example, if you’re writing a sum formula in G25 and want to select all cells above it to be in the formula, your key stokes will look a little like this: =sum (Up Arrow then ControlShiftUpArrow). To explain this in terms, it is:
- write the formula,
- hit the Up Arrow once to get to the first number in the selection, and then
- hold Control/Shift while hitting the Up Arrow to select the series.
These keyboard shortcuts also can be used in the formula bar to navigate quickly within a formula.
Open an Excel workbook and give these tips a try. Once you get used to using keyboard shortcuts, you will need the mouse less and less, allowing you to finish your tasks faster.