Featured post

National Rail Strike – Latest News

UPDATE – 21 May 16:40

Planned industrial action has been suspended

Good news, the planned strike has been called off!

Many thanks to all our customers and staff for the support in ensuring we were prepared to deliver all our planned events!

Good news training team, you don’t have to spend Monday night in a hotel. ;-) enjoy your long weekend.

Regards

STL

Industrial action due to affect National Rail services on Monday 25 & Tuesday 26 May 2015

The RMT have announced that Network Rail workers are to stage a 24-hour national rail strike from 5pm on bank holiday Monday in a dispute over pay.

As with any potential disruption to our training schedule, we are taking additional measures to ensure all our courses run on the day affected (Tuesday 26th May). Whilst there is a possibility that the strike may be averted we will assume it is going to happen.

Our commitment – courses never cancelled

Again we are committed to running all our planned courses. In the last 10 years we’ve only had 27 out of 12,364 courses disrupted!  As you can see it is a rare event and we are confident that our training delivery will not be disrupted with this latest rail strike.

As we have done in the past, should the worst happen, we will reschedule the course with delegates agreement and offer a free course.

Your options:

  • Come along as planned safe in the knowledge we will be prepared
  • If you face travel disruption, you can reschedule your course, please see options here.

It is our understanding that should the strike go ahead provision will be made by Network Rail for rail ticket refunds, please see links below.

Useful resources:

Our status page

Latest national rail strike information from Network Rail

Network Rail status page

follow #IndustrialAction

BBC – Network Rail workers vote to strike…

Featured post

Best in Class Training – Be delighted not just satisfied

Our new homepage goes live.

(April 10 2015) We have brought some firsts and plenty of innovation to our industry over the years. This has led to frequent “flattery” from imitators in our market and we feel it is time to provide even more proof to our claims.

To this end you will find our new homepage features proven performance. You can not only drill-down into our own record on performance – you can see what our customers say. Check out independent reviews and browse over 40,000 customer feedback testimonials.

We will continue to set the bar for quality, value and transparency in our market and wish our imitators the very best of luck. We thank our customers for helping us make a difference as a training partner and look forward to forging new long term mutually beneficial relationships.

Kind regards

All at Best STL

 

Featured post

Four benefits of Moving to Office 365

By J. Peter Bruzzese

The recent global recession is still quite fresh in everyone’s minds. From an IT perspective it forced IT administrators to tighten their belts and hold off on upgrades of both hardware and software for quite some time.
The growth of “cloud” solutions (like Office 365) has provided IT teams with another option when considering new hardware and software. Here we’ll take a closer look at the features available, benefits and a key consideration when moving to Office 365.

What is Office 365, and why move to it?

The name is somewhat confusing because it appears to be the next flavor of the Office product suite (Word, Excel, PowerPoint, etc…).  In fact, Office 365 is many things, it is a cloud-based, hosted services solution for Office applications, email, collaboration, and more.
Exchange, SharePoint and Lync have moved from a traditional on-premise hosting to Microsoft datacenters (i.e.. their “cloud”). Instead of paying for the software upfront, you pay as a monthly/annual subscription for those services (hence the term Software as a Service or SaaS).

Office 365 has a variety of different packages to choose from and some of them offer the Office Suite as part of the subscription too.  If you choose one of these plans you can put the latest flavor of Office (Word, Excel, PowerPoint, etc.) on desktops/mobile devices.  And there are a variety of other services offered with Office 365 like OneDrive for Business, Yammer, Office 365 Video and more.

There are some key reasons why a move to Office 365 can benefit your organization from the perspective of an IT team getting ready to decide on-premises vs. cloud.  Let’s consider four benefits for moving to Office 365 and one key user consideration for IT administrators.

Four benefits of moving to Office 365:

No-Cost Server Infrastructure:  If you are looking to modernize your server-side solutions the cost of upgrading your existing infrastructure could be exorbitant.  Moving mailboxes to the cloud eliminates that concern and cost.  Microsoft will worry about the hardware and storage, and you can pay as you grow for easy scalability.

