Mail Merge With Tricky Number Formats

Have you ever attempted to mail merge with fields that contain formatted numbers, only to find that the formatting does not carry over onto your merged Word document? This is a common source of frustration with mail mergers, and something that we can quickly remedy on the Excel end. Let’s take a look… but first, big thank you to Jamie for having this excellent question at Wednesday’s Excel Essentials session and inspiring this Byte.

Insert Merge Field dropdown in Word

Exercise Files

If you would like to follow along, download today’s exercise files below to your desktop.

For this Byte, I am assuming you know the basics of working with Mail Merge in Word. If you are not comfortable with Mail Merge, please attend a Word Essentials training with me! I would love to show you how it works.

Excel File

Let’s take a look at the Excel file first. This is a list of employees… here are all out new professors who we are going to send letters to, welcoming them to Starfleet.

There are columns with Last Name, First Name, Salary, FTE, and Phone Number. The number fields are all formatted: Salary as currency, FTE as rounded to two decimals, and Phone Number as the Special Phone Number format.

Excel file with names, Salaries, FTE, Phone Number, and TEXT columns

Heads up: Notice that, highlighted in yellow, there are also some “helper columns” with TEXT titles. Let’s ignore those for the present, because in real life, perhaps you haven’t created those, and instead incorporated number formatting, as most of us do in our Excel Files

Word File

Opening up the Word File, you will find a letter welcoming professors to Starfleet Academy. Our plan is to enter merge fields into the bold area circled below.

Letter with merge field entry circled

Mail Merge with Formatted Number Columns

Let’s start our merge. We are going to build this using our number formatted columns. Again, I am assuming you have merged before for this exercise. If you are not comfortable with Mail Merge, please come attend one of my Word Essentials session. I would love to show you!

1. In the Mailings tab, go to Start Mail Merge, and select Letters.

Start Mail Merge dropdown

2. For Select Recipients, select Use an Existing List. Browse for the Excel file, wherever you chose to save it.

Select Recipients dropdown

3. Take a look at the Insert Merge Field dropdown, and make sure that you see all the columns you saw in your Excel file. Remember, we are focusing on the first five merge fields for now (not the TEXT ones).

Insert Merge Field dropdown

4. Insert each merge field in the indicated location. It should look like the example below.

Merge fields inserted into Word document

5. Let’s toggle the Preview Results button to see how this will look.

Preview Results button

This is usually the point where people realize that certain types of number formatting do not come through in Mail Merge. The salary, phone number, and FTE are just general number formatting. The phone number has no dashes, the salary has no commas, and the FTE is missing a “0.”

Merge fields previewed with unformatted numbers.

6. Let’s remove the Phone, Salary and FTE merge fields, and try this again. This time, insert the TEXT options that we saw in our “helper” columns in the Excel file.

Insert Merge Field dropdown with TEXT fields circled.

So Salary should be replaced with TEXTSal, FTE with TEXTfte and Phone with TEXTPh.

Merge fields inserted as described

7. When you Preview results, it will look much more like what we had in mind.

Preview with formatted numbers

What difference! Let’s see what makes these columns different in our Excel file.

Text Formulas

Open the Excel file again, and let’s do some investigating. We are going to check out our “helper” columns in columns F, G, and H that contain TEXT in the header.

1. Let’s see how the formula for TEXTSal is set up. Click into cell F2, and take a look in the formula bar. The formula used for Sal was:

