Category Archives: Office Tips and Tricks

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?

Access: Tuning Your Client Settings

Access_MaintenanceMicrosoft Access is a great application for creating databases. It is also used as a tool for just storing data. Where Excel will limit you to just over a million rows this application has no issues whatsoever to store a couple of million rows. In that state Access just serves as a backend. I enjoy seeing people create databases in Microsoft Access. From small community databases to extensive company databases. It is usually the application to start with before taking the next step to for example SQL Server.

Split Database
When done creating your database it is good practice to split your database. You will get a front end and a back-end. The front end contains the forms, queries, reports, macros and modules. The back-end contains the tables. The front end contains links to the tables of the backend. Now when multiple users need to use your database you provide them with a copy of your front end. You must also remember that users need to have a copy of Microsoft Access installed on their computer. In all other cases you either have to create an App or create a runtime version. What you need to know about the client application is that 9 out of 10 times this is installed via the IT Department. Users don’t know or don’t care about the environment they are going to use your database in. They should but that’s where your advice comes into play.

Client Settings
When end users develop databases they often use the environment that is presented to them. As I told you Access is a great application but also has a lot of settings that can be turned of when your database is ready to be used. So let’s have a look at the settings when you start creating a database. For this example I am using Access 2013 but it also applies to previous versions. Remember we are talking about client settings for a single user here.

Current database Options
Access_CurrentDBWow that are a lot of options to choose from. Which ones are good and more important what does it mean when I disable an option? Let’s have a closer look.

  1. Use Access Special Keys
    This option gives the option to enter the VBA screen via the ALT-F11 keys combination. It also provides the option to show the navigation pane when pressing F11. So if you think users should not be able to do so, uncheck this option.
  2. Compact on Close
    This must ne the favorite option for a lot of people. My advice would be to compact and repair your database manually and not by setting this option to true. When working with multiple users Access will try to compact the database when someone closes his front end. When other users are still using the shared backend Access stops the compacting process because that would conflict with the current usage of the database. This option causes quite some corruption to the various databases. So uncheck! There is a button for it and use that when you know no one is using the database.
  3. Use windows themed controls on forms? That’s a personal choice. If checked Access will always check first to see if the used theme is present (takes time!). My advice… uncheck!
  4. Enable layout view. Great if you are developing databases. When done uncheck the end-user only needs the final view and not the layout view! So uncheck.
  5. Enable design changes for tables in Datasheet View. It surprises me that this option is actually there when end users are working with a finished database. Of course you have to uncheck this one. Nobody makes changes to the database but the developer.

Well that’s the first step. Now for the next step. In this paragraph i will point out the various options you might ‘uncheck’. See if you can find them in the Access – Options dialog.

  • Auto index on Import/Create
    This is a notorious option in Access. I wrote a special post on this which you can find here. It’s best to clear all the options you find there. Indexing should be done manually but that’s my opinion.
  • Enable Error Checking. This is a great option while you are developing the database. But when the database is done uncheck this option on the client setting.
  • Proofing
    I never use proofing in a database environment. But this could be a good option for the end-user. So your choice here.
  • Client settings
    – Display options. Check this one very carefully. Show animations is not one essential part of the database. So uncheck. Show Action Tags… (). This option offers additional choices when user works in a form. So if desired leave it on otherwise uncheck. (Add to contacts, show map for address etc).
  • Name Auto Correct options
    This is also a famous option in Access. Wow this changes all my field changes in my queries and forms if I made changes to my table design. Yes it does and the big issue here is that it does not change your VBA added functionality. Furthermore a background process has to check this every time just to make sure no table design changes have been made. So for the developer yes keep it checked. Once finished please turn this off. You will see a significant change in performance when Access does not have to check this.

Options, options and more options
Microsoft Office applications are great. One downside can be the gazillion settings that are available to the end users. For databases all these options can cause performance issues. So when deploying you database make sure the client settings for your end users are optimized for working with your database. Help them… this is a one time investment which will save a lot of issues later on.

 

Outlook: Do Not Send Response For Meeting

NoResponse_IHow many meetings do you plan using Outlook? And how many meeting responses do you receive after you have pressed Send? For lots of people this might be one of the great annoyances of Outlook. You plan a meeting with quite a group of people and they all decide to respond neatly. That means that you know who will be attending but do you really want to know? It also means quite a lot of return mail. Not all meetings require all attendees to be there or you just don’t want to know. Many people I talk to created rules to have meeting responses be redirected to some specific folders. If you don’t need or want to receive meeting responses after you planned the meeting follow this tip

Plan your meeting as usual
Start by planning your meeting as usual. Open ‘New Meeting’ and proceed like always. However before pressing the Send button have a look at the ribbon that is available. You will see a button in the ‘Attendees’ group called ‘Response’. This button has two options. You are looking for the first one:

NoResponse_II

Once you click on the arrow you see a check mark in front of Request Responses. Right that is exactly what we don’t need for this meeting. You uncheck it by simply clicking on it. And while you are at it have a look at the second option:

NoResponse_III

Not many users will use this but if they do you are in trouble… We don’t want people to propose a new time for this meeting. So uncheck this and you are good to go.

When to uncheck the Request Responses?
Well that’s up to you. Some companies have regular meetings with a lot of people who should attend. It is often the more informal meetings that are optional. Those would be good candidates to have the responses unchecked.

Don’t go running late…
And if you do have a meeting and are running late read my blog post about the running late button on your Windows Phone 🙂

Excel: How To Create Diagonal Headers

DiagonalI don’t know how many lists and tables in Excel i  have seen the past few years. Some of them are real artworks and others just plain and simple. Somehow there is one type of table/list header that always makes an impression. I am talking about the lists with the diagonal headers. Aways wanted to know how people create these headers? Well follow the steps below and before you know it this one will be in your Excel tool belt as well!

Why diagonal headers in the first place?
That is the question to start of with. Sometimes your column headers are just to wide for the data the column actually hold. But you do want that good label header. Setting the header vertical is one option, but setting it diagonal just add that little touch to easier readability. So this is what we are after: (click on the image to see a larger version)

Diagonal_VIII

First step is to select the header row (only the cells that contain data please! you don’t need the entire 16000 cells in row one).

Diagonal_I
With the first header row selected right-click and choose format cells from the menu.

Diagonal_II

You will now see a familiar dialog pop up. Select the second tab ‘Alignment’ and set the orientation to 45 degrees. just drag the red dot… ( as shown in the image).

Diagonal_III

Now we set some nice color by choosing the cell style color. But wait that is not what we are after? Nope we have to adjust it a little more.

Diagonal_IV

Make sure the header is still selected. Now open the cell style menu once more. Now choose the first option under the Data and Model group. I chose calculation.

Diagonal_V

Diagonal_VII

Some adjustments…
Now you see that your color will change to the cell style you have chosen. Don’t panic you can reset the color later to the color of your liking. But he, it is starting to look the way I want it 🙂 Now for some final adjustments choose the color style you had before. The headers will stay diagonal and your color is applied.

Diagonal_VIII

Users still want to filter their data remember….
As a final touch make a table out of this list. Easy thing is just to go to the table style menu under the ‘Format as Table‘ button. Choose one of your liking and make sure the header row is also in the selection area. You now have a fully functional table with filter possibilities with diagonal headers! Adjust column widths and you are good to go. Lists anyone?

 

Nifty SmartArt Trick In Microsoft Office

SmartArtIconWhen SmartArt was introduced in Office 2007 everyone could make their documents just look a little more ‘designed’. Wow with just a little more clicks we had this diagram looking great with no hassle at all. But did you know that you can do more with that smart art option? How about creating a collage picture from some of your photo’s with a few simple clicks. Let me show you what I mean. For this example I used PowerPoint but anyone using Microsoft Office knows that you can do this in Word and Excel as well. So pick an application and see if you can follow my steps here.

