Lessons Archive

Split Columns with Formula to create a cleanup template

Length: 10 minutesComplexity: Easy

If you want to create a template that you can reuse every month, then it is better to spend the extra time creating formula that will do the work for you. The first functions to learn are the LEFT, RIGHT and MID functions which allow you to extract a set number of characters from the LEFT of the cell, the RIGHT of the cell or the MIDdle of the cell. For the more complex situations you may need to use the formulas shown lower down.

Split Columns with Flash Fill

Length: 10 minutesComplexity: Easy

Flash Fill, which is only available in the Windows version of Excel 2013 and above, helps significantly in a number of data cleaning instances. We can use it to quickly and easily split columns even if they are not consistent.

Split Columns with Text to Columns

Length: 10 minutesComplexity: Easy

The Text to Columns tool is well known and used regularly to split columns in Excel. Here we can see how to use it, as well as some tips and tricks associated with it e.g. use a delimiter (e.g. the implied columns are split with a comma, or dash), use a fixed width (e.g. the first 5 characters are columns A, the next 10 are columns B etc), importing credit card, bank account and other numbers that are either very long or start with a zero exactly as they are without dropping the zero or rounding the numbers at the end.

Remove duplicates Exercises

Length: 10 minutesComplexity: Easy

Remove duplicates exercises to test what you have learnt during this part of the course

Automatically remove duplicates in Excel with a formula

Length: 10 minutesComplexity: Easy

Occasionally you may need to create a template that automatically 'removes' duplicates from a list. Normally it is not possible to delete rows in Excel without using a macro, but you can set up formula to create a separate list that only brings through the unique items, thereby automatically 'removing' the duplicates from this new list.

Removing Duplicates in Excel- Advanced Filter

Length: 10 minutesComplexity: Easy

A useful tool to remove duplicates is the Advanced Filter which is based on the Filter but allows for filtering in place or else creating a copy of the data set without the duplicates. Whereas the normal Data Filter struggles to show you the duplicates without you manually going through every one, the Advanced Filter will identify and extract them for you.

Formula to identify duplicates in multiple columns

Length: 10 minutesComplexity: Easy

One of the benefits of using the Remove Duplicates tool is that it can be used to look across multiple columns and find the duplicates where multiple columns are the same. You can achieve the same thing with a formula. By combining the COUNTIF with a CONCATENATE formula you can easily compare across multiple columns

Formula to identify duplicates in Excel

Length: 10 minutesComplexity: Easy

Although the tools are useful, it is sometimes necessary to use a formula to identify which items are duplicates without necessarily deleting them. This is easily achieved with the COUNTIF formula. Furthermore, you can use the COUNTIF formula to determine which instance of the duplicate you are looking at (1st instance, 2nd instance etc)

Delete Unnecessary Rows in Excel with Formula help

Length: 10 minutesComplexity: Easy

If you don't want to select from the Filter each item, you can combine the Filter and some formula to make it easier. So by creating a formula (or many formulas) eventually you can have a column which tells you whether that row should be kept or deleted.

Delete Unnecessary Rows in Excel with a formula

Length: 10 minutesComplexity: Hard

Although it is not possible to delete rows of data with a formula (unless you want to use VBA which we are trying to avoid), there is a way to get Excel to create a separate listing of the 'cleaned' or 'scrubbed' data. This is useful if you regularly get 'dirty' data and find that you repeat the same processes again and again each month. By using clever formula to identify the lines that you want and the lines that you don't want, you can create a template where each month you only need to copy and paste the data in and Excel does all the other work.

Filling in data gaps in Excel- Tool

Length: 15 minutesComplexity: Easy

If you have ever experienced the situation where you receive data that has gaps in it, and you need to fill in the data gaps so that you can continue to work with the data you will love these tips. A common way to do this is to MANUALLY work through the entire spreadsheet and copy each little section down. But this is very manual. A better way is to ....

Delete Unnecessary Rows in Excel

Length: 10 minutesComplexity: Easy

If you receive a dataset, you may need to delete some unnecessary rows (blanks, headers, footers etc). You can manually go through each line and delete the rows. Another way that some people use is to sort the data and then delete the rows. Although this way works we find it a bit dangerous as the order of the data may be important....

Excel tests for numbers seen as text

Length: 10 minutesComplexity: Easy

If it is a proper number it should align to the right, be possible to format it into different number types and currencies, add it up by highlighting the cells, not have an apostrophe in front of the number, not have a zero as the starting number, Excel warns you with a little green note in the top left corner and gives a FALSE when tested with the ISNUMBER formula

