Excel: Magic Macros

In honor of the wizardry of technology, and because everyone should be having a blast during summer break, I made a fun exercise for us in Excel… a crystal ball… via the magic of Excel Macros.

To give credit where credit is due, this was inspired by an exercise Mr. Excel showed us at Excelapalooza last fall.

Exercise File

Here it is… ask it anything! Crystal Ball Document

Allow Macros

This magic involves macros, so if you want to use this crystal ball, you might be prompted to “allow macros” or “Enable Content.” If so, go ahead and allow them. And more on this later…

How to Use it

Alright, this is about a sophisticated as a Magic 8 ball, which is to say, you will think of a yes or no question, then press the “Ask Me Anything” button on the Crystal ball.

Crystal ball, with arrow pointing to Ask Me Anything button

Your answer will appear in cell B2, next to the “Your Answer” writing. Keep pressing the button for different answers (there are some good ones in there).

Look Behind the Curtain

How did I do this? Let’s take a peek behind the curtain.

Unprotect Workbook

This is a protected workbook. Go to the Review tab and select Protect Workbook to toggle off the protection. I did not apply a password. This will allow you to discover a sheet I have sneakily hidden.

Unhide Sheet

Right click on the tab of the sheet (where you see the sheet name) and select Unhide.

Hey there is a hidden sheet in this workbook! This looks a lot like the results we have been seeing when we ask the crystal ball for answers. But where do these results come in to play?

Formula in B2

Go back to the Crystal Ball sheet and click on cell B2, where the results appear. Look up in the formula bar… it looks like there is a pretty nifty nested formula that is selecting a random number between 1 and 14 and matching it up to possible results.

If you want to learn more about nested formulas, come to one of my Excel Advanced Formulas sessions! Those of you who have attended before, I could have also used VLOOKUP on this cell, but for some reason I was feeling old fashioned and went with the old Match/Index one-two punch.

Macro

Okay, the hidden sheet makes sense, and the formula is logical, but what does this button have to do with anything? How exactly does this work? The answer is… Macros!

What are Macros?

Macros are basically a shortcut you create that will perform a series of commands. If you get tired of copy/pasting as values in Excel, you could make a macro button that would do that for you. I once talked to someone who consistently wanted to print only the first page of a document, so we made a macro that would do just that.This saved her a lot of clicks throughout the day.

Basically, any combination of commands, formulas, you name it, you can create a macro for it. Macros use a special code called VBA (Visual Basic for Applications). If you want to take a look at the super simple macro behind this button, press Alt + F11 on your keyboard.

Now, don’t let this scare you off, macros can also be recorded via a macro recording tool in Excel. You don’t have to learn VBA to create macros, though it can really help.

I Thought Macros Were Something Bad…

I hear this a lot. Sometimes you open a document and receive a stern warning about macros… you may have even received a message like this when you downloaded this exercise. Because macros carry coding for actions, they can hide malicious code from ne’er-do-wells. Think of it this way, macros are a tool, and any tool can be used for good or bad purposes. So if you download something from a source you are unsure of, and you want to be extra careful, don’t allow macros to run, though know this will possibly reduce functionality. But if you made the macros yourself, or it is sent to you from a trusted friend (like me), you are probably fine.

Are You Interested in a Macros Session?

This Byte was a super fly-by narrative about macros. A couple of you have told me you are interested in learning more about macros… if you would like to attend a session like this, let me know! If there is a lot of interest, maybe I can put something together. You can either comment on this post or send me an email. I always love to hear from you anyway.

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

Excel: 3D Maps and Geographic Data

I have been including this as a bonus exercise in our Pivot Table sessions, but it is too good not to share as a Byte. I learned about this magical feature (wizardry) from a session taught by Mr. Excel last fall. This is the beginning of the cool AI features to come with Microsoft Office… it is an amazing tool to help you better illustrate location oriented data, or better yet, location data on a timeline. Let’s check it out.

Availability

This feature is available to those of you using the Windows version of Microsoft Office 2016 or later. WSU faculty and staff, if you have an older version of Office and would like to upgrade, please contact the Help Desk at 4357. If you are a Mac user and want to try out this feature, please come to one of our open labs listed in myTraining! We would love to have you, and you will be able to export the data we create for use in presentations.

Exercise File

Follow along with the exercise file: GeographicInfo

This is a list of fictional student registrations: student hometowns, class, and the date they registered.

Data Layout

Notice how this list is laid out vertically: each column is a new piece of information and each new row is a separate entry. This is exactly how data should be laid out in Excel: for pivot tables, for graphing, and also for geographic data.

Oh, and remember… friends don’t let friends merge cells. It can really mess with features that create visual elements with data… But I digress.

Creating the Map

Just like with your pivot table data, you do not need to preselect all this information.

Place your cursor anywhere inside the data. I selected cell B5 randomly.
Go to the Insert tab, and in a group called Tours select 3D Map.

