Category Archives: Office Tips and Tricks

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?

 

 

Office: Remove Background From Picture

Remove_BGThe Office suite has many possibilities to refine your documents. Sometimes you want to add that nice looking picture but it needs a little tweaking. If you have the experience and the tools you can use Photoshop to edit your pictures. If you do not have Photoshop or the knowledge to work with Photoshop you can use the build in functionality of Microsoft Office. In this blog post I will show you how to remove the background of images so you can use them in your Office documents.

Insert Picture
For the first example I am going to insert a picture in a blank PowerPoint slide. Remember this can also be done in  Word and Excel. We start with a simple picture. A crack in a wall where we want to remove the background from.

Remove_BG_I

When the picture is inserted you will see an additional tab appear in your ribbon. This format tab is just the one we need for our task. With the picture in place we click on the contextual format tab. There you click the Remove Background button. This will present you with another contextual tab as shown below

Remove_BG_II

Overview of actions
At this point something interesting will happen. You picture becomes a ‘negative’. A purple color with some handles to play around with. The magic happens on the left on the slides sorter where you can see a preview of your outcome. So while moving the handles in your picture your preview will be updated constantly.

Remove_BG_III

Easy background removal
I know what you are thinking… This one is easy! It is.. so let’s try a more difficult picture. So new picture same task. This time I will insert a traffic light where I want to remove the background.

Remove_BG_IV

Same technique same handling
We want to get rid of the blue sky and that pole thing on the bottom right. So take the same steps. Activate the contextual tab and move the handles so it fits your needs. Keep your eye on the preview on the left.

Remove_BG_V

Now for a difficult one
Remember it takes a little patience to get the result you might be looking for. The key tip here is Zoom in, Zoom in and Zoom in. The next picture is one of my favorite pictures just because of its simplicity.

Remove_BG_VI

Now remove that background
In this picture we need to completely remove a building from the background. That’s a whole different ballgame. Let’s see what we can accomplish. First we activate the Remove Background contextual tab. Now we are of to the tuning part. Remember Zoom is the keyword here.

Remove_BG_VII

Starting point
When the remove background tab is activated PowerPoint will propose a first step. Not bad but it shows a couple of twitches that need some additional tuning. Everything that is ‘negative’ will be removed from the picture. In this case the ‘ONE WAY’ signs have some tweaking that needs to be done. To solve that the first steps I take is to set the ‘square’ a little bigger by moving the top handles up a bit and the bottom handles down a bit.

Remove_BG_VIIIRemoving ‘the rest’ – tuning
The first steps I took was zooming in on my picture. You can do this simply by using the zoom + sign on the lower right of your PowerPoint screen. Now I need to get rid of the purple strip on the white outlining of the ONE WAY sign. I click the button ‘Mark areas to keep’. This will change you mouse to a ‘pen’. With this pen you can draw over the area to keep. So in this case I want to keep the white outline of the sign.

Remove_BG_X

Select just a little more…
To make sure PowerPoint gets what I want I select just a little more white border than needed. Now PowerPoint will understand that this is part of the outline. In your picture you will see the purple area disappear from the outline just as you wanted. But what if I want to remove areas instead of keeping them? Well in that case choose the button ‘Mark areas to remove’. Instead of a + sign in your line you will see a – sign in your selection line. That way if you have a very complicated picture you can always detect what areas you have selected to keep and which areas should be removed.

Remove_BG_XI

Cropping the final part
I usually set the selection handles to a wide range. The final step is to ‘crop’ all the areas I don’t need in my picture.

Concluding
Sure it takes a little effort to get the result you are looking for. But remember easy pictures show instant result. More complex pictures take more tweaking. I think the provided default functionality offers a lot of possibilities without having to dive into Photoshop to get the same result. If you have simple pictures and need to keep going I would say give it a try!

Autonumbering In Your Word Document

Numbered_List_IIf there is one option in Microsoft Word that I have seen users use various techniques for it has to be autonumbering. Backspacing and pressing the Enter key are on top of the list. Most times I will explain that a lot of options in Word are simply On and Off options. Autonumbering is no exception. The big disadvantage to messing with the autonumber feature is that it can cause nightmares if you haven’t applied it correctly. In the end you have to spend a lot of time getting the numbers right if you have been backspacing and entering the whole document. Let’s show how to avoid this.

Start numbering
Start by creating a new document and typing a 1.  (that is a 1, a dot and a space). Type some text to get started with and after that press the Enter key. You will automatically get a 2. showing up in your document.

Numbered_List_III

That’s great. We get the famous thunderbold sign offering the various options to choose from. Nope that are not the options we need so we type the text for number two and after that we press Enter again.

Numbered_List_XII

Now here’s where the interesting part comes into play. Suppose you don’t want number three yet and you first want to add a paragraph with text before adding number three. Here is where most people start with tampering the list. I will show you the various options and what the specific differences are. I have added a square around the button that is activated in the ribbon. Keep that in mind.

