Author Archives: Maurice

PowerPoint: Show Your Progress With Slide Master

Insert Slide MasterHave you ever wondered how long a presentation will take? Have you ever wondered what the progress is of the presentation? Well I have! Too many presentations last forever almost putting you to sleep if you don’t start using Whats App between slides. Why not show your audience the progress of your presentation? In this post I will show you a tip how to implement a kind of progress so your audience knows where you are during your presentation. Simply divide it into sections using the various titles slides. A big advantage will be that you have the structure you are looking for while setting up the presentation.

A blank canvas
TitleSlide_ISo we start of with a blank canvas and move on from there. Start PowerPoint and choose blank presentation. Now on the Home tab in the Slides group have a look at the layout menu. It will show you the default setting for this presentation. This is where we are going to make the changes. We would like to have three Layouts we can choose from. Because we haven’t made any changes yet there is only one set of layout available. Click on the image to enlarge…

The progress bar idea
For this presentation I will use three images to show the progress of my presentation. I will put the images at the bottom of my slides. The idea is that the presentation will be divided into three sections. So when I am presenting and talking about the first section it will show an active first image with the second and third images being a kind of grayed out. So let’s start looking for some appropriate images. I think the images below will serve the right purpose!


Change the master slide layout
While I am at it I would like to structure my way of setting up this presentation. So I decide to create three types of Master slides which I can use while creating my presentation. The first section will be about “Medical IT”. The second section will be about “Insights” and the final section will be about “Analysis”. Change the work environment by setting it to “Slide Master” setting. Easiest way I have been using many years is by clicking the “Normal” view button in the status bar. If you click this button while holding the Shift button it will bring you straight into the Slide Master view.


Add the second section by adding a New Slide Master
Because we would like to add three sections I decided to add a new Slide Master. So in Slide Master view click the button on the ribbon that states “Insert Slide Master”. You will get a new Slide master underneath the existing one. While you are at it add a third Slide Master. So in total you will have three Master Slides available.

Adding images to the various Slide Masters
Click on the first Master slide of your collection. Add the images to your slide here. Remember we want to show progress. To create the illusion of progress I grayed out the second and the third images. Add the images also to the second and third master slides you have created. Don’t forget to grey out the appropriate images in the appropriate sections. The image below shows the images I have placed on the second section. This means that I have grayed out the first and third image in this section.


Even more structure
I you take a peek in “Normal View” from time to time you will see that PowerPoint has chosen some default names for you slide layouts. Let’s make this more structured and fitted to our needs. Right click every single Master slide we have created (three in our case). From the appearing menu choose “Rename Master”.



As you might have seen I have chosen the “Medical IT”, “Insights” and “Analysis” titles for my master slides. So change them accordingly to your preference and have a look in the slide layout menu in “Normal View”.


Divide the presentation into sections
We now have three kinds of layout to choose from. But if you look closely you will see that you have a lot of slides to choose from. How many of those types of layout do your really use during the presentation? Exactly my point! Why not remove those we will not use? Again it is into the slide master view. Right click the layout slides you don’t like or you will not be likely to use. From the menu choose “Delete Layout”. You will get a lot cleaner overview of slides you will want to use when creating your presentation. My favorite layout? Blank! Have a look at the image and ask yourself…. how many times did I use this slide layout in my last presentations?


Create sections in the Slide Sorter
Final step is to create specific section names for each slide master and you are good to go. I like to do this in the Slide Sorter view (status bar second icon), but you can also do this in Normal View. Right click in front of the slide where you want the next section to start and click “Add Section”. Rename your section to in this case the same name as the slide masters and you will have one perfect setup for your presentation!


