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

Excel: Don’t Merge Those Cells! Here is an Alternative.

Friends don’t let friends merge cells! This is something you hear often among Excel enthusiasts. People usually merge cells in an attempt to make a spreadsheet look nicer. That being said… not only is the beauty of a spreadsheet less important than its functionality, which is definitely adversely affected by merged cells… but there is actually a way to alter the appearance identically to merging cells without all of the many disadvantages that come with merged cells. Let’s check it out.

Exercise

If you would like to follow along with my demonstration below, here is an Exercise file: MergedCells

This is a fictional list of students and grades, with some merged cells at the top.

Merged Cells

The first row of data contains 3 sets of merged cells: A1 with B1; C1 with D1; and E1 with F1. If you select any of these, notice in your Alignment group that Merge and Center is selected.

Perhaps someone did this in an attempt to make their spreadsheet look less cluttered? Let’s see why this might have been a bad idea.

Why Merged Cells are Problematic

There are actually quite a few ways that merged cells can be problematic. Here are just a few.

Sort and Filter

Let’s say I would like to use the custom Filter buttons we play with in Excel Essentials. You want to filter by all students with an A.

1. Click anywhere in the top row, and on the right side of the Home tab, select Sort and Filter, and Filter.

2. Now, Go to the dropdown created next to Grade.

Normally I would have the opportunity to filter by letter grade, but because E1 and F1 were merged cells, Excel instead only offers for you to filter by grade %. Not very helpful.

PivotTables

Maybe instead, we can make a PivotTable from the data, and pivot by the letter grade? Go to the Insert tab, and select PivotTable.

What is this? We are receiving an error because we don’t have true column labels (header row) when cells are merged like they are in our top row.. this means that Excel doesn’t know what our categories are to create a PivotTable.

(Shameless plug: come to an Excel: Pivot Tables training if you would like to learn more.)

Formulas

This is probably the biggest one for me. Let’s say I want to count the number of 22 year old students in my class. No problem! Let’s do a CountIf formula.

