Compatibility Mode: How to Re-Activate Your Favorite Features

This happens frequently after a PivotTables session… An attendee excitedly returns to their computer to create a PivotTable from an existing Excel file, only to discover that the PivotTable screen looks a lot different than it did in class, and a lot of their favorite features are grayed out in the ribbon. This is not just an Excel issue… sometimes this happens Word or PowerPoint: certain features are mysteriously faded in the user’s ribbon. The culprit? Compatibility Mode. Let’s talk about this setting, and how to escape from it, in your Microsoft Office documents.

What is Compatibility Mode?

The intentions of Compatibility Mode are in the right place. Its purpose is to make a document as functional as possible with older versions of software. So if you are using Office 365, and your friend is running Office 2010, you could send this Compatibility Mode document to them and rest assured it will look the same to them as it does to you.

For this reason, you will often see data that is exported from other programs; Reporting Services and WSU Reporting, for instance; default to Compatibility Mode. The software programmers aren’t sure what version of Office you will be using, so they err on the safe side, and have the export default to Compatibility Mode.

Another possibility: you are opening an old document. If someone created this document in 2001, and never updated the file format since then (we will talk about this further down), chances are you are in Compatibility Mode by default.

Missing Features

Earlier I mentioned that PivotTables are often the first place people notice missing features in the ribbon…

… But this happens in other Excel tabs, PowerPoint, and Word as well.

The features that gray out vary from version to version, but bottom line, newer features are what disappear in Compatibility Mode. When someone is experiencing these ghosts of buttons, this is almost always the culprit.

How to identify Compatibility Mode

How do you know for sure if your document is in Compatibility Mode? There are several ways.

1. You might have some visual cues; the icons look slightly different in Compatibility Mode, shown below, on the left side of the icons.

2. At the top of your document, next to the title, you might see the words Compatibility Mode

3. Go to your Save As screen (File, Save As). What do you see in the dropdown under the title? If it says Excel 97-2003 Workbook, you are in Compatibility Mode.

In fact, stay in the Save As screen, and I will show you how to fix this.

How to Escape From This Setting (and get your favorite features back)

In your Save As screen (shortcut: F12), Compatibility Mode reads as Excel 97-2003 as the File type (dropdown under the document title).

To fix this issue, click on the dropdown under the title, and select the first option for Type: Excel Workbook in this case (Word Document for Word and PowerPoint Presentation for PowerPoint).

Important: you must close the file and reopen it to see the new options appear.

That’s it! You have your features back! I hope this helps with some of your legacy documents or documents exported from various sources.

Thoughts?

What do you think? Have you been plagued by the trials and tribulations of Compatibility Mode?

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: Secret Recording Tab

We are all finding ourselves making more recordings in PowerPoint lately. I hope you had a chance to try out the new and improved Record Slide Show feature in PowerPoint. In fact, you are probably using a variety of recording features in PowerPoint and have likely noticed that these features are spread out throughout numerous different tabs in the ribbon: Slide Show recording is on the Slide Show tab, screen recording on the Insert tab, exporting a video is in Backstage View, and so on. Let’s simplify the process with a secret tab: the Recording Tab.

Activate the Recording Tab

We learn about another secret tab in Excel Macros session, but PowerPoint has its own special tab: the Recording Tab.

To activate this tab:

1. Right click anywhere on the contents of the ribbon.

2. Select Customize the Ribbon.

3. On the right side of the popup is a list of the tabs in your ribbon. Scroll down to Recording. Click on the checkbox next to this tab to activate it in the ribbon.

4. Press OK.

Recording Tab

Hey, you have a new tab! How exciting. Here are some details about what you can access here.

A. Record Slide Show: A feature found on the Slide Show Tab. Learn more about this feature here.

B. Screenshot: Normally found on the Insert Tab, this feature allows you to grab a picture of your screen for a presentation.

C. Screen Recording, Video, and Audio: Also found on the Insert Tab, powerful tools for inserting video, audio, or recording your screen in action.

D. Save as Show, Export to Video: Typically you have to venture to Backstage View (File) to access these features.

