Month: July 2019

Excel: Using Slicers With Password Protected Sheets

Who doesn’t love slicers? What an awesome way to sort and filter data. We have talked about how, in addition to being a time saver, slicers can make it easier to share your data with others who are not as comfortable working in Excel. Not everyone understands, “filter the data,” but we all can understand “push this button!”

Slicers circled

At the same time, in the advanced Excel sessions, we talk about how you often want to protect a workbook from accidental changes, particularly after you have spent a lot of time creating elaborate formulas. Unfortunately, protection also renders your slicers useless… Here is a great compromise: you can protect a document and leave your slicers fully functional by incorporating a very simple method. Let’s take a look.

Before we do, big thank you to Erin for having this question in last week’s Excel Essentials session and inspiring this byte!

Exercise File

If you would like to follow along, you can download the exercise file here:

SlicerProtectDocExerciseDownload

This is a list of fictional students in a fictional class with their fictional grades. (This is fictional data, friends). In this scenario, it looks like you decided to turn this data into a table, because of course you did! And you added some slicers for ease of use so you can quickly filter and only view students with A’s or Freshmen with A’s, etc.

By the way, if you are not fully comfortable with tables or slicers in Excel, please come attend one of my Excel Essentials sessions! I would love to see you.

Disclaimer

  • With protection options, be very careful when applying passwords. If you lose the password, unfortunately, we cannot get it back for you!
  • You can also lock a document without applying a password, which is handy to know if you are worried less about dishonesty and worried more about accidental typing. This is the route we are going to take today.

Unlock Slicers

1. Right click on a slicer and select Size and Properties.

Right click menu, Size and Properties circled

2. On the right side of the screen a Format Slicer menu will appear. Click the triangle next to Properties.

triangle next to Properties circled

3. Uncheck the box next to Locked.

Box next to locked unchecked

4. Follow the same steps for the second slicer.

Protect Sheet

  1. In the ribbon, go to the Review tab, Protect group and select Protect Sheet.
Protect sheet button circled

2. This is where you could set up a password, but for today, let’s leave that area blank.

3. Scroll down in the list and check Use AutoFilter. Click OK.

Use AutoFilter circled

Now… notice that you cannot make a change to a specific cell (error message below), but the slicers should be fully functional allowing users to filter the data.

Locked cell error message

Unprotect Sheet

Don’t forget, when you want to edit, you can Unprotect the same place you Protect a sheet.

In our case, this was on the Review tab, Protect group. It can also be found in Backstage View.

Unprotect Sheet button

If we had created a password, this is also where we would have been prompted to enter it to unlock the sheet.

Thoughts?

I would love to hear your thoughts on this! Have you been frustrated by not being able to protect a worksheet and user slicers? Will this save you any trouble?

Congratulations, Power Users!

Congratulations to our newest Power Users! For the full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser

OneNote: Web Clipper

You are using and loving OneNote, right? In OneNote Essentials training, we discover how this program opens up tons of possibilities for organizing thoughts, integrating Outlook items, and so much more. One feature I talk briefly about in this training, but can’t show in detail, is the nifty Add-On you can use in your browser of choice. These Add-Ons can streamline organizing information you are pulling from the web.

OneNote Browser Add-On Downloads

About half of you are using Chrome, about a quarter are using Firefox, and the other quarter one of the other browser options.

Here is the good news… Regardless of your browser of choice, the link below will detect your current browser and send you to the appropriate download. If you use multiple browser programs, you can open it in each one (Chrome, then Firefox, etc) and download the clipper for each program.

OneNote Clipper Download

1. Select Get the OneNote Web Clipper

Get the OneNote Clipper button

2. You will be redirected to the appropriate log in page with a button allowing you to add the clipper to your browser.

Add to Chrome button

After the clipper is installed, it will appear:

  • Chrome/Firefox/Edge: to the right of your address and search bars
  • Safari: to the left of your address bar

Log In

