Advanced data cleaning with Power Query

Inconsistency is the bane of those who deal with large amounts of data, whether it’s duplicate values, conflicting column names or missing values. In fact, according to Microsoft, users spend up to 80 per cent of their time preparing their data before they can analyse it and make decisions accordingly.

That’s where Power Query comes in. In just a few clicks, this handy feature helps to automate data-cleaning and formatting even when importing from multiple sources such as web pages or nonstandard spreadsheets. Once set up, you can refresh the data anytime and Excel will reapply all the cleaning steps instantly.

Creating interactive dashboards

Excel can produce various data visualisations, meaning you may not need to use more specialised visualisation software. Tools such as slicers, pivot tables and pivot charts allow users to visually filter their data with just a few clicks, making it easy to focus on specific segments.

For example, a retailer selling multiple product varieties and categories can create clear, colour-coded graphics showing different product sales over several periods. You can also use Excel dashboards to track KPIs, monitor trends and visualise progress, then share this data with stakeholders.

The dashboards automatically update when the underlying data changes, making real-time analysis accessible to any user.

Using Excel as a project management tool

With the rising costs of business software, who isn’t looking to cull a few subscriptions? One of these could be project management, given that Excel can manage a surprising range of tasks and deadlines (especially for smaller teams or projects).

Users can create Gantt charts, timeline trackers and even resource allocation sheets in Excel, then personalise them as needed. For example, you can colour-code tasks by priority, track deliverables with progress bars, and set up automated alerts when deadlines are approaching. Excel can act as your project command centre, helping all team members see every aspect of the project at a glance.

Automating tasks with macros

If you find yourself often performing the same tasks, such as formatting spreadsheets or applying formulas to datasets, you’ll save a lot of time with macros. Essentially, macros allow you to record a sequence of steps and play them back with a single click, letting the macro repeat those tasks automatically.

For example, updating a weekly sales report normally involves copying data from a source, pasting it into a report, formatting the data and finally generating charts. A macro can handle this for you in seconds, eliminating human error and saving time.

Data visualisation beyond the basics

Most users stick to bar, line or pie charts when visualising data, but Excel also offers varieties such as histograms, scatter plots and waterfall charts. These are especially useful for statistical analysis or financial reporting.

A waterfall chart, for example, can visualise how an initial value might be affected by a range of positive and negative values over time. Let’s refer back to our retailer example from earlier. The waterfall chart can help teams to understand not just which products sold in which locations or seasons, but how total sales were affected by returns or faulty goods.

Simply put, Excel’s advanced charts allow you to see the picture more fully with your data.

Enhancing functionality with Excel add-ins

Excel’s capabilities can also be expanded with various add-ins, many of which are free or included with Office 365. For example, the Solver add-in can propose solutions to problems such as determining the best mix of investments for maximum returns. Power Pivot is another powerful tool for handling large datasets, allowing users to perform advanced calculations and build sophisticated data models.

Excel as a database

Offering more than 1 million rows and 16,000 columns, Excel is robust enough to serve as a lightweight database for smaller datasets. Structured tables in Excel allow you to create relationships between datasets, validate the data to ensure accuracy, and finally apply filters to view subsets of data. This functionality makes it easier to manage and analyse complex datasets without needing a separate database application.

For small businesses or individual users, Excel’s database capabilities are more than enough to manage customer records, inventory or financial data.