Just to reiterate… you are not accessing new features with the Recording Tab, but you may find this to be a time saver as it brings together features that normally live all over the ribbon in various disparate tabs.

Thoughts?

What do you think? Will you utilize this secret tab to save some time when recording 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

Maddie Domebo (Picture coming soon!)

Excel: Merging Data with Power Query (Bonus Video!)

Power Query in Excel is an incredibly powerful tool. A while back, I posted a brief introduction to this feature. I wanted to expound upon that information with another great use for Power Query: merging data. This is a common situation: a lot of us have data files we are pulling from different sources that contain unique information. Combining this data into one powerhouse sheet can be a great time saver. Let’s take a look at how to do this.

Video

Hey, I am trying something different! Thank you to those of you who completed our ITS Applications Training Survey last week. A ton of you said you wanted videos and exercises, so let’s try it out today. Below is a link to a YouTube video, and read on for the exercise files. I will also include written instructions, as per usual.

Exercise Files

Here are a couple downloads for you:

MergeExercise: Start Here

MergeExerciseSolved: to check your work

Two Disclaimers

For this Byte, I am assuming you already have some basic knowledge about Excel, like Tables and PivotTables. If you would like to learn more about either of these, I would love for you to attend a session! Look for sessions in myTraining.
Also, you will likely need to be using the Windows version of Excel, preferably 365, though this should also be possible in 2016.

About This Workbook

This spreadsheet contains three sheets: each with unique information about our key players in the name column.

The first sheet contains their number and location,
The second contains their favorite animal, and
The third their favorite candy. Our goal is to bring all this data together in one powerhouse worksheet.

Import Data as Queries

1. In the first sheet, place your cursor in the middle of the data. Press Ctrl + T to turn it into a table. You can also do this from the dropdown in the Home tab.

2. Make sure “My table has headers” is checked and all the data is selected, and press OK. Repeat this step with each sheet of data, so you have a total of 3 tables in the worksheet.

3. Select one of the tables. Go to the Data tab in the ribbon, Get & Transform data group, and select From Table/Range. If you don’t see this button in the Get & Transform Data group, check the Get Data dropdown for it.

…you will be taken to the Power Query editor screen…

4. In Applied Steps on the right, click the X next to changed type to remove it.

5. In the upper left select the dropdown for Close & Load To…

6. Select Only Create Connection and press OK.

7. Repeat Steps 3 – 6 for all the tables you created. You will see a list of tables appear on the right side of the screen under Queries and Connections.

Merge the First Two Queries

1. In the Data tab, go to the Get Data dropdown, hover your mouse over Combine Queries, and select Merge.

2. In the Merge screen, select your first table in the upper dropdown, and your second table in the second dropdown.

3. Select the two common columns: in this case, it is the Name column. Click on the Name column under each table. Press OK.

4. In the Power Query screen, find the double headed arrow at the top of the last column, from the merged table. Click on this button to expand information.

5. Select what you would like to expand. In this case, we are unchecking the name box, and just keeping Animal selected. Press OK.

This will expand the Animal column from the second table. Note that it applies its own naming format to the column title. You can rename the column, if desired. Double click on the title to change the name.

Keep this Power Query screen open for the next step.

Merging Subsequent Queries

We now have 2 merged queries. We can add the last one from within the Power Query screen.

In the Power Query ribbon, Combine group, select Merge Queries.

2. In the Merge screen, your Merged query will appear at the top by default. In the second dropdown, select the final table. Just like you did in the previous section, select the Name columns in both queries, and press OK.

3. Just as you did in the previous section, the final table appears at the top with a double headed arrow. Click on the double headed arrow to expand. And leave only Candy selected. Press OK.

4. Select Close and Load To… but this time keep the default selected: Table on a New Worksheet. Press OK.

Final Table

Our merged query is visible as a table on a new worksheet.

A Couple Notes

It helps if you think about your final table like a Pivot Table:

If you need to change something about the data, be sure to change the data source (one of the data sheets), not the merged query.
When you do change the data source, be sure to refresh the query. A simple way to do this is to right click on the merged table and select Refresh.

Thoughts?

