Conditional formatting (5)
Lists and ranges (5)
Macros (VBA procedures) (63)
Miscellaneous (39)
Excel bugs and glitches (3)

How to speed up and optimize VBA code

Sooner or later, those writing in Visual Basic for Applications have a problem - although the code makes life easier and does everything automatically, it takes a very long time. In this article, I decided to collect a few simple recommendations that will help speed up the work of VBA code, and in some cases it is quite impressive - dozens, or even more, times. The main focus of the article is on beginners, so the simplest optimization methods are given at the beginning of the article. More "deep" solutions for code optimization are given at the end of the article, because to apply these solutions, you need sufficient experience in VB and immediately such optimization methods may not be clear to someone.

1. If there are a lot of Activate and Select in the code, especially in cycles, you should immediately get rid of them. How to do this, I wrote in the article: Select and Activate - why are they needed and are they needed?
2. Be sure to disable during code execution:
   • automatic recalculation of formulas. So that formulas are not recalculated with each manipulation on the sheet during code execution - this can wildly slow down the code if there are a lot of formulas:

     Application.ScreenUpdating = False

   • Just in case, disable event tracking. It is necessary so that Excel does not execute any event procedures that may be in the sheet in which changes are made. As a rule, these are events of changing cells, activating sheets, etc.:

     ActiveWorkbook.ActiveSheet.DisplayPageBreaks = False

     if printing is done inside the code, then it is advisable to insert this line immediately after the line that prints the sheet (provided that printing does not occur in a cycle. In this case, after the completion of the printing cycle).

   • Just in case, you can turn off the display of information in the Excel status bar (in which cases information is displayed there at all and why you can find out in the article: Display the progress). Although this does not consume much resources, sometimes it can still speed up the code:

     Application.StatusBar = False

     Application.StatusBar = False

   The main thing to remember - all of these properties need to be turned back on after the code runs. Otherwise, there may be problems with working inside Excel. For example, if you forget to enable auto-recalculation of formulas - most formulas will be recalculated exclusively by the forced method - Shift+F9. And if you forget to turn off the screen update - that is, there is a chance to block yourself from the ability to work on sheets and books. Although by default the ScreenUpdating property should return True if it was disabled inside the procedure, it's better not to rely on this and get used to returning all properties to their proper places forcibly. In fact, it all boils down to a few lines:

   "Return screen update Application.ScreenUpdating = True "Return auto-recalculation of formulas Application.Calculation = xlCalculationAutomatic "Enable event tracking Application.EnableEvents = True

   What does this code look like in practice? Suppose you need to write in a loop of 10,000 lines of values:

   Sub TestOptimize() "disable screen updating Application.ScreenUpdating = False "Disable auto-recalculation of formulas Application.Calculation = xlCalculationManual "Disable event tracking Application.EnableEvents = False "Disable pagination ActiveWorkbook.ActiveSheet.DisplayPageBreaks = False "Direct cell filling code Dim lr As Long For lr = 1 To 10000 Cells(lr, 1).Value = lr "for example, just number the lines Next "Return screen update Application.ScreenUpdating = True "Return auto-recalculation of formulas Application.Calculation = xlCalculationAutomatic "Enable event tracking Application. EnableEvents = True EndSub

   Breaks in printed pages can not be returned - they slow down the work in any case.

3. Loops like Do While to find the last cell should be avoided. Beginners often make this mistake. It is much more efficient and faster to calculate the last cell on the entire sheet or in a specific column without this braking Do While loop. I usually use

   lLastRow = Cells(Rows.Count,1).End(xlUp).Row

   lLastRow = Cells(Rows.Count,1).End(xlUp).Row

   I described other options for determining the last cell in detail in the article:

For more experienced VBA users, I will give several solutions for optimizing codes in various situations:

