Excel is a complicated program — but if you take a look at any of our résumés, we all claim to be highly proficient in it. The reality is that most of us aren’t quite that savvy, and so most businesses only scratch the surface of all the ways that Excel can help to streamline and improve their operations.
While it can be intimidating to get to grips with complicated formulae and macros, there are plenty of simple but effective Excel features that could change the way that you work. Here are four of the top ones that you might not have heard of but we think your business should be using.
- Batch calculation
It’s common knowledge that Excel can be used for many basic calculations with fairly simple formulae. However, when you have row upon row of values to manipulate, it’s more efficient to perform a batch calculation.
You can do so by copying a cell containing the value you intend to apply, then selecting all the data that you want to perform the mathematical operation on and selecting “Paste Special”. There are many ways to use Paste Special, but in this case, a menu will then pop up allowing you to select what kind of calculation to perform — be this addition, subtraction, multiplication or division.
If large batch calculations become a key component of your day-to-day operations, Excel-based web apps may also be useful. This is because if multiple users are frequently working from a spreadsheet for calculations, there is a risk of overriding the original formula sheet or creating endless versions of the file to save different values. In turn, this could cause data loss or confuse other users.
On the other hand, web apps take your original spreadsheet and formulae and generate an online calculator that your staff can input directly into. Crucially, inputs into a web app won’t affect the original sheet — as Excel experts from EASA explain, “the correct spreadsheet is “hardwired” within the application ensuring only it is used”, in order to “prevent version proliferation and confusion”. As a result, you can avoid these common issues and ensure efficient calculations.
- Pivot Tables
Excel’s Pivot Table feature is a convenient means to see a quick, user-friendly overview of all the data in your spreadsheet. This handy tool can condense the key trends in a dataset into a simple visual cue, allowing you to compare information and draw fast conclusions.
Making a Pivot Table is straightforward, but it can take some time to become proficient with all of its features and analytics tools. To get started, simply select the desired cells in your sheet and select “Insert” > “Pivot Table”. From here, you’ll need to manipulate the features of the table, such as the values that you’d like to make your key variables, the labels, filters and so on.
Businesses could benefit in a number of ways from using Pivot Tables. Many use the tool to visualize a breakdown of their spending, which can be useful for budget allocation. Because they can condense a number of different variables into overarching categories, Pivot Tables can be a straightforward way to compare your company’s outgoings for several different channels, for example print and digital marketing. This way, they can inform decision-making and provide a useful visual resource to use in presentations and meetings.
- Flash Fill
This is an impressive tool that demands practically no intervention from the Excel user — which is great, because it frees up your time to go and learn more about Pivot Tables.
If you’re a regular user, you’ve probably used Flash Fill already without realizing it — it’s the feature that automatically fills in your spreadsheet with values when it senses a pattern in your inputting. Simply start by inputting a desired value in a new column, and select Flash Fill to receive suggestions.
There are a number of ways that you can use Flash Fill for business purposes, which will largely assist with the administration side of your operations. One way is to separate specific text excerpts from a column of source data, for example to extract forenames or surnames from a list. Other uses include changing the case of text in the source column, converting phrase numbers into numerical values, and even automating email address construction for staff — for details, see this handy guide from ExcelDemy.
- Index/Match
Searching through a large Excel workbook can be a time-consuming affair, even if you opt to use simple in-app tools like VLOOKUP and HLOOKUP, which search for specific values vertically and horizontally, respectively. However, Index/Match allows you to perform more comprehensive searches of your entire dataset, in order to retrieve all the data that is associated with your search criteria.
To do so, start in the cell you’d like your value to appear and type “=INDEX”, then select the cells that you would like to return a value from. Next, continue to type “MATCH” and select the cell containing the value to look up, followed by the range of cells to search against. If this all sounds a little complicated, there are video tutorials out there to help you get started.
This feature can be particularly useful if you’re dealing with large and detailed datasets, such as staff onboarding information or customer contact records. Using Index/Match, if you have one piece of information that is linked to the values you are trying to find, you’ll be able to easily run a search that returns the desired information. For example, if you know a customer ID number and need to quickly retrieve contact data such as their home address and phone number, Index/Match can locate the fields that contain it.
With a little practice, we can all live up to that famous title and get to a point of “proficiency” in Excel — helping businesses to grow and improve their processes with these four handy features.