Category Archives: Office Tips and Tricks

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!

TitleSlide_XII

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.

TitleSlide_III

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.

TitleSlide_IV

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”.

TitleSlide_V

TitleSlide_VI

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”.

TitleSlide_IX

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?

TitleSlide_VIII

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!

TitleSlide_X

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!

TitleSlide_VII

 

 

 

Advertisements

EXCEL: AVERAGEIFS With A Twist

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:

AVGIFS VI

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.

AVGIFS VIII
This is what the AVERAGEIFS function looks like:

AVGIFS VII

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:

AVGIFS X

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!

AVGIFS V

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!
AVGIFS IV

And here is what the end result might look like:

AVGIFS III

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:

Outlook_Remove_Reply_Bar_INIT

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.

Outlook_Remove_Reply_Bar_EN

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.

Outlook_Remove_Reply_Bar_I_EN
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.

Outlook_Remove_Reply_Bar_II_EN

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:

Outlook_Remove_Reply_Bar_III_EN

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.

Concluding
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.

Find_duplicates_I

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”.

Find_duplicates_III
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:

Find_duplicates_VI

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.

Find_duplicates_VII

Concluding
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:

Import_Files_II

This will present you with an import wizard dialog.

Import_Files_III

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.

Import_Files_IV

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.

Import_Files_V

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…”

Import_Files_VI

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

Import_Files_VII

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.

Import_Files_VIII

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

Import_Files_XIII

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..

Import_Files_IX

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:

Import_Files_XIV

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:

Import_Files_XV

 

Import_Files_XVI

Explanation
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).

Import_Files_XVII

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.

Replace
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!

Conclusion:
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!