Excel: Custom Dropdown Dashboard List with New FILTER Function 📊

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…

Filter in action

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

Source data: list of students and information in Excel

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.

List selected, source contains list seperated by commas

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

Dropdown list for favorite color

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

=Filter function being built in C2, as described below

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

Source Data, Columns A through E selected as Array

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

Filter function, as described in text

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

Favorite color selected, list has filtered

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

GIF showing the filter in action

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…

Interests as lists within cells

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

=FILTER formula, as described in text

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.

Filter in action

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:

0 in address column

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;;@

More number formats menu, custom selected, code listed below inserted

Much better!

0 is now blank space

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

Excel: Dynamic Dependent Dropdown Lists

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.

Two sets of dropdown lists

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.

Data tab, 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.

Desert Type dropdown

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.

Lists selected, as described

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

Formulas tab, create from selection

3. Check Top Row and click OK.

Top row selected

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.

Data tab, 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.

Data Validation screen, as describe in text

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.

Error message

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

Secondary dropdown list working in Excel file

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.

Another secondary dropdown, showing blanks

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.

Dropdown list slider

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.

Lists selected, as described

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

Find and Select dropdown, go to special highlighted

3. Select Blanks and click OK.Blanks radio button selected

4. Right click over the selected blanks and click Delete. Right click menu, delete highlighted

5. Select Shift cells up and click OK.

Shift cells up selected

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

Secondary list displayed, blanks no longer appear

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.

Data Validation Screen

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

Excel: Create Stunning Map Charts with Geography Data Types

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.

County populations displayed on a map

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.

List of counties and populations

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.

Insert tab, data types group, 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.

Locations identified, signified by a map icon on the left of county names

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.

Additional information about counties can be extracted at upper right

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.

Information extracted, as described above

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.

Insert tab, charts group, Maps 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.

Contextual chart design tab

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.

Kansas county by population map chart

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

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 + ; to insert date

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.

Ctrl + K to insert hyperlink

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.

Ctrl + ; to insert date

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.

Ctrl + a number for 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 + Shift + > to increase size

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.

Ctrl + drag to duplicate

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

Ctrl + Drag to duplicate

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.

Four headed arrow

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.

Ctrl + Shift + C to copy formatting

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 + Shift + V to Paste formatting

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 + Alt + V is paste special

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 + $ to apply currency format

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.

Ctrl + Shift + ! to apply number format

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.

File is Alt F

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

File Menu options

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

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.

Blank rows

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…

Auto fill handle

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

Autofill stops at blank rows

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.

Tables stop before blank rows

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

Select all

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

Find and Select, got to special

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

Blanks selected

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

Blank rows highlighted

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

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!

Blank rows gone

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.

What If analysis displaying scenarios

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

STart date and loss per day

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.

Data tab, What If Analysis, Scenario Manager

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

Add Scenario

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.

Scenario name, changing cells

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.

Scenario values

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.

Scenario list

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

Scenario list, summary button

Accessing Scenarios

You can access scenarios this way:

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

Data tab, what if analysis, 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.

Scenario Manager list

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.

Start date and loss per day affects total expenses and remaining balance

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

Start date and loss per day affects total expenses and 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.

What If analysis displaying 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

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.

Grayed out buttons in ribbon

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…

Grayed out buttons in ribbon

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

Grayed out buttons

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.

Icons

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

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.

Save As screen file type drop down

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

Save As screen file type drop down

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

Save As screen file type drop down

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

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.

Power query screen

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.

Date

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.

table setting

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.

Data tab

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

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

x next to changed type

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

close and load to

6. Select Only Create Connection and press OK.

only create connection

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.

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.

Combine data, merge

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

select tables

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

Select name column

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.

double headed arrow to expand

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

select animal only

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.

CHange name of column

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.

Merge

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

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.

only candy selected

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

Close & Load Screen

Final Table

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

Final Table

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.

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

Top 10 Secret Key Commands in Microsoft Office

Whenever I mention one of these shortcuts in a session, I see people taking out their pencils to write them down. Most of us know some commonly used keyboard commands in Microsoft Office; Ctrl + Z to undo, Ctrl + C to copy, and so on… but I have some lesser-known favorites to share with you today. Also, take a moment to check out this article, which contains some more general shortcuts.

Header by pressing alt + ctrl + 1

 

F12 = Save As

If you have taken any of my Essentials sessions, you know this one, one of my absolute favorites. Remember in the old days when you used to be able to select Save As from the menu, and you would be taken directly to a dialog box with everything you wanted in one place? If you are like me, you found that box to be way faster than clicking through the options one by one in the new File/Backstage menu.

So just press F12. It does everything you want, and works in all your favorite Microsoft Office programs.

Save As screen

F7 = Spell Check

Want a quick spell check? No need to visit the ribbon, just press F7.

Word Spell Check

