Category Archives: Excel

Advanced Filtering in Microsoft Excel

Take a guess… how many rows will have been filtered in Excel by now? I have no idea but I do know that filtering is used by millions of excel users worldwide. Why do we use this feature so much? I think that it’s because it has such a low learning curve and you get an instant result. The way it is presented is easy for the end user and you can apply multiple filters in one set of data. So why is there the distinction between Filter and Avanced Filter? To get a clear view on filtering it is important to know how to apply your filter. This is always a great topic in my Excel courses. We start of by applying the simple filters and soon I start asking questions which make filtering a lot harder than when we started. Let me explain.

Basic Filtering
Let’s take a look at an example. In the list below we are going to apply a basic filter by looking for Children’s Books. Go to the little arrow in the header of the category column, and in the dropdown
deselect the All option and select the option [Children’s Books].

Continue reading

Cascading Combo Boxes in Excel (Part II)

In a previous post (this one) we described how to create cascading combo boxes in Excel using data validation. You can achieve the same functionality in your Userforms in VBA. The advantage is that you can do the programming and the end user can create the various lists and maintain those lists on their worksheets. That is the combination we are looking for. In this post I will describe one of the many ways this can be accomplished. Here is how this can be done.

Cascading CB

Creating the lists
First we start of by creating the various list options. The first list is the application list. Here we write down the Office apps which we can choose from. The depending lists should only display the options belonging to the specific office application. To make it clear I colored the various lists. See picture below. When we choose Excel from the first combo box we only want to see the green options which belong to excel. When we choose one of the options from the second list we only want to see those options related to that list option.  In the example we choose “Functions” so we only want to see the options belonging to “Functions” as displayed in the light green list. If we choose another option from the first list we only want to see the colored option related to that color or choice made from the previous list. So if you want to try this then recreate the list as shown in the picture below. (click on the picture to enlarge)

Naming the lists
The next important step is to “name” the lists you have just created. Naming list is a good habit because it does not matter where you move the list the name will always point to the correct list. We will name the first list we have created “MOS_Apps”. The easiest way to do this is by selecting the list you have created and then click the name box once. So select “A1:A5” and typ in the name “MOS_Apps” and press Enter. Now we have to name all the other lists as well. For ease of use I have named the second lists to their counterpart application name. So do the following:

Select “C1:C5” – click the name box and type Excel – press Enter
Select “E1:E5” – click the name box and type Access – press Enter
Select “G1:G5” – click the name box and type Word – press Enter
Select “I1:I5” – click the name box and type OneNote – press Enter
Select “K1:K5” – click the name box and type PowerPoint – press Enter

Select “C8:C10”– click the name box and type Functions – press Enter
Select “E8:E10” – click the name box and type Tables – press Enter
Select “G8:G10” – click the name box and type SpellCheck – press Enter
Select “I8:I10” – click the name box and type Section – press Enter
Select “K8:K10” – click the name box and type Slide – press Enter

To keep the example simple we only took the first options from the second lists. This means that if you choose Excel and in the second combo choose Function you will see the options belonging to Functions. If you want to extend this with all the other options from the second list then you create a list with options belonging to “sheets” and name that list “Sheets” and so on for every other option.

Creating the Userform
With all the lists created and all the lists “named” we can created our userform with the combo boxes. So open the VBA Editor (press Alt+F11 or any other way you are used to get to the VBA Editor).

Make sure you are using the correct Excelbook by clicking once on the name. Now click on “insert” in the menubar and choose “Userform”. Click on the “combo box” control in the toolbox and click on the userform. Repeat this two times. Your Userform should now look like the userform in the picture below.

Setting the rowsource of the first combo box
The next important step is to set the rowsource of the first combobox. Select the first combo box and look at its properties on the left in the property explorer. Look for the option “Rowsource” and type the following =MOS_apps.

What you actually do here is referring to the named range you created earlier. Let us see if that worked as expected (see picture below).

Setting the rowsource of the second combobox
We don’t know what the row source of the second combo box is because we don’t know what option has been chosen from the first one. In other words the row source has to be set when the first choice has been made. Let’s say the user has chosen Excel as the first choice from the first combo box. The second combo box should in that case only show the options belonging to Excel. To achieve that you have to do the following:

Double-click on the first combo box. This will bring you to the change event for that combo box. Now enter the following code between the Sub and End Sub lines:

Me.ComboBox2.RowSource = Me.ComboBox1

What we are doing here is setting the row source at runtime meaning that after the first choice has been made the second combo box will be filled. Now let us check to see if that actually worked:

It did. Now the last combo box needs to be filled. We do this the same way as we did the second box with the following steps:

Double-click on the second combo box and between the Sub and End Sub lines write the following code:

Me.ComboBox3.RowSource= Me.ComboBox2

The third combo box will be filled with the appropriate list after the choice from the second combo box has been made. Let’s see if that worked:

It did!

