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.

Dictate button

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.

Zoom Tool

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.

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

Collapse magnifier settings

Display Settings

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

Windows key search for display setting

…or the keyboard shortcut is Windows Key + U.Display screen options from numbered list belowWithin this screen is the ability to:

  1. Make all text bigger
  2. Make the entire screen larger by default
  3. Change the size of Apps and text on other displays
  4. 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:

  1. Press the Windows Key and go to Settings
  2. Select Ease of Access
  3. Select Narrator

Narrator in sidebar

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

Quick Access ToolbarYou 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…

Right click menu when right clicking on object in the ribbon: Add to quick access toolbar circled… Or press the dropdown arrow on the right of the QAT, and select More Commands to access a full list.

Dropdown menu, More commands selectedMore 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.

Dictate button

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…

Real aloud button

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

Excel QAT screen

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.

Alt Menu

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

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.

Menus as described below

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.Search Tools Advanced Find

3. Go to the Advanced Tab in the popup.

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

Menus as described below

5. For Condition, select On or Before.

Condition with on or before selected

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

7. Select Add to List and Find Now.

Screen as described in instructions

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

9. Right click, and Delete.

right click menu with delete selected

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 + ;

Emoji Popup

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

Donut Filtered to show percent remaining

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…

Pie Chart PivotChart

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.

As described above, showing 100%

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!

Donut Filtered to show percent remaining

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.

Starting List, animal names and random numbers

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.

Format as Table circled in ribbon

2. Pick a design if you would like (in this case, yellow for WSU).Original data, formatted as a table

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

Helper Column with formula for percent as described

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

  1. Click on the table to activate Table Design tab.
  2. On Table Design tab, Select Summarize with PivotTable.Table design tab, summarize with pivot table circled
  3. Location of Pivot Table: on a new sheet, titled Pivot.
  4. Build the table with Item as rows, Helper Column as Values.Pivot Table5. 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.

Helper cell with GetPivotData formula

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.Formula in a different helper cell as described

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

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

Right click menu on chert

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.

Header with formula directing bavk to helper cell

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.

Donut Filtered to show percent remaining

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

Robin Mishler

Megan Nold (not pictured)

Kylie Sharpe

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.

Excel screen with merged cells

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.

Excel screen with merged cells

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.

Sort and Filter2. Now, Go to the dropdown created next to Grade.

Sort and filter by 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.

Pivot Table errors

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:

Formula attempting to select full column

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.

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

Unmerge Cells

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

right click, format cells screen

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

Alignment tab, center across

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

Center across looks the same as merge and center

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

Sheree Smith

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.

More arrow in arrange group

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:

Regular calendar view

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

Schedule view

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

Arrange group, Schedule View selected

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

  1. Go to the Calendar module.
  2. In the Home tab, Arrange group, click on the arrow in the lower right of the group.Arrange group, more arrow
  3. 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.

Powerup Session poster. visit 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

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.History Tab

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.

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

Restore bar

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

Page Versions Dropdown Menu

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.

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

History Search

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.

Find by Author button2. On the right, you can refine your search by a specific author.

Search results by 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

Courntey Lockhart

  • Susan McCoy

Susan McCoy

  • Kristen Powell
  • John Hammer
  • Trisha Wenrich

Microsoft Office 365 PowerUp! Sessions

New Session for WSU Faculty and Staff

Microsoft Office 365 at WSU will feature biannual updates, so you will start seeing new buttons and features appear in your ribbon periodically (perhaps you have noticed a few already). So, we are testing out something new…

Microsoft PowerUp!

  • The sessions will be called Microsoft PowerUp!
  • PowerUp is intended to showcase new features in 365, and will contain new information not covered in previous Microsoft Office sessions.
  • The sessions will be quick. These are demos, not full training sessions, so you can plan for about an hour.
  • Two January sessions will be offered: January 8th and 15th.  
  • Sessions are listed in myTraining, so sign up now and we will save you a spot and a free guide!

 

Microsoft Office: Color Themes and Custom Color Palettes

Working with color themes in Microsoft Office can open up a whole world of possibilities for customizing your documents. Most people don’t realize how much they are already interacting with themes in Office, or how much control they can have with just a couple clicks. Let’s check it out. But first, a special thank you to Sheree for having some excellent color palette questions in an Excel Essentials session a couple weeks ago and inspiring this Byte.

Exercise File

You can follow along on one of your existing documents, or if you would like a starting place, here is a Word document you can start with:

