Excel is essential for many accountants, but data entry can be slow and it’s easy to overlook errors. Simon Hurst, owner of The Knowledge Base, says improving speed is as much about structure and accuracy as it is about time saving shortcuts.

“Sometimes the biggest waste of time is using Excel at all,” he says. For transactional data, a dedicated system or app might be better.

But when Excel is the right tool – for example, internal reports or client work – there are smart ways to make input faster and more reliable.

1) Structure first. Enter once, use often

The single most important thing is to understand structured data. “If you are typing the same client name and address into multiple rows, you are creating inefficiency and risk,” says Hurst. 

Instead, Hurst explains that you should structure your spreadsheet so each unique item is only entered once. “Use tools such as lookup functions and Data Validation dropdown lists to link everything else back.”

This reduces duplication and makes the spreadsheet easier to update and audit.

2) Use the power of Excel Tables

Hurst describes structured tables as “key to automating the process from data to final report”. When data is organised in an Excel table, formulas, formats and dropdowns can be applied automatically to new rows. This means fewer steps and fewer opportunities for mistakes.

Converting a range to a table is straightforward. Follow this walkthrough to go from static ranges to dynamic structures.

Tables work seamlessly with named ranges for dropdown menus. Hurst recommends this step-by-step method to create a reusable dropdown for client names:

1. Create a table with a single column headed Client
2. Enter the list of client names
3. Click one name, then press Ctrl+A to select all
4. In the Name Box, type ClientList and press the Enter key
5. In the Data Validation settings, select “List” and enter =ClientList as the source
6. The dropdown now reflects your table, and will update when new names are added

If accountants adopt just one new Excel habit, Hurst suggests learning how to use structured tables properly. “Understand structured data and apply it using Excel tables,” he says. “That alone will massively improve speed, consistency and reliability.”

3) Dropdowns, checkboxes and validation

Dropdown menus are one of the easiest ways to reduce typing errors. “Data Validation is still one of the most underused tools in Excel,” says Hurst. “It is simple to apply and makes entry more reliable. Excel now even supports autocomplete in these lists.”

Recent Excel updates also support embedded checkboxes, which can be useful for simple inputs such as Yes/No or completed/not completed.

For a more advanced guide to validation features, see this Excel Community article.

4) Keyboard tricks to enter faster

While Hurst does not rely heavily on shortcuts during data entry, he recommends the following:

• CTRL+Enter – Fills all selected cells with the active cell’s value
• CTRL+D – Fills down from the row above
• CTRL+R – Fills right from the cell on the left

These shortcuts are useful for quickly completing recurring values across multiple cells.

5) Clean up fast with Power Query

When working with exported or inconsistent data, Power Query is Hurst’s recommended solution. “It is the most important Excel development since the cell itself,” he says in his typically understated way.

Among a great deal else, Power Query allows accountants to:

• Convert invalid or inconsistent date formats;
• Remove unnecessary spaces;
• Split product codes or identifiers;
• Convert text-based numbers to real numeric values;
• Round values; and
• Eliminate duplicate rows based on full row content.

After you define the necessary steps, Power Query runs them automatically on refresh. There is no need to use formulas or reapply filters. A full example is provided in part 2 of this guide.

6) Create templates to reduce repeated effort

Recurring tasks such as monthly journals, forecasts or cash flow schedules should not require building a new file each time. “Excel Templates are a much-underused feature,” says Hurst.

By saving a workbook as a template file (with an .xltx extension), accountants can:

• Protect master formats
• Embed formulas, styles and instructions
• Prevent overwriting of base files

To use a saved template, go to File > New > Custom and select it from your available templates. Hurst recommends treating templates as you would accounting formats: invest time upfront to reduce future rework and improve consistency.

7) Design for non-expert users

Excel workbooks are often used by people with little spreadsheet knowledge. Hurst advises keeping layouts simple, using consistent formats and writing basic guidance such as “Enter date in dd/mm/yyyy format”.

Conditional Formatting is useful for highlighting issues such as missing fields or invalid numbers. Formatting cells to indicate which fields can and cannot be edited is also a helpful cue.