Microsoft Excel’s conditional formatting with formulas is a powerful feature that allows users to visually enhance their data based on specific criteria. Rather than manually scanning through rows and columns of numbers to find outliers or important trends, individuals can set up rules that automatically apply different formatting styles—like font…
Power Query is an Excel tool that allows you to import data from various sources and transform it to fit your needs. You can use it to clean, reshape, and combine data without altering the original data source. It’s particularly useful for automating the data import process and can handle…
The TRIM function in Excel is a versatile tool that helps users maintain clean data by removing extra spaces from text. Whether it’s leading, trailing, or multiple consecutive spaces within the text, TRIM takes charge of tidying up strings in cells. This function is particularly useful when cleaning data that…
Excel allows you to format numbers in several different ways. Some of the most common are: Currency format: Insert a dollar figure (or any other currency symbol) before a number Fraction: expresses a decimal as a fraction Percentage: expresses a fraction as a percentage Number: Displays the raw number (with…
Many functions return ‘#N/A’ which can corrupt an other wise clean set of calculations. For example, suppose you’re performing a VLOOKUP on a two column list of products and prices and you’re looking for a product not listed. Your VLOOKUP formula will return a ‘#N/A’ result. If this formula is…
Goal seek is an Excel functionality used to find the value of a parameter which gives a calculation a particular desired result. Let’s illustrate this with a simple example. Suppose you have a spreadsheet which calculates the profit of clothing line for a shop. And you’d like to find the…
Often you’ll want to search a list of data – a list of toy products with prices in two columns for example – for a word (a particular product, say), to return a corresponding piece of data (the price of the product in our example). So, using our simple example,…
One of the key user groups for excel is the financial community. And they are well catered for by excel, especially in a set of financial functions that simplify one of their key uses of excel: net present value calculations. For those that don’t know what that is here’s a…
Sometimes complex formulae need to be used again and again in an excel workbook. Until recently that meant copying the formula from one cell to another, and repointing the references (ie the parameters). So, to use a simple example, if you wanted to calculate the hypotenuse of a right angled…