Tips & tricks For Microsoft Excel, Power BI, SQL and more

In this guide, we look at how Excel’s AGGREGATE function turns messy, multi-source data into reliable, actionable reports by performing sums, counts, averages, percentiles, and other calculations while automatically ignoring specified errors or hidden rows. Best practices include labelling outputs clearly, testing formulas on small samples, combining AGGREGATE with pivot tables or filters, and using Excel’s error-checking tools to troubleshoot formulas for maximum accuracy and efficiency.

The article explains how businesses can quickly calculate value-added tax in Microsoft Excel using the formula =Net*(1+VAT), where the VAT rate is set with an absolute cell reference for accuracy. It walks through setting up columns for net sales, VAT rate, and gross sales; entering the formula; copying it down multiple rows; and double-checking results. Automating VAT this way streamlines invoicing, enhances financial reporting, and minimizes manual errors.

In this guide, we explore how dirty data—duplicates, inconsistencies, misspellings, extra spaces, and more—undermines analysis and decision-making, making data cleaning a vital first step in Excel. It walks through practical tools like Remove Duplicates, the TRIM function for stray spaces, PROPER/UPPER/LOWER for fixing text case, and Find & Replace for standardising values.

This article provides a step-by-step guide on how to create a histogram in Excel to visualise numerical data distributions. It walks readers through importing data, inserting a histogram, customising the chart, adjusting bin widths, and adding data labels. The guide uses employee age data as an example and highlights how histograms can help identify patterns and outliers.

In this guide, we explain variance as a measure of how widely data points deviate from the mean and shows why understanding this spread is useful for deeper insight and risk assessment. It walks readers through calculating variance in Excel, distinguishing between the VAR.S function for a sample and VAR.P for an entire population, then demonstrates each with a car-sales case study.

This guide explains how compound annual growth rate (CAGR) offers a smoothed view of performance and shows step-by-step ways to calculate it in Excel. It then moves beyond the basic formula, demonstrating how businesses can use CAGR to compare investment returns, project revenue under multiple scenarios, and strengthen corporate forecasting amid economic uncertainty.

The article explains how Excel’s AGGREGATE function lets you calculate sums, counts, and averages while automatically ignoring hidden rows, errors, or missing data—problems that derail standard formulas. A step-by-step example shows how to total orders, count active clients, and find average orders per client even when some rows are hidden or contain errors.

Learn how to harness Excel’s compound interest formulas to build powerful financial forecasting models for your business. This guide covers practical applications like investment growth, ROI projections, and cash flow planning, with step-by-step instructions, advanced Excel tips, and real-world case studies to sharpen your forecasting strategy.

This blog explains how Excel’s LOOKUP functions—particularly XLOOKUP and VLOOKUP—can retrieve data based on multiple criteria. It walks through a step-by-step example of finding an employee’s sales in a specific region, showing both an XLOOKUP formula and a VLOOKUP alternative that uses a helper column.