Tag Archives: excel

Excel: Freeze And Stop Scrolling!

Freeze_Panes_IWhen working with long lists in Microsoft Excel you will probably want to see the top row all the time so you can see the labels. If you start scrolling down the default setting in Excel is to scroll the headers (labels) as well. That way you can’t tell what the column headers are when you are scrolling down. This is where the Freeze Panes functionality steps in. Far to often I see people struggling with this option while actually it is pretty straight forward to use. In this blog post I will show you the use of Freeze Panes to keep you from losing the list headers. (click on the images for larger views)

The goal…
Here is what we are trying to accomplish. In the picture below you see part of a list which has no formatting. So we want to keep the top row so when we scroll down the header labels will stay visible.

Freeze_Panes_II

As you know by default Excel will scroll down everything so you will not see your header labels anymore.

Freeze_Panes_V

Various options
There are various options to choose from. By far the easiest one is to create a table out of your list. Click somewhere in your list. Click on the Home tab and click on the Format as Table button.

Freeze_Panes_III

This will convert your list into a table. Now try scrolling down and see what happens with your headers? Wow the column letters disappear and are replaced by your header labels. How cool and effective is that!. If you don’t want to have the annoying filter arrows just remove the filter by clicking on the Data tab and the Filter button. Right out of the box functionality for you.

Freeze_Panes_IV

I don’t want my list to be a table!
I can hear you say that. Well in that case you can use the Freeze Pane functionality. We remove the format as table functionality and try to accomplish the same with Freeze Panes. Make sure you have placed your cursor somewhere in the list. Now from the View tab click the Freeze Panes button (arrow) and choose Freeze Top Row. Excel will automatically freeze the top row for you.

Freeze_Panes_VI

Scroll down…
You can now scroll down and your header labels will stay visible as requested. You can apply the same if you want to keep your first column visible. In that case choose the option Freeze First Column. Ok we are getting there! But here is the next challenge…..

I want it both ways!
Sure you want both ways because that is what Excel users do, scroll right and down. So what we are looking for is the top row frozen and the first column frozen. How to accomplish this.

Freeze_Panes_VII

In the picture above I have selected the first row and the first column just for clarification. You don’t have to select the first column and the top row to get things done. It is the position of the active cell that is important here. To freeze the first row and the first column you have to position your cursor in the correct cel. In this case you should set the active cell to B1 (highlighted yellow in the picture below). Now Excel knows it needs to freeze the top row and the first column.

Freeze_Panes_VIII

So it is of to the View tab and the freeze panes button. Click the little arrow and click Freeze Panes. Your first column and top row are now frozen!

Now for the last challenge….
I don’t want the first column frozen but I want the first three columns to freeze and the top row as well. To clarify I have selected the first three columns and the top row again. I have highlighted the applicable active cell that should be activated and after that it is a round trip to the View tab and the Freeze Panes button where you choose the Freeze Panes option.

Freeze_Panes_IX

Rule of thumb here
So basically the rule of thumb here is as follows. You place the active cell right to the column(s) you want to freeze. So if you want the first 7 columns to freeze you place the active cell in column 8. Same applies to your top rows. If you have more rows on top that you would like to see than place the active cell in the number of rows + 1. Example: you have three top rows you would like to see and seven columns you would like to freeze. You place the active cell in column eight and row four. Now you can choose freeze panes and you are set.

Unfreeze?
Freeze_Panes_XISimple as can be. When you had enough of all the freezing just choose unfreeze from the same View tab and Freeze Panes button. You are back to square 1. So nothing to select first just go straight to the View tab and click the Freeze Panes button!

Final thoughts…
Freeze panes is great functionality in Microsoft Excel. I think this should not be so hard to use. Every now and again I get feedback from my courses where someone has seen the light. Finally I get how this works. That’s what we are aiming for right?

 

 

Advertisements

CustomUI Editor Part II

Add your own icons to your own ribbon

In this post i will show you how easy it is to add your own icons to your own ribbon in Microsoft Office. For this example i will use a Word document but you can just as easy make this in Excel and PowerPoint. In earlier posts i wrote about the custom UI editor. This insanly great tool for creating your own ribbons in Microsoft Applications. So that will be the tool i am using in this post. You can download the custom ui editor tool here.

The example i am creating here is a simple Word document with a custom tab with custom icons.

First make a word document which you want to use for this example. I will call it: Custom Icons. The custom UI editor tool was last updated for 2010 but to show that you can easily use this for 2013 documents i created a 2013 Word document. So the document is created, saved in a location and we are good to go. You can click on the various images to see enlargements!

Open the CustomUI editor

The next step is to open the custom ui editor. Choose open en locate your document to which you want to add a custom ribbon. For my example i created an empty Word document with no fancy stuff attached. A plain word document. Next step is to choose a type of ribbon you want to add. For this example i’ll choose a custom tab.

CUE_I CUE_II

Now with that in place you will see that de custom ui editor has presented you with some sample XML in de code window. This is the code you have to modify later on. We will first add our own icons attached to the document. For that i will add some sample icons i have stored on my pc.

CUE_III CUE_V

these are the icons i have added to my ribbon:

CUE_IV

The first time i added some icons this way i was a little startled because i did not see anything happen. You have to click the plus sign to actually see that the icons are added to the document.

CUE_VI CUE_VII

Ok, we have the document, we have the icons all we need to do now is modify the existing xml which was created when we chose custom tab at the beginning. If you are like me and you don’t want to typ to much you will use copy / paste. There’s nothing wrong with that but remember that all the buttons should have unique id’s in order for the ribbon to be created. So copy paste and rewrite the id’s for every button. Als modify the imageMSO part. imageMSO is an internal reference to existing icons from Microsoft Office. Because we are using our own icons you have to reset this option to image=”iconname here” (where iconname here is the name of your custom icon!).

CUE_VIII

The last step we have to do is to check if our ribbon will actual show up in our document. For the sample i have set two icons to large and three of them to normal. This will give you the next output:

CUE_IX

Yep there it is, my very own tab in my ribbon with my very own icons! Wow how cool is that. All done with the custom ui editor!


Pitfalls
Is there anything else i should know before i start taking these steps or will it always work. The only thing you have to remember is adding icons this way the only restriction you have is the size of the icons. In the sample i created here i added icons which have a size of 48X48 pixels. You will just have to test what the maximum size is for adding it to your tab. My advise? stick to 48 x 48 and smaller. That size fits perfectly!


Happy customizing 🙂

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

Create Content That Gets Noticed

Documents, Presentations, and Workbooks by Stephanie Krieger
This is one of those books that really should be on your shelf. Of course we all create documents and think they look nice. Very often though we produce the final piece by using techniques we mastered ourselfs. But how about some structure in those techniques? Stephanie Krieger is one of those writers and users who knows exactly what we are talking about.  A dedicated Microsoft MVP so you know you get value for money. You can get a glimps of the intro of the book here and as you know by now there is always a sample chapter you can read on the MSPress blog. So if you are in doubt to buy this book or not… click here and let this chapter convince you.

Happy Reading

Maus