What do you think? Did you like the video? I hope this was helpful. Feel free to send me an email anytime.

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

La’Rell Marion (picture coming soon!)

Top 10 Secret Key Commands in Microsoft Office

Whenever I mention one of these shortcuts in a session, I see people taking out their pencils to write them down. Most of us know some commonly used keyboard commands in Microsoft Office; Ctrl + Z to undo, Ctrl + C to copy, and so on… but I have some lesser-known favorites to share with you today. Also, take a moment to check out this article, which contains some more general shortcuts.

F12 = Save As

If you have taken any of my Essentials sessions, you know this one, one of my absolute favorites. Remember in the old days when you used to be able to select Save As from the menu, and you would be taken directly to a dialog box with everything you wanted in one place? If you are like me, you found that box to be way faster than clicking through the options one by one in the new File/Backstage menu.

So just press F12. It does everything you want, and works in all your favorite Microsoft Office programs.

F7 = Spell Check

Want a quick spell check? No need to visit the ribbon, just press F7.

Especially useful in Excel, where we don’t get the red squiggly lines underneath misspelled words like we do in Word.

F7 will also work in PowerPoint…

Alt + Q = Find Features

This is a big one. Sometimes you know that a feature exists in Microsoft Office, but you are just not sure where to find it. Press Alt + Q to search for it.

Forget how to get to the Accessibility Checker? Press Alt + Q and search for it…

While I still think it is a good idea to know where features live in the ribbon, this is an excellent tool to find what you are looking for in a hurry.

Ctrl + Y = Redo

Companion to the famous Ctrl + Z (undo), Ctrl + Y gets a lot less fanfare, and I have no idea why. It is a great cheat if you don’t know a key command for something. Ctrl +Y will simply recreate the last action.

Let’s say you want to insert a row into Excel in a variety of different places. Maybe you don’t know the key command (Shift Ctrl + +), so you insert one row… with the old-fashioned right click , Insert.

To insert another row, go to the new location, and hit Ctrl +Y. Click on the next location, Ctrl +Y, and so on. This will be much faster than performing the old “Right click, Insert” method 20 or so times.

It also works for formatting. A couple weeks ago, I was editing a Word document with quite a few pictures that I wanted to have identical style formatting. I selected one picture, applied a style format, then selected the remaining pictures, pressed Ctrl + Y and voila! All the same formatting was applied.

Note: Ctrl + Y will only apply the last action, so if you were making several format changes to an item, you might be better off copying formatting with the Format Painter. Please attend an Excel, Word, or OneNote Essentials session to learn more about this amazing feature!

Ctrl + Home/Ctrl +End = Go to the Beginning or End

Need to get back to the top of a document, or conversely, need to find the end? Try Ctrl + Home or Ctrl + End to navigate.

Ctrl + Home will take you back to the top of your document, and Ctrl + End will take you to the bottom.

Note: this will require that you are using a full keyboard… sometimes the Home and End buttons are not present on smaller keyboards or laptops.

Ctrl + Shift+ (arrow) = select an entire column or row

If you have attended Excel Advanced Formulas you know this one. Ctrl + Shift + Down Arrow will select all the data in a column. This also works with a right arrow to select all the data in a row. And in Word, it will select an entire row of data at a time.

Outlook: Ctrl + Shift + M = Create Email

I mentioned in a previous article that Outlook has some amazing time saving shortcuts. This one and the next one are my two favorites.

Regardless of the Outlook module you are working in, Ctrl + Shift + M will create a new email message. No need to return to the mail module.

Below I was in my Calendar module, and started an email with this command.

Outlook: Alt + S = Send Email

Once you are done typing your email, don’t touch that mouse… Alt + S will send the email.

There have been occasions where someone was standing behind me as I used this command and the last in Outlook, and they exclaim, “How did you do that??” The wizardry of key commands!

Word: Alt + Ctrl + 1 (or 2, or 3) = Format as Heading

We are all utilizing headers after attending Word Essentials, right? Well, here is a quick way to set a line to Heading 1, 2 or 3: Alt + Ctrl + 1 (or 2, or 3).

Excel: Ctrl + T = Convert Range to Table

