Tag Archives: Microsoft 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

Show – Hide Objects in Microsoft Excel

Selection_Pane_Excel_IVI wrote a blog post about the Selection Pane in Microsoft PowerPoint a while ago. You can read it here. Now since this is such a great tool for your Office applications I think you should use this in Microsoft Excel as well. But to my surprise a lot of people don’t know about this great feature in Excel. So to get this cleared I will show you how you can use this in Excel.

Scenario: a dashboard with some objects in it.
Suppose you are creating a nice looking dashboard in your Excel sheet. You add a couple of nice boxes, pivot tables and a picture. After examining the dashboard you see a couple of things you might need to add to the sheet. In most cases the shapes get in the way. You may have to move them around a bit to get to the part of the sheet where you need to add additional info. After adding the additional information you have to move back everything into its original place. This can be a time-consuming task just to make the dashboard look more like you want it to. Here is where the Selection Pane comes into play. Let’s take a look at an example.

Dashboard:

Selection_Pane_ExcelThis dashboard has several objects to play around with. Now to reveal those go to the Home tab and at the far right you will see the button which says “Find and Select“. From this button choose the last option: Selection Pane.

Selection_Pane_Excel_IIThere you go. You are presented with a selection pane which reveals all objects on that specific sheet. Now let’s see what we can accomplish by clicking on the little ‘eye’ icons on the right of the displayed objects. In our example we see that ‘rectangle 3’ is covering our cells. If you wanted to change the field name from the small pivot table just above the second slicer you had to remove the rectangle first. By clicking on the ‘eye’ icon of the rectangle 3 object you see that this is object is temporarily hidden.

Selection_Pane_Excel_III

You can make all the necessary changes you would like without having to move the rectangle out of its original place. When done all you have to do is click once more on the ‘eye’ icon in the selection pane and the rectangle will be shown again!

So instead of moving all your objects around your sheet to get the task done give the selection pane a chance to prove itself to you. You will see that it makes your live a whole lot easier.

MOS Master 2013 requirements simplified

MOMS2013So you are an Ace at Microsoft Word but not so in Microsoft Excel. Or maybe the other way around. You are the Guru at Microsoft Excel but use Word simply as a typewriter. You want to become certified and show your skills. You are ‘the Master’. But how do you become a master in Microsoft Office if you do not have the skills for both applications? Well be prepared to get certified because requirements to become a master have changed.

Less certifications to become a master!
Yes, it’s true. As of january 5th 2015 you need less certifications to become a MOS Master. Does this not devaluate the certification you might think? No way I will explain why it has become easier to get the master certificate and why it still holds its value.

Back in the good old days
You needed to be very good in Word, Excel, PowerPoint and Outlook (or Access) to become a master. Office 2013 did not make it any easier. It became even worse. You need to get an additional certificate to call yourself a master. A total of 6 exams is needed. Word and Excel was splitted into to expert exams. Wow you can imagine that a lot of people out there passed their chances when hearing about those requirements. I took the challenge and passed them all but what a challenge it has been!

Microsoft Word Expert – Microsoft Excel Expert
Better insights and feedback learned that it is not motivating trying to achieve a credential if you also need to take an exam in an area that you are not that comfortable in. So they split it up!

Become a MOS Master through the track that suits you well
You are great with Word right? Well choose the Word Track to become a master. You are the master of Excel? Well then choose the Excel track. You are good in everything (a true Office Ninja?) then take the expert track!

Requirements as of january 5th 2015

New MOS 2013 Master Certification
Track Required Elective (select 1) Total Exams
Word 77-425: Word 2013 Expert Part 1 77-422: PowerPoint 2013 4
77-426: Word 2013 Expert Part 2 77-424: Access 2013
77-420: Excel 2013 77-423: Outlook 2013
77-419: SharePoint 2013
77-421: OneNote 2013
Excel 77-427: Excel 2013 Expert Part 1 77-422: PowerPoint 2013 4
77-428: Excel 2013 Expert Part 2 77-424: Access 2013
77-418: Word 2013 77-423: Outlook 2013
77-419: SharePoint 2013
77-421: OneNote 2013
Experts 77-425: Word 2013 Expert Part 1 No Elective Required 4
77-426: Word 2013 Expert Part 2
77-427: Excel 2013 Expert Part 1
77-428: Excel 2013 Expert Part 2

data from: www.certiport.com (additional info available on the site here)

Legacy certification – Office 2010?
The above described requirement changes apply to Office 2013. But what about Office 2010? Well those requirements did not change. Those certifications requirements have only one expert exam to take for Excel and Word. Complete it with PowerPoint and one elective and you are good to go.

So what’s keeping you from jumping up and down. Get on those Excel boots, dust off your Microsoft Word basic skills and become a Microsoft Office Master in 2013! If you succeed post it here and let me know. If you need some additional tips take a look at the MOS Certification section on this blog.