Excel in a small business: 5 functions that will speed up your work (and they are not pivot tables)

Copying data between sheets and manually checking overdue invoices, correcting typos in the client database – these tasks can sometimes take many hours per week. In Excel, these tasks can be done in a few seconds. The problem is not with the program itself, but that we use it like a better notepad. Meanwhile, even the standard Microsoft Office 2024 or Office 365 has functions that immediately shorten work time – without programming knowledge or advanced training.

XLOOKUP – the successor to VLOOKUP that saves invoices and price lists

Building a price list with references to another sheet used to be troublesome when the VLOOKUP function returned an error after every column rearrangement. XLOOKUP eliminates this problem – it does not require specifying the column number and searches data in both directions, giving much greater freedom when arranging the sheet. It is an Excel function used to find a value in a specified range and return the corresponding result from another range.

Imagine a sheet with 300 products: an employee enters the product code, and Excel automatically fills in the name, price, and VAT rate. The invoice issuing time drops from several minutes to several dozen seconds.

Remember that XLOOKUP is only available from Excel 2021 and within Office 365 – it is one of the main reasons to consider updating the suite.

Conditional formatting: how to visually spot overdue payments in a second? 

Conditional formatting assigns a color or icon to a cell depending on its value. In practice: overdue invoices are marked with a red background, payments due within 7 days – orange, settled – green – automatically, without any manual review.

Flash Fill – organizing the client database without formulas

Do you have a database where the first and last name are entered in one column, but the CRM requires them separated? Flash Fill (Ctrl+E) solves this without any formula. You manually enter the first result, press the shortcut – Excel analyzes the pattern and fills in the rest automatically. It also works for extracting domains from emails, formatting phone numbers, or standardizing spelling. It has been available since Excel 2013.

Drop-down lists – how to avoid errors when employees enter data?

If different employees enter the same value in different ways – “Warsaw”, “warszawa”, “WARSZAWA” – filters and reports stop working correctly. Drop-down lists solve this problem: instead of typing anything manually, the user simply selects a ready entry from the list. You set them up in the Data tab → Data Validation → List. If you link the list to an Excel table, it will expand automatically with new entries. Works in every version of the program.

Sparklines – sales trend analysis in one cell

Sparklines are miniature charts embedded directly in the cell. Do you have a table with sales of 15 products for 12 months? You add a “Trend” column, insert sparklines (Insert tab → Sparklines) and within a minute you see which products are rising, falling, or staying steady – without creating dozens of separate charts. Available since Excel 2010.

Which versions of Office support these features?

Four out of five described tools work without any update. However, if you want XLOOKUP, you need Office 2024 (one-time license) or Office 365 (subscription with access to current updates). The choice between them depends on whether you value fixed costs or flexibility – both versions are available at key-soft.pl.

How to avoid human errors (typos) when entering sales data?

The most effective method is to minimize manual data entry. Where values repeat – product names, categories, order statuses, contractor names – it is worth replacing a free text field with a drop-down list. The user selects a ready option instead of typing it manually, so a typo is physically impossible.

Where manual entry is unavoidable, conditional formatting is helpful – you can configure it so that a cell changes color for a value outside the norm, e.g., an amount exceeding a set threshold. This doesn’t eliminate the error but makes it immediately noticeable.

When organizing existing inconsistent data, Flash Fill works fastest – it standardizes the entire column’s format based on one sample entry, without any formulas.

Are these features difficult to learn for a non-technical person?

None of the described features require programming skills. Flash Fill works with a single shortcut. Drop-down lists are set up in a few clicks. Sparklines are inserted in half a minute. Conditional formatting requires entering a simple formula but is based on ready-made templates – just change the cell address.

Learning XLOOKUP takes the most time. However, it comes down to remembering four elements of the function. A person who has never used formulas should master the basics within an hour.

The effects of each of these tools are visible immediately. It is therefore easy to learn on the fly – checking results and correcting errors without the risk of damaging data.