Why did we use £700 million to catch your attention?  You’re probably aware of the data breach at the UKs Ministry of Defence (MOD) in 2022 where the details of 19,000 people from Afghanistan seeking relocation to the UK were leaked, by accident.  The breach came to light when some of these details started appearing on social media. 

The UK government moved to relocate as many vulnerable people as possible, this is where the £700 million comes in. 

Beyond the many questions the MOD face about their general handling of sensitive data, one of the central causes of the breach seems to lie with poor use of an Excel workbook. 

It would seem that an MOD official, while trying to double check some data, shared a workbook.  This was done believing there was only 120 rows of data.  But there was much more. 

This is just the latest in a long history of poor Excel use resulting in material losses. 

Austerity 

In 2010 a couple of Havard economists (Carmen Reinhart and Kenneth Rogoff) used a workbook to make the case for austerity, stating that once a government’s debt reaches 90% of Gross Domestic Product, growth reverses. 

Unfortunately, many countries followed a policy of austerity to keep debt below that magic 90%. 

Sadly, for all of us, there was a small error in their calculations.  There would be no contraction in these economies, there would, in fact, be growth.  

JP Morgan Chase & Co 

A single trader managed to lose over $60 billion and cause the company to be fined a further $920 million. 

When the dust settled it was clear that a great deal of copying and pasting of data was going on. Unfortunately, the formulas were not being checked! 

And the list goes on 

From issuing twice as many tickets at the 2012 Olympics for the synchronised swimming, GCHQ tapping hundreds of telephones by mistake, losing COVID test data, to FANNIE MAY losing $1 billion because of a dodgy formula, there is a wealth of salutary tales to explore. 

Why are we still making these mistakes? 

The first question is should we be using Excel?  Many expensive failures have occurred because Excel was simply the wrong tool for the job.  But you already have it don’t you? Why pay for another product, and all the training that goes with it? 

If you’re going to build your systems on Excel (or Google Sheets, or Libre Office, etc) have you really equipped your teams with the skills they need? 

The problem with falling back on Excel, when it’s not really the best tool, is that your teams will need to be far more proficient using Excel.  Persuading Excel to work effectively at tasks it’s not best suited for requires skill and diligence, without which it becomes massively time consuming and terrifyingly inaccurate. 

If you’re committed to Excel 

So back to the real world.  Excel is what you have, and you need to make it work, and here’s the bit you’ve been waiting for, you need to train your teams. 

Times are tough, and when times are tough training is an easy saving.  But here’s a little challenge for you, take 5 minutes and list the number of decisions, performance monitoring, stock control, strategy, payroll, asset management anything that carries a financial, regulatory or reputational risk. 

How much of that is driven by Excel workbooks?  

How often have you wondered how something has ended up costing so much? how something got so bad without anyone noticing? why on earth we made the decisions we did?  Perhaps, just perhaps poor Excel use was to blame. 

We may not save you £700 million, but having teams highly skilled and confident using Excel is a good place to start, it may keep you out of the Excel disaster hall of fame. 

Sleep well! 

The post Could some Excel training save you £700 million?  appeared first on Happy.

Excel’s answer ususally comes in the form of a Pivot Table, even if the answer is a single value. If you want to keep the result you can just ask Excel to insert the Pivot Table, and it will place it on a new sheet.

If you haven’t tried this yet, you must give it a go!

I’m a big fan of anything that makes using software quicker and easier. But more is coming!

Excel will soon be able to write formulas for you

Full disclosure here, we’re massive Excel geeks here at Happy! But we do appreciate that not everyone has time to appreciate the beauty and elegance of a well written formula – you probably have much more to focus on. So how would you feel if Excel could write your formulas for you, or help you fix your formulas if they go wrong?

Soon Excel will be able to do just that, and, as the afore-mentioned geeks that we are, we have seen it! As Microsoft insiders we get to try out all the new stuff before it’s released to the world. Sometimes it’s not fully ready as so can be a bit “buggy”, but it’s always exciting.

Let me show you how it works.

This currently only works in the Excel web app (as of summer 2023).

Next, your data. It must be formatted as a table, as Copilot uses structured references to write your formulas.

Now here’s a couple of examples.

In this first one I would like to create a Full Name field in the dataset below.

As it’s an Excel table I can just type my heading in cell G1 and the table will expand.

I now need to let Excel know what I need it to do, so I show it by entering the first couple of full names.  As soon as Excel has figured out what I’m trying to do, it will offer me a formula to finish the whole column off.  Here I only needed to enter the first two names and Excel was right on it.

If I’m happy to accept the offer, I can just click apply.

Here’s another. Now in this one I’ve been asked to provide a field with the house number and postcode. Just as before I’ll give Excel a few clues, and here’s its suggestion:

You’ll notice a few errors. This is because the data is not as consistent as the first few entries might suggest. But I’m not going to worry about that just yet – I’m going to accept Excel’s formula, and suggest corrections.

All I need to do, is to go back to the cells with errors and enter the correct data. As I do this, Excel will learn from this and rewrite the formula to try and meet the new demands.

If you get a chance to try this, you may find it’s not quite perfect as yet and still has some limitations, but this is possibly the most exciting feature to come to Excel since the Pivot Table! 

Related Blogs

• How to Create a Pivot Table — If you’ve never used Pivot Tables before, start with this two-minute video.
• Calculations on a Filtered List in Excel — In this blog, learn how to create a dropdown filter in your Excel spreadsheets.
• Save Time in Excel with Autofill — Learn how to use the useful tool Autofill. Nicky explains how in the two minute video.
• How Microsoft Excel Can Increase Your Productivity — Billy talks about some of the reasons why Microsoft Excel is so indispensable to productive workplaces in this blog.

The post Would You Like Excel to Use AI to Write Formulas For You? appeared first on Happy.

For vast swathes of the working population, IT (or computers) had never been utilised every day of their working lives. These people demonstrated their value to the organisation with other qualifications or skills that required little or no IT knowledge.

Their value was so high that, as IT started creeping into every facet of life and business, we “protected” them from this new technology as it distracted them from performing the very work that we needed from them. 

We found other members of the team who were willing to fill the IT gap, or we gave them stripped-down IT and made it so simple to use that they needed almost no understanding of the equipment they were using. This meant that they were denied the opportunity to learn various essential IT skills that had become indispensable to the rest of us. 

Our intentions were honourable – we believed we were helping – but the result is a digital “us” and “them”; some learned, some stagnated. It has become very clear that the nature of modern work means that we can no longer isolate these highly skilled, experienced and valuable colleagues from the world of IT.

Enter Teams

Microsoft Teams is a relatively simple desktop and web-based app that brings together a whole range of communication and collaboration tools.

Aside from the usual irritations of a new app not quite working like an old familiar app, most people familiar with the likes of Skype, Google Hangouts and Zoom could adapt to using Teams with minimal support. This is because they are using a range of transferable skills gradually learned over many years with different types of software in a wide variety of environments.

Many users will be completely unaware of the vast range of knowledge and skills they have amassed. In short, they are unconsciously competent – they can use software such as Teams without thinking about the individual steps required to complete a task.

The unconsciously competent value the flexibility and usefulness of Microsoft Teams, but they may be unable to recognise how challenging this software platform is to those who have been isolated from IT for all these years.  

This can also make it incredibly difficult for the unconsciously competent to support and teach those new to IT and Teams. Not everyone possesses the ability to breakdown and explain all of the individual steps that they themselves are able to execute without breaking a sweat. The unconscious nature of our competence can result in us missing vital steps in our support and then wondering why the people we’re trying to help become stressed and disillusioned with the use of the software.

Don’t lose the talent

So where does that leave us all?  We must retain the people who have all the experience and talent our organisations need, but we must also ensure the IT is fully integrated into our business.

If you’re reading this, you probably know a little about Happy. For many years we have endeavoured to make our competence (and just as often, our incompetence) conscious. This means that we are expert in breaking down both simple and complex software into step-by-step, bitesize course components. 

Not only do we understand the software, but we understand people and how stressful learning new IT skills can be. We will take delegates from the very start and not only develop those essential skills and build confidence, but have fun doing it.

The post The Curious Case of Teams and Unconscious Competence appeared first on Happy.

What’s wrong with a VLOOKUP?

• Defaults to an approximate match
• Only searches from the top
• When carrying out an approximate match will only return the next smaller value
• Must reference a table array, referencing unnecessary data, forcing unneeded calculations
• Can only return results from columns to the right of the lookup column
• The use of the column index number prevents column insertions or deletions in the lookup table

Most of these could be avoided by using INDEX and MATCH, but XLOOKUP fixes all of these and provides so much more.

How does XLOOKUP do better?

• Defaults to an exact match
• Will search from the bottom of an array or the top
• When carrying out an approximate match will find the closest larger or smaller value
• By referencing separate lookup and return arrays columns can be added or removed without causing errors
• This also means that the return array can be to the left of the lookup array
• By only referencing the data required the XLOOKUP will out perform an equivalent VLOOKUP
• As well as exact and approximate matches you can now use wild cards
• Will carry out a binary search on sorted data
• It also replaces the HLOOKUP

Interested? Then read on!

The anatomy of an XLOOKUP

An XLOOKUP looks like this: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Let’s break this down…

• Lookup_value — the lookup value, just like the VLOOKUP, is the value you are trying to match in your lookup array.
• Lookup_array — this is the column you are trying to match in, this would be the left most column in the lookup table of a VLOOKUP
• Return_array — the column or columns you require the values returned from. If you select multiple columns the new Spill feature will return a row of data from the return_array
• If_not_found – optional. No more need for the IFNA function, you can specify a value in the XLOOKUP. If you do not use this argument you will continue to receive the #NA error for value not available.

With Match_mode, we now have 4 different match options!

• 0 – exact match (default)
• -1 – exact match or next smaller item
• 1 – exact match or next larger item
• 2 – wild card character match

The wild card match could be a complete game changer! This allows you to use the classic wildcards of * and ? which means you can match parts of a string or account for misspellings.

For example:

Search_mode

This last argument is frequently dismissed by many on the web but I think this has far more potential than many realise. We have 4 options here:

• 1 – search first to last – this will find the first match starting from the first value in the lookup array
• -1 – search last to first – this will search from the last value in the lookup array. Useful if you have multiple matches but want to find the last match
• 2 – binary search (sorted in ascending order)
• -2 – binary search (sorted in descending order)

The binary search is interesting. A binary search starts at the middle value and dismisses the half of the array that the lookup value cannot be in. When looking up in very large datasets this can improve lookup speed.

XLOOKUP in action

Here is a simple example, just using the first three arguments. The account number is matched in column G:G and the last name is returned from column B:B.

In this example, the fourth argument has been used to replace the #NA error with a text string.

So is this the end for VLOOKUP?

In the short term, probably not, but as more and more organisations use Office 365 there will be less reason to use VLOOKUP and more overwhelming reasons to use XLOOKUP instead.

Want to learn more about XLOOKUP?

XLOOKUP is covered in our Excel Core Level 4 course – alongside advanced formulas and conditional formatting, VLOOKUP, Index and Match and Dynamic Arrays.

Find out more

The post How to use XLOOKUP in Microsoft Excel appeared first on Happy.

The most often quoted benefits are:

• Improved employee retention
• Productivity gains
• Increased employee motivation
• Financial savings on office space and other facilities
• Better work/life balance
• Better resilience/disaster recovery options (a pandemic virus perhaps?)

Despite the UK’s resistance to flexible working, current events may push organisations into exploring the option of their staff working from home.

The Coronavirus

Apart from the regular hand washing, government’s are recommending self-isolation if you have any reason to suspect that you may have come into contact with the infection.

There have been several projections about how many people could be away from work, but many of us, forced to stay at home, may be perfectly able and willing to continue working. We may decide to self-isolate as a precaution or because schools and other childcare is temporarily unavailable.

We would never encourage any employer to pressure unwell employees to work but it’s important to recognise the stress that fit employees could feel knowing work that they can do is piling up.

So, what’s stopping them? It’s likely that their organisation is just not set up to properly support them.

Some of the barriers to flexible working from home are often thought to be:

• Concerns about monitoring performance
• Feeling isolated from team and business
• Concerns about supporting home workers
• Concerns about home workers supporting others
• Additional costs of working from home
• Data security
• Increased communications costs

It’s also important to recognise that not all roles suit home working.

Concerns about performance with regards to home working are usually unfounded. Research would suggest home workers over-deliver. If you trust and empower your teams it won’t matter where they are physically, they will deliver, provided they have the right tools and support of course! (Learn more in Henry’s blogs about the benefits of creating happy, empowered workplaces.)

How can you facilitate home working?

By using Cloud-based software such as Office 365 or G Suite, it’s easier than ever for staff to work from home.

Use online versions of software and store files in the Cloud

IT can make sure your home workers have access to the data and tools they need. When formally part of a contract of employment, home working may require the provision of equipment such as laptops, phones, and possibly desks or chairs.

As a temporary or emergency measure, web services can enable employees to use their own computers to keep working. Web-based services such as OneDrive, SharePoint and Teams can be configured to allow online working only, preventing workers from downloading data to non-authorised devices (ideal if you have GDPR concerns). Users will not even have to install any software as web-based versions of Word, Excel, PowerPoint, Outlook and many other tools are available within the web browser.

If you don’t need the apps or large amounts of data storage, Microsoft have released a ‘freemium’ version of Teams for up to 300 users but with only 10 gigabytes of data storage.

“What about our specialist software?” I hear you ask. Most modern software vendors are very much aware of the need for modern business to be flexible and web based. Many provide APIs (Application Programming Interface) that allow web-based access without the need for expensive VPN or remote desktops such as Citrix.

Although events may force businesses into finding more flexible ways of working, adopting Cloud-based services will make your business more flexible, responsive, resilient and a more attractive proposition to existing and prospective employees.

Facilitate meetings through video calls

IT can make keeping in touch with your teams much more personal. Using Apple Face Time, Skype for Business or Teams makes video calling very easy.

If your organisation is using Office 365 you will possibly have access to Skype for Business or Microsoft Teams. These business tools not only allow simple video calls but also facilitate video conferencing, collaborating with documents and even providing training and basic IT support.

Using these video conferencing tools you can maintain support, personal and professional development, involvement and communication.

One last tip: Don’t forget to change out of your pyjamas before joining a video conference!

Learn more with our courses

• Collaborating with OneDrive and OneNote — In this half-day course, you will learn how to use OneDrive and OneNote to share documents with colleagues so that everyone has access to the same copy of the document and collaborate in real time.
• Introduction to Microsoft Teams — This half-day course covers setting up Teams and how to structure them, as well as using Channels, Chat, Files and Meetings.
• Leading Engaging Remote Meetings — After this workshop (held over video call), you will ROCK at engaging distant participants, and have tons of tips to share with others.
• Live Online Interactive Learning — Learn key skills through interactive 90-minute webinars with our expert facilitators. Our public course programme covers Excel, Word, PowerPoint and more.

The post How Home Working Can Help Your Business to Remain Healthy During the Coronavirus Crisis appeared first on Happy.

This should be the starting point of your Office 365 journey. If you work in a large organisation this may already be mapped out as part of a digital transformation programme.

The simplest scenario is often that Office 365 has come along just to give you access to the latest desktop applications and email. There may be no need for Teams, OneDrive or SharePoint at all. But beware, someone, somewhere down the line will think that all these exciting features are a wonderful idea!

It may be that you need a simple Cloud-based file store to replace an ailing file server with the added benefit of remote access and email. If no other processes in your organisation are changing significantly then the creation of sites and libraries could be very simple. Making your new libraries echo your existing file structure may help users adapt to SharePoint.

The full digital transformation is becoming more common. This involves making full use of Office 365’s feature to revolutionise your business process allowing seamless remote and mobile working.

Here are our top five tips for a blissfully easy and stress-free Office 365 roll out.

1. It’s good to talk!

You need your users to buy into this Office 365 project. You may face resistance caused by fear, anger, stress, workload, any number of reasons. The most powerful weapon you have is involvement.

Start talking to the end users at the earliest opportunity. Remember, many will have fallen victim to previous software roll outs. Making them the centre of the process will develop ownership.

Explain the reasons for the changes, promote the benefits to them and be honest about the challenges. Within organisational and technical limits, allow the teams as much freedom to design their own solutions as possible.

Find out how the end users use the current software, what works for them and what does not. Their current frustration can act as powerful motivators for change.

Make communication easy. Meet regularly, provide phone numbers, email addresses, use Yammer (other social networks are available!) and most importantly respond quickly. You are involved in so much more than just an IT roll out — you are leading cultural change.

2. What do we turn on first?

You know what you need Office 365 for. You’ve liaised, negotiated and now you’re ready to start switching Office 365 on! But where do you start?

There are two main options: the ‘big bang’ where everything is switched off and Office 365 is turned on, or a phased approach, where you turn features on one at a time.

The Big Bang method can save costs as you are not paying for two systems at once. However, it can bring chaos to your organisation — a few technical hiccups could have serious financial implications!

Meanwhile, a phased roll out focuses on the technology. Traditionally, this would mean replacing old systems and software one programme at a time. However, Office 365 is an integrated environment — turning on one feature at a time makes no sense and can significantly reduce its effectiveness, and cause a lot of frustration!

A mix of the two methods is certainly a better way forward. Moving to OneDrive to allow users to familiarise themselves with the SharePoint-style environment and Teams gives users more communication options.

3. Phase the people not the technology

The best way to phase your roll out is to focus on the people. If you skipped over “It’s good to talk” above, go back and have a quick read.

During your consultation and development of your Office 365 project, you will have identified several groups of users. Everett Rogers identified five groups of adopters to new technology:

• Innovators – approx. 2.5% of your users
• Early adopters – approx. 13.5% of your users
• Early majority – approx. 35% of your users
• Late majority – approx. 34% of your users
• Laggards (his words, not mine!) – approx. 16% of your users

Find your innovators — nurture and empower them. They will explore, test and occasionally break your new systems, and they will be energised and excited by the new technology and the opportunities it brings.

Form these people into a team and quickly roll out the full Office 365 platform to them, then roll out to the other groups as quickly as is practical. This also has financial benefits as you only buy the Office 365 licenses as you need them.

This team of ‘super users’ can also encourage buy-in from other members of your organisation. Consider getting some Train the Trainer training for some of these super users to develop a small team of in-house training support.

4. Leading from the “top”

Senior managers can so often fall into the ‘Late’ majority or even the ‘Laggards’ group of adopters. They will often claim to be too busy to attend training, briefing or work groups. In a worst case scenario some may even openly criticise and question the validity of the roll out. They will often have access to admin teams to whom they will ‘delegate’ anything involving your Office 365 roll out.

This should not be an option! Whether naturally an innovator or not, managers need to model the ‘Innovator’ role. They must always be in the first training sessions and the first to receive the latest features. Not being natural innovators, they may need considerably more support, however they will be key to the roll out being a success.

5. Effective training and support

You will need more than the Innovators and Early Adopters to use your new systems. The other adopters will need varying levels of support.

If you are going to use a training provider to deliver any training for you, identify them early and get them involved as soon as is possible. Engaging a provider at the last minute to provide standard training programmes will not serve your users well. A good provider will understand the challenges you face and guide you through a full range of options and approaches to support your roll out.

Consider providing online resources for your team too. This may be links to resources on other’s websites, such as Microsoft, or the creation of custom Quick Reference Guides or manuals to guide your users through your SharePoint Libraries. Your training provider should be able to provide bespoke online resources for your users. A good training provider should be able to advise on what works best.

The key is to make as many different types of support available as possible to cater for all of your adopters, and your budget.

It’s all about the people

A final thought. With any IT project, it’s very easy to start thinking that it’s all about the IT, but it isn’t. IT projects fail or succeed not because of the IT, they succeed or fail based on how well we have involved the users.

Good luck!

The post 5 Tips for a Blissfully Easy Office 365 Roll Out appeared first on Happy.

While SharePoint 365 is a vastly simplified proposition compared to previous versions, it is still an immensely powerful system.

Central to the whole SharePoint concept is document management. Before you can place your documents into your libraries they have to be designed and built. The process of creating document libraries is easy, too easy some might say, and this can tempt you into creating libraries and uploading your documents before you given it any thought at all.

But, before you start building your library, here are our top tips.

1. Start with the end in mind

Ignore Stephen Covey at your peril! The first thing to remember is that a SharePoint library is not a place to store documents.

Well, okay, it is a place to store documents, but that’s not the end we should bear in mind. 

All the information and data that your SharePoint site is whizzing around for you is there to support and enable your business processes. This should be at the front of your mind throughout the whole process.

Think about the flow of the information. Documents are not usually created and then stored just for the sake of it. They pass from individual to individual, group to group. It’s in ensuring this flow of information that your whole SharePoint site needs to focus on. 

2. Think about the process, not the team 

We used to design SharePoint systems around teams, with each team having their own site. 

This rarely makes sense these days. If you consider where the bottlenecks are in your business processes, I wouldn’t mind betting a slap-up dinner for two that they are where a process, and its associated information, passes from one team to another. 

The team approach is often retained because of a very poor understanding of security and permissions. Managers believe that having “their site” with “their documents” keeps them safe. The reality is that the complications of allowing other to access those documents becomes a nightmare and actually makes everything far less secure. Perhaps another blog for another day! 

Designing your SharePoint sites and libraries around groups of people will not address these issues. Focusing on the processes can. Very few business processes take place within one team, so creating a site around a process will not only make the documents more accessible but also simplifies your permission structure. 

3. Folders or metadata?

This deserves a blog all to itself as well!  The real answer is you will probably need both, but how do you decide?  Here are a few questions to ask:

• Is the library used by people from different parts of the organisation to carry out structured business processes? Or is the library used by a group of specialists who need to disseminate their data to the rest of the organisation? If the answer is yes, then metadata and possibly some folders.
• Is the library temporary or short-lived, part of a project perhaps? If the answer is yes, then feel free to provide the users with whatever they want.
• Do you need to synchronise just part of the library? Or does the library structure need to be clear in File Explorer? If the answer is yes, then folders and some metadata.

There are other questions that do not have such clear answers:

• How receptive are your end users to change? Generally, the less confident your end users are with IT generally, then the more resistant they could be to adopt the metadata approach.
• What training and support is available to end users? This could be the make or break of your SharePoint roll out. Users with basic IT skills are very unlikely to just ‘pick things up’, and if left without support, will find way to not use your beautiful SharePoint sites. I have seen people make very creative use of folders in Outlook to create a parallel filing system to avoid SharePoint! 

4. One big library or several smaller ones?

Good question! Here are some things to consider:

• Is your library likely to amass more than 5,000 documents?

If so, you will run into the ‘List View’ threshold limit. A SharePoint library can only show up to 5,000 documents in a flat, list view. You can get around this by using metadata fields to group the documents together, or use folders. If this is important, you may need to have several smaller libraries.

• Are you considering using folders to limit permissions?

If so, consider creating several different libraries. Managing unique permissions for folders in a SharePoint library can become an administrative nightmare and lead to failures in your security. Creating separate SharePoint libraries with the permissions your require is a far simpler permissions structure, and end users will be much clearer about what data should go where. This will lead to better security — remember, it’s people that ultimately make or break the security of your files.

If you have a set of documents that are for a specific purpose, or documents that are only used by a particular group, it may make sense to create a separate SharePoint library just for them.

5. User testing 

I’m a big believer in testing. If you have several options and are not sure which is best, make several versions!

By asking the end users to try several versions and give you feedback it is possible to eliminate what doesn’t work and refine your concepts until you have the best possible outcome. 

Planning ahead 

“My manager just wants one simple library with all the folders from our server just copied across.”

I’ve heard that so many times! So often a manager will hear or read how the competition is making great use of SharePoint and will ask for a library to be set up, without fully understanding its potential.

A well-designed site with libraries using thoroughly tested metadata will always be easier to grow, develop and redesign than a quickly uploaded set of folders.

The only constant is change! 

The post How to Create a SharePoint System That Will Help You Work Smarter appeared first on Happy.

Firstly, I had a quick scan for the obvious – hidden sheets, too much formatting, abandoned Pivot Tables. Nothing stood out, so I moved on to the number one cause (in my list anyway) of bloated workbooks – those “empty” cells.

What are “empty” cells?

Workbooks have a hard life. They’re often brought into being by people who do not understand them well, who fail to nurture them, who leave them in the care of thoughtless colleagues. Under these conditions workbooks can both fail to thrive and, in the worst cases, become a bit, well, feral.

One of the most innocent ways we can upset our workbooks is by not being clear where our data is. We might think its obvious – if a cell has something in, it’s data. If it’s empty, it’s not. But are we being clear about that?

Excel has very different ideas of data to us. For instance, if you were to open a blank workbook, select column A:A and click the Bold tool, do you have any data? The cells are all still empty, and to the eye nothing in your workbook has changed.

