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 […]

# 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 […]