PowerPoint: Extract All Media with One Action

There are a variety of reasons that you may find yourself needing to extract all the media content (video, audio, pictures) from a PowerPoint presentation. This process can be especially cumbersome if you have a large presentation, where saving video from slides one at a time is a daunting prospect. Here is a cool hack if you find yourself in this position. By the way, this trick will extract pretty much all content, so even if you aren’t looking for media perse, you will find this interesting. Before we jump in, thank you to Taylor for having this question in PowerPoint Advanced training last week and inspiring this byte.

File Name Extensions

First thing’s first! This trick will be a lot easier if you have your file extensions visible. If they are not already (or if you are not sure), here is what you can do:

1. Open up any folder, or visit your File Explorer in your task bar.

2. Click in the View button at the top. This will pop out the ribbon in File Explorer (yes, there is a ribbon in here! I want to do a byte on this too!)

3. In the Show/Hide group in the View tab, make sure that the box next to File name extensions is checked.

Duplicate File (optional)

This is optional, but we will be changing the file extension next… so if this is your first time and this is an important PowerPoint presentation, I would recommend duplicating it so you have an original version in case something goes awry.

Changing File Extensions

Here is the PowerPoint file, with some media attached. Now that we have turned extensions on, you can see the extension is .pptx.

If you are working with an older file, the extension may read as .ppt. If this is the case, you will need to resave it as the file type Powerpoint Presentation to bring it up to date (more about this here).

1. Click on top of the name of your file to highlight it, or right click and select rename. Instead of renaming the file, however, we are going to change the extension.

2. At the end of the title, change the extension from .pptx to: .pptx.zip

It will look like this:

3. Press Enter. You will receive a warning message. Click Yes.

Note: You can always change the file extension back to .pptx the same way.

New File Appearance and Behavior

The appearance of the file changes. At initial glance, it looks like a normal zip file.

Double click on it, however, and you will find a variety of folders. Select ppt.

Here are the “guts” of your Powerpoint… including a file called media.

Inside the Media file are all the images and videos, including any slideshow recordings you have made that are tied in to your PowerPoint.

Who knew it would be so easy to extract all this information?

Thoughts?

What do you think, will this help you with any of your projects, or did you find it interesting to see the “guts” of your PowerPoint presentation?

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

10 MORE Secret Commands in Microsoft Office

Almost immediately after I posted my Top 10 Favorite Secret Commands in Microsoft Office, so many more came to mind… So without further ado, here are 10 more commands that go above and beyond our usual well-known commands.

Ctrl + K = Hyperlink

This command works in all your Microsoft Office programs, and other programs as well (like OU Campus!). Select your text, and rather than going through a right click menu, press Ctrl + K to access your hyperlink options.

Think about how quick this action can be if you have already copied your hyperlink, you can simply select text, Ctrl + K, Ctrl + V (paste), enter, and you are done.

Ctrl + ; = Insert Date (Excel)

This is different than the =TODAY function we talk about in Excel Advanced Formulas class. This command, Ctrl + ; does not insert a formula; rather, it inserts today’s date as stagnant text.

By the way, a similar command is Ctrl + Shift + ; to insert the current time.

Ctrl + 1, 2, 3… = Outlook Navigation

We have talked before about how Outlook has some incredible time-saving commands, and here is one of my favorites. Ctrl + (a number) will take you through the basic Outlook navigation.

Here are the basic navigation commands in Outlook:

Ctrl + 1 = Mail
Ctrl + 2 = Calendar
Ctrl + 3 = Contacts
Ctrl + 4 = Tasks
Ctrl + 5 = Notes
Ctrl + 6 = Folders
Ctrl + 7 = Shortcuts
Ctrl + 8 = Journal

So the next time you are in your mail module, and want to take a look at your calendar, try Ctrl + 2 to quickly access it!

Ctrl + Shift + < or > = Adjust Text Size (Word, PPT)