Color Palette Exercise

This is a Word document with a few visual elements that incorporate theme colors. These are all things that will be affected by altering the color theme.

Themes and Color Palettes

What does it mean to apply a color theme? In most of your Office programs, you are already using a color theme, whether you realize it or not. The default is the Office color theme. You see your theme colors in everything from the color options for your fonts, to your default headers, to tables and charts… and more.

In the test file you downloaded, you are seeing it in the headers, the chart, the icons and the table…

One of the easiest ways to check your current palette is to visit the Home tab, Font group, and click on the dropdwon arrow next to the Font Color.

Notice how there are Theme colors, and Standard Colors. The Theme colors display your current color palette. There are gradations underneath each main theme color…. lighter and darker versions of each of the theme colors to create contrast.

Change the Color Theme

Changing your color theme is simple!

1. Go to the Design tab, Document Formatting group. (By the way, this is also where you can go to change your default font settings for the document).

2. Select the Colors dropdown to see a full list of themes. Hover your mouse over each color palette to see a preview.

3. Select a color theme by clicking on it.

Because theme colors are utilized in so many ways in this document, we really see a change in appearance!

Custom Color Palettes

If you are artistically inclined, you might be interested in creating your own custom color themes.

1. In the Design tab, Document Formatting group, click on the Colors dropdown again, but instead of selecting a predefined color set, select Customize Colors…

2. Click on the dropdown beside any of the accent colors to make an alteration. Typically Accent 1 is the color you will see the most in a document.

3. You have the ability to select colors from a color wheel, or select More Colors to enter an exact RGB color.

4. When you are finished, name the color palette and click Save.

5. The new color set will appear in a new section at the top of the Colors dropdown called Custom.

You will be able to access this color palette anytime you create a new document on this computer.

Accessing Custom Color Themes in other Microsoft Programs

Once you have created a color theme that you like, you may want to access it in other programs. Microsoft knows this, so has made them accessible to you in many of your Office programs. They are in slightly different locations though, so let’s take a look.

PowerPoint

In PowerPoint, you often see color themes even more prominently than in Word. Here is one popular theme called Berlin, that features a red and yellow color set.

1. In PowerPoint, visit the Design tab, Variants group.

2. Select the dropdown arrow in the lower right:

3. Here is where you will see your color theme options, plus the new custom color you just created in Word. Neat!

This definitely changes the look of this document…

Excel

In Excel, Color themes affect features like tables, charts, shapes, and fonts. The default in Excel is the Office color set as well. These colors should look familiar:

1. In Excel, visit the Page Layout tab, Themes group.

2. Select the Colors dropdown.

3. Here is your custom color theme again… no need to reinvent the wheel.

Outlook

Outlook? Did you read that right? Yep! Outlook also has the same themes and color sets you know and love from your other Office programs.

1. Open up a new email.

2. Visit the Options tab, Themes group.

3. Click on the Colors dropdown, and there you are…

Thoughts?

What do you think? Do you think you will utilize custom color themes in your Office documents? By the way, if you create a cool WSU themed color theme, I would love it if you would share it with me!

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

Madelyne Toney

Linda Claypool

Susan Johnson

Karen Wilson

Jaime Scherer

Jessica Casper

Cara Tucker

Linda Young (not pictured)

Outlook: Special View Settings with Conditional Formatting

Are you lost in a sea of emails? It might be useful to make certain emails stand out with a special color or formatting. Perhaps a fillable form that comes to your inbox with a specific subject could be green, or all emails from your boss could show up as red. Customizing your inbox with … Continue reading “Outlook: Special View Settings with Conditional Formatting”

Are you lost in a sea of emails? It might be useful to make certain emails stand out with a special color or formatting. Perhaps a fillable form that comes to your inbox with a specific subject could be green, or all emails from your boss could show up as red. Customizing your inbox with conditional formatting can be a huge time saving tool to help draw attention to certain emails in your inbox at glance. Let’s take a look. Before we get started, I would like to thank Michelle for having an excellent question in last week’s Outlook Advanced class that inspired this Byte.

View tab, view settings

About Conditional Formatting

