[techtalk] Tech-Talk: EXCEL - Lock Spreadsheet Cells to Protect Your Formulas

Tue Feb 6 12:20:48 PST 2018

Greetings!

The Tech-Talk e-newsletter for Oregon library workers is distributed weekly via the Libs-OR and TechTalk mailing lists. Please do not forward.

Know someone who might be interested in receiving a copy but they’re not on the Libs-OR or TechTalk 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 receive the TechTalk newsletter!

This paid subscription service 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. Enjoy!

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

Brought to you by the State Library of Oregon

[State of Oregon]

The State Library is pleased to provide you with Tech-Talk.com. We encourage you to make the most of this skill-building membership site at www.Tech-Talk.com<http://r20.rs6.net/tn.jsp?f=001Xf53aNHVllpE6Z8MSVQKvsOnQ5xgf-W3C_j3TbV-qFmutCi105he_xi01TGIihyYYJc0cD7KR1gEc_fNNSUdOD9X2BRdCaKy6RlDHVw5Cf3B7-SjmJJNkqVEBOWp4NOuKxNYUEAo6_9OMyxyOsk4qg==&c=M6IqKJWgLiKrV03lWDqvCW35O0JFZFMyhcUJ8Z4IKn-lrsLuyBQauw==&ch=BhMzfiI1akbkq2QykfSd3KPrvWwgOgTXVAgnX6F8UjfceTrcDdQP4g==>.

Username: ORLIBTECH
Password: ORLIBTECH (case sensitive)
Contact Darci Hanning at darci.hanning at state.or.us<mailto:darci.hanning at state.or.us> if you have questions.

[lock spreadsheet]<http://r20.rs6.net/tn.jsp?f=001Xf53aNHVllpE6Z8MSVQKvsOnQ5xgf-W3C_j3TbV-qFmutCi105he_1O0KxfLQ3OpfMHuOW8U66h8C8IDmwg-XiQ4X1ch9cYgYxencV26KHkL6SffZeZM1ZSDgHcxCUWKFkvtOskWw9cck0mHmNU2UQeEehbJS7ZLRq8ZAEpjQjEaAQpwpokmSTEuOu_8SqGTJEg05upAPLowbQb8YzrYKQ==&c=M6IqKJWgLiKrV03lWDqvCW35O0JFZFMyhcUJ8Z4IKn-lrsLuyBQauw==&ch=BhMzfiI1akbkq2QykfSd3KPrvWwgOgTXVAgnX6F8UjfceTrcDdQP4g==>

 This Week's Tech-Talk: EXCEL

1. VIDEO ... Lock Spreadsheet Cells to Protect Your Formulas
2. ARTICLE ... Lock Spreadsheet Cells to Protect Your Formulas
3. COMMUNICATING ... Try being absurd in negotiations

NOTE: To view the VIDEO, click here<http://r20.rs6.net/tn.jsp?f=001Xf53aNHVllpE6Z8MSVQKvsOnQ5xgf-W3C_j3TbV-qFmutCi105he_1O0KxfLQ3OpfMHuOW8U66h8C8IDmwg-XiQ4X1ch9cYgYxencV26KHkL6SffZeZM1ZSDgHcxCUWKFkvtOskWw9cck0mHmNU2UQeEehbJS7ZLRq8ZAEpjQjEaAQpwpokmSTEuOu_8SqGTJEg05upAPLowbQb8YzrYKQ==&c=M6IqKJWgLiKrV03lWDqvCW35O0JFZFMyhcUJ8Z4IKn-lrsLuyBQauw==&ch=BhMzfiI1akbkq2QykfSd3KPrvWwgOgTXVAgnX6F8UjfceTrcDdQP4g==>. Enter the Username and Password!

Tech-Talk: EXCEL - Lock Spreadsheet Cells to Protect Your Formula
Intermediate

[lock cells]

I created an expense report in Excel to give to a friend who was starting a business. I wanted her to be able to insert her own data into it, but not change any of the formulas.

You may have a similar example, a situation where you took a lot of time to make sure that calculations were built into a worksheet so that it saved time by doing the computing for you. If someone clicked inside a cell that had a formula and entered their own data instead, the formula you created would disappear.

If you hesitate to share information in your spreadsheets with colleagues because you're afraid they'll accidentally change a formula (or certain data) -- there’s a great solution!

It's actually easy to protect the formulas and content in your spreadsheet by locking some of the cells in the spreadsheet.

Protect Cell Content

Before we go through the steps in this process to lock certain cells in your spreadsheet, there's a concept to understand first, that will help you to remember the process.

The steps for locking cells will seem backward. You'd think you could just "protect" certain cells and all of the others would remain editable. However, what you are really doing is unlocking the cells you don't want to protect, and then protecting the rest of spreadsheet.

[lock cells]

First, Unlock Data Cells

Open the Excel worksheet you want to protect.

[lock cells]

·     To unlock any cells, rows or columns that you want other people to be able to potentially change, select each cell or range.

·     On the Home tab, in the Cells section, click Format, and then click Format Cells.

·     On the Protection tab, un-check the Locked check box, and then click OK.

Next, Unlock Any Graphics

To unlock any graphic objects like pictures, shapes, or Smart Art, that you want users to be able to change:

·     Click to select the graphic. If you have more than one, hold down the CTRL key and then select each graphic object that you want to unlock.

·     In the Ribbon the Picture or Picture Tools will be displayed along with the Format tab.

·     On the Format tab, in the Size group, click the Dialog Box Launcher arrow to the right of the word "Size".