Do you want to increase a block of text by exactly one font size? In Word or PowerPoint, select your text, then try the commands Ctrl + Shift + > to increase all selected text by one font size or Ctrl + Shift + < to decrease.

Ctrl + [Drag] = Duplicate

We have talked about Ctrl + [drag] in Acrobat fillable forms, but did you know if works in Microsoft Office as well?

If you would like to duplicate a block of text in Word: select the text, hold down your Ctrl key, and with your mouse click and drag it to a new location. You will have an exact copy of the selected text.

Same story in Excel. Want to copy a block of cells? Select them, hold down the Ctrl key, and with your mouse click and drag them to their new location

One note in Excel, you will want to hover your mouse on the line of selected text, until you see the four headed arrow cursor. Otherwise, Ctrl will simply deselect one of the cells in your group, rather than move them.

Ctrl + Shift + C = Copy Formatting

Almost like the Format Painter in command format… you know that Ctrl + C is copy, but did you know that Ctrl + Shift + C copies formatting?

This will work in most of your Microsoft Office programs. Select the text you would like to copy, and press Ctrl + Shift + C.

Select the text where you would like to copy the formatting, and press Ctrl + Shift + V. The Format Painter as a command, how cool!

Ctrl + Alt + V = Paste Special

Ctrl + V is paste, and we just learned that Ctrl + Shift + V is use when copying formatting… here is another paste option: Ctrl + Alt + V will Paste Special. This will work in most of your Microsoft Office programs.

Frequently used in Excel, but also when transferring Excel data to Word… Select your data and copy (Ctrl + C), select the new location and press Ctrl + Alt + V to access Paste Special options.

Ctrl + Shift + $ = Currency Format (Excel)

There is nothing wrong with selecting a number format from the ribbon, but if you know you want currency, it could speed things up considerably to select your cells and press Ctrl + Shift + $.

Ctrl + Shift + ! and beyond= Number Formats (Excel)

While you are looking at the numbers on your keyboard, try out a few more. Ctrl + Shift + ! will give you a standard Number format. Ctrl + Shift + % will give you percentages, and so on. Test out a few, and see if there are number formats you find yourself reaching for regularly.

Alt + F = File Tab

The Alt commands are a powerful tool in Microsoft Office. Alt will take you to the ribbon in your Office programs, and there are so many paths you can learn and memorize from there. Here is a great one to get you started:

Alt + F will take you to the File menu.

From here, notice the highlighted letters letters on top of popular commands. S for Save, P for Print and so on…

Even if you don’t end up falling in love with all the Alt commands in the ribbon, Alt + F is an easy one to learn and utilize.

Thoughts?

What do you think, will you use these shortcuts to save time in Microsoft 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

Stephanie Versch (Picture coming soon!)

Word: The Spike Feature

Have you ever heard of the Spike feature in Word? If you are shaking your head, you are not alone… it is not well known. This is a handy hidden feature that could be an enormous help to you if you are copying and combining separate pieces of data. It is kind of like your clipboard, but if utilized properly, can be a lot faster. Let’s check it out.

The Scenario

The best uses for Spike involves the need to move and combine separate blocks of text. In the example below, we want all the important, highlighted text to be pulled together at a separate location, removing it from the extemporaneous paragraphs. We could copy and paste three times, or we can utilize the Spike to do it all at once…

Step 1: Cut Content to Spike

Start by selecting the first piece of information to be moved. With that text selected, press Ctrl + F3 on your keyboard.

It will appear that this text has disappeared, but it has been cut to a special location… and if its disappearance bothers you, ctrl+ z (undo) would bring it back.

Repeat this step with the second and third important piece of information. Now you will be left with only extemporaneous information.

Step 2: Paste Content from Spike

There are a several options to paste this content, and none of them involve the usual methods. All of these techniques will paste the entire contents that you previously cut to the Spike.

The First Method: Type “Spike”

Note: This method will not clear out what you have stored in the Spike, and as you cut more items to the Spike, it will be added to existing content.

Place your cursor where you would like the text to go
Type the word “Spike”; you will need to type slowly and you will see some helper text appear above the word.
Press enter to insert the text.

