5 time-saving tips in Excel
1. Use keyboard shortcuts to get around. Data keeps getting bigger and bigger. Unfortunately, our wrists, shoulders and elbows aren’t evolving to keep up with the scrolling it would take to navigate around and select it all. Fortunately, Excel has some great shortcuts to carry you across and down thousands of rows and columns of data in a snap.
Ctrl+Arrow Keys. Using the up, down, left or right arrow keys will take you either to the top, bottom, leftmost or rightmost cell in your data. If there is a blank cell in its path, it will stop there. But, you can send it on its way with the same keyboard combination twice more. For example, if all cells were filled down to A97098, from A1, Ctrl+DnArrow would take you down 97,098 rows, unless there was a blank cell somewhere in that column.
Ctrl+Shift+Arrow Keys. If you don’t just want to get to the far reaches of your rows and columns, but also select the data in them, simply add Shift to the Ctrl+Arrow key shortcut. For example, if you’d like to select A2 through A97098, select cell A2, hold down Ctrl and Shift, then the down arrow key. Provided there are no blank spaces in the column, it will select from A2:A97098.
Ctrl+A. This shortcut, the first time you use it, selects all contiguous data. Do it again, and it will select all cells on a worksheet. Twice does the same thing as clicking the cornerstone (above row 1 and to the left of column A). Ctrl+A is a great test for seeing if your data is ready to pivot, chart, sort or filter. If it doesn’t select all of your data, it probably means you have a completely blank row or column somewhere.
2. AutoFill fast.
AutoFill handle. You probably know that if you put your cursor on the box in the lower right corner of a cell (the autofill handle), then click and drag it down, it will automatically fill the cells.
However, if you have data on the left or right of the columns you’re filling, double-clicking the autofill handle will fill down as far as the data go, no matter how far down the data go.
Days of the week. Type Monday or any other day of the week, and autofill down, and you’ll get the consecutive days of the week (Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday).
Months of the year. Type January or any other month of the year, and autofill down, and you’ll get the months in order (January, February, March, April, May, June, July, August, September, October, November, December).
3. Create duplicate worksheets in the time it takes to create one. Add as many blank worksheets as you need. Then, select the first worksheet and, while holding down the Shift key, select the last one. Now, whatever you type on the worksheet you can see will be populated on all the others.
4. Size things up, easily. Need several rows or columns exactly the same size? Click and drag across them and click and drag to size one. Now, they’re all that size.
5. The status bar has a secret—select data on a worksheet. If it’s numeric, the status bar (bottom of the Excel window that shows the word Ready on the far left) will show the sum, minimum value, maximum value, average and count.
Don’t see them all? Right click on the status bar and select the answers you’d like to see. No typing formulas, no fuss.