One of the most frustrating things that can happen when importing data is having multiple data types within the same column. For example I recently had a price file sent to me that had hardcoded cost prices e.g. $4.99, mixed in with discounts off the trade e.g. 30%. The data looked something like this: Even […]

# Resolving #### in Excel

Occasionally at work colleagues will ask me what the hash symbols in their spreadsheets mean and if the data has disappeared. The data is still there it just cannot be displayed properly because it exceeds the width of the column. The solution, resize the columns and make them wide enough and tall enough for the […]

# Using Solver Add-In to work out the Sub-values of a Total

The solver add-in is a handy plugin that is used to fill in the blanks for formulas that would usually require trial and error to workout. In this example I will be using it to evaluate which values within a column make up a user defined total. Adding the Solver Click on File then Options Click […]

# Extending VLOOKUP with CHOOSE

One of the more repetitive tasks an Analyst gets stuck with on a daily basis is looking up values using more than one criterion. This can be fixed by creating a temporary “Combination” column or by using several flavours of formula. For example if we wanted to look up the Cost of Sales against a […]

# Conditional Formatting Formula Rules

Conditional Formatting with Formula Rules There are several pre-defined options for highlighting ranges, Greater than, Less Than, Between… however if we want to define more than one criteria for highlighting cells, then you may want to define a Formatting Rule. For example you may have a spreadsheet of students which you would like to highlight […]

# Excel Using COUNTIF and COUNTIFS

COUNTIF If you are counting cells and only require one set of criteria COUNTIF should be your first option. The syntax is very straight forward: =COUNTIF(range, criteria) Where your range is the group of cells you are comparing and the criteria equates to True or False. If your criteria is true for the cell within […]

# Excel Searching for values with VLOOKUP

Arguably the most helpful function Excel has to offer. Vlookup allows you to find any value you require within a table, providing your data is organised in vertical lists (the column headers are in the first row of the table). The Vlookup tool allows you to search for a lookup field in one column and […]

# Excel Conditional Filtering

Excel provides a wealth of filters that are readily available under the current filter option, however sometimes in our need to cut and modify data we require a more precise method of filtering through the rows. If in the below screenshot I wanted to grab all the accounts that equal “546” and display only their […]

# Excel Filtering Your Data

There are several ways to filter your data in excel. One of the more convenient and quicker methods is to use a filter. For example if you are only after one particular customer’s sales within a spreadsheet you could place a filter on the Customer Name or whichever column allows you to uniquely identify your […]

# Excel Formulas A Beginners Guide

One of the most useful benefits Excel provides is a straight-forward way of calculating data. In the below example I will be demonstrating how to create a margin formula which will be recalculated on each row. By default formula calculations are set to automatic, however sometimes they can be switched off. To double-check they are turned on, in […]