=TEXT(C2,”##,##0″)

Excel, F2 selected, formula bar circled

2. Do the same for TEXTfte in G2. The formula used was:

=TEXT(D2,”#0.00″)

Excel, G2 Selected, formula bar circled

3. … and finally, TEXTPh in H2. The formula used was:

=TEXT(E2,”[<=9999999]###-####;(###)###-####”)

Excel, H2 selected, formula bar circled

So our investigating reveals that TEXT formulas have been used in these columns, which came over in a much better format for mail merge purposes.

If you would like to learn more about the TEXT function, take a look at this handy guide from Microsoft that will walk you through creating all of these used above, and more!

Thoughts?

Has this ever happened to you when you were mail merging with numbers? Do you think you will incorporate TEXT functions with your future merges?

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

Haley Underhill

Excel: Navigating Inside a Document with Links

Utilizing links in an Office document can do so much more than simply take your end users to a website. Links are also a great way to help people navigate around a document. Below is a great example… let’s check it out.

Exercise File

Feel free to follow along with today’s Exercise File:

This is a special file full of (fictional) information; there are a variety of sheets at the bottom of the document: a Home sheet, a Management sheet employee information, a sheet with a Class List, and last but not least, Dessert Sales.

You would like to share this document with a group of people who may or may not be very proficient with Excel. Just like we saw with slicers, creating buttons for people to press can be a huge help if people are not familiar with navigating around an Excel document.

Assigning Links

Linking within the Document

We want to assign links to the icons on the Home sheet.

1. Click on the icon of people to select it.

2. Right Click on top of the image, and select Link. (note: this may be called Hyperlink in your version of Office)

3. Notice on the left side of the screen there are a variety of location options. For the icons, we are going to select Place in This Document.

4. Select the Management sheet and press OK.

5. Test out your link by deselecting the icon (click on any cell), then click on the icon.

6. Follow the same process to link the Backpack image to Class List, and the Cake Slice to Dessert Sales.

7. Notice each additional sheet (besides Home) has some arrows in an icon to the right of content. Click on this icon to select it, then link this to the Home sheet.

Test out the icons you just created! You should be able to navigate to a sheet by pressing on an icon in the Home sheet, then go back home by pressing on the Arrows on any other sheet. How cool!

Linking Outside the Document

We have created all sorts of links inside our document. Let’s take a look at linking outside the document.

1. On the Home sheet, press on the WSU Logo to select it.

2. Right click on top of the logo, and select Link (or Hyperlink).

3. On the left side of the screen, press Existing File or Web Page.

4. In the Address box, type http://wichita.edu

5. Press OK.

Test out your newly created link by clicking on the WSU Logo.

Additional Notes

  • Linking to Documents: When you linked to the webpage, you might have noticed the contents of your computer pop up underneath. You can link to an existing document, but remember that the only people who can open the link will be people who have access to the location you are linking to. If you link to something on your desktop, probably you are the only person who can open the link (and only on that machine).
  • Cell Reference: When linking to a place in this document, you can have the link go directly to a specific cell. Enter the cell name in the field provided, otherwise it will default to A1.
  • Removing a link: At any time, links can be removed or edited by right clicking on top of the link and selecting Remove Link or Edit Link.

Thoughts?

What do you think? Will you use links to navigate within your shared documents?

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: PivotTables and Calculated Fields

Formulas and PivotTables. Many of us have gotten caught up creating formulas outside of a PivotTable by referencing cells within a pivot table, only to be disappointed when we update our PivotTable and cells shift back and forth. There are several ways of dealing with this issue. One way is to create a Calculated Field within your PivotTable. This is something we cover briefly in Excel PivotTables sessions, but it is definitely worth a review.

Fields, Items and Sets drop down with Calculated Field circled

Exercise File

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

This document contains two sheets: one sheet contains the PivotTable, and the other contains the source data, showcasing a couple years of dessert sales. Right now, you have income and expense information in the values of the PivotTable. Since we have those figures handy in our data, it would be nice to figure out net income.

For this Byte, I am assuming you are comfortable with PivotTables in Excel. If you are not, please come attend my PivotTables, Charts, and Pictures sessions! Sessions are listed in myTraining and are updated regularly.

PivotTable Contextual Tabs

We are going to be working a lot with the PivotTable Tools contextual tabs. Like any other contextual tab in Microsoft, remember that you need to click on the element to see the corresponding contextual tab(s).

PivotTable Tools COntextual Tabs

When you click on the PivotTable, you should see two contextual tabs for PivotTable Tools: Analyze and Design.

Click onto the pivot table to activate Pivot Table TOols tab

Be sure you can see these tabs before moving on to the next step.

Inserting a Calculated Field

Remember, our end goal is to create a Net Income field that will subtract the Expenses from the Revenue.

1. With the PivotTable Tools activated, go to the Analyze tab, Calculations Group.

2. Select the dropdown for Fields, Items & Sets and select Calculated Field.

Fields, Items and Sets drop down, Calculated Field circled

3. In the popup screen that appears, you will see all the PivotTable fields listed at the bottom of the screen. At the top is an opportunity to name the field and a space for a formula.

Insert Calculated Field Pop up

4. Click in to the Name box and change Field1 to Net Income.

5. Click in to the Formula box, and double click on Revenue in the Fields area. Type a “-” on your number pad, and double click on Expense. We are building a formula that will read: = Revenue – Expense.

6. Press Add.

Insert Calculated Field pop up, filled out as described in text.

7. Press OK, and look at the field you just created incorporating a formula from two other fields. Pretty cool!

Pivot table with new Calculated Field column

Notes

  • PivotTable Fields: You will see this calculated field appear in the Values section of your PivotTable Fields.
Sum of Net Income visible in the Values box of Pivot Table Fields list.
  • Calculations from Calculations: If you are creating another Calculated Field, you will see the Net Income field we just created as a field option for use in future formulas.
Calculated field visible in a new Calculated Field pop up screen for further calculations

Thoughts?

What do you think? Can you incorporate Calculated Fields into your PivotTables? I would love to hear from you!

Open Labs

Did you know that Applications Training has open labs every other week? Whether you have a Banner question or a Microsoft question, feel free to stop by! Open lab times are listed in myTraining under the title Open Lab Assistance on Banner 9, Time Entry, Reporting, & Microsoft/Adobe.

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

Julie Scott

Julie Scott with Power User Certificate

Excel: Using Slicers With Password Protected Sheets

Who doesn’t love slicers? What an awesome way to sort and filter data. We have talked about how, in addition to being a time saver, slicers can make it easier to share your data with others who are not as comfortable working in Excel. Not everyone understands, “filter the data,” but we all can understand “push this button!”

Slicers circled

At the same time, in the advanced Excel sessions, we talk about how you often want to protect a workbook from accidental changes, particularly after you have spent a lot of time creating elaborate formulas. Unfortunately, protection also renders your slicers useless… Here is a great compromise: you can protect a document and leave your slicers fully functional by incorporating a very simple method. Let’s take a look.

Before we do, big thank you to Erin for having this question in last week’s Excel Essentials session and inspiring this byte!

Exercise File

If you would like to follow along, you can download the exercise file here:

This is a list of fictional students in a fictional class with their fictional grades. (This is fictional data, friends). In this scenario, it looks like you decided to turn this data into a table, because of course you did! And you added some slicers for ease of use so you can quickly filter and only view students with A’s or Freshmen with A’s, etc.

By the way, if you are not fully comfortable with tables or slicers in Excel, please come attend one of my Excel Essentials sessions! I would love to see you.

Disclaimer

  • With protection options, be very careful when applying passwords. If you lose the password, unfortunately, we cannot get it back for you!
  • You can also lock a document without applying a password, which is handy to know if you are worried less about dishonesty and worried more about accidental typing. This is the route we are going to take today.

Unlock Slicers

1. Right click on a slicer and select Size and Properties.

Right click menu, Size and Properties circled

2. On the right side of the screen a Format Slicer menu will appear. Click the triangle next to Properties.

triangle next to Properties circled

3. Uncheck the box next to Locked.

Box next to locked unchecked

4. Follow the same steps for the second slicer.

Protect Sheet

  1. In the ribbon, go to the Review tab, Protect group and select Protect Sheet.
Protect sheet button circled

2. This is where you could set up a password, but for today, let’s leave that area blank.

3. Scroll down in the list and check Use AutoFilter. Click OK.

Use AutoFilter circled

Now… notice that you cannot make a change to a specific cell (error message below), but the slicers should be fully functional allowing users to filter the data.

Locked cell error message

Unprotect Sheet

Don’t forget, when you want to edit, you can Unprotect the same place you Protect a sheet.

In our case, this was on the Review tab, Protect group. It can also be found in Backstage View.

Unprotect Sheet button

If we had created a password, this is also where we would have been prompted to enter it to unlock the sheet.

Thoughts?

I would love to hear your thoughts on this! Have you been frustrated by not being able to protect a worksheet and user slicers? Will this save you any trouble?

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

Your Answer now has a response: Nope.

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.

Review tab, Protect workbook button

Unhide Sheet

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

Right click menu, unhide selected.

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?

Sheet with responses

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.

VBA coding of Macro

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:

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

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

Look at this nifty Timeline slicer you just created!

Timeline Slicer

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.

Clear filter button on slicer

Timeline Tools

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

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.

Filter button

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

Glenn

Jeremy Webster

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.
Customize Ribbon selected on dropdown

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

Ribbon with Tabs, groups and commands labeled

The Ribbon is made up of:

  1. Tabs: e.g. Home, Insert, Design
  2. Groups: printed at the bottom of each tab, e.g. Clipboard, Font, Paragraph
  3. 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

  1. Right click in a grey space on top of the ribbon and select Customize the Ribbon.
    Customize Ribbon selected on dropdown

    • Notice this looks similar to the QAT customization screen, but this time Customize Ribbon is highlighted.

Customize Ribbon highlighted

  1. Highlight the Home tab on the right and press New Group at the lower right of the screen.
    New Group button circled
  2. 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.
    New group, rename button circled

Add Commands

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

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

Edit footer slected, add button circled

  1. Do the same for Edit Header, and Page Setup

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

Special group with new 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:

  1. Right click on the ribbon and select Customize the Ribbon
  2. On the right pane, change the dropdown from Main Tabs to Tool Tabs.Tool Tabs selected
  3. Find the Picture Tools tab and select the only group (Format).
  4. 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:

  1. Right click on the ribbon and select Customize the Ribbon.
  2. Highlight the command or group (in our case, Special) on the right pane
  3. Press the Remove button between the two panesSpecial group highlighted, remove button circled

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:

  1. Outlook: Journal Feature to the Home tab (and QAT, because I am extra).
  2. 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.
  3. 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.

Recruitment Figures from exercise file

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?

 

Figures with new percent calculation

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.

  1. Select Cell C6 by clicking on it.
  2. Go to the Data tab, Forecast group, and select What If Analysis.
    Data Tab what if analysis
  3. Select Goal Seek
    Goal Seek Option
  4. In the pop screen that appears:Pop up screen, as described in text below
    • 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.
  5. 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.

New goal lists 24% increase needed

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

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

 

 

 

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.

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

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.

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

Map View

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:

  1. Press the + next to Category and select Class
  2. Press the + next to Time and select Date

Category and date fields

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

Dimension viewable by class

but that is not all you have just done…

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

Play button

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.

Create Video and Capture Screen buttons

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!

Launch Tour screen

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

Word Template with Themes and palettes

Save As

  1. 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.
  2. In the Save as Type dropdown menu, select Word Template.
    Save As screen with type dropdown
  3. 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.
    Custom Office Templates Folder Location
  4. Press Save.

 Access Your Personal Templates

Let’s test out your new template!

  1. 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.
    File, New screen
  2. At the top of the templates, notice you now have a Personal tab. Press this button.
    Personal Templates button
  3. You should see your new template, the Office Bytes Template. Select this to apply the template to your new Word document.
    Template in the Personal Templates folder

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

Amy Yonai