Note: if this is your first time using this feature, you may be asked if you want to enable this feature. You do. This is going to give Excel permission to access these maps online.

Another Note: it may take a few moments for the feature to run… so don’t be alarmed if you see the famous spinning wheel.

A new screen will load with a map view. You can move the globe around with your mouse, or zoom in with the + and – buttons at the lower right.

Adjusting the Map

We want to see more information than just data points, though. Notice that the right side of the screen has an almost pivot table feel: with different groups of customizable information. Let’s insert a couple fields:

Press the + next to Category and select Class
Press the + next to Time and select Date

Now rotate your globe, and notice that there is dimension to the entry points…

but that is not all you have just done…

Notice there is a play button at the bottom of your screen. Press this button to see a moving timeline of registrations.

Isn’t this awesome??

Export Options

Take a look at the ribbon in the Map view. Notice the second option allows you to Create Video or Capture Screen. Maybe you would like to incorporate this live timeline into a PowerPoint, or maybe you just need a quick picture of the map. These options will allow you to do just that.

Saving Your Maps

This file is going to save with your excel file once you exit the map view. To access it again after closing out of map view, revisit the Insert tab, and select 3D Maps. Instead of starting over, this also gives you the option to revisit the “tour” you created. You might choose to create several!

Thoughts?

I would love to hear how you end up using 3D maps in your work space! Let me know. 😊

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

Jose Intriago Suarez

Microsoft Office: Creating and Using Templates

Microsoft templates are a mystery to a lot of people. Maybe you have found a way to save a file as a template. You noticed a new type of extension appear at the end of the file… but for the most part you see it behaving the same as a regular office file. So you wonder, what is the big deal with templates? I have to let you in on a secret. The usefulness of template files is all about where they are saved. And… they can actually be a timesaving lifesaver in a pinch, especially for repetitive work. Let’s explore how templates work in MS Office.

Exercise Files

This process will work the same in Excel, Word and PowerPoint; here are some sample templates to try out for each program:

p.s. This awesome PowerPoint template is available through the Strategic Communications Templates page. I hear that more will be coming soon, so stay tuned!

Open the File

Let’s open one of the files, the Word Template. Let’s say this is a file you had spent a lot of time creating; from selecting a theme and color palette, to tweaking the Styles to creating a dynamic Table of Contents. (More on those options for a future Byte).

Save As

Go to File and select Save As (or a lot of you know that my favorite shortcut for a quick Save As screen is F12). Location doesn’t matter, because that is about to change when we select type.
In the Save as Type dropdown menu, select Word Template.
Notice this changes the default location of your save to Custom Office Templates Folder.This is important: do not change this save location! This is where Word will look for your template.
Press Save.

Access Your Personal Templates

Let’s test out your new template!

Go to File and select New. You are creating an entirely new Word document, just like you would in the future when starting from scratch.
At the top of the templates, notice you now have a Personal tab. Press this button.
You should see your new template, the Office Bytes Template. Select this to apply the template to your new Word document.

Notice how this brings in not only the content, but all the themes, colors, fonts and styles.

This will work the same way with your PowerPoint and Excel file examples. Give it a try!

Thoughts?

Do you have a handy use for templates like this in your office?

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

Amy Yonai

Excel: Organizing Sheets

There are two types of people in the Excel world: people who rename their sheets by double clicking, and people who rename their sheets by right clicking. A lot of us are double clickers, and therefore have never noticed the plethora of options for organizing sheets available on the right click menu. There are some very useful organization tools available to you on this menu… Let’s take a look.

Exercise File

You could really follow along on any old Excel spreadsheet, but if you would like one to experiment upon, here is the exercise from the Form Entry blog entry: ExcelSampleWorkBook

This workbook contains a list of fictional course grades that work with in some of the Excel trainings.

Right Click Menu

To try out the options below, hover your mouse over the sheet name (Fictional Course Grades) and right click. A menu will appear to select the options lined out below.

Rename

This is the one most of you know about already. You could also rename a sheet by double clicking on it.

Move or Copy

Okay, this is such a useful time saver. If you have a sheet you would like to duplicate or move to another location, rather than selecting the entire contents of a sheet and copy/pasting it, consider this option.

When you select Move or Copy a pop up will appear with some more options.

The dropdown at the top will allow you to select any open Excel Workbook to move or copy your sheet to. So if you have other Excel workbooks open right now, you will see a list of all of them here.
The center section allows you to choose where in the workbook you would like the sheet to go (in front of or behind existing sheets).
The Create a copy box is something you would want to check if you don’t simply want to move the sheet, you would also like it to remain in its original location.

Check this box and press OK. Notice you now have two sheets with the same content in the same workbook.

This is especially handy if you are wanting to create multiple versions of tables, charts, or data lists that are similar enough in layout that you don’t want to reinvent the wheel every time.

Color Code Sheets

On to many people’s favorite organizational technique: color coding!

Select the Tab Color option. This will bring up the full list of theme colors and a full color wheel under More Colors.