In I1 I entered =Countif( … then I tried to select my range, column D…. look what happens:

Excel doesn’t want to allow me to include column D alone… it wants to include Column C as well. How annoying! We could probably find our way around this formula issue, but even then, I guarantee these merged cells will get in your way with a future formula.

Macros

It is worth mentioning that there are macros that can be interfered with when you use merged cells; it depends on what type of macro you are building.

All in all, merged cells are just not worth the trouble.

Another Option: Center Across Selection

If you are truly attached to the look of merged cells, there is another option. It is called Center Across Selection.

First, let’s undo the merged cells. Select the merged areas, then go to the Home tab, Alignment group, select the dropdown for Merge and Center, and select Unmerge cells.

2. Select A1 and B1, and Right Click on top of them. Select Format Cells.

3. In the popup screen, go to the Alignment tab, and click on the dropdown next to Horizontal. Select Center Across Selection. Click OK.

4. Repeat this step with C1 and D1 selected, then E1 and F1 selected. Appearance wise, it will look just like merged cells.

This still will be somewhat limiting; for instance, you may still have difficulty with a PivotTable unless you convert this to a Table first, but you will not experience nearly as many drawbacks as merged cells.

A Question for You

Whether you choose to center across or merge cells, I think it is an important question to ask yourself, why are you wanting to do this? Is it truly necessary? When at all possible, I would recommend avoiding either of these practices. I understand the desire to beautify a workbook, but clearly labeled columns with long lines of uninterrupted data are the truly beautiful spreadsheets. Their beauty is in their functionality; and when functionality is lost, nobody will really care much about how the top row looks. Just a thought, from someone who has “unmerged” many cells in many peoples’ spreadsheets over the years.

Thoughts?

What do you think? Has this convinced you to unmerge and never merge again? Either way, I will be here to help 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

Sheree Smith
(First Power User of the decade!)

Outlook: Disabling Automatic Schedule View in Calendar

The Automatic Schedule View feature in Outlook is generally regarded as a gnawing frustration for most Outlook users. It is not a debilitating occurence, but something that requires constant correction. Here is the good news… you can turn it off so it never bothers you again.

Before we jump in, big thank you to Megan for having this question in the last Outlook Advanced session.

How is Schedule View Triggered?

Most people, when viewing multiple calendars at a time, are used to viewing them side by side like this:

But, by default, if you open more than 5 calendars at a time, instead your view automatically changes to something like this:

This horizontal orientation is Schedule View. Not the end of the world… most people just mosey up to the Arrange group in the ribbon, and flip back to Work Week or Week view (depending on their preference).

That being said, some find it a bit jarring when you are browsing through 4 calendars, then open up one more and suddenly the whole view is changed. Sort of interrupts your train of thought…

Who knows, maybe you even like Schedule View (no judgement), but would like to activate it yourself, rather than have it flip on automatically. Here is how you can make that change.

Disabling Automatic Schedule View

Go to the Calendar module.
In the Home tab, Arrange group, click on the arrow in the lower right of the group.
Scroll down to Display Options, and either uncheck “Automatically switch…to Schedule View”, or change the minimum number to automatically switch from 5 to a number you prefer.

Display options, box unchecked next to automatically switch to Schedule View feature

4. Click OK.

Some of this is personal preference. I turned this feature off completely. For me, I would rather change my view by hand rather than have it automatically change when I am in the middle of browsing calendars.

You may decide that you want it to automatically change for you, but just after a larger number of calendars are opened. Either way, the power is now in your hands!

Thoughts?

What do you think, do you think you will make an adjustment to your Schedule View defaults?

Microsoft PowerUp!

Hey are you all signed up and ready for one of my upcoming PowerUp sessions? I hope to see you there! More information can be found at wichita.edu/powerup.

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

Arlene Thomsen

OneNote: History Tab and Restoring Page Versions

In OneDrive Essentials training, we learn all about restoring old versions of documents stored in the cloud. Did you know you have the same ability with your OneNote Notebooks? And the good news is, it is remarkably simple. For that matter, you also have a variety of additional features available to you that involve the history features in your notebooks. Let’s take a look.

A Note on OneNote Applications

There are many types of OneNote Applications in the mix: phone applications, tablet applications, Windows Store applications, Mac applications, and so on… The application I am referring to for this Byte is the OneNote Desktop Application for PC.

This does not mean these history features are not available in other apps, but the Desktop Application is by far the most versatile way to access OneNote notebooks, and is still the way I would recommend accessing your notebooks primarily.

History Tab

Just a heads up that we will be hanging out in the History tab in the desktop PC application of OneNote for all of the features described below.

Restoring Page Versions

One of the most exciting abilities in the History tab is the Page Versions feature. Just like your OneDrive documents, by default OneNote is keeping track of different versions of each page in your notebook.

Go to the History tab, History group and select the dropdown for Page Versions.
Select Page Versions
Notice on the right side, old page versions will appear shaded in grey. Select one to take a closer look.
To restore a page version, or see more options, click on the gold bar that appears at the top of the scren.

There are a couple more options worth mentioning in the dropdown for Page Versions, including the ability to:

Delete all versions in a section, group or notebook
Disable history for this notebook (not recommended).

Recent Edits

Relatedly, you can search by recent edits made to a notebook. This can be especially helpful with notebooks you are sharing with others.

In the History tab, Authors group, select the Recent Edits dropdown.

Recent Edits Dropdown menu

2. Select a time range to view.

3. Search results will appear on the right side of the screen, with edited pages highlighted in yellow.

Find by Author

Here is another great tool to keep you organized if you are sharing your notebook: the ability to search by author.

1. Go to the History tab, Authors group and select Find by Author.

2. On the right, you can refine your search by a specific author.

Thoughts?

I hope you find these History tricks useful for your OneNote notebooks! If you find a handy use for these features, or have any questions, please feel free to drop me a line.

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

Courtney Lockhart

Susan McCoy

Kristen Powell
John Hammer
Trisha Wenrich