How to group worksheets in excel

Have you ever found yourself in a situation when you need to perform the same tasks on multiple sheets? That’s very easy to do with the Group Worksheets feature. If your sheets have the same layout and structure, just group them together, and any changes you make on one sheet will be automatically applied to all other worksheets in the group.

Advantages of grouping worksheets in Excel

When you are working with a set of identically structured sheets, grouping them together can save you a lot of time. Once the worksheets are grouped, you can enter the same data, make the same changes, write the same formulas and apply the same formatting to all the worksheets at once without having to switch through different sheets and edit each one individually.

Here are just a few examples of what you can do to a group of worksheets:

  • Add new or edit the existing data on several worksheets at a time.
  • Perform the same calculations with the same regions and cells.
  • Print out a selection of worksheets.
  • Set up the header, footer, and page layout.
  • Correct the same typo or mistake on multiple sheets.
  • Move, copy, or delete a group of worksheets.

How to group worksheets in excel

In the screenshot below, we are setting up a table with the same data, formatting and layout for the 4 grouped worksheets: East, North, South and West.

How to group worksheets in Excel

To group sheets in Excel, hold down the Ctrl key and click the sheet tabs of interest one by one. After clicking the last tab, release Ctrl .

To group adjacent (consecutive) worksheets, click the first sheet tab, hold down the Shift key, and click the last sheet tab.

For example, here’s how you can group two worksheets:

Once the worksheets are grouped, you can edit them all in one go. Also, you can perform calculations that will automatically reflect on all the worksheets in the group.

As an example, suppose we want to calculate the amount of commission based on the commission percentage (column C) and sales (column D) on the following sheets: East, North, South and West.

Here’s the fastest way:

  1. Group the 4 sheets.
  2. Enter the below formula in cell E2, and copy it down through cell E5:
    =C2*D2

How to group worksheets in excel

Done! The formula will appear on all the grouped sheets in the same cells.

How to group all worksheets in Excel

To group all the worksheets in a workbook, this is what you need to do:

How to group worksheets in excel

  1. Right-click any sheet tab.
  2. Choose Select All Sheets in the context menu.

How do you tell if worksheets are grouped in Excel?

There are two visual signs of grouped worksheets in Excel:

How to group worksheets in excel

The sheet tabs in a group have a white background; the sheet tabs outside the group appear in gray.

The word Group is added to the name of the workbook; as soon as the worksheets are ungrouped, it disappears.

How to ungroup worksheets in Excel

After you’ve made the desired changes, you can ungroup the worksheets in this way:

How to group worksheets in excel

  1. Right-click any sheet tab in the group.
  2. Choose Ungroup Sheets in the context menu.

Or you can simply click any sheet tab outside the group to ungroup tabs.

That’s how to group and ungroup worksheets in Excel. I thank you for reading and hope to see you on our blog again next week!

Ben Stockton
How to group worksheets in excelBen Stockton
Writer

Ben Stockton is a freelance tech writer from the United Kingdom. In a past life, he was a UK college lecturer, training teens and adults. Since leaving the classroom, he’s been a tech writer, writing how-to articles and tutorials for MakeUseOf, MakeTechEasier, and Cloudwards.net. He has a degree in History and a postgraduate qualification in Computing. Read more.

How to group worksheets in excel

If you’re editing multiple worksheets in Microsoft Excel, it might be helpful to group them together. This allows you to make changes to the same range of cells across multiple worksheets. Here’s how to do it.

Grouping Multiple Worksheets in Microsoft Excel

Grouping worksheets together in Excel can be useful if you have an Excel workbook with multiple sheets that contain different data but follow the same layout.

The example below shows this in action. Our Excel workbook, named “School Data,” contains multiple worksheets related to the operation of a school. Three of the worksheets have lists of students for different classes, named “Class A,” “Class B,” and “Class C.”

How to group worksheets in excel

If we group these worksheets together, any actions we perform on any of these worksheets will be applied to all of them.

For instance, say we want to insert an IF formula in column G (cells G4 to G12) on each worksheet to determine whether any students were born in either 1998 or 1999. If we group the worksheets together before we insert the formula, we can apply it to the same range of cells on all three worksheets.

To group worksheets together, press and hold the Ctrl key and click each worksheet you want to group together at the bottom of the Excel window.

How to group worksheets in excel

Grouped worksheets appear with a white background, while unselected worksheets appear in gray.

The example below shows the IF formula we suggested above inserted in the “Class B” worksheet. Thanks to worksheet grouping, the same formula was inserted in cells G4 to G12 on the “Class A” and “Class C” worksheets, too.

How to group worksheets in excel

If we further modify any of these cells—like by adding a second set of formulas to column H—the change will be applied to all the grouped worksheets simultaneously.

Grouping All Worksheets in Microsoft Excel

When you press and hold Ctrl, you can select multiple individual worksheets and group them together. If you have a much larger workbook, though, this is impractical.

If you want to group all the worksheets in an Excel workbook, you can save time by right-clicking one of the worksheets listed at the bottom of the Excel window.

From here, click “Select All Sheets” to group all your worksheets together.

How to group worksheets in excel

Ungrouping Worksheets in Microsoft Excel

After you’ve finished making changes to multiple worksheets, you can ungroup them in two ways.

The quickest method is to right-click a selected worksheet at the bottom of the Excel window, and then click “Ungroup Sheets.”

How to group worksheets in excel

You can also ungroup individual sheets one at a time. Just press and hold Ctrl, and then select the sheets you want to remove from the group. Worksheet tabs you ungroup will return to a gray background.

  • › How to Create an Automatic Outline in Microsoft Excel
  • › Buying a Used GPU? Here’s What to Look Out For
  • › Stop Closing Apps on Your iPhone
  • › 10 Things to Do With Your New iPhone
  • › What Is Spam, and Why Do We Call It That?
  • › 11 Things to Check When Buying a Used iPhone
  • › Emulate Classic Consoles With Portable Retro Handhelds

