To summarize and report results from separate worksheets, you can consolidate data from each sheet into a master worksheet. The sheets can be in the same workbook as the master worksheet, or in other workbooks. When you consolidate data, you assemble data so that you can more easily update and aggregate as necessary.

For example, if you have an expense worksheet for each of your regional offices, you might use consolidation to roll these figures into a master corporate expense worksheet. This master worksheet might also contain sales totals and averages, current inventory levels, and highest selling products for the entire enterprise.

Tip: If you frequently consolidate data, it might help to create new worksheets from a worksheet template that uses a consistent layout. To learn more about Templates, see: Create a template. This is also an ideal time to set up your template with Excel tables.

Ways to consolidate data

There are two ways to consolidate data, either by position or category.

Consolidation by position: The data in the source areas has the same order, and uses the same labels. Use this method to consolidate data from a series of worksheets, such as departmental budget worksheets that have been created from the same template.

Consolidation by category: When the data in the source areas is not arranged in the same order but uses the same labels. Use this method to consolidate data from a series of worksheets that have different layouts but have the same data labels.

Consolidating data by category is similar to creating a PivotTable. With a PivotTable, however, you can easily reorganize the categories. Consider creating a PivotTable if you need more flexible consolidation by category.

Note: The examples in this article were created with Excel 2016. Though your view may differ if you"re using another version of Excel, the steps are the same.

How to consolidate

Follow these steps to consolidate several worksheets into a master worksheet:

Use a formula to consolidate data

If the data to consolidate is in different cells on different worksheets:

Enter a formula with cell references to the other worksheets, one for each separate worksheet. For example, to consolidate data from worksheets named Sales (in cell B4), HR (in cell F5), and Marketing (in cell B9), in cell A2 of the master worksheet, you would enter the following:

Tip: To enter a cell reference-such as Sales!B4-in a formula without typing, type the formula up to the point where you need the reference, then click the worksheet tab, and then click the cell. Excel will complete the sheet name and cell address for you. NOTE: formulas in such cases can be error-prone, since it’s very easy to accidentally select the wrong cell. It can also be difficult to spot a mistake after entering a complex formula.

If the data to consolidate is in the same cells on different worksheets:

Enter a formula with a 3-D reference that uses a reference to a range of worksheet names. For example, to consolidate data in cells A2 from Sales through Marketing inclusive, in cell E5 of the master worksheet you would enter the following:

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on

Let's say you have several sales reports for different regions. If you want to perform calculations and graphs on the entire amount of data, you need to collect all the data on one summary sheet. Switching between multiple workbooks, endlessly copying hundreds of lines, or writing VBA can be time consuming.

With the Merge Sheets add-on, you can collect data onto one summary sheet in a matter of seconds:

• Collect sheet data from different workbooks on one sheet
• Collect data from sheets with the same name and combine by tab name
• Merge data from sheets with identical structure under one heading
• Preserve formatting in result summary 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 sheet 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 data from sheets with the same name into one sheet

2. Mark the sheets that need to be combined. The data tree displays all the sheets in all open workbooks.

3. Click the Merge button > Done! All data is copied onto one main summary sheet.

How to combine data from multiple sheets into one pivot sheet

Let's say you have a series of sheets, and each of them contains a sales report for a specific product category. The add-in will help you copy all of these individual reports and combine the data into one summary sheet.

1. Click the Merge Sheets button > Select Merge data from multiple sheets into one sheet.
2. 
   
3. Click the Merge button > Done, all the data from the selected sheets is collected on one master sheet in a new workbook.

How to combine data from sheets with the same name into one summary sheet

Let's say you have a number of books, and each of them contains a report on regional sales. Each report is divided into sheets with data on specific products - thus, the tabs in regional reports have the same names. The add-in will help you copy data from all reports into one summary sheet.

1. Click the Merge Sheets button > Select Merge data from sheets with the same name into one sheet.
2. Check the "Tables with headers" checkbox if this is the case.
   Tip: this way, the data will be combined under a single heading. This is convenient if the structure of the sheets that you combine is uniform, for example, if the reports were created using the same template. If the headers do not match, each data range will be added with its own header.
3. Select the sheets to merge by selecting the appropriate checkboxes in the data tree.
   Tip: Instead of sequentially grouping sheets with the same name into groups (one name group after another), you can select them all at once. The add-in will automatically collect data based on the same tab names and place them on the corresponding separate sheets of the summary book.
4. Click the Merge button > Done, all the data of the selected sheets with the same name are collected in a new summary workbook.

How is data copied to the summary 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 source sheet.
• The copied ranges are added sequentially, one range below the last line of the previous range.
• The XLTools Merge Sheets add-on preserves cell and table formatting, cell references, functions and formulas, merged cells, etc.
• The original data is not changed.

How to combine several 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.

Have questions or suggestions? Leave a comment below.

A printed publication called a brochure is known to each of us. When you think of a brochure, the first thing that comes to mind is a small book that contains advertising or promotional information. curriculum. The description of the brochure found on the Internet also characterizes it as a printed, not periodical book publication, derived from the French word brocher - to stitch.

The text content of the brochure has departed slightly from the original; today the material filling out the brochure is of a reference or advertising nature. The capacity of the brochure and the ability not only to tell about the product, but also to show it, thanks to colorful illustrations, made the brochure one of the most popular advertising media. A reputable company must have in its arsenal not only business cards, booklets, leaflets or catalogs, but also a brochure that has the ability to minimum costs, convey detailed information to a large number of potential clients. A brochure, unlike a booklet or leaflet, inspires confidence; it is pleasant to hold in your hands and flip through the pages. A well-made design, selected and thoughtful text, pleasant paper quality, bright illustrations are able to hold the reader’s attention, but we must not forget about such an important factor on the path to perfection as the way our brochure is bound together. The thickness of the brochure depends on the number of pages, therefore, the fastening must be carefully selected. The main task of a brochure, besides the informational task of course, is not to end up in the waste paper basket, but to last as long as possible.