• The best optimization of the code, if you have to work with the cells of the sheet directly, process them and, possibly, change the values, then it is faster to do all the processing in an array and unload it on the sheet at once. For example, the code above for filling cells with numbers will look like this in this case:

  Sub TestOptimize_Array() "Directly the code for filling cells Dim arr, lr as long "remember in an array in one fell swoop all the values ​​of 10000 rows of the first column arr = Cells(1, 1).Resize(10000).Value "if you need padding for two or more columns "arr = Cells(1, 1).Resize(10000, 2).Value"or "arr = Range(Cells(1, 1),Cells(10000, 2)).Value "or automatically calculate the last cell and enter data into the array, starting from cell A3 "llastr = Cells(Rows.Count, 1).End(xlUp).Row "last cell of column A "arr = Range(Cells(3, 1),Cells(llastr, 2)).Value For lr = 1 To 10000 arr(lr,1) = lr "Fill the array with serial numbers Next "Upload the processed array back to the sheet in the same cells Cells(1, 1).Resize(10000).Value = arr End Sub

  Sub TestOptimize_Array() "Directly the code for filling cells Dim arr, lr As Long "remember all the values ​​of 10000 rows of the first column arr = Cells(1, 1).Resize(10000).Value "(!LANG: if you need to fill for two or more columns"arr = Cells(1, 1).Resize(10000, 2).Value "or"arr = Range(Cells(1, 1),Cells(10000, 2)).Value "or automatically calculate the last cell and enter data into the array, starting from cell A3"llastr = Cells(Rows.Count, 1).End(xlUp).Row "последняя ячейка столбца А "arr = Range(Cells(3, 1),Cells(llastr, 2)).Value For lr = 1 To 10000 arr(lr,1) = lr "заполняем массив порядковыми номерами Next "Выгружаем обработанный массив обратно на лист в те же ячейки Cells(1, 1).Resize(10000).Value = arr End Sub!}

  But here one should also take into account the fact that large arrays can simply cause memory overflow. This is most relevant for 32-bit systems, where less memory is allocated for VBA and Excel than in 64-bit systems.

• If you use fast IF - IIF , then replace it with IF ... Then ... Else
• It is also better to use the same IF ... Then ... Else instead of Switch() and Shoose()
• In most cases, checking a string for "not empty" is better with Len() than a direct comparison with emptiness: Len(s)=0 instead of s = "" . This is due to the fact that working with strings is much slower than working with numeric data.
• Don't apply string concatenation unnecessarily. For example, s = "AB" will be faster than: s = "A" & "B"
• Do not directly compare text values. It's better to use the built-in StrComp function:
  If s s1 Then will be slower than
  If StrComp(s, s1, vbBinaryCompare) = 0
  and even more so if the comparison needs to be case-insensitive:
  If LCase(s) LCase(s1) Then will be slower than
  If StrComp(s, s1, vbTextCompare) = 0
• For...Next loops are generally faster than Do...Loop loops.
• Avoid assigning variables of type Variant . Although the temptation is great, this type takes a lot of memory and further slows down the code. Also, for object variables, the faceless global type Object should be avoided whenever possible and a concrete type should be used:

  Dim rRange as Range, wsSh as Worksheet

• If you work with arrays, then you can specify this explicitly when declaring:

  Dim arr() as string, arr2() as long

  but this is only if there is confidence that strictly specified data types will be entered into the array

Of course, these are not all tricks and solutions for optimization. But the first couple should be enough. Plus, you should always use common sense. For example, if the code runs in 2 seconds, then there is probably no point in optimizing it further. Of course, if this code is not one of those that simply change the value of one or two cells.

Did the article help? Share the link with your friends! Video lessons

("Bottom bar":("textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24," textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance" :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600 ,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500," textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100% ; background-color:#333333; opacity:0.6; filter:a lpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive": "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))

Holds 32,767 characters. Therefore, the function of quickly changing the width of a column according to its content is not always convenient. :) Although in most cases it helps out. Move the mouse cursor over the right border of the column heading 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 worksheet.

2. How to quickly select an entire row or column

When working with large amounts of data, it is impossible to do without keyboard shortcuts, with which it is easy to move around the sheet and select individual columns or rows. Combinations Ctrl+Spacebar And Shift + Spacebar just responsible for the quick selection of the current column or row.

I once watched a four year old use the copy and paste hotkeys to enter a game key. Looks like an inseparable trinity ctrl+c, Ctrl+V And Ctrl+A absorbed by the young generation along with mother's milk. By the way, the "Select All" command in Excel has a small feature: a single click Ctrl+A selects the current range, and double selects the entire table.

4. How to make changes to multiple sheets at the same time

Steamed turnip, your way out! For example, to change the font size on all sheets at once, you need to collect them in a group by clicking on the tabs of the sheets 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 in - ask him to enter the data inside the cell in several lines. Of course, a school trick, but it works with a bang with the older generation. Combination Alt+Enter prevents jumping to another cell and moves the cursor to a new line within the current cell.

Inserting an additional row or column takes three or four mouse clicks. This is not much, but only until you need a dozen new columns at once, for example. However, having previously selected the required number of already existing cells, you can get off 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 hymn. These combinations should be repeated until there is no hard worker left on the planet who drives in the date and time manually.

• tutorial

Foreword

It so happened 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, and so on) and, at the same time, they take a fair amount of time. It seems that the process is automated and no human intervention is needed, but the time it takes 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 I get straight to the point, I'd like to draw your attention to the post: A Few Tips for Working with VBA in Excel. In particular, the “Speed ​​up macros” block has useful code examples that you should use 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 into a cell, then it will not take much time, but if you need to write (read, refer) to thousands of cells, then 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 you transfer this data to an array, then the macro execution can be reduced to several seconds (minutes).