How to group worksheets in excel Ben Stockton
Ben Stockton is a freelance tech writer from the United Kingdom. In a past life, he was a UK college lecturer, training teens and adults. Since leaving the classroom, he’s been a tech writer, writing how-to articles and tutorials for MakeUseOf, MakeTechEasier, and Cloudwards.net. He has a degree in History and a postgraduate qualification in Computing.
Read Full Bio »

A worksheet is a collection of cells where you keep and manipulate the data. Each Excel workbook can contain multiple worksheets.

Select a Worksheet

When you open an Excel workbook, Excel automatically selects Sheet1 for you. The name of the worksheet appears on its sheet tab at the bottom of the document window.

How to group worksheets in excel

Insert a Worksheet

You can insert as many worksheets as you want. To quickly insert a new worksheet, click the plus sign at the bottom of the document window.

Rename a Worksheet

To give a worksheet a more specific name, execute the following steps.

1. Right click on the sheet tab of Sheet1.

2. Choose Rename.

How to group worksheets in excel

3. For example, type Sales 2016.

Move a Worksheet

To move a worksheet, click on the sheet tab of the worksheet you want to move and drag it into the new position.

1. For example, click on the sheet tab of Sheet2 and drag it before Sales 2016.

Delete a Worksheet

To delete a worksheet, right click on a sheet tab and choose Delete.

1. For example, delete Sheet2.

How to group worksheets in excel

Copy a Worksheet

Imagine, you have got the sales for 2016 ready and want to create the exact same sheet for 2017, but with different data. You can recreate the worksheet, but this is time-consuming. It’s a lot easier to copy the entire worksheet and only change the numbers.

1. Right click on the sheet tab of Sales 2016.

2. Choose Move or Copy.

How to group worksheets in excel

The ‘Move or Copy’ dialog box appears.

3. Select (move to end) and check Create a copy.

How to group worksheets in excel

Note: you can even copy a worksheet to another Excel workbook by selecting the specific workbook from the drop-down list (see the dialog box shown earlier).

When I select and Group multiple rows, I cannot see the “-” (collapse) or the “+” (expand) signs on the left of the spreadsheet. This happens for both Rows and Column groups. This happens for only certain Tabs (Worksheets) within the Workbook. This is Excel 2010.

Is there a solution to this?

Answers

You may find this article helps explain how to hide/show the outline symbols. You can apply the setting to individual sheets or the workbook.

  • Marked as answer by Sally Tang Monday, November 15, 2010 9:03 AM

All replies

Are those rows or columns different than others?

Create a new file in Excel 2010, copy the rows and columns in the new file, and then group them again. Can you see the “+” or “-” signal?

If you have any feedback on our support, please contact [email protected]

No, the issue applies to ALL rows and columns BUT ONLY in certain tabs of my large workbook. In other tabs, the Group/Ungroup functionality works perfectly well.

I am unable to copy these tabs to a brand new workbook since I would have to lose all of my work (of several months) on my current workbook and all associated formulas linking different (20+) tabs.

Can you help, please?

Sorry to hear that you are unable to try those steps.

Is the worksheet or the cells protected? You might not group or ungroup if the worksheet or cell is protected.

Do you have multiple worksheets grouped together (look for [Group] in the title bar)?

Check this if it works: click the View tab, is the Outline Symbols checked?

Also, you cannot group shapes and other objects across multiple programs.

For more information about how to group/ungroup in Excel, you can refer to this article:

If you want to view multiple Excel worksheets at the same time, execute the following steps.

1. Open a workbook.

2. On the View tab, in the Window group, click New Window.

Excel opens a new window containing another view of the document.

3. On the View tab, in the Window group, click Arrange All.

4. Select the desired arrange setting. For example, click Horizontal.

How to group worksheets in excel

The titles (view-multiple-worksheets:1 and view-multiple-worksheets:2) indicate that two windows of the same file are open.

6. In the lower window, select the sheet tab of Wk2. You can now view the sales in week 1 and week 2 at the same time.

How to group worksheets in excel

Note: any changes you make to one window are immediately reflected in the other window.

7. On the View tab, in the Window group, click View Side by Side (by default, Synchronous Scrolling is activated), to scroll both worksheets at the same time.

Method 2. Right click on one of the sheet, and then click Select All Sheets

How to group worksheets in excel

One practical use of selecting multiple worksheets is to print selected worksheets.

Select multiple worksheets using Excel Worksheet.Select Method

In Excel VBA, it is not necessary to select worksheets in order to run a Macro on selected worksheets, because you can use VBA to loop through worksheets with specific name.

But if you really want to use VBA to physically select multiple worksheet, you can use Worksheet.Select Method

Syntax of Worksheet.Select Method

Name Required/Optional Data Type Description
Replace Optional Variant (used only with sheets). True to replace the current selection with the specified object. False to extend the current selection to include any previously selected objects and the specified object.

Example

To select Sheet1 and Sheet2, use the False Property in Sheet2

Select multiple worksheets using Array

To select Sheet1 and Sheet2, put the worksheet names in Array

Alternatively, create an Array first and then define the array items

Example

Assume there are 5 worksheets in a workbook, from Sheet1 to Sheet 5 in order, the below example copies Sheet and and Sheet5 before Sheet1

How to Name Worksheets When Exporting SSRS reports to Excel

1 4

Let’s say we have the following report that shows total sales by product category by territory:

When we export this report to Excel, we’d like each territory to appear in its own worksheet and each worksheet named after its territory:

How do we make this work? Easy! 1) Put every group on its own page, and 2) name each page using the same field the group uses.

Step 1: Put each group on its own page

To put each group on its own page, open the group’s property window.

Then, in the Page Breaks category, put a check mark in the Between each instance of a group check box.

Click OK to complete this step.

Step 2: Name the pages of the group