The Second Method: QuickParts

You can also paste content from your QuickParts… this method will also not clear the contents of the Spike.

Place your cursor in the desired location.
Go to the Insert tab
Select QuickParts
Hover over AutoText
Select Spike.

The Third Method: Ctrl + Shift + F3

This method will clear out what has been store in the Spike as well as paste the joined contents in a new location.

Place your cursor in the desired location
Press Ctrl + Shift + F3 on your keyboard.

Remember, if you picked one of the first two methods, at some point you will need to do this last method to clear the contents of the Spike.

Function Keys

It is worth mentioning, as a lot of us are working from laptops at the moment… Since you have to incorporate a function key (F3), remember that you may have to activate your function keys for this, or any function key feature, to work. Many laptops have keys that serve dual purpose as function keys and other features, like volume or brightness.

Look for a key that says “Fn” to toggle on the function keys.

Thoughts?

What do you think? Do you have a use for the Spike feature in your Word 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

Susan Norton (picture coming soon!)

Outlook: Free Up Space FAST by Taming Large Emails

Sometimes archiving just doesn’t do the trick. Your mailbox is full, you archive, and still find yourself close to the precipice of another notification that you will need to free up space. So you go through old emails, deleting hundreds of junk mails, only to see the tiniest bit of space free up. What is going on? Well… the culprit is usually not the hundreds of text-only junk emails, but rather a handful of very large emails. Here is how you can isolate those large emails, and free up space FAST by contending with those large beasts in your mailbox.

Method 1: Sort

At the top right of your email list, notice that your default is to sort emails by Date. This is usually what you want, but for the moment, you want to find your largest emails.

Click on this dropdown, and select Size.

Notice how this organizes your mailbox. Your emails are separated into:

Huge: 10-25 MB emails
Very Large: 5-10 MB emails
Large: 1-5 MB emails
Medium: 25KB – 1 MB emails
Small: 10-25KB emails.

This is often an “aha!” moment for people. You might discover, for instance, that a newsletter you receive weekly is the culprit for the size inflation in your inbox. Perhaps you forgot about how a month ago a large quantity of pictures were sent to you, and so on.

Combing through and deleting a handful of your Huge and Very Large emails can free up tons of space in no time.

Method 2: Incorporate a Search Folder

Search Folders are not a new topic in Office Bytes… See this article for more: Outlook Search Folders and Smart Folders. Long story short, you can create a variety of search folders to create a window into a search in your Outlook program, and Large Mail is an excellent option if you want to keep an eye on large emails long term.

1. On the left side of the screen, scroll down to Search Folder. It is usually toward the bottom of your screen.

2. Right click and select New Search Folder.

3. In the popup, scroll down to Large Mail. Customize this folder further by choosing a size at the bottom. Press OK.

4. Now you will have a handy folder on the left side of your Outlook screen that you can access any time to keep track of large emails.

You can delete emails from either your sorted inbox view, or from the Search Folder you created.

Don’t Forget to Empty Deleted Items!

Don’t forget, those items you delete will sit in your deleted items folder and continue to take up space in your Office account until you empty the folder.

One way to do this is to right click on the Deleted Items folder, and select Empty Folder.

Remove Large Attachments

Perhaps you have found a large email; and while you still wish to hold on to the email itself, the attachment is no longer necessary, or has already been downloaded. Another option you might consider is removing the attachments from large emails.

1. Select the email message.

2. Click on the dropdown next to the attachment, or click on the attachment. Do not double click, as this will open the attachment, just a single click to select it.

3. In either the dropdown, or in the Attachments contextual tab that appears when you select the attachment, select Remove Attachment.

Thoughts?

What do you think? Will this clear up some space in your Inbox? I did this last week, and cleared out almost 2 GB in about fifteen minutes. I was shocked to discover that an image laden newsletter I had been receiving weekly was the primary culprit.

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: Remove All Blank Rows in a Document