I will provide an example 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 come in handy.

Example

Suppose we have data on “Sheet1” (“Sheet1”). The data is contained in 50 columns (columns contain titles) 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” (“Sheet2”), for further processing (for other needs). Although 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 term on the sheet (in the array) Dim i As Long" 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-blank row in the first column" Also, in the last row, in the first column, there should not be an empty cell.Of course, if there is data in this row at all.Otherwise, the last row will be the last non-empty cell.FinalRow = Sheet1_WS.C ells(Rows.Count, 1).End(xlUp).Row "=10 000 "Search for 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 data operations we need. "Check that there is no division by zero. "It is assumed that columns 2 and 3 are numeric data "Otherwise, error handling is 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 data to Sheet2, copy 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 this example, the array is filled with the specified range. If we have an explicitly defined two-dimensional array, then we can copy its value to the sheet in this way:

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, while displaying only the result on the sheet. Sometimes it makes sense to display the result on a sheet, then perform some actions (such as sorting) and load the data into an array again.

For me, it was a big surprise to speed up the work of the macro due to arrays, since the data on the sheets, in fact, is already a two-dimensional array. But it turns out that accessing memory is much faster than accessing cells on a sheet.

In the future, I plan to write tips (examples) for quickly finding data on a sheet, but this will be another post. If you have questions, comments, please write.

Thanks for attention. Happy development.

Economists typically spend about half of their working time in Excel. At the same time, for many of us, communication with the program gives quite a few headaches, because. performing routine operations takes a lot of time. Let's talk about how to work faster in Excel.

There are practically no alternatives to Excel. Not surprising, because Excel allows you to do almost everything. It is like an empty easel for an artist. You sit down at it and start painting your picture.

A smear, another smear… Several hours pass and… A couple of small plates and a graph with a standard design. Sad isn't it?

And it happened to me. You work, you work. Already exhausted, and the work has just begun. What to do with it?

There is an exit! True, not everyone knows about him, and therefore they have to stay at work, postpone gatherings with friends, dates with loved ones and just burn out at work. I don’t know about you, but it doesn’t suit me, and today I will tell you my secrets of speed work in Excel.

Interesting? Then let's go!

The first thing to do is to throw out the mouse!

That's how you read it right. To work faster in Excel, you need to remove the mouse. ONLY do not rush to close the tab, now everything will become clear.

I understand that the mouse is very dear to you. You cannot live without her, but believe me, it is she who stops you. I'm not crazy, I just know a much more convenient and faster way to navigate Excel pages and simply don't use this wonderful gadget.

It's all about the hot keys that allow you to simply switch between pages with lightning speed, select, add or delete buildings / columns. In general, knowledge of literally 5-7 combinations speeds up work by an order of magnitude. Many mouse navigation operations take 3-5 or even 10 times longer.

I'll give you an example. To add multiple rows in Excel, there are three options:

1. Option 1. Take the mouse, hover it over the corresponding column or several columns, depending on how many columns you need to insert. Right-click and select Paste from the drop-down box.
2. Option 2. Using the buttons on the Home tab in the Cells group. Again, select, go to the Home tab. Click on the corresponding buttons to get the result.
3. Option 3. Press to highlight a column. Ctrl + 'Space', to add a column click Ctrl+'+'.

You can say that the savings are not great. In one way I will do it in 15-20 seconds, in another in 3-5. And what will I do with the freed 12-15 seconds? And in general, how many hot keys do you need to learn to feel the difference?

Agree! The time saved by one hot key is not great, but all our work in Excel is a monotonous repetition of the same operations hundreds of times in different sequences. Means if conditional economy on one operation we will assume 10 seconds. When repeating one operation 20-30 times a day, saving from knowing one combination of hot keys is 3-5 minutes. So knowing 10 hot keys saves 30-50 minutes of time!!! Now that's cool!!!