1 Among the many methods of fastening pages that are popular with customers, the staple method is especially popular. The method is reliable, time-tested, and economical. The option of stapling with a paper clip has been well known to us since childhood; this is how ordinary notebooks were, and continue to be, stapled together. The so-called saddle stitch option, when the ends of a staple or paper clip pierce the middle of the spine fold in two or more places and are secured, tightly fastening the sheets of the product. Another option for stitching with a paper clip is called wheelbarrow stitching, here the staple pierces the fold of the spine with front side, and the ends of the brackets are clamped, not inside the brochure, but on its reverse side. Both options for staple binding have their advantages and disadvantages. If the customer chooses the “stitch” fastening method, then the product fastened in this way will not open enough. Which of course will affect its overall appearance and the service life of the brochure. But if the ordered brochure does not have a large number of sheets not exceeding 60 pages, then the method of binding using a paper clip fits better, like no other. You can also stitch a block of a product using threads; this stitching method is used if the product is covered and contains a large number of sheets. The sheets are joined and stitched with thread, and then the cover is attached to the product using hot glue.

2 A brochure with a large number of pages and a soft cover can be bound using the KBS method, which means adhesive seamless fastening. A brochure bound this way looks great. A small disadvantage of this type of binding is the same impossibility of placing the brochure open, and since the brochure also contains illustrations in addition to text, difficulties with full viewing cannot be avoided. The service life of a brochure made with the help of KBS is several years, and all this time the pages of our brochure can be opened constantly, without fear of damaging it appearance any damage. Despite the fact that with the help of KBS, the brochure has a rather solid appearance, it is also necessary to take into account the fact that not all materials are suitable for this method of binding. Some types of thick paper, such as coated paper, are difficult to glue, in which case it is necessary to choose another, more suitable method.

3 Securing a brochure with a spring is rightfully considered the most effective and elegant. The spring-loaded brochure is attractive and inspires confidence. Typically, the spring that holds the sheets of a brochure together is metal or plastic. The cover of such a brochure can be either soft or hard, made using various design materials. Using embossed cardboard will easily make your brochure stand out from others. This brochure, fastened with a spring, has the most complete spread, requires replacement pages, and will serve the benefit of your campaign longer than others. In addition, spring-loaded brochures are distinguished by their ability to have both a minimum and a maximum number of pages.

4 A close relative of the spring brochure binding, this is a variant of the bolt/ring brochure. The color range in case of choosing such binding is more extensive, and fan binding will give the brochure an original look.

In any case, which option of binding method for the brochure you would not prefer, remember that the last of the printing manufacturing processes, of course, gives the brochure a finished look, but primarily serves for convenient and long-term use, while delighting customers with its appearance.

Assistance from MoscowBrand printing house specialists in the development and production of brochures, detailed information, competent and comprehensive consultation, will help you with your choice, ensuring excellent quality and low cost.

If the data you want to analyze spans multiple sheets or workbooks, you can combine them into 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 from this data. Such a sheet could contain totals and averages for sales, current inventory levels, and the most popular products throughout the organization.

The consolidation type should be chosen based on the appearance of the sheets being merged. If the data on the sheets is arranged uniformly (row and column names may differ), use consolidation by location. If the sheets for the corresponding categories use the same row and column names (the data may be located differently), use consolidation by category.

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 sheets must be in list format with no empty rows or columns.

Consolidation by category

To consolidate by category, the data range on each source sheet must be in list format with no empty 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, consolidation will not sum these columns.

On one of the pages of this site a short article was published about how to save active Excel workbook sheets into separate files. It's time to consider the reverse question of how to collect information from different files in general and how to combine multiple files into one workbook 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. Exist different methods allowing you not to transfer a spreadsheet sequentially along a chain from one user to another, but to work in it simultaneously or in parallel. One such method is document sharing, where users can enter simultaneous work with the information in this document.

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 from different workbooks into one

Combining different files into one or certain sheets into one workbook has various solutions. This and standard option with copying and pasting sheets into the desired workbook, and using macros, and installing additional programs that specialize in merging files, such as MergeExcel. Each of these methods has its pros and cons. For my part, I would like to offer another way to solve the problem of combining sheets from different books into one. Without having to deal with macro code or installing additional programs, you can quickly expand Excel with new capabilities using an add-on called an add-in.

The add-on for merging different files into one is based on a VBA macro, but differs favorably from it in its ease of use. The add-on 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. Then everything is intuitive, files are selected, sheets of these files are selected, additional merging parameters are selected and the “Start” button is pressed.

macro (add-in) for combining multiple Excel files into one workbook

The add-on allows you to:

1. With one click, 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. combine all sheets of selected files into one workbook;

4. merge only non-empty sheets of selected files into a workbook;

5. collect sheets with a given name into the final book (you can use a mask using special matching characters);

6. collect sheets of selected files with a specific number (index) or range of numbers into one book;

7. collect sheets with a certain value in a given range of cells;

8. set additional parameters for merging, such as:

a) assigning names to the sheets of the merged files;

b) deleting from the book in which the data is being merged its own sheets that were originally in this book;

c) replacing 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

It is also possible to automatically go through all the workbooks specified in the dialog box, select only the necessary sheets in them, copy user-defined value ranges and paste them on a separate worksheet of the active workbook. In this case, you can choose the method of placing data on the sheet with totals; ranges of values ​​can be located one after the other, both horizontally and vertically. This way you can