After you attended Excel Essentials, you ran back to your computer to convert all your ranges to tables, right? The functionality we see for tables goes even beyond what we see in Essentials and Pivot Tables sessions. It is just a good practice to start utilizing them.

Instead of using the Table feature from the Home tab of the ribbon, place your cursor in the middle of your data and press: Ctrl + T.

Of course, you can still make adjustments via the contextual Table Tool tab.

Thoughts?

What do you think, are any of these one of your favorite shortcuts? Or do you have a different favorite secret shortcut to share?

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

Kaleb Basham
Joan Wilson

PowerPoint: New and Improved Slide Show Recording

If you haven’t visited PowerPoint’s recording options for a while, you are missing out. The Record Slide Show feature is typically utilized when your end goal is to export your presentation to a video format; either to be posted online somewhere or in a kiosk setting. Previously Record Slide Show would allow you to record audio to go along with your presentation. Then you could export a complete video showing your slides with your voiceover. This was already a great tool, but the feature received a fantastic facelift in 365, enabling even more abilities. Let’s take a look.

Record Slide Show

Record Slide Show lives on the Slide Show tab, Set Up group.

You have the choice to start from the selected slide, or from the beginning of the show.

Control Center

The new control center for recording a slide show is phenomenal. Within this screen are the following tools:

1. Start or Stop Recording

In the upper left part of the screen is the ability to start or stop recording. Once you create a recording, there will also be the ability here to replay what you have created.

2. Notes

In the center of the screen is a way to access any notes that are tied to a slide. Click on the dropdown to view them.

To the right of the Notes dropdown is the ability to make the text of the notes larger or smaller, signified by a large and small “A”.

If you would like to learn how to create Notes, please attend a PowerPoint Essentials session!

3. Incorporate Front Facing Camera to Record Yourself

The exciting aspect of the improved feature is that it will incorporate your computer’s camera to allow you to record audio and video of yourself within the recording. Your video will appear in the lower right of the PowerPoint presentation.

There is the ability to turn off audio or video below the image.

Note: if you do not have a camera or microphone, the options will be greyed out. You will still be able to record a slide show, however, and can incorporate things like markup (below).

4. Markup

In the bottom center of the screen are some markup tools. As with all markup tools, this will work best with a stylus, if possible.

The recording will incorporate your markup as you create it.

5. Slide Number and Timer

Lastly, in the lower left you will see slide number and a timer.

A Couple Notes

Shortcuts

Most of the shortcuts in the Record Slide Show view will be similar to those you have been using in Slide Show view:

To advance to the next slide, you can use your usual method: the space bar, an arrow key, a clicker, etc.
To escape from this view, click your Esc key.
Caution: some shortcuts, like W for whiteboard, or B for a black screen, do not work in this view.

Export Options

Don’t forget, you can export your final document via your usual method,

1. File –> Export

2. Select a method. In the case below, I selected Create a Video.

3. Be sure you have selected Use Recorded Timings and Narrations to incorporate your recording.

**Important: if you receive an error when you try to open the video, give it a little time. Often the icon for the exported video appears before the video has finished exporting. Give it a few minutes and you should be good to go.

Thoughts?

Did you know about this improved feature? What do you think?

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

Michelle Dehaven

Outlook: The Power of Drag and Drop

Most people are somewhat familiar with the concept of “drag and drop” when it comes to software… that is to say, when you click on an item, hold down your mouse, and drag it to another location. Sometimes it is as simple as moving a file from one folder to another; the action of clicking on the file and dragging it to the new folder. But there are other tools you can access with this action. Outlook takes this feature to another level, and contains a remarkable number of possible uses for drag and drop. Here are just a few…

Attachments

In Outlook Essentials, we talk about the Attach File button in the ribbon…

… but another popular way to attach files is by dragging the file from its location into the body of an email. Particularly handy if your document is located on the desktop.

This will also work to drag the attachment from the body of a received email into a new email.

Reorder Calendar View

Here is a secret, little-known drag and drop feature: reordering calendars. Many of us work with multiple calendars open at once, like my two coworkers’ calendars below.