Tip number two follows from the first - learn hotkeys. There are not so many useful hot keys, so after learning 10-20 combinations, you will quickly feel the difference in speed.

"Good advice!" - you object. “And how do you teach them? Stand on a stool in front of colleagues and recite like poetry? 🙂

No. Of course, it's not like that. try to find your own way of learning hotkeys. What can be done for this?

For example, you can take a list of hotkeys and select 3-5 most used functions. Try to work them out, practice how they work. Get used to pressing them when you need them. Over time, the fingers themselves will lie on the keyboard so that you can quickly and conveniently switch between sheets. Learned these - go to the next, and then another and another.

And of course, here is a list of useful shortcuts and hotkeys:

Hotkeys will definitely help you work faster in Excel. Train in their use and very quickly the speed of your work will increase significantly. But that is not all!

I have a few more tips in store. Ready? Go!

Excel tip number THREE. Functions and their combinations that you must master

It may sound trite, but you need to know the functions to work faster in Excel. I am sure that you, my dear reader, are fluent in Excel functionality and can make very cool reports and calculations in Excel. However, it is possible that not all functions are subject to you, and there is something to strive for.

Also, each function can have many features of use. So, for example, do you know that the SUM function can add values ​​from different worksheets in your workbook without having to select each of them individually? Those.

Instead of a formula

=SUM(Sheet1!A1; Sheet2!A1; Sheet3!A1; Sheet4!A1; Sheet5!A1; Sheet6!A1; Sheet7!A1; ... SheetN!A1)

The formula will look like

=SUM(Sheet1:SheetN!A1)

What is all this for? In addition to the rich functionality of Excel, which is in its standard formulas, there are many combinations, the knowledge of which allows you to solve non-standard tasks. So, Excel does not have a MINESLI function at all. Yes, there is SUMMESLI, COUNTIF, but MINESLI didn't. Also MAXIF, MEDIANIF, etc., but all this is solved by using area functions. You may have seen when a formula is wrapped in curly braces.

Some functions work fine only in conjunction. This is what I'm talking about now about INDEX and SEARCH. It would seem that stupid functions individually, but in pairs they provide excellent functionality.

What is needed for this? For example, subscribe to our group in Facebook and wait for new posts. 😉

Also see the list of formulas that I recommend you pay attention to:

There are many more functions in Excel, but you can deal with these for now. I am sure that most of them will help you work faster in Excel and be at least significantly more efficient.

Excel tip number FOUR. Structure and formatting in files

If you have come across the principles of modeling, then you obviously know that in order to avoid errors, it is enough to put things in order in your calculations.

Confusing calculations, piling up and inconvenient navigation lead to the fact that even the author himself begins to stray in the file.

How can this be avoided? I'll give you some advice:

• Format the file. Let you have the same number of decimal places, the same font throughout the document, and a limited color palette. I now like to use palettes of the same color but different tones (light blue, blue and dark blue). Looks very stylish.
• Separate input data, calculations and results. This is not always necessary, but when there is a lot of data, the presence of a summary plate simply saves.
• Do the same formulas by column or row. agree it is not very correct when the same indicator in different periods is considered differently. In this case, usually this is not visible until you look at the formula. and sometimes you can forget.
• Try to avoid circular references and references to external files. Usually they give different results on different computers. There was a case when the error in updating external links was greater than 7 zeros. Ay!
• Simplify and save space. Excel allows you to make as many rows and columns as you like. In my 10 years of working with the program, I have never used a sheet completely. It is unlikely that you will succeed, so do not fence complex formulas. Better do the calculation in several steps.

Here are some tips. Believe me, they make life easier. There are fewer errors, calculations are more accurate and clearer. if you have any other tips, leave them in the comments.

In this advice I am not original at all. Ironically, achieving mastery is up to you and how much and how hard you practice.

I would like to give an intensive course for 28 minutes, after which everyone will know Excel better than its developers know, but alas. This doesn't happen. Constant and hard work leads to truly impressive results. That is why very soon we are launching an Excel training, which will be available on our website for free (at least for the first 1000 users).

Pavlov Nikolay

In this article, I would like to present to you the most effective Microsoft Excel techniques that 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 tricks for every day - simple and effective, described without "water" - only "dry residue". Most of these examples will take you no more than a minute or two to master, but they will save you much more.

Quick jump to the desired sheet

Do you happen to work with Excel workbooks that consist of a large number of sheets? If there are more than a dozen of them, then each transition to the next desired sheet in itself becomes a small problem. 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 - the table of contents of the book will appear with a complete list of all sheets and you can go to the desired sheet in one movement:

This is much faster than scrolling through the sheet tabs with the same buttons in search of what you need.

Copy without formatting damage

How many hundreds (thousands?) of times have I seen this picture, standing behind my listeners during trainings: the user enters the formula in the first cell and then "stretches" it to the entire column, breaking the formatting of the lines below, since this method copies not only the formula, but also the cell format. Accordingly, further it is necessary to manually correct the damage. A second for copying and then 30 - for repairing the design spoiled by copying.

Starting with Excel 2002, there is a solution to this problem - simple and elegant. Immediately after copying (dragging) the formula to the entire column, you need to use the smart tag - a small icon that temporarily appears in the lower right corner of the range. Clicking on it will display a list of possible copying options, where you can select Copy only values ​​​​(Fill without formatting). In this case, the formulas are copied, but the formatting is not:

Copy only visible cells

If you have been working in Microsoft Excel for more than a week, you should have already encountered a similar problem: in some cases, when copying and pasting cells, more cells are inserted than were copied at first glance. This can happen 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 breaks in the numbering of visible rows. If we select, copy and paste data from this table in the usual way, we will get 24 extra rows. We want to copy and paste only the totals!

You can solve the problem by painstakingly selecting each row of totals while holding down the CTRL key - as you would to select non-contiguous 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 be copied (in our example it is A1:C29)

Press the F5 key on the keyboard and then the Special button in the window that opens.
A window will appear that allows the user to select not everything, but only the necessary cells:

In this window, select the option Only 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 exactly the visible cells and insert only the 5 lines we need instead of the unnecessary 29.

If there is a suspicion that you will have to do such an operation often, then it makes sense to add a button to the Microsoft Excel toolbar 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 move it with the mouse to the toolbar:

Turning rows into columns and vice versa

A simple operation, but if you don't know how to do it right, you can spend half a day manually dragging and dropping individual cells:

In fact, everything is 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 done in three steps: Copy the table

Right-click on an empty cell and select Paste Special

In the window that opens, set the flag Transpose (Transpose) and click OK:

Quickly add data to a chart

Let's imagine a simple situation: you have a report for the last month with a visual chart. The task is to add new numerical data to the chart already for this month. The classic way to solve it is to open the data source window for the chart, where you can add a new data series by entering its name and selecting the range with the desired data. And often this is easier said than done - it all depends on the complexity of the diagram.

Another way - simple, fast and beautiful - is to select cells with new data, copy them (CTRL+C) and paste (CTRL+V) directly into the chart. Excel 2003, unlike later versions, even supports the ability to drag a selected range of data cells and drop it directly into the chart with the mouse!

If you want to control all the nuances and subtleties, then you can use not the usual, but a special paste 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 new data will be added:

Similarly, you can easily create a chart using data from different tables from different worksheets. It will take much more time and effort to perform the same task in the classical way.

Filling empty cells

After exporting 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 gaps do not allow you to apply familiar and convenient tools such as autofilter and sorting to tables. Naturally, there is a need to fill in the voids with values ​​from higher cells:

Of course, with a small amount of data, this can easily be done by simple copying - by manually stretching each header cell in column A down to the empty cells. And if the table has several hundreds or thousands of rows and several dozen cities?

There is a way to solve this problem quickly and beautifully with a single formula:

Select all cells in the empty column (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, click the Select button. You will see a window that allows you to choose which cells we want to highlight:

Set the radio button 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 in the first selected cell (A2). Press the equals sign and then the up arrow on your keyboard. We get a formula that refers to the previous cell:

To enter the created formula at once into all the selected empty cells, press not the ENTER key, but the CTRL + ENTER combination. The formula will fill in all empty cells:

Now it remains only to replace the formulas with values ​​​​to fix the results. Select the range A1:A12, copy it and paste it into the cells with their values ​​using Paste Special.

Dropdown list in a cell

A trick 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 listeners on the first day.

The idea is very simple - in all cases when you have to enter data from some set, instead of manually entering the cell from the keyboard, select the desired value with the mouse from the drop-down list:

Selecting a product from a price list, a customer's name from the customer base, an employee's full name from the staff list, etc. There are many uses for this feature.

To create a dropdown list in a cell:

Select the cells where you want to create a drop down list.

If you have Excel 2003 or older, then 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 a semicolon

If the range of cells with initial values ​​is on the current sheet, it is enough just to select it with the mouse.

If he is on another sheet of this book, then he will have to give 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