Blog Archive

Licensing Gotcha #1 – OWA

Microsoft Licensing ClarityIf you’ve dealt with Microsoft Licensing, you’ll know there are lots of exceptions, gotchas, myths and riddles.  We want to clear some of those up in our blog posts so number one is a good place to start.  These all assume basic Microsoft licensing knowledge but if you need to top this up with some free training, look at our licensing courses or contact us.

Microsoft Exchange Server is licensed in a server and CAL (client access licence) model.  The CALs can be per-user or per-device.  These CALs provide the user or device rights to access the Exchange server but you need a client to read and send email, deal with calendar entries and so on.  The obvious client is Outlook; part of the Office family of applications.  In the past the Exchange CAL included an Outlook licence but this was no longer included after Exchange Server 2003 and since then, Outlook needs to be purchased separately.

Perhaps you don’t have Outlook?  That’s ok because the Exchange CAL provides the rights to access e-mail, calendar, contacts and tasks through either Outlook Web App (OWA) or through a mobile device via Exchange ActiveSync.

Typically a user will access their mailbox from a number of devices.  This is fine if you have deployed Exchange per-user CALs; the user can log into OWA from pretty much any internet connected device (hotel kiosks, airport lounge machines, home, work, internet cafés, etc.) and the user can synchronise to any supported mobile device.

If you have deployed per-device CALs then the user can only use OWA from licensed devices and can only synchronise from licensed mobile devices.

Hopefully you see the gotcha here.  Exchange is brilliant at providing access anywhere, anytime and on any device but only if you licence per-user.  For organisations that have per-device Exchange licensing, anywhere access becomes extremely restricted.


IE Auto-Scaling Annoys Me

Like many people, I work on multiple monitors.  Take now for example; I’m writing this blog post on my main monitor and Jeremy Kyle is playing in Internet Explorer on the second monitor (is he the father and the brother?  Who really cares?).

My monitors are set to different resolutions and when I drag an Internet Explorer window from my main monitor to the second monitor, it automatically scales to 150% which makes it unusable.  This ‘helpful’ behaviour is actually extremely annoying to me but there is an easy way to prevent this from happening.

Right click your desktop and select Screen Resolution.  Click Make text and other items larger or smaller and then check the box for Let me choose one scaling level for all my displays.

Stop Internet Explorer auto scaling

 

You may need to sign out of Windows and back in again for some bizarre reason but that should solve the issue.

Now, back to Jeremy Kyle, “My teenage son picks his nose too much”.


Dude, where’s my data?

Office 365 Logo

A play on the film title from “Dude, where’s my car?” in which, well actually, we can’t remember the plot but we do remember the title and something about a scene with tattoos.

Putting movies to one side, customers often want to know where their mailboxes reside in Office 365 and Exchange Online.  If my billing address is in Europe I can assume my data centres are located in Dublin and Amsterdam but can I verify that?  Also, the mailboxes for individual users and resources might be in either of those locations.  Here’s where a little PowerShell comes in handy.

There’s a useful getting started with Powershell resource on the Microsoft website.  You can also search the web for myriad explanations but be aware many of these will be out of date especially if you’re using Windows 8.1.

Windows 8.1 includes PowerShell by default so on the Start screen, type PowerShell, right-click Windows PowerShell in the search results and select Run as Administrator.  I’ll explain why you need to run it as admin in a moment.

How to run Windows powerShell

 

So many memories of the old DOS window.  PowerShell doesn’t speak Office 365 by default so we have to teach it by installing the Windows PowerShell cmdlets for Office 365 management and deployment.  Your keyboard should now be bristling with all the power you have at your fingertips.

1 – Set the PowerShell execution policy.  The default setting for execution policy is restricted which would not allow PowerShell to run scripts at all; secure but not very useful.  RemoteSigned tells PowerShell it can run scripts on the local machine but any scripts downloaded from the Internet must be signed by a trusted publisher.  This is also why you need to run PowerShell as administrator; in order to change this setting.

Set-ExecutionPolicy RemoteSigned

2 – Set your Office 365 admin credentials.  A popup window will appear where you type in your Office 365 admin user and password.  This will also tell the PowerShell cmdlets which Office 365 tenant you want to connect to.

$cred = Get-Credential

3 – In order to perform administrative tasks on Office 365 you can connect to the online service using the credentials you supplied.

Connect-MsolService -cred $cred

4 – You can list all the Office 365 admin commands.  Note that Exchange, SharePoint and the other cloud services have their own set of commands.

Get-Command –Module msonline

5 – To connect to Exchange Online, type the following commands.  You don’t need to change anything here; again, the tenant to connect to is specified by the login ID in the credentials.