Yet something very significant has changed: the last used cell.

The culprit?

Excel knows it has a propensity for “panic buying” when it comes to memory, both working memory (RAM) and storage (file size) so it has some “self-control” strategies built in. Rather than grab enough resources to watch every cell on every sheet it only worries about the cells from A1 to the last cell that you’ve used and doesn’t worry about the rest of the sheet.

Do you remember formatting column A:A? Our last used cell is now A1048576. Not only is Excel “keeping an eye” on over a million cells, it has also saved a small amount of data about the text (even though there is none) about the cell being bold.

So, what would happen to our last used cell if I were to enter some data in cell J5? Our last used cell is now J1048576. That is now a whopping 104,857,600 cells that Excel is “keeping any eye on”. Remember, all I’ve done is formatted column A:A and entered a single piece of data into cell J5. If I’d also formatted row 1:1 my last used cell would now be XFD1048576 – that’s 17,179,869,184 cells.

Putting things right

Right, let’s fix this! If I delete the data in J5 I should be back to cell A1048576 being my last used cell, right? Wrong! Even if you delete the contents of the cell and remove its formatting the last used cell is never forgotten. So what should we do?

1. First of all, find your last used cell using the shortcut CTRL + END. If this cell is significantly beyond your data you’ll need to remove the offending rows and columns.
2. To do this, find the first row beneath your data, select the whole row and use the CTRL + down arrow key to select all of the rows from here to the bottom of the worksheet.
3. Then use CTRL + Minus key to remove the rows. The rows will immediately be replaced with nice new empty ones.
4. Do the same for the columns and you should notice a sizeable reduction in your file size.

Deleting or clearing the contents and formatting will not work.

After doing this on just one sheet in our workbook the size fell from 30MB back to the more usual 108KB.

I have to be honest with you good people and state that it’s not always this easy, but this is a good place to start!

Related Blogs

• Save Time in Excel with Autofill – Learn how to use the useful tool Autofill. Nicky explains how in the two minute video.
• How Microsoft Excel Can Increase Your Productivity – Billy talks about some of the reasons why Microsoft Excel is so indispensable to productive workplaces in this blog.
• How to Create a Pivot Table in Excel – Pivot tables are the perfect way to sort and analyse data in Excel. This two minute video takes you through the process of how to do so. 
• Calculations on a Filtered List in Excel – Darren talks through the process of how to create a dropdown filter in your Excel spreadsheets.

The post The Mystery of the Humongous Excel File appeared first on Happy.

When learning a skill such as using IT effectively, elements of involvement, experimentation and questioning remain vital. Our classroom courses are highly involving courses when delegates are “doing” and we need to capture that principle of involvement with any alternative to the traditional classroom.

Happy’s Live Online Interactive Learning combines the cost effectiveness of online training but retains the key to effective learning: involvement.

What is Live Online Interactive Learning?

Live Online Interactive Learning is an online seminar or webinar. Learners log in to the software such as Skype on their own machine and can be anywhere – at their desk, or at home. The trainer then guides the learners through the material, encouraging participation and interaction.

Each session is 90-minutes long and has up to 10 learners per session. This ensures that your trainer can give full one-to-one support where necessary – just as we would in the classroom. Each session focuses on a key area, giving you the tools you need to succeed.

We have a range of available sessions covering Excel, Word and Office 365 – or we can create a bespoke session just for you.

Here are five reasons to try Happy’s Live Online Interactive Learning solutions:

1. A live session led by an experienced Happy trainer – not a video or a recording of a session. This ensures the trainer can challenge and be challenged by delegates, check understanding answer questions, offer quizzes and activities to support learning. Maintaining this high level of involvement ensures effective learning.
2. Delegates are provided with demonstration and exercise files for them to use if desired, just like in the classroom.
3. No software download required to join – you can use Skype for Business or GoTo Training to join in.
4. No need to release people for a whole day. Standard sessions are just 90-minutes long and can be done at your desk or at home. Cherry pick individual sessions or learn a whole package over a number of weeks or months.
5. Need something bespoke? We can design and deliver bespoke programmes just to your requirements whether it be an exotic Excel function that few others use or based on your specialist software.

The post Five Reasons to Try Live Online Interactive Learning Solutions appeared first on Happy.