Using the Total cell style in Excel

Trying to review a spreadsheet with unformatted data can be a real eye sore, but by using the Total cell style in Excel you can quickly give your data more context.

An example of using the Total cell style in Excel

Below we have a list of sales figures for several sales reps in a travel company. In just a few steps by applying the Total style helps the “Total” column of sales figures stand out.

Total cell style in Excel

Using a couple of Excel styles can make all the difference

1. Select the range of cells you wish to format. Goto the Home tab, then the Styles group. Then click on the “More” button the more button in Excel to expand the group. (for Excel 2013 & 2010 click on Cell Styles button, see the second screenshot below)

Using the Total cell style in Excel 2007

How to select Total option from style section

Using the Total cell style in Excel 2010

Selecting Cell Styles in Excel 2013 and 2010

2. Now within the Style options, under the section, “Titles and Headings” just select the Total option. The selected range of cells will now display the Total formatting.

In the example above, we also repeated these two steps for the row with “John”, and with one further twist, also included an additional style option under “Themed Cell Styles” which was to select a colour.

Tip: If you are experimenting with different styles you may wish to remove a cell style, to do this simply select the range of cells you wish to “reset”, then go to the Style group (within the Home tab) and select Normal from the “Normal, Bad, Good, Neutral” section.

how to remove a style in Excel

To remove a style, just hit the Normal button

What we have covered here is a simple demonstration of how using styles can lead to more engaging and professional-looking spreadsheets. You can even create your own custom cell styles which may contain multiple formatting options and can be a real time saver when dealing with similar spreadsheets.

Found this useful? Learn more about Excel cell styles:-

  • How To Use The Cell Styles Functionality In Excel 2010. Read more
  • How do you group a selection of styles on a workbook? Read more
  • A comprehensive guide to applying, creating and removing cell styles

 

 

How to calculate age from date of birth using Excel

An easy way on how to calculate the age from date of birth using Excel formula

Here we’ll look at how using the Today() function along with a less well known function, Datedif(), calculates the age from a date of birth. Even if this isn’t a pressing need of yours the example below is a handy demonstration of the usefulness of these two functions in an Excel formula to calculate age from a date of birth.

The following formula uses both these functions to achieve the desired result.

=DATEDIF(B4,TODAY(),”y”)
where “B4” is the actual cell containing the date of birth.

Here’s the formula in action.

How to calculate age from date of birth using Excel

Formula using the Datedif() & Today() functions

Tip: You can also express the age as months, or even days by simply changing the “y” in the above formula to “m” or “d” respectively.

There are many ways to achieve the same result and the example above is one such way on how to calculate age from date of birth using Excel.

How Excel stores dates:

Dates and time are some of the most common types of data that people use in Excel and the way that Excel stores dates is quite different to how we would imagine.

Instead of storing a date as day, month, year (01/01/1900, for example) Excel actually allocates a serial number (which is generated from working out how many days have elapsed since the year 1900 to that date).  And yes, any date before 1900, as far as Excel is concerned, just doesn’t exist! This is why if you don’t have the correct cell formatting for date data you get a number bearing no relation to the date!

Additional resources:

You can view answers to actual Excel users’ questions related to time and date on the following links:

http://www.microsofttraining.net/post-6898-calculated-age-person-each.html

http://www.microsofttraining.net/post-23813-calculating-ages-dates.html

For a more in-depth look at how to use dates and times in Excel:

http://support.microsoft.com/kb/214094

 

UK government pays £5.5m penalty for not listening to Microsoft

UK Government pays £5.5m to Microsoft

£5.5m says UK government should have taken Microsoft seriously about XP

Imagine you’re at an important hospital appointment and you’re kept waiting because the scanner, which potentially cost £100K, needs a re-boot. And then you discover it’s driven by Windows XP which is no longer supported by Microsoft. That would be insane wouldn’t it?

Sadly not. It’s a true story and it’s probably just the tip of the iceberg across the NHS. Just how big a problem it is we don’t know because, as The Register discovered, the Department of Health doesn’t actually know the penetration of XP amongst its million PCs spread across GP surgeries, trusts, hospitals and other organisations. An estimate from healthcare specialists EHI Intelligence back in September 2013 put the penetration of NHS PCs running XP at 85%. Its survey suggests that NHS managers were keeping their fingers crossed that either Microsoft would change its mind or some sort of deal would be done. Fortunately for them, the government was able to finalise a deal with Microsoft for its public sector users – but only for a year, only for security updates for XP, Office 2003 and Exchange 2003 and at a cost of around £5.5m. As people are wont to ask in such situations, how many nurses could that have paid for?