Convert text to numbers shortcuts in Excel

In the ideal world you should never receive a 'number' that Excel doesn't recognize as a number. However, with so many different systems and different ways the world treats decimals (full stop or comma?) this will always be an issue. Two shortcuts are shown to provide an instant answer.

Excel not recognising all dates

Length: 10 minutesComplexity: Easy

Occasionally, especially if your data includes dates moving from American (mm-dd-yyyy) to European (dd-mm-yyyy), you will see that Excel 'recognises' only some of the dates and not the others. You will know you have this issue if you see that all the dates that are considered valid by Excel are day 12 or less and the ones treated as text are day 13 or above. This is a serious issue and it means that not only the unrecognised dates are wrong but also the dates that you thought were recognised are incorrect.

Convert dates stored as text to dates (with formula)

Length: 10 minutesComplexity: Easy

If your dates are not pulling through correctly you may need to use one or more of these formula to convert them to a valid date. As mentioned previously, the benefit of using formula to do the conversion is that you can re use the formula which means you can create templates to clean the data.

Convert dates stored as text to dates (with tools)

Length: 10 minutesComplexity: Easy

If your dates are not pulling through correctly you may need to use one of these tools to (quickly) convert them to a valid date. As mentioned previously, using one of the inbuilt tools is great when you just quickly want to get Excel to 'see' the date.

Excel does not recognise dates

Length: 10 minutesComplexity: Easy

A common issue when importing and working with data is that Excel seems to have problems with noticing what is a date and what isn't a date. The key is to understand how Excel sees dates and then what needs to be done to convince Excel that it is looking at dates. Firstly it is important to realise that the format of the date (is it 25 Jan 2015 or Jan 25 2015 or 25/1/2015 or 2015/25/1) is not there for Excel's benefit. Formatting is only for the users benefit. All the above formats are seen exactly the same to Excel i.e. 42029. Excel basis all dates on the 1st Jan 1900 so the above dates are 42 029 days after the 1 Jan 1900. If Excel recognises that a cell contains a date, you should be able to format the date into any format you want including as a number. If you can't change the format of the date it is NOT A VALID Excel date no matter how much it looks like it to you. As far as Excel is concerned it is text.

Removing Duplicates in Excel- Tool

Length: 10 minutesComplexity: Easy

One of the first tools to learn to remove duplicates is the Remove Duplicates tool. This tool allows you to easily remove the duplicates in a data set. It has the added advantage of being able to specify what is meant by a duplicate i.e. if one column is the same, or do multiple columns have to be exactly the same to qualify as a duplicate.

Identifying Duplicates in Excel- Conditional Formatting

Length: 10 minutesComplexity: Easy

Duplicate entries in Excel are often the cause of problems. There are a number of ways to identify which items are duplicates. One of the easiest ways is to use the Conditional Formatting tool which will highlight either all the duplicates or all the unique items. A major benefit is that this is live in that as new data is entered it will automatically change colour if it is a duplicate.

Excel not recognizing a space

Length: 10 minutesComplexity: Easy

Occasionally when you try some of the methods mentioned in this course, you may come across situations where Excel does not seem to be seeing what you see. This is most common with spaces. If you try and replace a 'space' with nothing you may find that Excel tells you there are no spaces even though you can clearly see a space. Excel is seldom wrong and if it says there are no spaces then there are no spaces (i.e. no spaces generated by clicking the space bar on your keyboard). However we need to understand what Excel sees as a space. The video below shows the issue and how to address it...

Click in the cell and press enter (but many times)

Length: 10 minutesComplexity: Easy

When working with data, especially imported data, you may find that simply by clicking in a cell and then pushing enter, the 'text' will convert to numbers (it suddenly goes from left aligned to right aligned). If you have tried other methods that haven't worked then below are some methods that mimic this process but all in one go. Effectively what we want is Excel to go into a cell and then come out to go to another cell (much like you do when you click in a cell and push enter).

Replacing the problem characters

Length: 10 minutesComplexity: Easy

If the VALUE function doesn't work, then there must be some characters that are confusing Excel into believing that a 'number' is actually text. Firstly we need to see what these are. Sometimes you can see them straight away but sometimes you need to play within the cells to see what you need to do to make Excel see a number. Take a few cells and delete the characters that you think are causing the problem (typically commas, full stops, spaces). After each enter you should be able to see whether Excel has converted the cell into a number or is looking closer to a number. Once you know what the problem is you can address it...

Converting cells with the VALUE function

Length: 10 minutesComplexity: Easy