With the group selected in the Row Groups panel, press F4 to open the Properties window.

Next, expand the Group property and look for the Page Name sub-property. From its dropdown, select .

In the Expression dialog, select the Fields category and then double-click on the same field the group uses; in this case that would be the Territory field.

A reference to the field appears in the window at the top of the Expression dialog.

Click OK and that’s it! Now, when you export the report to Excel, the worksheet names will match the group names!

How to group worksheets in excel

How to match data in two Excel worksheets

  • Post author: Amos Gikunda
  • Post published: June 29, 2020
  • Post category: Worksheet

Reading values in one tab are easy when it comes to excel. But looking up values in two different tabs is not easy for some people. However, there is a way that you can match data using a formula to make your work easier. You will learn several ways on how to match data in two worksheets in this tutorial.

The first thing that you think of when you get two same workbook versions is to compare them. Then you will probably merge these two worksheets after comparing them for differences. Another great advantage of comparing data in two worksheets is that it helps you spot issues such as wrong formatting, inconsistent formulas, duplicate records and broken links.

1. Viewing them side by side

If you are not dealing with very large sheets, you can view them side by side. This method is quick and easy and you can compare two sheets in a workbook or two different workbooks visually.

For example, if you want to compare two sales report for different moths side by side and want to figure out the best selling products then you need to follow these steps.

Steps

1. Open the workbooks that need to be compared

2. Click on the View tab

3. Click on the view side by side button and that’s it

How to group worksheets in excel

The two windows will be displayed horizontally by default. However, if you can split them vertically by clicking then arrange all buttons then select vertical.

How to group worksheets in excel

How to group worksheets in excel

You can also arrange them in tiled or cascade windows when comparing more than two worksheets. Turn on the synchronous scrolling option on if you want to scroll down the two worksheets simultaneously. You can find them synchronous scrolling option right under the side by side option on the view tab.

2. Comparing two sheets in the same workbook

If the sheets you want to compare are in the same workbook then you need to perform these steps.

1. Open your excel file

2. Then click on the view tab, open window group and then click new window button

3. The same file will be opened in a different window

4. Click the corresponding button on the ribbon to enable side by side view.

5. Now choose the first sheet on window 1 and then window 2 choose the second sheet

3. Using third-party tools

Microsoft Excel comes with a handful of features and it also allows integration of third-party tools that make work easier. The built-in tools can match data in two worksheets but not as comprehensively as the third-party tools. So if you are targeting to spot all the differences in formatting, formulas, or values then you need to use the following tools.

1. Change pro for excel

5. synchronizer Excel Compare

Third-party tools are very efficient when it comes to comparing excel sheets. They are easy to use and specifically for updating, comparing, and merging excel sheets. The above three techniques are very effective when it comes to matching data in two Excel worksheets. Also, Softwares are very helpful especially when one is dealing with huge chunks of data.

If you work with a lot of worksheets in Excel, you would know the management of it can become an issue.

Once you have more than a couple of worksheets, you need to manually arrange these.

How easy would it be had there been a way to quickly sort the worksheets in Excel.

While there is no inbuilt feature way to do this, it can be done (easily) using VBA.

In this tutorial, I will give you the code and the exact steps you need to follow to sort worksheets in Excel.

You can tweak the code to sort the worksheets in an ascending or descending order.

VBA code to Sort Worksheets in Excel

Below is the code that will sort the worksheets in an alphabetical order as soon as you run it.

The above is a simple code that uses to For Next loops to analyze each worksheet against all the worksheets.

It compares the name of a worksheet against all the worksheets and moves it based on its name in the alphabetical order.

It then moves on to the next worksheet and then checks it against all the worksheets.

This process is repeated for all the worksheets and the final result is an order of worksheet sorted in an alphabetical order.

A few important things to know about this code:

    is used to make sure that the lowercase and uppercase are not treated differently.
  1. The value of Application.ScreenUpdating is set to False at the beginning of the code and changed to True at the end of the code. This ensures that while the code is running, you don’t see it happening on the screen. This also helps speed up the code execution.

If you want to sort worksheets in a descending order, you only need to change the < (less than) sign with the > (greater than) sign.

The below code would sort the worksheets in descending order:

You can also give the user the option to choose whether he/she wants to sort in ascending/descending order.

The below code would show a message box and the user can select the order to sort.

The above code when executed shows a message as shown below. It sorts based on the selection (Yes for Ascending and No for Descending).

In case you click Cancel, the code stops and nothing happens.

How to group worksheets in excel

Note: The sorting cannot be undone. In case you want to keep the original order as well, make a copy of the workbook.

Where to Put the VBA Code

Excel has a VBA backend called the VBA editor.

You need to copy and paste the VBA code into the VB Editor module code window.

Here are the steps to do this:

  1. Click the ‘Developer’ tab. (Can’t see the developer tab? Click here to learn how to get it).
  2. Click on Visual Basic option. This will open the VB editor in the backend.
  3. In the Project Explorer pane in the VB Editor, right-click on any object for the workbook in which you want to insert the code. (If you don’t see the Project Explorer go to the ‘View’ tab and click on ‘Project Explorer’.)
  4. Go to Insert and click on Module. This will insert a module object for your workbook.How to group worksheets in excel
  5. Copy and paste the code in the module window.How to group worksheets in excel

How to Run the VBA Code

In Excel, there are various ways to run the VBA code.

You can run the code right from the Visual Basic Editor (also called the VB Editor).

You can insert a button or a shape in the worksheet and assign the macro to it. When you click on the button, it will run the macro instantly.

You can also add the macro to the Quick Access Toolbar (QAT). Now whenever you have to sort the worksheet tabs, you can just click on the macro code icon in the QAT.

You May Also Like the Following Excel/VBA Tutorials:

How to group worksheets in excel