For many bodies such as the Metropolitan Police and HM Revenue and Customs, migrating away from XP will be completed by the end of the year. But it’s all a bit late and reeks of panic. The government’s central purchasing agency, the Crown Commercial Service, is putting on a brave face and claiming that its deal with Microsoft is saving the government £20m by centralising negotiations – but why has it had to happen in the first place?

“We see it as a serious case of hiding behind the sofa and hoping it will all go away,” says Rich. “Windows XP was born 13 years ago – that makes it venerable in technology terms. It can’t last forever.”

Microsoft has been flagging up the end of life for years and offering assistance in moving on. Yet it seems to have come as a surprise to many that’s it’s been true to its word. “Either they thought that the sheer numbers of XP users still out there would be persuasive in getting XP’s life extended or it’s an example of poor management”, says Rich Talbot, Trainer at Best STL. The NHS story suggests the latter. “We can’t even sigh and say that this is what happens in large public bodies, because organisations in every commercial space from small to large have missed the deadline too,” adds Rich.

Best STL hears grumbles about the greedy face of business forcing businesses to spend money when they can least afford it. Is Microsoft being fair in withdrawing support? Let’s look at its arguments.

  • Security issues leading to compliance risks. Security is definitely one of the biggest issues facing organisations today and Microsoft claims that an ageing OS can’t be protected sufficiently well. If that’s true, it’s important. And if organisations continue to use an operating system without updating security they will suffer. It’s quite likely that there’s a huge amount of malware silently sitting on XP systems today waiting to be initiated by attackers to steal data and devastate businesses. Even if the worst doesn’t happen, failing to comply with ever-tightening financial regulations is asking for huge financial penalties and loss of face.
  • Lack of ISV and hardware manufacturers’ support. Microsoft quotes Gartner research on its web site, arguing that a growing number of ISVs won’t support XP with new versions of their software and that manufacturers won’t support XP on their new PCs. On the other hand, one of the reasons that organisations may be holding back from upgrading is that they don’t know if their current legacy applications will work on Windows 7 or 8 or Linux or something else. Where there are specialised applications like the driver for that scanner, this could well be true – but, as Rich says, it’s time that someone found out.
  • Greater productivity with newer versions of Windows and Office. “Yes, it’s possibly true but it’s a very general claim and depends on your needs,” comments Rich. “We can all recognise this as marketing puffery.”

Is there an alternative argument that Microsoft is being harsh on government and businesses in times of economic difficulty? “We don’t think so,” says Rich. “The global financial crisis began over six years ago. How long can we keep claiming poverty? In any case, if the likes of Microsoft stopped driving for profitability, where would be the development that we all really need?”

So here’s the quandary. Microsoft is insisting that there will be no help beyond this final year for anyone. That’s hardly surprising and managers really need to avoid the temptation to sign up for the year and then promptly hide their heads again. So what’s the bottom line? Says Rich “If you’re running any applications on XP that are business-critical you need to take action now because lack of security and compliance are a real threat. If you’re driving important apps – such as that scanner – with XP then while security might not be your priority, lack of day-to-day support that puts the equipment out of action could be.”

Are you concerned about upgrading to a new version of Windows – we can help with migration and training for individuals and organisations - contact us for more information.

How to: Excel VBA clear clipboard

In Excel there’s a lot of data being moved around, perhaps via copy and paste. If you leave a lot of stuff lying around on in memory or cache you’ll get a message along the lines of “you’ve left a lot of information on the clipboard…”. Generally in VBA you can avoid this with other methods but sometimes copying and pasting is the only way. How do you clear the clipboard in Excel VBA?

excel vba clear clipboard

It’s done with a simple line of code which you place after the paste operation:

Application.CutCopyMode = False

You might see this in macro recorded code – it is created when you press ‘Esc’ on the keyboard to stop copying, or if you’ve simply pasted. This line of code empties the clipboard and clears the memory cache.

Smarter version

You can avoid these altogether by bypassing the clipboard entirely. This is one of those things you can only do with VBA. There’s a silent Destination argument of the Copy command, which isn’t obvious unless you press a space immediately after a Copy instruction. You certainly won’t get it from the macro recorder.

