September 16th, 2017

To be honest, I am not a big expert in Excel. Many, even the simplest automation processes in it baffle me. Therefore, this article was very interesting and useful to me. Maybe you will learn something new for yourself. So let's read...

Today we want to bring to your attention a new portion of tips to speed up your actions in this program.Nikolai Pavlov, the author of the “Planet Excel” project, will talk about them, changing people’s understanding of what can actually be done using this wonderful program and everything Office package. Nikolay is an IT trainer, developer and product expert Microsoft Office, Microsoft Office Master, Microsoft Most Valuable Professional. Here are the techniques he personally tested for accelerated work in Excel. ↓

Quickly add new data to a chart

If for your already constructed chart there is new data on the sheet that needs to be added, then you can simply select the range with the new information, copy it (Ctrl + C) and then paste it directly into the chart (Ctrl + V).

Flash Fill

This feature only appeared in the latest Excel versions 2013, but worth upgrading to new version ahead of schedule. Let's assume that you have a list of full names (Ivanov Ivan Ivanovich), which you need to turn into abbreviated names (Ivanov I.I.). To perform such a conversion, you just need to start writing the desired text in the adjacent column manually. On the second or third Excel line will try to predict our actions and perform further processing automatically. All you have to do is click Enter key to confirm and all names will be converted instantly.

In a similar way, you can extract names from emails, merge full names from fragments, etc.

Copying without breaking formats

You most likely know about the “magic” autofill marker - a thin black cross in the lower right corner of a cell, by pulling which you can copy the contents of the cell or a formula to several cells at once. However, there is one unpleasant nuance: such copying often violates the design of the table, since not only the formula is copied, but also the cell format. This can be avoided if, immediately after dragging the black cross, click on the smart tag - a special icon that appears in the lower right corner of the copied area.

If you select the “Copy values ​​only” option (Fill Without Formatting), then Microsoft Excel will copy your formula without formatting and will not spoil the design.

Displaying data from an Excel spreadsheet on a map

IN latest version Excel 2013 now has the ability to quickly display interactive map your geodata, for example sales by city, etc. To do this, go to the “App Store” (Office Store) on the “Insert” tab and install the Bing Maps plugin from there. This can also be done using a direct link from the site by clicking Add button. After adding a module, you can select it from the My Apps drop-down list on the Insert tab and place it on your worksheet. All you have to do is select your data cells and click on the Show Locations button in the map module to see our data on it.

If desired, in the plugin settings you can select the type of chart and colors to display.

Quickly jump to the desired sheet

If the number of worksheets in your book exceeds 10, then it becomes difficult to navigate through them. Click right click mouse on any of the buttons for scrolling the sheet shortcuts in the lower left corner of the screen.

Selection (adjustment) of calculation results to the required values

Have you ever fit the input values ​​in your Excel calculation to get the output desired result? At such moments you feel like a seasoned artilleryman, right? Just a couple of dozen iterations of “undershooting - overshooting”, and here it is, the long-awaited “hit”!

Microsoft Excel can do this adjustment for you, faster and more accurately. To do this, click the “What If Analysis” button on the “Insert” tab and select the “Parameter Selection” command (Insert - What If Analysis - Goal Seek). In the window that appears, specify the cell where you want to select desired value, the desired output and the input cell that should change. After clicking “OK,” Excel will perform up to 100 “shots” to find the total you require with an accuracy of 0.001.

Well, and some more simple usefulness about Excel:

With the release of Excel 2010, Microsoft has almost doubled the functionality of this program, adding many improvements and innovations, many of which are not immediately noticeable. Whether you're an experienced user or a beginner, there are many ways to make using Excel easier. We will talk about some of them today.

Select all cells with one click

All cells can be selected using the combination Ctrl keys+ A, which, by the way, works in all other programs. However, there is an easier way to select. By clicking on the button in the corner of the Excel sheet, you will select all cells with one click.

Opening multiple files at once

Instead of opening each Excel file individually, you can open them together. To do this, highlight the files you want to open and press Enter.

Navigate Excel Files

When you have multiple workbooks open in Excel, you can easily navigate between them using the Ctrl + Tab key combination. This feature is also available throughout Windows system, and can be used in many applications. For example, to switch tabs in the browser.

Adding new buttons to the Quick Access Toolbar

Standard in the quick panel Excel access There are 3 buttons. You can change this quantity and add those that you need.

Go to File ⇒ Options ⇒ Panel quick access" Now you can select any buttons you need.

