Print this article | Return to Article | Return to CFO.com
A trick to navigate and sum thousands of rows and columns that is simple enough for novices to use, and still useful to experts.
Bill Jelen, CFO.com | US
October 12, 2011
Editor's Note: If you would like bill Jelen to answer your Excel question in an upcoming column, post a question at the Community Center on the right or send an e-mail note to billjellencfo.com. If Bill selects your question to use in a column, you'll win one of his Excel books as a thank you.
Irving S. wins a copy of Using Excel 2010 for his question: "I am an ancient CPA but a novice Excel user. Is there an easy way to highlight a worksheet to add up the columns and rows to get the totals. Currently I have a spreadsheet with numbers that goes from B4 to AJ2158. I start at B4 hold down the ctrl button and invariably never get to AJ 2158. Your response will be greatly appreciated. Thank you."
There are a couple of navigation tricks that will make this task easier, perhaps even as easy as 10 keystrokes1. Rather than show the steps with data from B4:AJ2158, I will illustrate with a smaller data set so you can see the effect of each navigation step.
As an aside, these steps require the first row of numbers and the first column of numbers to have no blank cells.
If you press Ctrl and Arrow key, Excel will jump the cell pointer in the direction of the Arrow key, stopping at the cell before the first blank cell. In Figure 1, pressing Ctrl+DownArrow would move to B13. However, you want to select from B4 to B13, so add a Shift key to the keystoke. Hold down both the Ctrl and Shift keys. While Ctrl and Shift are held down, press the down arrow key. You will have now selected the entire first column of numbers as shown in Figure 2.
In a similar fashion, press Ctrl+Shift+RightArrow to select all columns in the data set. Excel will look through the first row of the selection and stop just before the first blank cell in row 4. You have now selected the range B4:F13 (Figure 3)
From here, you want to select one more row and one more column. Do not hold down the Ctrl key for the next two steps. Use Shift+RightArrow to select the blank cells in the Total column (Figure 4).
Use Shift+DownArrow to select the blank cells in the Total row (Figure 5).
At this point, you could either click the AutoSum icon (found in the Home or Formulas tab of the Excel ribbon or on the standard toolbar in Excel 2003 and earlier versions of the software). Or, since your hands are already on the keyboard, press Alt and the Equals sign. Excel will fill all the blank cells along the right edge and bottom row with =SUM functions (Figure 6).
Learning how to navigate with Ctrl+Arrow and select with Ctrl+Shift+Arrow key is useful because the trick is scalable. Even if you have thousands of cells as in Irving's example, the exact same keystrokes will add totals at the edges of the data, in about 10 keystrokes (Figure 7).
CFO contributor and Microsoft MVP Bill Jelen is the author of 33 books about Microsoft Excel and the host of MrExcel.com. Send questions for future columns to billjelencfo.com.
1Here is how I calculate this as 10 keystrokes: Hold down Ctrl+Shift with your left hand (2 keystrokes). Press Down Arrow, Left Arrow with your right hand (3rd and 4th keystrokes). Let go of the Ctrl key with your left hand (5th keystroke - an unkeystroke). Press Right Arrow then Down Arrow with your right hand (6th & 7th keystroke). Let go of the Shift key (8). Press Alt and Equals (9th and 10th keystrokes).