The first time you click on the clipper, you will be prompted to log in to your OneNote account. This will give the clipper access to place your selections into your OneNote notebooks.

To log in, remember:

1. Your email is your myWSUID@wichita.edu (e.g. A123Z987@wichita.edu).

2. Password is your usual myWSUID password.

3. You will be redirected to a Shocker log in page; this will look familiar if you have taken the OneDrive training.

4. You will likely be asked to Duo, so be sure to have your device of choice at the ready to authenticate.

Clip!

Once logged in, check out the clipper! You have a choice of clipping:

  • Full page
  • Region
  • Article
  • Bookmark
Clipper Options

And, you can select a location to place your clipping. Select the dropdown by Location to see your OneNote notebooks and folders.

OneNote Locations

Thoughts?

What do you think? Do you think you will use the clipper?

Congratulations, Power Users!

Congratulations to our newest Power Users! For the full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser

Jacquelyn Johnston

Jacquelyn Johnston

PowerPoint: Restrict Editing with a Password

You have probably password protected documents in Word or Excel. In Excel Advanced Formulas, we even talk about how you can protect individual cells from being altered, while leaving the rest open to changes. Microsoft does not make it as obvious in PowerPoint that password protection is a possibility is it does in its other programs… but you actually have several options, including the ability to restrict editing while still allowing users to view your presentation. Let’s take a look.

Password Screen

Exercise File

No Exercise File today! You can open up PowerPoint into any random template if you would like to follow along.

Be Cautious with Microsoft Passwords

The usual password disclaimer applies…

  • Be very careful applying passwords in any Microsoft program.
  • If you lose the password, neither Microsoft nor ITS can reset it for you (trust me, I have been there).
  • You may want to save an copy of your original file without a password for your personal use.

Password Protecting Documents in Word and Excel

Password protection in Word and Excel is fairly obvious; you even have a special button in the Review tab to guide you through Restrict Editing features or Protect Sheet/Workbook in the case of Excel.

Restrict editing button in Word

With both Word and Excel, there are also a variety of Protect Workbook features in backstage (File) view.

Excel: Protect Workbook dropdown

On the other hand… in PowerPoint, the protection features are absent in the Review tab, though there are a lot of the same options for encryption in backstage view as Word or Excel…

Powerpoint restrict access options, prompting users to Connect to Rights Management Servers

Encrypt with Password would require someone to have a password to open your document, but maybe you only want to restrict editing. This is where people become frustrated.

For many people, Restrict Access looks like the right place to go, but for many it directs them to Connect to Rights Management Servers, which errors out when selected. There is a better way to do this…

“Save As” Password Options

1. With your presentation open, go to File, Save As (or remember my favorite shortcut, F12).

2. In the lower right, click on Tools dropdown and select General Options.

Save as screen, tools dropdown

3. A screen will appear prompting you to either:

  • Require a password to Open, or
  • Require a password to Modify. Let’s stick with Modify for now.

4. Enter a password for Modify, and click OK. You may be prompted to reenter the password.

Password to Open or Modify Screen

5. Save and close your PowerPoint file. Open the file again, and you should be prompted to either enter a password, or open a read only version.

Prompt when opening document: enter password or open as a read only document

Your users will still be able to view and print the document, but they will not be able to make changes.

Remove the Password

Later, if you would like to remove the password:

1. Open the file, entering the password when prompted.

2. Revisit the Save As screen to find the Tools, General Options where you first set the password.

Save as screen, tools dropdown

3. Here you can remove the password you originally created and press OK

Password screen with current password and ok circled.

Thoughts?

What do you think? Did you know about this feature in PowerPoint?

Congratulations, Power Users!

Congratulations to our newest Power Users! For the full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser

  • Rosemary Hedrick
Rosemary Hedrick

Word: Mail Merge with PDF Attachments