Now show the progress
TitleSlide_XIWith that all taken care of it’s up to you to show the progress of your presentation. Add slides as needed and choose the correct layout slide. Adding slides to the first section? Choose the first layout from the layout menu. Adding slide to the second section of your presentation? Add slides from the second layout from the menu. When you are presenting the audience can actually see which section is being covered by the active images on the bottom of your presentation. If you have reached the third section of your presentation your audience will know that you are in the final stages of your presentation. The only thing they will post in Whats App now is that the presentation is in it final stage!






AVGIFSA while back I wrote a post about the SUMIFS function. This blog post is about the AVERAGEIFS function. It’s slightly different to the regular AVERAGEIF function and I will describe the functionality behind it. In the end I will describe how you can use the AVERAGEIFS function using a reference to a data validation field. This provides a combo box list option which makes the function dynamic.

First things first
I know that everything I write about here can be done by filtering the dataset. The choice is entirely up to you. I like using the functions so I don’t have to filter my data constantly. If you want to join create the list with sample data as shown below. I have assigned named ranges to the various columns. This makes the function more readable. So I have named the first colored column “product” (which isn’t a very good name because it collides with the existing function name!). The second column is called “size” and the third column is called “color”. Finally the last column is called “price”. Of course you have the option just to select the various ranges like this “A2:A15”. I am a fan of naming ranges because I think that “Size” is easier to read than “B2:B15”. AVGIFS I

What we are trying to achieve
What we want to achieve is the average price range based on criteria we supply using the data. So in this case we are looking for three sets of answers.

  1. What’s the average price per product
  2. What’s the average price per product per size
  3. What’s the average price per product per size per color

The average price per product can be calculated by using the regular AVERAGEIF function. In the cells next to the list with data I added the following data so we have a place to get our answer:


Now let’s have a look at the first function. It states =AVERAGEIF(product, “Jeans”, Price). In this case we just want to see the average of either the Jeans or the Shirts. Because we have only one criteria so we can use AVERAGEIF here. We need to use the IF because we have more than one variable (Jeans, shirts). The order of arguments of the function is important when working with the AVERAGEIF or AVERAGEIFS functions. So let’s see if we can spot the difference: First we have the AVERAGEIF function. This function first wants to know the range where to look for the criteria. The next criteria is to look for what in that specific range (your criteria) and finally you select the column on which Excel should calculate the average.

This is what the AVERAGEIFS function looks like:


Now here’s the hard part. In contrast to the AVERAGIF function this function starts with the range that needs to be averaged (the last argument in the AVERAGEIF function), the second argument is the range we are going to use to look for the specified value and finally you provide the specific value you are looking for in the previously set range.

So if we want to know the average price for Jeans we use the following:
=AVERAGEIF(product, “Jeans”, price) which reads as follows
Look in the product range for the criteria “jeans” and look in the price column. From all the records you have found show me the average.

Now for the AVERAGEIFS function which we use to get an answer to the question:
Show me the average price for the product “Jeans” with the size “s”.

The hard part is the difference in order in comparison to the regular AVERAGEIF. The AVERAGEIFS version starts with the range that needs to be averaged and after that you set the various criteria. In our example this leads to the following AVERAGEIFS function:

=AVERAGEIFS(Price, product,”jeans”,size,”s”)

Which translates like show me the average price for the product “jeans” where the size is “s”.
By the way it seems that Excel doesn’t mind if you use capitalized letters in your criteria!

In our last sample we see that the question is as follows:

Show me average per product per size per color. So we have an additional criteria set. For the AVERAGEIFS function that makes no difference. Look at the picture and see what function has been entered to tackle this question.

Adding a bit of functionality using data validation
Using the AVERAGEIFS function this way can be tedious because I do have to change the criteria used in the function to see other values when for example the product is Shirt instead of Jeans. To tackle this problem I decided to add data validation to cells. This way I can use comboboxes to make my choices without having to change mu function. Let’s see how this is done:


and now we come to the nice part of adding this combo box to the AVERAGEIFS function.
Instead of typing the value “Jeans” or “Shirt” we just refer to the cell which contains the data validation and the choice we have made in that cell!