Diagonal line in cells

Sometimes there are situations when you need to add a diagonal line to the table. For example, to separate date and time. To do this on home page In Excel, click on the familiar borders icon and select More Borders.

Adding empty rows or columns to a table

Inserting a single row or column is quite simple. But what if you need to insert many more of them? Select the required number of rows or columns and click "Insert". After that, select the place where you want the cells to move, and you will get the required number of empty lines.

High-speed copying and moving of information

If you need to move any information (cell, row, column) in Excel, select it and move the mouse over the border to change the pointer. After that, move the information to the place you need. If you need to copy information, do the same, but with the Ctrl key held down.

Quickly remove empty cells

Blank cells are the bane of Excel. Sometimes they just appear out of nowhere. To get rid of them all at once, select desired column, go to the Data tab and click Filter. A downward arrow will appear above each column. By clicking on it, you will be taken to a menu that will help you get rid of empty fields.

Advanced Search

By pressing Ctrl + F, we get to the search menu, with which you can search for any data in Excel. However, its functionality can be extended by using the "?" characters. And "*". A question mark represents one unknown character, and an asterisk represents several. They are worth using if you are not sure what the query you are looking for looks like.

If you need to find question mark or asterisk and you don’t want Excel to look for an unknown character instead, then put “~” in front of them.

Copying unique entries

Unique entries can be useful if you need to highlight non-repeating information in a table. For example, one person of each age. To do this, select the desired column and click “Advanced” to the left of the “Filter” item. Select the source range (where to copy from) and the range where you want to place the result. Don't forget to check the box.

Creating a selection

If you are doing a survey that is limited to men aged 19 to 60, you can easily create a similar sample with using Excel. Go to the menu item “Data” ⇒ “Data Validation” and select the required range or other condition. When entering information that does not meet this condition, users will receive a message that the information is incorrect.

Quick navigation using Ctrl and arrow

By pressing Ctrl + arrow, you can move to the extreme points of the sheet. For example, Ctrl + ⇓ will move the cursor to the bottom of the sheet.

Transposing information from a column to a row

Enough useful feature, which is not needed very often. But if you suddenly need it, you are unlikely to transpose one at a time. There is a special paste for transposing in Excel.

Copy the range of cells you want to transpose. After that, right-click on the desired location and select Paste Special.

How to hide information in Excel

I don’t know why this might be useful, but nevertheless there is such a function in Excel. Select the desired range of cells, click Format ⇒ Hide or Show, and choose the desired action.

Combining text with "&"

If you need to combine text from multiple cells into one, you don't have to use complex formulas. Just select the cell in which the text will be connected, press “=” and select the cells sequentially, placing the “&” symbol in front of each one.

Changing the case of letters

Using certain formulas you can change the case of the entire text information in Excel. The "UPPERCASE" function makes all letters uppercase, and the "LOWER" function makes all letters lowercase. "PROPNACH" capitalizes only the first letter of each word.

Entering information with leading zeros

If you enter in Excel number 000356, then the program will automatically turn it into 356. If you want to leave leading zeros, put an apostrophe “’” in front of the number.

Speed ​​up typing difficult words

If you type the same words often, you'll be glad to know that Excel has AutoCorrect. It's very similar to autocorrect on smartphones, so you'll immediately understand how to use it. With its help, you can replace repeated structures with abbreviations. For example, Ekaterina Petrova is an EP.

More information

In the lower right corner you can follow various information. However, few people know that by right-clicking there, you can remove unnecessary lines and add the necessary lines.

Renaming a sheet using double click

This is the easiest way to rename a sheet. Just double-click on it with the left mouse button and enter a new name.

And here are some more useful things for you: for example, and here are some. Did you also know

• Tutorial

Preface

It just so happens that today many people have to work (write macros) in VBA in Excel. Some macros contain hundreds of lines of code that have to be executed every day (week, month, quarter, etc.) and, at the same time, they take a fair amount of time. It seems that the process is automated and human intervention is not needed, but the time taken to execute a macro can span tens of minutes, or even several hours. Time, as they say, is money, and in this post I will try to significantly speed up the execution time of your macro and, perhaps, this will have a positive effect on your business, and ultimately money.

Before starting work

Before we get straight to the point, I'd like to take a look at the post: Some Tips for Working with VBA in Excel. In particular, in the “Speed ​​up macros” block there is useful examples code that should be used along with my tips for speeding up work to achieve maximum results.