No More Upgrades or Fixes:  Much of the stress of an admin is handling fixes and upgrades for solutions to ensure they are patched properly and as secure as possible.  With Office 365 this is all handled for you.

Availability:  Microsoft provides a 99.9% SLA for availability.  Whereas on-premise you would have to have redundant servers on-site and additional servers in an off-site location to provide that level of availability, Microsoft has it all in place from day one to ensure your users have consistent access to their email and other services.

Services Offered:  As mentioned earlier, Office 365 is a mix of different services.  It’s quite amazing really when you consider all the different tools provided.  You can access the services through traditional means (Outlook connected to Exchange Online) or through browser-based connectivity (Yammer or SharePoint Online through your browser).

Upon logging into your portal you are offered a variety of tools to choose from, and the list is growing!  Note the options in the figure below.  OneDrive, Sites, Delve, Video, Office Online (Word/Excel/Point/OneNote) … offering a compelling price/features proposition.

collaborate_office_online

Office 365 App Launcher – benefits of moving to office 365

So we’ve covered four benefits of moving to Office 365, what could the key consideration be? Here it is…

Training

Take a user who has been on Windows XP with Office 2003 for the past 10 years.  Give them a new laptop running Windows 7/8 (v10 coming soon) and Office 2013.  Introduce the user to the new tools for communication and collaboration like Yammer, SharePoint, OneDrive for Business and so forth.  Instead of praising your team the users vent frustration at the dip in performance as they struggle to process all the new upgrades.

Don’t do that to your people.  Going from the menu structure of legacy Office into the new ribbon interface alone will be an overwhelming task for some.  If you have gone with Windows 8, the new interface is quite a step change (even for experienced users).

Support your people.  You want to give them the latest and greatest and you want them to experience the productivity boost that should come with new hardware and software possibilities.

But you must do so through training first.

Manage the change for users through classroom training, with all the benefits of hands on exercises under the guidance and support of an experienced trainer.  Or even run workshop sessions BEFORE you put that new laptop or desktop in front of them with all the new bells and whistles.

And then you might want to provide 24/7/365 support training through a video portal, which perfectly complements any classroom based training. An eLearning portal with an easy to use interface and short searchable learning clips helps users improve and maintain performance.

We are certainly living in exciting times.  Cloud solutions like Office 365 are offering small-midsize business an opportunity to have enterprise grade solutions right at their fingertips, without all the server side heavy lifting being placed on the IT admin.  The end-users will love the new possibilities opened to them and the increase in productivity through new communication and collaboration tools.  Exciting times indeed!

J._Peter_Bruzzese J. Peter Bruzzese (Office 365 MVP) is the CIO of ClipTraining.com, providers of short, task-based video training through an online portal solution.  He is an internationally published author with over a dozen titles to his credit about Windows/Office/Exchange/etc.  He is a technical speaker for Microsoft and others.  He writes a weekly column for InfoWorld entitled “Enterprise Windows” and is a strategic technical consultant with Mimecast.  You can follow JPB on Twitter @JPBruzzese and email him at jpb@cliptraining.com

For information on eLearning services in the UK, ClipTraining partner and UK distributor, Best STL, can be emailed at info@microsofttraining.net

Further reading

Office 365 Training in the ClipTraining Library
Classroom based MS Office training

36.5 Office 365 Features That Will Boost Your Productivity
What’s In & What’s Out In Office 2013
A well-trained I.T. team: Your company’s secret weapon

 

Data Visualisation in Excel

By Ann K. Emery

With an estimated user base of 500 million people worldwide, many of us use Excel at work and personally for home budgets, projects, to-do lists, and more.

It doesn’t take too much use of Excel before you reach the point of trying to express your data visually. This is where Excel charting options and other tools are on hand to help you with data visualisation in Excel.

What’s the right chart for your data visualisation in Excel?

Should it be a pie chart, bar chart, line charts, exploded charts? Well fret no longer, and read on!

