Here are 6 tips to help save you keystrokes and repetitive steps in Excel. Click on each tip to see a screen shot of the suggestion. For more tips, check out the complete article at PC Mag.
This is a no brainer, but so easily overlooked. You start typing a series of repetitive things like dates (1/1/14, 1/2/14, 1/3/14, etc.) and you know you’re in for a long day. Instead, begin the series and move the cursor on the screen to the lower right part of the last cell—the fill handle. When it turns into a plus sign (+), click and drag down to select all the cells you need to fill. They’ll magically fill in the pattern you started. Guess what—it can also go up a column, or left or right on a row as well.
Even better—you can Auto Fill without much of a pattern. Again, pick a cell or cells, move to the fill handle, click, and drag. You’ll get a menu of options. The more data you input at first, the better the Fill Series option will do creating your AutoFill options.
New in the latest version of Excel is one of its best features. Flash Fill will smartly fill a column based on the pattern of data it sees in the first column (it helps if the top row is a unique header row). For example, if the first column is all phone numbers that are formatted like “2125034111” and you want them to all look like “(212)-503-4111,” start typing. By the second cell, Excel should recognize the pattern and display what it thinks you want. Just hit enter to use them. This works with numbers, names, dates, etc. If the second cell doesn’t give you an accurate range, type some more—the pattern might be hard to recognize. Then go to the Data tab and click the Flash Fill button.
You can use Flash Fill to try this, but Text to Columns is pretty brain dead simply and doesn’t need much pattern recognition. Say you’ve got a column full of names, first next to last, but you want two columns that break them out. Select the data, then on the Data tab click Text to Columns, then choose to separate them either delimiters (based on spaces or commas—great for CSV data values) or by a fixed width. The rest is like magic, with extra options for certain numbers. Fixed width is utilized when all the data is crammed into the first column, but separated by a fixed number of spaces or period.
You’ve got a bunch of rows. You want them to be columns. Or vice versa. You would go nuts moving things cell-by-cell. It’s a pretty good chance you already know that you can copy that data, select Paste Special, check the Transpose box, and paste with a different orientation. If you didn’t, you do now.
For some reason, you may have to write the same thing over and over again in cells in a worksheet. That’s excruciating. Just click the entire set of cells, either by dragging your cursor, or by holding the Ctrl key as you click each one. Type it on the last cell, then hit Ctrl+Enter—and what you typed goes into each cell selected.
Let’s say you’ve got a huge amount of numbers in decimal format you want to show as percentages. The problem is, that numeral 1 shouldn’t be 100%, but that’s what Excel gives you if you just click the Percent Style button (or hit Ctrl-Shift-%). You want that 1 to be 1%. So you have to divide it by 100. That’s where Paste Special comes in. First, type 100 in a cell and copy it. Then, select all the numbers you want reformatted, select Paste Special, click the “Divide” radio button, and boom goes the dynamite: you’ve got numbers to convert to percentages. This also works to instantly add, subtract, or multiply numbers, obviously.