Excel: Timeline Slicer

Slicers are hugely popular in both Excel Essentials and Excel Pivot Tables training. We go for standard slicers in both of those sessions, but there is another type that is particularly fun to play with if you are working with dates; the Timeline Slicer.

Exercise File

Download today’s Exercise File to follow along:

Timeline Slicer ExerciseDownload

This is… what else… some dessert sales! Desserts make their way into a lot of my training exercises (wonder why?).

The Dessert Sales sheet shows entries in checkbook style: date, dessert type, and then income and expense on each day.

The Dessert Pivot sheet is referencing data on the Dessert Sales sheet. You must have a pivot table with dates in order to use the Timeline Slicer tool. If you are not comfortable with Pivot Tables, check out my session Excel: Pivot Tables, Charts and Pictures, listed in MyTraining.

Create the Slicer

Go to the Dessert Pivot sheet
Click on the pivot table to activate Pivot Table Tools contextual tabs
Click on the Pivot Table Tools Analyze tab
In the Filter group select Insert Timeline
Select Date and press OK

Look at this nifty Timeline slicer you just created!

Working with the Timeline Slicer

Just like your standard slicers, you can click on a month to narrow down information in the pivot table.

Month Selected, Pivot table showing only one month

Notice you also have the ability to select a range of months by hovering your mouse between two months and clicking and dragging.

Cursor between two months, ready to click and drag. double headed arrow visible

To clear the filter, click on the clear filter button at the upper right of the timeline slicer.

Timeline Tools

Click on the timeline to select it and notice this activates a Timeline Tools contextual tab.

Among other options, you have the ability to adjust the size of the Timeline and change the color to another available theme color.

Troubleshooting

Oh no, I sliced by a month and then accidentally deleted my slicer! How do I unslice my data?

Don’t forget that whenever you create a slicer, you are creating a shortcut to filter. Notice how a filter appears at the top of the row labels.

To clear this filter click on the filter button and select Clear Filter from “Months”.

Clear filter option in pivot filter button dropdown

Thoughts?

What do you think? How will you use the Timeline tool with your workbooks?

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

Glenn Gunnels

Jeremy Webster

Microsoft Office: Customizing the Ribbon

In every Micrsoft Essentials training, we talk about customizing the Quick Access Toolbar (QAT). In a similar vein, did you know you can customize your Microsoft Ribbon as well? Indeed, you can create your own custom groups with those hard-to-find favorite features. This is just another way to save yourself a few clicks throughout the day… which we all know can add up to a lot of time. Let’s take a look.

Exercise File

There is no exercise file today, because you will be customizing the ribbon on your individual download of Microsoft Office. If you would like to follow along, open up a blank Word document.

Layout of the Ribbon

The Ribbon is made up of:

Tabs: e.g. Home, Insert, Design
Groups: printed at the bottom of each tab, e.g. Clipboard, Font, Paragraph
Commands: the buttons/features within each group

Throughout all our sessions, have also talked about how we see specific Contextual Tabs, or Tool Tabs, appear as we access certain features (pictures, tables, etc.). All of these can be customized.

How to Customize the Ribbon

Let’s say that you have been doing a lot of work in Word. You would like to make it easier to find Alt Text when inserting images. You also frequently find yourself adjusting Headers, Footers, and Page Setup options, and would like to save yourself a few clicks in finding these features.

Create a Group

Right click in a grey space on top of the ribbon and select Customize the Ribbon.
- Notice this looks similar to the QAT customization screen, but this time Customize Ribbon is highlighted.

Highlight the Home tab on the right and press New Group at the lower right of the screen.
A new group will appear in the home tab list. Select it and press Rename to name it something else. I am going to name mine Special.

Add Commands

Let’s add a few commands to the Special group.

Select the Special group on the right pane.
On the Left Pane, change the dropdown from Popular Commands to All Commands.
Scroll down to Edit Footer. Select it, press the Add button between the two panes.

Do the same for Edit Header, and Page Setup

Once you click OK, you will see a new group with your custom commands.

Customizing a Contextual/Tool Tab

We added the previous commands to the Home tab in Word. We also would like to add Alt Text to the Picture Tools contextual tab. Contextual/Tool tabs are tabs that we don’t see until we select a specific object (like a picture) the document.

Note: if you are using Office 365, Alt text will already appear on the Picture Tools tab by default.

To customize the Contextual/Tool tabs:

Right click on the ribbon and select Customize the Ribbon
On the right pane, change the dropdown from Main Tabs to Tool Tabs.
Find the Picture Tools tab and select the only group (Format).
Follow the instructions above to add Alt Text to your picture tools tab.

Remove Commands

You will not be able to remove the default commands from the ribbon, but if you would like to remove commands that you have added yourself, you can do so easily:

Right click on the ribbon and select Customize the Ribbon.
Highlight the command or group (in our case, Special) on the right pane
Press the Remove button between the two panes

Ideas

Remember, this works in all your Microsoft Office programs, not just our Word examples above. I am sure there are special features you wish you could access more easily, so I can’t wait to hear what you decide to do! A few I have added to mine are:

Outlook: Journal Feature to the Home tab (and QAT, because I am extra).
All Programs: Alt Text to the Picture Tools tab
- As I mentioned earlier, in Microsoft 365, Alt text will appear on the picture tools tab by default, but if you are on 2016 or earlier, it can be a huge time saver to add it yourself.
Excel: Set Print Area to the Home Tab

Thoughts?

How will you customize the ribbon on your Office programs? I would love to hear which commands you decide to add!

Congratulations, Power Users!

Congratulations to our Power Users! For a full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser

Excel: Forecasting with Goal Seek

Excel has several built-in forecasting features that can be excellent tools for planning and strategizing. The feature we are going to examine today is called Goal Seek. Goal Seek allows you to work with related figures and determine how much one value would need to change in order for another to meet a goal.

In other words, if you know what number you would like to attain in one cell, but don’t know what input value is needed in a related cell to reach that number, this is the tool for you. Let’s take a look.

Exercise File

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

Scenario

What are we looking at here? These are recruitment numbers for some fictional colleges on campus. FY19 enrollment headcount is in column B, and you would like to calculate goal enrollment for FY20 in column C.

Column C contains formulas that will calculate an increase in percent based on what you input into cell F1. Go ahead and test it out. What happens To column C if you type 5 into F1?

So an increase of 5% would mean all enrollment figures would increase as shown.

Goal Seek

That formula is nice, but your real hope is to Goal Seek. You would like to figure out how large of a percent increase would be required to raise total enrollment (C6) to 3000.

Select Cell C6 by clicking on it.
Go to the Data tab, Forecast group, and select What If Analysis.
Select Goal Seek
In the pop screen that appears:
- Set Cell should already read C6, since that was the selected cell.
- To Value: this is our enrollment goal, which was 3000, so change that to 3000.
- By Changing Cell: select cell F1 (next to Increase Enrollment By) or type F1 in the space provided.
- Basically, we are saying we want to see what the percent increase would need to be for our goal of 3000 students to be reached.
Press OK.
Excel should work out our calculation from here…

Interesting! It looks like enrollment will need to increase by 24%. And each college’s appropriate rate is lined out with their needed increases.

One quick note, all these cells are rounding to the next whole number, so figures are not exact (we can’t recruit a percent of a student after all).

In the pop up that appears either press OK to accept the change, or Cancel to go back to the original numbers.

Thoughts?

Where could you put this to use in your office? Let me know what 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

PowerPoint: Slide Layout and Accessibility

PowerPoint has a lot of aesthetic features, so it can be easy to become so focused on the visual aspects of a presentation that we forget about what is happening behind the scenes. A slide deck can appear perfect from a visual perspective, but definitely still have a long way to go internally. Let’s take a look.

Exercise File

Download today’s exercise file here: LayoutExercise

This is a (very important!) presentation about Star Trek characters. It consists of a title slide and three additional slides with content. At first glance, It looks like all the content slides are laid out identically, but they are actually quite different.

Selection Pane: Reading Order

Similar to Accessibility settings that we talk about in Acrobat DC Essentials training, there is also a Reading Order to PowerPoint documents.

Someone in your audience may be visually impaired and accessing your PowerPoint with a screen reader. The Reading Order is the order the screen reader will read the contents of the slide. How do you access the reading order? Via the Selection Pane.

Select Slide 2: The Original Series.
In the Home tab, Drawing group, select the Arrange dropdown and select Selection Pane

A menu will appear on the right. This is the Selection Pane displaying the Reading Order.

Important: reading order is from bottom to top! So in the case of Slide 2, the Title is on the bottom, where it should be if it is to be read first.

Click on any of the items in the Selection Pane to highlight their location on the slide. Or, click on any item in the slide to see it highlighted in the Selection Pane. Items may be reordered by clicking and dragging.

Outline View

I mentioned that all these slides appear identical, but they are actually set up differently. One quick way to see this is to change your View to Outline View.

Go to the View tab, Presentation Views group, and select Outline View.

Notice how slides 3 and 4 are missing something very important: a title! Titles are indicated in bold next to the slide number.

But wait, what is going on here, I was sure I saw titles on those two slides?

Let’s return to Normal view and investigate. Go to View tab, Presentation Views group, and select Normal.

Layout

Select Slide 3: The Next Generation.
Take a look at your Selection Pane on the right (if you left that view from earlier, you can get it back by following the steps under Selection Pane above). Where is the Title?
Click on to the title words in the slide (The Next Generation). Notice this highlights a Text Box in the Selection Pane on the right. It looks like someone has deleted the title box on this slide and inserted a text box. This means that:

