Nothing is more exciting than new functions in Excel, and if you agree with me on this, I have two pieces of good news for you today.

One is called GROUPBY and one is called PIVOTBY, and I think you are going to fall in love with both of them.

Microsoft 365

One word of caution: you will need to be using Microsoft 365 to have access to these functions. Unfortunately, these are not available in older versions of Excel.

Exercise File

If you would like to try out these functions on the data example I am using in my screenshots, I will include a workbook below. That said, there are many potential uses for these functions, so I am hoping this also inspires you for your own workbooks!

Exercise File: GroupByPivotBy

There are three sheets in this workbook: one sheet contains a space for you to try out these functions, and the other two contain the solutions for you to check your work.

GROUPBY

Excel’s new GROUBY formula will allow you to summarize a data set by category; most commonly this could be a sum of values, but you are not limited to this: you could just as easily use another function for calculation, like average, or count.

Here is an example with some data you were introduced to in Excel Pivot Tables sessions: a fictional student list.

From this list, I wanted to summarize the scholarships by college, so I built a simple GROUPBY function to accomplish this task:

Here is a closer look at that GROUPBY result:

Construction

The official syntax of this function is:

GROUPBY(row_fields,values,function,[field_headers],[total_depth],[sort_order],[filter_array],[field_relationship])

With these new functions there are always copious options, so don’t let yourself get overwhelmed by the potential number of arguments in this function.

My Example

In this example, I built the formula with only the first three values: Row, Values, and Function. You can click on this image for an enlarged view.

I built the function in cell F2 (yes it lives in one cell, more on this in a moment): =GROUPBY(B2:B40, D2:D40,SUM)

This translates to:

• B2 to B40: row fields
• D2 to D40:  the values to calculate
• SUM:the function I want Excel to perform with those values.

From Microsoft

Here is some additional guidance from Microsoft. Note that only the first three fields are required. The rest are extras (although also worth checking out): GROUPBY function – Microsoft Support

PIVOTBY

PivotTables will always have a place in our hearts, but sometimes you prefer (or need!) to work with a formula. Now you can pivot without the table with the PIVOTBY function.

Here is that same starting data again:

Here is a PIVOTBY Function, with “college” set for rows and “sports team” set for columns:

Here is a closer look at that function result: a PivotTable without the table! 😲

Construction

The official syntax of this function is:

PIVOTBY(row_fields,col_fields,values,function,[field_headers],[row_total_depth],[row_sort_order],[col_total_depth],[col_sort_order],[filter_array],[relative_to])

My Example

I built this function in F2: =PIVOTBY(B2:B40,C2:C40,D2:D40,SUM)

Notice how it starts similar to GROUPBY but has an additional argument for column.

This is how this translates:

• B2 to B40: row fields
• C2 to C40: column fields
• D2 to D40: the values to calculate
• SUM: the function I want Excel to perform with these values

More detailed explanation of each potential argument can be found here: PIVOTBY function – Microsoft Support

As is the case with GROUPBY, not all fields are required. For instance, I only completed the first three in mt example: Row, Column, and Values.

Reminders/Tips for Working with New Functions

Here are a couple reminders for working with new or unfamiliar functions.

Function Arguments

After you create your function, but before you have entered values, go to the function arguments key (signified by “fx” next to your formula bar):

This is a great tool to walk you through unfamiliar fields, or just to allow you to explore the full field options. With both GROUPBY and PIVOTBY there will be a scroll bar with additional options.

Array Functions and #SPILL

Both GROUPBY and PIVOTBY are array functions, so you build them in one cell (F2 for instance), but they will spill into surrounding cells.

This means that, if something is in the way where Excel needs to spill content, you will see a #SPILL error. Deleting the content that is blocking the array to spill will resolve this error.

New Functions: Training Available

If this has inspired you to learn more about the new functions available in Excel 365, visit myTraining and look for a new course called Excel 365: New Functions. 