There are two things that are the kryptonite in an otherwise perfect Excel document: merged cells and blank rows. We have already tackled merged cells, so today I want to focus on blank rows. People commonly insert blank rows for stylistic reasons, but here’s the thing: don’t. It may feel like you are making your document more readable, but really you are making it less functional. Let’s take a look.

The Trouble with Blank Rows

If you are plagued by blank rows in your documents, you may be experiencing a variety of issues. Look at the example below… this is the concert promoter example we use in our PivotTables session.

In this scenario, however, the concert promoter has decided that it would help him/her visually to separate out each event with a blank row.

So, what’s the problem? For one, when Excel arrives at a blank row, it thinks you have finished your thought. This manifests in many inconveniences. For instance:

AutoFill Issues

What if we decide to insert a formula in a subsequent column? We learn about utilizing AutoFill with formulas in Advanced Formulas, so we hover our mouse in the lower right until we see the AutoFill Handle and double click…

… Only to find that Excel stops at the blank row. In a document with 5,000 rows and numerous blank rows, you probably don’t want to have to manually autofill every time you insert a new formula…

PivotTables and Tables

How about creation of Tables and PivotTables? We know to put our cursor in the middle of the data and let Excel select all the information that will create our PivotTable or Table, but look what happens when we have a blank row… the Table selection will stop just shy of that first blank row. And even if you override this, you are stuck with numerous “blank” results. Not fun.

Other Examples

Sort and Filter, chart creation, and many more features will just not work properly with blank rows. When you really want to work with your data, you will discover so many more examples. Usually people call me at this point, thinking they are “bad at Excel,” when really they are doing everything right to create a PivotTable, or sort data, it is just those blank rows getting in their way.

So now that you want to remove all the blank rows in your document, what should you do?

Removing All Blank Rows

Of course, you could highlight each row by hand and delete them one by one, but an example that came up last week was a person with a 2000 row document that contained numerous blank rows sprinkled throughout. If this is the case for you, here is how you can remove all blank rows in your document in one fell swoop:

1. Select all the data in your document. There are several ways to do this, but one quick was is to click on the Select All button: the triangle above your first row (pictured below).

2. On your Home tab, Editing group, select the Find & Select dropdown. Select Go To Special

3. In the Go To Special screen, select the radio button next to Blanks. Press OK.

4. This will highlight all your blank rows. Quick note: if you see too much content selected, hold down your Ctrl key and click to deselect. For instance, sometimes this feature will select the blank column to the right of your data, and you can deselect the entire column of data this way (Ctrl + select column).

5. With these blank rows selected, go to the Home tab, Cells group. Select the Delete dropdown, and select Delete Sheet Rows.

All the selected blank rows will be deleted. So much better! You can now enjoy a happy and carefree life, now that you are free from the restrictions of blank rows!

Thoughts?

Will this save you some trouble in any of your documents? I would love to hear how you use this!

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

Faye O’Reilly
(Picture coming soon!)

Excel: Calculating Scenarios with What If Analysis

There are many reasons you might be running through a list of possible scenarios. If you would like to transform your data into a visualization of a variety of possibilities, you might be interested in the What If Analysis feature in Excel.

Video

Here is an accompanying video for this blog post. I hope this is helpful!

A Fictional Applicable Situation

Think of a business considering a start date, weighing the cost of a delayed open. They have two possible amounts for how much they stand to lose per day if they don’t open: $250 loss per day and $500 loss per day. They also have three possible proposed open/start dates: 6/1/20, 7/1/20, and 8/1/20. The owner would like to know a variety of outcomes, given different possible start dates and different possible income loss per day to delay opening.

Of course, this is a vastly over simplified version of a business accounting model, but will hopefully serve to show you how this feature works.

Document Formulas

The two cells that will prompt changes in our What If Scenario are the Start Date, of which there are three possibilities (June, July, August), and Loss/Day, of which there are two possibilities ($250 and $500).

The total Expenses and Balance in reserve will change as the date and loss figures change.

Creating Scenarios

