Conditional Formatting in Outlook is a really useful tool that allows you to customise how different messages appear in your inbox message list based on criteria that you set.
By default, conditional formatting makes unread messages appear in bold but you can add your own customisations also. For example, you may want messages that are just sent to you and no one else appear larger in your message list, or you may want message that are sent to you from your manager to appear in a different colour so they stand out. Both of these things can be done by applying conditional formatting.
Setting up Conditional Formatting
From your inbox:
Click on the Viewtab
In the Current View group, click View Settings
Click Conditional Formatting
Click Addto create a conditional formatting rule
Let’s look at an example. I want to create a formatting rule for mail that is sent only to me. I have named by rule ‘Only Me’.
Put a tick in the ‘When I Am’ box and specify ‘the only person on to To line’
Select how you wish these messages to appear. As these are important I have changed the font style, made it purple and bigger so they really stand out.
When I switch back to my inbox, I can now see the conditional formatting has been applied to my message list.
Another example would be setting conditional formatting for mails that come in from a specific person.
To do this, repeat the above steps but when you click Condition, enter the name of the person in the From field. Click OKand then set the formatting options through Font.
There are lots of options within conditional formatting so its worth taking a look at trying out different options to make your important mails really stand out from the others.
As its Halloween this weekend we thought we would dedicate this week’s blog post to something that people find particularly scary! Something that sends chills down your spine! OK, I am being a little dramatic but it is fair to say that when it comes to Excel, Formulas are the thing that tend to strike fear into the heart of most.
Fortunately, formulas have never been easier or more human in Excel which means you don’t have to be a mathematical genius to understand them. Today we are going to take a look at ‘Using VLOOKUP to extract data’, a really common and useful formula which will help you cross-reference spreadsheets and save a lot of time.
Using VLOOKUP to extract data
VLOOKUP is a great time saving formula.
Let’s set the scene…
In this spreadsheet, I have a table (Table 1) that lists Items in column C and their associated unique Item Number or ID in column A. When a customer orders an item, I log the Item Number and the Customer Name in a separate spreadsheet (Table 2). However, the Item Name is not listed. I am going to use VLOOKUP to quickly and easily reference the Item ID and pull back the Item Name.
STEP ONE: Name your lookup range
This step is optional. You do not have to name your lookup range but I find that this is the best way of ensuring that your data pulls through correctly and it means that you don’t have to mess around making cell references absolute (fixed).
Highlight the lookup table (the table that contains the data you are pulling through, in this case Table 1)
Click in the Namebox and type a name for your table i.e. PRODUCTS.
NOTE: It is worth noting that table name cannot have spaces. If I wanted to name it ‘PRODUCT KEY’ I would need to use the format ‘PRODUCTKEY’ or ‘PRODUCT_KEY’.
STEP TWO – Create the VLOOKUP Formula (the scary part)
Click in the cell where you want the value to appear (C12)
Click on the Formulasribbon
Click Insert Function
Enter the criteria into the fields
Lookup_value – The lookup value is the information that you want to reference. It is normally a piece of information that is in both tables. Excel needs a reference point and in this example we are using the Item ID.
Click the cell that contains the first Item ID (A12)
Table_array – The table array is the table Excel is referencing. Excel needs to know which table to find the Item ID. This is where naming your table in Step 1 comes in useful.
Any named tables will appear in a list. Select the table ‘PRODUCT’. If you did not name your table, you would need to highlight the cells instead.
Col_index_num – This is the column in the lookup table that contains the data you want to pull through. In our example, we need the ITEM NAME. This information is contained in Column 3.
Range_lookup – This field can be left blank or will contain the value ‘TRUE’ or ‘FALSE’. Which one you use depends on how your table is sorted and the data you want to pull through. If you have your tables sorted in Ascending order, you can leave this field blank or use TRUE. TRUE also searches for an exact match. In our example, my data is not sorted and so I need to enter FALSE. In my experience, most data that you are dealing with is not sorted nice and neatly and so FALSE is the more common value to go in here.
If the formula has been added correctly, you should now see the item name in cell C12. Excel has essentially looked in the lookup table for the Item ID and then pulled through the corresponding Item Name.
Once you have used the helpful dialogue box a few times and you are comfortable with the format, you can just type the formula into the cell. E.g. =VLOOKUP(C12,PRODUCTS,3,FALSE)
You could repeat the above steps to complete the rest of the table but that can be quite tedious especially if you are dealing with a lot of data. To quickly copy the formula down, you can use the AutoFill handle in the bottom right-hand corner.
Drag the handle down OR double-click on the handle
TIP: If you get an unexpected result when using the auto-fill handle, such as the wrong data being pulled through or the same Item Name repeated, it is worth checking that you do not have your Auto-fill settings set to manual.
Click the Formulasribbon
Click Calculation Options
Set to Automatic
So as you can see, Excel formulas do not have to be a scary prospect.
Do you forever find yourself running the same search over and over again in Outlook? Maybe you frequently have to search for emails from your manager that contain attachments?
Outlook has a really useful search feature that you can use to find your emails but wouldn’t you rather be able to find what you are looking for in one click? Creating Outlook Search Folders for your frequently searched for email items is a great way to cut down on the amount of time you spend searching for emails.
Click the Foldertab
Click New Search Folder
You can choose to select the items you would like to search for from the default list presented, i.e. if I want to create a search for all emails from my manager, I would select ‘Mail from and to specific people’and then select my managers name by clicking ‘Choose‘.
However, if you would like to create a folder that searches using multiple criteria you will need to create a Custom Search.
Scroll down in the list and select Create a Custom Search Folder
Add a name for your search folder
You can select criteria for your search from any of the three tabs. In this example, I want a search folder that quickly searches for all emails from David that contain attachments.
A new folder will be created under Search Folders in your folder list. Click on the folder to run the search.
I think you will agree it is worth taking the time to setup some search folders in your Outlook as it will greatly reduce the amount of time you spend searching for emails.
Did you know you can cut and paste with Microsoft Spike? Probably not! Word has a hidden feature that most people do not know about to collect text on the clipboard from multiple locations and then paste it in to your document all in one go! Most Word users are familiar with using the clipboard to copy and paste text, but Spike works in a slightly different way.
Highlight the paragraph of text you would like to cut
Select the next paragraph of text you would like to cut
Continue cutting text from your document in this way. The paragraphs do not need to be next to each other, you can cut from anywhere in the document to move them to the clipboard.
Once you have finished cutting, you can paste all of the paragraphs in the order that you cut them into another part of the document,
Press CTRL+SHIFT+F3, OR
Type spikeand press F3
This clever piece of functionality utilises the Quick Parts feature in the background and is a useful trick to remember next time you are formatting a long document.
There are many great partnerships in life; Bert and Ernie, Batman and Robin, Yin and Yang and of course the humble Cheese and Pickle sandwich.
Introducing the Dynamic Duo – Cortanaand Microsoft Edge. These two separate applications seamlessly interact to provide you the consumer with a much more fluid and full browsing experience.
Lets back track a little. If you are reading and this and thinking ‘What in the world is Cortana?‘, then you are not alone.
Cortana is your clever new personal assistant! Cortana will help you find things on your PC, manage your calendar, track packages, find files, chat with you, and tell jokes. Yes, I said tell jokes. That was not a typo. The more you use Cortana, the more personalized your experience will be.
Now if you are anything like me, the words ‘Microsoft’ and ‘Assistant’ may conjure up this image in your mind…
Remember this guy?
Ah, Mr Clippy. Everyone’s favourite letter writing obsessed friend. The good news is, Cortana is very different to previous Microsoft Assistant tools and it can be utilised very effectively from within Microsoft Edge.
Turn on Cortana
Before you can use Cortana in Microsoft Edge you need to turn it on in System Settings.
Click the Windows button in the bottom left-hand corner OR press the Windows logo key on your keyboard
Start typing ‘Cortana’ to search
Select ‘Cortana & Search settings’
Use the slider to turn Cortana on
Once Cortana has been turned in, you can now ‘Ask Cortana’ questions directly from your we pages as you are browsing.
Ask Cortana when browsing the web
For example, I was looking at the BBC News website this morning and I was reading an article on the Falkland Islands. I was curious as to where the Falkland Islands were located so I utilised Cortana within my Edge browser.
Highlight the word you would like to search
Right-click and select ‘Ask Cortana’
Cortana will load information related to your search in a pane on the left-hand side of the screen.
NOTE: You must ensure you turn Cortana on within settings in order for ‘Ask Cortana’ to show in the menu when you right-click.
Hands up if this applies to you. You’ve spent hours working on an awesome document, typing and formatting like a mad thing, switching from one window to the next when suddenly….ahh! You’ve accidently closed the document you were working on without saving. *Cue lots of colourful language and crying*. Fortunately, Word 2013 allows you to recover your unsaved documents.
Click File to go to the backstage area
Click the Manage Versions drop-down arrow
Click Recover Unsaved Documents
Select your document and click Open
A yellow warning message will display across the top of the document.
Click Save As
Save your document as normal
Tip 2: Edit a PDF in Word 2013
If there is one thing sure to infuriate me, it’s receiving a form or document that I need to complete in PDF format! For years, PDF was the ‘go to’ file format if you wanted to prevent others from editing your document. Well not anymore! In Word 2013, you can easily edit and make changes to a PDF document. You can almost hear the sound of Microsoft users everywhere rejoicing!
Click the File menu to go to the backstage area
Click the Open tab
Navigate to the correct folder and select the PDF file
A dialogue box will appear informing you that your PDF file will be converted to an editable Word Document. It is worth noting that if your document contains a lot of images or graphics it might not look exactly like the original PDF and may need some ‘tweaking’.
Tip 3: Preserve your eyeballs and switch to Read Mode
Read Mode is a great option to use if you find yourself spending time reading or checking long documents. Also, if you are like me and find it easier to read text on a darker background, you can switch the background colour and give your eyeballs a well needed break from black on stark white. All of the document review options you know, love and need are still available in Read Mode.
Click the View tab
Click Read Mode
The document layout will change to an easy-to-read, two page per screen format.
Click the grey arrow to move to the next two pages
To maximise the ‘real estate’ on the page, you can hide the toolbar by clicking the ‘Auto-hide reading toolbar’ button in the top-right corner
To change the background colour of the page
Select Page Colour from the menu
Select Sepia or Inverse
Tip 4: Replying to comments
Collaborate smarter on documents by utilising Word’s reply to comment feature. This is an inline option that enables you to directly reply to any comments added by colleagues ensuring a smoother workflow.
Ensure that you have comments showing
Right-click on the comment icon within the comment
Select Reply to Comment from the menu
Add your reply comments. These will appear slightly indented underneath the original comment and will display under your name.
Click on any colleagues name in the comments section to directly IM, Call, Video Chat or Email without leaving Word.
Tip 5: Permanently delete cropped areas of a picture
This was something that I didn’t discover until recently. Did you know that if you crop a picture in Word and then send that document to a colleague, they can see the full, uncropped picture and restore it to the original? Imagine the embarrassment of cropping out a company logo or sensitive information only for it to re-appear again with a simple click of the Crop button by a ‘Word savvy’ colleague. Don’t be an amateur, ensure you permanently delete all cropped areas before sending.
Select a picture in your document
Click the Picture Tools contextual ribbon
Click the Format tab
Select Crop from the drop-down menu
Drag the handles to select only the part of the picture you would like to retain
Click Crop again
Once the picture has been cropped ensure that you permanently remove the cropped section so that it cannot be restored by a colleague.
Select the picture
Click Compress Pictures
Place a tick in the ‘Delete cropped areas of picture’ tickbox
Tip 6: Touch/Mouse Mode
In an increasingly mobile world it is becoming extremely important to be able to access and work on documents and files ‘on the go’. Mobile devices play a huge role in this with more and more people working from Smartphones and tablets. The new Touch/Mouse Mode in Word (available across all Office applications) optimises the spacing between commands for use with a mouse or a touchscreen. Yes, Microsoft have developed a solution to ‘fat finger’ syndrome. Hoorah!
Select the Touch/Mouse Mode icon on the Quick Access Toolbar
Tip 7: Border Painter Tool
This new feature in Word 2013 enables you to fully control your border style and placement in a table. Go crazy with your painting and decorating and really make those tables stand out!
First, setup the colour, weight and line style for the border
Select the table
Click the Design tab under the Table Tools contextual ribbon
Set the desired Border Style, Pen Colour, Line Weight
Click Border Painter. The curser will change to a paintbrush
Paint over a border of the table to apply the new style
Tip 8: Adding online video
Have you found a really awesome video on YouTube or Bing that you would love to be able to insert into your Word document to really make it come alive? Utilise Word 2013’s Online Video feature to search and add any video into your document.
Click the Insert tab
Click Online Video
Select Bing Video Search, YouTube video search or paste the video embed code directly.
Select a video
The video will be inserted into your document.
Click the Play button
You can also share the link to the video on social media from within the Word document.
Click the Share icon
Select a social media service to share the link on.
Office 2016 is released on 22nd September 2015 and hits the volume licensing pricelist on the 1st October. What’s new? Some will be disappointed it’s not radically different from Office 2013. Others will breathe a sigh of relief and appreciate the consistency between the versions whilst having a good level of improvement over Office 2013 which was a fantastic suite of applications. We’ll be running a series of hints and tips blog posts over the next month detailing the main changes.
For now, if you’re a Microsoft partner you can visit the Microsoft Drumbeat site, packed full of sales training events and resources to help you start, grow and accelerate your Office 365 practice (registration required).
If you love videos of Americans waving their hands around too much and generally being very excited with words like ‘super’ and ‘cool’ then take a look at the 400 second-long Vimeo video.
Q – Is this going to be an automatic upgrade from 2013 to 2016 if I’m on Office 365?
A – No. If you want to deploy Office 2016 you’ll need to do that using your usual deployment processes, e.g. via the Office 365 portal. There will be automatic upgrades in the future but there aren’t any full details on that yet, for example how to accept or prevent the automatic upgrade to 2016.
Q – Will Office 2016 still be available as a perpetual, on-premises product?
A – Yes, Office 2016 is available on-premises and through Office 365 subscriptions. Unless you have Software Assurance on Office 2013 on-premises, you’ll need to buy the licence to Office 2016 as it’s a completely new version. Office 365 includes new version rights so if you’re on an Office 365 which includes the Office apps (e.g. E3, E4, Business, Business Premium, ProPlus) you have the rights to 2016 immediately.
Q – I’m on Office 365; how long can I stay on the 2013 release?
A – For 12 months after the release of Office 2016.
Q – InfoPath is no longer included in the 2016 release, where can I get this?
A – InfoPath 2013 is the last version and can be downloaded from Microsoft’s download centre. Your Office 365 ProPlus licence allows use of InfoPath 2013 and it will still be supported
Small and midsize businesses (SMBs) face a complex and changing landscape when it comes to understanding all of the different ways new technologies can help their businesses.
Many Microsoft partners are capitalising on the opportunity to grow their businesses by expanding their roles to that of a trusted advisor and business consultant — looking for ways to help SMBs modernise across the IT platform.
Microsoft is focused on helping you deliver solutions that address your customers’ key goals and business challenges. So you can use technology to help make them be more successful, whether by improving operational efficiency, protecting data, helping employees be more productive, or better connecting with customers. In fact, only Microsoft offers a complete platform with the flexibility to deliver the solutions your customer’s need, from server to cloud, desktop to mobile devices.
ModernBiz Technical Series
The ModernBiz Technical Series provides training, demonstrations and hands-on instruction on how to use the latest Microsoft technologies to deliver solutions to SMB organisations. This set of training courses is designed to prepare Microsoft value-added reseller (VAR) partners to help customers get the benefits of the modern business by providing solutions and services that span the entire IT ecosystem, from server, to cloud, to devices.
In this training, you will:
Get hands-on experience: With a focus on building real-world solutions, this training consists of presentations, demos, and hands-on labs.
Get the skills you need to build real-world SMB solutions: This technical series is designed specifically for partners working with SMB customers to build solutions using the latest products and technologies from Microsoft.
Any of the ModernBiz Technical Series courses can be attended as a standalone course or as a part of the complete series.
Who should participate: The ModernBiz Technical Series course is for Microsoft value-added reseller (VAR) partners who work with small and midsize organisations. The training is designed for those who are ready to learn more about meeting the technical needs of SMBs with Microsoft solutions.
Audience: IT Professionals, Consultants, SMB Resellers
Level: 200 (Technical) This training aligns to the Microsoft ModernBiz campaign for SMB partners.
There are free one or two day courses for each of these key technology areas.