The real power of Excel is often left untapped. According to Excel Off The Grid founder Mark Proctor, many accountants hit a ceiling because they do not know how to make it work for modern, insight-driven analysis.

Proctor, who teaches Excel users to automate their work, explains how accountants can level up their Excel analytics from practical features to fundamental mindset shifts in seven simple steps.

1) Fix the foundations: start with better data

"Most people struggle to get to good analysis because they’re spending too much time just preparing the data," he says. A major stumbling block is managing inputs from various systems such as ERP, payroll and stock, and combining them into a single, meaningful dataset.

On top of that, many important business metrics are not even collected. "Understanding what drives the business and setting up systems to gather that data regularly can make a huge difference."

One of the most common structural issues is the use of cross-tab layouts, where dates are laid across the top of a table. While easy to read, these are hard to analyse. "It’s better to ‘unpivot’ the data and turn those date columns into rows, so the structure supports calculation."

2) Reframe ‘Analytics’ in Excel

When accountants think of analytics, they often picture charts and totals, but it is broader than that. "I define analytics as any value with context," Proctor explains. That might include subtotals and graphs, but it could also be a red or green box, a text label or a conditional format that highlights something important at a glance.

The real shift is moving from reporting to analysis. "Analysis is an action, not just a process. Reports alone do not create value. We need to design reporting processes that encourage insights, not just numbers."

3) Let Power Query do the heavy lifting

If there is one feature Proctor recommends above all others, it is Power Query.

"Analytics must be based on data. When data comes from multiple sources, Power Query helps users stop manually shuffling numbers and focus instead on analysing." This powerful tool, available natively in Excel, allows finance professionals to automate data cleaning and preparation.

Top Power Query time-savers include:

• Unpivot: fixes problematic cross-tab formats.
• Combine files from folder: merges multiple files into one dataset with a single query.
• Merge: joins data from different queries into a single, unified table.

When combined with dynamic source paths, users can update data simply by clicking ‘refresh all’.

Want to try it? Here is a step-by-step Power Query example.

4) Move beyond standard pivot tables

While pivot tables are a staple of Excel use, they have their limits. Therefore, users often resort to manual actions, such as copy-pasting intermediate results, to obtain the view they need.

Power Pivot is a more powerful replacement that has been available in Excel for over a decade. It allows users to:

• Build relational data models from multiple sources.
• Perform advanced calculations using the DAX language.
• Handle large data volumes more efficiently.

DAX formulas may look like Excel functions, but they introduce the concept of ‘filter context’, offering more flexibility. However, this also brings more complexity. Proctor’s advice to master Power Pivot: "practice, practice, practice".

5) Use visual tools with purpose

Visual tools such as slicers and conditional formatting are not just for presentation; they support meaningful analysis.

• Slicers allow users to filter data instantly, helping both analysts and stakeholders to drill down into what really matters.
• Conditional formatting highlights outliers or patterns and draws attention to areas that need further investigation.

However, not all visuals are helpful. Proctor warns against pie charts that feature more than three segments, as well as any 3D charts. "They distort interpretation and can mislead, even unintentionally."

6) Design with the end in mind

Too often, Excel workbooks are built around the data rather than the decisions. "Analysis should be question-led," he says. A robust workbook starts with the key business questions and builds backwards from there.

To make models understandable and maintainable:

• Separate inputs, assumptions, calculations and outputs into distinct sections.
• Document your logic so you understand your own thinking months later.

7) Do not just learn Excel - learn the business

Better Excel skills are only half the answer. The other half is understanding what the business needs.

Proctor says: "Talk to your internal customers. Understand their concerns. Analysis has to serve a purpose. It should help the business make better decisions – that only happens when you know what questions to ask."