This asynchronous course covers the new 365 functions XLOOKUP, UNIQUE, SORT, SORTBY, FILTER, and TEXTJOIN. It is designed as a supplement to the Excel: Advanced Formulas live sessions that are also listed in myTraining.

Check it out and let me know what you think!

 

Have you heard about the fantastic new Functions for Excel in 365? XLOOKUP, SORT, FILTER, UNIQUE, to name a few. Speaking of unique… a particularly unique case came up recently presenting the opportunity to create a dashboard of sorts using Excel’s new FILTER function. I had to share this with you, because this was a very cool setup. We ended up with a sleek custom list on one sheet that, prompted by dropdown selection, pulled data from the source into an array formula. Read on for more information…

Before you do, I want to thank Brittany for this question (along with her patience with several tests and fumbles) for inspiring this byte.

Download Workbook

Here is a copy of the workbook used throughout this writeup: FILTER Function Example Workbook

There are three sheets in this workbook:

• Favorite Color: filters source list by favorite color
• Interest: filters source list by interest: this one searches a list of values within the cells
• Source Data

Source Data: The Background

This case involved a sheet of Source Data that contained student data: names, details, etc.

We needed to give end users the ability to isolate a list of student names by a specific student interests. A filter or slicer would not work in this case, as there were multiple potential values for “interest.” A student could list one, or several. We wanted to create a dashboard sheet with a dropdown list that would allow users to select an interest and generate a list of results.

I have also seen this sort of situation occur with exported Qualtrics survey results, if multiple responses are allowed. So if you are a Qualtrics fan, you will want to know about this!

Data Validation

Start with the dropdown selection that the unique list will reference:

• Click into the cell where you would like the dropdown to appear. In our case, A2.
• In the Data tab, select Data Validation

• In the Data Validation popup, change Allow to List, and either list the items you would like to appear in the dropdown, or select cells that contain your list.
  • In this case, type the list out, separated by commas.
  • Be sure the spelling matches the source data. This will be important for the formula step.

And voila, there is the dropdown list! On to creating formulas…

By the way, you can read about some more cool Data Validation tricks here: Dynamic Dependent Dropdown Lists.

Also, we talk about Data Validation in more detail Excel Advanced Formulas training, so I would love to see you in a session if you want to learn more about this feature.

=FILTER Function

The =FILTER Function is new with Office 365. This is an array formula, so it will fill into the cells to the right and below where you build the function.

As an introduction, start with the Favorite Color sheet, a more straightforward example.

• The formula is built in C2. If you have the example file, click into C2, and take a look at the formula bar.
• The anatomy of the  =FILTER Function is: =FILTER(Array,include,[ifempty])

• • Array is the cells you want to return if the conditions are met. In this case, Columns A through E in the source data.

• • Include is the filtering statement. Where would you like Excel to look, and what value is it looking for? In this case, the source data sheet, “Favorite Color” column should equal the value in A2, where Data Validation lives. For the example file it looks like this: “Source Data”!D:D=’FavoriteColor’!A2
    • • Note: because the source data is in a table, the range is named “Favorite Color”, instead of being a column name.

• • The last step is the easiest. If empty: what value would you like to appear if there are no filter results? Perhaps “No Students Listed”, or even empty quote (“”) to leave blank.

Notice how, although we built the formula in C2, it extends into the cells beyond. The beauty of array formulas!

And thanks to Data Validation, adjusting the =FILTER formula will be easy.

If you are interested in reading more about=FILTER, here is what Microsoft has to say: Microsoft’s Writeup about =FILTER.

Special Case: Search/Filter by Text that Contains a Value

I mentioned previously that there was an additional complication with our case, as the results appeared within a column, with multiple values listed for “interest.” So a simple filter would not have yielded results…

One excellent source for Excel dilemmas like this is the site ExcelJet. They had an excellent solution to this quandary that I will explain more below, but here is their article: Source: ExcelJet

Solution

