You will agree that an Excel sheet can run up to thousands of rows and columns. You will also agree that when you are working on one such worksheet, you might have to navigate into deep rows and columns. This takes you away from the reference row and column i.e cell A1, where you might need to revisit frequently.
Now, using the scrolls isn’t one of the best ways to get back to that cell. Rather, we will see three better and cool ways to do this.
So, without further ado, let us jump to A1.
Cool Tip: Also check out our post on various ways to jump from one sheet to another. There are a lot more than what you use.
1. Using Keyboard Shortcuts
You can access the VBA environment in Excel 2016 by opening the Microsoft Visual Basic for Applications window. First, be sure that the Developer tab is visible in the toolbar in Excel. The Developer tab is the toolbar that has the buttons to open the VBA editor and create Form/ActiveX Controls like buttons, checkboxes, etc. Jan 14, 2016 Excel 2016 Form Controls I have a worksheet that was created in Excel 2013 with form controls (not activex controls) When the worksheet is opened on a Mac running Excel 2016, I'm unable to select the form controls..ie..select a drop down or an item in a list box.
- These instructions apply to Excel 2010, Excel 2013 and Excel 2016. Click the File tab, click Options, and then click the Add-Ins category. In the Manage box, select Excel Add-ins and then click Go.
- With an entire column selected, use Control + Shift + + (Mac: Control + I) to insert a new column to the right of the selected column. You can also insert multiple rows and columns. Just select the number of rows or columns you want to insert before using the shortcut.
- Excel 2016 can be tough to use. But you don’t need to be a spreadsheet jockey to get the most out of Excel 2016. Just follow these three tips. Microsoft Windows may get all the press coverage.
To me this is the easiest and the quickest way to get back to A1 from anywhere in the worksheet. All you need to do is press these two keys together: Ctrl + Home
Exception: This method will not take you to A1 if you have freeze panesExcel 2016 For Mac User Guide
activated on rows and columns. In such a scenario it will take you to the first row, first column after the freeze panes.Take a look at the image below. Here, rows 1 through 4 and columns A and B are in freeze mode. So pressing Ctrl + Home will take you to C5.
2. Using Go To Dialog Box
This method has an extra step and takes you a bit longer to get to A1. However, unlike method 1, it assures your jump to A1 no matter where you are and no matter what’s in freeze mode.
Step 1: Press either F5 or the combination Ctrl + G. That will launch the Go To dialog box.
Step 2: On the dialog box, under Reference text box, type A1 and hit Enter or click on the OK button.
Quick Tip: This method can be used to navigate to any cell quickly. All you need to know is the reference of the cell you want to jump to.3. Using Name Box
We know that MS Excel has a name box that displays the reference of the cell you are in. You can actually use this box to go to cell A1.
To do that, type A1 in the name box and hit Enter. Immediately, the reference will shift to A1.
There is a second trick here. You can actually define a name for cell A1, say, for example, Home. Then you can click on the name box drop down and select Home to jump to A1.
Conclusion
How often do you navigate away from the A1 reference? And how often are you required to get back? If the answer is, “very often”, you need to remember these tricks. Not all of them though, you can pick the ones you like the most or are comfortable with most.
Know of any other similar trick? Do share.
Also See#Lists #msexcelDid You Know
Eastman Kodak was founded in 1888.
More in Windows
Top 8 Fixes for the Windows 10 Brightness Slider Not Working Issue
When cutting/copying some cells to paste them into another sheet/page, sometimes Excel freezes/locks up and fades out. At the top toolbar it says in brackets 'not responding'.
Eventually, I must click 'X' to close the program. It offers to wait for the program to respond, but never does – it just does nothing until I finally close it, where it offers to recover files etc.
Is there an issue with memory here? What can I do to stop it locking up?
slhck25 Answers
I have had a similar problem on a new, plenty-fast i7 machine. Even with a trivial spreadsheet with half a dozen cells and one formula, Ctrl-C would freeze Excel for 20-30 seconds. I, however, would always get control back after that time. Turned out to be something to do with default printer. My default was a network printer that was offline. When I went to control panel and changed my default printer to CutePDF writer or something like that, copies became instantaneous as they should be.
Go to File Options. Select the 'Advanced' tab. Go to the 'Cut, copy, and paste' section. Uncheck 'Show Paste Options button when content is pasted' and 'Show Insert Options buttons'
JonI had this problem on a worksheet with no macros - it turned out to be conditional formatting. When I removed the conditional formatting from the whole worksheet all was working again. Then I added it back.
I do not recall this exact problem but I have had similar problems on a number of occasions. Like you I had to abort Excel to clear the problem and it told several such failures before I identified the cause.
The cause was Excel waiting on a question about the operation I had requested which was hidden behind the main Window. I have since discovered that getting that hidden question to display can be difficult but is possible. I do not know why, on rare occasions, the question is not on top as it usually is.
Try looking for a hidden question next time this happens.
In my situation I had an excel sheet with Conditional Formatting on some cells and for some reason this Conditional formatting was copied a couple of hundred times, probably when I copied the cell the custom formatting for the entire range of cells was copied as well.
I had to remove the Custom Formatting on hundreds of cells which took ages as you can't select multiple Rules when deleting them. So manually deleting one rule after the other, while every Delete takes a couple of seconds...well you get the point.
Anyway, my Excel sheet is quick again when the Conditional Formatting was reset to only a few Rules.
In MS Office 2013, Go to File, then Info and Select 'Check for Issues' (Inspect Work Book) , then Select Inspect Document.
There will then be a Checklist for what you want to check, I just left the default checks.
Then Hit Inspect, it'll then tell with Explanation marks '!' what some of the issue are. Hit remove All in each category and then try to copy and past again.
Worked for me, best of luck!!
Removing Office 2013 update KB3114717 did the trick for me. I found this after running the MS OffCAT tool for Office. CPU is back to normal and Excel doesn't freeze when doing copy/paste.
For me it was a TeamViewer.Check for every program that could use clipboard.
The easier way to display the background running question if it is a response/question popup is to right-click mouse at a clear section in the task bar and select Tile windows vertically rather than selecting Show Desktop to show all active windows.
slhckGot it, its all about the hidden question. I open some other excel file, and I did not click the 'enable editing'. this make freezes every time I copy some column. The problem solved when I click the 'enable editing'. how this help
I had same problem. It was an issue with Conditional Formatting and Copy/Paste/Undo etc. The paste must be a Paste Special and Merge Conditional Formatting Paste and not the default. Else the CF fragments.
I created a shortcut key to VBA script that automatically did this paste. So instead of a Ctrl+V the user does a Ctrl+S (or hits a button) which runs a macro that is:
Run5kRunning Excel for Office 365 MSO (16.X). Found that if I have two Excel files open, one was opened from my local drive, the 2nd opened from an email and loaded in protected view when I try copy from the first file which is Not in protected view it hangs and I'm forced to close file. If I reopen the first file from my local drive, close the 2nd file in protected view I have no problems with copy/paste. If I reopen the file from the email (loads in protected view), then click Enable editing on the 2nd file, return to the first try copy/paste I have no problems.
To summarize if I have a file open in protected view it causes copy/paste to freeze with any other file that I have open so enable editing on everything you have open.
- Goto
File => Options => Advanced tab - Goto
Cut, copy, and pastesection. - Uncheck:
Show Paste Options button when content is pasted - Uncheck:
Show Insert Options buttons - Uncheck:
Cut, copy and sort inserted objects with their parent cells
For those of you who read through all of these answers and did all of these things to no avail, I have another thing for you to try that worked for me. Like you, I read and did all of these things and they didn't help.
In my case, Office 2010 version of Excel (32bit) on Windows 7, the program would freeze for MINUTES not seconds.
I noticed that the amount of RAM that Excel.exe was using would approximately triple while the program was frozen, and that dwm.exe was using about one core of CPU (12-13% on a quad-core w/ HT versus 1-3%). I used Process Explorer and took a complete dump of Excel while it was frozen, then installed and ran WinDBG (good beginner guide which is all I used found here), and learned that there was 'heap corruption'.
In this case, Windows updates hadn't been applied for YEARS, and I found out Service Pack 2 for Office 2010 was one of them (https://www.microsoft.com/en-us/download/details.aspx?id=39667). This computer was also being offered an update KB3078667 which has to do with a memory leak that occurs in Desktop Window Manager (dwm.exe).
After installing and rebooting, the problem went away. Hope this helps somebody else out there, as it's not just exclusive to Office 2010.
This security bulletin is applicable, look at it and see if you have this update installed or a superseding one that has one of these in it, if not, installing one of these may ameliorate your problem.
I have a spreadsheet of Corn prices that covers 20 years - 1000 worksheets. I also have some worksheets at the end showing the results (graphs, etc.) of various scenarios. From time to time I try different things and re-use existing columns on the price worksheets. All of a sudden when I hit ctrl+C (copy) it froze and I got a 'not responding'.
One of two things fixed it. I got rid of some of the summary worksheets - now if works. Either: I had hit some Excel limit as far as what it could handle but I don't think that is likely. What is more likely is my new use of one of the existing columns was an 'IF/THEN' code that returned a text answer rather than a numerical answer. I believe the freezing was due to one of the existing summary sheet items (graphs, etc.) expecting a number field and finding a text field.
Matthew WilliamsFor me it was a weird program, a virtual machine, that hooks into clipboard and monitors it. Closing that program solved the issue.
Salman ASalman A- If your problematic copy-paste workflow is including calculations, ask yourself why you are doing that. Your workflow may need to be revised.
- Complex non-plaintext data could be getting in the way. Test this by making a plain-text-only and numbers-only new document, by copying individual columns, pasting into Notepad, copying into columns in the new document.
- There may be hidden rows and columns that are getting copied along with the visible portion. There may be hidden data by pivot tables, so that the actual amount of data being transferred is much bigger than expected.
- If the file size is big, you may be overwhelming the PC. Modern Excel files are actually ZIPped XML files. At one place I worked at, an Excel sheet was causing problems on old PCs, so I tried manually unzipping it (renaming it .zip and using 7Zip to expand it). The 200 MB archive came out to over 2 GB of plaintext.
If you have a Protected Cells in a Protected Sheet, you may get the same error as well.
Please Unprotect your sheet and then do your action, either select, copy or paste.
I experienced this issue as well today, and I was absolutely clueless as to why Excel was freezing up for ~20 seconds whenever I tried to copy something. I most of the suggestions mentioned in this question, to no avail.
I later found out during the same day that my C: drive (the installation drive for MS Office) apparently didn't 'have enough' empty space. It was odd, since I already had 10 GB free. Regardless, I simply emptied up another 10 GB on that drive, and copying inside Excel is working fine again.
So, if you haven't found a solution yet, I suggest freeing up some space on the drive you have Excel installed on. That might just be the trick.
Maybe not directly related to any of the issues above, but I had a user who was copy/cut from one tab to another tab within a same sheet, and Excel was freezing. It ended up the cells from the source tab was locked. Once I unlock the cells (highlight the cells, right click and format cells, under protection tab, uncheck Locked), then paste wouldn't freeze Excel.
Another solution is to check for hidden picture objects. I found that the excel document had hundreds of hidden image items that weren't visible. The below steps are from https://blogs.office.com/en-us/2009/01/30/hidden-and-invisible-objects/
The first thing you want to do is verify that you have additional objects on your spreadsheet. The easiest way to view a sheet’s objects is to turn on the on the Selection Pane (on the Home Tab go to the Editing Chunk > click the Find and Select Dropdown > select the Selection Pane option making the pane visible). Looking at the selection pane you will see a list of all objects on the current sheet and an indication if the objects are visible or hidden. If the selection pane is blank then you do not have extra objects on the sheet.
The top of the Selection and Visibility Pane for my sheet. Notice that 100 objects were pasted into my document. I have seen customer files with thousands of hidden objects.
Microsoft Excel 2016 For Mac
Once you have the selection pane open and verified that you have objects on the sheet, the next step is to put your spreadsheet in Design Mode if it is supported on your current workbook. Design Mode will only be enabled in Excel if your sheet contains certain types of controls. Turning on Design Mode will allow you to select all controls, not just the basic shapes and form controls. To enter Design Mode select the Design Mode button in the Controls Chunk on the Developer Tab. If this button is disabled in the Ribbon then your sheet does not contain the types of controls that require this step. (Note: If you do not have the Developer Tab visible in the Ribbon you can enable it with the following steps: Office Button > Excel Options
check the “Show Developer tab in the Ribbon†checkbox on the default Popular tab).
You now are ready to select objects using the Go To Special dialog. To select all objects in the sheet: Ctrl+G to open the Go To dialog > select the Special button > select Object > click OK. The objects will then be selected and their selection handles are visible on the sheet.
My sheet with all objects selected. You can see that in addition to the previously visible objects I now also have selected some textboxes previously hidden on my sheet.
At this point you can simply hit the Delete key and remove all of these objects from the sheet. You can also choose to be more selective about the objects you delete; if you have other objects already on your sheet you wish to keep, ctrl+clicking the objects in either the selection pane or on the sheet will remove them from the current selection prior to hitting delete.
i opened the clip board and turned off all the options. problem fixed.
open the clip board by clicking the arrow at the bottom right corner of the home tab. At the bottom left of the clip board click options. Uncheck all the options.
I've had this problem for some time and I have a file with a ton of conditional formatting. I unchecked 'Enable Live Preview' and things seem to be working way better now. Hopefully this helps someone else as well.
My issue was resolved by a READ ONLY warning banner on a mail window behind the worksheet I was in. Once I unlocked for editing, the worksheet functioned normally again.
To me on Win10 helped 'Troubleshoot compatibility' on Excel shortcut. System fixed something and the problem disappeared.
![where Did Controls Go Excel \2016 For Mac\](https://www.stellarinfo.com/blog/wp-content/uploads/2016/10/How-to-Find-Scanpst.exe-in-Outlook-2016-2013-2010-and-2007.jpg "where Did Controls Go Excel \2016 For Mac\")