The reading order is out of order… it could be reordered by clicking and dragging, but…
There is also no official Title on this slide. Fixing the lack of title will actually fix both issues.

Let’s first confirm the correct Layout is selected. Go to Home tab, Slides group and select the Layout. It looks like Two Content is selected. Since there is both text and a picture, this makes sense. Let’s not change this, but it is good that we confirmed it.

The real issue is that the Title got deleted and replaced with a text box. Let’s fix this. Start by deleting the imposter title: click on the text box that contains “The Next Generation,” then click on the line of the text box to select the whole text box. Press the Delete key to completely remove this imposter title.
Go back to the Slides group and select Reset. This is going to reset the current slide into its original two content layout, while keeping the content.

A true title box appears. Retype your title into the new title box. Notice the selection pane is now correct, in that there is a title, and that the reading order has been corrected.

Change Layout

What about Slide 4? Let’s follow the same steps as above.

Under Layout, I noticed the wrong layout is selected. It is currently a Title and Content and should be a Two Content. Let’s change this slide to a Two Content.

Notice this causes the slide to reset. The fake title text box may need to be deleted to make room for the real title.

Side note: you might be noticing right about now that remediation is a lot harder than creating a slide correctly to begin with. This is almost always the case, regardless of the program you are using!

Once slides 3 and 4 are corrected, revisit Outline view, and notice the new look! Titles on every slide, what a beautiful sight!

Accessibility Checker

Another way to find these layout issues is by running an Accessibility Checker. A full write up of how to work with this feature may be found here: Accessibility Checker Article

Go to File, Check for Issues, Check Accessibility.

When a number of text boxes are found on a slide, the checker will remind you to check your reading order. Click on the dropdown next to this warning to access the same Selection Pane a different way.

Thoughts?

Did you know about these tricks for checking slide layout and reading order? How will you put this to use?

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

Tegan Perry

Word: Solutions for Common Issues with Numbered Lists

Automated numbered lists are a feature a lot of us take for granted in Microsoft Word…. They usually work seamlessly and automatically, but sometimes these lists can work against us: restarting a list of numbers at 1 when we mean to continue our list, or picking up formatting that we can’t seem to shake, like creating all bold numbers in spite of unbold text. Don’t despair; these little inconveniences are remarkably simple to fix.

Exercise File

To follow along, you may download the exercise file: NumberedLists

This file contains a fascinating list of silly words laid out in series of numbers lists. Let’s take a look at our options.

Continuing or Restarting Numbered lists

Look at the second section of words, starting with “Brouhaha.” It is clear that this list should not be starting over; it should be a continuation of the previous list. This is a simple fix.

Right click on top of the number 1 next to the word Brouhaha.
Select Continue Numbering

This will pick up the value from the previous numbered list. What if the opposite happens? Word guesses that you would like to continue numbering, but you actually intend to start over? Easy peasy! Follow the same process, but this time select Restart at 1.

Additionally, occasionally when you insert a numbered list, you will see a lightening bolt appear with a dropdown arrow. This is just another way to access the same feature, a shortcut inserted by Word that will allow you to make the decision whether to continue numbering or restart at 1.

Change Number Values

There is also an option in this menu to Set Numbering Value. This option is for those times when you need a special number, perhaps one that is out of sequence with the rest of your numbered list.

Formatting Numbered Lists

Sometimes you create a lovely numbered list, and for whatever reason, Word picks up on formatting from a previous line of text, making all the numbers bold, or a previously used color.

On the exercise document, look at the third section. Someone used a blue bold font for the text above the numbered list, and Word assumed that this should apply to the numbers on the list. To fix this, let’s take a closer look at that right click menu.

Right click on top of the first blue number, next to “Taradiddle”
Attached to the numbering options in a separate section is the ability to change the formatting: to remove the bold formatting and recolor the text.
- Note: sometimes these options appear above the numbering options, sometimes they are below.

Sub Points

Creating Sub Points

In the last section, numbers 4 and 5 should be sub points of number 3. To demote them to sub point click to the left of “Our Friends,” and hit tab on your keyboard. Do the same for “Our Neighbors.”

This has created sub points, and Word assumed that you would like to indicate sub points with lowercase alphabet:

If the alphabet isn’t your goal, you can always click into the text in the line of a or b, go the numbered list dropdown in the Paragraph group of the Home tab, and select a different format. Maybe Roman numerals?

Promoting Sub Points

By the way, how do you change your mind and promote a sub point back to being a main point? Well, you could use the Decrease Indent (left arrow) in the Paragraph group…

But you all know I am a fan of shortcuts, and my favorite one for this purpose is shift + tab.

Thoughts?

Have numbered lists caused you trouble in the past? Will any of these tips help you going forward? 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