Excel: Spreadsheet Compare

Has this happened to you? You create an Excel spreadsheet that you share with your coworker. She edits the document, sends it back to you, and you are left scanning through a long list of entries to try to determine what changes she made…

In our Word Advanced sessions, we talk about the nifty Compare feature in Word. This feature is absent in Excel, but there is a similar option for Excel users via a Microsoft Add In called Spreadsheet Compare. This Add In comes with PC versions of Microsoft Office 2013, 2016, and 365… in other words, you probably already have it…

Compare Files button in ribbon

Before we jump in, thank you so much to Hannah and Jamie for inspiring this Byte!

Exercise Files

If you would like to follow along, below are a couple of Exercise Files. Save these to a place where you can easily find them for the next step (e.g. Desktop).

This is a fictional class list of students. We are going to assume that the FictionalClass download is the original and the FictionalClassEdited is what your coworker altered and sent back to you.

Spreadsheet Compare Add In

To access the Spreadsheet Compare Add In, click on the Windows icon in the lower left of your task bar, and search for Spreadsheet Compare.

Windows button searching for Spreadsheet Compare

You will be taken to a sort of mission control for comparing spreadsheets. You haven’t loaded anything in here yet, so there is not much to see.

Spreadsheet Compare program with no files loaded

Compare Files

1. In the upper left, click on the first button called Compare Files.

Compare files button in the ribbon

2. A pop up screen will appear. Notice it is asking you to select an “older” file and a “newer” file.

3. Click on the folder to the right of each of these fields, and browse to find the FictionalClass (older) and the FictionalClassEdited (newer) that you downloaded from the exercise files above.

Compare files pop up with folder icons circled

4. The two workbooks will load into the left (older) and right (newer) panes. Notice that changed cells are highlighted in green. Click on one of these cells, and it will select the corresponding cell in the other sheet.

two spreadsheets next to each other with changed cells highlighted.

5. Look at the bottom of the screen for an outline of the various changes that were made. This list notes all sorts of changes that were made, including the contents of cells and the formatting of cells. You can also click on any of these to be taken to the change in the worksheet on the panes above.

Changes lined out underneath

6. Try double clicking on one of the changes highlighted in the bottom pane. You can also access this feature by clicking on Show Details in the ribbon.

Details of the changes that were made to a cell.

It is a little disorienting. This popup screen is showing you what was in the original document, and the change that was made in the second sheet, in kind of a strange format. Simply click on the X in the upper right to go back to the main Spreadsheet Compare screen.

Export Results

It is possible to export all the results from this comparison. in the Export group is a button called Export Results.

Export Results button circled in the ribbon.

This will create an Excel file of all the differences between the two workbooks.

Differences exported in a new Excel document.

Drawbacks

While this is a nifty way to quickly see changes between two Excel files, it lacks some of the handiness of the Word Compare feature…

  • You can’t accept or reject the alterations/differences, you can only see them displayed and highlighted. Any desired changes will still have to be made by hand in the source document.
  • There is not a way to save a combined document containing both the original and changed document.

Other than that, a pretty cool tool!

Thoughts?

Do you think you will find a use for Spreadsheet Compare? Do tell!

100th Power User Certificate Awarded!

This is a most exciting week in Power User history! We have a three way tie for 100th WSU Microsoft Office Power User. Big congratulations to:

  • Tiffany Morgan
  • Matt Stiles
  • Lauren Wilson
Lauren Wilson, Tiffany Morgan, and Matt Stiles on a poster titled "Congratulations, Power Users!"

For more information about the WSU Microsoft Office Power User Program, visit wichita.edu/poweruser

Mail Merge With Tricky Number Formats

Have you ever attempted to mail merge with fields that contain formatted numbers, only to find that the formatting does not carry over onto your merged Word document? This is a common source of frustration with mail mergers, and something that we can quickly remedy on the Excel end. Let’s take a look… but first, big thank you to Jamie for having this excellent question at Wednesday’s Excel Essentials session and inspiring this Byte.

Insert Merge Field dropdown in Word

Exercise Files

If you would like to follow along, download today’s exercise files below to your desktop.

For this Byte, I am assuming you know the basics of working with Mail Merge in Word. If you are not comfortable with Mail Merge, please attend a Word Essentials training with me! I would love to show you how it works.

Excel File

Let’s take a look at the Excel file first. This is a list of employees… here are all out new professors who we are going to send letters to, welcoming them to Starfleet.

There are columns with Last Name, First Name, Salary, FTE, and Phone Number. The number fields are all formatted: Salary as currency, FTE as rounded to two decimals, and Phone Number as the Special Phone Number format.