Concluding:
You can easily create cascading combo boxes using lists from the worksheet. Name your lists and make sure you choose the change event of the combo box to set the row source.  Now the last question is how to deal with double “naming” options in the worksheet. We will tackle this in the next blogpost about cascading combo boxes.

Happy Cascading 🙂

Download the sample file:

CascadingCombosInExcel_II

Cascading Combo Boxes in Excel

Cascading Combo Boxes in Excel aka Depending Lists
Everyone who uses Access knows about the Cascading combo boxes they can create. What does this mean you might ask. Well suppose you have a combobox with several options to choose from. The next combobox also has options to choose from. Often you would want to see only the options that apply to the option chosen in the first combobox. In other words the second option you can choose depends on the first option you have chosen. Tip: click on the images to enlarge…

Combo_I AccesCombo_II_Access

 
Combo_III_Access

 

 

 

 

 

 

Now how is this done in Excel?
Well actually there are various scenarios here to choose from but in this first post I will show the the easiest one. In a next post I will show you how you can use this in your forms created from VBA.

Goal to accomplish
What we are trying to accomplish is the following scenario. We have a cell where we choose a  category option. In this case we would like to choose from the following options: Excel, Word and PowerPoint. Depending on the choice we have made we would like to choose from the following list the available features for that specific application. Ok ready to do this? Here we go.

Create a sheet containing the data as shown below

 

 

Now we have to ‘name’ the various sections. Let start with the category section first. Name the first selection [Category]. You do so by selecting  “E3:E5” and then click in the Name Box and type Category. Mind you that no spaces are allowed in the name you are going to use. Furthermore you have to press the [Enter] key to add it to the names.

 

 

Now repeat the same steps for the Excel, Word and PowerPoint columns. So for Excel select “F3:F8” and name the selection [Excel]. For Word select “G3:G8” and name this selection [Word] in the Name Box, finally for PowerPoint select “H3:H8” and name this [PowerPoint] in the Name Box
Now place the cursor in cel “B2”. This will be the cell where we are going to create a ‘dropdown box’ to choose from which contains the categories.
Now go the the “Data”-tab in the Ribbon and look for “Data Validation”. Choose the first option and set the following options:

 

 

 

 

 

 

 

 

 

 

 

 

 

When done you will see a little arrow appear next to the cell. Click the arrow and your dropdown box should now show the three options to choose from.

 

 

 

 

Ok, now we create a ‘dropdown’ box in cell “B4” where the various features show up which depend on the choices made in the categroy list. So if Excel has been chosen we should only see the options belonging to the list under “Excel”. To do so place the cursor in cell “B4”
Again visit the [Data Validation] button in the Ribbon and choose the first option. Now set the following options in the dialog you see as this:
For an explanation about the INDIRECT function i refer to the site of Chip Pearson who has a clear explanation about this:
http://www.cpearson.com/excel/indirect.htm

When clicking OK you will be notified that “the source evaluates in an error”. Click continue because that is a logical error because nothing has been chosen yet from the first combobox “Category”.

The end result: ok, let’s try this. First select a category from the category list and then select an option from the features list. You will see that if you change the category that the features list will change to the options belonging to the categroy chosen.

Cool right?

In a next blogpost I will show you how you can use this in a form in VBA and use the lists createdon a sheet. I will also show you how to use more combo’s being depended on others.

Be cool, be Excellent 🙂

Maus

Embedded Excel App

This is a new opportunity to share Excel functionality online. Just read about it on twitter so I thought I would give it a try.

I took the example of an earlier post ( this one ) and was curious to see if it would work. Well as you can see in the example here it does!

You can actually typ in values in this excel sheet. Go ahead and give it a try!

You want to try this in your own website or blog.. then go here and read all about it. Now that I know this is available it makes it much easier for us to explain some tips and trick that we share here. Only downside is that you don’t see the formula bar. I will dive in some more to see what the possibilities are!

IFERROR Makes your life much easier

I had the privilege to do beta testing for Office 2010. Excel is one of the apps I really like so I dove right in to see what could make my life easier in this version. One of the functions surprised me because for years I had been using something similar but 2010 changed all that. I’m talking about the function IFERROR. In earlier functions I would use IF ISERROR and then went on to check the conditions. If you have used this functionality in combination with VLOOKUP you know what I am talking about. A typical check could look something like this:

=IF(ISERROR(VLOOKUP(A1,C13:E25,3,FALSE));”No Match Found”;VLOOKUP(A1,C13:E25,3,FALSE))

or something similar to that.

In 2010 you can now shorten your formula using the IFERROR function. You don’t have to provide an else because Excel understands that it should show the first VLOOKUP result.  Now that’s what I call a real timesaver. Btw if you leave your ‘old’ function the way it is it will work as well.

Excel 2010 Inside Out

This is one of those book every Information Worker should have lying around on the workfloor. Everything well explained in a general sense. Also great material for preparing for the Excel 2010 Expert exam!

Want to take a sneakpreview in the book? Take a look at the MS Press Blog here to read chapter 14 “Every Day Functions”. Wow that is exactly the chapter i was looking for 🙂