To create What If scenarios:

1. Go to the Data tab, and select the dropdown for What-If Analysis. Select Scenario Manager.

2. If you have not created any scenarios, this area will be blank. Press Add.

3. Name your scenario something that will help you identify it later. I identified mine with the elements that would be changing in each scenario: the start date and the loss per day.

4. Click into the blank space next to Changing Cells and select a cell that you would like to create a scenario for. In my case, I selected two cells (for date and loss per day) by holding down the Ctrl key and selecting each of them. Press OK.

5. For my first scenario, I wanted to see what would happen if B1 were the date 6/1/2020, and B3 were $500. I entered those figures accordingly, then pressed Add.

6. Create as many scenarios as you would like. I made six possible scenarios and for the different possible loss amounts and start dates.

7. Select a scenario and press show to run the What If Analysis.

There is also an option to generate a summary on a new sheet with all the possible scenarios. Press Summary to generate this.

Accessing Scenarios

You can access scenarios this way:

1. Go to the Data tab, What-If Analysis dropdown, and select Scenario Manager.

Each of these listed scenarios represents a potential start date and a different loss per day amount.

2. To examine a scenario, either double click on one of them, or select one and press Show.

3. The various figures change as you select different possible scenarios: a July start date, with $250 loss per day means the total expenses were $17,000 and remaining balance $33,000.

An August start date with $500 loss per day leaves a $500 remaining balance.

Extras: Macros and Charts

You can customize your What If scenario with charts and macros. Look at this setup below, incorporating a Donut Chart (like this one) to accompany the data, and some text boxes linked to macros to run the What If Scenarios.

Pretty handy for a presentation or for distributing to others without having to send instructions for running the What If scenarios.

Downloads

I made a couple versions of this document for you to examine that incorporate the What If Analysis:

Original Version

Macro Enabled Version with Chart and Extras

Warning: you may choose to set up formulas a variety of ways, but the formulas in these documents were set up for forecasting, so are not intended to be access to far in the future. Long story short, you may get an error if you are opening these documents too many months from now; this is due to the formulas, not the What If feature.

Thoughts?

What do you think? Has this sparked any ideas? I hope this feature helps you out as you are making plans for different future possibilities.

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: Create a GIF from a Presentation

First of all, is it pronounced GIF, or GIF? Did you just say it in your head both ways? There is endless debate about the pronunciation, and for this I am grateful this article is in print rather than spoken. GIF stands for Graphics Interchange Format. You have likely experienced these short looping animations in a variety of ways, most notably in social media circles. Thanks to Office 365, you now have a new way of creating GIFs… through PowerPoint! Let’s take a look.

Action in the Presentation

Ideally, you will want these to be short and simple presentations. Because GIFs are all about action, consider applying a few actions (Transitions and Animations) to the presentation.

To learn more about Transitions and Animations, I would love to see you at a PowerPoint Essentials session!

You can set up timings for Animations in the Timing group of the Animations tab. Any durations and delays you apply will be honored when you turn the presentation into a GIF.

Timings

For transitions, any “mouse click” items will automatically be turned into a timed action, but you can choose to set timings for slides that you would like to allow a little extra time on for your end users. You can alter the “After” setting in the Transitions tab to accomplish this.

Exporting, and Settings

Once you have your PowerPoint ready to go, visit File, Export and select Create an Animated GIF.

Notice you will have the ability to change the file size and adjust the seconds spent on a slide, which will be overridden by any timings you set in the previous steps.

When you select Create GIF, you will be prompted to select a location, and you will be done!

Thoughts?

What do you think? Will you create GIFs from your PowerPoint Presentations?

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

Compatibility Mode: How to Re-Activate Your Favorite Features

This happens frequently after a PivotTables session… An attendee excitedly returns to their computer to create a PivotTable from an existing Excel file, only to discover that the PivotTable screen looks a lot different than it did in class, and a lot of their favorite features are grayed out in the ribbon. This is not just an Excel issue… sometimes this happens Word or PowerPoint: certain features are mysteriously faded in the user’s ribbon. The culprit? Compatibility Mode. Let’s talk about this setting, and how to escape from it, in your Microsoft Office documents.