Speeding up the macro

So, to the point... In order to really speed up the work of VBA in Ecxel, you need to understand that accessing a cell on a sheet takes a lot of time. If you want to write one value to a cell, it will not take much time, but if you need to write (read, access) thousands of cells, it will take much more time. What to do in such cases? Arrays come to the rescue. Arrays are stored in memory, and VBA performs operations in memory hundreds or even thousands of times faster. Therefore, if you have thousands, hundreds of thousands of values ​​in your data, then the macro execution time can take from several minutes to several hours, and if this data is transferred to an array, then the macro execution can be reduced to several seconds (minutes).

I'll give an example of the code and explain in the comments what's what, it will be clearer. In addition, some lines of code that are not directly related to the acceleration process may be useful.

Example

Let's assume that we have data on “Sheet1”. The data is contained in 50 columns (the columns contain names) and 10,000 rows. For example, we need to enter a value in the last column that is equal to the value in the second column divided by the value in the third column (starting from the 2nd line, since the first contains the title). Then we will take the first 10 columns and copy them to “Sheet2” for further processing (for other needs). Even if the example is banal, it seems to me that it can reflect the whole essence of this post.

"To explicitly initialize variables, enable this option "This will help avoid many errors Option Explicit Sub Test() "We will access the sheets through the variables Dim Sheet1_WS, Sheet2_WS As Worksheet "Variable for passing the deadline on the sheet (in the array) Dim i As Long " An array in which our data will be stored Dim R_data As Variant "Variables of the last row and column Dim FinalRow, FinalColumn As Long "You can initialize the sheet not by name, but by serial number "Set Sheet1_WS = Application.ThisWorkbook.Worksheet("Sheet1") Set Sheet1_WS = Application.ThisWorkbook.Sheets(1) Set Sheet2_WS = Application.ThisWorkbook.Sheets(2) "Search for the last non-empty row in the first column" It is necessary that the data is not filtered, otherwise the last row will be last line in the filter "Also in the last line, in the first column, there should not be empty cell. Of course, if there is any data in this line at all. Otherwise, the last row will be the last non-empty cell. FinalRow = Sheet1_WS.Cells(Rows.Count, 1).End(xlUp).Row "=10 000 "Finds the last non-empty column in the first row FinalColumn = Sheet1_WS.Cells(1, Columns.Count).End(xlToLeft). Column "=50 "Assign the data range on Sheet 1 to the array R_data = Sheet1_WS.Range(Sheet1_WS.Cells(1, 1), Sheet1_WS.Cells(FinalRow, FinalColumn)) For i = 2 To FinalRow "Perform the operations we need with the data. “We check that there is no division by zero. "It is assumed that columns 2 and 3 contain numeric data "Otherwise error handling will be required If R_data(i, 3)<>0 Then R_data(i, FinalColumn) = R_data(i, 2) / R_data(i, 3) End If Next i "Copy the data from the array back to Sheet1 "Before that, clear the data on the sheet (if there is formatting or formulas, then it’s better Sheet1_WS.Cells.ClearContents) Sheet1_WS.Cells.Delete Sheet1_WS.Range(Sheet1_WS.Cells(1, 1), Sheet1_WS.Cells(FinalRow, FinalColumn)) = R_data "Copy the data to Sheet2, copy the first 10 columns. Sheet2_WS.Range( Sheet2_WS.Cells(1, 1), Sheet2_WS.Cells(FinalRow, 10)) = R_data "Close the workbook and save it Workbooks(Application.ThisWorkbook.Name).Close SaveChanges:=True End Sub

IN in this example the array is filled with the specified range. If we have an explicitly specified two-dimensional array, then you can copy its value to the sheet like this:

Dim R_new() As Variant ............................................ " Explicitly specify the size of the array ReDim R_new(1 To FinalRow, 1 To 50) As Variant ................................... .......... Sheet1_WS.Range(Sheet1_WS.Cells(1, 1), Sheet1_WS.Cells(FinalRow, 50)) = R_new()

Conclusion

Most operations on data can be performed in an array, and only the result can be displayed on the sheet. Sometimes it makes sense to display the result on a sheet, then perform some actions (for example, sorting) and load the data into the array again.

It was a big surprise for me that the macro could be accelerated using arrays, since the data on the sheets is, in fact, a two-dimensional array. But it turns out that accessing memory occurs much faster than accessing cells on a sheet.

