Excel Expert

Things that survive being used by other people. Anyone competent can build a working sheet. What a specialist adds is structure. Separated input, calculation and output layers. Named ranges instead of buried constants, validation that stops bad entries, error handling, and documentation of how the thing works. They also replace the manual steps. A monthly report that takes four hours of copying and pasting usually becomes a refresh button, using Power Query to pull and reshape the data. Then dashboards that answer a specific question rather than displaying everything available. The difference shows up when a model has to change, or when the person who built it is unavailable. That is when most firms discover what their spreadsheets are actually worth.

Power Query is the first answer for anything that involves pulling data in and reshaping it. It handles imports, cleanup, merging sources and transformations, it refreshes on demand, and it does not require code, so somebody else can maintain it. Macros and VBA are for automating actions inside a workbook, such as formatting, generating documents, or driving a sequence of steps. They are powerful and they are also the thing nobody can maintain once the author leaves, so they get used deliberately rather than by default. Power BI is for when the reporting has outgrown a spreadsheet: multiple sources, many users, and dashboards that refresh on their own. A specialist should be telling you which of the three a job needs. That includes saying when the answer is none of them, and the process itself is what should change.

When more than one person edits it, when it holds data other systems need, or when being wrong is expensive. Those three are the practical signals. A spreadsheet that several people open at once will eventually hold three versions of the truth. A spreadsheet that is the only record of a business process is a single point of failure with no backup and no audit trail. And a model driving pricing or payroll decisions carries a risk that its author usually cannot see. That said, replacing a working spreadsheet with a system nobody adopts is worse than leaving it alone, and it happens often. The honest answer is often to keep the spreadsheet and make it safer. A specialist who never says that is selling something, and noticing it tells you what kind of adviser you have.

By making the structure visible and the assumptions editable. The rules are boring and they work. Separate inputs, calculations and outputs onto their own sheets. Put every assumption in one clearly marked place rather than typing numbers into formulas. Use named ranges and consistent formatting so a reader can tell what is an input at a glance. Avoid clever formulas that only their author understands. Document what the model does, what it assumes, and what breaks it, on a sheet inside the file rather than in an email. Then have somebody else use it before it goes live, because the author is the worst possible tester. A model that only one person can operate is a liability dressed as an asset. It stays that way until the day that person is unavailable.

Yes, and it is one of the most common requests. The situation is familiar: a critical workbook built years ago by somebody who has left, still running, and nobody dares touch it. The work starts with an audit rather than an edit. Map the inputs and trace the calculation chain. Find the hardcoded values and broken references, test the outputs against known results, and document what the thing actually does. Only then does anything get changed, and always against a preserved copy. Sometimes the finding is that the model has been quietly wrong for a while, which is uncomfortable and better known than not. Scope the audit on its own, with a description of what the workbook is supposed to produce. Knowing what state it is in is the decision, not a step toward one.