What is Compatibility Mode?

The intentions of Compatibility Mode are in the right place. Its purpose is to make a document as functional as possible with older versions of software. So if you are using Office 365, and your friend is running Office 2010, you could send this Compatibility Mode document to them and rest assured it will look the same to them as it does to you.

For this reason, you will often see data that is exported from other programs; Reporting Services and WSU Reporting, for instance; default to Compatibility Mode. The software programmers aren’t sure what version of Office you will be using, so they err on the safe side, and have the export default to Compatibility Mode.

Another possibility: you are opening an old document. If someone created this document in 2001, and never updated the file format since then (we will talk about this further down), chances are you are in Compatibility Mode by default.

Missing Features

Earlier I mentioned that PivotTables are often the first place people notice missing features in the ribbon…

… But this happens in other Excel tabs, PowerPoint, and Word as well.

The features that gray out vary from version to version, but bottom line, newer features are what disappear in Compatibility Mode. When someone is experiencing these ghosts of buttons, this is almost always the culprit.

How to identify Compatibility Mode

How do you know for sure if your document is in Compatibility Mode? There are several ways.

1. You might have some visual cues; the icons look slightly different in Compatibility Mode, shown below, on the left side of the icons.

2. At the top of your document, next to the title, you might see the words Compatibility Mode

3. Go to your Save As screen (File, Save As). What do you see in the dropdown under the title? If it says Excel 97-2003 Workbook, you are in Compatibility Mode.

In fact, stay in the Save As screen, and I will show you how to fix this.

How to Escape From This Setting (and get your favorite features back)

In your Save As screen (shortcut: F12), Compatibility Mode reads as Excel 97-2003 as the File type (dropdown under the document title).

To fix this issue, click on the dropdown under the title, and select the first option for Type: Excel Workbook in this case (Word Document for Word and PowerPoint Presentation for PowerPoint).

Important: you must close the file and reopen it to see the new options appear.

That’s it! You have your features back! I hope this helps with some of your legacy documents or documents exported from various sources.

Thoughts?

What do you think? Have you been plagued by the trials and tribulations of Compatibility Mode?

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: Secret Recording Tab

We are all finding ourselves making more recordings in PowerPoint lately. I hope you had a chance to try out the new and improved Record Slide Show feature in PowerPoint. In fact, you are probably using a variety of recording features in PowerPoint and have likely noticed that these features are spread out throughout numerous different tabs in the ribbon: Slide Show recording is on the Slide Show tab, screen recording on the Insert tab, exporting a video is in Backstage View, and so on. Let’s simplify the process with a secret tab: the Recording Tab.

Activate the Recording Tab

We learn about another secret tab in Excel Macros session, but PowerPoint has its own special tab: the Recording Tab.

To activate this tab:

1. Right click anywhere on the contents of the ribbon.

2. Select Customize the Ribbon.

3. On the right side of the popup is a list of the tabs in your ribbon. Scroll down to Recording. Click on the checkbox next to this tab to activate it in the ribbon.

4. Press OK.

Recording Tab

Hey, you have a new tab! How exciting. Here are some details about what you can access here.

A. Record Slide Show: A feature found on the Slide Show Tab. Learn more about this feature here.

B. Screenshot: Normally found on the Insert Tab, this feature allows you to grab a picture of your screen for a presentation.

C. Screen Recording, Video, and Audio: Also found on the Insert Tab, powerful tools for inserting video, audio, or recording your screen in action.

D. Save as Show, Export to Video: Typically you have to venture to Backstage View (File) to access these features.

Just to reiterate… you are not accessing new features with the Recording Tab, but you may find this to be a time saver as it brings together features that normally live all over the ribbon in various disparate tabs.

Thoughts?

What do you think? Will you utilize this secret tab to save some time when recording in PowerPoint?

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