Have you ever wished that you could do an email Mail Merge with PDF attachments as the merged results? There are a variety of reasons you may want to do this. Maybe you would like to send individual PDF letters to students via email attachment, or perhaps you want to have a partially completed PDF form that is personalized for each person you are emailing. The possibilities are endless! By the way, big thanks to Julie and Meghan for having an awesome question in last week’s Word session that has inspired this Byte!

Background

  • Today I am going to be referencing an Add In that comes with the installation of Acrobat DC on a computer. To request Acrobat DC, or other Adobe Creative Cloud programs, contact the Help Desk at 4357.
  • Also, for these exercises, I am assuming you have some experience with Mail Merge in Word. If you have never merged before, or are not comfortable with the process, please come attend my Microsoft Word Essentials training! You will be comfortable with it in no time.

Exercise Files

Here are a couple files for experimentation, if you would like to follow along:

Sample LetterDownload
Mail MergeDatabaseDownload
  • Sample Letter Word Document: this is your file that you would like to turn into a merged PDF for recipients. This is a fictional letter for potential students.
  • Mail Merge Database Excel File: this is a fictional database for linking to the merge document.
  • Important: you should save these both to your desktop before starting, since you will have to browse to find the Excel file when merging.

Creating the Merge

You will start this merge like any other email merge:

1. Open the Sample Letter Word file.

2. Go to Mailings tab and select Start Mail Merge

3. Select Letters

Start Mail Merge dropdown, Letters selected

4. Select Recipients and browse for the Excel file

Select recipients dropdown, Use an existing list selected

5. Insert Merge Fields as desired. I am going to insert:

  • Address block
  • Greeting line, and
  • Interest field
Insert Merge Field dropdown

Work Offline

In real life, this is an optional step when doing an email merge, but I will ask you to do it this time, since we are working with fake email accounts, and you probably don’t want to receive a bunch of bounce back emails. When you work Offline in Outlook, it allows you the opportunity to review your email merge before going back online and sending them.

  • In Outlook, go to the send/receive tab and select Work Offline.
Outlook Send/Receive tab, Work Offline selected

Finish and Merge

1. Instead of going to Finish and Merge, like we usually do, we are going to select Merge to Adobe PDF.

Mailings tab, Merge to Adobe PDF circled

2. In the popup screen:

  • Specify a name for your PDF
Specify PDF File name completed with "Welcome" title
  • Check the box next to Automatically send Adobe PDF files by Email. Email data will populate by default, but that is something that could be changed if the wrong column is selected.
Automatically send Adobe PDF files by Email box checked. Email selected from dropdown.
  • Type in a special email Message, if desired.
Message inserted in Message box: We can't wait to see you this fall!
  • Press Ok

3. Select a location to save the merged PDF files. This creates separate PDFs for each of the merge results, for your records.

If there you don’t see a location that will work, notice you can also create a new folder in the lower left.

Browse for location screen

Double Check Results

  1. Open your Outlook.
  2. Since you are working Offline, these files are going to be sitting in your Outbox. You should see a number on the left side of your screen next to Outbox. Click on the Outbox to access the folder.
Outbox displayed on list

3. You will see a list of emails. Double click on any of them and take a look at the email that is set to send. You will see your message, subject line, and a PDF attachment.

Email from results, with PDF attachment

4. Double click on the attachment. Here is the letter you wanted to send, as a PDF attachment.

PDF of merged Word file

5. The individual PDFs are also living wherever you chose to store them on the previous section, step 3.

Work Online

Don’t forget that you need to go back online again with your Outlook! When you go back online, all of the emails in your outbox are going to send automatically, and in this case (with our fake emails) is not a good idea.

Red X next to each email to delete from Outbox.
  1. Click on the red X to the right of of each of the emails to delete them.
  2. When you are finished, revisit the Send/Receive tab and toggle off the Work Offline button to go back online again.

Thoughts?

Will you use this in your area? I would love to hear how you plan to use this tool!

Congratulations, Power Users!

Congratulations to our newest Power Users! For the full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser