How to Append Multiple Worksheets

How to Append Multiple Worksheets
Coming up next: How to Find and Replace Data in Excel

You're on a roll. Keep up the good work!

Take Quiz Watch Next Lesson
 Replay
Your next lesson will play in 10 seconds
  • 0:01 Append and Consolidate
  • 0:38 The Consolidate Command
  • 3:05 After You Consolidate
  • 3:48 Consolidating Multiple…
  • 4:36 Lesson Summary
Add to Add to Add to

Want to watch this again later?

Log in or sign up to add this lesson to a Custom Course.

Log in or Sign up

Timeline
Autoplay
Autoplay
Speed

Recommended Lessons and Courses for You

Lesson Transcript
Instructor: Karen Sorensen

Karen has a Bachelors in Communications. She has 25 years of experience in Information Systems, Adult Learning and Virtual Training.

When you have multiple worksheets or workbooks with similar data, you may want to merge them together in order to create one master. This lesson will guide you through the steps to appending multiple worksheets together.

Append and Consolidate

When you have multiple worksheets that are similar in structure (rows, columns and titles), you may need to merge the information into one worksheet. Append means to add to; when you append multiple worksheets, you are adding one worksheet to another. This could mean you are adding a worksheet or multiple worksheets to an existing one, or combining all into one new worksheet. This lesson will introduce you to the Consolidate tool in Excel. I will share an example of using the tool and guide you through the steps to appending multiple worksheets together.

The Consolidate Command

Imagine you are the CEO of a sporting goods store. You have four locations: San Diego, Las Vegas, Seattle and Salt Lake City. You have just created a workbook with a worksheet for each store. Each worksheet details the revenue results for each department, such as Sports, Apparel, Outdoors and Exercise Equipment. You now want a Master worksheet - one with all the information for each of the four stores. And the last thing you want to do is copy and paste the information from all four worksheets into one. Too time consuming!

What if you could create a new worksheet, call it Master and move it to the first position in your workbook. Then, tell Excel to get the data from the other worksheets and bring it over! Oh, and would you also total things up while you're at it? You are not in the mood to be writing formulas. Now that's my idea of easy. So, let's do it. Make sure you have a new worksheet created and have your cursor in cell A1. There are several steps, but don't be overwhelmed. It's actually easier than it looks.

  1. Go to the Data menu in the ribbon and look in the Data Tools grouping of commands.
  2. Click on the Consolidate command (and a dialogue box will open).
  3. Leave the Function box at Sum (since we want Excel to total the numbers for us).
  4. With your cursor in the Reference box, open the first sheet (in our example, this is the San Diego Store).
  5. Then, select the range of data you want to add to your Master worksheet.
  6. Click on the Add button to add the range reference to the All References box.
  7. And then, you'll need to repeat steps number five and number six to add the range references for the other three stores.
  8. Check the boxes for Top Row and Left Column (this will add the column and row titles to our Master worksheet).
  9. And then, check the box for Link all worksheets together. Creating a link between all worksheets tells Excel that if you make any changes to the revenue numbers for the four stores, Excel should update the Master automatically.
  10. And then, click OK.

Whew! Okay, let's take a look at our new Master worksheet:

Master Worksheet
master worksheet

To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create an account
Support