Excel file with names, Salaries, FTE, Phone Number, and TEXT columns

Heads up: Notice that, highlighted in yellow, there are also some “helper columns” with TEXT titles. Let’s ignore those for the present, because in real life, perhaps you haven’t created those, and instead incorporated number formatting, as most of us do in our Excel Files

Word File

Opening up the Word File, you will find a letter welcoming professors to Starfleet Academy. Our plan is to enter merge fields into the bold area circled below.

Letter with merge field entry circled

Mail Merge with Formatted Number Columns

Let’s start our merge. We are going to build this using our number formatted columns. Again, I am assuming you have merged before for this exercise. If you are not comfortable with Mail Merge, please come attend one of my Word Essentials session. I would love to show you!

1. In the Mailings tab, go to Start Mail Merge, and select Letters.

Start Mail Merge dropdown

2. For Select Recipients, select Use an Existing List. Browse for the Excel file, wherever you chose to save it.

Select Recipients dropdown

3. Take a look at the Insert Merge Field dropdown, and make sure that you see all the columns you saw in your Excel file. Remember, we are focusing on the first five merge fields for now (not the TEXT ones).

Insert Merge Field dropdown

4. Insert each merge field in the indicated location. It should look like the example below.

Merge fields inserted into Word document

5. Let’s toggle the Preview Results button to see how this will look.

Preview Results button

This is usually the point where people realize that certain types of number formatting do not come through in Mail Merge. The salary, phone number, and FTE are just general number formatting. The phone number has no dashes, the salary has no commas, and the FTE is missing a “0.”

Merge fields previewed with unformatted numbers.

6. Let’s remove the Phone, Salary and FTE merge fields, and try this again. This time, insert the TEXT options that we saw in our “helper” columns in the Excel file.

Insert Merge Field dropdown with TEXT fields circled.

So Salary should be replaced with TEXTSal, FTE with TEXTfte and Phone with TEXTPh.

Merge fields inserted as described

7. When you Preview results, it will look much more like what we had in mind.

Preview with formatted numbers

What difference! Let’s see what makes these columns different in our Excel file.

Text Formulas

Open the Excel file again, and let’s do some investigating. We are going to check out our “helper” columns in columns F, G, and H that contain TEXT in the header.

1. Let’s see how the formula for TEXTSal is set up. Click into cell F2, and take a look in the formula bar. The formula used for Sal was:

=TEXT(C2,”##,##0″)

Excel, F2 selected, formula bar circled

2. Do the same for TEXTfte in G2. The formula used was:

=TEXT(D2,”#0.00″)

Excel, G2 Selected, formula bar circled

3. … and finally, TEXTPh in H2. The formula used was:

=TEXT(E2,”[<=9999999]###-####;(###)###-####”)

Excel, H2 selected, formula bar circled

So our investigating reveals that TEXT formulas have been used in these columns, which came over in a much better format for mail merge purposes.

If you would like to learn more about the TEXT function, take a look at this handy guide from Microsoft that will walk you through creating all of these used above, and more!

Thoughts?

Has this ever happened to you when you were mail merging with numbers? Do you think you will incorporate TEXT functions with your future merges?

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

Haley Underhill

Outlook: Search Folders and Smart Folders

Search Folders often elicit an “aha!” moment for attendees in Outlook Advanced training. Many people discover these handy folders can take the place of their existing complicated folder rules. Let’s see how these special folders work.

New Search Folder View

Exercise File

Hey there is no exercise file for you today! Just open up your Outlook and follow along.

Oh, and if you are a Mac user and think this won’t apply to you, think again! I have a solution for you toward the end.

PC Users: Create a Search Folder

On the left side of your Outlook mail, you likely have all sorts of folders. Scroll all the way down to the bottom, and you will find Search Folders

Search Folders on left menu

1. Right click on top of Search Folder and select New Search Folder.

right click menu, new search folder

2. A popup screen will appear with a plethora of options. Lets start with one toward the top: Mail Flagged for Follow Up. Select this option and press OK.

New Search Folder View

3. A new folder appears on the left side of your screen under Search Folders. Click on it, and here is a shortcut to all the mail you have flagged for follow up.

Don’t have any messages flagged for follow up? No problem, we are going to create some more folders.

For Follow Up Search folder visible on left side of screen

Create Another Search Folder

1. Follow step 1 above, and this time select Mail from and to specific people.

2. Toward the bottom of the popup, press Choose to search for a person.

New search folder view, choose button circled.

3. You will be taken to the old familiar search screen (blurred out here for WSU employees’ privacy). In the upper left, type the last name of someone who emails you often.

Global Address List

4. When you find their name on the list, select it, and press OK.

5. Now you have a folder of all the emails in your inbox from this person.

More options

It is worth your time to look through all the Search Folder options. Popular Search Folders include:

  • Unread mail
  • Mail flagged for follow up
  • Important mail
  • Mail from specific people
  • Categorized mail
  • Mail with attachments
  • Mail with specific words (think about form results that come to you with the same title)
  • Custom Search Folder: if all else fails, you can completely design your own
New Search folder view, mail with attachments selected.

Since most of us have a lot of activity on the left side of our Outlook screen, if you want to draw attention to your new Search Folder, you can add it to favorites by right clicking on it.

Right click menu, add to favorites circled

In this way, and a couple other ways, Search Folders are going to behave very much like other folders. But there are some important differences.

A Word About Search Folders

Since they behave similar to regular folders, it is easy to forget that Search Folders are a different type of animal.

A shortcut to search

A search folder is a shortcut to search, nothing more. It is a window into what could have been an advanced search. This is important for several reasons:

  • The mail in the folders still only exist in your inbox, not a separate folder within the inbox.
  • Deleting a search folder will not delete the contents, just the search window.
right click menu, delete folder circled.

If you delete a search folder, you will even receive a message that tells you as much.

warning message indicating that items items in the folder will not be deleted if folder is deleted.

Note: be careful when you do so that you are selecting Delete Folder, not Delete All.

Mac Users: Smart Folders

Mac users, it is finally your turn! While you don’t have Search Folders, you have something arguably cooler. They are called Smart Folders.

1. Start by clicking into the Search bar in the upper right. This will activate the contextual Search tab.

2. Click on the Search tab, and create a customized search: perhaps email from a specific person, then plugging in their name underneath.

3. In the upper right of the Search tab, press Save Search.

Mac view: search tab circled, save search button circled

4. A new folder is created in your Smart Folders, all you have left to do is name it!

New folder appears in Smart Folders on the left.

In a lot of ways, the Mac Smart Folders are more user friendly than the PC Search Folders, but they both accomplish the same task: they give you a very special window into a search of your inbox.

Thoughts?

What do you think? Will you find a use for Search Folders or Smart Folders? Will this eliminate the need for some of your folder rules?

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

PowerPoint: Custom Slide Shows

Did you know that you can create custom slide shows in PowerPoint? Let’s say you have a PowerPoint document that you would like to use for two different presentations, but you have a few different slides that you would like to show for each presentation. You can easily create several custom slide shows from the same document. Let’s check it out.

Exercises

If you would like to follow along, you may download today’s exercise here:

This is a PowerPoint document about Wichita State University. The data is all fictional, of course!

The idea is that you would like to create two possible presentations from this one document: one presentation for parents and one for students.

Set Up Custom Slide Shows

1. Go to the Slide Show tab in the ribbon.

2. In the Start Slide Show group, select the dropdown for Custom Slide Show and select Custom Shows.

3. In the popup that appears, press New.

4. Give the slide show a name in the box provided. Perhaps our first one will be the student presentation, so we can title it Student Show.

5. Select the slides you would like to appear in the slide show: perhaps everything except Finances and Statistics.

6. Press the Add button, and make sure the slides appear on the right pane. Notice you have the ability to rearrange the slide order with the arrows on the right.

7. Press OK.

8. Repeat the steps above, but this time create a Parent Show, consisting of all the slides except Recreation.

9. Now when you visit the Custom Slide Show dropdown, Custom Shows, you can see both the shows you created.

Access Custom Slide Shows

Are you ready to show one of your presentations? Revisit the Custom Slide Show dropdown, and select either the Student Show or the Parent Show to start one of these presentations.

Thoughts?

What do you think? How will you use Custom Slide Shows?

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

Carody Bryan

Linda Steinacher

Excel: Navigating Inside a Document with Links

Utilizing links in an Office document can do so much more than simply take your end users to a website. Links are also a great way to help people navigate around a document. Below is a great example… let’s check it out.

Exercise File

Feel free to follow along with today’s Exercise File:

This is a special file full of (fictional) information; there are a variety of sheets at the bottom of the document: a Home sheet, a Management sheet employee information, a sheet with a Class List, and last but not least, Dessert Sales.

You would like to share this document with a group of people who may or may not be very proficient with Excel. Just like we saw with slicers, creating buttons for people to press can be a huge help if people are not familiar with navigating around an Excel document.

Assigning Links

Linking within the Document

We want to assign links to the icons on the Home sheet.

1. Click on the icon of people to select it.

2. Right Click on top of the image, and select Link. (note: this may be called Hyperlink in your version of Office)

3. Notice on the left side of the screen there are a variety of location options. For the icons, we are going to select Place in This Document.

4. Select the Management sheet and press OK.

5. Test out your link by deselecting the icon (click on any cell), then click on the icon.

6. Follow the same process to link the Backpack image to Class List, and the Cake Slice to Dessert Sales.

7. Notice each additional sheet (besides Home) has some arrows in an icon to the right of content. Click on this icon to select it, then link this to the Home sheet.

Test out the icons you just created! You should be able to navigate to a sheet by pressing on an icon in the Home sheet, then go back home by pressing on the Arrows on any other sheet. How cool!

Linking Outside the Document

We have created all sorts of links inside our document. Let’s take a look at linking outside the document.

1. On the Home sheet, press on the WSU Logo to select it.

2. Right click on top of the logo, and select Link (or Hyperlink).

3. On the left side of the screen, press Existing File or Web Page.

4. In the Address box, type http://wichita.edu

5. Press OK.

Test out your newly created link by clicking on the WSU Logo.

Additional Notes

  • Linking to Documents: When you linked to the webpage, you might have noticed the contents of your computer pop up underneath. You can link to an existing document, but remember that the only people who can open the link will be people who have access to the location you are linking to. If you link to something on your desktop, probably you are the only person who can open the link (and only on that machine).
  • Cell Reference: When linking to a place in this document, you can have the link go directly to a specific cell. Enter the cell name in the field provided, otherwise it will default to A1.
  • Removing a link: At any time, links can be removed or edited by right clicking on top of the link and selecting Remove Link or Edit Link.

Thoughts?

What do you think? Will you use links to navigate within your shared documents?

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

Excel: PivotTables and Calculated Fields

Formulas and PivotTables. Many of us have gotten caught up creating formulas outside of a PivotTable by referencing cells within a pivot table, only to be disappointed when we update our PivotTable and cells shift back and forth. There are several ways of dealing with this issue. One way is to create a Calculated Field within your PivotTable. This is something we cover briefly in Excel PivotTables sessions, but it is definitely worth a review.

Fields, Items and Sets drop down with Calculated Field circled

Exercise File

If you would like to follow along, here is an exercise file:

This document contains two sheets: one sheet contains the PivotTable, and the other contains the source data, showcasing a couple years of dessert sales. Right now, you have income and expense information in the values of the PivotTable. Since we have those figures handy in our data, it would be nice to figure out net income.

For this Byte, I am assuming you are comfortable with PivotTables in Excel. If you are not, please come attend my PivotTables, Charts, and Pictures sessions! Sessions are listed in myTraining and are updated regularly.

PivotTable Contextual Tabs

We are going to be working a lot with the PivotTable Tools contextual tabs. Like any other contextual tab in Microsoft, remember that you need to click on the element to see the corresponding contextual tab(s).

PivotTable Tools COntextual Tabs

When you click on the PivotTable, you should see two contextual tabs for PivotTable Tools: Analyze and Design.

Click onto the pivot table to activate Pivot Table TOols tab

Be sure you can see these tabs before moving on to the next step.

Inserting a Calculated Field

Remember, our end goal is to create a Net Income field that will subtract the Expenses from the Revenue.

1. With the PivotTable Tools activated, go to the Analyze tab, Calculations Group.

2. Select the dropdown for Fields, Items & Sets and select Calculated Field.

Fields, Items and Sets drop down, Calculated Field circled

3. In the popup screen that appears, you will see all the PivotTable fields listed at the bottom of the screen. At the top is an opportunity to name the field and a space for a formula.

Insert Calculated Field Pop up

4. Click in to the Name box and change Field1 to Net Income.

5. Click in to the Formula box, and double click on Revenue in the Fields area. Type a “-” on your number pad, and double click on Expense. We are building a formula that will read: = Revenue – Expense.

6. Press Add.

Insert Calculated Field pop up, filled out as described in text.

7. Press OK, and look at the field you just created incorporating a formula from two other fields. Pretty cool!

Pivot table with new Calculated Field column

Notes

  • PivotTable Fields: You will see this calculated field appear in the Values section of your PivotTable Fields.
Sum of Net Income visible in the Values box of Pivot Table Fields list.
  • Calculations from Calculations: If you are creating another Calculated Field, you will see the Net Income field we just created as a field option for use in future formulas.
Calculated field visible in a new Calculated Field pop up screen for further calculations

Thoughts?

What do you think? Can you incorporate Calculated Fields into your PivotTables? I would love to hear from you!

Open Labs

Did you know that Applications Training has open labs every other week? Whether you have a Banner question or a Microsoft question, feel free to stop by! Open lab times are listed in myTraining under the title Open Lab Assistance on Banner 9, Time Entry, Reporting, & Microsoft/Adobe.

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

Julie Scott

Julie Scott with Power User Certificate

PowerPoint: Creating Master Slide Layouts

Why would someone want to alter a Master Slide in PowerPoint? Several reasons… Sometimes you are making a simple alteration to the overall formatting or appearance of your slides – we do this in PowerPoint Essentials training. Other times you might want to do what we are about to do today, which is to make a larger layout change for your presentation. Altering the Master Slide might sound intimidating, but it can be a huge time saving step… and can make uniformity of your presentation so much simpler. Let’s check it out.

Exercise Files

No exercise file today! You can open PowerPoint to your favorite theme (or even no theme at all). For reference, my screen shots are showing the Gallery theme.

Master Slides

You have seen Master Slides before, even if you didn’t know where they lived. Whenever you select New Slide on the Home tab, you were picking from preset Master Slides. You probably noticed these options change when you select a different theme… that is because each theme comes with its own unique set of Master Slides.

New Slide view

Let’s find where these Master Slides live. In the View tab of the ribbon, Master Views group, select Slide Master.

View tab, slide master button circled

This can be a bit disorienting at first. Your presentation seems to disappear (but don’t worry, it is still there!), and you are thrown onto a new tab called Slide Master with a plethora of options.

Slide Master tab

At any time, you can leave Master View by selecting Close Master View in the Slide Master tab.

Close Master View circled

Master Slide View and Creating Layouts

Let’s make some adjustments to the Master Slides.

1. Go to View tab, Slide Master (instructions above).

2. On the left side of the screen, notice a list of all the existing layouts. Hover your mouse over one of them to see the name appear.

Mouse hovered over slide layout to see title
  • Notice there is a plethora of options on a right click menu… more on this later. But for now, notice that if you like a layout, and want an additional slightly altered version of it, you have the ability to duplicate and alter.
Right click menu, duplicate circled

3. In the Slide Master tab, go to the Edit Master group an select Insert Layout.

Insert Layout circled

4. In your new layout, find the Master Layout group, and select the dropdown for Insert Placeholder.

Insert Placeholder circled

5. Notice you have the option to limit content to a specific type, or insert generic content (what you are most used to seeing in layouts). We are going to insert Content.

Insert Placeholder options

6. Your cursor will change to a cross hair. Draw out a box the size you would like your content box to be on your slides.

Drawing a placeholder

7. Let’s do another one. Go back to Insert Placeholder (above), and this time select SmartArt. Draw out this box next to the Content box you created. Notice you can use the PowerPoint guides (red dashed lines) to see if the boxes have lined up properly.

Size guides
  • You should be left with a layout similar to this:
Final new layout

8. Remember how we explored the right click options earlier? Find your new layout in the slide list on the left. Right click on top of it, and select Rename Layout.

Right click menu, rename selected

9. Give it a new name that you will easily be able to identify.

Name box

Test Out Your New Slide

Let’s test it out! Remember to leave the Master View, go to the Slide Master Tab, and select Close Master View on the right.

Close Master View button circled

You will be taken back to your presentation. Select the New Slide dropdown. Do you see your new layout?

Newly created master slide visible in New Slide dropdown

More About Master Slide View

While you were in Master Slide View, You might have noticed a couple of other things about the Master Slide tab…

Themes, Colors and Fonts

In PowerPoint Essentials training, we talk about manipulating themes, colors and fonts on the Design tab… the Master Slide tab contains another way to access these same features.

Themes, Colors, Fonts on Master Slide View

Title

To the left of where we selected Insert Placeholder, you might notice a checked box for Title. This is a very important little box.

Master Layout: Title

Luckily this box is checked by default, but be sure not to uncheck it. All of your slides should have a title: this is usually found at the top of your slide. If you were to remove this title box and replace it with a text box for instance, your PowerPoint will not be as easy to work with, nor will it be accessible to people using screen readers. Long story short, leave it checked!

Thoughts?

Will this help you customize your presentations? I would love to hear from you!

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

  • Tonya Bronleewe
  • Donna Hughes

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:

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