Using Backspace
Here’s what will happen if you use the backspace key. The 3 will disappear and the indentation will change.

Numbered_List_IVThe indentation is often the problem. Your text will continue below the I from Inserting. If that is what you are after a backspace could be the way to go.

Using the Enter key
Here’s what will happen if you use the enter key. The 3 will also disappear and the indentation will also change.

Numbered_List_VBy pressing the Enter key another option comes into play. Not only will the indentation change but the style will also be reset to normal. This is important to know.

Using the autonumber button in the ribbon
The final option you can use is the autonumber button in the ribbon. This will give you the following layout:

Numbered_List_VI

As you can see the 3 will also disappear and the indentation will be set below the previous numbering aligning the text with the previous numbers.

NOTE:
It is vital to say that there is no wrong option here. You have to decide what fits your needs best.

Adding the third option
After you added your text you can press enter again. Now let’s add another number and preferably we would like to continue the numbering with number three. The easiest way to do this is click on the autonumber button on the ribbon again.

Numbered_List_VII

This will add a one to your document again. But this is not what we are after. You will see the thunderbold icon appear again. By clicking this option you will be surprised to see the option that will be offered. “Continue numbering” exactly what we are after!

Numbered_List_VIII

Thunderbold option disabled?
Now what if you have disabled the thunderbold options? No worries you will see the same option if you right-click the mouse. You will see the following menu with a separate section for autonumbering.

Numbered_List_IX

Continue where you left…
With that taken care of you can take the next step. Add more text! If necessary add more paragraphs in between numbers and use that simple autonumber button on your ribbon. Need a number? Click the autonumber button, don’t need a number but just want to add a paragraph of text? Click the autonumber button again. Need to continue numbering again? Click the autonumber button on the ribbon again, use the thunderbold option to continue numbering or choose from the right-click menu options.

Numbered_List_XI

No more tampering the autonumber!
So by simply clicking the autonumer option in the ribbon to On or Off you will get perfectly numbered documents. The great thing is that if you use the button on the ribbon you can easily add numbers in between. Try it… Press enter after the second option (2.) You will see that a number three ia added and every number afterwards is automatically incremented. This is how we want to see numbering in your documents!

Happy numbering 🙂

 

 

 

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.

Word Online – Save As, Rename or Not?

Word_Online_SaveAs_XIIf there is one thing every user knows about Microsoft Word it’s the fact that you can Save your documents or you can choose Save As. So what happens when you take that away from them. Exactly you create chaos! In the last year innovations follow-up rapidly. You have Office, Apps, Office Online, 2013, 2010, 365…. You tell me what I am using. When working with your favorite applications it is important to know which version you are working with. If you do, you know the limitations that specific version has. For example let’s meet the Word Online version.

Save As, sure…
Let me dive straight into it. I am going to make a new document in Word Online using My personal OneDrive. So I start up OneDrive and choose “Create” and from the list I choose “Word Document”.

Word_Online_SaveAsEasy enough right? I add some text to the document and after a couple of seconds I see that Word has saved my document. You can see this at the bottom of your document:

Word_Online_SaveAs_IIIOk, I am fine with that. But I think this will be saved as something like ‘document1’ or something like that. That’s not what I am looking for so I want to rename this to something more meaningful. So the next logical step would be to go to File and choose Rename or Save As. Let’s see if we have that option:

Word_Online_SaveAs_IVYes, there it is. Exactly what I am looking for. So I choose Rename and give my document the proper name. No worries here!

OneDrive for Business
Now here’s the challenge. Let’s try this using OneDrive for Business. Same steps. I open OneDrive for Business (your famous My Site) and click on the ‘Waffle’.

waffle

Great, now choose Word Online and start creating some text. I know Word will save my document automatically so I don’t have to worry about that. Now all I have to do is going to File and rename my document to give it a meaningful name. So of to File it is…

Word_Online_SaveAs_VIII

Surprise…!
What? I am presented with two options here, Download a Copy and Download as PDF. I don’t want either of these I just want to rename this to something meaningful. Luckily Word is telling me my document is save automatically… yes to document1 and that’s not what I want.

Return to your document and have a look at the title bar of the document:

Word_Online_SaveAs_VYou will see the document name presented in the title bar. Hover you mouse over the title and a label ‘Rename File’ will appear. By just clicking on the document name you are able to rename your file. Phew, close call but I can rename my document to something meaningful this way.

Conclusion:
Whenever possible I use the client software. This offers me all functionality I am used to all these years. If presented with other option such as Word Online I do experiment with that to see if it can be an addition to my work tools. What surprises me is the fact that functionality that is available in OneDrive is not available in OneDrive for Business. I am training people right now in using Office 365 and using Office Online. So far 2 out of 10 people discovered that this is the way to rename their documents. For the other users their documents became ‘Document1, Document2 etc…’. So somehow it doesn’t seem that intuitive at all. So make sure you know the limitations of your work environment and you will be good to go 🙂

 