So look at the image above and see that the AVERAGEIFS function is not looking at “Jeans” or “Shirt” anymore but instead it is looking at a cell reference which contains our drop down combo boxes. What ever you choose will be the variable for the AVERAGEIFS function.

But wait… what if it goes wrong?
Everyone who works with Excel knows that you might get the famous #DIV/0 when something is not supplied or is not available. Most of them just leave that and will carry on. I am the person who likes to provide a little error handling just in case. So to finish the function of properly I embedded the whole AVERAGEIFS function in the IFERROR function. Now when a certain combination is not possible just because the option is not there you will see “Not Available” instead of #DIV/0! which looks better in my opinion!

And here is what the end result might look like:


Every time you change the combo box value to a different setting the function will recalculate and show you the outcome for that specific combination. How cool is that!


Microsoft Word: Protect Your Sections!

Restrict EditingHave you ever thought about protecting certain areas of your Word document? In some cases you really don’t want users to change any text in a particular paragraph. Microsoft Word has the option to protect the document but in that case it shuts done the entire document. So we are looking for functionality that enables us to protect certain areas of our document while the rest of the document stays editable. Here’s one way to achieve that goal.

Starting with some text
If you would like to follow the steps create a document to perform the next steps. I started with a new blank document and added some random text in it by using =rand(20). This creates 20 paragraphs of random help text in your document.

Setting up the document: Breaks
The first thing we have to do is create page breaks in our document. For practice purposes I’ve just added breaks after each paragraph. There are various kinds of page breaks available. The one we are looking for is the continuous section break. This will keep your text in place but breaks down your document in various sections.

Restrict Editing IV

So choose the spot and after each paragraph insert a continuous page break. Your document should look something like this: see pic below

Restrict Editing V

Restrict Editing possibilities
The next step we have to do is set the restriction on the document. To do so click on the Review Tab and look for the button that states “Restrict Editing”.

Restrict Editing II

Click on that and the Restrict Editing task pane will appear on screen. In the task pane you will see three sections.

Restrict Editing IIIWe will use the second section here: Editing Restrictions. Enable the check box that says: “Allow only…. Document”. From the combo box select the Filling in forms option. When done you should see a text appear below the combo box which states “Select sections…”. It will be no surprise that we need to click that text. A new dialog will pop up right in the middle of your screen. In this dialog every section you have created in your document is protected by default. Uncheck the sections you don’t want to protect leaving only those you do want to protect enabled.


Restrict Editing VI








Restrict Editing VII

Enforce protection
Now the last step we need to take is to enforce the protection. To do so click in the last section of the task pane and click the button that states “Yes, Start Enforce Protection”. A dialog will appear asking you to provide a password. As usual you need to type your password twice. So choose wisely and make sure you don’t forget the password. When done try editing the specific sections that you have protected and try editing sections that you did not protect. Pretty cool right?

Restrict Editing VIII

Protecting documents
During my work I see a lot of documents. Especially when we are helping a customer migrating his documents from an earlier version of Office to the current version. That’s when a lot of documents pop up asking us for passwords. And yes you have guessed it, most of the times those passwords have been forgotten or set by employees that are long gone from that company. So my advice would be to use passwords wisely and in reduced numbers. Don’t just go about protecting every document you might create. There will be a day when it will bite you back if you don’t know the password anymore 🙂

Outlook: Disable The Email Reply Taskbar!

Outlook_Reply_BarI never thought for one moment that this could be such a big issue but apparently it is for many people. I am talking about this little taskbar on the right side of the screen in Microsoft Outlook. I got this simple question: “How to turn this off, I don’t want it there“. Well like many people do I started clicking my way through the application searching for a check box that would disable or hide this feature. As one might expect with no avail because this is not a check box option. Keep reading and I will show you how to disable this feature.

Quick Access Toolbar
Our journey starts with the Quick Access Toolbar. You know that place where you can store your own ribbon preferences.

By default it looks something like this on a regular laptop / desktop with no touch enabled screen:


Touch enabled screens
Most of our devices have a touch enabled screen these days. Ever since the touch enabled screens were introduced the Quick Access Toolbar got this new option to choose from.


Have a look and see that there is an option called “Touch/Mouse Mode” available. However because I am writing this on an old regular non touch enabled laptop I have to activate it first to see it. If you do have a touch enabled device you will see this option by default in your Quick Access Toolbar.

Now here’s where the magic starts. On touch enabled devices this is the default setting. It is optimized for touch. This also produces the additional toolbar on the right side of your screen in Outlook.


By now you will know the answer how to disable this nifty little taskbar. By choosing the Mouse option in the Quick Access Toolbar the toolbar on the right side of your Outlook screen will no longer be visible. Here’s how that looks:


A Switch mode
One might say that this is a switch mode to choose from. If you want to see the taskbar click on Touch mode. If you don’t want to see it click on the Mouse mode. If you have a non touch screen based device but still want to take advantage of this feature you have to add it to your Quick Access Toolbar first. From that moment on you can switch any way you like.

As with many things it is not all that difficult. You just need someone to show you how it is done and from that point on all the irritations are history! Happy Switching 🙂

Excel: Find Duplicates.. Do Not Remove Them!

Find_DuplicateOne of the finer tasks in Excel is working with lists. When working with lists, one of the most common questions is “are there any duplicates”. Now most people know that there is a button at their disposal the famous “Remove Duplicates”. This is great but it just removes them. How about if you want to inspect the data before it is removed? There are various ways to find duplicates in your lists. Conditional formatting offers a great way to highlight duplicate values in your list. Personally I have been using a simple function to check for duplicates. It gives me control of what I can do when it finds duplicates and how to handle them. Let me show you what I mean.

Sample data
To give you an idea how to follow the steps I use some sample data from the old Northwind database. Just a couple of rows with data which I have enhanced with some duplicate values so you get the picture.


Sort the data and add an additional column
The first step I take is sorting the data. My preference is to use the Data tab because it has this great group of functionality you can use at once.

Find_duplicates_IISo sort the data and we are ready for step 2. The second step I take is adding an additional column which I call “Duplicate”.

Now the third step I take is I add an IF statement (function) in cell A2.
Find_duplicates_IVThe IF function is pretty straight forward. I compare the value of cell B2 with the value of cell B1. If it has the same value it should show “Duplicate”and otherwise leave the cell empty. Once you have entered the function press Enter and copy it down in column A. It should look something like this:

Find_duplicates_VAs you can see it skips the first new value and every value after that which has a duplicate value gets the word Duplicate in front of it.

Duplicates on more columns
I know what you are thinking. This is one column but what if I want to check more columns? Well basically you take the same steps as above. In the example above I checked for duplicates in the first column. Now I will check the first and second column for duplicates. Have a look at the picture below:


As you can see I enhanced the function a bit. Using the & -sign I tied two values together to match the other two values that are tied. So basically I say look at the value of cell C2 and stitch the value of cell D2 to it. These combined values should be equal to the values of cell C1 & D1. If this is the same value then show “Duplicate” otherwise leave empty.

Finding a whole duplicate row
Knowing what we know now we can easily look for a duplicate row. Just concatenate all the values from every column using the & -sign. Then compare that with all the concatenated values of all cells from the row above. Copy that formula down and you are good to go.


Working with lists of data is currently one of the most common tasks to do at a job. When you are like me and working with lots of data having the knowledge to de-dup is mandatory. Find the best way that fits your needs. For me it is the simple IF-function that does the trick without having them removed straight away 🙂




Automate Your Import Process in Access