Insert Pictures
The first step I take is insert some pictures I’d like to use for my SmartArt. In this case I’ve chosen some pictures I took when visiting the Yankee Stadium in New York. I selected 5 pictures and clicked ok. The slide looks something like this:

Smart_Art_IV

As you can see all pictures are still selected and they should remain that way. So don’t click anywhere yet because we are not done yet.

Back to the Ribbon
With all pictures still selected you should see a contextual tab appear in your ribbon. In the contextual ribbon tab click on the Picture Layout button as shown below.

Smart_Art_VWow this is cool! Pick the picture layout you like best. For this example i chose the first one. This will create the following SmartArt.

SmartArt_IYes, this looks great! But…. it says “Text” right there in my picture and i don’t want that. Well the trick here is to open the text pane from the SmartArt and click the space bar once. This will create a space in the text box and will remove the word “text” in the central picture. Do this for all text boxes!

Some more design…..
Now add some more design to the SmartArt by clicking the design tab in the ribbon and picking the one you like best. Shown below

Smart_Art_VIIOther Layouts
When you have picked a layout and like to see the other possibilities just click the ribbon again and change the layout in the SmartArt section. It will show you the possible SmartArt that can be used. Not every SmartArt is applicable but the ones that do will show you a nice preview.

Examples: let me show three layouts I chose while working with the same pictures

SmartArt_I

SmartArt_II

Smart_Art_III

Wow how cool are those? Better yet, if you would like to save this SmartArt as a picture just right-click the smartart and choose ‘Save As Picture’. You now have 5 pictures melted into 1 without distorting the original files. Yes non designers, we can be designers too!

Smart_Art_VIIIHappy SmartArt(ing) 🙂

 

CustomUI Editor: Trigger Your Own Macro

 

Custom_UI_ButtonPreviously I did some posts about the CustomUI editor. This nifty little tool makes it easy to create your own ribbon in the various Office applications. We have seen how to add your own ribbon, add icons to your ribbon and how to add a gallery to your ribbon. In this post I will show you how you can add a ribbon, a button and how you can assign an action to your button. The example will show a userform in Excel when you click the button in your own customized ribbon. As always I will describe the steps to achieve the result we want. So let’s do this!

 

First things first. Startup Microsoft Excel. It is important to know that there is a distinction in versions one might use but I will point those out to you. The example I made uses Excel 2010 (and beyond) but it is possible to use Excel 2007 as well.

Blank document Save as
The first step we need to do is create a blank document in Excel and save it as ‘Macro enabled’ workbook. Why? Because we want to assign a macro to the button we will create in our ribbon and files which contains macro’s are mandatory to save as macro enabled workbook!

CustomUi Editor
Ok with the blank Excel workbook saved we can now open the CustomUI editor and start our journey. We will start from scratch. Choose open file and browse to the file you just created in Excel and click ‘Open’. Your Excel document will appear in the treeview. Now click on Insert from the menu bar.
Important: this is where the distinction between versions is made. So if you are using Office 2007 choose the second option from the drop down menu otherwise follow the example.

Custom_UI_EIT_IIf you have selected one of the two options you will see an additional node appear in the treeview. You should see a little icon with the word custom next to it. Ok we are doing great so far. Now because we don’t want to reinvent the wheel all the time we are going to use some default XML code. Again go to the Insert option from the menu bar and from the drop down choose ‘Sample XML’ and choose ‘Custom Tab’ from the next menu.

Custom_UI_EIT_IINow with that in place we can do the necessary tuning to make it ours. The default sample XML has all the elements we need. It will create the ribbon, group and button we are looking for. But because it is a sample we need to modify it to our needs. In the picture below I ‘boxed’ the part we need to modify:

Custom_UI_EIT_IIIIn the picture below you will see the modifications i have made to the existing XML to make it our own. I’ll describe the changes I have made.