TechNet Wiki Summit – This is for You!

Banner2_jpg-550x0

First things first. This Summit is for you and is Free to attend! Go to this page and register. Have a look here at what this Summit has to offer you. Date:  March 17-18-19, 2015. Well with that done let me explain a little about TechNet and TechNet Wiki.

Community work
One of the things I like to do when I have some spare time is write blog posts about the technology is use in my work related activities. Before you can answers questions or help people with their technology related issues you have to gain the knowledge yourself. Well let me tell you the TechNet environment is just the place to start. Whether you just want to absorb or really want to participate this is the place to be.

TechNet Home
Sure every site has a starting point. TechNet is no exception. Here we go… http://www.technet.com
TechNet_IINow have a look. The Home page does offer some interesting options. But have a look at the menu bar. That’s where the real magic is. The Third option “Wiki” has a ton of information at your disposal. Credo here? Can’t find it – Write it! How cool is that.
TechNet_IIITake your time
Let me tell you, this is not the place you explore in a couple of minutes. There is so much going on that might take you a while so here’s the tip: bookmark this place and come back regularly. All activity you see here is done by enthusiasts, evangelists and people who want to be involved. You can be a part of that as well. Tip: half way down this page you will find a link to the Wiki Ninja’s blog. Have a look there and see how much activity is going on!

Gallery
So what could this option be? Some sort of show off directory what everyone is doing? No way this is the place where enthusiasts share their knowledge. Thousands of free downloads contributed by volunteers. Looking for a script to print all AD users to an Excel file? It’s here. Looking for free Powershell scripts? It’s here. Go on have a look and see what’s available for you. I’ll bet you find what you have been looking for all this time. Same credo here… can’t find it, write it and contribute it here.

Forums
For years I have been active in Forums and this one is no exception. Users from all around the world post their questions here and it is up to the community to answer them. Let me tell you if you post a question here it will be answered!
TechNet_IForums are the place where you go to if you have a question and would like some help from other people who just might know the answer. Just about every IT related subject can be a topic. I have to say that by just reading answers from all those volunteers you will gain a lot of knowledge as well. Best thing would be to participate. And don’t be fooled you will be taken seriously here no matter how simple the question might be.

Summit Tracks
I hope this post will give you some insights in the TechNet community. As I stated earlier the upcoming Summit is organized entirely on a voluntary base. You can choose from various tracks:

TechNet_Dev_Track

TechNet_Wiki_Track

TechNet_Infra_Track

Well all I have to say now is I’ll be there… will you?

p.s. you don’t even have to leave your house for it 🙂

Outlook: Share a custom contacts folder

Shared_Contacts_IDefault contacts folder
Everyone who uses Outlook has a default contacts folder. All their personal contacts will be stored in this folder. This is not typically the folder you would want to share. I meet a lot of people who have large business contacts folders. They created those folders themselves. The question that regularly returns is how to share that specific contacts folder or create a custom contacts folder.

Create your own custom contacts folder to share
First let’s start with creating a custom contacts folder which we will share with our co-workers.

Shared_Contacts_II

From the Ribbon select the “Folder” tab. On the left click on the “New Folder” button. The following dialog will pop up:

Shared_Contacts_III

In this dialog (which is in Dutch but you get the picture right?) fill in a name for you custom contacts folder. I named it Customers. Next click on the contacts folder from the available list. This will automatically set the second combo box to this folder contains contact items. Click OK and your custom list will show up in your contact list directory (as shown below)

Shared_Contacts_IV

Share this contacts folder
Since this is not a default outlook folder you have to share it yourself. To do so follow these steps. In the contacts directory right-click on the list you have just created and click Share Contacts.

Shared_Contacts_VThe big advantage sharing this way is the option to set the Folder permissions as you go. You can see this also appear in the menu below Share Contacts. You will see a mail window appear where you can check the box for permissions. So it is possible to work together in this list. If you want to be the only one that has exclusive rights leave the check box unmarked. That way the recipients can only view the list.

Shared_Contacts_VI

Click Send and as software usually does you will get another confirmation box to respond to:

Shared_Contacts_VII

The recipient will receive a mail which contains the link to the custom contacts folder. If he or she clicks on the link they will see the contact list appear in their contacts directory.

How the recipient sees it
And this is how the recipient will see it:

First the mail which tells the recipient to click on the button ‘open’ which is actually hard to see. The text in the red square states “Open contacts folder
mailAnd in their contacts directory he or she will see the custom contact list appear under shared contacts (image shows dutch version of outlook).

contactpersonenThere you go. Your own custom contacts folder shared with other recipients. Remember you can set individual permissions per recipient. So multiple people can edit, add and delete and if you want to the others have ‘read only’. In this day and age of sharing only one contacts folder would be sufficient for the whole company don’t you agree?