Sometimes calendar order gets altered for some mysterious reason, or maybe you just want them to be in a different order than the default. Drag and drop to the rescue!

Click on the calendar name on the left pane and change the order with drag and drop.

In this case, I chose to flip the order, but I could easily flip it back with another drag and drop.

By the way, if you are working with calendar groups (come to Outlook Advanced to learn more), you can reorder an entire group with drag and drop. Pretty nifty!

Create Appointments from Other Modules

When I wrote about the Journal (secret feature!) I showed you this little trick, dragging and dropping a Journal entry into the calendar to make a note of the time you spent on the phone call or conversation.

The same goes for the other modules. What if you would like to set aside some time to look at the content of an email, or make a meeting in response to it?

Drag and drop the email from your inbox into the calendar module in the navigation.

This will create an event with the details of the message plugged in. Don’t forget to set the time and invite attendees, of course!

Create Emails from Other Modules

What about the inverse? Perhaps you have an event, the details of which you would like to share in an email without an actual invitation.

Try dragging and dropping a calendar event into your mail module in the navigation.

This will create an email, with all the contents of your calendar event, ready to share with interested parties.

Create Tasks from Other Modules

There are tons of ways to create tasks from an email, but one of them is drag and drop! Same story here, click on the email and drag it down to the tasks module to get started.

In this case, sometimes the flag button we talk about in Outlook Essentials is a little quicker, but this may come in handy too!

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

Kelley Smetak

Microsoft PowerUp! Videos Posted: Check Out What’s New in Office 365

Almost 150 people attended the special Microsoft PowerUp! Sessions… If you missed them, or even if you attended and wanted to revisit some of the special content, videos are available now!

The purpose of these sessions was to give everyone a quick update on some of the highlights now available (or coming soon, depending on your update schedule) in Microsoft Office 365. Keep an eye out for future sessions as more updates are released…

PowerPoint Updates

Word Updates

Excel Updates

Bonus Content: Sandy

This is Sandy (Power User!), with a very kind testimonial. 😊😊

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

Erin LeBegue (not pictured)

Chris Leonard

Tyra Miles

Marsha Compton

Microsoft Accessibility Tools for Low Vision

Creating accessible documents is a vital practice; if you haven’t checked out my entry about the Microsoft Office Accessibility Checker, or Responsible use of Color in Documents please do! To continue on this subject, I thought we could talk about some Microsoft Windows and Office tools for people with low vision. If you are somebody with low vision, or know somebody with low vision, check out some of your options below. By the way, even if you do not have low vision, you might find some of these tools handy, for a variety of reasons.

Microsoft Zoom Tool

Microsoft’s Zoom tool is an excellent resource and comes with the default installation of Windows 10. This tool will instantly magnify your entire screen.

Within the Zoom tool, you can adjust the zoom level with the + or – buttons, the settings of which will be saved the next time you access the feature.

To quickly access the Zoom tool, press the Windows key + plus sign (+).

Take a look at more key commands below for the Zoom tool.

Key Command	Action
Windows key + plus sign (+)	Zoom in (activate zoom tool)
Windows key + esc	Turn off zoom
Ctrl + Alt + Space bar	In zoom mode, show whole screen (context)
Windows key + U	Ease of Access Center: Make default text bigger, apps bigger, change size of cursor and mouse pointer (more on this below)
Windows key + minus sign (-)	Zoom out by one increment

When the zoom tool is activated, select the gear to view more settings.

The settings offer the ability to collapse the magnifier window and create me screen space. To change this setting, select Collapse Magnifier Window.

Display Settings

There are several ways to access display settings. You can always press the windows key and search for display:

…or the keyboard shortcut is Windows Key + U.Within this screen is the ability to:

Make all text bigger
Make the entire screen larger by default
Change the size of Apps and text on other displays
Change the size of the cursor and mouse pointer

Narrator

Windows 10 has a built in screen reader called Narrator. To access Narrator either:

Press the windows key and type Narrator,or
Use the command Windows Key + Ctrl + Enter

To access more settings for narrator:

Press the Windows Key and go to Settings
Select Ease of Access
Select Narrator

High Contrast Settings