Their solution was to nest =ISNUMBER and =SEARCH into the =FILTER function. They explain in fantastic detail in their article above, but this combination of functions will effectively ask Excel to search if a value exists in the contents of the cell at all, even with other values present in the list, as in our case.

Here is the formula for our situation, built in C2 of the Interest sheet.

=FILTER(‘Source Data’!A:E,ISNUMBER(SEARCH(Interest!A2,’Source Data’!E:E)),”No Students Listed”)

The generic formula is:

=FILTER(rng1,ISNUMBER(SEARCH(“txt”,rng2)))

See the Interest tab in the Workbook Download above to see this formula live and in person.

Contending with Zeros

If you have worked with array formulas before, you may have noticed that, when there are no results, a “0” is returned. For instance, if the address cell is blank in the source data, it will look like this in the =FILTER result:

You can employ a custom number format here if you would like the 0s to display blank.

1. Select the entire column.

2. Under Number formats –> More number formats, select Custom

3.  Under Type, insert this code: 0;-0;;@

Much better!

Thoughts?

What do you think about this trick? Do you think you will find a use for this combination of features and the new =FILTER function? I would love to hear from you. Feel free to drop me a line!

Congratulations, Power Users!

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

In Advanced Excel training, you learned about creating dropdown lists using Data Validation. That was pretty handy on its own… but what if you would like to create a dynamic dependent dropdown list?

For example, below I would like to select a Dessert Type in Column A, and have only those specific desserts appear in my secondary list in Column B.

This can be an incredibly useful tool; imagine lists of cities by state, or students by class, and so on. Lets see how we can set up this slightly more complicated Data Validation.

Exercise File

Interested in following along? Use the exercise file below.

DependentDropdownExercise

Primary List

When you attended Excel: Advanced Formulas with me, you learned all about Data Validation. The first part of this setup will feel just like what we did in training.

1. Select all of Column A.

2. In the Data tab, select Data Validation.

3. In the popup:

• • • Under Allow, select List. 
    • Click into the Source box
    • Select the cells with the dessert types: E1 through J1. Click OK.

All the cells in column A now have a dropdown list of dessert types.

Dependent Dropdowns

So far, this is nothing new… but remember that your goal was to create a dependent dropdown list: i.e. when someone selects “Cake” as a Dessert Type,  you would like only the cake options to appear, and so on.

Named Ranges

1.  Select the entire range that contains the lists: E1 through J12.

• • Notice how Cake has more options than any other type: we will address that later, but for now, cast as wide a net as possible to include all the desserts.

2. In the Formulas tab, Defined Names group, select Create from Selection.

3. Check Top Row and click OK.

This is going to create named lists based on that top row. Which could have a variety of uses… one will be what we will do next.

Apply Data Validation

For Column B, similar to the first step, you want to again apply some Data Validation, but this time, the options you want to appear need to be based off of what users select in Column A.

1. Select all of Column B.

2. Revisit Data Validation: go to the Data tab and select Data Validation.

3. Change Allow dropdown to a List, just like in the first Data Validation

4. For Source, instead of selecting cells, insert the formula =INDIRECT(A1).

• • This is going to reference cell B1 to look at A1, and through the magic of Relative References (also discussed in Advanced Formulas training), each subsequent row will reference the cell to the left of it to access those named lists you established in the “Named Ranges” step.

5. If you receive an error message that the Source “evaluates to an error,” click Yes.

• • Don’t worry, this error is not an issue; the formula is currently evaluating blank cells in Column A and doesn’t know what they reference.

The fun part: try it out!  Select a Dessert type in column A, and look at the customized options that appear.

We still have work to do, though. Remember how we have lists of different lengths? This is especially apparent when you pick a shorter listed dessert type, like Confections.

This leads to some trailing blank spaces in those named range lists. We will get rid of these blank spaces in the next step.

By the way, if it looks like you can’t see all your options in that secondary list, there is a slider bar on the right… At this stage, you might have to scroll up on some of the shorter lists. Again, we will fix that shortly.