Import_Files_IHow about 1,2 million rows of data?
Well there you are, Excel specialist and forced to import a text file containing well over 1,2 million rows of data. You do not have the Power BI tools installed nor do you know how to work with those tools. YouTube? right have a look how it is done and repeat the steps. You will repeat the steps but you have no clue as to what you are doing. Why not import the data into Microsoft Access? You can apply the same functionality as you do in Excel when working with large amounts of data. Two million records? Just dunk it into Access and you are good to go. In this blog post I will describe the process of importing well over 1 million rows of data into Microsoft Access. You can see this as a monthly task so to be more specific we are going to save the steps so we can repeat them next month and the month after that.

Importing a flat file with over 1,2 million rows of data in Microsoft Access
So the goal is to import the flat file into an Access table and automate this process so we can repeat the steps every month. The first step would be to create a new blank database (no web stuff just a regular desktop database). When done you will be presented with a new table ready to be used. Close this table and don’t save it. Now on the ribbon click the External Data tab and from the Import and Link group choose Text File:


This will present you with an import wizard dialog.


We are going to follow the steps from the wizard but at some point we are going to take a side step. Let’s first determine what we are confronted with:

  • We need to provide the location of the .txt file. That’s the easy part. Just browse to that specific directory using the Browse button
  • Next you have three options to choose from. We are on the hunt for importing data into our database so we choose option 1. examine the other options so you know what is available out there.

When you have confirmed the .txt file you want to import click the OK button. You will be presented with the next step from the wizard.


This is where we need to take some time to do some checking. Access tells you that it seems that your data is in a ‘Delimited’ format. It assumed right this time so we click Next. Here is the next step using the wizard.


Another dialog we have to check…
First we determine the delimiter used in our text file. In this case the ‘Tab’ delimiter is correct. Most of the users prefer a semicolon (;) which is also available. My data has a first row that contains header information so we check the check box “First Row Contains Field Names”. Now instead of clicking the Next button I am going to click the Advanced… button. Why? because I want to save the import steps I am making for later use. Here is the dialog which will pop up when you have clicked “Advanced…”


In this dialog we determine the data types for the various fields from the text files. Let’s take a look at the different choices we have to make:

  • Language: defaulted to your installation (mine is dutch)
  • Decimal symbol: important for Europeans (US has a dot here)
  • File format: as determined in the previous step
  • Field information: because we are creating a File Import Specification which we would like to save we have to rename the field names to the appropriate names.
    TIP: do not press Enter after you have renamed Field1 to something else. Entering will close the dialog!!
  • I don’t want to import the field LineTotal so I will mark that field as ‘skip’. As you can see I have renamed all field names accordingly


Saving the Import Specification
When you have renamed all fields you click the “Save As…” button. With this button you save all the parameters you have set during the wizard. So next time Access will know the field names and data types you have defined.