·     The Format Shape Window will open; Choose Properties and un-check the Locked check box.

Lastly, Protect the Worksheet

The last step is to now protect the Worksheet.

·     On the Review tab, in the Changes group, click Protect Sheet.

[protect worksheet]

·     The Protect Sheet window will open.

·     Leave the top box, Protect worksheet and contents of locked cells CHECKED.

·     In the Allow all users of this worksheet to list, select the elements that you want users to be able to change.

·     TIP: Even if you leave the Select locked cells and Select unlocked cells boxes checked, users can click on these, but not edit them.

OPTIONAL. In the Password to unprotect sheet box, type a password for the sheet, click OK, and then you'll be prompted to reenter the password to confirm.

Adding a Password to unprotect sheet is a choice. If you don't add one, any user can Unprotect the worksheet and make changes.

To Unprotect the Worksheet

If you would like to make changes to the locked cells, you'll need to Unprotect the worksheet.

·     Simply go to the Review tab and in the Changes section, click on the Unprotect Sheet.
·     You'll be prompted to enter your password (if you entered one), and then you can make changes to the protected cells.

[http://files.constantcontact.com/ee1208b4001/f8f1ecc8-4884-4c21-8ed0-cf647e6db944.png]

Communications: Negotiating
Try being absurd to get reluctant data

You're negotiating. Money is involved.

One scenario has you at work with your boss. Okay, maybe you don't think of it as a negotiation because you've been given an assignment; you don't feel you are in the driver's seat. But, there is data and information that would help you achieve success. If only you could get it.

Or, another situation, you need some work done by a contractor and don't want to wait for a formal quote. You want a ballpark idea, but the individual is reluctant to toss out a number.

You want to know...

·     How large of a budget do you have for this task?

·     How much do you expect this will cost?

Here's the trick for getting the magic money number from someone. In a light-hearted manner, ask a question involving a three-part absurdity.

"How large of a budget do we have ... $500, $5000, $50,000?
(TIP: The amount always starts with the same number
so that the focus is on the range, not a specific amount.)

"How much do you expect this to cost ... $10, $100, $1000?

In Summary: Ask about the money, followed by three examples (one ridiculously low, one in the ballpark, one ludicrously high).

Inevitably, it will surprise and amuse the other individual ... so much so they will offer up the correct number.

[innovation]

 CLICK HERE ... for the TECH-TALK LIBRARY!<http://r20.rs6.net/tn.jsp?f=001Xf53aNHVllpE6Z8MSVQKvsOnQ5xgf-W3C_j3TbV-qFmutCi105he_xi01TGIihyYYJc0cD7KR1gEc_fNNSUdOD9X2BRdCaKy6RlDHVw5Cf3B7-SjmJJNkqVEBOWp4NOuKxNYUEAo6_9OMyxyOsk4qg==&c=M6IqKJWgLiKrV03lWDqvCW35O0JFZFMyhcUJ8Z4IKn-lrsLuyBQauw==&ch=BhMzfiI1akbkq2QykfSd3KPrvWwgOgTXVAgnX6F8UjfceTrcDdQP4g==>

NOTE: Forgot your user ID? See your sponsor block at the top of this email.
Problems? Contact us at Support at Tech-Talk.com<mailto:Support at Tech-Talk.com>

 YOU CAN ASK A QUESTION!

Do you have an idea for an article, or a problem using technology in some way...

We can help! CLICK HERE<http://r20.rs6.net/tn.jsp?f=001Xf53aNHVllpE6Z8MSVQKvsOnQ5xgf-W3C_j3TbV-qFmutCi105he_7SmhEU77h4SsFn_8I_Gb9QxJD4w542ummXie9YH6wr1BdbkfNHF5I42RHl9ebXdbYgqWczs5UlkcFEGRUhMXduNba_w7nBwD-uTi78Ln9xNAy6aGkHwZ1w=&c=M6IqKJWgLiKrV03lWDqvCW35O0JFZFMyhcUJ8Z4IKn-lrsLuyBQauw==&ch=BhMzfiI1akbkq2QykfSd3KPrvWwgOgTXVAgnX6F8UjfceTrcDdQP4g==>.

Just contact us and we'll get the answer for you right away.

[http://files.constantcontact.com/ee1208b4001/464c50f5-0d77-4705-bb24-bf95437ce585.jpg]

Copyright 1996-2017 Shared Results International. Published weekly. Do not forward this issue of Tech-Talk.com without prior permission from Shared Results International. Distribution is limited by contract. Forwarding it to unauthorized recipients constitutes copyright infringement.
For information on site licenses or how to include additional recipients, email darci.hanning at state.or.us<mailto:darci.hanning at state.or.us>

Cheers,
Darci Hanning, MLIS
Technology Development Consultant
darci.hanning at state.or.us<mailto:darci.hanning at state.or.us> | 503-378-2527 | www.oregon.gov/osl/ld<http://www.oregon.gov/osl/ld>
Follow us: Facebook<http://fb.me/orlibsupport> | Twitter<https://twitter.com/ORLibSupport> | Tumblr<http://www.statelibraryor.tumblr.com/> | Pinterest<https://pinterest.com/statelibraryor/>
Ask me about the Edge Initiative<http://oregonedge.net/>!
[cid:image005.png at 01D31C27.4B9FEA30]

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://omls.oregon.gov/pipermail/techtalk/attachments/20180206/63272c5f/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/20180206/63272c5f/attachment.png>