When you want to convert cell values that are appearing as text to be valid numbers in Excel the first thing to try is the VALUE function in Excel. As per the help files, the VALUE function 'converts a text string that represents a number to a number'. This will not work everytime though so you need to know more...

Establish a Control Total

Free Preview

Length: 10 minutesComplexity: Easy

The are some basic rules for data cleansing that you should follow when trying to clean some data in Excel. As eager as you may be to get stuck into the file sent via email or downloaded, take a few seconds to follow these rules to make your work life easier

Work off a Copy

Free Preview

Length: 10 minutesComplexity: Easy

The are some basic rules for data cleansing that you should follow when trying to clean some data in Excel. As eager as you may be to get stuck into the file sent via email or downloaded, take a few seconds to follow these rules to make your work life easier

Filling in data gaps in Excel – Option 3- Formula

Length: 15 minutesComplexity: Standard

The GOTO Special and Filter trick work well for filling in data gaps in Excel but there are times you want to set up a template so that every month or so you can simply copy paste the data and formula do the rest.

Filling in data gaps in Excel – Option 2- Tool

Length: 15 minutesComplexity: Easy

The GOTO Special trick works well for filling in data gaps in Excel but there are times where Excel cannot remember that many blank cells. Perhaps when you hit 100 000 rows. So there is another way to do it and you probably already use this tool everyday.

Two Variable Data Table in Excel

Length: 15 minutesComplexity: Standard

How to create a two variable data table in Excel to run a live sensitivity in your spreadsheet models by changing two variables at the same time and seeing all the combination results

Single Variable Data Table in Excel

Length: 15 minutesComplexity: Standard

In our opinion Data Tables is one of the best tools in Excel, both for running sensitivities and for repeating calculations in operational models. To see its benefit consider the example below. We have a property that we want to purchase and we have put the salient details in a model and calculated the monthly repayment and the total interest paid over the period. But we want to see what these will be at different interest rates as shown in the red circle. We could change the interest rate and copy/ paste as values the answers but what happens when the details change, and this is time consuming anyway. <a href="https://online-excel-training.auditexcel.co.za/wp-content/uploads/2015/06/single-variable-data-table-in-excel-1.jpg"><img class="aligncenter size-full wp-image-4213" src="https://online-excel-training.auditexcel.co.za/wp-content/uploads/2015/06/single-variable-data-table-in-excel-1.jpg" alt="single-variable-data-table-in-excel" width="716" height="393" /></a>A single variable data table will solve this.

Excel Scenario Manager

Length: 15 minutesComplexity: Easy

How to use the Excel Scenario Manager to easily run scenarios through your model and get a detailed summary of each scenario and the impact on key outputs

Goal Seek Multiple Cells (Solver)

Length: 10 minutesComplexity: Standard

How to use the free Excel Solver Add In to get Excel to change multiple cells at the same time to achieve a desired result in some of your results cells e.g. change the number of cups of different types of coffee I sell to see what my maximum revenue will be if I can only sell X cups of Coffee A, Y cups of Coffee B etc.

Load Solver Add In

Free Preview

Length: 5 minutesComplexity: Easy

How to load solver add in into Excel to be able run multi variable goal seeks in Excel

Goal seeking a solution in Excel

Free Preview

Length: 10 minutesComplexity: Easy

How to get Excel to calculate what an input cell needs to be in order to get the answer you want out of a spreadsheet. This is very useful when budgeting and forecasting.

Vlookup multiple values

Length: 10 minutesComplexity: Easy

Occasionally when performing a VLOOKUP, the lookup values are not all in one column but rather over 2 or more columns. So for example if we are looking up a person we want to use there first name and surname but these are in different columns in the lookup table.

Partial vlookup match- contains part of the value

Length: 15 minutesComplexity: Standard

How to do a partial vlookup match so that for example the vlookup finds something like Anitha Bath when only searching for the word Bath or finding the correct phone number when you only know 8 or the 11 characters.

Excel ageing formula

Length: 10 minutesComplexity: Standard

How to create an excel ageing formula to age accounts receivables, stock, invoices, products or any other item that needs to be aged in useful bands for further analysis in Pivot Tables or other reporting tool.

Vlookup #n/a

Length: 15 minutesComplexity: Standard

Giving the seven reasons why a vlookup formula may end up with a #n/a error message.

Vlookup #name? error

Length: 5 minutesComplexity: Easy

Reasons for a vlookup #name? error in a vlookup function and how to correct it

Vlookup #value error

Length: 5 minutesComplexity: Easy

The reason for a vlookup #value error and the likely cases where this might happen