Group Column in excel means bringing one or more columns together in an excel worksheet, this gives us option to contract or expand the column and excel provides us a button to do so, to group columns we need to select two or more columns and then from data tab in the outline section we have the option to group the columns.

How to Use Column Grouping in Excel? (with Examples)

Example #1

Following are the steps of excel column grouping –

  1. Select the data that you are using to group the column in excel.

How to group worksheets in excel

How to group worksheets in excel

How to group worksheets in excel

This is the result when you want to hide the column C and D in a spreadsheet, and it automatically enables the grouping option in your spreadsheet.

Example #2

  • Step 1: Select column B and C
  • Step 2: Go to the data option in the excel toolbarExcel ToolbarThe toolbar, also known as the quick access toolbar, is located on the left top-most side of the excel window and has only a few options by default, such as save, redo, and undo. Users can, however, customize it to their liking and add any option or button to make it easier to access the commands.read more and select the group option in the outline toolbar, as shown in the below screenshot.
  • Step 3: Go to the option group and make the group of a column as selected.

Now you will be able to see the two minus signs; it means that there are two groups created in a particular spreadsheet that you want to group.

How to Hide or Unhide the Group Column?

  • Step 1: Click on the minus sign, which was created while grouping the column.
  • Step 2: When you clicked on the minus sign, a column will collapse, and it results in the hide in a column.
  • Step 3: Once you clicked on the minus sign, it automatically shows the plus sign, which means that if you want to unhide the column, just click on the plus sign to unhide the columns.
  • Step 4: You can likewise utilize the small numbers in the upper left corner. They let you cover-up and unhide all groupings of a similar dimension without a moment’s delay. For instance, in my table on the screen capture, clicking on 2 will conceal columns B and D. This is particularly helpful on the off chance that you made a progressive system of collection. Clicking on 3 will unhide or hide columns C and D.

Shortcut Keys to Hide or Unhide Column Grouping in Excel

  • Step 1: Select your data. Press Shortcut Excel KeysShortcut Excel KeysAn Excel shortcut is a technique of performing a manual task in a quicker way.read more – Shift + Alt + right arrow. You will see the dialogue box in your excel spreadsheet as follows-
  • Step 2: Select the radio button on a column to hide the columns in excelHide The Columns In ExcelThe methods to hide columns in excel are – hide columns using right-click option, hide columns using shortcut cut key, hide columns using column width, hide columns using VBA code.read more .
  • Step 3: Click on the Ok, and you will be able to hide and unhide the columns in excelUnhide The Columns In ExcelUsing the Home tab of the Excel ribbon, using the shortcut key, using the context menu, altering the column width, using the ctrl+G (go to) command, and using the ctrl+F (find) command are some of the ways to unhide a column in Excel.read more .

Why you should use the Excel Column Grouping

  1. To effectively expand and contract the section or areas of a worksheet.
  2. To limit schedules or side estimations that different users probably won’t require while working in excel worksheets.
  3. To keep data composed and in an organized structure.
  4. As a substitute for making new sheets (tabs).
  5. As a better option than hiding cells.
  6. It is the better function as compare to hiding the column function.
  7. It helps you to set up the grouping level.

Why you should not use the Excel Column Grouping

  1. You won’t be able to make a group of the cell which is not adjacent cells.
  2. If you are managing different worksheets and need to assemble similar lines/columns on numerous worksheets in the meantime, it is not possible to use this function.
  3. Always need to check that your data should be in a sorted form.
  4. You always need to check while grouping the column in excel that you are selecting the correct column which needs to group.

Things to Remember

  1. You won’t be able to add the Calculated Items to grouped Fields in your excel spreadsheet.
  2. It’s impractical to choose a few non-nearby columns.
  3. Clicking on the minus icon will hide the column, and the icon will change to the plus sign letting you instantly unhide the data.
  4. You can select the range and press Shift + Alt + left arrow to remove the grouping from your excel spreadsheet.

Recommended Articles

This has been a guide to Group Column in Excel. Here we discuss how to use Group Excel Column along with examples and downloadable excel templates. You may also look at these useful functions in excel –

For example you have four tables with same layout in four worksheets as below screenshot shown. And now you need to create a chart with extracting a data series from each worksheet, and create a chart with extracting data points from each worksheet, how could you solve them in Excel? This article recommends two workarounds for you:

  • Create chart with extracting many data series from multiple worksheets
  • Create chart with extracting many data points from multiple worksheets
  • How to group worksheets in excel

Create chart with extracting many data series from multiple worksheets

This section is talking about creating a column chart with extracting many data series from multiple worksheets in Excel. You can follow below steps to archive it.

1. Click Insert > Insert Column Chart (or Column)> Clustered Column. See screenshot:
How to group worksheets in excel

2. Now we insert a blank chart. Right click the blank chart, and select Select Data from the right-clicking menu. See screenshot:
How to group worksheets in excel

3. In the opening Select Data Source dialog box, click the Add button.
How to group worksheets in excel

4. And in the Edit Series dialog box, specify the series name and series values from a worksheet, and then click the OK button.
How to group worksheets in excel

5. When it returns to the Select Data Source dialog box, repeat step 3 and step 4 to add data series from other worksheets. Finally, all added data series from worksheets are listing in the Legend Entries (Series) box as below screenshot shown.
How to group worksheets in excel

6. In the Select Data Source dialog box, click the Edit button in the Horizontal (Category) Axis Labels section to open the Axis Labels dialog box, and then specify the axis labels as you need, and click the OK button. See above screenshot:

7. Click the OK button to close the Select Data Source dialog box.

8. It’s optional. Keep selecting the chart, click Design > Add Chart Element > Legend, and then select a legend option from the submenu. See screenshot:
In our case, we select the Legend > Bottom.
How to group worksheets in excel

So far we have created a clustered column chart with four data series from four worksheets. See screenshot:
How to group worksheets in excel

Create chart with extracting many data points from multiple worksheets

