[techtalk] EXCEL/G-Sheets – Merge Multiple Spreadsheets into One File

Tue Sep 1 16:06:27 PDT 2020

Greetings and welcome to this week’s issue of Tech-Talk!
Combine several spreadsheets into one
[http://r20.rs6.net/on.jsp?ca=8efe985c-6b5d-426e-9aad-7b1306da192e&a=1100778316270&c=980e0ed0-0713-11e6-a7b7-d4ae528ed502&ch=981cb4d0-0713-11e6-a7b7-d4ae528ed502]

[http://files.constantcontact.com/ee1208b4001/34916cb2-412f-43fa-8294-1f7ecb0e9a3d.png]

Announcements

WEBINARS

·     September 2: Open Q&A ... WORD / Google Docs [ALL LEVELS]

·     September 9: Are You Using the Full Power of Gmail? [INTER/ADV]

·     September 24: Finding Metrics That Matter, Social Media Analytics for Libraries [NOVICE / INTER]

Register Here<http://r20.rs6.net/tn.jsp?f=0016j1mL6oftGZOvD4Vrtzlh_Y5kZsxNFZc4v1HibaAK9rzpzR5zxYPdILfKdR8bsspu65GDYcs4l0BfXkDNzIMQc_mlpWeDWDdBauoJ08W4evv49d8OFwya7xSSD8NapaL2b2plRxy6dC4XxqJrnLTcCWzWyWColzg&c=acY_6jqKtzmlMM6b7pl0ne4E6aBPNBA3mN4iWN0IMmafvUxSEC9AGQ==&ch=tdV9BXDtrYjcVF6fxoQlNeqhk8_eh2F4K5316rOKcSxc_rFeJIdPww==>

Watch the Recordings Here<http://r20.rs6.net/tn.jsp?f=0016j1mL6oftGZOvD4Vrtzlh_Y5kZsxNFZc4v1HibaAK9rzpzR5zxYPdInFOf0VWFb6_KvDKNk_dHgleLSzJ6-FTpKyv53QGgmdvMG-FSQtpXsQz2wndp2Z2sOCRrH84uKAq1uFzkdsCD8ALA6JnpM_qiSrTRrEfMW6xzS0lOY0Bos=&c=acY_6jqKtzmlMM6b7pl0ne4E6aBPNBA3mN4iWN0IMmafvUxSEC9AGQ==&ch=tdV9BXDtrYjcVF6fxoQlNeqhk8_eh2F4K5316rOKcSxc_rFeJIdPww==>

[https://files.constantcontact.com/ee1208b4001/d76eccca-b6cd-42a7-b50d-5a729d6b9078.jpg]

A TECH-TALK DESIGN CONTEST

You can win merchandise with your great ideas.

We're adding the Tech-Talk logo to popular items like mugs, t-shirts and other fun stuff and we're looking for creative text and designs from you.

Learn the contest rules and how you can submit ideas.<http://r20.rs6.net/tn.jsp?f=0016j1mL6oftGZOvD4Vrtzlh_Y5kZsxNFZc4v1HibaAK9rzpzR5zxYPdHIighH7ebJGwDJ0rdIcZwv9Vky8X-wQNwhVgMwScbrlPqAtH6KzOLAlKCkcr_NcRMVIV_wjCwesnQR9LP7wzKo2gNO_MpTJcJ0waDkz3nbRxSbzBZz9n8k=&c=acY_6jqKtzmlMM6b7pl0ne4E6aBPNBA3mN4iWN0IMmafvUxSEC9AGQ==&ch=tdV9BXDtrYjcVF6fxoQlNeqhk8_eh2F4K5316rOKcSxc_rFeJIdPww==>

If we use what you create, we'll send you the merchandise of your choice! There's no limit to the number of winners or to the number of entries (or wins) by one person.

 This Week's Topic: EXCEL / Google Sheets

1. ARTICLE & VIDEO ... Merge Multiple Spreadsheets into One File
2. COMMUNICATING ... Soften your voice with a one word question
3. LEADERSHIP ... In which mode are you most of the time?

[video]<http://r20.rs6.net/tn.jsp?f=0016j1mL6oftGZOvD4Vrtzlh_Y5kZsxNFZc4v1HibaAK9rzpzR5zxYPdOszpsT4a8faUnOhB8rG1CZju4Ug7-oithu60T-beAx6VLk79kO85itP2binnHe7SHULSDP76nT6kvGXdL9bbtC-5rS72nJK4C9v8l2iYixihXVeY2szvPKP3cxNJ_N_DfMWC18gj8vjI92HtgQO5-2flGPEmwvYzg==&c=acY_6jqKtzmlMM6b7pl0ne4E6aBPNBA3mN4iWN0IMmafvUxSEC9AGQ==&ch=tdV9BXDtrYjcVF6fxoQlNeqhk8_eh2F4K5316rOKcSxc_rFeJIdPww==>

Photo by Lewis Fagg<http://r20.rs6.net/tn.jsp?f=0016j1mL6oftGZOvD4Vrtzlh_Y5kZsxNFZc4v1HibaAK9rzpzR5zxYPdOszpsT4a8fayeIUGzKlMdpUAkYuWaoLDiOifir9poKhnHVWHKR8_28HU-WfYQarQ5MKTfWnrupZx9H-L7ZCTZTzxiyNhDogeblyeQtr1QsArxopPyAPbVXjGj7SK79tuZmfSJPPhaMqxnlinKvStZWZ1wHSbij4rIveVXSHve33yNWxJfO9QRwcG2n21o7l0qym8Wz9mUzlQRsf5or0_Mw=&c=acY_6jqKtzmlMM6b7pl0ne4E6aBPNBA3mN4iWN0IMmafvUxSEC9AGQ==&ch=tdV9BXDtrYjcVF6fxoQlNeqhk8_eh2F4K5316rOKcSxc_rFeJIdPww==> on Unsplash<http://r20.rs6.net/tn.jsp?f=0016j1mL6oftGZOvD4Vrtzlh_Y5kZsxNFZc4v1HibaAK9rzpzR5zxYPdOszpsT4a8fa5RJo0qopFSiGjozUiUF6jAo9tS0GbICpo9x9GIqqc96lmFMXjSWK41pb0ZtUVnQmbzg7bBi7AUM1lgBoFqJyJ2E9sMAk2YLp6IN54aETA5byc12xK3l3eGE4Kuw5zkMJzuzCIUOsWwDff0HEGu8Nle_POYYJSZtZ-0ELY4U-OiLha65_2IGLklQvhxqPmrg6FoApxVJrL-lFNZd5c4eNmQ==&c=acY_6jqKtzmlMM6b7pl0ne4E6aBPNBA3mN4iWN0IMmafvUxSEC9AGQ==&ch=tdV9BXDtrYjcVF6fxoQlNeqhk8_eh2F4K5316rOKcSxc_rFeJIdPww==>

Brought To You By State Library of Oregon

[State of Oregon]

Visit Tech-Talk.com Database<http://r20.rs6.net/tn.jsp?f=0016j1mL6oftGZOvD4Vrtzlh_Y5kZsxNFZc4v1HibaAK9rzpzR5zxYPdMGbWeIw5NnT-JjJPVb-MQPCSkVMR5BaMVpbUnf_5OSl3yyml63ft9vAzr7cb-vaqQTGpcgFXjcQNYwYnbN2NrHrrlbY5fSEwBjjh4yd-z1q&c=acY_6jqKtzmlMM6b7pl0ne4E6aBPNBA3mN4iWN0IMmafvUxSEC9AGQ==&ch=tdV9BXDtrYjcVF6fxoQlNeqhk8_eh2F4K5316rOKcSxc_rFeJIdPww==>

Username: ORLIBTECH
Password: ORLIBTECH

Questions about Tech-Talk?
darci.hanning at state.or.us<mailto:darci.hanning at state.or.us>

EXCEL / Google Sheets - Merge Multiple Spreadsheets into One File
Intermediate

[Merge multiple spreadsheets]

Do you deal with lots of spreadsheets? Are many of them in one workbook ... or would you like them to be?

I frequently work on a tracking report in Excel, compiling Tech-Talk webinar registrations. I have many individual files – one for every topic. Not only is it clumsy jumping from one file to the next, it's far easier to analyze when I have them all together in one sheet.

It becomes especially powerful to use this combined sheet with pivot tables. (See Tech-Talk article<http://r20.rs6.net/tn.jsp?f=0016j1mL6oftGZOvD4Vrtzlh_Y5kZsxNFZc4v1HibaAK9rzpzR5zxYPdI419_wq_N9hgKsVaX8saOuZyFRyhojhQshX5QFhog17pt2fPXV-d3QNxWeTOCypXmvRYk1RQjGjitIAdV3Vnmc2FKP9yrH2odXf5yj7UY4VPjJ3xjvUruxoQYxK48mEKAS3LmLPi1XsVVZHz91MiZGs3NFP-nftuA==&c=acY_6jqKtzmlMM6b7pl0ne4E6aBPNBA3mN4iWN0IMmafvUxSEC9AGQ==&ch=tdV9BXDtrYjcVF6fxoQlNeqhk8_eh2F4K5316rOKcSxc_rFeJIdPww==>, webinar<http://r20.rs6.net/tn.jsp?f=0016j1mL6oftGZOvD4Vrtzlh_Y5kZsxNFZc4v1HibaAK9rzpzR5zxYPdOszpsT4a8faYfBQGmWoZlmbxc2ibT3th4wMQ-fpTzlcM5x0L5aoDOotqtt741H9Jp7tyBt-nuaacg88oFBPvGbzI_I61EejZ0v-YXDzr45RDEFXvXRrGwNOmP6XqrTSGGSMISeGkUfc&c=acY_6jqKtzmlMM6b7pl0ne4E6aBPNBA3mN4iWN0IMmafvUxSEC9AGQ==&ch=tdV9BXDtrYjcVF6fxoQlNeqhk8_eh2F4K5316rOKcSxc_rFeJIdPww==> or treasure hunt<http://r20.rs6.net/tn.jsp?f=0016j1mL6oftGZOvD4Vrtzlh_Y5kZsxNFZc4v1HibaAK9rzpzR5zxYPdNhjLhppB666lfFdM6oRFiYv30y3ZHiVhKLtj0LEIPPSS3cD8HuALyuhhhJsVeaODCo29UWNvXKlv4Zz01AwABfZpnlYojtqLIDC9Rr81qBS_w_0xBsDHjG1N_Guw6YxvnFVC4TDwWWiz-lYCBb_5e8=&c=acY_6jqKtzmlMM6b7pl0ne4E6aBPNBA3mN4iWN0IMmafvUxSEC9AGQ==&ch=tdV9BXDtrYjcVF6fxoQlNeqhk8_eh2F4K5316rOKcSxc_rFeJIdPww==> about pivot table.)

So I created a master spreadsheet for all 12 topics. You'd think that would be a lot of copying and pasting, right?

There's an easier way! Let us show you how in both Excel and Google.

NOTE: Just to be sure we are all on the same page, a workbook is the whole Excel file. Worksheets are the individual sheets /tabs / pages within a workbook.

Combine Excel Files

When you have multiple Excel workbooks that you want to combine into one, using the Move/Copy feature will help you get your files organized quickly.

TIP: It is helpful to save all of the Excel workbooks in one folder before you begin.

·     First, open the spreadsheet that you want to create by combining all files into one -- your "master" file. You may want to start with a new one so that you keep all of your original data files intact.

·     TIP: So that in your combined workbook you can find the information you need quickly, before you merge the spreadsheets into one, be sure each tab at the bottom has a distinctive name to it. It could be the category, date, etc… so that when you open the large file, you will know quickly which tab you want to view.

[new master spreadsheet]

[Move or Copy]

·     Next, open all individual workbooks that you want to combine into one singular file.

·     Go to the first workbook you want to add to your master document and click on the tab at the bottom. (You can add multiple tabs/spreadsheets at once by clicking the Shift key and then clicking each tab. They will turn white when selected.)

·     Right-click on the tab and select the Move or Copy choice.

·     In the box that opens, use the drop-down window for the To book to select the name of the Master file (as in the image below).

·     In the Before sheet window, select the PLACEMENT of the file tab that you want to add to the master file. The names of the sheets listed are the ones that already exist in your master file. This allows you to decide where in the spreadsheet you want the content to display (e.g. first, second, third, etc…) based on the sheets that are already included.

·     You can choose to Create a copy by checking the box at the bottom. This is a great option to keep your original files intact.

[move or copy]

·     Click OK and your worksheet will be added to the new file.

·     Once you are finished with the first worksheet, close it and go to the next one. Keep moving through each file until all spreadsheets are added to the master workbook.

Merging Content with Google Sheets

There are several different ways to consolidate multiple Google Sheets into one file. We'll go through the steps for one of the easiest.

As with Excel, make sure you have already created a master spreadsheet ahead of time ... where the copied content will be included.

[copy to existing spreadsheet]

·     Open the first Google Sheet file that contains the content (sheet) that you want to copy into another file.

·     On the tab at the bottom of the sheet, right-click and choose Copy to, Existing spreadsheet.

·     A window will open prompting you to select the worksheet where you want to copy the content to. You may need to browse your files to find it. Click on the name of the sheet and click the Select button at the bottom.

[select sheet]

·     You should see a "Sheet copied successfully" window. Click OK.

·     In your master worksheet you'll now see the copied sheet appear in a new tab at the bottom.

[https://files.constantcontact.com/ee1208b4001/e2533c81-68ed-4d87-bb78-6365c9a96462.png]

·     Repeat these steps for each sheet you'd like to move over to your master one.

[https://files.constantcontact.com/ee1208b4001/41d9a61c-7e5a-4de3-b0a4-4021dd05cea3.png]

Communications: Writing
Soften your voice with a one word question

Studies show us that more than 80% of the time, we communicate by giving information to others. We tell them stuff.

For example, other things we could be doing are seeking information, testing our understanding or clarifying to name a few.

But instead, we tell, tell, tell. Just as I'm doing right now, right?

Did you notice what I just did? I told you something and then asked for your agreement. I involved you a bit in the thought process. I made you stop and think whether or not you agreed with me, by adding the word "right" and a "?".

You probably read right over it, not giving it a thought. But the intent was there to soften my telling so that maybe it would seem a bit as if we were in conversation. That your thoughts mattered. (Which they do, of course.)

Here are some sentences I've used recently ... without the softening. It's information I wanted to convey.

·     We're closer to the future than we think.
·     Read, enjoy, watch ... we'll talk when you're ready.
·      I'm sure you experience some of this rebellion in your own library, school or institution.

Now read the same sentences with one word and a question mark at the end. How do they make you feel?

·     We're closer to the future than we think, right?
·     Read, enjoy, watch ... we'll talk when you're ready, OK?
·      I'm sure you experience some of this rebellion in your own library, school or institution, right?

It's a great technique, but don’t overdo it! And certainly not one right after the other as in this example. Just sprinkle them in here and there, sparingly. Otherwise, you'll come across as weak, unsure and needy instead of confident, courteous and respectful.

[https://files.constantcontact.com/ee1208b4001/da1c6ee9-ba35-4af5-9c80-8e1b5781189e.png]

Leadership
In which mode are you most of the time?

Whether you're an official manager, supervisor or team captain, you're still a leader in some ways. Each time you speak, you may be influencing others to your way of thinking. If you're being stubborn or difficult, others may follow.

Realize that you do have this power. So what is your approach? Start noticing your leadership modes. How often do you use each style of communicating: autocratic, democratic, benevolent and egalitarian? (Search in Tech-Talk<http://r20.rs6.net/tn.jsp?f=0016j1mL6oftGZOvD4Vrtzlh_Y5kZsxNFZc4v1HibaAK9rzpzR5zxYPdPBrx6yfNqoQ5ijuVfAZwEjL2P-mOriKPV6U6gW5AOh0X6uUCI-N-KM13th4kZWAI5AxhLmmUPD5asz2wB1QZ-BrAv076AAjriHxcYeJVGAD&c=acY_6jqKtzmlMM6b7pl0ne4E6aBPNBA3mN4iWN0IMmafvUxSEC9AGQ==&ch=tdV9BXDtrYjcVF6fxoQlNeqhk8_eh2F4K5316rOKcSxc_rFeJIdPww==> for articles explaining each of these modes. Here's one<http://r20.rs6.net/tn.jsp?f=0016j1mL6oftGZOvD4Vrtzlh_Y5kZsxNFZc4v1HibaAK9rzpzR5zxYPdNeeCKlTu16DdlP_8_zP7utCaZTLCQ7T6kZXDDHIVV9jg8K_2AKBxvz-RU0iLSX1W5k1cer6GMqk6OGanG-QCWeHmpanKy1ZY-chDsw6JG2fj7tNRcvJN73sDUaBZrod3jZIRV8hDacbnMSzWJVEOQyTRkcsbEQTjg==&c=acY_6jqKtzmlMM6b7pl0ne4E6aBPNBA3mN4iWN0IMmafvUxSEC9AGQ==&ch=tdV9BXDtrYjcVF6fxoQlNeqhk8_eh2F4K5316rOKcSxc_rFeJIdPww==> that summarizes them nicely.)

Talk to your colleagues. Get their opinions. How do they feel about your leadership modes? Find out if they feel listened to by you. Ask what makes them feel included and excluded.

Then ask yourself…

·     Which type of leadership mode do I use most often?
·     Which approach do I want to be my preferred method?
·     Do I need to modify or strengthen my belief in the value of other people's contributions to show mutual respect?

How do you think you'll do? (Click and tell us<http://r20.rs6.net/tn.jsp?f=0016j1mL6oftGZOvD4Vrtzlh_Y5kZsxNFZc4v1HibaAK9rzpzR5zxYPdOszpsT4a8fampo6wiIb8zYi59tchtHduHYfWkv2oM2-2YNPkaQV0Z3ii5XP-bokIC0uYqvS_1fXKHtxcNWys63VSdGq87fCZUs5v2iiEGLSx67x8bpb9lHW6Mte5h1vN3zNL83soxxLcMYUM7wYIIY=&c=acY_6jqKtzmlMM6b7pl0ne4E6aBPNBA3mN4iWN0IMmafvUxSEC9AGQ==&ch=tdV9BXDtrYjcVF6fxoQlNeqhk8_eh2F4K5316rOKcSxc_rFeJIdPww==>.)

[http://files.constantcontact.com/ee1208b4001/b13a7a6a-7445-4d8e-b741-4b475385ef5b.png]<http://r20.rs6.net/tn.jsp?f=0016j1mL6oftGZOvD4Vrtzlh_Y5kZsxNFZc4v1HibaAK9rzpzR5zxYPdKzxiS_TlIlyFABuCW2JYn86uYcxoNScD5jRMAm9rZ1Y3IHutu6I0-37oUM2ot_3h2ktVmTUgvnHR3cCleFqiWdLc1kESWZuti7YDKXPwIjM&c=acY_6jqKtzmlMM6b7pl0ne4E6aBPNBA3mN4iWN0IMmafvUxSEC9AGQ==&ch=tdV9BXDtrYjcVF6fxoQlNeqhk8_eh2F4K5316rOKcSxc_rFeJIdPww==>

[Ask a question]<http://r20.rs6.net/tn.jsp?f=0016j1mL6oftGZOvD4Vrtzlh_Y5kZsxNFZc4v1HibaAK9rzpzR5zxYPdI419_wq_N9hwDG97S5Zt-dMBsFulZYo8YYZvsqNvxoEIhCxgdHyGY2oxlmtwT5PsOkrLPADNIkGJlezuGbqO48YSGkKN5mCbd7GFqMvP6nQe8sreGYAgyI=&c=acY_6jqKtzmlMM6b7pl0ne4E6aBPNBA3mN4iWN0IMmafvUxSEC9AGQ==&ch=tdV9BXDtrYjcVF6fxoQlNeqhk8_eh2F4K5316rOKcSxc_rFeJIdPww==>

Copyright 1996-2020 Shared Results International. Published weekly. Distribution is limited by license. For information on how to include additional recipients, contact support at tech-talk.com<mailto:support at tech-talk.com> 941-355-2092.

The Tech-Talk e-newsletter for Oregon library staff is distributed weekly via the Libs-OR and TechTalk mailing lists. Know someone who might be interested in receiving a copy but they’re not on TechTalk or Libs-OR mailing lists? Have them contact darci.hanning at state.or.us<mailto:darci.hanning at state.or.us> or visit http://listsmart.osl.state.or.us/mailman/listinfo/techtalk to subscribe to just the Tech-Talk newsletter!

Tech-Talk is a paid subscription service for staff of Oregon libraries and is supported in whole by the Institute of Museum and Library Services (IMLS) through the Library Services and Technology Act (LSTA), administered by the State Library of Oregon.

Cheers,
Darci Hanning, MLIS
Public Library Consultant / CE Coordinator
Continuing Education Resources: https://libguides.osl.state.or.us/conted

darci.hanning at state.or.us<mailto:darci.hanning at state.or.us> | 503-378-2527| www.oregon.gov/library<https://www.oregon.gov/library>
Follow us: Facebook<http://fb.me/StateLibraryOR> | Twitter<https://twitter.com/StateLibraryOR> | Tumblr<http://www.statelibraryor.tumblr.com/> | Pinterest<https://pinterest.com/statelibraryor/>

[cid:image001.png at 01D5DB5B.50C9D890]

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://omls.oregon.gov/pipermail/techtalk/attachments/20200901/799af419/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.png
Type: image/png
Size: 15548 bytes
Desc: image001.png
URL: <https://omls.oregon.gov/pipermail/techtalk/attachments/20200901/799af419/attachment.png>