Office Bytes – Page 6 – Bite sized tips and tricks for WSU Microsoft Office Power Users

Word: Compare and Combine

In the last Office Byte, we talked about the ability to compare two Excel documents via a special Add In. In the case of Word, we can use a feature already present on the Review tab, and in many ways it is even cooler. We cover this Compare feature in the Word Advanced training… if you would like to learn more about this, I would love to see you at a future session! Let’s take a look.

Exercises

If you would like to follow along, here are a couple of exercises. The first is a fictional draft of a document, and the second is a final version.

7Employee-BenefitGuideDraft Download

8EmployeeBenefitGuideFinal Download

Much like with the Excel example, our goal is to ascertain the difference between the two documents.

Also, big thank you to Hannah in HR for letting us use her Final document as an exercise (the draft is a fictional version of her final).

Compare

In Word, the Compare feature lives on the Review tab. This will make sense when you see what happens after we load up both documents.

1. In a blank Word Document, go to the Review tab, Compare group, and click on the Compare dropdown.

2. Select Compare.

3. A popup will appear. Click on the folder to the right of the Original Document and browse to select the draft document. Then click on the folder to the right of Revised document, and select the final version.

Compare documents screen, folder icon circled

4. Notice you have the ability to label the changes. You could list the name of the author of the revised document here. Click OK.

Label changes with: James T. Kirk as author

One more note… there is a button that says More at the lower left of this screen. For now, I am going to skip that, but it is worth taking a look as you use this Compare feature more.

Navigating the Compare Screen

Hey look, a mission control screen! What are we looking at here?

Revisions

On the left side you will see Revisions. you can click on any of these to be taken directly to the appropriate area of the document.

Original and Revised Documents

On the right side, you have two panes: the Original and the Revised document.

Combined/Compared Document

In the center are both documents together. As you scroll down in this document, you will see the left and right panes scroll with you.

Already, this “mission control” view (not the official name) is making it much easier to compare the differences between these two documents, and see exactly what my coworker changed. But there are some other cool things about this feature.

Changes: Accepting or Rejecting

I mentioned there is a reason this feature lives on the Review tab. The center document effectively took our original, overlaid the revised version, and is retroactively treating it like comment and markup.

What does this mean? For one, you can accept or reject each of the revisions, just as if your coworker had used markup to change your document. What a cool tool! Especially if your office doesn’t regularly use Track Changes features, and you want to utilize their functionality…

Additional Notes

A couple more things I want to make sure to share:

New Document

When you chose to Compare these two documents, take a look at the top of your Word screen. This created a new document titled Compare Result.

You can save this as its own document. It will not have the mission control experience when you reopen, however it will retain the changes as if it were a document with track changes enabled, and you will be able to come back later and decide to Accept or Reject changes.

Combine

So that was compare… what is this Combine option in the same dropdown? If you selected Combine instead of Compare and ran through the same exercise, you actually wouldn’t see a huge difference.

So a common question is… what is the difference between the two features? I have heard it said that the difference is Compare is only for two documents, and Combine is for multiple documents… this confused me, since I don’t see a way to add more than two in the Combine screen. What I came to learn is that Combine allows for track changes to be turned on on the documents that are being compared… it is basically Compare for two documents with tracked changes already enabled… allowing for more collaborators.

Long story short (too late)… the features behave remarkably similarly, with the one exception that if Track Changes is turned on in either of the documents to be opened, Combine seems to be the method of choice.

Annnd… if you want to learn more about Track Changes, check out Word Essentials training! [/shameless plug]

Thoughts?

So what do you think, do you think you will use Compare with your Word files?

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

Tierney Mount

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…

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).

FictionalClass Download

FictionalClassEdited Download

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.

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.

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.

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.

Exercise Files

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

WordLetterforMerge Download

ExcelforMerge Download

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.

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.

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

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).

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.

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.

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

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″)

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

=TEXT(D2,”#0.00″)

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

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

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.

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

1. Right click on top of Search Folder and select 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.

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.

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:

WichitaStatePresentation Download

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:

IconLinksExcercise Download

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:

CalculatedField Download

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).

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.

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

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.

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

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.

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

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.

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

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

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.

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.

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.

You should be left with a layout similar to this:

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.

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

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.

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

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!”

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:

SlicerProtectDocExercise Download

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.

3. Uncheck the box next to Locked.

4. Follow the same steps for the second slicer.

Protect Sheet

In the ribbon, go to the Review tab, Protect group and select Protect Sheet.

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.

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.

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.

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

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

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

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

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

Exercises

Compare

Navigating the Compare Screen

Revisions

Original and Revised Documents

Combined/Compared Document

Changes: Accepting or Rejecting

Additional Notes

New Document

Combine

Thoughts?

Congratulations, Power Users!

Exercise Files

Spreadsheet Compare Add In

Compare Files

Export Results

Drawbacks

Thoughts?

100th Power User Certificate Awarded!

Exercise Files

Excel File

Word File

Mail Merge with Formatted Number Columns

Text Formulas

Thoughts?

Congratulations, Power Users!

Exercise File

PC Users: Create a Search Folder

Create Another Search Folder

More options

A Word About Search Folders

A shortcut to search

Mac Users: Smart Folders

Thoughts?

Congratulations, Power Users!

Exercises

Set Up Custom Slide Shows

Access Custom Slide Shows

Thoughts?

Congratulations, Power Users!

Exercise File

Assigning Links

Linking within the Document

Linking Outside the Document

Additional Notes

Thoughts?

Congratulations, Power Users!

Exercise File

PivotTable Contextual Tabs

Inserting a Calculated Field

Notes

Thoughts?

Open Labs

Congratulations, Power Users!

Exercise Files

Master Slides

Master Slide View and Creating Layouts

Test Out Your New Slide

More About Master Slide View

Themes, Colors and Fonts

Title

Thoughts?

Congratulations, Power Users!

Exercise File

Disclaimer

Unlock Slicers

Protect Sheet

Unprotect Sheet

Thoughts?

Congratulations, Power Users!

OneNote Browser Add-On Downloads

Log In

Clip!

Thoughts?

Congratulations, Power Users!