There are several ways to access high contrast settings in Windows. One way is to select the Windows Key and type “High Contrast”. Within the High Contrast options is the ability to turn on high contrast and adjust the settings.

High Contrast mode can also be activated with the command:
Alt + left shift + Print Screen

Microsoft Office

You know this Byte wouldn’t be compete without some discussion about the Microsoft Office suite.

Quick Access Toolbar (QAT)

Don’t forget that in all of your Microsoft Office Programs is the ability to add your favorite tools to the Quick Access toolbar. This is the tiny toolbar that appears above the ribbon in Microsoft Office Programs.

You can customize your QAT with any command in the program, eliminating the need to hunt for frequently used features.

To add something to the QAT, either right click on the item and select Add to Quick Access Toolbar…

… Or press the dropdown arrow on the right of the QAT, and select More Commands to access a full list.

More about the advantages of the QAT below…

Dictation

Heads up, you will need a working microphone on your computer for this one; some have this by default, but if not they are affordable to purchase.

In the Home tab of Word and PowerPoint, and in the Message tab of Outlook, there is a Dictate button, which will allow you to dictate the content of your document or email.

In addition to taking dictation of your words, you can also verbally input punctuation and spacing, like:

Comma
Semi colon
Period
Question Mark
New paragraph (for a new line)

In general, you can also access Dictation in Windows 10 with the command: Windows Key + H

Read Aloud

Just like you needed a microphone for Dictation above , make sure you have working speakers for Read Aloud. This feature lives in the Review tab of Word…

… and the Home and Message tabs in Outlook.

Excel is a little bit trickier, but there is an ability to have cells spoken to you. Remember your QAT (above)? This is where we will go to turn this feature on in Excel

Follow the instructions above to access More Commands in the QAT. Then this feature is found under All Commands. It is called Speak Cells.

While you are in the QAT settings, you can also add Stop Speaking Cells to your QAT. Once this is in your QAT, you will see how easy it is to access when you read the next section.

The Power of Alt

When you are in any Microsoft Office program, press Alt on your keyboard. Notice that some labels appear in your ribbon.

These labels contain letters and numbers that are shortcut keys to access the tabs and QAT in the ribbon.

In the example above (in Word), you can press Alt to access the commands, then P for the Layout tab. Now you have a new set of options. Maybe you choose to press M to access Margins.

In other words, you could learn a series of commands with these keys. In the future, maybe you would remember you could just press Alt, P, M to get to your margins.

Even if you are not someone with low vision, you might find that learning some of these shortcuts can really speed up productivity. These key commands almost always end up being a lot faster than hunting for icons.

Keyboard Commands

Most of us are familiar with a few keyboard commands. Ctrl C to copy, Ctrl V to paste, and so on. The fact is, there are tons of keyboard commands for Office applications, in addition to the handy Alt trick you learned above. And much like the Alt example, even if you are not someone with low vision, you might find that learning some of these shortcuts can speed up productivity.

Take a look at an example below, with a handful of commands that you can use in Outlook. Would you be interested in lists like this for all your Microsoft Office programs? If so, let me know!

Outlook Navigation

Key Command	Action
Ctrl + 1	Mail
Ctrl + 2	Calendar
Ctrl + 3	Contacts
Ctrl + 4	Tasks
Ctrl + 6	Folder List
Ctrl + 8	Journal
Ctrl + E (or F3)	Search
Ctrl + Y	Go to a Different Folder
Alt	Access the ribbon

Create Item

Key Command	Action
Ctrl + shift + M	Create New Email
Ctrl + shift + A	Create Appointment
Ctrl + shift + C	Create Contact
Ctrl + shift + L	Create contact group
Ctrl + shift + Q	Create Meeting Request
Ctrl + shift + K	Create Task
Ctrl + shift + E	Create Folder
Ctrl + shift + J	Create Journal Entry

Email