In this article, I share four strategies for effective data visualisation in Excel:

  1. Consult a chart taxonomy
  2. Surround yourself with visualization inspiration
  3. Sketch plenty of drafts by hand
  4. Explore preliminary patterns via computer

Your data will shine, you can stop banging your head against the wall, and your viewers will applaud the visual clarity you’ve provided.

Strategy 1: Consult a Chart Taxonomy

First, gather inspiration from a chart taxonomy. By taxonomy, I mean diagrams that classify chart types and help you decide which one to use.

Excel’s Insert Chart Menu

This is the easiest taxonomy to use—the classic menu of Excel’s chart options that you’ve probably seen a million times before. The only problem is that some of these charts are, well, pretty rotten. Excel will suggest awful charts like 3D exploding pie charts and radar charts that are impossible to read and interpret. But, this menu is still a good starting point. And that’s where the additional taxonomies come in—to fill in the gaps.

Excel's Insert Chart Menu

Chart Suggestions—A Thought Starter

Andrew Abela has a well-known Chart Suggestions guide on extremepresentation.com. This guide is wildly popular because he groups charts by function: comparison, distribution, composition, and relationship.

Andrew Abela's Chart Suggestions--A Thought Starter

The Graphic Continuum

Jon Schwabish released the Graphic Continuum in 2014. The project began as a large poster—you can see him holding it up. He also created 8.5 x 11 inch laminated reference cards.  Both the poster and reference cards are available on policyviz.com.

Jon Schwabish's The Graphic Continuum

Essentials Chart Choosing Tool

A year and a half ago I started designing my own chart taxonomy. I sketched all the charts I use on a regular basis on 3.5 x 5 inch index cards.

Then, I sorted, sorted, sorted, and sorted. I thought about which charts were best for displaying patterns over time, which charts were best for part-to-whole dynamics, and so on.

Ann K. Emery's "Essentials" Chart Choosing Tool

I tried rearranging my thoughts into a handout, a PowerPoint slide, and even mind-mapping software. I wasn’t satisfied with the static versions I produced. Last fall, the Essentials chart choosing tool was born.

You can use the filters to explore the chart types on your own, just like I had sorted the index cards on my living room floor. You might focus on charts that are ideal for patterns over time, or for graphing a dispersion or spread of data, or on charts that can be made within Excel.

Data Visualisation in Excel - using the essentials chart choosing tool

Then, you can click on icons that interest you. Each chart has a separate page with a description, examples, and links to tutorials.

Strategy 2: Surround Yourself with Visualization Inspiration

Data visualization experts are constantly on the hunt for good examples.

On Twitter, I follow @NYTgraphics, @PostGraphics, and @PewResearch to ensure that my feed is full of strong visualizations.

On Pinterest, I store examples of everything from bar charts to computer-free visualizations, like charts made from stones, fruit, and paper.

I also print full-page, full-colour charts and tape them above my desk. When I’m beginning a new project, I glance up at my gallery to get ideas for chart styles.

Taping chart examples above your desk for inspiration

Strategy 3: Sketch Plenty of Drafts by Hand

I also sketch, draw, and doodle plenty of drafts before I create anything on the computer.

Here’s how it works:

  • Grab some scrap paper and markers, and then step away from the computer. Give your brain some creative time away from the screen rather than staring at a blank monitor.
  • Set the timer for 10, 20, or even 30 minutes. Doodle as many drafts as you can. I aim for at least 10 drafts of each dataset. Only the best one will survive the editing process.
  • Share drafts with colleagues early and often. Gather as much feedback as you can.
  • Next, create one or two of those promising drafts on the computer.
  • Edit, edit, edit! I adjust Excel’s default settings so that my charts follow the best practices outlined in the Data Visualization Checklist.
  • Put your easiest-to-follow chart in your final presentation or report.

The importance of sketching graphs on paper before sitting down to a blank computer screen

Strategy 4: Explore Preliminary Patterns via Computer

I also use quickie computer strategies, like Excel’s spark lines and conditional formatting, to help me narrow down the focus of my charts.