Sometimes you may want to create a chart whose data points are from different worksheets. This section will introduce Kutools for Excel’s Dynamically Refer to Worksheets to extract data points from multiple worksheets into a new worksheet, and then create a chart with these data points.

Kutools for Excel – Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Free Trial Now!

1.On the Sheet Tab bar click the New button or to create a new worksheet.

2. In the new worksheet, select the cell whose cell contents you will extract from other worksheets, and click Kutools > More (in the Formula group) > Dynamically Refer to Worksheets. See screenshot:
How to group worksheets in excel

3. In the opening Fill Worksheets References dialog box, please do as follows:
(1) Select Fill vertically cell after cell from the Fill order drop down list;
(2) In the Worksheet list section, check the worksheets where you will extract data points from.
(3) Click the Fill Range button and the Close button successively.
How to group worksheets in excel
Note: If you have several data series whose data points are from different worksheets, you can repeat this step as you need.

And then you will see data points are extracted from different worksheets. See screenshot:
How to group worksheets in excel

4. Select the extracted data points, and create a chart. In our case, we create a clustered column by clicking Insert > Insert Column Chart (or Column)> Clustered Column.

Till now we have created a clustered column chart whose data points are from different worksheets. See screenshot:
How to group worksheets in excel

Kutools for Excel – Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now

Normally, it might be not difficult to copy and paste data in Excel. But, do you know how to copy a range of data and paste into multiple worksheets quickly? And what if batch copying data from multiple worksheets? The following methods will help you solve them easily.

  • Copy and paste data into the same range of multiple worksheets
  • Copy same cell from multiple worksheets
  • Copy all data from multiple sheets to single sheet

Copy and paste data into the same range of multiple worksheets

We can copy a range of data and paste to multiple worksheets with the Fill >Across Worksheets feature in Excel. Please do as follows:

1. Select the range in current worksheet you will copy and paste into multiple worksheets.

2. Go to the Sheet Tab bar and select multiple worksheets (including current worksheet) you will paste the data into. See screenshot:
Note: Holding the Ctrl key, you can select multiple nonadjacent worksheets by clicking each worksheet one by one; holding the Shift key, you can select multiple adjacent worksheets by clicking the first worksheet and the last one.
How to group worksheets in excel

3. Click Home > Fill > Across Worksheets. See screenshot:
How to group worksheets in excel

4. In the opening Fill Across Worksheets dialog box, please check one option based on your need, and click the OK button.
In our case, we check the All option and click the OK button. See screenshot:
How to group worksheets in excel

And then the selected range of data is copied from the current worksheets and pasted into other selected worksheets.

Copy same cell/range from multiple worksheets

This method will introduce Kutools for Excel’s Dynamically Refer to Worksheets utility to copy the same cell from multiple worksheets in Excel.

Kutools for Excel – Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now

1. Create a new worksheet with clicking the New buttonorin the Sheet Tab bar.

2. Select the cell (says cell D7) in the new created worksheet whose cell content you will copy from other worksheet, and click Kutools > More (in the Formula group) > Dynamically Refer to Worksheet. See screenshot:
How to group worksheets in excel

3. In the opening Fill Worksheets References dialog box, please (1) select an option from the Fill Order drop down list; (2) check the worksheets that you will copy cell contents from in the Worksheet List section; and (3) click the Fill Range button and the Close button successively. See screenshot:
How to group worksheets in excel

And then the contents of same cell are copied from the specified worksheets into current worksheet. See screenshot:
How to group worksheets in excel

Kutools for Excel – Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now

Copy all data from multiple sheets to single sheet

This method is talking about a workaround to copy all data from multiple worksheets, and then paste into a new worksheet by Kutools for Excel’s Combine (Worksheets) utility.

Kutools for Excel – Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now

1. Click Kutools Plus > Combine to open the Combine Worksheets dialog box.

2. In the opening Combine Worksheets – Step 1 of 3 wizard, check the Combine multiple worksheets from workbook into one worksheet option, and click the Next button. See screenshot:
How to group worksheets in excel

3. In the Combine Worksheets – Step 2 Of 3 wizard, please only check current workbook in the Workbook list section; check the worksheets you will copy data from in the Worksheet list section, and click the Next button.
How to group worksheets in excel
Note: The same range button can help you quickly specify the same range in each worksheet as the range of worksheet you selected in the Worksheet list section.

4. In the Combine Worksheets – Step 3 of 3 wizard, please configure the settings as you need, and click the Finish button.
How to group worksheets in excel

5. And then a Combine Worksheets dialog box pops up and ask you for saving the combination scenarios. Please click the Yes button or No button as you need. In our case, we click the No button.
How to group worksheets in excel

So far we have copied the data from all selected worksheets, and pasted into a new workbook.

This Excel VBA tutorial explains how to use Worksheet.Select Method to select a single worksheet or multiple worksheets.

Select worksheets in Excel

When you click on a worksheet tab, the worksheet is highlighted.

To select multiple worksheets, you can hold down Ctrl and then left click the mouse on each worksheet tab.

To select all worksheets at once, right click on one of the sheet, and then click on Select All Sheets

How to group worksheets in excel

One practical use of selecting multiple worksheets is to print selected worksheets.

In this tutorial, I will explain how to perform the same tasks in the above scenarios using Excel VBA Worksheet.Select Method.

Excel VBA Worksheet.Select Method

In Excel VBA, it is not necessary to select worksheets in order to run a Macro on selected worksheets, because you can use VBA to loop through worksheets with specific name.

Syntax of Worksheet.Select Method

Name Required/Optional Data Type Description
Replace Optional Variant (used only with sheets). True to replace the current selection with the specified object. False to extend the current selection to include any previously selected objects and the specified object.

Example 1 – Select a single worksheet

To select Sheet1 only

Example 2 – Select multiple worksheets

To select Sheet1 and Sheet2, use the False Property in Sheet2