Remove Blanks

You might remember this trick from a previous Byte, which covers how to Remove Blank Rows in a Document:

Excel: Remove All Blank Rows in a Document

In this case, we are going to remove a few blank spaces with the goal of cleaning up those dependent dropdown lists that contain blanks.

1. Select the range of cells surrounding your lists.

2. In the Home tab, find the Find and Select dropdown and select Go To Special

3. Select Blanks and click OK.

4. Right click over the selected blanks and click Delete.

5. Select Shift cells up and click OK.

That takes care of the rogue blanks in the dropdowns in Column B. Much better!

Cleaning up the Header Row

This is optional, but if it bothers you that your header (in this case cells A1 and B1) both contain unnecessary dropdowns with Data Validation, you can remove those as a last step.

1. Select Cells A1 and B1
2. Go to Data tab, Data Validation.
3. Select Clear All in the lower left, and click OK.

One More Thought

In real life, you would likely set this up with your source lists on a separate, hidden sheet. That would look much cleaner than having all the lists in view, like we did here. But for the simplification of this example, I included them all on one sheet here.

Thoughts?

What do you think, will you use dependent dropdown lists in your workbooks? 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

• • Caroline Beach

Here is a question that has been particularly popular recently. How do you create a map charting figures by county, city or zip code in Excel? There are a variety of ways to tackle this question, but today I want to show you how to use data types in conjunction with the map chart type to create a chart by county in Excel.

This Byte is inspired by two different faculty members who emailed me with this question… you know who you are, and thank you for the inspiration!

Starting Place: The List

Your starting place should include clearly named locations and associated figures. In my case, I picked a few Kansas counties (sorry if I left out yours!), and their populations.  The numbers could have been any figure you are tracking, of course.

Step One: Convert to Geography Data Type

The first step is to make sure Excel can identify the locations listed in your document. This can be an optional step, but skipping it may mean that Excel can’t identify one or more of your locations and the map therefore won’t cooperate, so best practice is to start here.

1. Select the data to be identified (counties, in this case).

2. Go to the Data tab, Data Types group and select Geography.

3. Excel will attempt to identify the locations. If all goes well, a little map icon will appear to the left of the county name.

By the way, notice the little box at the upper right of your selection. Click on this to extract other pertinent information about your location. This is not part of your chart, but a cool trick in Excel worth mentioning.

Here are a few extracted fields, so you can see how they look: image, largest city, area. Notice how Excel creates a new column for each one.

Here is a bit of inspiration: imagine that you have a list of zip codes and figures and you need to create a map by county. Simply convert the zip codes to geography data types, extract county, and off you go. Pretty handy, right?

Step 2: Create a Chart & Customize

Now that Excel has identified our data, we are ready to create a chart.

1. Select the data to be charted. In this case, county and population columns.
2. Go to the Insert tab, Charts Group
3. Select the Map dropdown, Filled Map option.

4. Excel will create a map with your data. If you don’t care for the default colors and appearance, don’t forget you can customize all your charts with the contextual Chart Design tab in the ribbon.

Shameless Training Plugs:  If you would like to learn more about the Chart Design contextual tab, please attend one of my Excel Pivot Tables, Charts and Pictures sessions (now offered remotely). Also, don’t forget you can easily change your theme colors on the Page Layout Tab in the ribbon. We cover this one in Excel Essentials if you are interested in learning more.

It may take a few tries to get a map you are happy with. Don’t be discouraged! The results are fantastic.

One More Word on Geography Data Types

In the example above we mapped by county, but you may find yourself needing to map by zip code, state, country… the Geography data types can recognize all of these and more. Experiment with this powerful tool and you may be surprised what you find.

Thoughts?

What do you think, do you have any geographic data that is calling out for a better visualization? I can’t wait to hear how you use this feature!

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

• Debbie Neill
• Kelsey Unruh

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

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

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.

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