How to fasten sheets with a paper clip. How to merge sheets in excel
On one of the pages of this site, a short article was published about how to save sheets of active excel workbook to separate files. It is time to consider the reverse question of how to collect information from different files in general and how to combine several files into one book in particular. This can be done easily and quickly, but more on that later.
Simultaneous work of users in one workbook
In some cases, several users have to collect or process information simultaneously. There are different methods that allow not to transfer the spreadsheet sequentially along the chain from one user to another, but to work in it simultaneously or in parallel. One such method is to share a document, where users can work on the data in that document at the same time.
Another way to speed up the process of collecting or processing data is to divide the workbook into separate sheets and then merge them after custom processing. We have already talked about how you can divide a workbook into separate sheets and save these sheets as separate files. Now let's take a closer look at how to combine them back.
Combining sheets of different workbooks into one
Combining different files into one or certain sheets into one book has a variety of solutions. This is the standard option with copying and pasting sheets into the right book, and the use of macros, and the installation of additional programs that specialize in merging files, such as MergeExcel. Each of these methods has its pros and cons. For my part, I want to offer another way to solve the problem of combining sheets from different books into one. Without having to deal with the macro code or install additional programs, you can quickly extend Excel with new features using an add-on called an add-in.
The add-in for combining various files into one was created on the basis of the VBA macro, but it compares favorably with its ease of use. Add-in is easy to connect and is launched with one click of a button displayed directly in the main menu, after which a dialog box appears. Further, everything is intuitive, files are selected, sheets of these files are selected, additional merge options are selected and the "Start" button is pressed.
macro (add-in) to merge multiple excel files into one workbook
The add-on allows:
1. With one click of the mouse, call the macro dialog box directly from the Excel toolbar;
2. select files to merge, as well as edit the list of selected files;
3. merge all sheets of selected files into one workbook;
4. merge into the workbook only non-empty sheets of the selected files;
5. collect sheets with a given name in the final book (you can use a mask using special match symbols);
6. collect sheets of selected files with a certain number (index) or a range of numbers into one book;
7. collect sheets with a specific value in a given range of cells;
8. set additional parameters for merging, such as:
a) assigning the names of the combined files to the sheets;
b) removal from the book in which the data is combined, own sheets that were in this book originally;
c) replacement of formulas with values (calculation results).
If necessary, the names of the sheets of the generated workbook can be quickly changed using the add-in for automatic sheet renaming.
Combining ranges of values from different sheets of different workbooks on a separate sheet
There is also the possibility in automatic mode iterate through all the workbooks specified in the dialog box, select only the necessary sheets in them, copy the user-defined ranges of values and paste them on a separate worksheet of the active workbook. In this case, you can choose how to place data on the sheet with totals, ranges of values can be located one after the other both horizontally and vertically. Thus it is possible
If the data that you want to analyze is on multiple sheets or in multiple workbooks, you can combine it on a single sheet using the Consolidate command. For example, if you have a separate expense sheet for each regional office, you can use consolidation to create a corporate expense sheet based on this data. Such a sheet might contain totals and averages of sales, current inventory levels, and most popular products across the organization.
The type of consolidation should be chosen taking into account how the sheets to be merged look like. If the data on the sheets is located in a uniform way (the names of the rows and columns may differ), use the consolidation by location. If the same row and column names are used on the sheets for the corresponding categories (the data may be arranged differently), use category consolidation.
Consolidation by location
Consolidation by category
Avg., and the other - Average
Consolidation by location
To consolidate by location, the range of data on each of the source worksheets must be in list format with no blank rows or columns.
Consolidation by category
To consolidate by category, the data range on each of the source sheets must be in list format with no blank rows or columns. In addition, the categories must be named the same. For example, if one of the columns is called Avg., and the other - Average, the consolidation will not sum these columns.
Hello, habralyudi!
I read on Habré about the stitching of a book and realized that I am not the only one interested in the topic. The soul started: the proposed method of classical firmware gives a high-quality book, but not everyone can master the effort and time invested in this labor feat. Plus, you need a lot of skill - no one can make a more or less high-quality book the first time. What is the cost of cutting a block alone - do you really think that at least once in your life you can do it exactly for a block of 200 sheets with a clerical knife? And if you want to do not a single book a year, but at least 2-3 a week? We would have simpler methods, and preferably no less effective. I share!
Method 1
With a volume of up to 40 sheets (and this is already 80 pages!) We sew with a simple rotary stapler in the middle of the sheets, making an ordinary notebook (such as a student's). To do this, we buy a rotary stapler, designed for a staple of great depth. Its working part is able to rotate 90 degrees, and a bracket of great depth (not width, but depth) can easily pierce up to 40 sheets. In a few seconds we have a neatly stitched book.
Method 2 (practically without limiting the volume of the book)
We print the book on A4 paper or smaller. We take a clerical hole punch, and selecting from a stack of 20-25 sheets, we make holes in them. It is very important here that the holes are on all sheets at the same distance, both from the edge of the sheet and from top to bottom. To do this, you need to have a hole puncher with a built-in alignment ruler. Such a hole punch costs exactly as much as without a ruler, but will allow your future book to look pretty neat. We simply insert the sheets with holes obtained in this way into a pre-purchased folder-folder. The whole variety of such folders comes down to the following types: folders on sliders, on ropes, on brackets. We choose a stapled folder by paying attention to the following:
:: The size of the bracket should be slightly larger than you need to insert all the sheets. Sheets should not fit back to back! After insertion, the pages should turn freely.
:: Staples should come apart as tight as possible.
:: When the staples are connected, there should not be the slightest gap between them, otherwise the sheet will not fall out, but will cling when turned over, which is very annoying.
:: It is desirable that the staples are separated by hand - by tearing, or with the help of tabs at the bottom and top of the folder. Do not buy a folder where the staples are bred with a bulky mechanism - it will not be convenient to use, the feeling of a “book” will disappear.
:: It is advisable to choose a soft cover for the folder. Its size should be larger than the sheets placed there. The best cover is made of plastic.
Just insert the sheets into such a folder - and the book is ready. And there is no need to think that this is a primitive: I have seen such books published by foreign publishers (although the staples there are one-piece). At first I thought they were just laughing at me. But no - foreign residents perceive such "books" as books. Well, for them, McDonald's is a restaurant.
Method 3
Buying a notebook right size and volume (they come in up to 200 sheets, these are 400 pages), on a plastic or metal spring, always with blank sheets (no “checkered” or “striped”). Carefully remove the spring (without any tools whatsoever). Print a book on the received sheets. Insert back. Very carefully compress the spring with your fingers, evenly pressing on each “tooth”. Do not pinch and do not remember the teeth (otherwise the book will look untidy), the sheets will not fall out anyway. The book is of decent quality.
Method 4
We print the book. Using a clerical hole punch, as in method No. 2, we make holes. But now we make a row of 4 holes - 2 higher, 3 lower. Do not forget to prepare the bottom and top cover in the same way. In the hardware store we buy a device for planting rivets or lurex. Through the resulting holes with the help of rivets or lurex we connect the pages and covers. If the covers are made of cardboard or half-cardboard, use a ruler to compress the cover along the opening line until the first opening of the book. If the cover is made of plastic - you need to scratch half the depth of the plastic along the opening line with a nail - along this line it will open (it may not be possible to make a neat groove the first time). Of course, such a book will not be opened "to the very spine" - this must be taken into account when printing out the contents. It turns out to be a very comfortable and beautiful book. With some skill, you can make a cover from a single piece of material - then the "spine" will not be visible from the outside.
Method 5
We buy a machine for binding on a plastic spring (such a “spring” bears little resemblance to a spring). The machine costs from $ 30, and it is no more difficult to use it than a toaster. Up to 500 sheets can be stapled using the plastic springs. There are similar machines for stapling to a metal spring, but they and the springs for them are more expensive and will sew you no more than 130 sheets. The resulting books are very easy to use. Correct name such machines "Binder for binding on a plastic (metal) spring." When buying, pay attention to the following: the body and handles must be metal; knives should be switched off individually - the more the better; there should be an adjustment of the indent from the edge; choose a machine designed for the maximum number of pages to be stitched, and for the maximum number of simultaneously punched pages - do not save here; all knives must move synchronously and without the slightest hook; the rest, including the manufacturer, does not really matter to the individual user.
Method 6
Let's make real books. "Real" are of two types: stitched and glued. Stitched ones are the highest quality, but also the most difficult to manufacture, which means they are not the topic of this article. Glued - the most common, take a look at your bookshelf: if the pages of the book in the spine area, under the cover, are connected by a half-millimeter layer of hardened glue - this is it. These are the books, and they are of professional quality, and we will make them at home without any problems. To do this, you need to buy a thermal binding machine for $ 50 and hot glue. The machine melts the initially solid hot melt adhesive. After printing and cutting, the block of sheets is inserted inside into the machine and crimped by it. On the finished block book cover is glued by hand. That's all. Up to 700 sheets can be stapled using this method (depending on paper thickness).
Method 7
Metal channel binding (metalbind) promises high quality, immediacy and low cost of binding up to 300-600 sheets of A4 80gsm thick at home. The device, which costs about $200, compresses the block with a metal bracket along the entire block. According to reviews - very reliable. Interesting feature- the bracket can be unclenched and reused up to 10-20 times.
Note:
I tried all these methods (except metalbind) myself. I have a lot of books sewn in this way. It's simple, fast, and really accessible to everyone. Good luck!
Tags: binding, books
Author Lady Bet asked a question in Other languages and technologies
How to merge all pages of a book in excel into one? then you need to make a selection and get the best answer
Answer from Ўlya Pavlova[guru]
It makes sense to merge the sheets of a book if these sheets contain the original data entered according to the same structure on each sheet. This can be done by copying the values from the sheet to the shared sheet.
If the sheets contain formulas, and even with various links, then copying will ruin all the work. Links must be relative.
Remember that the value of Excel is precisely in the formulas that are automatically calculated.
And if you work only with data, albeit in a tabular form, then you can easily get by with Word tables, where tables allow you to add visible rows similar in structure from other tables by copying or transferring through the buffer, but there are no calculated formulas there.
Selection in Excel can be done from several sheets, in a loop moving from sheet to sheet in the order of the sheets, or in the list of sheet names. All you need to do is create a cycle.
Answer from 2 answers[guru]
Hello! Here is a selection of topics with answers to your question: how to combine all the pages of a book in excel into one? need to make a selection
Answer from Striped giraffe Alik[guru]
What for? What is a sample? Some things can be done "through sheets"!
Answer from Vitaly Lapin[guru]
Excel has PivotTables, that's a good thing
Answer from Yoergey Rakovets - 1C Franchising[active]
wuvoo
Answer from Ozer Honest[guru]
Well, of course, there is no way to "combine" the sheets. Is that to copy all the information from them to one. Weird question. How does the program know how to arrange the information in a hypothetical "merge"? And there was never a need for such a thing. You describe what you need - somehow it will be clearer. And 100 or more sheets can easily be described in some situations by one formula. Well, no - still Visual Basic remains.
Answer from Irina Korchagina[newbie]
No need to merge pages
Suppose you have several sales reports for different regions. If you want to perform calculations and draw graphs on the entire amount of data, you need to collect all the data on one summary sheet. Switching between multiple workbooks, copying hundreds of lines endlessly, or writing VBA can take a long time.
With the Merge Sheets add-on, you can collect data into one pivot sheet in seconds:
- Collect sheet data from different workbooks on one sheet
- Collect sheet data with the same name and merge by tab name
- Merge sheet data with identical structure under one heading
- Keep Formatting in Result Pivot Sheet
Add "Merge Sheets" to Excel 2019, 2016, 2013, 2010
Suitable for: Microsoft Excel 2019 - 2010, desktop Office 365 (32-bit and 64-bit).
How to work with the add-on:
How to merge data from multiple sheets into one into one in 3 steps
With the Merge Sheets add-in, you can collect and merge data from multiple sheets—and from different workbooks—into one master sheet in just 3 steps:
1. Click the Merge Sheets button on the XLTools panel > Select the type of operation:
- Merge sheet data with the same name on one sheet
2. Mark the sheets that are tedious to merge. The data tree displays all sheets in all open workbooks.
3. Click the Merge button > Done! All data is copied to one master summary sheet.
How to combine data from multiple sheets in one pivot sheet
Let's say you have a series of sheets, and each sheet contains a sales report for a specific product category. The add-in will help you copy all these individual reports and combine the data into one summary sheet.
- Click the Merge Sheets button > Select Merge Data from Multiple Sheets into One Sheet.
- Click the Merge button > Done, all the data from the selected sheets are merged into one master sheet in the new workbook.
How to combine data from sheets with the same name on one pivot sheet
Let's say you have a number of books, and each one contains a regional sales report. Each report is divided into sheets with data on specific products - thus, the tabs in regional reports have the same name. The add-in will help you copy the data for all reports into one summary sheet.
- Click the "Merge Sheets" button > Select "Merge data from sheets with the same name into one sheet".
- Check the "Tables with headers" box if so.
Tip: so, the data will be combined under a single heading. This is convenient if the structure of the sheets you are merging is uniform, for example, if the reports are created according to the same template. If the headers do not match, each data range will be added with its own header. - Select the sheets to merge by checking the appropriate checkboxes in the data tree.
Tip: instead of merging sheets with the same name into groups one by one (one name group after another), you can select them all at once. The add-in will automatically collect data on the same tab names and display them on the corresponding separate sheets of the summary book. - Click the Merge button > Done, all the data of the selected sheets with the same name are collected in a new summary workbook.
How data is copied to the pivot sheet
Merging data essentially means extracting and copying data from multiple source sheets to a new sheet.
- The data is copied completely - the entire range up to the last used cell on the original sheet.
- The copied ranges are added sequentially, one range under the last line of the previous range.
- The XLTools Merge Sheets add-in preserves cell and table formatting, cell references, functions and formulas, merged cells, and more.
- The original data is not changed.
How to merge multiple sheets into one workbook
You can combine multiple sheets into one book using the XLTools Book Organizer add-on. It helps you copy and manage multiple sheets at the same time.
Got questions or suggestions? Leave a comment below.