$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://ps.outlook.com/powershell -Credential $cred -Authentication Basic -AllowRedirection

Import-PSSession $Session

6 – Return your mailbox information to see where they’re located.  If the server name begins with db you’re in Dublin and if it’s am you’re in Amsterdam.  Happy travels!

Get-Mailbox


How to Access the Old Print Preview

Microsoft have to please over a billion users of Office so there will always be some elements that people aren’t so keen on. You can tell we’re being diplomatic here can’t you? I must admit I stayed with the Windows Classic start menu when I used Windows Vista back in the day.

I had a question from a user at Land Securities during an Office demonstration session. The person was working on a small device and the Word print preview in Office 2010 and 2013 only shows the document in half the window. He missed the old print preview dialogue box where he could view the document as full screen.

Office Print Preview Window

 

When features of Office are deprecated, some of them remain in the product but are moved into the background. This is one of those features. To access the old-style Print Preview window you need to customise the Ribbon or Quick Access Toolbar.  Let’s use the Quick Access Toolbar for our example.  To add commands to the toolbar, click the arrow to the far right.  As the command in question is not in the common commands list, select More Commands….

Customise the Ofice Quick Access Toolbar

 

This will show the Customise Quick Access Toolbar page in the options window.  Click on the dropbox and select All Commands to display an alphabetical list of all the Word commands; a surprising number of them.

Office Ribbon Commands

 

Scroll down in the list until you see Print Preview Edit Mode.  Double-click the command to add it to your Quick Access Toolbar.

Add a command to the ribbon

 

And now you have the Office 2007 style Print Preview window at the touch of a button.

Command added to the Quick Access Toolbar

 

Whether it will still be there in the next version of Office is anyone’s guess of course, so caveat emptor or another suitable Latin phrase for be careful when using old Office commands.

Old style print preview

 


Excel Chart Conditional Formatting

Craig from Lloyds Banking Group asked if Excel’s conditional formatting can be applied to charts.  I’d like to provide the answer in the form of a news sandwich (good news, bad news, good news).

It’s a nice sunny day as I’m writing this article.

No, you can’t I’m afraid, Craig.

You can create a pseudo conditionally formatted chart.

Excel Conditional Formatting – a Recap

You have a list of customer accounts and due dates in Excel.  You want to highlight customers who have due dates within the next week in orange and you want to highlight customers who have due dates within 2 days in red.  That makes your list more readable and Excel more human.  Back in the old days of Excel 2003 you’d need to do some functions to achieve this and you were limited to 3 conditions per cell.  Conditional Formatting was introduced in Excel 2007 and has improved ever since.  It allows the user to create some nifty formatting based on cell values with just a few clicks.

Excel Conditional Formatting Date Example

 

You can also choose conditions based on averages, top %, text occurring, duplicate values and many more.  You can also use icons such as red, amber and green or perhaps smiley faces.  And also you can choose data bars which allow you to visualise the value according to magnitude.

Excel 2013 Data Bars screenshot

 

So Craig’s question asked if one can apply this Conditional Formatting feature to bars or segments within Excel charts?

And if you recall, the answer is no but you can create a similar experience by categorising the data into distinct, banded columns and charting those.  Let’s take a table (below) with data series and values which we want to chart.

Excel table ready for conditional formatting

 

A default chart will represent each column in the same colour (below).

Excel chart without conditional formatting

We want each bar coloured according to the band of values it falls into.  To do this we can create the bands we want to chart as additional columns in the table (below).  In rows 1 and 2 we list the boundaries of each band (e.g. 0 and 500).   Each value cell (G4-M16) is then calculated using a formula =IF(AND(G$1<$F4,$F4<=G$2),$F4,””).  For each cell, the Amount (F4-F16) is compared to the boundaries.  If the value falls within a boundary then value is written into that cell, otherwise it’s left blank.  The result is our mapped table below.

Chart with additional columns for conditional formatting

 

Now if we click on the default chart we created earlier, Excel highlights the table data used for the chart (below).

Excel data used for the default chart

 

Drag the red and blue boxes to select our new data (below).

New data selection for chart

 

And voila, we have our chart with conditionally coloured bands and a key to those bands (below).  Note, in true Blue Peter fashion I’ve adjusted the colours for each band and also adjusted the gap and spacing of the bars (simply double-click on any of the bars and you’ll have access to those settings).  This is a rough and ready but effective solution.  It won’t be the most efficient for large volumes of data because we are running the calculations for every cell repeatedly.  We could use VBA for a more efficient method but that’s another blog entry for the future…

Excel chart with conditional formatting