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