Why do Business Analysts use spreadsheets?
Business Analyst is one of the most popular job titles. As a blanket term, it covers many roles that deal with data and business processes and often focus on specific industry knowledge, software or programming languages. What they all have in common is the frequent use of Excel.
Expert spreadsheet skills are a must when dealing with various systems and informing decisions with data insights. Excel often acts as connective tissue between different analytics packages, allowing to share data sets with less technical colleagues or visualising data at speed.
Also, Excel Experts and VBA Developers frequently assume the role of business analyst. Helping customers from many industries with their spreadsheet challenges, we always put the effort into understanding their goals and niche to make Excel tools suitable for their business.
What expert Excel skills should Business Analysts have?
We've gathered insights from business leaders on Excel skills they look for in Business Analysts. The BAs are usually expected to have advanced or expert spreadsheet skills to qualify for an open role or contract. They regard the abilities to organise data, use Excel features, effectively use spreadsheets to draw insights from data, teach others how to use new systems and stay efficient and business-oriented.
Here are fourteen Excel skills to look for in a Business Analyst:
Organising Data
1. Uses Data Validation to Avoid Messy Reports
The worst sin of business analysts seems to be using irrelevant data. Data is just information, and it's the analyst's job to choose the most important inputs. Otherwise, it results in messy reports.
Keeping that in mind, I always look for data validation skills essential for managing data. Along with checking Excel skills, I can test the candidate's approach to data analysis.
Maciek Kubiak, PhotoAiD
2. Is cautious about Excel file formats
As Excel files are usually just a link in the chain of programmes used in the data or analytics lifecycle, it pays off to be mindful about file formats. Some tools can only take CSV files and some customers may not accept emails with Excel files containing macros due to their safety regimes.
Using Excel features
3. Searches for Similarities With Fuzzy Lookup
As a business analyst, you have to deal not only with computer-generated data but also with plenty of manual data input. While, in a perfect world, VLOOKUP can handle most tasks that involve comparing tables, in reality, it doesn't.
With Fuzzy Lookup, you won't have a hard time finding matches in different tables. Its algorithm uses a similarity index to return names that are similar but not exact matches. Whenever a business analyst has to deal with large amounts of manual data, this Excel skill will save hours of repetitive work.
Maximilian zur Muehlen, CustomPlasticPart
4. Understands Why IF Formulas Are Vital
All business analysts need to have the skill set necessary to properly organize and filter data quickly. One of the best ways to do exactly that is using IF formulas. Specifically, these functions allow analysts to filter data based on certain criteria.
As an example, by using IF formulas, a business analyst can organize prospective competitors based on: (1) competitor geographic location, (2) competitor expected annual revenue, or (3) competitor social media followers.
IF formulas provide an easy way to find granular data worthy of analysis.
Lucas Nudel, Pride Palace
5. Shows Mastery Over VLOOKUP
For business analysts, data modelling and analysis are common aspects of the job. Without an agile awareness of Excel's vast functionality, it becomes more difficult to interpret data and make well-informed business decisions.
VLOOKUP is an example of a must-have Excel skill. It's a function that enables the user to lookup values in a table and retrieve specific data entries from any given column. This means that you won't have to parse through data manually, which can be a laborious and time-consuming process.
Instead, proper use of VLOOKUP will save you time and streamline your workflow. For this reason, it's a prerequisite in any business analyst's tool belt.
Gregory Yong, Convincely
6. Leverages Pivot Tables
Pivot tables are an incredible feature of Excel and allow the easy summarisation and manipulation of data. While they are easy to understand once you have used them a few times, many analysts, unfortunately, have never experimented with the tool. Simple formula knowledge is also important, particularly one that uses array functions. If the analyst has a basic understanding of array formulae, I am confident they can pick up the rest.
David Wachs, Handwrytten
Drawing insights from data
7. Discerns Patterns in the Data
Business analysis is widespread usage of MS Excel in the workplace. Being a business analyst entails analyzing data and making decisions based on it. Businesses naturally collect data in their day-to-day operations, such as product sales, website traffic, supply spending, insurance claims, and so on. Being a business analyst entails transforming data into something helpful for those in charge of the company. You could, for example, generate a profit report based on the day of the week.
If the company consistently loses money on Sundays, management could use this knowledge to make a choice (such as closing on Sundays). This is one Excel talent that I believe you should look for when employing business analysts.
Edward Mellett, Wikijob
8. Uncovers Clarity From Clutter
Most experienced business analysts have common Excel skills from data filtering to cell formatting, however, the most valuable one to possess is the ability to place data in its most understandable format.
Every business requires certain levels of Excel ability, from intermediate to advanced, yet the common thread linking all of them is their need to have meaningful information put at the forefront, clearing it from the clutter of numbers.
The ability to expertly use charts, graphs, and other highlighting techniques which emphasize critical data, saves valuable time and frustration, making presentations clearer and at the same time, allowing for the easier mapping of strategy.
Adelle Archer, Eterneva
9. Can visualise data
The role of Business Analysts (especially those focused on Business Intelligence) is to enable decision-makers to inform their choices. Visual presentation of data allows spotting patterns and relations otherwise difficult to identify. The ability to choose an appropriate chart and graphs sets or at least prepare data in Excel for visualisation is immensely useful.
Considering people
10. Can teach others how to use Excel
Sustainable change and low-maintenance systems are what is expected from Business Analysts delivering projects. That is not possible without bridging the skills gaps which often are present in Excel skills. The ability to teach Excel functionalities to those affected by change programmes is vital for the success of Business Analysis projects.
11. Can document spreadsheet systems
Business Analysts deliver change projects or provide insights to other employees. Therefore, everything they create should be useful to others and (over time) independent from them. Thorough yet concise documentation helps with that. Depending on the project, it can be simple notes inside the file, a formal PDF document or recordings of procedures.
Driving business efficiency
12. Understands industry-specific Excel functionalities
Depending on the assignment, a Business Analyst may deal with very different sets of data. Knowing spreadsheet features most suitable for purpose can help with this.
For instance, logistics or warehousing analysts should be able to assure high quality of data to avoid duplication and substandard taxonomies. Actuarial business consultants should be able to use statistical distribution functions while investment analysts financial mathematics concepts in Excel (NPV, IRR, etc.).
The use of different templates is also a valuable skill. Business Analysts delivering change and project improvement projects can leverage Gantt Charts and project trackers and even integrate them with KPI dashboards.
13. Can integrate Excel with other software
Nowadays Excel is rarely used as a standalone tool. The ability to convert extracts from external platforms and systems (e.g. HR, Accounting, Project Planning or Logistics) into formats that fit Excel templates is very useful for Business Analysts. Also, setting up data imports via “Get Data” functionality enables much broader use of spreadsheets.
14. Can automate with VBA
Skills in programming Excel macros with Visual Basic for Applications come in handy when automation is needed inside the spreadsheets. Sometimes there is no point in exporting data to a database or other external database just to process it and load it back to Excel. VBA procedures can perform all structured manual work in seconds.
Terkel creates community-driven content featuring expert insights. Sign up at terkel.io to answer questions and get published.