Here’s a chunk of code I just recorded, copying and pasting a block from one sheet to another:

    Range("A1:J4292").Select
    Range("F3221").Activate
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

Lots of typically superfluous code there. Lines that end “…Activate” are almost always useless, indicating which cell has been selected – why this is useful in this context I don’t know. Here’s a truncated version:

Range("A1:J4292").Copy Sheets("Sheet2").Range("A1")

With this command it doesn’t use the clipboard so no Excel VBA clear clipboard command required. And of course, we have one line of code instead of seven. Generally speaking less code means quicker execution, but there’s lots of exceptions to that.

If you want to explore this further, type up the .Copy bit then press space. Examine the arguments for further insight into how you can use this command.

Bonus marks – work with any range of data, from anywhere in the workbook

By prefixing the command with the source sheet (“Sheet1″ in this case), and adding CurrentRegion off the starting cell will continue selecting until it has captured all of the data (it will stop at completely empty rows and completely empty columns):

Sheets("Sheet1").Range("A1").CurrentRegion.Copy Sheets("Sheet2").Range("A1")

Excel VBA Uppercase, Lowercase and more

Ever received a spreadsheet and all the data on it is in the wrong case? You might received a dodgy dump of data from your website or database, or maybe the user-inputted data is poorly or inconsistently formatted. Here I’ll guide you through how to do Excel VBA Uppercase, lowercase and capitalising the first letter of a string. All these tasks are invaluable when you have to manipulate your data using VBA.

wrong kind of case. we want excel vba uppercase

Not this kind of case! (image copyright iconarchive)

Excel VBA Uppercase

First, converting strings to upper case through Excel VBA: you need the Ucase function. Let’s say you want to loop through column A, replacing the current cell with an upper case version:

For i = 1 to cells(Rows.Count, 1)

     Cells(i,1) = Ucase(Cells(i,1))

Next i

And that’s how you do Excel VBA Uppercase.

Excel VBA Lowercase

Lower case couldn’t be simpler: the Lcase function. Here’s an example of converting a variable to lower case:

Dim empStatus As String

empStatus = "CONTRACT"

empStatus = Lcase(empStatus)

MsgBox empStatus

How do I capitalise the first letter in a cell (or string) ?

Excel VBA does not have a native way to do this; you could use some clumsy string manipulation methods but let’s lean on the Excel functionality: the Excel Proper function. You can summon any function from Excel using the Application.WorksheetFunction method.

Here’s an example, capitalising the first letter of every cell in column C:

For i = 1 to cells(Rows.Count, 3)

     Cells(i,3) = Application.WorksheetFunction.Proper(Cells(i,3))

Next i

Three quick ways to manipulate text strings using Excel VBA,

How do I sort in Excel VBA?

Excel VBA is used for all sorts (!) of data manipulation tasks. One of the most common is sorting your data. Performing an Excel VBA sort seems trivial but some parts of it may misbehave if you’re not diligent.

I’ve seen some people try to write their own sort routine in Excel VBA, but frankly Microsoft has spent thousands (millions?) of dollars refining their Excel Sort technique, so why not just record a macro where you use that?

It starts simply enough. View > Macros > Record new macro, fill in the details, hit Sort then Stop Recording.

Play it back – seems fine.

Add some new rows, play it again – still fine.

But it isn’t.

The problem comes when you look at what has been sorted. If you notice, your macro has only worked on the same range as your original recorded macro. Extra data outside of that first selection will not be included.

A quick peek in the Visual Basic Editor (ALT + F11) will demonstrate this clearly.

excel vba sort

Boom, there it is. A reference to a fixed range of cells (A2:H31). Aside from the other typically overcautious lines from the macro recorder (thank goodness the xlPinYin method was set!) these cell references are the bits that will cause you problems. It’s the worst kind of error too, the one that doesn’t cause an error but silently causes havoc in the background.

The key is then to create a dynamic range. This is the source of many issues with beginner code in VBA. As with most VBA problems, there are many ways to solve it. Try this out:

Sub MySortMacro()

    Dim LastRow As Long
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("A2:H" & LastRow).Sort Key1:=Range("C3:C" & LastRow), _
       Order1:=xlAscending, Header:=xlNo

End Sub

Let’s take a look at that.

    Dim LastRow As Long

Create a new variable called LastRow as a Long datatype (an integer that goes roughly up to 2 billion). Why a Long and not an Integer? An Integer goes up to ~36,000. If your data is 36,000+ rows, you will run into the overflow error. 2 billion is plenty!

    LastRow = Cells(Rows.Count, 1).End(xlUp).Row

Count how many rows are in the first column (,1). Go to that last cell and do a CTRL + up arrow motion. In most sets of data this will hit the last item of data in that column. Get the row number then store that in the LastRow variable.

    Range("A2:H" & LastRow).Sort Key1:=Range("C3:C" & LastRow), _
       Order1:=xlAscending, Header:=xlNo

This heavily truncated command concatenates (or ‘joins’) the LastRow variable to the ranges required. The “A2:H” bit is the full range of data, “C3:C” is which column you want to sort by.

There you go. Simpler code, easier to read and will work with any range of data.

You can get lots of great VBA tips like this on our Excel VBA Introduction / Intermediate courses, or Excel VBA Advanced training course.

Read about our Excel VBA training solutions

How to use a VLOOKUP function in Excel VBA

VLOOKUP is one of the most useful and versatile functions in Excel. As you work further with macros it’s not uncommon to make your create an Excel VBA VLOOKUP macro. With this you get the ability to reference your tables of data, but automated.

Wait, what’s a VLOOKUP function?

The Vertical Lookup is one of Excel’s most popular commands. It’s most common use allows you retrieve data from another table of data based on a key value. For example, in one Excel sheet you may have a list of one customer’s invoice numbers, and in another sheet a list of all your invoice numbers plus other columns, such as amount, customer and invoice date. A VLOOKUP function can use the invoice number as a reference point to extract one or more other related columns of data. This avoids sloppy copy-and-paste and ensures the data remains up to date.

excel vlookup function

An example of a simple VLOOKUP retrieving a ticket price for a given country.

There are other smart uses of the VLOOKUP, such as being able to search for duplicates, group values into buckets and check to see if items exists but this is enough detail for now.

For a more thorough discussion of the VLOOKUP function, check out our article here. Even better, come on one of our Excel Advanced courses!

So what about using Excel VBA VLOOKUPs?

You can retrieve data from sheet to sheet programmatically using VBA alone, usually with nested FOR NEXT loops and variables to track your current cell position. These can be a bit fiddly and the learning curve can be a little steep (if you want to learn how to do this check out our Excel VBA Introduction / Intermediate course).

an example of excel vba vlookup style functionality

The CopyRecords macro is simulating VLOOKUP-style functionality.

Luckily, VBA provides you with the Application.WorksheetFunction method which allows you to implement any Excel function from within your macro code.

So if your original VLOOKUP in cell B2 was something like this:

=VLOOKUP(Input!A2, Data!A1:X200, 5, FALSE)

The VBA version would look like this:

Range("B2") = Application.WorksheetFunction.VLookup(Sheets("Input").Range("A2"), Sheets("Data").Range("A1:X200"), 5, False)

Notice a couple of things:  I had to insert the Sheets and Range objects so VBA could properly interpret it.

I want a sneakier version!

Don’t want to do that Sheets and Ranges business? You can adopt a little-known punctuation trick in VBA that converts things into cell references: the square brackets. Did you know you can turn this:

Range("A1") = "Fred"

into

[A1] = "Fred"

With a little lateral thinking we can do the same to our VLOOKUP:

[B2] = [VLOOKUP(Input!A2, Data!A1:X200, 5, FALSE)]

Make it more robust

The code lines above will do the bare minimum. They’ll get the job done. What if you grab the result of the VLOOKUP and store it in a variable?

result = [VLOOKUP(Input!A2, Data!A1:X200, 5, FALSE)]

And then throw an unexpected value at it?

vba error 1004

Of course! Error 1004! Why didn’t I see that coming?

Let’s add a little error-handling so your code doesn’t come to a screeching halt. There’s a number of ways you can test this, and various things you can do with the error, so here’s only one suggestion:

On Error GoTo MyErrorHandler:

  result = [VLOOKUP(Input!A2, Data!A1:X200, 5, FALSE)]

MyErrorHandler:
  If Err.Number = 1004 Then
    MsgBox "Value not found"
  End If

Or possibly

On Error GoTo MyErrorHandler:

  result = [VLOOKUP(Input!A2, Data!A1:X200, 5, FALSE)]

MyErrorHandler:
  If Err.Number = 1004 Then
    result = ""
  End If

The first example throws the error right up in your face. The second one is a ‘silent’ error that pushes a null string into the result variable. Not usually advisable as you can’t actually spot the problem but on some occasions you just want to move past the code issue. You could replace the empty string with your own custom error message.

Make it dynamic

This is all well and good but what if your data grows and shrinks? You should be on the lookout for dynamic methods. You can consider things such as dynamic range names and similar, but here’s a VBA option you can consider:

    Dim ws As Worksheet
    Dim LastRow As Long
    Dim TargetRange As Range

    On Error GoTo MyErrorHandler:

    Set ws = Sheets("Data")

    LastRow = ws.Cells(Rows.Count, "X").End(xlUp).Row
    Set TargetRange = ws.Range("A1:X" & LastRow)

    result = Application.WorksheetFunction.VLookup(Sheets("Input").Range("A2"), TargetRange, 5, False)

    MsgBox result

MyErrorHandler:
    If Err.Number = 1004 Then
      MsgBox "Value not found"
    End If

Note I went back to standard sheet and range references; they’re tricky to mix and match with square bracket notation.

What’s happening here? We set up three variables, to house the sheet name, last row and final range. Then we calculate what the last used row is with the “xlUp” method. This is equivalent to pressing CTRL + up on the keyboard when working in Excel. This finds the last row on the worksheet, and finally we set this as the range used.

There’s lots of variations on this, depending on whether you need dynamic columns too and how regular your data is, but this is a great method for getting you started.

So there it is, from soup to nuts, ways to implement VLOOKUP functions in Excel VBA.

Looking for help on your VBA projects? We offer the UK’s largest schedule of VBA training events, with all versions and levels trained. The Introduction and Intermediate levels will give you all the tools you need to get started, while the Advanced course will allow you to hook up other Office applications and communicate with databases. We can also visit your offices to deliver training, or consult on your projects. We can also offer Access VBA and Word VBA too.

Read about our Excel VBA training solutions

Spreadsheet Art – 10 of the Best

mona lisa spreadsheet art

Excel pixel art is a global phenomenon, and while it’s as geeky as ASCII art, it somehow feels a little friendlier – perhaps because it brings back happy memories of 8-bit computer games to people of a certain age.

Here are 10 of the best Excel pixel art pictures we were able to find online, although some of them are a little TOO good to have been drawn by hand.

1. Amit Agarwal – Pixelated Mona Lisa (pictured above)

Agarwal freely admits to using an automated spreadsheet art program to create his images, but an Excel pixel Mona Lisa is still worth a look, even if she wasn’t hand-drawn.

2. TormentedShopao – Boredom in the Office

super mario spreadsheet art

If this Mario scene really was hand-drawn on Google Docs (Google’s cloud-based competitor to Microsoft’s Excel) then DeviantArt contributor Shopao may be as tormented as his name suggests.

3. James (Christpunchers) – Donkey Kong in Excel

donkey kong spreadsheet art

Another from DeviantArt, and this time it’s Donkey Kong getting the Excel treatment, proving again the link between spreadsheet art and 8-bit gaming.

4. Stera8 – Trout’s Famous Catch

trouts famous catch spreadsheet arttrouts famous catch pixel art

Not actually anything to do with fishing, but this deserves a place on the list because a) it’s based on a real-life event, not an 8-bit video game, b) it was a first attempt, and c) it only took 90 minutes. Not bad!

5. Sakumoti – Megaman X in Excel

megaman x spreadsheet art

Pew pew! Mega Man X (his friends just call him X) made his first appearance in December 1993 on the SNES, some 14 years before the version of Excel used to create this picture of him.

6. Andrew G – Mario. In Excel

mario spreadsheet art

We’re not 100% sure Andrew G is the original artist of this piece, which appeared on BuzzFeed in March 2011 – not least because he says ‘Excel’ crashed before he could save it (oops), when the screenshot is clearly of OpenOffice (double oops!).

7. Mateus Ziehe – Nelson Muntz

simpsons spreadsheet art

It seems logical to transform a cartoon into pixel art, and it’s nice to see Nelson – complete with catchphrase – take some of the limelight for a change.

8. Jim Silverman – Sonic & Knuckles in Excel

sonic spreadsheet art

Too much Mario on this list and not enough Sonic? Let Jim Silverman’s Excel-based character interpretations fill that void – unless you’re looking for Tails, who didn’t make it into the picture for some reason. Poor Tails…!

9. Joey Garcia – Angry Birds Fan Art