I selected the orange standard color. Notice how it will look a little bit different depending on if you have selected the color coded sheet. Typically the color appears a little bit faded when selected. I created a Sheet 3, and selected it in the image below… you can see how it looks different from the first two sheets.

Hide Sheets

In Excel Essentials we talk about hiding rows and columns. In Advanced Formulas we talk about how sometimes you want to put formulas on one sheet that reference another. Here is a great feature that brings the two concepts together. Maybe you want to put a list of data on a hidden sheet, and only show the summary pivot table on another. Hiding a sheet allows you to do that.

Just remember, just like with hidden rows and columns, anyone will have the ability to unhide them. To unhide a sheet, right click on top of the existing sheet, and select Unhide.

We talk about protection options in Advanced Formulas, which can address some of the privacy issues with hiding sheets and rows, but protection comes with some large caveats, so if you are interested in learning more about protection, please come to an Excel Advanced Formulas session. Plus, it is just fun to learn about formulas, so come anyway!

…or of course feel free to email or call me and we can chat about it. 😊

Thoughts?

What do you think? Will any of these organization tips help you with your Excel 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

Brenda Achey (as Flash Phil)

Word: Creating Fillable Forms Without Acrobat

For a lot of us, creating a fillable form means going to Acrobat, which boasts a robust fillable forms feature. (By the way, if you are interested in learning about Acrobat Fillable forms, please attend one of my training sessions on the subject!)

Word also has the ability to create forms fields; this is by no means intended to replace Acrobat in the Fillable Forms sphere, but it does offer some unique abilities… and there may be times you even prefer Word to Acrobat for this purpose.

Let’s check it out.

Exercise File

To follow along, download the exercise here: WordFormExercise

This is a sign up sheet for a fictional session. So far, there is just some text for guidelines for form fields. We will be inserting the form fields throughout the document, prompted by the text.

Also, here is a Solution Document. This will give you an idea of where we are going with this exercise.

Developer Tab

To see your form field tools, you will need to activate the Developer tab in the ribbon.

Right click on the ribbon
Select Customize the Ribbon
Check the box next to Developer and click ok.

Now you should have a new tab called the Developer tab, complete with group called Controls, where we are going to experiment.

Navigation

Fields

In your new Developer tab, the Controls group contains a series of form fields that can be inserted. Hover your mouse over each one to see their function.

Design Mode

There is a button in this group called Design Mode that you can toggle on and off during the creation of the form. Toggle this button on when designing the form, and off when you want to experience the form like your end users.

Properties

When you are in Design Mode, you will have the option to edit Properties of a form field.

This will allow you to name your different fields, and control other features, like dropdown lists and more (stay tuned for those).

Field Options

Text Fields

Lets insert text fields for Name and Email Address.

Click to the right of Name
Select the Rich Text field (first Aa button)
Click to the right of Email Address
Click on the Plain Text button (second Aa button)

These two fields will behave very similarly. There are, however, more robust abilities for your users to format text with Rich Text. That being said, Plain Text offers the option in Properties to allow users to input carriage returns, which can also be handy. Both are excellent tools for fillable forms.

Picture

The Session Sign Up is requesting that people submit a bio picture. Picture attachment is a form field as well.

Click to the right of Bio Picture
Select the Picture form field.

This will place a box in the form that users can press to browse for and attach a picture.

Check Box

For the dietary needs area, you may want to put check boxes next to Yes and No

Click to the left of Yes
Select the checkbox field
Repeat with the No option

Combo Box and List Box

The Combo and List Box options are very similar; both will give users a list of options to choose from, but the Combo Box will allow users to hand type their own entries as well.

Click underneath the “please specify” wording in the form and select the Combo Box (the button to the right of the checkbox field).
In the ribbon select Properties
At the bottom of the popup, press Add to add list options.

Possible list options could be:

Vegetarian
Vegan
Gluten Free
Peanut Allergy

Note the ability to remove list items or change their order with the buttons on the right.

Create a List box under “Which Session will you be attending?” question. Notice how you create it the same way, though unlike the Combo Box, users will not have the ability to add their own hand written choice.
- Possible list may be:
  - Session 1
  - Session 2
  - Session 3

Date Picker

Insert the Date Picker under the Date prompt in the form.

This will allow users to select a date from a calendar view.

Test it Out

Let’s test out the form. Toggle the Design Mode off.

For text fields, click into the field to type text.

Click on the Bio Picture field to be prompted to browse for a picture.

Test out your drop down lists. With both you can press the down arrow to see the full list of options, but you can also hand type an answer on the combo box.

The date picker dropdown arrow brings up a useful calendar from which users can select dates.

Be sure to check out the Solution Document to compare. This was a quick overview, but it is a good sized byte! 😊

Thoughts?

What do you think? Did you know you could create fillable forms in Word? Do you have a handy use for this feature in your office?

Congratulations, Power Users!

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