Clear Excel files
Eighteen topics that matter when developing sound, user-friendly Excel files.
Worksheets, rows and columns
Excel has three dimensions: rows, columns and sheets. Using them efficiently matters for ease of use and maintenance. Don't put everything on one worksheet - use multiple worksheets where the logic calls for it; helper sheets can be hidden later so the user isn't burdened by them. The same applies to rows and columns: spread complex formulas across multiple columns and hide the helper columns. Avoid blank rows within tables.
Named ranges
Formulas use cell references by default, for example =A1*A2. A clearer alternative is
=Quantity*Price. Names instead of cell references make formulas more
readable and easier to maintain, don't require $ signs when copying, and make linked
VBA code more reliable.
Formulas and functions
Excel is a powerful calculation tool, with advanced functions for almost every field — in practice you use only a fraction of them. Commonly used worksheet functions include SUM, COUNT, IF, VLOOKUP, MATCH, SUMIF and COUNTIF. The right function for the right situation prevents unnecessarily long and complicated formulas.
Dates and times
A date in Excel is a number, formatted as a date: 23 June 2014, for example, is the number 41,813 - the number of days since 1 January 1900. That makes it simple to calculate the difference between two dates - a 60-day payment term after an invoice date, for example. The part after the decimal point is a time: 41,813.25 means 23 June 2014 at 6:00am (a quarter of a day). Overtime calculations and complete rosters can be built the same way.
Conditional formatting
Conditional formatting automatically formats a cell depending on its content: a background colour, font colour or icon, for example.
Data validation
To minimise the chance of errors and make an input field user-friendly, data validation sets limits on an entry - a value between 1 and 10, a date within a range, or a choice from a predefined list, for example.
Whether typed input is actually rejected depends on the error alert you choose. Stop refuses the value and does not let the user continue. Warning asks for confirmation and then allows the value anyway. Information reports it and lets the value stand. Only the first is a limit; the other two are a hint.
Pasting can also step around it, and can overwrite the target cell's validation rule - leaving both a value that is wrong and no rule any more, with no warning. Data validation is therefore a way to keep typing mistakes out, not a lock on the data. Where a value genuinely has to be right, that check belongs in the application or the database as well, where pasting cannot reach it.
Number formatting
Number formatting shows up in amounts (€), dates and percentages, but also in units such as "3.5 m³" or "12 units". That avoids an extra column and keeps the cell usable in calculations.
Page layout
With the right page settings, printed data comes out neatly, without unnecessary splits across multiple pages. Headers and footers are well suited to page numbering, logos and file names.
Sorting and filtering
A neatly set-up, contiguous list - without fully blank rows or columns - can be sorted or filtered in one click, without data getting mixed up.
Pivot tables
A pivot table is a dynamic summary of a table and takes seconds to create - provided the underlying list is in order and it's clear what summary is needed. Don't try to fit everything into one pivot table; several pivot tables are often clearer.
Charts
A good, clear chart starts with a tidy, structured table. A column, line or pie chart is usually the best choice; too much data in one chart hurts readability. Better to spread the data across several charts instead.
Links between files
A link is created when a formula pulls data from another Excel file. That can produce incorrect values as soon as that link is broken, for example because the source file is moved or emailed. An alternative is integrating the source data into a worksheet of the target file, or an import macro in VBA.
Protecting a worksheet
To stop users from overwriting a formula by accident, or a workbook from showing incorrect results, it's advisable to protect worksheets so that only input cells remain editable. A distinctive colour (light yellow, for example) makes the difference between protected and unprotected cells clear.
The word "protection" promises more here than it delivers: sheet protection guards against mistakes, not against unauthorised people. Anyone allowed to open the file can lift it. Where data genuinely has to stay out of reach of some of the users, that is a question about rights on the data itself - not about a checkbox in Excel.
Protecting a workbook
Worksheets not directly relevant to users are best hidden - that keeps a file clear and tidy. Workbook protection stops users hiding, unhiding, deleting or adding sheets themselves.
Sharing a workbook
Working in the same workbook with several people at once is possible through OneDrive or SharePoint Online, but at business-critical scale it soon runs into limits: locked files, clashing changes and failed merges. We build Excel solutions that prevent this, while keeping the familiar Excel Desktop App - read more in our knowledge article on Excel with multiple users.
Visual Basic for Applications (VBA)
VBA is the programming language behind Excel, used to automate complex and repetitive tasks. It's a professional, extensive programming language: applied in a structured and well-considered way, it prevents applications from turning into something no one understands any more.
Interface (menu, ribbon, buttons)
For a user-friendly Excel application, the ribbon - the dynamic menu structure at the top of Excel - is the most important tool: every available command is always in view. In modern versions of Excel, the ribbon can't be extended with custom commands via VBA, but can via XML or certain add-ins. Worksheet buttons and keyboard shortcuts are an alternative way to run macros.
Working with other software
VBA allows Excel to integrate with other software such as Word, Outlook or other mail programs: automatically processing calculation results or charts into a Word report, or automatically sending data and files to email addresses managed in Excel.
Does your Excel file meet these characteristics?
Briefly describe the problem with your file, and we'll suggest a practical way forward — with no obligation. If we want to take a look at the file, we'll agree on a secure way to share it afterwards.