Until now, sometimes with a smile I remember one of my first field corporate trainings 10 years ago.
Imagine: the open space office of the representative office of an international FMCG company, huge as a football field. Chic design, expensive office equipment, dress code, expats cooing in the corners – that’s all 🙂 In one of the meeting rooms, I start a two-day advanced training on the then current version of Excel 2003 for 15 key employees of the economic department, along with their leader. We get acquainted, I ask them about business tasks, problems, I ask them to show several typical work files. They show the kilometer length of unloading from SAP, the sheets of reports that they make on this, etc. Well, it’s a familiar thing – I mentally figure out topics and timing, adjust to the audience. Out of the corner of my eye, I notice how one of the participants, demonstrating a piece of his report, patiently pulls the cell with the formula down by the black cross in the lower right corner for several thousand lines, then skips the end of the table on the fly, pulls it back, etc. Unable to stand it, I interrupt him curling the mouse around the screen and show a double-click on the black cross, explaining about auto-completion down to the stop.
Suddenly I realize that the audience is suspiciously quiet and everyone is looking at me strangely. I imperceptibly look around myself wherever I can – everything is OK, my arms and legs are in place, my fly is buttoned up. I mentally rewind my last words in search of some terrible clause – there was nothing criminal, it seems. After that, the head of the group silently gets up, shakes my hand and says with a stone face: “Thank you, Nikolai. This training can be completed.
Well, in short, it turned out that none of them had a clue about double-clicking on a black cross and autocomplete. It somehow happened historically that there was no one to show them such a simple but necessary thing. The entire department pulled formulas manually for thousands of lines, poor fellows. And here I am. Oil scene. The head of the department then asked very much not to reveal the name of their company to anyone 🙂
Several times later there were similar situations, but only with individual listeners – most now, of course, know this function.
The question is different. After the first joy of mastering such a wonderful feature, most users come to understand that automatic copying of formulas by double clicking on the black cross (autocomplete marker) has all the positive aspects and negative ones:
- Copying does not always happen to the end of the table. If the table is not monolithic, i.e. there are empty cells in adjacent columns, then it is not a fact that autocomplete will work until the end of the table. Most likely, the process will stop at the nearest empty cell before reaching the end. If there are cells occupied by something below the column, then autocomplete will stop on them exactly.
- When copying cell design spoils, because By default, not only the formula is copied, but also the format. To correct, click on the copy options button and select Only values (Fill without format).
- There is no quick way to also conveniently stretch the formula not down but to the rightexcept to pull by hand. Double clicking on the black cross is just down.
Let’s try to fix these shortcomings with a simple macro.
Press keyboard shortcut left Alt + F11 or button Visual Basic tab developer (Developer). Insert new empty module via menu Insert – Module and copy the text of these macros there:
Sub SmartFillDown() Dim rng As Range, n As Long Set rng = ActiveCell.Offset(0, -1).CurrentRegion If rng.Cells.Count > 1 Then n = rng.Cells(1).Row + rng.Rows.Count - ActiveCell.Row ActiveCell.AutoFill Destination:=ActiveCell.Resize(n, 1), Type:=xlFillValues End If End Sub Sub SmartFillRight() Dim rng As Range, n As Long Set rng = ActiveCell.Offset(-1, 0).CurrentRegion If rng.Cells.Count > 1 Then n = rng.Cells(1).Column + rng.Columns.Count - ActiveCell.Column ActiveCell.AutoFill Destination:=ActiveCell.Resize(1, n), Type:=xlFillValues End If End Sub
Such macros:
- can fill not only down (SmartFillDown), but also to the right (SmartFillRight)
- do not spoil the format of the cells below or to the right – only the formula (value) is copied
- empty adjacent cells are ignored and copying occurs exactly to the end of the table, and not to the nearest gap in the data or the first occupied cell.
For greater convenience, you can assign keyboard shortcuts to these macros using the button Macros – Options (Macros — Options) right there on the tab. developer (Developer). Now it will be enough to enter the desired formula or value in the first cell of the column and press the specified key combination for the macro to automatically fill the entire column (or row):
Beauty.
PS Part of the problem with copying formulas to the end of the table was solved in Excel 2007 with the advent of “smart tables”. True, they are not always and not everywhere appropriate. And to the right, Excel never learned to copy on its own.
- What are macros, how to use them, where to get Visual Basic code and where to paste it.
- Smart tables in Excel 2007-2013
- Copy formulas without link shift