Custom_UI_EIT_IV

  1. Changed the tab id: this is important because the tab id should be unique. This can be anything you’d like to call it. I called it ‘EITID’ which is the abbreviation for ExcelImportToolID
  2. Changed the group id: this is important because the group id also has to be unique. can you explain why I chose ‘grpEIT’ ?
  3. Changed the button id. You know by now that this is important so make it unique. You might notice that I keep the names in sync with the other ID’s.
  4. Make sure the imageMso has a capital M because otherwise it won’t show the icon!
  5. The final piece is the onAction bit. This is where I attach my macro reference.

Save, save, save
Of course you need to save from time to time so now would be a good moment! Let’s continue to the next step. Let us see if this works so far. Open Excel and open your document you’ve just created. It should contain a ribbon with just one button. it should look like this:

Custom_UI_EIT_VHave the same output? Ok, just remember it does not do anything yet because the macro is not there yet. So if you click the button you would get an error!

The macro
In the Excel file i created a userform (doesn’t have to be that fancy a blank userform will do). If you want to follow this example make sure the userform is called ‘frmImport’. Click insert – Userform from the menu bar and create your userform. Save your Excel file and we are ready for the next step. Adding a ‘callback’ from the customui editor.

Custom_UI_EIT_VI

Back to the CustomUI
Open the customui editor again and open your Excel file. You should see the XML code you have created earlier. Now to generate the callback for Excel click on the last button in the menu bar as shown below: Custom_UI_EIT_VIIIYou will see that the custom ui editor will generate some callback code for you. Copy that piece of code and after that close the custom ui editor.

Custom_UI_EIT_IX

Open the Excel file again in Microsoft Excel and go to the VBE (Visual Basic Editor). I added a separate module for clarity. You can do so by clicking: Insert – Module from the menu bar within the VBA environment.  When done you should see a clean module. Paste your code from the custom ui editor here:

Custom_UI_EIT_VII

Last step:
The final step we have to do is add the actual action between the lines the custom ui editor created for us. In this case we want to show the userform when the button is clicked. So all we have to do is add the line: frmImport,show between the lines that are already there. Be curious… test your ribbon callback by clicking on your button in your custom ribbon!

Custom_UI_EIT_XTaddddaaa! there it is! Well done. Now nothing is keeping you from adding more buttons and adding more macro’s to your customized ribbon. So go ahead and feel free to add more buttons and more actions by simply expanding you xml using the customui and expanding your vba module with the generated code you just have to paste and copy. Let me know how it goes 🙂

Outlook: running late button on windows phone!

RunningLateRunning Late
Don’t you just hate it when you are running late for an appointment or meeting? Traffic is holding you up, no parking space to be found or you just simply overslept. One way or the other you have to notify the meeting organizer that you will be late. What to do… write a mail, send a text or WhatsApp? When you have a Windows Phone and your appointment is in your calendar you can just hit the ‘Late’ button and send a notification to the meeting organizer. Let me show you:

In your calendar tap the meeting which could look something like this:

RunningLate_INow at the bottom om the meeting event you see the various options to choose from. In this case you see four options

accept: hit this button to accept the meeting. Not what we are looking for right now. If you have accepted already this button won’t be there.

decline: hit this button if you want to decline the meeting. If you have declined well the appointment will be there right 🙂

respond: well the obvious choices to make here

late: yes this is the button we are looking for. Hit this button if you think you might not make it on time. The next step will be presented:

RunningLate_IIThis is the easy step. Click the option which is applicable. Mind you if you have a meeting with a lot of people do not touch the email everyone option! Best would be to choose the first option. This will send an email to the meeting organizer which would be sufficient.

After that you will see a pre-filled email appear which you can just send to the meeting organizer.

Yes, hit send and you will be excused for the start of the meeting. Wow that might just give you the slack you were looking for right. So your live just got a little easier with just three clicks!

RunningLate_IIIRunning late

Well running late for an appointment or meeting can have several causes. Just try to remind yourself why you were running late for this meeting. It is better to find a solution for why you are running late instead of hitting this button every time. Keyword here: planning!

Windows Phone
I have a Windows Phone, i do not know how this works on iPhone or Android. Do these phones have the same features on board?