Spark Lines

Spark lines are miniature within-cell bar charts and line charts. They’re available in Excel 2010 and higher on both PCs and Macs.

To insert spark bars or spark lines, highlight the row or column that you’re interested in, and then head over to Insert > Spark Line.

It takes a lot of mental energy to read each and every number in this table, so spark lines provide an instant glimpse at the highs and lows in my dataset. I use these insights to think about which patterns I might want to visualize in my final product.

Using Excel's spark lines and spark bars to explore preliminary patterns in your dataset

Conditional Formatting

Conditional formatting is one of my favourite features of Excel. These features allow you to automatically colour-code your cells based on their contents.

To apply conditional formatting, highlight the rows or columns that you’re interested in, and then head over to Home > Conditional Formatting.

In this example, I turned my regular table into a heat table using Colour Scales. The larger numbers automatically get coloured in with darker colours and the smaller numbers get coloured in with lighter colours. Now, I can spot the highs and lows in my dataset instantly.

Sometimes I use these heat tables as inspiration for future charts, that is, to locate patterns that should get emphasized in other bar charts or line charts.

Other times, these heat tables are my final product. As shown below, I added a title and made this table printer-friendly. Then I can print the colour-coded table and share it as a handout at a meeting.

Using Excel's conditional formatting (automatic color-coding based on the cell's contents) to explore preliminary patterns in your dataset

What are your strategies for selecting the right chart for your dataset? Please share your tips in the comments section below.

Loop Through Excel Worksheets and Workbooks

Using loops to easily make changes across multiple worksheets

Loops are one of the key tools in Excel VBA when we need to perform tasks through a number of objects (cells, worksheets, charts, workbooks etc.) .  Here we will look at how to loop through Excel worksheets and workbooks.

Loop Through Excel Worksheets

Loop Through Excel Worksheets

Below you will find three examples using different loops but all three will perform exactly the same task.

The For Each loop

  1. An object variable (sh) is used and declared as Worksheet to tell Excel that we want store worksheets (the address) in the memory  of our computer (Dim sh As Worksheet).
  2. The For Each loop will loop through each worksheet in the active workbook (For Each sh In ActiveWorkbook.Sheets).
  3. The code will add 500 in A1 in all sheets in the active workbook.

Sub LoopSheets()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Sheets
sh.Range(“A1″).Value = 500
Next sh
End Sub

The For Next loop

  1. A data variable is used to store a whole number (integer) in the computer’s memory (Dim iCounter As Integer).
  2. The For Next loop is used to loop through all sheets in the active workbook but the loop needs to know how many worksheets there is in the active workbook (ActiveWorkbook.Sheets.Count).
  3. The iCounter variable is used to move through the worksheets and the value 500 is entered in A1 in all worksheets in the active workbook (Sheets(iCounter).Range(“A1″).Value = 500).

Sub LoopSheetst2()
Dim iCounter As Integer
For iCounter = 1 To ActiveWorkbook.Sheets.Count
Sheets(iCounter).Range(“A1″).Value = 500
Next iCounter
End Sub

The Do loop

  1. A data variable is used to store a whole number (integer) in the computer’s memory (Dim iCounter As Integer).
  2. 1 is stored in the iCounter variable (iCounter = 1).
  3. Do Until loop is used to run until criteria is met in this example until the value in the variable iCounter is total number of worksheets in the active workbook plus one (Do Until iCounter = ActiveWorkbook.Worksheets.Count + 1).

Sub LoopSheets3()
Dim iCounter As Integer
iCounter = 1
Do Until iCounter = ActiveWorkbook.Worksheets.Count + 1
Sheets(iCounter).Range(“A!”).Value = 500
iCounter = iCounter + 1
Loop
End Sub

Loop Workbooks

loop workbooks

Below you will find three examples using different loops but all three will perform exactly the same task but this time the loops will loop through workbooks.

