Excel, like many apps, is a program of habits. You discover an annoying quirk, provide you with a strategy to work round it, and it turns into etched into your muscle reminiscence. Over time, I’ve discovered that a few of these quirks have surprisingly easy options: tweak the way in which you’re employed, press a unique key, or change a setting and go away it alone. If any of those 5 Excel annoyances sound acquainted, you’ll be able to be taught the fixes in a couple of minutes this weekend.
Ctrl+Z undoes one thing in one other workbook
Give every file some respiration room
Ctrl+Z often does precisely what you anticipate: it undoes the very last thing you probably did. That’s, till you are working in two Excel workbooks without delay and it out of the blue undoes one thing within the different file.
Excel retains its undo historical past inside every occasion of this system. Consider an occasion as a separate Excel course of: a number of workbooks can share one occasion, which means they share the identical undo historical past. To provide a workbook its personal undo historical past, open it in a second occasion.
This sounds sophisticated, but it surely’s really not. Maintain Alt, right-click the Excel icon (whether or not that is in your taskbar or a desktop shortcut), then click on Open or Excel. You may then be requested if you wish to begin a brand new occasion of Excel, so click on Sure.
In case you use a PERSONAL.XLSB file on your macros, you might even see a File in Use message while you open the second Excel occasion. It is because PERSONAL.XLSB is already open in your first occasion. Click on Learn Solely, and your workbook will nonetheless be totally editable and savable. The restriction applies to PERSONAL.XLSB, not the workbook you are engaged on.
With the workbooks now in separate situations, every has its personal undo historical past. So Ctrl+Z in workbook A solely undoes adjustments made in workbook A, no matter what you have completed in workbook B.
There are some minor trade-offs to concentrate on: separate situations use extra reminiscence, and dealing with hyperlinks, macros, or different connections between workbooks may be much less handy. For unrelated workbooks, although, separate situations can prevent from an disagreeable Ctrl+Z shock.
Excel does not autofill the alphabet
Educate it a brand new sequence
Excel is nice at persevering with sequences of numbers and dates, however should you’ve ever tried to get it to proceed A, B, C, D…, it does not mechanically deal with the alphabet as a sequence.
You may repair that by including the alphabet to Excel’s Customized Lists. Go to File > Choices > Superior, scroll all the way down to the Common part, and click on Edit Customized Lists. Choose NEW LIST, sort A by way of Z with every letter on a separate line, and click on Add.
Now enter A in a cell and drag the fill deal with. Excel will proceed by way of the alphabet all the way in which to Z. Higher nonetheless, it remembers the record, so that you solely need to set it up as soon as.
Your method is masking the cell you want
Transfer the typing elsewhere
In some eventualities, comparable to while you’re modifying a hard-coded worth to show it right into a method, the method textual content can shortly prolong past the cell you are modifying and canopy the cells you must click on.
Till just lately, I might often work round this by clicking a close-by cell and utilizing the Arrow keys to maneuver to the one I wanted. That works, but it surely’s irritating when you end up doing it repeatedly.
You may keep away from this by telling Excel to edit cell contents within the Components Bar as a substitute. Go to File > Choices > Superior, and uncheck Enable modifying immediately in cells. You may nonetheless double-click a cell or press F2 to begin modifying as earlier than; the distinction is that Excel places the method within the Components Bar reasonably than over the worksheet.
In case you’ve used Excel for years, that is fairly an enormous change to how you’re employed, so that you would possibly in the end want direct cell modifying. However should you usually need to work round lengthy formulation masking close by cells, transferring method modifying to the Components Bar can take away that annoyance.
Alt+Tab does not soar between worksheets
Give every tab its personal window
Alt+Tab might be one of many most-pressed shortcuts for anybody who works in a number of apps every single day. However should you go to make use of it to modify between worksheets in the identical Excel workbook, it will not take you the place you anticipate. That is as a result of Home windows treats these worksheets as a part of the identical app.
For worksheets subsequent to one another, Ctrl+Web page Up and Ctrl+Web page Down are often the quickest strategy to transfer between them. However while you’re always switching between two specific worksheets, there’s a greater choice.
Go to View > New Window. Excel opens one other window displaying the identical workbook, so you’ll be able to have a unique worksheet seen in every window. You may then use Alt+Tab to modify between them. Any adjustments you make are nonetheless saved to the identical workbook.
That is notably useful when the worksheets are far aside, as a result of you do not have to cycle by way of all of the intervening tabs simply to get again to the one you want.
Arrow keys insert cell references
Put Excel into the precise mode
This Excel habits can go away you scratching your head if you do not know why it occurs. You are modifying a method in a dialog field, press an Arrow key to maneuver the cursor, and Excel out of the blue inserts a cell reference as a substitute.
This occurs as a result of these method fields open in Enter mode by default. In Enter mode, urgent an Arrow key switches Excel into Level mode, which means the keystroke selects a cell so as to add to the method as a substitute of transferring the cursor.
The repair is to press F2. This switches Excel to Edit mode, the place the Arrow keys transfer the cursor by way of the method textual content as a substitute. Press F2 once more to modify again to Enter mode.
You may see which mode Excel is utilizing on the standing bar on the backside of the window. Search for Enter or Edit when you’re working in a method subject.
That is a type of Excel behaviors that makes far more sense as soon as you realize what’s taking place. There’s nothing mistaken with the Arrow key or the method; Excel is just in a unique mode than you anticipated.
Make Excel work your manner
As soon as you realize the place to look, a lot of Excel’s little annoyances have easy fixes. And there are many different small adjustments you’ll be able to implement this weekend to make Excel really feel extra like your personal, from customizing the ribbon to uncovering some helpful hidden standing bar choices. Spending a couple of minutes making these adjustments at this time can prevent lots of problem subsequent week.
