VBA, visual basic for applications, is a common coding language that can be used by Excel developers to automate Excel to perform a wide variety of tasks.
The use of VBA in Excel allows for a much more customizable problem-solving approach to Excel problems rather than a one size fits all approach. Because the language is relatively old and very well known, a plethora of resources exists to help novices learn the basics of the language.
Types of Tasks VBA Can Help With
The automation of a mundane and repetitive task would be the most common use for VBA. For example, say you had two lists of numbers in the first two columns of a large spreadsheet.
For each set of adjacent numbers, you wanted to add them together and display the sum only if the sum was greater than 10. In cases where the sum exceeded 15, you would want the cell to be bolded and red.
Rather than going through the trouble of writing an Excel formula, populating cells, and then using conditional formatting, you could write a VBA code that could accomplish all of those tasks at once.
In addition, the code would be transferable to other files that you had that needed the same formatting done. VBA also offers the flexibility to create tables, insert new sheets based on conditions, and anything else the coder can think of.
VBA code is written in a separate window from the actual Excel file and is run when the coder presses a run button. The code can then be saved and used with other files.
Learning the Language of VBA
VBA problem solving is not possible if the user is not able to code it themselves or find someone who can. Fortunately, there are many free VBA courses online.
A good starting point would be a simple Google search on a beginners’ guide to VBA. Several YouTube series exist on the topic as well for those that prefer to watch and follow along as they go.
The key though to learning any coding language is practice. In order to be a successful VBA coder, practice is needed to develop the skills that the platform requires.
Many practice problems can be found with these courses or by hiring an Excel programmer or a VBA consultant to help with more complex Excel coding and automation tasks.
Almost all office jobs now require basic levels of computer literacy because so much of our working lives are spent working on computers and in programmes like Excel. If you're job-hunting or working on your CV, adding Microsoft Excel skills to your CV can really make your application stand out.
Displaying excel skills on your resume immediately signals that you have knowledge and experience of Microsoft Excel - and skills in the wider Office suite can be invaluable. Where many candidates trip up, however, is how to list Excel and computer skills on their CV.
With so much information to include like qualifications, employment history, practical skills, a personal statement and relevant Excel training courses, knowing how to make your CV stand out by clearly showing and communicating your skills and experience can be a challenge.
Today we'll cover how to write about Microsoft Office Skills on your resume and some tips for making sure your CV secures that all-important interview.
Avoid These Common Miskates When Listing Excel Skills On CV
When adding computer skills to a CV many candidates will simply list ‘Microsoft Excel’ with no further details. As Microsoft Office programmes have a huge range of functions and features, this is both vague and unhelpful for an employer. It's a sure-fire way to get your CV added to the 'no' pile.
What you must do is display your own skill level, to distinguish yourself from other applicants. Some of the ways you can do this include:
Use the correct MS Excel terms e.g. VBA, VLOOKUP, Workbook
Use examples to prove your experience
List your key strengths in the programme
Reference qualifications, courses and CPD you've undertaken
Update your online resume aka LinkedIn
Now we've got a basic idea of how to build a CV and include MS Excel skills let's go into some more detail about each of these points.
Demonstrate Knowledge Of Excel On Your CV
If you’re competent in Microsoft Excel, don’t just write the programme name on your CV. Prove your skill level by mentioning the features you’re familiar with.
A knowledge of Macros, Pivot Tables, Data Manipulation and VLOOKUPs is highly desirable by many employers, and displaying your awareness and familiarity with these functions is invaluable.
This can be applied to other Microsoft Office programmes too. If you have experience in PowerPoint, you can mention your knowledge of Slide Masters, SmartArt, Animations, and importing data into a presentation.
By including these details, you’re indicating from the outset that your knowledge is genuine, and letting an employer know exactly what you can do.
Provide Examples of Your Excel Skills
When applying for a job, listing your Excel skills on your CV is a great start. To really impress an employer, however, it’s best to provide examples of how you’ve used these skills in the past. This will not only back up your claims it will also give an indication that you know how to apply these skills effectively.
If you’re just starting out on the career ladder, don’t be afraid to list university experience. For example, if you created financial models as part of a research project, then be sure to mention them.
If you have experiences in the workplace, such as creating a sales report or analytics database, don’t leave this out. You can state this alongside your skills, as part of your employment history, or within your personal statement – just ensure that your valuable experiences aren’t hidden from view.
Be Truthful About Your Excel Knowledge
Whatever you write on your CV, the most important rule of all is not to lie.
Regularly, candidates list a desired skill on their CV under the assumption that if it’s required, they can learn it on-the-job, or at the last minute. However, if a job is advertised as requiring a certain skill - for example, an understanding of pivot tables - it’s not uncommon for a short test to be built into the interview process. If you list skills you don’t have, you’ll be wasting both the interviewer’s time and your own.
Talk About Excel Training, Courses & Qualifications
If you have been learning to use Excel or have specific MS Excel qualifications from Excel training courses these will really make your CV just off the table! Accredited training courses show that you have a proven skill level within Excel, and most Excel training courses have a beginner, intermediate and advanced level.
Even if you haven't been on accredited courses showing that you've done learning off your own back, using free courses, watching webinars and attending events is another great signal to an employer that you're serious about building a career with Excel.
Update Your LinkedIn Profile To Showcase Your Skills
LinkedIn is a fantastic network for job hunters because there are millions of recruiters that use the network every day to find candidates for positions they're trying to fill. Having an up to date and compelling LinkedIn profile will help your chances of being discovered.
Using LinkedIn to follow hashtags, industry figures and companies you want to work for it a great way to build you knowledge in your field, network with new people and come across an opportunity you may not otherwise have discovered.
Boost Your Chances By Listing Excel Skills On Your CV
When job hunting, it can be daunting to constantly see lists of desired skills that you may not yet possess. There’s no need to worry, however, as help is at hand.
Here at The Excel Experts, we don’t just provide business excel consultancy, we also offer training across the entire Microsoft Office suite.
We can deliver introductory, basic or advanced levels of Microsoft Excel training, via online courses, remote assistance, one-to-one training, or group sessions.
We can help you work towards an Office qualification, alongside giving valuable experience, which will be invaluable to your CV. For more information, don’t hesitate to contact our friendly team, today.
Calculating the Cost of Delay for a project is an important management tool, particularly when multiple projects use the same finite resources. The projects cannot all start simultaneously if they all need the same resources, so which one is chosen to go first?
Using an Excel spreadsheet to calculate the Cost of Delay will show the impact on the organisation’s profit if a project is delayed, which helps managers evaluate the distribution of resources to mitigate the financial impact.
Most projects also overrun and have an element of slippage. Often, due to outside circumstances, or something unforeseen, milestones and completion dates have to be re-set, which can significantly impact when the project is completed.
In this post, we cover the basics of the cost of delay and why is it so important to quantify the Cost of Delay and link to s spreadsheet to help your business calculate and manage the cost of delay for your projects.
Cost of Delay Example
Let’s use a cost of delay example to demonstrate how helpful this calculation is.
In the example, the product development process would occur in the first three months, with some marketing taking place in months 2 and 3 to increase public awareness. In month 4, the product is ready for the market, and there is now growing sales revenue and costs budgeted for subsequent months.
New product development – months 1-3
Marketing starts – months 2-3
New product launch – month 4
Calculations can be made to set percentages for cost types, which can easily be adjusted. The projection could also include Discounted Cash Flow calculations for the net return (a standard Excel formula).
Everything looks good with a significant cumulative net profit at the end of month 12. The project gets approval, and off it all goes.
However, the best-laid plans do not always work out as expected. Suppose the following occurs:
In month 3, a component supplier goes into administration and causes a supply chain problem
An important tant resource on this project is required on another urgent project that is of greater importance
These are events that are unforeseen and out of the control of the project manager. The effect is to push the product launch back by a month and to have a further month of development costs and some additional marketing costs at month 4.
Sales revenue is likely to be lower in the first month because of the delay (public perception of the product may have been damaged). The revenue will always be playing ‘catch up’ against the original budget over the subsequent months.
Also, a competitor may have been working on a similar product, and they may have managed to launch their product in month three which will damage the sales.
The cost of this one-month delay is very serious and harms the overall profit for the 12 months.
In month 4, there was a massive change in the projected profit from 61K to a loss of 11K. Working along to month 12, the amended cumulative profit is running well below the budgeted profit, which is the cost of the delay that happened in month 4.
The overall result at month 12 is that profit for the new product is 116K below what was expected.
Using Excel For Cost Of Delay Calculations
On seeing the effects of the one-month delay in month 4, management will want to get things back on track so that the original profit figure is achieved in month 12. Excel is an ideal tool to keep track of the profit being made over the timescale.
Excel allows managers to juggle cost and revenue factors and do ‘What If’ scenarios (which is a built-in Excel function) to get the best scenario and profit for the project, considering various assumptions and knowledge.
They could do this by increasing sales volume, but this will need increased marketing costs. Because of the flexibility of Excel, it is very easy to play around with the numbers for subsequent months to mitigate the adverse effects of the month four delay.
Managers can produce different scenarios in Excel, save each scenario off as a different file to be easily referred to again, and use ‘What If’ functions to ensure that the goal is achieved at month 12.
Excel has excellent chart facilities, and data such as this is easier to view as a chart than just as a string of numbers:
Conclusion & Cost Of Delay Template Download
Excel is a perfect tool for evaluating the Cost of Delay on a project. This can be used to decide between two or even multiple projects and the order in which they are executed.
If there is an unexpected delay on a project, it can also allow managers to generate different scenarios to mitigate the financial impact of the delay.
Excel has enormous flexibility in terms of displaying and calculating data and presenting it in chart form. It is an easy task for anyone to create a spreadsheet to calculate the Cost of Delay for projects.
Last week we learnt that the Government’s Track & Trace System failed because the data exceeded maximum number of rows in an Excel file. Then #excelgate kicked off with a lot of knowledgeable people (and not so knowledgeable) expressing their opinions.
For the most of it these opinions expressed their pre-existing stance over the virtues or failings of Excel. if you were pro Excel you highlighted the mistakes in the use of Excel, if you were anti Excel you highlighted the limitations of Excel.
What we know is that the government was using and outdated version of the Excel Workbook file format. The xls file format with a limit of 65,536 rows whereas the current format introduced back in 2007 has a limit of 1,048,576 rows.
When the row limit was exceeded the file missed some 16,000 tests and the rest as they say is history. As of writing this there have been over 24,000,000 million tests this year, with over 600,000 people testing positive. Daily this has now reached over 16,000 positive tests.
What we can conclude from this is that the user was not an Excel Expert, and that the government might still be using seriously outdated software. What we can also probably conclude is that they were adding the positive test results to an Excel sheet working on a weekly basis, so when the results went over 10,000 cases a day the end was nigh.
While there is little more to be said other than these facts and conclusions, the opinions expressed #excelgate has raised some issues that need to be addressed.
What Is, And What Isn’t Excel?
First, let me outline what Excel is and is not. Excel is part of MS Office and therefore is designed for office and home use. We refer to Excel as ‘Lego for Data’, meaning that it can get a working software model up and running very quickly. In doing so it offers unrivaled value.
At The Excel Experts, we know that Excel is not a database but it can connect to most databases. In fact, with the ‘penny dropping’ about Excel’s ability to deliver better value, building front and back ends to connect to these databases has become a growth area for our services.
Sometimes our clients do not realise is that they have an existing database platform that they are already paying, for that comes with their Web Hosting.
With Excel as with any other software, it is horses for courses. If you are in a tearing hurry to get a solution or require the best possible value, as with most SMEs, then Excel is very much part of it. Unfortunately, of Excels’ many functions, none of them handle incompetence. The incompetence that was behind this story.
Chaos has been caused by the Government using Excel as a repository for Covid-19 test results in England.
Records have been lost and not reported, and there has been huge reputational damage to the Government and a loss of confidence in the official figures.
The methodology used has become the butt of many jokes on social media.
The BBC puts the blame on Public Health England for using an old file format (.XLS) which only allows 65,536 rows as opposed to the new post-2007 file format (.XLSX) which allows 1,048,576 rows, which is a huge difference.
The source data was provided by a number of commercial firms and the transfer medium was CSV (comma-separated value) files. Bearing in mind the purpose of the data, these files could be very large, sometimes larger than 65,536 rows.
Excel is very useful for consolidating data and presenting it in an easy-to-read dashboard format, using charts and summaries, but this is only as good as the integrity of the imported data.
One of the problems with using the old XLS format is that even if you load the file into a post-2007 version of Excel, it still remains at 65,536 rows unless you do ‘Save As’ and save it in the new XLSX format. This looks like it was not done.
I suspect that the developers ‘cannibalised’ an old XLS dashboard without considering the lack of row capacity. This probably had VBA code to automate loading the CSV files into Excel.
If VBA is used to load a CSV file into an XLS file, there is no warning that the number of rows has been exceeded and the data truncated. I have tested this in Excel 2003 using a very large CSV file (1m records) and using VBA code to open the file, no warning message is shown that the data is truncated.
It is easy to see why the error has occurred.
It is basically down to users/developers not being aware of the row constraint on XLS files. The whole import procedure was probably working quite well until one day a CSV file came along that was over the limit of 65,536 rows, and data was lost.
How this could have been prevented
In the import code within VBA, once the CSV file was imported, a copy should have been saved off as a CSV file under a different name. VBA could then be used to check the copy file size against the original. If the sizes did not agree, then this would be flagged up as a danger.
Using a reference in VBA to Microsoft Active Data Objects (ADO), VBA could link directly to each of the CSV files and use each file as a database table. An ADO recordset could be created using SQL (Structured Query Language) for each CSV file, a number of rows would be checked against the recordset ‘RecordCount’ property and the worksheet ‘Rows Count’ property, and then using the ‘CopyFromRecordset’ method the data would be copied into the worksheet.
Using ADO, it is also possible to connect back to the current workbook and use each worksheet as a separate table, providing that the information is tabular with column headings at row 1. This enables full SQL commands to be used to manipulate the data with enormous flexibility and effectively turns Excel into a full database application.
Working on the assumption that the Excel workbook was ‘cannibalised’ from an earlier application, it would have been better if the Excel application had been developed from scratch using XLSX files. However, I suspect that there was a time constraint here and the whole thing was rushed through without time to create an original application.
Conclusion
Excel can be used as a database, having the advantage of being able to service a dashboard within the same application.
The problem here is poor design and implementation which did not cover every possible import file size. Testing was also lacking – large CSV files should have been used for testing and checks made to see that all data was appearing.
It is possible that the developers of the application did not understand the full capabilities of Excel, particularly using ADO, and sadly, this has reflected badly on the use of Excel workbooks for solving problems like this.
This article was commissioned by one of our senior consultants at The Excel Experts. For further inquiries please email ben@theexcelexperts.co.uk with the subject line #CallTheExperts .
How are you getting on with zoom and other online channels for collaborative working? Do you still have ‘in the flesh’ meetups with your work based colleagues and clients? Do you want to??
At the beginning of lockdown, as a company, we were, like many millions of companies and individuals thrown, unceremoniously, into a sea of doubt and insecurity. That sometimes feels like enveloping us. SURE we all have good and bad days, so WHEN do you start seriously thinking about how you are going to emerge from the other side of these problems in a healthier position than when you went in?
Should we bunker down and dream of sunnier times ahead, and there surely will be many sunny days ahead let’s make no mistake. WHEN we get over this crisis as individuals, as companies, countries, and one planet and its peoples, how will we be? Relieved! Yes!
We decided to hire a design company to rebrand our business. To think ahead to what people want most, and how we as an Excel agency can play our part in the new Post COVID Economy. Believing that we may need a new business model moving forward, and a much clearer vision of who we are and what we represent.
In the end, we are what we are as a business because we like to work with people and meet people, talk to people, and help solve their problems for them. I can vouch that this is true, for every expert in our team and I’m proud of that. At the moment our website just doesn’t look … well.. very friendly… We really hope that the new one will be and that our website visitors and hence our new customers, will come to value that quality all the more.
If you have a great service and want to put that message ‘out there’ then I’d argue that rebranding your business is a really worthwhile thing to consider. It’s not just about making up a good sales pitch, although that is clearly important. It’s about refocusing on the qualities that matter to you about your business. WHY did you start this business in the first place? What gets you up in the morning? Then taking those insights and working them into a new vision, a new brand. Hope you enjoy the trip.
The apparent strengths of Excel can also be its weaknesses.
The problem with the Covid data fiasco was NOT a problem with Excel. From what I can make out, someone didn’t realise that the old Excel format had a large, but possibly limiting, size on the data it can process. (BBC News)
The covid data in question exceeded that limit. IF the government had used the newer xlsx format that every reasonably recent version of Excel can handle perfectly, this problem would not have happened.
I’ve been reading suggestions all morning that the problem was in using a spreadsheet instead of a database. Using Excel I’d argue, was a perfectly good idea, Excel can perform the main functions of a databased perfectly well, it just wasn’t set up quite right…
You could store the data in a proprietary database if you wish. Even use the mysql database that you have on your website but it’s not essential! The problem with rewriting for a database to me is.. it took this long to make it work in Excel! Stay with the system but use it properly please.
THESE are some of the problems with Excel:
If a spreadsheet is created by the ‘end user,’ they will generally be bypassing IT project discipline and oversight as well as development processes and methodologies. If this occurs,you then you may get the problems that you would expect. This is not a problem WITH Excel, only with the way it CAN be used. A robust testing system is required and this is possible to build into Excel.
Data can be accidentally or erroneously entered into a spreadsheet with possibly disastrous consequences. It takes a little work to lock Excel down so that accidental deletion and changing of data cannot occur. Again, this is possible though.
Sometime you don’t want everyone to have access to everything in a spreadsheet. Again, It’s a simple matter to add a ‘login’ system on Excel so that users only see the areas that they need. Once in, they can only change the cells that they need to change.
It is possible to covert old Excel file formats into newer ones very easily. In doing so you also improve the system so it safely and efficiently complete all of the functions needed.
Possible ways to move forward.
This is why I have approached the Government and offered the services of our experienced Excel Experts, for free. To help solve these Excel issues without the need of wasting more money on a bespoke database. If we think back to the NHS database issues, it might be wise for the Government to take us up on this offer, and let us solve their Excel problems.
The Excel Experts is a large team of people with collectively hundreds of years experience in working from home (WFH) solving problems in Excel and automating complicated procedures in Excel for clients. They love working with Excel and getting the best out of it for a client.
The Excel Experts can make a significant contribution to the new post Covid19 business world, and can help your business succeed in these challenging conditions.
The Covid19 pandemic has completely changed the way in which businesses work. Many businesses are now allowing their employees to work from home for the foreseeable future in order to observe social distancing. Most businesses are now finding that they cannot social distance their employees with everyone present in the office.
The Excel Experts has a huge amount of experience in dealing with every Excel problem known and also doing this operating remotely (WFH). The team of experts is very used to sorting out client requirements, making suggestions for improvements, and building a good working relationship without ever visiting the client’s premises.
Almost everyone uses Excel day-to-day for various tasks, but very few people realise the enormous potential that it has in saving time and making your life easier, and this is where The Excel Experts come in. Excel has its own programming language, and this can be used to develop automated systems cutting a task of several hours down to a few minutes, saving costs and freeing employees to deal with other tasks.
With the Excel Experts, you do not need to recruit a permanent ‘Excel Expert’ with all the associated overheads involved e.g. Employers National Insurance (13.8%) and pension contributions. The Excel Experts provides people instantly who are well versed in home working on Excel, and who will provide value for money from day one.
Whether you want an Excel expert for a day or for a longer period, The Excel Experts can easily adapt to your requirements and offer flexibility. Think of it as Uber for Excel!
The Excel Experts offers significant discounts on day rates, and further discounts for extended periods. Using an Excel Expert would be far more economic than recruiting a permanent staff member.
If an Excel Expert goes sick, or is unavailable due to other reasons, another Excel Expert can seamlessly take over the project. The Excel Experts work as a team and support one another.
One of the biggest fears of a business over allowing working from home has always been ‘how confidential is my data?’
The Excel Experts is compliant with the General Data Protection Regulation (GDPR) and other data protection regulations. The Excel Experts assures confidentiality of data and Excel spreadsheet material
The Excel Experts has years of experience in dealing with private data and material, and Non-Disclosure Agreements (NDA) with clients are perfectly acceptable.
Several parents have complained to me about using Google classroom especially the sequence required to photograph our childrens work and upload it to Classroom.. it can be a several step process.
Here is an easier way, use the phone app – it makes it all much easier 🙂 I’ve made a quick tutorial on how to install and use the app for this purpose. Hope it helps
Install Google classroom app on your phone
So first its a trip to Google Play Store and install the Google Classroom App
on to your phone.
Accessing the work that has been set by the teacher
Open the App and choose the class you want
choosing the class on the app
At the bottom of the screen you can see 3 icons in Google Classroom
Click on Classwork Icon at the bottom
Submitting work to Google Classroom
Grey means the work has been handed in and blue, still to be submitted
Lets see whats being set for Foundation on Monday click the icon
And here is the list of all the attachments and video for the days class.
Click on Add attachment button, this is where you will submit a piece of paper that you’ve been working on
click take photo Take the photo click OK
its done
At the bottom you can see the upload you just created. Your teacher can see what great kids you’ve been and you can add more documents.
IF you want to add a comment or two about the submission you can write it here. Then press the ‘Hand In’ button.
Using Microsoft Flow for process automation – part 2.
Following on from our first blog about Microsoft Power Automation formerly know as Flow, we’re going to move on down the flow program looking at each action as we go…
If there was a buzz phrase for the Post-Covid New Dawn it must be ‘Remote Collaboration’ so we must start with a discussion about this…
As a team, we are a rather disparate group of individuals here at The Excel Experts . We work together on projects sometimes, but essentially we’re all experts at working from home, and using the various forms of Remote Collaboration available such as Skype, Zoom etc.. Within the group, we use mostly emails and, as everyone knows a mailbox can fill up pretty quickly when business is doing well.
So we felt very much that the time was approaching, when we needed a better platform to work from than an outlook address book of consultants and a folder of email projects.
It also became obvious that as an agency that revolves around Microsoft technology, mainly Excel and Outlook… Microsoft Teams was going to be the best platform to reduce our dependence on emails. I was able to add all our consultants as ‘guests’ and this seems to give them the required access that we needed to move to the next stage. Our team now have a really good environment to help collaboration and sharing of ideas, online, remote working from home. Well done Microsoft.
Sharing Data with your Team
We wanted to share the leads with our Team and this is easily done in MS Flow (now known as Power Automate)
Using the ‘Post a message’ action its straightforward to lift the data inserted into the form, and post it into a teams channel using a little bit of html
Same for the ‘Html to text’ action click on the </> symbol to insert the html…
Using Microsoft Planner
The above HTML looks like this
So we now have a message in the team channel about the lead, and we have a new task in the team Planner.
In the next part we will be looking at ways of using planner and flow to help link up other parts of the business. Every time we automate a repetitive process, we save ourselves time, time to hopefully do more enjoyable or certainly more productive things.
The Excel Experts are here to help with your Excel tasks big or small, including spreadsheet help, consulting, reporting, VBA, automation, and Power Bi. Let us help you take your Excel skills to the next level with our experience in delivering solutions.
