10 Microsoft Excel Shortcuts to Make Your Life Easier

Microsoft Excel is one of the most widely used software tools in the military. We use it more than we fire our weapons. Many a staff officer and commander have spent countless hours creating that perfect spreadsheet to accomplish the mission. And if you’ve ever frustratingly uttered, “there has got to be a more efficient way to do this,” check out these Excel tips.

Thanks to Army Captain Dan Hudalla for contributing this post!
If you found it useful, also check out “11 Keyboard Shortcuts You Must Learn.”

Auto Fill Series

Have you ever needed to fill sequential data such as dates, sequence numbers or even days of the week and thought, “there has to be a better way?” There is! Excel can automatically fill in data in series. Simply start a series, like “Mon,” “Tue,” and then highlight that series. You’ll see a small plus sign at the bottom right corner of your highlighted box. Drag it however far you’d like, select “Fill Series” and excel will save you needless typing.

Adjacent Data Fill

You can use the above tip in conjunction with Excel’s ability to guess what you’re doing. If you already have data and need to type accompanying data next to or below what you’ve already entered, simply start typing the new data, highlight it, and then double-click the cross you see in the bottom right of the highlighted box. Excel will automatically fill in the new series adjacent to the column that’s complete.

Range Names

Sometimes you need a cell or range often and you’d prefer not to reference a random cell like AB1003. Range names simplify this and make it easier for you and others to follow your formula math. You can actually change the name of a cell from “B2”, for instance, to something more familiar like “Income.” This works with entire ranges as well. In this example, I re-named cells A4 thru A10 “PU_scores,” cells B4 thru B10 “SU_scores” and cells C4 thru C10 “Run_scores.” In Cells D4 thru D10 I can now refer to the range names to add them together to calculate a total physical training score. This is super helpful when you have large arrays of data.

Auto Formulas

Just as auto fill can make fairly accurate educated guesses to fill a series, Microsoft has built-in auto formulas to do the same thing for summing, averaging, counting, etc. These can be very valuable. For Example, say you’d like to sum an entire row. Most users will highlight a cell, type “=SUM(“ and then highlight the cells to sum. Instead, try clicking on the auto sum icon. Once selected, excel makes an educated guess as to which cells you want to sum. If it guesses incorrectly, just highlight the right cells. For an even faster way, just hold the Ctrl button and then push the = button.
The auto sum button can be very helpful, especially for summing a lot of data.

Quick Moves

If you have large sets of data on a spreadsheet and you want to go from top to bottom, left to right or from end to beginning, it can be a pain to scroll all the way up or down. This is especially cumbersome when you’re trying to highlight the data with your mouse. A basic Excel “must” is to know how to use the Ctrl button to move. Simply hold Ctrl and push the right, down, left or up arrow to quickly move to the end of a table. If you’re in a blank cell, Excel will move quickly to the next filled cell available. Some other cool moves include Ctrl Home (Moves the active cell to the top of your worksheet), Ctrl End (Moves you to the last used cell) and Ctrl A which will select the entire region of cells you’re working with.

Quick Highlighting

If you know how to move quickly within a worksheet you can also highlight cells more quickly. Instead of using your mouse, which is cumbersome for large bits of data, simply hold Ctrl, Shift and move the arrows in the direction you’d like to highlight. To highlight the data in the picture below I held Ctrl, Shift and then press → once and ↓ once. It would be just as quick if I had 10,000 rows of data.

Split Screen

Many Excel users know about splitting the screen view but if you don’t you’re probably making your life more difficult. If you’ve found yourself scrolling down to find data in a spreadsheet but forgetting which column is which then split screen is for you. To avoid this, highlight the row (or column) below (or right) of your headings. Go to the view tab and select “Split.” This will allow you to scroll down in the bottom half of your screen while keeping your headings in view. You can split your screen horizontally or vertically and adjust the split as necessary. You can also freeze this split once you have it where you want it so that you don’t see the little bar on the screen. To freeze it select the “freeze panes” option.

Inserting Comments

If you work on a spreadsheet that others will use or see, it’s often helpful to explain your methodology for building the sheet. A simple way to do this is to leave comments in a cell. To leave a comment, select a cell, right-click and select “insert comment.” Now you can stave off questions later by answering them up before they come up. All a user has to do is hover over the cell and your comment will appear.

Format Painter

Another time saver is Format Painter. Maybe you want one table to look like another or you realize that you used the wrong font on one large table but it’s correct in another. No need to change the fonts, backgrounds, etc. Just use Format Painter. Simply highlight the correctly formatted area, select the “Format Painter” from the Home tab and paste the format over whatever area you need to.

Easy Absolute References

For the more intermediate Excel user you may know that you can “lock” a cell reference by using the dollar sign. In the example below, I wish to calculate the percent of total sales each salesperson contributed. The formula I will use is the salesperson’s total divided by the company’s total. I can “lock” the cell referencing Total sales in cell C29 by using $C$29. The $ symbol tells excel to always use Column C for this calculation and the second $ tells Excel to always use row 29 (I could also use a Range name here). Now when I Auto-fill the formula’s in column D every salesperson’s percent of total sales will calculate correctly. For those of you who already know this tip, try using F4 to automatically toggle the absolute reference so you don’t actually have to type any $ symbols!