Save you import specification by giving it a name. Now follow the regular steps to finish the wizard (we don’t need to take any further action, we don’t need a primary key so you can set those to no primary key. Choose a table name at the and, and your table will be created containing the data from the .txt file


Save the import steps?
At the end you will be asked if you want to save these import steps. Confirm and click OK after you have given it an appropriate name. Why do we need to save the steps twice? I am going to explain right now..


Two steps to automate
Developers will not use the file import steps you have saved as the image above shows. They will use the File Import Specification file we have created earlier. Strange thing is that this file can not be found in the combo box when you creat an automation macro. Let me show you what I mean:

Automate this process using the autoexec macro
Every Access developer knows that you can create an automation process by naming your macro “autoexec”. So we are going to create a macro which will fire the import steps as described above using the saved import steps. From the ribbon choose create macro. You will be presented with the following screen:


Activate additional options
By default you will not see all available options. You have to click the “Show All Actions” button. This will reveal more available options with an exclamation mark in front of it. Can you figure out what that means? Anyway we choose the option ImportExportData and on the center screen we are presented with a combo box to choose from. Remember what I said about the Import Specification File we save during the wizard? You won’t find that file in the combo box here. What you see here is the filename we presented right at the end of the wizard. Now save the macro and name it “autoexec”. Delete the table you created earlier and close and reopen the database. Your table will be created automatically for you.

Automate from code (VBA)
This step takes a little more work but gives just that little more flexibility. First create a module (Ribbon – Create module). Now type the following bit of code as you see in the image:




Although it doesn’t return anything the macro editor expects a function here so we create a function. The first line of code tells Access to delete the table. The second line of code tells Access it should import the text file from that specific location using the Import Specification we have saved during the wizard steps. By not deleting the table this piece of code will do the clean up first for you. Save the module (give it a name you like)

Now create the macro
Instead of using ImportExport commands choose the Macro commands option and look for RunCode. In the text box in the center of the screen type in your function name and you are good to go. Name your macro (autoexec would be great, but be aware that you already have a autoexec macro from the previous steps. Rename that for now to something different).


Close and reopen the database and you data will be imported accordingly.

Concluding: If Excel hasn’t got the room to fit your needs Microsoft Access might be able to offer you just that little more. If you have imported the data into the table you will see that Access also offers all filter capabilities you have in Excel. Why not use both?




Find, Replace And Format In One Action

find_replace_select_IWait, wait how did you do that? Every now and again you get these wondered looks from other users when performing an action in Word, Excel, PowerPoint and so on. A couple of days ago I was reviewing a document and decided that a specific word needed to be replaced by another word and should be emphasized in that specific document. With a blink of an eye I replaced every occurrence of that specific word, replaced it by the one I thought would be appropriate and formatted is at the same time. How I did it? Here we go…

Sample document
You can follow the steps by creating a simple document. We will start of by starting Word. Choose blank document and type the following: =rand(50) followed by pressing Enter
This should give you 50 paragraphs of random text extracted from the help files. That should give you something to play with.

This sample text contains the word document frequently. For demonstration purposes this is great. We are going to replace this word with the word File and emphasize it by making it Bold and Red at the same time.

Start by clicking the Replace option in the Home tab in the group Editing (far right on the home tab).
find_replace_select_IIThis will present you with the following dialog:
find_replace_select_IIINow here is where we provide the necessary information. First we type in the word File in the “Replace with:” text box. Now the important next step is hidden under the “More >>” button. So click that and your dialog will be expended to the following situation:
find_replace_select_IVThe “More >>” caption has changed to “<< Less“. We are looking for formatting options so at the bottom left you will see a button that states “Format”. Click that button and a new menu will appear. From the menu choose “Font”. This will present you with the following dialog:
find_replace_select_VThis is a dialog you should be familiar with. Make the appropriate choices and click OK. The font dialog will be closed returning to the previous dialog. One major change has been added. This is often the part being overlooked by many users. Have a look at the image below and focus on the red box around the formatting option:
find_replace_select_VIThis indicates that it will replace the word “document” with the word “file” and will format that specific word blue and bold! Now click replace all to see if we achieved some result.

find_replace_select_VIIWe did! It replaced 50 instances of the word document with the word file and applied the appropriate formatting as well. The output looks like this:
find_replace_select_VIIINo text just formatting?
Is it possible to just replace the formatting and don’t replace a word? Yes it is. If you wanted to apply a formatting to the word document and not replace it by any other word just leave the “replace with..” text box empty and click it once so it has focus. Now follow the same steps as described earlier and click replace all. This will replace all occurrences of the word document with the word document with formatting applied to it just the way you set it up!

You can make a real efficiency boost by applying the various techniques in Word. Instead of taking 50 steps by clicking every word you can apply it all in one action. Believe me if you have used this once you will never go back!


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.


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


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.


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.


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.


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.


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.


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.


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.

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.


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


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.


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.


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.


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.


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.


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.


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.


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.

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.


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.


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:


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.

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.


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!


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.


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.


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 🙂