Just like we experienced in Excel, conditional formatting in Outlook will look for certain conditions and apply a specific appearance to them. Here are a few caveats before we jump in:

  • This feature is currently only available for the PC desktop application of Outlook (apologies to Mac users).
  • Conditional Formatting applies to one folder at a time. This means that you can create separate rules for each folder. It also means that you should be cautious of which folder is selected before jumping into the view setting.
  • Changes you make with this setting are at your computer application level only: it is a view setting within the Outlook desktop application. This means that other instances of Outlook (e.g. the online application, or the desktop application running on an additional computer) on will not adopt these view settings.
  • If you would like to learn more about Conditional Formatting, please attend one of my Outlook Advanced sessions (listed in myTraining).

Create a Conditional Formatting Rule

1. Start by selecting the folder where you would like to apply the rule. This may just be your Inbox if you are not a big fan of folders.

2. Go to the View tab, Current View group, and select View Settings.

View tab, View settings

3. Select Conditional Formatting.

Advanced view settings screen, conditional formatting button

Here are all your current Conditional Formatting rules. That’s right, you already have some rules… like the way an unread message uses blue font, for instance… I would not recommend changing existing default rules.

3. On the right select Add.

Conditional formatting box, add button circled.

4. At the bottom of the popup, you will be prompted to name your rule. Use a name that is relevant to you.

Properties with a name created: "amazing title"

From here, there are two settings we have to create: Font and Condition. Just like with Excel, it is easy to get carried away with creating one setting and forget to do the other…

Font Settings

Let’s set the appearance first.

1. To the left of your title, select Font.

font button in conditional formatting popup

2. A popup screen appears with various font options along the top: font type, style and size…

Font screen, font styles, sizes, color visible

… and of course the funnest part, in the lower left, Color!

Color dropdown menu

3. Make your font and color selections, then press OK once.

Condition Settings

This second part is easy to forget. Sometimes we spend a lot of time crafting the perfect appearance, and then forget to tell Outlook what the Condition is where the appearance should be applied.

1. Below where you selected Font, select Condition.

Condition button, conditional formatting screen

Take a look at your options… you have the ability to format based on:

  • Specific words in a subject field
  • Emails from or to specific people
  • Situations where you are the only person in the To: line
  • … and we will see some more examples shortly.
Filter screen, condition optiosn including items mentioned in bullet points above.

2. For this scenario, I am going to set a condition based on emails From a specific person. Click on the From button to browse the directory. Double click on the person’s name, then press OK.

  • This option works best if you browse for a name, rather than typing in the full email.
Global address list, name search for Ali Levine. OK button circled.

3. Let’s take a look at the More Choices tab, because things get even more interesting here. Not only are more options present here, but we could layer these on top of the previous selection… here are conditions based on:

  • Category
  • Only unread or read items
  • Items with attachments
  • Importance level
  • Items that are flagged
Filter options in conditional formatting, more choices visible, as described in bullets above.

So think about this, you could set your formatting to color code emails from a specific person that are unread; or emails that have specific words in the title that also have attachments. So many possibilities!

4. Once you have this set how you would like, press OK. Then OK again to get back to your Outlook folder.

Conditional formatting screen, OK button circled

If all went well, you should be able to see the effects of your lovely new rule. I picked purple for mine…

Inbox shows email from Ali Levine with purple font

Thoughts?

What do you think about Conditional Formatting in Outlook? Do you think you will apply some of these rules to your Outlook folders? 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

Samantha Dolan

Samantha Dolan

Excel: Power Query: A Simple Introduction

We all occasionally find ourselves navigating the rough terrain of ugly data. Power Query is an excellent tool to have in your back pocket for this eventuality. But here is the deal, Power Query is just that… powerful, and because of this, potentially complicated. In fact, I know a lot of people who have been interested in learning about this beast, only to get quickly overwhelmed in the complexities. So here is my aim with this Byte: a simple introduction to Power Query. Let’s check it out.

Power Query editor screen

What is Power Query?

Power Query is a feature in the PC version of Excel 2016 or later, also known as Get & Transform. There are a multitude of uses for this feature, but it really shines to take on ugly data like this:

Ugly data: lots of information crowded into one column

… and transform it into something more workable.

data neatly formatted as a table

Furthermore, since the result is a query that is connected to your data, it can be updated with a simple click of a button.

Exercise File

If you would like to follow along with my steps below, here is an Exercise File:

A Couple Notes:

  • You will need the PC desktop version of Excel 2016, 365, or 2019 to use this feature.
  • For this Byte I am assuming you have worked with Tables and Pivot Tables in Excel before. If you haven’t, please come to my Excel Essentials and Excel Pivot Tables Sessions and learn about them!
  • Needless to say, this is all fictional data.

The Scenario