The For Each loop

  1. An object variable (wBook) is used and declared as Workbook to tell Excel that we want store workbooks (the address) in the memory of our computer (Dim WBook As Workbook).
  2. The For Each loop will loop through each open workbook  (For Each wBook In Workbooks).
  3. The code will add 2 in A2 in sheet 1 in all open workbooks.

Sub LoopWorkBooks()
Dim WBook As Workbook
For Each WBook In Workbooks
WBook.Sheets(1).Range(“A2″).Value = 2
Next WBook
End Sub

The For Next loop

  1. A data variable is used to store a whole number (integer) in the computer’s memory (Dim iWB As Integer).
  2. The For Next loop is used to loop through all open workbooks but the loop needs to know how many open workbooks we have (Workbooks.Count).
  3. The iWB variable is used to move through the open workbooks and the value 2 is entered in A2 in sheet 1 in all open workbooks (Workbooks(iWB).Sheets(1).Range(“A2″).Value = 2).

Sub LoopWorkBooks2()
Dim iWB As Integer
For iWB = 1 To Workbooks.Count
Workbooks(iWB).Sheets(1).Range(“A2″).Value = 2
Next iWB
End Sub

The Do loop

  1. A data variable is used to store a whole number (integer) in the computer’s memory (Dim iCounter As Integer).
  2. 1 is stored in the iCounter variable (iCounter = 1).
  3. Do Until loop is used to run until the criteria is met in this example until the value in the variable iCounter is total number of open workbooks plus one (Do Until iCounter = Workbooks.Count + 1).

Sub LoopWorkBooks3()
Dim iCounter As Integer
iCounter = 1
Do Until iCounter = Workbooks.Count + 1
Workbooks(iCounter).Sheets(1).Range(“A2″).Value = 2
iCounter = iCounter + 1
Loop
End Sub

Loop workbooks & worksheets

loop workbooks

loop sheets

 

x

In the examples below nested loops are looping through workbooks and worksheets and again the For EachFor Next and the Do loop are used to do the job.

The For Each loop

Exactly as in the examples above in this post variables are used to store the address of the workbooks and worksheets in the computers memory (Dim WBook As Workbook & Dim sh As Worksheet). A For Each loop is used to run through the workbooks and one to run through the worksheets.

Sub LoopWorkBookSheets()
Dim WBook As Workbook
Dim sh As Worksheet
For Each WBook In Workbooks
For Each sh In WBook.Worksheets
sh.Range(“a1″) = 2
Next sh
Next WBook
End Sub

The For Next loop

Two For Next loops are needed to run through all worksheets in all open workbooks. Two variables are used (counter variables) to loop one workbook at the time and one worksheet.

Sub LoopWorkBookSheets2()
Dim iWB As Integer
Dim iCounter As Integer
For iWB = 1 To Workbooks.Count
For iCounter = 1 To Workbooks(iWB).Sheets.Count
Workbooks(iWB).Sheets(iCounter).Range(“b1″).Value = 450
Next iCounter
Next iWB
End Sub

The Do loop

It takes more coding to run through all worksheets in all open workbooks by using the Do loop. Again two loops are needed one for the workbooks and one for the worksheets.

Sub LoopWorkBookSheets3()
Dim iWorkBookCounter As Integer
Dim iSheetCounter As Integer
iWorkBookCounter = 1
iSheetCounter = 1
Do Until iWorkBookCounter = Workbooks.Count + 1
Do Until iSheetCounter = Workbooks(iWorkBookCounter).Sheets.Count + 1
Workbooks(iWorkBookCounter).Sheets(iSheetCounter).Range(“c1″).Value = 5
iSheetCounter = iSheetCounter + 1
Loop
iWorkBookCounter = iWorkBookCounter + 1
iSheetCounter = 1
Loop
End Sub

Some people prefer to use the For Each loop for a couple of reasons. The For Each loop is a faster loop and normally you need less coding.

Good luck with your loops!

Useful Resources

Macro to Loop Through All Worksheets in a Workbook

Excel VBA 2013: Track Changes With the Inquire Add-In