you can also add the False argument for the first Worksheet

How to group worksheets in excel

Example 3 – Select all worksheets in the workbook

The below example selects all worksheets in current workbook

After you have selected all worksheets, you can deselect them by selecting anyone of the worksheet. To avoid specifying which worksheet, I use ActiveSheet in the below example.

In multiple selection, ActiveSheet refers to the first selected worksheet.

With the macro below it is possible to compare excel sheets.
The result is displayed in a new workbook listing all cell differences.

This example macro shows how to use the macro above:

Comments

i want micro compare two workbooks with highlit same cell

Hi
I need help to write macro to compare two sheets in excel, sheet1 and sheet2 on same excel book and display all the differences between both the sheets on sheet3. My headings will always be the same between sheet1 and sheet2 but the information in both the sheets may vary. I included the headings that will be on both the sheets:

ID Number Date of Birth Payroll Number Surname First Name Salary Member Group

I am struggling with this. So please extend your helping hands Thank you.

i’ve a macro script to identify the matching column value in two open excel(Column A in first excel and column A in second excel) . I need matching column values to be copied to new excel(third excel) in column A. Please guide me.

Dim wb1 As Workbook
Dim wb2 As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim Cell As Range
Dim sBook As String

If Workbooks.Count < 2 Then
MsgBox “Error: Only one Workbook is open” & vbCr & _
“Open a 2nd Workbook and run this macro again.”
Exit Sub
End If

Set wb1 = ThisWorkbook
For Each wb2 In Workbooks
If wb2.Name wb1.Name Then Exit For
Next

On Error Resume Next
ReDo1:
Application.DisplayAlerts = False
sBook = Application.InputBox(Prompt:= _
“Compare this workbook (” & wb1.Name & _
“) to. “, _
Title:=”Compare to what workbook?”, _
Default:=wb2.Name, _
Type:=2)
If sBook = “False” Then Exit Sub
If Workbooks(sBook) Is Nothing Then
MsgBox “Workbook: ” & sBook & ” is not open.”
GoTo ReDo1
Else
Set wb2 = Workbooks(sBook)
End If

Application.ScreenUpdating = False
For Each ws1 In wb1.Sheets
If Not wb2.Sheets(ws1.Name) Is Nothing Then
Set ws2 = wb2.Sheets(ws1.Name)
For Each Cell In ws1.UsedRange
If Cell.Formula = ws2.Range(Cell.Address).Formula Then
Cell.Interior.ColorIndex = 35
ws2.Range(Cell.Address). _
Interior.ColorIndex = 35
End If
Next Cell
If ws1.UsedRange.Rows.Count = _
ws2.UsedRange.Rows.Count Or _
ws1.UsedRange.Columns.Count = _
ws2.UsedRange.Columns.Count Then
For Each Cell In ws2.UsedRange
If Cell.Formula = ws1.Range(Cell.Address).Formula Then
Cell.Interior.ColorIndex = 35
ws1.Range(Cell.Address). _
Interior.ColorIndex = 35
End If
Next Cell
End If
End If
Next ws1

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

I recieve an error:

Sub TestCompareWorksheets()
‘ compare two different worksheets in the active workbook
CompareWorksheets Worksheets(“Sheet1”), Worksheets(“Sheet2”)
‘ compare two different worksheets in two different workbooks
CompareWorksheets ActiveWorkbook.Worksheets(“Sheet1”), _
Workbooks(“WorkBookName.xls”).Worksheets(“Sheet2”)

——- CompareWorksheets ActiveWorkbook.Worksheets(“Sheet1”), _
Workbooks(“WorkBookName.xls”).Worksheets(“Sheet2”) -> for me it tells ‘subscript out of range (Error 9)’ but when i delete it – sub works fine. so is it neccesary codeline?

I want to write a comparison macro between sheet 1 and sheet 2 where result will be displayed in sheet 3 in terms of True/False. Can anyone help me on this?

This macro working and being help a lot. Thank you for sharing it. Is there any way to inculde the column name in results sheet? It will be more informative if results sheet diplay the records which has differences along with Coulmn name?

I can get this code to work:
CompareWorksheets Worksheets(“Sheet1”), Worksheets(“Sheet2”)

but when I try the alternate
‘ compare two different worksheets in two different workbooks
CompareWorksheets ActiveWorkbook.Worksheets(“Sheet1”), _
Workbooks(“WorkBookName.xls”).Worksheets(“Sheet2”)

I get an error. My troubleshooting included:
Saving the workbook as an .xlsm file, renaming the sheets to compare, having the WB open, having it closed, moving the reference file to the same folder location as the active file, moving it to a different location as the active file. Maybe a couple other things too, I can’t remember everything I tried.

The code I’m using is exactly what you have only I put in my file name and worksheet numbers:

CompareWorksheets ActiveWorkbook.Worksheets(“24G”), _ Workbooks(“S:\PD042\WORK\user name\P42-2 Bookplan-Tracking\P42-2 AMM Total Tracking Load Two.xlsm”).Worksheets(“24”)

Does anything stand out to you as incorrect? or should this work as expected?

“You just have to copy the two example procedures above and paste them into a normal module sheet in your workbook (you can’t use the sheet modules).

Open a workbook that contains two sheets you want to compare.
Edit the sheet names used in the macro “”TestCompareWorksheets”” (or, if you are lazy, rename the sheets in the workbook).

In Excel you press Alt+F8 to open the macro dialog box and run this macro: “”TestCompareWorksheets””.

A more detailed description of this procedure is available here:
http://www.erlandsendata.no/english/vba/howto.php “

very newbie at writting macros, so i can’t make this one work. do i need any special add-in?

“You just have to copy the two example procedures above and paste them into a normal module sheet in your workbook (you can’t use the sheet modules).

Open a workbook that contains two sheets you want to compare.
Edit the sheet names used in the macro “”TestCompareWorksheets”” (or, if you are lazy, rename the sheets in the workbook).