The scenario is we have some ugly data that we have exported from a different source. The source has thrown lots of data into one column of a spreadsheet. Monthly, we are asked to create a pivot table of fees owed by class and area code, but this will be rough going, given how the data looks:

Unformatted data

Format as a Table

I have mentioned before that formatting your data as a table has many advantages, and here is another excellent example. There are many data sources you can use for Power Query, but let’s start here for now.

1. Click on any cell inside the data. Do not preselect the entire column.

2. In the ribbon, select Format as Table, and select any style.

Format as table dropdown selected

3. Make sure that all the data is encompassed in your range, make sure My table has headers is checked, and press OK.

Create Table dialogue box, my table has headers selected.

More details about tables are discussed in the Excel Essentials training, so check out one of those sessions if you have not already.

Same data, formatted as a table.

Create a Query

1. Go to the Data tab, and in the Get & Transform Data group, select From Table/Range.

(note: you can also access this from the Get Data dropdown if it is not readily visible)

Data table, Get and transform data group, from table/ range.

2. You will be taken to the Power Query Editor.

Power Query screen

This is a little like an alien abduction from your comfortable Excel home… the look and feel are kind of the same as the rest of Excel, but also kind of different. Power Query uses a different type of code than the rest of Excel, so some things will not be intuitive. I don’t want to get too far in the weeds, but for now note that:

  • There is no undo, but you can always delete a step on the right,under Applied Steps, where coincidentally, every step of the query will appear forevermore.
  • There is a ribbon with Transform and Add Column option. Although options will look similar on both tabs, items on the Transform tab will change an existing column, and items on the Add Column tabwill create a new one
  • A lot of options are also accessible on a right click menu.

Transform the Data

In the query, there is only one column with a lot of ugly data, but I notice most of the data is separated by a colon “:”. Let’s begin by splitting up the columns by this delimiter.

1. Click at the top of the column to select all of the data in the column.

2. In the ribbon, or on a right click menu, select Split Column –> By Delimiter.

Split column, by delimiter circled

3. Make sure Colon is selected, and Each ocurrence of the delimiter is selected. Press OK.

Split column by colon, at each occurrence of the delimiter selected.

Your data should split into separate columns at each occurrence of a colon.

Same data split into separate columns in Power Query screen

4. Let’s work on splitting the phone number. Select the phone number column, and Split Column –> By Number of Characters.

Split column, bu number of characters

5. We want to split after 3 characters, Once, as far left as possible. Click OK.

Split at 3 characters, once, as far left as possible.

Cool, now we have a separate column for the area code!

6. At the top of each column, double click on the title and give each column a name: Name, Area, Phone, Grade, Class, Fees.

Titles typed for name, area, phone

Load the Transformed Data

1. You have the option to name your query on the right side of the screen, under Query Settings –> Properties. If you will be doing multiple queries, this might not be a bad idea.

Query Properties, Name of Students entered.

2. After this, the final step is to press the Close & Load button in the Home tab.

Close & Load circled in home tab.

Like magic! Our data loaded to a new tab in a new table.

Old data organized into a table.

Good News Part One: Data is Easier to Work With

Okay, this data is going to be so much easier to work with. Remember, my original goal was to make a pivot table showing fees owed by area code. Now that this data is in a table, in a few clicks, I have exactly the information I need. Beautiful!

Pivot table by area code and class, calculating fees.

Good News Part Two: Updating Data

We could have fixed up that data in a variety of other ways in Excel (hello, Flash Fill!), but the especially cool part about Power Query is the ability to refresh data in the future with one click. Remember I mentioned this was a monthly report I had to create… so here is what I would do next month… rather than recreate all the steps to make the data readable again:

1. Load the new data into the source table.

2. Right click on the query table and select Refresh

All the query steps we did in the previous section will happen automatically with my new data.

Right click menu, Refresh circled.

More Types of Queries

This example involved a query from a table within a document, but you can query tons of different sources: Excel documents in seperate locations, Access databases, Azure databases, online sources… seriously, check out all the options on the Data tab, Get Data dropdown.

Get data dropdown with location options

Interested in a Power Query Session?

This was a very basic introduction to Power Query. There is a lot more to cover about this amazing feature. WSU friends, I would like to poll the audience here… I think it would be fun to offer a Power Query Session to delve more deeply into its various abilities, but would like to hear from you all if this is something that would interest you. If you are interested, please send me an email!

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

Christina Covey

Christina Covey

Brandon Whiteside