Especially useful in Excel, where we don’t get the red squiggly lines underneath misspelled words like we do in Word.

Excel Spell Check

F7 will also work in PowerPoint

PowerPoint Spell Check

Alt + Q = Find Features

This is a big one. Sometimes you know that a feature exists in Microsoft Office, but you are just not sure where to find it. Press Alt + Q to search for it.

Forget how to get to the Accessibility Checker? Press Alt + Q and search for it…

Find Features

While I still think it is a good idea to know where features live in the ribbon, this is an excellent tool to find what you are looking for in a hurry.

Ctrl + Y  =  Redo

Companion to the famous Ctrl + Z (undo), Ctrl + Y gets a lot less fanfare, and I have no idea why. It is a great cheat if you don’t know a key command for something. Ctrl +Y will simply recreate the last action.

Let’s say you want to insert a row into Excel in a variety of different places. Maybe you don’t know the key command (Shift Ctrl + +), so you insert one row… with the old-fashioned right click , Insert.

Right click menu, insert

To insert another row,  go to the new location, and hit Ctrl +Y. Click on the next location, Ctrl +Y, and so on. This will be much faster than performing the old “Right click, Insert” method 20 or so times.

New row with Ctrl + Y

It also works for formatting. A couple weeks ago, I was editing a Word document with quite a few pictures that I wanted to have identical style formatting. I selected one picture, applied a style format, then selected the remaining pictures, pressed Ctrl + Y and voila! All the same formatting was applied.

picture formatting. Ctrl + Y to reapply

Note: Ctrl + Y will only apply the last action, so if you were making several format changes to an item, you might be better off copying formatting with the Format Painter. Please attend an Excel, Word, or OneNote Essentials session to learn more about this amazing feature!

Ctrl + Home/Ctrl +End = Go to the Beginning or End

Need to get back to the top of a document, or conversely, need to find the end? Try Ctrl + Home or Ctrl + End to navigate.

Ctrl + Home will take you back to the top of your document, and Ctrl + End will take you to the bottom.

End of a row

Note: this will require that you are using a full keyboard… sometimes the Home and End buttons are not present on smaller keyboards or laptops.

Ctrl + Shift+ (arrow) = select an entire column or row

If you have attended Excel Advanced Formulas you know this one. Ctrl + Shift + Down Arrow will select all the data in a column. This also works with a right arrow to select all the data in a row. And in Word, it will select an entire row of data at a time.

All data selected in column with ctrl + shift + down arrow

Outlook: Ctrl + Shift + M = Create Email

I mentioned in a previous article that Outlook has some amazing time saving shortcuts. This one and the next one are my two favorites.

Regardless of the Outlook module you are working in, Ctrl + Shift + M will create a new email message. No need to return to the mail module.

Below I was in my Calendar module, and started an email with this command.

Create Email

Outlook: Alt + S = Send Email

Once you are done typing your email, don’t touch that mouse… Alt + S will send the email.

There have been occasions where someone was standing behind me as I used this command and the last in Outlook, and they exclaim, “How did you do that??” The wizardry of key commands!

Word: Alt + Ctrl + 1 (or 2, or 3) = Format as Heading

We are all utilizing headers after attending Word Essentials, right? Well, here is a quick way to set a line to Heading 1, 2 or 3: Alt + Ctrl + 1 (or 2, or 3).

Header by pressing alt + ctrl + 1

Excel: Ctrl + T = Convert Range to Table

After you attended Excel Essentials, you ran back to your computer to convert all your ranges to tables, right? The functionality we see for tables goes even beyond what we see in Essentials and Pivot Tables sessions. It is just a good practice to start utilizing them.

Instead of using the Table feature from the Home tab of the ribbon, place your cursor in the middle of your data and press: Ctrl + T.

Ctrl + T converts to Table

Of course, you can still make adjustments via the contextual Table Tool tab.

Table Tools Tab

Thoughts?

What do you think, are any of these one of your favorite shortcuts? Or do you have a different favorite secret shortcut to share?

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

  • Kaleb Basham
  • Joan Wilson

Microsoft PowerUp! Videos Posted: Check Out What’s New in Office 365

Almost 150 people attended the special Microsoft PowerUp! Sessions…  If you missed them, or even if you attended and wanted to revisit some of the special content, videos are available now!

The purpose of these sessions was to give everyone a quick update on some of the highlights now available (or coming soon, depending on your update schedule) in Microsoft Office 365. Keep an eye out for future sessions as more updates are released…

PowerPoint Updates

Word Updates

Excel Updates

Bonus Content: Sandy

This is Sandy (Power User!), with a very kind testimonial. 😊😊

 

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

 

Erin LeBegue (not pictured)

Chris Leonard

Chris Leonard

 

Tyra Miles

Tyra Miles

 

Marsha Compton

Marsha Compton