In Excel you press Alt+F8 to open the macro dialog box and run this macro: “”TestCompareWorksheets””.

A more detailed description of this procedure is available here:

I see error “Sub script out of range” while running
ub TestCompareWorksheets()
‘ compare two different worksheets in the active workbook
CompareWorksheets Worksheets(“Sheet1”), Worksheets(“Sheet2”)
End Sub

very newbie at writting macros, so i can’t make this one work. do i need any special add-in?

How to group worksheets in excel

While working with excel, we get a lot of new scenarios which have not imagined at all, to excel daily user, we should be in a position to encounter those scenarios. We cannot pinpoint at any one situation, but for sure, those unique situations will arise. One of the particular situations we have faced recently was getting the sum of multiple line items into one cell when we have all the line item values in one column. So in this article, we will show you how to group by sum values in excel.

How to group worksheets in excel

You are free to use this image on your website, templates etc, Please provide us with an attribution link How to Provide Attribution? Article Link to be Hyperlinked
For eg:
Source: Excel Group Sum (wallstreetmojo.com)

How to Sum Values by Group in Excel?

Below is how to use sum values by the group in excel with examples.

Examples #1 – Sum Group-Wise in Excel

When you receive the data, there are many line items, and when you summarize it, we may get a summary total in very lines, so this is the common task we all have done by using a pivot table Using A Pivot Table A Pivot Table is an Excel tool that allows you to extract data in a preferred format (dashboard/reports) from large data sets contained within a worksheet. It can summarize, sort, group, and reorganize data, as well as execute other complex calculations on it. read more tool. However, summing the values of all the items of a single group in a single cell is a different task without using a pivot table.

  • For example, look at the below data in excel.

For the above data, we need to create a city-wise sum of values in a single-cell by arranging all the cities in order.

  • For your reference, we need to get the sum of each city like the below one.

In the above image, we have an extra column as “City Total,” and all the cities are sorted one after the other, then we have used the SUM function to arrive at each city total. But the problem with the above method is we have used the SUM excel function SUM Excel Function The SUM function in excel adds the numerical values in a range of cells. Being categorized under the Math and Trigonometry function, it is entered by typing “=SUM” followed by the values to be summed. The values supplied to the function can be numbers, cell references or ranges. read more , and for each city, there are different line items, and for each city, we need to apply the SUM function individually, so it takes a lot of time when the data is large.

Example#2 – Combination Formula to Get Group-Wise Sum in Excel

Take the above data for this instance as well.

  1. Sort the data based on city names.

How to group worksheets in excel

How to group worksheets in excel

How to group worksheets in excel

How to group worksheets in excel

How to group worksheets in excel

How to group worksheets in excel

How to group worksheets in excel

How to group worksheets in excel

How to group worksheets in excel

How to group worksheets in excel

Now let me explain the result.

  • In cell C2, we have the city “Bangalore” total because the logical testLogical TestA logical test in Excel results in an analytical output, either true or false. The equals to operator, “=,” is the most commonly used logical test.read more of IF function says if cell A2 = A1, then returns an empty cell or else gives the overall total of “Bangalore” city. Since the A2 = A1 logic is not correct, it has returned the sum of the city “Bangalore.”
  • Now come to the second result in the cell D3. We have got the empty result because in this cell, A3 = A2 logical tests is TRUE, so the result is empty.
  • But when the formula reaches D5, the logical cell test is A5 = A4, so in this case, in cell A5, we have “Hyderabad,” and in cell A4, we have “Bangalore,” so both are different, and this time SUMIF returns the total of “Hyderabad.”
  • This combination of IF & SUMIF can be used to return a sum based on a group of values.

Things to Remember

  • You need to sort the data based on the group you are targeting. In this example, our target was to get the total based on the city, so we have sorted based on the city.
  • With the Pivot Table, we get the overall summary.

Recommended Articles

This has been a guide to Excel Group Sum. Here we learn how to sum values by excel group and using IF and SUMIF function along with examples and a downloadable excel template. You may learn more about excel from the following articles –

You can quickly compare two worksheets in the same workbook or in different workbooks by viewing them side by side. You can also arrange multiple worksheets to view them all at the same time.

On the View tab, in the Window group, click New Window.

On the View tab, in the Window group, click View Side by Side .

In each workbook window, click the sheet that you want to compare.

To scroll both worksheets at the same time, click Synchronous Scrolling in the Window group on the View tab.

Note: This option is available only when View Side by Side is turned on.

If you resize the workbook windows for optimal viewing, you can click Reset Window Position to return to the original settings.

To restore a workbook window to full size, click Maximize at the upper-right corner of the workbook window.

Open both of the workbooks that contain the worksheets that you want to compare.

On the View tab, in the Window group, click View Side by Side .

If you have more than two workbooks open, Excel displays the Compare Side by Side dialog box. In this dialog box, under Compare Side by Side with, click the workbook that contains the worksheet that you want to compare with your active worksheet, and then click OK.

In each workbook window, click the sheet that you want to compare.

To scroll both worksheets at the same time, click Synchronous Scrolling in the Window group on the View tab.

Note: This option is available only when View Side by Side is turned on.

Beginning with Excel 2013, workbooks that you open in Excel are no longer displayed as multiple workbook windows inside a single Excel window. Instead, they are shown as separate Excel windows. Because workbooks don’t share the same Excel window, they are displayed with their own ribbon, and you can view the open workbooks on different monitors.

If you resize the workbook windows for optimal viewing, you can click Reset Window Position to return to the original settings.

To restore a workbook window to full size, click Maximize at the upper-right corner of the workbook window.

Open one or more workbooks that contain the worksheets that you want to view at the same time.

Do one of the following:

If the worksheets that you want to view are in the same workbook, do the following:

Click a worksheet that you want to view.

On the View tab, in the Window group, click New Window.