Key Command	Action
Ctrl + R	Reply
Ctrl + Shift + R	Reply All
Ctrl + Shift + M	New Message
Alt + S	Send a message
Ctrl + F	Forward
Arrow Keys	Next message, Previous Message
Ctrl + Period	Next message (with msg open)
Ctrl + Comma	Previous message (with msg open)
F9	Check for New Messages
Ctrl + shift + B	Open Address Book
Insert	Add Quick Flag to unopened message
Ctrl + U	Mark as Unread
Ctrl + shift + I	Switch to Inbox
Ctrl + shift + O	Switch to Outbox

Format Text

Key Command	Action
Ctrl + B	Bold Text
Ctrl + Shift + L	Add bullets
Ctrl + I	Italic Text
Ctrl + U	Underline Text
Ctrl + ]	Increase Font Size
Ctrl + [	Decrease Font Size
Ctrl + X	Cut
Ctrl + C	Copy
Ctrl + V	Paste
Ctrl + K	Insert Hyperlink

All Items

Key Command	Action
Ctrl + S	Save
F12	Save As
Ctrl + Z	Undo
Ctrl + D	Delete
Ctrl + P	Print
F7	Check Spelling
Ctrl + L	Left Align
Ctrl + E	Center Text
Ctrl + R	Right Align
Alt + S	Save and Close

Thoughts?

What do you think? What did I forget? If you are someone with low vision, are there features that you use in Microsoft Windows or Office that help you better use the applications? 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

Amanda Duffy

Outlook: Delete Old Calendar Events in Batch (Plus Bonus Tip)

Most of us employ archiving to some degree in our Outlook experience. Even with archiving, sometimes you may find that you don’t feel the need to keep old calendar events from many years ago… and believe it or not, they are taking up space in your Exchange account. If you are in this boat, you may decide to delete old calendar events in batch. Here is a handy way to accomplish this in Outlook.

By the way, shout out to Aaron for having this quandary and inspiring this Byte!

Using Advanced Search

There are probably several ways to accomplish this task, and some of it depends on your version of Outlook. We are going to use Advanced Search for this Byte. And you can follow along up until the last three steps if you are uncertain if you want to delete but just want to explore the feature.

1. Navigate to the calendar in the navigation (lower left). In the upper right click into the search field.

2. This activates your search tab. In this tab, select Search Tools and Advanced Find.

3. Go to the Advanced Tab in the popup.

4. In the Field dropdown, select Date/Time fields and End.

5. For Condition, select On or Before.

6. For Value, list the date that you want to cut off.

7. Select Add to List and Find Now.

8. Click into the body of results. Press Ctrl + A to select all

9. Right click, and Delete.

10. Don’t forget to empty your deleted items folder to free up space!

The same process could theoretically apply to mail, though I have found that people tend to archive mail and/or want to be more cautious about deleting them in batch (understandably)… whereas fewer people care about what meetings and lunches they attended in 2014.

Bonus Tip: Emojis

How about a bonus Outlook tip at no extra charge? I have to thank Emily for this one, because I had no idea about this shortcut.

If you want to insert an emoji into an email, you can access a full list with the command: Windows Key + ;

If you don’t see this appear, be sure you have clicked into the body of the email message.

By the way, you can access these emojis in other screens as well… try it out in your browsers!

Thoughts?

What do you think? Do you need to clear out any old events in your calendar to free up some space? Let me know how it goes!

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: Filter a Pie PivotChart and Maintain Percent of Whole (A Different Take on Progress Charts)

A fantastic question came up during last week’s open lab. Is it possible to create a PivotChart pie chart that can be filtered to show only the percent of the item selected? Here is one solution for that question (and spoiler alert, it is technically not a PivotChart).

A few things before we get started…

First, thank you to Marsha for having this question and inspiring this Byte!
Second, this article assumes you have some knowledge of Tables, PivotTables, and Charts in Excel. If you don’t, please come attend a session… times are listed in myTraining.
Lastly, this is basically a different take on the idea of a progress pie chart… and I am by no means the creative genius behind this overall idea! If you have a minute, look at some of the tutorials out there for the different use cases for these. They are mighty handy.

Background

Normally when you select a slicer or filter a Pie chart by one item, each item shows as 100%. Here is an example:

Below is a visual of a PivotChart Pie Chart…

Looks great… but when I add my slicers and filter by one item, it always shows as 100% of the total, because it is 100% of what is displayed.

What if we want to use those slicers, but also maintain the visual of the item’s percent of the grand total… in other words, in this case, to still show as 36% of the whole? Some finagling is in order.

Exercise

Rather than an exercise, I am going to try something different. Here is the final document… and below will be the steps I took to create it: Example Document

In the document, one sheet is the source data, and the other sheet is the final chart with slicers that will adjust the pie chart accordingly.

Click on a few slicer buttons to test it out. Pretty cool!

Starting Point

We originally started with source data (on the Source Data sheet in the document) that consisted of two columns: item and a number.

Format as a Table

This first step might be optional, depending on your use case. Since data would be fluctuating in our case (e.g. new rows of data added), it was best to format as a table.

We learn about tables in Excel Essentials, and the advantages of formatting as tables in Excel Pivot Tables, so please come to a session if you are interested in learning more!

1. You can either go to the Home tab, Format as Table, or use the command Ctrl + T.

2. Pick a design if you would like (in this case, yellow for WSU).

Create a Helper Column for Percent

We need a cheat column that would keep a percent constant when filtered, so a helper column was in order. You have probably noticed that formulas look different in a table… you still want to build them in the first cell of a column to carry them down.

We want the column to take the figures in column B and divide them by the total. The formula ends up looking like this: =[@Number]/SUM([Number])

This looks complicated, but it is very automatic. Here were my keystrokes/mouse clicks:

[click into the first cell of Column C], Type = [click on B1] / SUM [Select B2 through B7].

Create a Pivot Table

Click on the table to activate Table Design tab.
On Table Design tab, Select Summarize with PivotTable.
Location of Pivot Table: on a new sheet, titled Pivot.
Build the table with Item as rows, Helper Column as Values.5. Insert Slicer for Item (on the PivotTable Analyze tab).

Create Helper Cells with GETPIVOTDATA

So here is the twist… Instead of creating a PivotChart, we are actually going to create a chart from helper cells that reference information from the PivotTable.

1. The first helper cell references the Grand Total of the PivotTable:

[Click in B1], type = [click on the Grand Total cell of the PivotTable].

Notice how this appears in the formula bar as a GETPIVOTDATA formula in the formula bar. This cell will always return whatever Pivot Grand Total is Displayed.

2. The second helper cell, in B2, contains the formula =1-B1. So it will always read the difference of 100% from whatever the GETPIVOTDATA result shows.

Create Chart from Helper Cells

Finally, we can make the chart!

1. Select the two Helper Cells in B1 and B2.

2. Go to the Insert tab, and select the Pie Chart dropdown.

3. Select the Doughnut (it could be any of the pies, but this is the route I took for reasons you will see).

4. If desired, you can pick special colors by right clicking on any data point and selecting Fill. I picked some WSU colors.

Finishing Touches

The number you see in the center of the doughnut is actually a title. Remember how we learn in Excel Pivot Tables, Charts and Pictures training that a chart title can reference a specific cell? This will come in handy here.

1. Title: Click on the line of the title and press = , then click on B1

So this Title will always reference this cell, which will always reference the grand total displayed on the PivotTable.

This means you will always have a number corresponding to the colors on the chart.

2. Text Box: This may be optional depending on your situation, but I inserted a Text Box at the top of the chart to read Percent of Total: Insert tab, Text Box.

3. Position Slicers: Final step was moving the Slicers and resizing them so they fit neatly on top of the Pivot Table. This was only cosmetic, and is also optional. Really there are infinite possibilities for how you could play around with final formatting with a chart like this.

A Simple Alternative

It is worth mentioning that, depending on your situation, you may be able to skip the PivotTable step by incorporating a total row in your Table. This would apply if you didn’t need to sum multiple entries of specific items.

You would still need to incorporate helper columns, but instead of linking to the Grand Total of the PivotTable, they could link to the Total Row sum.

So many possibilities!

Thoughts?

What do you think? Has this sparked any ideas? Have you used pie/doughnut charts for this type of scenario before?

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

Robin Mishler

Megan Nold (not pictured)

Kylie Sharpe