Maddie Domebo (Picture coming soon!)

Excel: Merging Data with Power Query (Bonus Video!)

Power Query in Excel is an incredibly powerful tool. A while back, I posted a brief introduction to this feature. I wanted to expound upon that information with another great use for Power Query: merging data. This is a common situation: a lot of us have data files we are pulling from different sources that contain unique information. Combining this data into one powerhouse sheet can be a great time saver. Let’s take a look at how to do this.

Video

Hey, I am trying something different! Thank you to those of you who completed our ITS Applications Training Survey last week. A ton of you said you wanted videos and exercises, so let’s try it out today. Below is a link to a YouTube video, and read on for the exercise files. I will also include written instructions, as per usual.

Exercise Files

Here are a couple downloads for you:

MergeExercise: Start Here

MergeExerciseSolved: to check your work

Two Disclaimers

For this Byte, I am assuming you already have some basic knowledge about Excel, like Tables and PivotTables. If you would like to learn more about either of these, I would love for you to attend a session! Look for sessions in myTraining.
Also, you will likely need to be using the Windows version of Excel, preferably 365, though this should also be possible in 2016.

About This Workbook

This spreadsheet contains three sheets: each with unique information about our key players in the name column.

The first sheet contains their number and location,
The second contains their favorite animal, and
The third their favorite candy. Our goal is to bring all this data together in one powerhouse worksheet.

Import Data as Queries

1. In the first sheet, place your cursor in the middle of the data. Press Ctrl + T to turn it into a table. You can also do this from the dropdown in the Home tab.

2. Make sure “My table has headers” is checked and all the data is selected, and press OK. Repeat this step with each sheet of data, so you have a total of 3 tables in the worksheet.

3. Select one of the tables. Go to the Data tab in the ribbon, Get & Transform data group, and select From Table/Range. If you don’t see this button in the Get & Transform Data group, check the Get Data dropdown for it.

…you will be taken to the Power Query editor screen…

4. In Applied Steps on the right, click the X next to changed type to remove it.

5. In the upper left select the dropdown for Close & Load To…

6. Select Only Create Connection and press OK.

7. Repeat Steps 3 – 6 for all the tables you created. You will see a list of tables appear on the right side of the screen under Queries and Connections.

Merge the First Two Queries

1. In the Data tab, go to the Get Data dropdown, hover your mouse over Combine Queries, and select Merge.

2. In the Merge screen, select your first table in the upper dropdown, and your second table in the second dropdown.

3. Select the two common columns: in this case, it is the Name column. Click on the Name column under each table. Press OK.

4. In the Power Query screen, find the double headed arrow at the top of the last column, from the merged table. Click on this button to expand information.

5. Select what you would like to expand. In this case, we are unchecking the name box, and just keeping Animal selected. Press OK.

This will expand the Animal column from the second table. Note that it applies its own naming format to the column title. You can rename the column, if desired. Double click on the title to change the name.

Keep this Power Query screen open for the next step.

Merging Subsequent Queries

We now have 2 merged queries. We can add the last one from within the Power Query screen.

In the Power Query ribbon, Combine group, select Merge Queries.

2. In the Merge screen, your Merged query will appear at the top by default. In the second dropdown, select the final table. Just like you did in the previous section, select the Name columns in both queries, and press OK.

3. Just as you did in the previous section, the final table appears at the top with a double headed arrow. Click on the double headed arrow to expand. And leave only Candy selected. Press OK.

4. Select Close and Load To… but this time keep the default selected: Table on a New Worksheet. Press OK.

Final Table

Our merged query is visible as a table on a new worksheet.

A Couple Notes

It helps if you think about your final table like a Pivot Table:

If you need to change something about the data, be sure to change the data source (one of the data sheets), not the merged query.
When you do change the data source, be sure to refresh the query. A simple way to do this is to right click on the merged table and select Refresh.

Thoughts?

What do you think? Did you like the video? I hope this was helpful. Feel free to send me an email anytime.

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

La’Rell Marion (picture coming soon!)