Speeding up your work in Excel: useful tips, functions, keyboard shortcuts. Unrecognized printer installed
In the section on the question How to speed up the opening process Word files and Excel? Just read the whole question. given by the author Rustic the best answer is More RAM, a more powerful processor, and fewer files running in the operating memory, add Word to the exceptions in the antivirus so that you don’t check it again.
Answer from Arsen Sandoyants[guru]
1. Try to remove all the beauty of the operating system
2. Set Word and Excel to run in Windows Scheduler
4. Customize the programs themselves: remove unnecessary toolbars, disable unnecessary graphic elements
5. Try to break the files into component parts (as if by chapters)
This is how these programs worked for me on a low-power computer. Now I have an i5 processor, 4 GB random access memory and no program hangs
Answer from lightly salted[guru]
Go here: C:Program FilesMicrosoft OfficeOffice12
and according to the files of the other book. mouse "EXCEL.EXE" and "WINWORD.EXE" - Send ---> Workspace (create shortcut).
On the desktop, click on the EXCEL shortcut (remove the extension) - RMB - Properties - and in the "Object" field at the end of the path, write this: "/prefetch:1".
And the path to the file will be like this:
"C:Program FilesMicrosoft OfficeOffice12EXCEL.EXE" /prefetch:1
(please note that between
"EXCEL.EXE" followed by slash 1 space /prefetch)
---
And we do the same for Word.
(there seem to be no other ways to optimize the speed of file expansion, but maybe there are :)
========
An Excel file has been sent to you big size? Does it slow down a lot and last a long time? After each action you must wait. However, there is not much information in the file. Why does Excel slow down, causes and consequences, and most importantly, how to deal with it? Let's figure it out now.
MS Office programs lag is a common problem. Excel slows down due to a number of reasons: misuse program functions (extra conditional formatting, pivot tables based on huge arrays, extra lines in documents). Excel is also not ideal and the brakes occur due to imperfection of the software part (appearing objects, the printerSettings folder in the structure). Therefore, we recommend that you go through all 10 steps below and do not stop at one.
Sometimes the range is formatted as a special table (Top panel - Insert tab - Table), this can lead to a very slow Excel file, especially if the tables are large.
To define this format, select any table cell. The Table Tools panel appears in top menu, the most right one. Select the entire table then tab Working with Tables, Designer - Service section - Convert to range.
2. Remove unnecessary conditional formatting (if Excel slows down when scrolling)
Read what conditional formatting is. A very useful thing, if used correctly, if not, it will make your file work slowly.
The fact is that when cells are copied, the formatting is also copied - if new information is constantly copied into the file, then the conditions of the format, especially conditional formatting, are preserved in it.
Let's remove it like this. Choose a sheet. Select the desired range or the entire sheet. IN top panel Home - Conditional Formatting— Delete rules — Delete rules from selected cells/sheet. Click.
3. Remove extra rows/columns (if the scroll slider is very small)
The most common problem for excel which I am meeting with. If someone accidentally moved to the end of the sheet (to line number 1 million) and saved the book that way. The file size immediately increased. You can reach the end of the table by accident - if you press the combination Ctrl + down or right arrow. It happens that at the end of the book there is some random symbol or fill.
The main sign of problems is the size of the slider; it is very small when the file is saved incorrectly, as in the picture.
Correct the situation by removing extra rows or columns. Find the last useful cell for you, highlight the first empty cell after it (or better yet, the first empty row/column after it), press . This keyboard shortcut selects cells below the selected row or to the right of the selected column. Right mouse button – Delete – Delete a row or column (usually takes a long time). After deleting, select cell A1 and save the file. The slider should increase.
4. Remove unnecessary objects
Very often, especially when copying from other files or sites, hidden objects are hidden in tables - pictures, shapes, etc.
To delete such objects, press Alt + F11 and copy the text below.
Sub DelOb() For Each i In ActiveSheet.Shapes i.Delete Next end sub
Or select and delete objects manually. Go to the Home menu - Editing - Find and select - Select a group of cells - Objects. Now delete.
5. Remove unnecessary data in the Excel file structure
Even experienced users don't know what the Excel file says Wikipedia, this is an archive file. Since 2007 release.
Those. The Excel file is opened, for example, by 7-zip or WinRar archivers. Inside open file can be stored unnecessary files, which slows down Excel sometimes tenfold.
Shall we remove the inconvenience? Do it first backup copy file :) Then run 7-zip or another archiver, menu “File” - “Open inside”. It is possible to open the file by clicking right click mouse - Open with and selecting.exe WinRar file or 7-zip.
An archive will open, also known as an Excel file with folders and files.
Find the “drawings” and/or “printerSettings” folders (most likely they will be in the xl folder) and delete them.
We do the same for WinRar.
Then we open the book as an Excel file, it will swear a little, display a few system messages, which does not find data, etc. Click OK on all windows, the file will be restored.
Be careful, if your file contains drawn buttons or other shapes, then deleting the entire drawings folder means deleting useful shapes.
Therefore, in the folder, delete only the vmlDrawing.vml files, they can accumulate information and weigh up to 100 MB.
6. Excel is slow - set up pivot tables correctly
If a PivotTable references a large range of cells, over 10K rows, it stores calculation results that can be very large. This makes the entire Excel workbook slow down, of course. To eliminate this reason, right-click on the pivot table - Pivot table options - Data tab - uncheck Save source data with file.
This will reduce the file by almost half.
7. Change the file format to .xlsb
If you work with huge tables and your files weigh more than 0.5 MB, then it is better to save such books in the . Binary Excel workbook format, i.e. special format to create a "database" based on spreadsheets. If you save large file in this format, the weight of the book will decrease by two to three times. Calculations to the file will also be faster, in some cases 2 times faster.
8. Unidentified printer installed
If you do not have a printer defined on your computer, i.e. If there is no default printer, then go to devices and printers and change the default printer to any other (even if there is no physical printer), if there is a printer, it is better to change the driver.
It happens that even when you delete the printer settings from step 5, the printer settings slow down the file.
9. Delete the PERSONAL file
Go to ...Application Data\Microsoft\Excel\XLSTART or ...Microsoft Office\Office12, find a file called PERSONAL in these folders and delete it. Read more .
10. Replace many created formulas with macros
Often a matrix is 10,000 rows by 10 columns with complex formulas referencing other sheets, worse than a book, slows down the file terribly. Try replacing formula calculations with written ones, converting formulas into copied ones as numerical values or text. This will help if a lot of actions are performed on the file, which means the file is often recalculated. I tried it myself recently and it helped. Write in the comments if you need help.
11. Reduce the size of your pictures
The easiest way for version 2007 and higher is to select a picture, and the Working with Pictures - Format tab will appear in the top panel.
Find the Edit section and select actions to compress the picture, read more.
12. Upgrade to a later Excel - 2013 or 2016
The developers of our favorite editor really do not stop developing the product. After conducting a series of experiments, I found that most complex operations such as calculating cells, executing macros, saving and others in version 2016 are performed faster by ~15% compared to 2007.
In some cases, Excel 2007 simply refuses to perform actions on the file, whereas in modern releases of the program the file works.
So one more good way Speed up your work in Excel - switch to MS Office 2013 and higher.
Now try it yourself.
If these 12 steps did not help, then it is better to transfer or copy all the information to new file. The file itself may be damaged.
The logical continuation of the article is optimizing computer resources for using Excel- read in.
If nothing helps, write comments, we will try to help.
Share our article on your social networks:Holds 32,767 characters. Therefore, the function of quickly changing the width of a column based on its content is not always convenient. :) Although in most cases it helps out. Place your mouse cursor over the right border of the column header and double-click on it so that the program selects the optimal column width. This trick works for multiple columns as well as the entire sheet.
2. How to quickly select an entire row or column
When working with large volumes data is impossible to do without keyboard shortcuts, which make it easy to move around the sheet and highlight individual columns or rows. Combinations Ctrl + Spacebar And Shift + Spacebar they are responsible for quickly selecting the current column or row.
I once watched a four year old use the copy and paste hotkeys to enter a key for a game. Looks like an inseparable trinity Ctrl+C, Ctrl+V And Ctrl+A absorbed by the younger generation along with mother's milk. By the way, the “Select All” command in Excel has a small feature: single click Ctrl+A selects the current range, and double selects the entire table.
4. How to make changes on several sheets at the same time
Steamed turnips, your way out! For example, to change the font size on all sheets at once, you need to collect them into a group by clicking on the sheet tabs while holding down the Ctrl key.
5. How to move a line in the current cell
If you want to make fun of a newbie, ask him to enter data inside a cell in several lines. Of course, it’s a school trick, but it works great with the older generation. Combination Alt+Enter prevents moving to another cell and moves the cursor to new line within the current cell.
Inserting an additional row or column takes three to four clicks. This is not much, but only until you need a dozen new columns at once, for example. However, by first selecting the required number of existing cells, you can get away with the same number of clicks.
7. How to quickly insert the current date
Apparently, the hotkeys Ctrl + ; And Ctrl + Shift + ; should be played on the radio instead of the morning anthem. These combinations should be repeated until there is no one left on the planet who manually enters the date and time.
Long opening Excel files may occur due to:
- large quantity formatting applied to the table
- problems with the printer
- presence of hidden objects
- inappropriate use of formulas
- huge file size, with a small amount of data used
- and God knows what other problems.
Getting rid of brakes in Excel
Availability huge amount formatting significantly slows down Excel
Imagine a table with 65,000 rows and 50 columns. Each cell has different colors, unique font and text size. Do you think the cursor will move quickly through such a file? If you don't have a MainFrame, then it's unlikely.
Try to fill out only what needs to be filled out. Don't apply formatting to an entire column or row if you can select only the required number of cells. Well, if you come across such a “formatted” file, then Excel has a wonderful button - Clear Formats 
Excel may also slow down due to problems with the printer.
More precisely, if the printer that is selected by default in the editor is not connected. But this problem is typical for Excel 2007 and older. In this case, the file launch is very slow. This can be corrected by selecting a new working printer or turning on a non-working one. Or default installation virtual printer "Microsoft XPS"Document Writer"
Presence of hidden objects
Hidden objects can appear in the table as a result of copying data from another file. Moreover, the object is transferred with zero dimensions, which is why it is not visible when selected. If there are a large number of them, this can significantly slow down the work.
We open the problematic file and make sure that we can find and select hidden objects. To do this, press Alt+F11, it will open Microsoft editor Visual Basic. Press F4, select ThisWorkbook from the drop-down list and set the value -4104 -xlDisplayShapes in the DisplayDrawingObjects field. Next, on the Excel Sheet we perform Home - Find and Select - Select a group of cells - Objects, click OK and then Delete key. Naturally, it is better to make a copy of the file and practice on it.
Overload with formulas
If the document is large, with several sheets and data aggregation is carried out on some of them, then the calculation on weak machines, laptops and netbooks will be slow and long. If the document is “your own” and can be redone, then it is advisable to replace unused formulas with values. It's done like this. Select the required range of cells, right-click and select Copy.
And also right-click on this selected range, select Paste Special and specify Values. This reduces the number of formulas that Excel must use to perform calculations. 
Also remember to use faster formulas:
Use IFERROR, it works faster than the combination IF + EOSH
Use -- to convert boolean values to zeros and ones.
It is preferable to use the combination INDEX + MATCH instead of VLOOKUP
MAX(A1;0) works faster than IF(A1>0;A1;0)
Long opening time due to large size
Moreover, it often happens that the amount of data in the tables does not proportionally correspond to the file size. The latter is most often greater. This can happen when the file has been or is in the Public domain. When this mode is set, Excel keeps a change log, which includes all changes for the period specified in the parameters.
You can disable or change the period in Excel 2007 and older, in the Review tab - Access to the book. And on the Details tab you set the values you need.
If the document is saved in xlsx or xls format, you can save it in xlsb format. And the speed with which the file will open will pleasantly surprise you. The increase in speed is achieved due to the fact that all data in this file is saved in a binary format, more “native” to Excel, which requires fewer resources to parse and convert the document. Plus to all this, the file size will be smaller. 
Again, if the document is saved in xlsx format and takes a long time to open, you can open it using any archiver (for example, winrar) and delete the revisions and worksheets folders. The first one stores all changes when the mode is turned on General access. The second folder contains images. Perform these operations on a copy of the file. By the way, if there are images in the document, do not forget to compress them. This is done through the Format tab - Edit group - Compress pictures and specify the preferred quality. It will help reduce the size of images, which will have a positive effect on the file size. 
The file may launch slowly in Excel 2003 and 2007 if the document is opened with network resource. But when copying to local computer- startup is fast. This can be cured by creating the missing partitions and adding the value:
HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\12.0\Excel\Security\FileValidation (for 2007)
HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\11.0\Excel\Security\FileValidation (for 2003)
Enableonload=dword:00000000
This option disables Office feature File Validation, which is responsible for validating the binary file format and checking whether it matches the MS Office file format.
Enable/Disable multi-threaded computing
This parameter is located in Settings - Advanced - Formulas tab. If you have several processor cores, you need to enable this item and specify the number of threads equal to the number of cores. If the application slowed down even before with the parameter enabled, then disable it. Ideally, this setting will speed things up when working with formulas.
I hope that after all the manipulations, the file will no longer take a long time to open, and Excel will not slow down when starting.
And below, very interesting and detailed video with comments on how to speed up Excel
Pavlov Nikolay
In this article, I would like to present to you the most effective techniques for working in Microsoft Excel, which I have collected over the past 10 years of working on projects and conducting trainings on this wonderful program. There is no description of super complex technologies here, but there are techniques for every day - simple and effective, described without “water” - only “dry residue”. Most of these examples will take you no more than one or two minutes to master, but they will help you save much more.
Quickly jump to the desired sheet
Do you find yourself working with Excel workbooks consisting of a large number of sheets? If there are more than a dozen of them, then each transition to the next required sheet becomes a small problem in itself. A simple and elegant solution to this problem is to click in the lower left corner of the window on the buttons for scrolling the sheet tabs not with the left, but with the right mouse button - a table of contents of the book will appear with full list all sheets and you can go to the desired sheet in one movement:
This is much faster than scrolling through sheet tabs using the same buttons in search of what you need.
Copy without damaging formatting
How many hundreds (thousands?) of times have I seen this picture, standing behind my students during trainings: the user enters a formula in the first cell and then “stretches” it across the entire column, violating the formatting of the rows below, since this method copies not only the formula, but also the cell format. Accordingly, you then have to manually correct the damage. A second to copy and then 30 to repair a design damaged by copying.
Starting with Excel 2002, there is a solution to this problem that is simple and elegant. Immediately after copying (dragging) the formula onto the entire column, you need to use a smart tag - a small icon that temporarily appears in the lower right corner of the range. Clicking on it will bring up a list possible options copying, where you can select Fill without formatting. In this case, the formulas are copied, but the formatting is not:
Copying only visible cells
If you work at Microsoft Excel more weeks, you should have already encountered similar problem: in some cases, when copying and pasting cells, more cells are inserted than were, at first glance, copied. This may occur if the copied range included hidden rows/columns, groupings, subtotals, or filtering. Let's take one of these cases as an example:
In this table, subtotals are calculated and rows are grouped by city - this is easy to understand by the plus-minus buttons to the left of the table and by the breaks in the numbering of visible rows. If you select, copy and paste data from this table in the usual way, then we get 24 extra lines. We only want to copy and paste the results!
You can solve the problem by painstakingly selecting each row of the totals while holding down the CTRL key - as you would for selecting non-adjacent ranges. But what if there are not three or five such lines, but several hundreds or thousands? There is another, faster and more convenient way:
Select the range to copy (in our example it is A1:C29)
Press the F5 key on your keyboard and then the Select button in the window that opens.
A window will appear allowing the user to select not everything in a row, but only the necessary cells:
In this window, select the Only option visible cells(Visible cells only) and click OK.
The resulting selection can now be safely copied and pasted. As a result, we will get a copy of the visible cells and insert, instead of the unnecessary 29, only the 5 rows we need.
If you suspect that you will have to perform such an operation often, then it makes sense to add it to the Microsoft toolbar Excel button to quickly call such a function. This can be done through the Tools> Customize menu, then go to the Commands tab, in the Edit category, find the Select visible cells button and drag it to the toolbar with the mouse:
Converting Rows to Columns and Back Again
A simple operation, but if you don’t know how to do it correctly, you can spend half a day dragging individual cells manually:
It's actually simple. In that part of higher mathematics that describes matrices, there is the concept of transposition - an action that swaps rows and columns in a matrix with each other. In Microsoft Excel, this is implemented in three steps: Copy the table
Right-click on an empty cell and select Paste Special.
In the window that opens, check the Transpose flag and click OK:
Quickly add data to a chart
Let's imagine simple situation: you have a report for last month with a visual diagram. The task is to add new numerical data to the chart for this month. The classic way to solve this is to open the data source window for the chart, where you add a new data series by entering its name and highlighting the range with the desired data. Moreover, this is often easier said than done - it all depends on the complexity of the diagram.
Another way - simple, fast and beautiful - is to select the cells with new data, copy them (CTRL+C) and paste (CTRL+V) directly into the chart. Excel 2003, unlike more later versions, even supports the ability to drag a selected range of data cells and drop it directly into the chart using the mouse!
If you want to control all the nuances and subtleties, then you can use not the usual one, but special insert by selecting Edit> Paste Special from the menu. In this case, Microsoft Excel will display a dialog box that allows you to configure where and how exactly the new data will be added:
Similarly, you can easily create a chart using data from different tables with different sheets. To perform the same task in the classic way it will take much more time and effort.
Filling empty cells
After downloading reports from some programs to Excel format or when creating pivot tables, users often end up with tables with empty cells in some columns. These omissions do not allow us to apply the usual and convenient tools such as autofilter and sorting. Naturally, there is a need to fill the voids with values from higher-level cells:
Of course, with a small amount of data, this can be easily done simple copying- manually dragging each header cell in column A down to empty cells. What if the table has several hundred or thousand rows and several dozen cities?
There is a way to solve this problem quickly and beautifully using one formula:
Select all cells in a column with blank spaces (i.e. range A1:A12 in our case)
To keep only empty cells in the selection, press the F5 key and in the navigation window that opens, press the Select button. You will see a window that allows you to select which cells we want to select:
Set the switch to Blank and click OK. Now only empty cells should remain in the selection:
Without changing the selection, i.e. Without touching the mouse, enter the formula into the first selected cell (A2). Press the equal sign on your keyboard and then the up arrow. We get a formula that refers to the previous cell:
To enter the created formula into all selected empty cells at once, click ENTER key, A combination CTRL+ ENTER. The formula will fill all empty cells:
Now all that remains is to replace the formulas with values to record the results. Select the range A1:A12, copy it and paste their values into the cells using Paste Special.
Dropdown list in a cell
A technique that, without exaggeration, everyone who works in Excel should know. Its use can improve almost any table, regardless of its purpose. At all trainings, I try to show it to my students on the first day.
The idea is very simple - in all cases where you have to enter data from some set, instead of manual entry select a cell from the keyboard desired value mouse from the drop-down list:
Selecting a product from the price list, customer name from the customer database, employee name from staffing table etc. There are many options for using this function.
To create a dropdown list in a cell:
Select the cells in which you want to create a drop-down list.
If you have Excel 2003 or older, select Data>Validation from the menu. If you have Excel 2007/2010, then go to the Data tab and click the Data validation button.
In the window that opens, select the List option from the drop-down list.
In the Source field, you must specify the values that should be in the list. Here are the possible options:
Enter text options in this field separated by semicolons
If the range of cells with the original values is on the current sheet, you just need to select it with the mouse.
If it is located on another sheet of this workbook, then you will have to give it a name in advance (select cells, press CTRL+F3, enter the name of the range without spaces), and then write this name in the field