angry birds spreadsheet art

The last videogame-inspired Excel pixel art creation on this list is not from the 8-bit era, but from the modern age of gaming apps, and features a Bad Piggy from Rovio’s Angry Birds – we think this one is a Minion Pig (if he’s a Corporal Pig, he’s lost his helmet!).

10. Google Docs – Holiday

Finally, not one to be left out, Google took to the Google Docs spreadsheet application to send a holiday greeting in 2008, posting a time-lapse video of their hand-drawn effort to YouTube.

Could a single spreadsheet bankrupt your business?

Dangerous Spreadsheets

That’s not a headline designed just to capture your attention. It’s a very real possibility.

Spreadsheets can be powerful. They’re full of functionality yet easy to use. Everyone can create and use a spreadsheet without much in the way of training.

But all those benefits are also the dangers. Spreadsheets weren’t designed to carry the burden of vast computations or huge numbers of contributing editors. If a business is over-reliant on spreadsheets – and there are often much better applications for the job – then it is up to the business to ensure that what’s being calculated is correct.

The consequences of one wrong answer can be catastrophic. We offer our Top 6 questions to ask about the critical spreadsheets in your business to protect you from failure.

1.           What’s a critical spreadsheet?

Because spreadsheets are so often created by the user population rather than the IT function the way they are developed, updated and shared is rarely regulated. This doesn’t really matter for personal use. But it becomes a serious problem where the business depends on a spreadsheet to determine budgets, pricing, billing, reporting or any other core financial data.

“There have been high-profile cases of organisations mis-reporting their finances because of errors in spreadsheets in recent years,” reports Gary Fenn, trainer at Best Software Training London (Best STL). “Not only does this hurt the company’s credibility but ever-tightening compliance regulations could mean huge penalty fines as well.”

2            Where are our critical spreadsheets?

Depending on the size of the business there could be tens of thousands of spreadsheets sitting on hard drives or in the cloud. Many will be of little risk to the business, but the challenge is to find those that could do serious damage.

“A thorough audit will start to give you those answers” says Gary Fenn. The way you do that could be anything from simply asking people to report what they’ve got through to using automated tools to scan network resources.”

If you have to comply with regulations such as Sarbanes Oxley, then flowcharting business processes with associated spreadsheets could greatly help the auditing process.

3.           Can we identify the risky spreadsheets?

The next step is to determine which of those spreadsheets are putting your business in jeopardy. The best way is to formalise a process for ranking spreadsheets according their risk. How significant would an error in that spreadsheet be to the business? How many people can update the data and the formulas in spreadsheet? How complex is the spreadsheet and how much data is it manipulating?

“It’s not enough to know where the critical spreadsheets are,” adds Fenn. “You need to create an inventory of what they are, why they were created and who can update them. And keep that inventory itself updated.”

4.           How can we protect those spreadsheets?Protect Your Spreadsheets

An obvious route is to fully utilise the security features available in spreadsheets such as access privileges and protected areas to ensure only the right people can make changes.

That’s a start, says Gary Fenn but “it’s more important is set up processes and policies which everyone can understand and that ensure spreadsheets are doing what they are intended to do without interference.”

This could include an agreed method for versioning spreadsheets across the organisation, approval processes for spreadsheet changes, and a procedure for verifying data that is input to the spreadsheet.

5.           Are we doing enough to satisfy compliance demands?

Financial regulations around the world, such as Sarbanes Oxley, have become much tighter since the global crisis. Yet there are few regulations specifically around spreadsheets. Despite that business leaders are expected to understand the risk and demonstrate how they are managing it.

“It’s all about responsibilities,” says Gary Fenn. “Make sure that both IT and individuals understand their roles in risk management, and that there is a process for reporting risk to the board. If you have a role already dealing with financial risk management, assign spreadsheets to be another aspect of their portfolio.”

6.           What about future spreadsheets?

Gary Fenn has advice for anyone about to embark on a new spreadsheet. “You should take time to design a spreadsheet before it’s used in a real business situation. Think about the final outcome – the question the spreadsheet is answering – and work backwards from there. If the spreadsheet has a business-critical role, conduct a financial risk and benefit assessment. This will help you decide the correct proportion of resources to allocate to the creation and management of the process.”

Windows XP – Going, Going, Gone…

Windows XP End of Support

Happy Retirement XP

 