In the future I plan to write tips (examples) on quick search data on the sheet, but that will be another post. If you have any questions or comments, please write.

Thank you for your attention. Happy development.

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 features (unnecessary 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 right-clicking - 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 big 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:

From time to time people contact me with questions about “why the office is slow.” IN this moment dealing with slow startup Excel programs from Office 2007. The problem is quite common. There may be many different reasons and, accordingly, different ways solutions. In order not to keep all this in my head, I am collecting all the methods found in this article.

Method one

Here is a translation of an article in English that I found on the Internet while searching for a solution to the problem:

Problem

If Excel is not yet running, double-clicking on a file will launch it instantly, but the clicked file may take a minute to load.

Solution

On Windows XP this problem can be solved as follows:

1. Run Conductor(Explorer)
2. Select menu Service(Tools)
3. Menu Folder properties(Folder Options)
4. Tab File types(File Types)
5. Find an element XLS
6. Button Additionally(Advanced)
7. List item Open(Open)
8. Button Change...(Edit...)

In the dialog box that appears, you must make two changes:

Application executing the action:(Application to perform action:)

At the end of the line, after the /e switch, add a space and "%1" (with quotes)

Unlaunched DDE application: (DDE Application Not Running:)

Paste text: (along with parentheses)

Details

We discovered the problem after upgrading to MS Office 2007. My father noticed that Excel spreadsheet loads quickly if you include the path to EXCEL.EXE on the command line. Loading a document via DDE, as Explorer does, seems to take much longer, according to at least, if the application is not yet running. The changes described above pass the file path immediately when the application is launched. The DDE command run later is just a comment and, as an empty line, ends up doing the default action from the box two lines above. Key /e seems to prevent an empty table from being opened, so I ruled it out first since if a file is passed in, an empty table usually won't open anyway. However, I received a message from a person ( there was a link to a Microsoft forum page that no longer exists. - translator's note) using the key /e, and since it doesn't bother me either, I suggest leaving it in place. If Excel is already running, the next document will be opened via DDE as before. As a result, only one Excel process will be running.

Word

Time and time again I get asked if I know a similar trick for Word. Even without any tricks, Word starts up quite quickly, but adding "%1" gave me a slight increase in startup speed. I don't know exactly what "" is. It's like a "special comment" that has some effect despite being a comment. I decided to copy this comment into the box " DDE application not running" and did not notice any unwanted effects. I also left the keys /e And /dde V command line, simply because I have not found any harm from them.

Return to previous state

If you tried these recommendations but they didn't help, reverting the settings to as before may turn out to be more difficult than you expect. It seems like the window you make changes to always adds %1 to the command when you click OK. The only solution I was able to find is to use the Registry Editor. The command is stored in the default key in the branch: HKEY_CLASSES_ROOT\Excel.Sheet.8\shell\Open\command

If you also want to return the DDE setting, check out the next thread ddeexec and delete the branch IfExec entirely.

other methods

Network resource unavailable

• Check if the system has network printers, which are currently unavailable. Office applications at startup they may try to detect them and wait a long time for a response.
• In the registry in the branch HKEY_CLASSES_ROOT\Applications find programs that have a path like \\another_comp\folder\app.exe. If this network resource is not available, make it available, or remove this setting. (First try adding an empty string parameter to this program NoOpenWith.)

Large file size

Remove invisible objects:

1. Make a backup copy of the file
2. Open it up
3. Click Ctrl+G
4. Button Select...
5. Switch to objects
6. Click OK
7. If the sign No objects found- go to the next sheet and try again
8. If you're stuck, don't worry, wait
9. After objects are selected, delete them with the key Delete on keyboard
10. Repeat this step on all sheets of the book.
11. Save the document and try how it opens now

File format

If the file is in the format xls(Office 2003 format), try resaving in a new format. Menu File (Office Logo) -> Save As -> Excel Workbook (*.xlsx). After this, the file will become slightly smaller and will be opened faster by Office 2007 and 2010.

Third Party Add-ons

1. Menu File(Office logo in the upper left corner)
2. Button Excel Options
3. On the left side of the window, select Add-ons
4. At the bottom, from the drop-down list, select COM add-ins
5. Click Go...
6. If there are add-ons in the window that opens, try disabling them one by one and check for changes in startup speed

Excel files take a long time to open due to:

• a lot of 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.

All of these brakes can be corrected, except perhaps the last one.

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 Special insert and specify the 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 Reviewing - Book Access tab. 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