Repeat steps 1 and 2 for each sheet that you want to view.

If the worksheets that you want to view are in different workbooks, continue with step 3.

On the View tab, in the Window group, click Arrange All.

Under Arrange, click the option that you want.

If the sheets that you want to view are all located in the active workbook, select the Windows of active workbook check box.

Tip: To restore a workbook window to full size, click Maximize at the upper-right corner of the workbook window.

On the View tab, in the Window group, click New Window.

In each workbook window, click the sheet that you want to compare.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers community.

Prior to upgrading her version of Excel, Paula was able to select more than one sheet and apply conditional formatting to a group of cells. But when she selects more than one worksheet in Excel now, the conditional formatting option fades. Paula is wondering how she can apply conditional formatting to more than one sheet at a time.

Good question, Paula. It seems that Microsoft did make this change as part of the ribbon-based user interface used in modern versions of Excel. (The ribbon-based interface was introduced with Excel 2007.) Why did they do this? I have no idea, but users are stuck with the results of the change—not being able to apply conditional formatting across multiple worksheets at the same time.

We were able to come up with only two workarounds. The first workaround involves copying the conditional formatting from one worksheet to another. Apply the conditional formatting to the first worksheet, then select all those cells to which you applied the formatting. Next, click the Format Painter (on the Home tab of the ribbon in the Clipboard group), switch to the target worksheet, and select the cells to which the formatting should be applied. That’s it.

The second workaround involves running the macro recorder while setting up the conditional formatting on your first worksheet. You can then re-run the macro as you display each of your other worksheets, in turn. The conditional formats are applied to the same cells on the other worksheets.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3433) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365.

Author Bio

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. Learn more about Allen.

MORE FROM ALLEN

Table Header Rows after a Manual Page Break

Insert a manual page break into the middle of a table, and you may find that subsequent pages of the table don’t always .

Attaching Macros to Documents

When you distribute documents to other people, you may want those documents to have associated macros that the reader can .

Recognizing a Header Row when Sorting

When you sort data in a worksheet, there are a couple ways you can do it. Using the simple way can result in .

Program Successfully in Excel! John Walkenbach’s name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, “Mr. Spreadsheet” shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Returning a Value Based on Text Color

Conditional formatting rules can be used to adjust the way in which information is displayed in Excel, such as the text .

Highlighting Cells Containing Specific Text

If you want to highlight cells that contain certain characters, you can use the conditional formatting features of Excel .

Diagonal Borders in a Conditional Format

Conditional formatting is a great tool for changing how your data looks based on the data itself. Excel won’t allow you .

Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click “Subscribe.”

I have two worksheets in two different Excel files. They both contain a list of names, id numbers, and associated data. One is a master list that includes general demographic fields, and the other is a list that only includes name and id, and an address. This list was pared down from the master list by another office.

I want to use the 2nd list to filter the first. Additionally, I want the results to include other fields from the master worksheet alongside the address fields from the second worksheet. I know how I could do this very easily with a database inner join, but I’m less clear on how to do this efficiently in Excel. How can join two worksheets in Excel? Bonus points for showing how to do outer joins as well, and I would greatly prefer knowing how to do this without needing a macro.

10 Answers 10

For 2007+ use Data > From Other Sources > From Microsoft Query :

  1. choose Excel File and select your 1st excel
  2. choose columns
    (if you don’t see any list of columns, make sure to check Options > System Tables )
  3. go to Data > Connections > [choose the connection just created] > Properties > Definition > Command text

You can now edit this Command text as SQL. Not sure what syntax is suported, but I tried implicit joins, “inner join”, “left join” and unions which all work. Here is a sample query:

How to group worksheets in excel

Support the accepted answer. I just want to emphasize on “choose columns (if you don’t see any list of columns, make sure to check Options > System Tables)”

Once you select the excel file, very likely you will see this data source contains no visible tables prompt, and the available tabs and columns are none. Microsoft admitted that is a bug that the tabs in the excel files are treated as “System Tables”, and the option for “System Tables” is not selected by default. So don’t panic at this step, you just need to click “option” and check “System Tables”, then you see the columns available.

VLOOKUP and HLOOKUP could be used to search for matching primary keys (stored vertically or horizontally) and return values from ‘attribute’ columns/rows.

You can use Microsoft Power Query, available for newer versions of Excel (similar to the accepted answer, but much simpler and easier). Power Query calls joins ‘merges’.

The easiest way is to have your 2 Excel sheets as Excel tables. Then in Excel, go to the Power Query ribbon tab, and click the ‘From Excel’ button. Once you have imported both tables into Power Query, select one and click ‘Merge’.

While I think Aprillion’s answer using Microsoft Query is excellent, it inspired me to use Microsoft Access to join the datasheets which I found much easier.

You need to have MS Access installed of course.

  • Create a new Access database (or use a scratch DB).
  • Use Get External Data to import your Excel data as new tables.
  • Use Relationships to show how your tables are joined.
  • Set the Relationship type to match what you want (representing left join etc.)
  • Create a new query which joins your tables.
  • Use External Data->Export to Excel to generate your results.

I really couldn’t have done that without Aprillion’s great answer.

You can’t preform SQL style joins on Excel tables from within Excel. That said, there are multiple ways to accomplish what you are trying to do.

In Excel, like Reuben says, the formulas that will probably work the best are VLOOKUP and HLOOKUP . In both cases, you match on a unique row and it returns the value of the given column\row to the left\down from the found id.

If you only want to add a couple extra fields to the second list, then add the formulas to the second list. If you want an “outer join” style table, then add the VLOOKUP formula to the first list with ISNA to test if the lookup was found. If Excel’s Help doesn’t give you enough details on how to use these in your particular instance, let us know.

If you prefer to use SQL then link the data into your database program, create your query, and export the results back to Excel. (In Access you can import Excel Worksheets or Named Ranges in as a Linked Table.)