If you’re running Windows XP, you’ll be familiar with that annoying update that happens when you try and shut down for the day. It takes ages, it means you can’t simply switch off and run out the door, and there’s a good chance you’ll miss your train because of that wretched ‘Updating 1 of 7’ message.

But what if there were no more updates?

What if XP was abandoned by Microsoft? What if it was left to fend for itself in a cold, hostile, update-free world? Well, that’s exactly what’s happening on April 8th 2014.

You’ll miss it when it’s gone…

XP has been one of Microsoft’s most successful operating systems to date. It was relatively bug-free (compared to the truly awful Vista), it’s stable, and the monthly patches from Microsoft made it one of the most robust and safest operating systems known to technosapiens. A lot of people are very, very fond of XP, especially in the home PC sector and SMEs. In fact, SMEs are more likely to be running on XP than almost any other OS. So we’ve become used to those monthly patch updates, even if they do make us late for the train.

But there’s just one more patch to come. After that? Well, you’re on your own.

The Zombie ‘bot Apocalypse is nigh…

Taking away Microsoft’s support for XP is no laughing matter. It’s not just home PC users that could find themselves vulnerable once the support for XP vanishes, either. Businesses, from SMEs right through to multi-nationals, are still using 2001’s finest – which means in just a few day’s time they’re potentially running some huge risks. After the final patch, there’ll be no more updates to protect your system against botnet attacks, DDOS (distributed denial of service) or other Internet nasties.

Hacker groups like LulzSec and Anonymous rely on an open back door that an out-of-date operating system provides them with, and can quickly render your entire system inert by creeping in and uploading a Trojan so ferocious that it would make the Spartans run away and cry. Remember, these are the people who shut down Sony and gave the NSA a run for their money, so it’s a threat that’s worth taking very seriously indeed.

This is just a ploy from Microsoft to get me to upgrade!

The cynical among you may well say exactly that, especially as the only sure-fire way to stay fully protected is to upgrade to Windows7 or above. However, for a lot of XP users that doesn’t just mean upgrading their software, but their hardware too. Many older PCs and laptops simply won’t have the processing power to run W8 or even W7. So, yes, those who are still tottering along using single core processors or even some early dual core set-ups may have to invest in some new tech as well as new software.

Out of MS Office hours

Another reason that this matters is that recent changes to the MS Office suite have rendered XP obsolete. Seeing as this is one of the most widely used business tools both for home businesses, SMEs and even the big players means that the changes in MS Office have had a real impact on the design of upgraded operating systems such as W7 and W8. In fact, it’s been highlighted as major contributing factors to the cessation of support for XP compatible systems altogether. Both Office 365 and Office 2013 are both incompatible with Windows XP, which means something has to give – and in this case it’s XP.

But the main worry is that the consequences of leaving yourself vulnerable could cost you a lot more than the price of a new laptop. A vulnerable system is a hackable system, and believe us, the hackers will sniff you out faster than a bloodhound chasing a Ribeye steak on a string.

Okay, you’ve scared the bejesus out of me. What do I do?

One simple word – upgrade.

Either go for Windows7 or, if you want to future-proof yourself for a little longer, Windows8 (although version 8 hasn’t been as well received as version 7). They’re both capable of supporting Office 365 and 2013, and there’s that all-important support in place for both systems well into the foreseeable future.

Windows7 is probably the most suitable for small business, and it currently commands a healthy 44% of the market. Both run faster than XP, which means less of a drain on laptop batteries. Windows8 is optimised for touch-screen tech, although that’s probably not a priority for SMEs right now. There’s also plenty of room for developing your system to take advantage of apps, and ensuring that your PCs and laptops are compatible with other tech such as smartphones and tablets.

So, what’s the panic?

It’s been shown that it takes on average around 12 to 18 months to fully upgrade an operating system within a business environment – and if you’re a big company you can look towards the top end of that estimate. You’ve got two weeks, tops. That means you need to act. Now.

So whilst we may all have a bit of a soft spot for XP, that withdrawal of support by Microsoft is going to leave around 37% of users vulnerable to cyber attacks, the risk of incompatibility with their customers’ systems, and without any access to all those wonderful applications and touch-screen wizardry. Upgrading isn’t really an option – it’s a necessity, especially if your business depends on your computer OS.

It’s time to say “Goodbye, XP, my old friend!” – and embrace the young pups that are Windows7 and Windows8.

Ready to take the plunge and upgrade to a newer version of Windows? We provide full upgrade support services. Contact us for more information.